Chapter 1 Overview of Database Concepts Chapter 1

Chapter 1 Overview of Database Concepts Chapter 1 Oracle 9 i: SQL 1

Chapter Objectives • Identify the purpose of a database management system (DBMS) • Distinguish a field from a record and a column from a row • Identify the basic components of an Entity. Relationship Model • Define three types of relationships that can exist between entities Chapter 1 Oracle 9 i: SQL 2

Chapter Objectives • Explain the purpose of normalization • Describe the role of a primary key • Identify partial dependency and transitive dependency in the normalization process Chapter 1 Oracle 9 i: SQL 3

Chapter Objectives • Explain the purpose of a foreign key • Determine how to link data in different tables through the use of a common field • Explain the purpose of a structured query language (SQL) Chapter 1 Oracle 9 i: SQL 4

Database Terminology • Database – logical structure to store data • Database Management System (DBMS) – software used to create and interact with the database Chapter 1 Oracle 9 i: SQL 5

Database Components • Bit (0 or 1) is the smallest data unit. • A few bits (usually 8) can be organized into a byte. • Each byte represents a character that is the basic building block of information. • Field • Record • File Chapter 1 Oracle 9 i: SQL 6

Database Components Character • Basic unit of data • Can be a letter, number, or special symbol Chapter 1 Oracle 9 i: SQL 7

Database Components - Field • A group of related characters • Represents an attribute or characteristic of an entity • Corresponds to a column in the physical database Chapter 1 Oracle 9 i: SQL 8

Database Components - Record • A collection of fields for one specific entity • Corresponds to a row in the physical database Chapter 1 Oracle 9 i: SQL 9

Database Components - File • A group of records about the same type of entity File (a table) Field A Field B Field C ……. . Record 1 Record 2 Record 3 Record 4 ……. Chapter 1 Oracle 9 i: SQL 10

What Are Relations • A Relational DB is a collection of relations • A relation is a two-dimensional table that has the following characteristics – Rows contain data about an entity – Columns contain data about attributes of the entity – Cells of the table hold a single value – Each column has a unique name – The order of the columns is unimportant – The order of the rows is unimportant – No two rows may be identical Chapter 1 Oracle 9 i: SQL 11

What Are Relations • Interchangeably Used Terms in Relational Database – table = file = relation – row = record= tuple – column = field = attribute Chapter 1 Oracle 9 i: SQL 12

Components Example Chapter 1 Oracle 9 i: SQL 13

The Customers File/Table/Relation • CUSTOMERS Name Address City State ……. Customer A John 123 5 th St. Huntsville AL ……. Customer B …… ………. ………… Customer C Customer D ……. Chapter 1 Oracle 9 i: SQL 14

Review of Database Design • Systems Development Life Cycle (SDLC) • Entity-Relationship Model (E-R Model) • Normalization Chapter 1 Oracle 9 i: SQL 15

Systems Development Life Cycle (SDLC) 5 Phases • Systems investigation – understanding the problem • Systems analysis – understanding the solution • Systems design – creating the logical and physical components Chapter 1 Oracle 9 i: SQL 16

Systems Development Life Cycle (SDLC) • Systems implementation – placing completed system into operation • Systems maintenance and review – evaluating the implemented system Chapter 1 Oracle 9 i: SQL 17

Entity-Relationship Model (E-R Model) • Used to depict the relationship that exists among entities. • Entity Definitions – An object of interest to the business – A class or category of thing – A named thing or a thing of significance about which the business needs information – Entity instance is the representation of a particular entity. Attribute is a characteristic of an entity. Chapter 1 Oracle 9 i: SQL 18

Entities: Examples COURSE INSTRUCTOR STUDENT code name fee length name phone no Chapter 1 Oracle 9 i: SQL 19

E-R Model Symbols Chapter 1 Oracle 9 i: SQL 20

