Normalization ISYS 464 Database Design Based on ERD

Normalization ISYS 464

Database Design Based on ERD • Strong entity: Create a table that includes all simple attributes – Composite • Weak entity: add owner primary key • Multi-valued attribute: Create a table for each multi-valued attribute – Key + attribute • Relationship: – 1: 1, 1: M • Relationship table: for partial participation to avoid null • Foreign key – M: M: relationship table – N-ary relationship: relationship table – Recursive relationship • Attribute of relationship • Superclass and subclass • Note: The database designed according to these rules will meet the 3 NF requirements.

Database design objectives • Eliminate data duplication. • Link related records in related tables.

Example Employee/Dependent report: Emp. ID: E 101 Ename: Peter Address: 123 XYZ St Dependent. Name Relationship DOB Nancy Daughter 1/1/95 Alan Son 12/25/03 Emp. Dependent Table: Emp. ID Emp. Name Address Dep. Name Relation Dep. DOB E 101 Peter 123 XYZ St Nancy D 1/1/95 E 101 Peter 123 XYZ St Alan S 12/25/03 Note: This database is able to produce the report, but has duplicated data.

Update Anomalies Due To Duplication • Modification anomaly: – Inconsistent data • Insertion Anomalies: – Enter an employee with no dependent – Null • Deletion Anomaly: – If Nancy and Alan become independent.

If we mix multivalue attribute with regular attributes in one table • Employee Table: – SSN, Ename, Sex, DOB, Phone – Employee may have more than 1 phone. • Key: SSN or SSN + Phone • Duplication ?

Example 2 • Emp. Dependent table: – Emp. ID, Ename, Address, Depname, Relation, Dep. DOB • Key: Emp. ID + Depname

If we mix two entities with 1: M relationship in one table • Faculty. Student table: – Faculty Advise Student: 1: M relationship – FID, Fname, SID, Sname, SAddress • Key: SID • Duplication?

If we mix two entities with M: M relationship in one table • Student. Course table: – SID, Sname, GPA, CID, Cname, Units • Key: SID + CID • Duplication?

Normalization • Decompose unsatisfactory relation into smaller relations with desirable properties. – No duplication • The original relation can be recovered by applying natural join to the smaller relations. – So that no information is lost in the process. • Keys and function dependency: – Which field is the key field of the EMp. Dependent Table? • Emp. ID + Dep. Name

Function Dependency • Relationship between attributes • X -> Y – The value of X uniquely determines the value of Y. – Y is functionally dependent on X. – A value of X is associated with only one value of Y.

Example • Employee table: – – SSN S 1 S 2 S 3 Ename Peter Paul Mary Sex M M F DOB 1/1/75 12/25/80 7/4/72 • Function Dependencies: – SSN -> Ename, SSN ->Sex, SSN -> DOB – SSN -> Ename, Sex, DOB • Any other FD: – Ename -> SSN ? – Ename -> Sex ? – DOB -> SSN ?

• What is the key of Employee table: – SSN • Observations: – – All non-key fields are functionally dependent on SSN. There is no other FD. The only FD is the key dependency. There is no data duplication in the Employee table.

Normalization Process • Inputs: – A “universal relation” – Function dependencies • Output: Normalized tables • Process: – Decompose the unnormalized relation into smaller relations such that in each relation the non key fields are functionally dependent on the key, the whole key, and nothing but the key. So help me Codd!

First Normal Form • The fields of a relation are all simple attribute. – All relational database tables meet this requirement. • Emp. Dependent table: – Emp. ID, Ename, Address, Depname, Relation, Dep. DOB – First normal form? Yes – Second normal form?

Second Normal Form • The non-key fields are functionally dependent on the key, and the whole key. – FD: • Emp. ID ->Ename, Address – Key: Emp. ID + Depname – Ename and Address depend on part of the key. • Every non-key field is fully functionally dependent on the key. • Decompose the EMp. Dependent table into two tables: – Emp. ID, Ename, Address – Emp. ID, Depname, Relation, Dep. DOB

