From week 3 Tutorial

1
From week 3 Tutorial
CAddr
CUSTOMER
CId
CName
PaidStatus
OId
ORDER
ODate
PName
PId
PART
PDesc
2
Mapping Infinite Multi-valued Attributes
PaidStatus
OId
ORDER
ODate
PName
PId
PART
PDesc
3
Mapping relationships
CAddr
CUSTOMER
CUSTPHONE
CId
CName
CPhone
4
Mapping relationships
CUSTPHONE
CPhone
PName
PId
PART
PDesc
5
Final Schema
PId
CId
CId
6
SQL Data Definition

• CREATE TABLE Part (PId INTEGER,
• PName CHAR(20),
• PDesc CHAR(30),
• PRIMARY KEY (PId))

CREATE TABLE Customer (Cid INTEGER,
CName CHAR(20), CAddr CHAR(40),
PRIMARY KEY (Cid))
7
SQL Data Definition

• CREATE TABLE Order (OId INTEGER,
• ODate DATE,
• PaidStatus CHAR(1),
• PId INTEGER,
• CId INTEGER,
• PRIMARY KEY (OId),
• FOREIGN KEY (PId) REFERENCES Part,
• FOREIGN KEY (Cid) REFERENCES Customer)

8
Joined Relations
Var1 Var2
a c
b d
Var3 Var4
b c
e f
1
2
3
4
9
JOIN
Table 1
Table 2
TID Var1
1 a
2 b
3 c
SID Var2
1 d
3 e
5 f
The Relations for (TIDSID) is
The Cartesian Product is
TID Var1 SID Var2
1 a 1 d
1 a 3 e
1 a 5 f
2 b 1 d
2 b 3 e
2 b 5 f
3 c 1 d
3 c 3 e
3 c 5 f
10
JOIN
Table 1
Table 2
TID Var1
1 a
2 b
3 c
SID Var2
1 d
3 e
5 f
The Cartesian Product for (TID-SID) is
The Cartesian Product is
TID Var1 SID Var2
1 a 1 d
3 c 3 e
11
Functional Dependencies and Keys

• Functional Dependency The value of one attribute
  (the determinant) determines the value of another
  attribute
• Candidate Key
• A unique identifier. One of the candidate keys
  will become the primary key
• E.g. perhaps there is both credit card number and
  SS in a tablein this case both are candidate
  keys
• Each non-key field is functionally dependent on
  every candidate key

12
Steps in normalization
13
First Normal Form

• No multi-valued attributes
• Every attribute value is atomic
• multi-valued attributes ? it is not a relation

14
Second Normal Form

• 1NF PLUS every non-key attribute is fully
  functionally dependent on the ENTIRE primary key
• Every non-key attribute must be defined by the
  entire key, not by only part of the key
• No partial functional dependencies

15
Functional Dependencies
Full Dependency
Transitive Dependencies
Partial Dependencies
Partial Dependencies
Order_ID ? Order_Date, Customer_ID,
Customer_Name, Customer_Address
Customer_ID ? Customer_Name, Customer_Address
Product_ID ? Product_Description, Product_Finish,
Unit_Price
Order_ID, Product_ID ? Order_Quantity
Therefore, NOT in 2nd Normal Form
16
Getting it into Second Normal Form Removing
Partial Dependencies
Partial Dependencies are removed, but there are
still transitive dependencies
17
Third Normal Form

• 2NF PLUS no transitive dependencies (functional
  dependencies on non-primary-key attributes)
• Note this is called transitive, because the
  primary key is a determinant for another
  attribute, which in turn is a determinant for a
  third
• Solution non-key determinant with transitive
  dependencies go into a new table non-key
  determinant becomes primary key in the new table
  and stays as foreign key in the old table

18
Getting it into Third Normal Form
Transitive dependencies are removed
19
Boyce-Codd Normal Form (BCNF)
Customer-street
Branch-name
Branch-city
Customer-id
Customer-city
Branch-ID
Customer-name
GetLoan
Customer
Branch
Loan-Number
Amount
20
Boyce-Codd Normal Form (BCNF)

• Customer-schema (customer-id, customer-name,
  customer-street, customer-city)
• customer-id -gt customer-name customer-street
  customer-city
• Branch-schema (branch-id, branch-name,
  branch-city)
• branch-id -gt branch-name branch-city
• Loan-info-schema (branch-id, customer-id,
  loan-number, amount)
• loan-number -gt amount branch-id
• e.g. (Melbourne101, c1234, L-44, 1000)
• (Melbourne101, c1235, L-44, 1000)
• NOT in BCNF
• Loan-schema (loan-number, branch-id, amount)
• Borrower-schema (customer-id, loan-number)