Relationships: An Example enrolled in COURSE STUDENT taken by Each STUDENT may be enrolled in one or more COURSES Each COURSE may be taken by one or more STUDENTS Chapter 1 Oracle 9 i: SQL 21

Relationships • The following relationships can be included in an E-R Model: – One-to-one – One-to-many – Many-to-many Chapter 1 Oracle 9 i: SQL 22

One-to-one Relationship • Each occurrence of data in one entity is represented by only one occurrence of data in the other entity • Example: Each individual has just one Social Security Number (SSN) and each SSN is assigned to just one person Chapter 1 Oracle 9 i: SQL 23

One-to-many Relationship • Each occurrence of data in one entity can be represented by many occurrences of the data in the other entity • Example: A class has only one instructor, but each instructor can teach many classes Chapter 1 Oracle 9 i: SQL 24

Many-to-many Relationship • Data can have multiple occurrences in both entities • Example: A student can take many classes and each class is composed of many students • Can not be included in the physical database Chapter 1 Oracle 9 i: SQL 25

Relationship Types: More Examples Many-to-One Many-to-Many One-to-One Chapter 1 Oracle 9 i: SQL 26

Many-to-One Relationships visited by CUSTOMER assigned to Chapter 1 Oracle 9 i: SQL SALES REPRESENTATIVE 27

Many-to-Many Relationships attended by PATIENT assigned to Chapter 1 Oracle 9 i: SQL HEALTH CARE WORKER 28

One-to-One Relationships BICYCLE is ridden by RIDER the rider Chapter 1 Oracle 9 i: SQL 29

Entity-Relationship Model • An entity is usually represented as a square or rectangle. • A line between two entities depict their relationships (a dashed line for optional relationship) – One-to-one: straight line – One-to-many: a straight line with a “crowfoot” at the “many” end. – Many-to-many: a straight line with a “crowfoot” at each end. Chapter 1 Oracle 9 i: SQL 30

Example E-R Model Chapter 1 Oracle 9 i: SQL 31

Normalization • Determines required tables and columns for each table • Multi-step process. Application of a series of rules that gradually improve the design • Used to reduce or control data redundancy Chapter 1 Oracle 9 i: SQL 32

Unnormalized Data Contains repeating groups in the Author column in the BOOKS table Chapter 1 Oracle 9 i: SQL 33

First-Normal Form (1 NF) • Primary key is identified – Primary key is a field or a set of fields (composite PK) that serve to uniquely identify each record in a table. e. g. the SSN field in a table that contains students’ record. • Repeating groups are eliminated. Single valued attributes only Chapter 1 Oracle 9 i: SQL 34

Primary Keys • Primary key – Value must be unique for each record – Serves to identify the record – Present in every record – Can’t be NULL – Usually numeric Chapter 1 Oracle 9 i: SQL 35

Candidate Keys • Candidate key – Any field that could be used as the primary key – Should be a unique, unchanging numeric field Chapter 1 Oracle 9 i: SQL 36

Surrogate Keys • Surrogate key: created to be the record’s primary key identifier when no suitable primary key exists • Surrogate key has no real relationship to the record to which it is assigned, other than to identify the record uniquely • Developers configure the database to generate surrogate key values automatically • Surrogate keys are always numerical fields Chapter 1 Oracle 9 i: SQL 37

Foreign Keys • Foreign key: a field in a table that is a primary key in another table • Foreign key creates a relationship between the two tables • Foreign key value must exist in the table where it is a primary key Chapter 1 Oracle 9 i: SQL 38

Chapter 1 Oracle 9 i: SQL 39

Composite Keys • Composite key: a unique key that you create by combining two or more fields • Usually comprised of fields that are primary keys in other tables Chapter 1 Oracle 9 i: SQL 40

First-Normal Form (1 NF) ISBN and Author columns together create a composite primary key Chapter 1 Oracle 9 i: SQL 41