• Employee Table: – SSN, Ename, Sex, DOB, Phone – Employee may have more than 1 phone. • FD: – SSN -> Ename, Sex, DOB, – SSN -> Phone ? • Key: SSN + Phone • 2 NF? No • Decompose into two tables: – SSN, Ename, Sex, DOB – SSN, Phone

• Faculty. Student table: – Faculty Advise Student: 1: M relationship – FID, Fname, Office, SID, Sname, SAddress • FD: – FID -> Fname, Office – SID -> Sname, SAddress, FID, Fname, Office • • Key: SID 2 NF ? Yes Duplication? Yes Why? – All non-key fields depend on the whole key, but not Nothing But the Key! • SID -> FID, Fname, Office • FID -> Fname, Office

Transitive Dependency • If X -> Y, and Y->Z then X -> Z. • Z if transitively dependent on the key. • SID -> FID, FID -> Fname, Office – SID -> Fname, Office – Fname and Office are transitively dependent on SID.

Third Normal Form • Every non-key field is: – Fully functionally dependent on the key, and – Non-transitively dependent on the key. • Decompose: – FID, Fname, Office – SID, FID, Sname, SAddress

Example Customer/Orders report: CID: C 101 Cname: Peter Address: 123 XYZ St OID Odate Sales. Person Amount O 25 1/1/04 John 125 O 30 2/25/04 Alan 500 Customer. Orders Table: CID CName Address C 101 Peter 123 XYZ St OID Odate Sales. Person Amount O 25 1/1/04 John 125 O 30 2/25/04 Alan 500

Example • Key: OID • FD: – OID -> CID, Cname, Address, Odate, Sales. Person, Amount – CID -> Cname, Address • 2 NF? Yes • 3 NF? No • Decompose: – CID, Cname, Address – OID, CID, Odate, Sales. Person, Amount

Example with 1: M Relationship • Faculty. Student table: – Faculty Advise Student: 1: M relationship – FID, Fname, SID, Sname, SAddress • FD: – FID -> Fname – SID -> Sname, Saddress • • Key: SID 2 NF? Yes 3 NF? No, because SID ->FID, FID -> Fname Decompose: – Table 1: FID, Fname – Tablw 2: SID, FID, Sname, SAddress

Example with M: M Relationship • Student. Course table: – SID, Sname, GPA, CID, Cname, Units • Key: SID + CID • Function Dependencies: – SID -> Sname, GPA – CID -> Cname, Units • 2 NF? No – Decompose: • Table 1: SID -> Sname, GPA • Table 2: CID -> Cname, Units • Table 3: SID, CID • 3 NF? Yes

Online Shopping Cart CID Addr Cname Customer 1 Has Cart. ID M Date Shopping. Cart M Qty Has M Product Price PID Pname

Normalized Database • Universal Relation: – CID, Cname, Addr, Cart. ID, Date, PID, Pname, Price, Qty • Key: Cart. ID + PID • FDs: – Cart. ID -> Date, CID, Cname, Addr – CID -> Cname, Addr – PID -> Pname, Price • Normalized database: – – CID, Cname, Addr Cart. ID, Date, CID PID, Pname, Price Cart. ID, PID, Qty

Database Design Based on ERD • Strong entity: Create a table that includes all simple attributes – Composite • Weak entity: add owner primary key • Multi-valued attribute: Create a table for each multi-valued attribute – Key + attribute • Relationship: – 1: 1, 1: M • Relationship table: for partial participation to avoid null • Foreign key – M: M: relationship table – N-ary relationship: relationship table – Recursive relationship • Attribute of relationship • Superclass and subclass • Note: The database designed according to these rules will meet the 3 NF requirements.

Denormalization • The refinement to the relational schema such that the degree of normalization for a modified relation is less than the degree of at least one of the original relations. • Objective: – Speed up processing