Chapter 5
© Prentice Hall, 2002 7
Primary key(Ms access) An attribute (or combination) that uniquely identifies each row in a relation Foreign key(eg) Represents relationship between 2 tables Attribute (or combination) in a relation that is the primary key of another relation in same database Composite key Key consists of more than one attribute
Course_Title Date SPSS Surveys Tax Acc 6/19/1996 10/7/1996 10/7/1996
Emp_ID 100 140
Name Margaret Simpson Allen Beeton
Dept_Name Salary Marketing Accounting 48,000 52,000
© Prentice Hall, 2002
4
Relational Data Model
Relation showing sample data
Fields(Attributes,columns)
Emp_ID 100
Name
Dept_Name
Salary 48,000 52,000 43,000 55,000 42,000
Chapter 5
© Prentice Hall, 2002
5
Relational Notation
EMPLOYEE1 Emp_ID Name
Dept_Name
Salary
EMPLOYEE1(Emp_ID, Name, Dept_Name, Salary) DEPARTMENT(Dept_Name, Location)
Chapter 5
© Prentice Hall, 2002 8
Key Fields
Figure 6-5 -- Schema for four relations (Pine Valley Furniture)
Primary Key Foreign Key
Combined, these are a composite primary key (uniquely identifies the order line)…individually they are foreign keys
14
Example schema from textbook(p212)
CUSTOMER
Customer_ID
Customer_Name
Address
…
ORDER
Order_ID
ORDER_LINE
Order_Date Product_ID
Customer_ID Quantity
Order_ID
PRODUCT
Dept_Name Salary Marketing Marketing Accounting 48,000 48,000 52,000
Course_Title Date SPSS Surveys Tax Acc SPSS C++ SPSS Java 6/19/1996 10/7/1996 10/7/1996 1/12/1995 4/22/1996 1/25/1997 8/30/1999
Margaret Simpson Marketing Allen Beeton Chris Lucero Lorenzo Davis Susan Martin Accounting Info Systems Finance Marketing
Tuples (Records,Rows)
140 110 190 150
– Short text statements using relational notation – Graphical like ERD but with attributes listed within
the rectangle for each relation
Chapter 5
© Prentice Hall, 2002
Info Systems 43,000 Info Systems 43,000 Finance Marketing Marketing 55,000 42,000 42,000
Eg2
Chapter 5
© Prentice Hall, 2002
13
Relational Database
Consists of any number of relations Structure described through use of conceptual schema TWO methods for expressing a schema:
© Prentice Hall, 2002
Emp_ID 100 140 110 190 150
Name Margaret Simpson Allen Beeton Chris Lucero Lorenzo Davis Susan Martin
Dept_Name Salary Marketing Accounting 48,000 52,000
Product_ID
Chapter 5
Product_Description
…
15
© Prentice Hall, 2002
Textual Notation
CUSTOMER(Customer_ID, Customer_Name, Address, City, State, Zip) ORDER(Order_ID, Order_Date, Customer_ID) ORDER_LINE(Order_ID, Product_ID, Quantity) PRODUCT(Product_ID, Product_Description, Product_Finish, Unit_Price, On_Hand) Note Customer_ID is FKey on ORDER Both Order_ID and Product_ID are Fkey on ORDER_LINE, but difficult to show Chapter 5
48,000 52,000 43,000 55,000 42,000
Not all tables qualify as relations Requirements:(MS access) – Every relation has a unique name. – Attributes (columns) in tables have unique names – The order of the columns is irrelevant – The order of the rows is irrelevant – Every attribute value is atomic (not multivalued, not composite) – Every row is unique (can’t have two rows with exactly the same values for all their fields) 10 Chapter 5
Course_Title Date_Compl eted SPSS Surveys Tax Acc SPSS C++ SPSS Java 6/19/1996 10/7/1996 10/7/1996 1/12/1995 4/22/1996 1/25/1997 8/30/1999
Info Systems 43,000 Finance Marketing 55,000 42,000
Chapter 5
© Prentice Hall, 2002 3
Relational Data Model
Relation Named two dimensional table of data
– Set of named column
Chapter 5
Course_Title Date SPSS Surveys Tax Acc 6/19/1996 10/7/1996 10/7/1996
12
Chapter 5
© Prentice Hall, 2002
Emp_ID 100 100 140 110 110 190 150 150
Name Margaret Simpson Margaret Simpson Allen Beeton Chris Lucero Chris Lucero Lorenzo Davis Susan Martin Susan Martin
– Notation for relational data model – Integrity Constraints – Transforming ERD to relational model – Normalization and Normal Form (NF)
Chapter 5
© Prentice Hall, 2002 2
Relational Data Model
Represents data in the form of tables
Data Structure – Data organized in the form of tables with rows and columns Data Manipulation – Operations to manipulate the data stored in the tables Data Integrity – Facilities to specify business rules that maintain the integrity of the data when manipulated