Composite Key-P. D. • More than one column is required to uniquely identify a row • Can lead to partial dependency - a column is only dependent on a portion of the primary key Chapter 1 Oracle 9 i: SQL 42

Second-Normal Form (2 NF) • Partial dependency must be eliminated – Break the composite primary key into two parts, each part representing a separate table Chapter 1 Oracle 9 i: SQL 43

Second-Normal Form (2 NF) BOOKS table in 2 NF Chapter 1 Oracle 9 i: SQL 44

Third-Normal Form (3 NF) Publisher contact name has been removed Chapter 1 Oracle 9 i: SQL 45

Summary of Normalization Steps • 1 NF: eliminate repeating groups, identify primary key • 2 NF: table is in 1 NF and partial dependencies eliminated • 3 NF: table is in 2 NF and transitive dependencies eliminated Chapter 1 Oracle 9 i: SQL 46

Linking Tables • Once tables are normalized, make certain tables are linked • Tables are linked through a common field • A common field is usually a primary key in one table and a foreign key in the other table • A foreign key is a column that is a primary key of another table Chapter 1 Oracle 9 i: SQL 47

Linking Tables • For every value of a foreign key there is a primary key with that value • A foreign key can never be null • A primary key must exist before the foreign key can be defined Chapter 1 Oracle 9 i: SQL 48

Chapter 1 Oracle 9 i: SQL 49

Just. Lee Books’ Database Assumptions – No back orders or partial shipments – Only US addresses – Shipped orders are purged (deleted) at the end of the month Chapter 1 Oracle 9 i: SQL 50

Structured Query Language (SQL) and DBMS • Data sublanguage • Structured Query Language (SQL) is a data sublanguage that has constructs for defining and processing a database • It can be – Used stand-alone within a DBMS command – Embedded in triggers and stored procedures – Used in scripting or programming languages • All DBMS use some variation of the standardized ANSI-SQL. (Oracle SQL Plus) Chapter 1 Oracle 9 i: SQL 51

Structured Query Language (SQL) and DBMS • SQL was developed by IBM in late 1970 s • SQL-92 was endorsed as a national standard by ANSI in 1992 • Data Definition Language (DDL) is used to define database structures • Data Manipulation Language (DML) is used to query and update data • DBMS: Database Management System • Examples of DBMS: Oracle, DB 2, Microsoft Access, SQL Server Chapter 1 Oracle 9 i: SQL 52

Client/Server DBMS Client/server database • Takes advantage of distributed processing and networked computers by distributing processing across multiple computers • DBMS server process runs on one workstation, and the database applications run on separate client workstations across the network Chapter 1 Oracle 9 i: SQL 53

Client/Server DBMS • Preferred for database applications that retrieve and manipulate small amounts of data from databases containing large numbers of records because they minimize network traffic and improve response times • Organizations generally use a client/server database if the database will have more than 10 simultaneous users and if the database is mission critical Chapter 1 Oracle 9 i: SQL 54

Client/Server Database Architecture Chapter 1 Oracle 9 i: SQL 55

The Oracle 9 i Client/Server Database • All Oracle server- and client-side programs use Oracle Net, a utility that enables the network communication between the client and the server Chapter 1 Oracle 9 i: SQL 56

Oracle DBMS • Oracle is the world’s most popular DBMS • It is a powerful and robust DBMS that runs on many different operating systems • Oracle DBMS engine: Personal Oracle and Enterprise Oracle • Example of Oracle products – SQL*Plus: a utility for processing SQL and creating components like stored procedures and triggers Chapter 1 Oracle 9 i: SQL 57

Oracle SQL *Plus • Oracle SQL*Plus or the Oracle Enterprise Manager Console may be used to manage an Oracle database • SQL*Plus is a text editor available in all Oracle • Case-insensitive • The semicolon (; ) terminates a SQL statement • The right-leaning slash (/) executes SQL statement stored in Oracle buffer Chapter 1 Oracle 9 i: SQL 58