From week 3 Tutorial

CUSTOMER Makes

ORDER

IsRequestedInPART

CId CName

CAddr CPhone

OId

PaidStatus

ODate

PDesc

PNamePId

Mapping Infinite Multi-valued Attributes

Makes

ORDER

IsRequestedInPART

OId

PaidStatus

ODate

PDesc

PNamePId

CUSTOMER

CId CName

CAddr

CPhone

CUSTPHONE Makes

CId CName CAddr

CUSTOMER

CPhone

CUSTPHONE

CId

Mapping relationships

PId PName PDesc

PART

ODate

ORDER

OId PaidStatus PId

CPhone

CUSTOMER Makes

CId CName

CAddr

IsRequestedInPART

ORDEROId

PaidStatus

ODate

PDesc

PNamePId

CUSTPHONE Makes

Mapping relationships

CId CName

CUSTOMER

CAddr

IsRequestedInPART

CPhone

PNamePId

CUSTPHONE Makes CUSTOMER Makes

ORDER

CId CName

CAddr

OId

PaidStatus

ODate

CId

PId PName PDesc

PART

ODate

ORDER

OId PaidStatus PId

PDesc

Final Schema

CId CName

CUSTOMER

CAddr

CId

PId PName PDesc

PART

ODate

ORDER

OId PaidStatus PId

CIdCPhone

CUSTPHONE

SQL Data Definition

CREATE TABLE Part (PId INTEGER,

PName CHAR(20),

PDesc CHAR(30),

PRIMARY KEY (PId))

PId PName PDesc

PART

CId CName

CUSTOMER

CAddr

CREATE TABLE Customer (Cid INTEGER,

CName CHAR(20),

CAddr CHAR(40),

PRIMARY KEY (Cid))

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)

CId CName

CUSTOMER

CAddr

CId

PId PName PDesc

PART

ODate

ORDER

OId PaidStatus PId

Joined Relations

Var1 Var2

a c

b d

Var3 Var4

b c

e f

12 3

4

Var1 Var2 Var3 Var4

1 a c b c

2 a c e f

3 b d b c

4 b d e f

JOINTID Var1

1 a

2 b

3 c

SID Var2

1 d

3 e

5 f

TID Var1 SID Var2

1 a 1 d

1 a 3 e

1 a 5 f

The Cartesian Product is

Table 1 Table 2

2 b 1 d

2 b 3 e

2 b 5 f

3 c 1 d

3 c 3 e

3 c 5 f

The Relations for (TID=SID) is:

JOINTID Var1

1 a

2 b

3 c

SID Var2

1 d

3 e

5 f

TID Var1 SID Var2

1 a 1 d

3 c 3 e

The Cartesian Product is

Table 1 Table 2

The Cartesian Product for (TID-SID) is:

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 table…in this case both are candidate keys

– Each non-key field is functionally dependent on every candidate key

Steps in normalization

First Normal Form

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

relation

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

Functional Dependencies

Order_ID Order_Date, Customer_ID, Customer_Name, Customer_Address

Therefore, NOT in 2nd Normal Form

Customer_ID Customer_Name, Customer_Address

Product_ID Product_Description, Product_Finish, Unit_Price

Order_ID, Product_ID Order_Quantity

Full Dependency

Transitive Dependencies

Partial Dependencies Partial Dependencies

Getting it into Second Normal Form: Removing Partial Dependencies

Partial Dependencies are removed, but there are still transitive dependencies

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

Getting it into Third Normal Form

Transitive dependencies are removed

Boyce-Codd Normal Form (BCNF)

Customer

Customer-id

Customer-street

Customer-city

Branch

Branch-ID

Branch-name

GetLoan

Loan-Number

Customer-name

Branch-city

Amount

Boyce-Codd Normal Form (BCNF)• Customer-schema = (customer-id, customer-name, customer-street,

customer-city)customer-id -> customer-name customer-street customer-

city

• Branch-schema = (branch-id, branch-name, branch-city)branch-id -> branch-name branch-city

• Loan-info-schema (branch-id, customer-id, loan-number, amount)loan-number -> 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)