Chapter Four Database Design (Relational)

1. Chapter FourDatabase Design (Relational) Objectives Summary Keys (Constraints) Relational DBMS Normal Forms

2. Summary • DB Lifecycle • Business Requirements • Design (ER) • Build DB • Production • Architecture of DBMS • Definitions • Data Models • Database Design (ER Model) • Strong Entity • Weak Entity • Relationship • Functionality • Functional Dependency

3. Keys (Constraints) • A set of attributes whose values uniquely identify each entity in an entity set or a relationship set • How do we identify keys? Relation R with a1, a2, … an

4. Keys (Constraints) • Super key: Any set of attributes that uniquely identify each table. Student (Name, ID, GPA, Major, Minor, Address, Phone)

5. Keys(Constraints) • Candidate Key: Smallest super key • Primary key: Candidate key selected by the DBA

6. Keys (Constraints) Characteristic of primary key: • Uniqueness: At any given time, no two tuples can have the same value for a given primary key • Minimally: None of the attributes in the primary key can be discarded without distorting the uniqueness property

7. Keys (Constraints) • Foreign Key: An attribute(s) in an entity set one (relation one) which is the primary key of entity two(relation two) R1 (a,b,c,d,e) R2 (x,y,z,a,w) Faculty (ID, Name, Salary, D_name, age, Hiring_date) Department(D_name, No_Faculty, D_head)

8. Relational DBMS

9. Relational DBMS • RDBM: Data are represented as a set of tables (relation is a mathematical term for a table) • Originated by E.F. Codd(1970) • Based on sets theory • Record base data model

10. Structure: • A set of relations (Table) • Each relation has a unique name • Each relation has a set of attributes (Columns) • Each relation has a set of tuples (Rows)

11. Restriction on RDB: • No two tuples are the same • No two attributes are the same • The order of tuples are immaterial • The order of attributes are immaterial • There is an attribute or collection of attributes which identifies tuples uniquely called Primary Key • Value of attribute must be atomic

12. Intention vs. Extension R: Relation Name an: attribute Tm: tuple T[an]: value of attributes for tuple T

13. Converting E.R Diagram to Relational • Strong Entity sets: • Let E be a strong entity set with attributes a1, a2,a3, … an • Create a relation R with n distinct columns each of which corresponds to one of the attributes in E

14. Converting E.R Diagram to Relational • Weak Entity sets: • Let W be a weak entity set with attributes a1 ,a 2,a3 , … ak • Let E be the strong entity set on which W is dependent • Let primary key of E be e1 ,e2 ,e3 , … ex • Create a relation R with k+x columns (a1, a2 ,a3 , … am) & (e1 ,e2 ,e3 , … ex)

15. Converting E.R Diagram to Relational • Relationship: • Let R be a relationship among entity sets e1, e2,… en with primary keys (Ei) and attributes a1… an • Create a relation called R with Un Primary key (Ei) U {a1, … an}

16. Example • Convert the school ER diagram into relational database.

17. Normal Forms (Guidelines for RD design) • How do we know this design is good? • If it is not a good design, What should we do? • Modify our design ??.

18. Normal Forms (Guidelines for RD design) • First Normal Form (1NF) • Deals with the shape of the records • A relation is in 1NF if the values of domain is atomic for each attribute.

19. First Normal Form: 1NF • Example: R (A, B, C, …) R ( A B ) R ( A B ) a1 b1, b2 => a1 b1 a1 b2

20. First Normal Form: 1NF Example: • Person (Name Age Children ) Smith 42 John, Lori, Mark • Person (Name Age Child ) Smith 42 John Smith 42 Lori Smith 42 Mark

21. First Normal Form: 1NF Example: • Student ( Name Birthday ) S1 Feb 2,91 S2 March 8,88 • Student (Name, D_Birth, M_Birth, Y_Birth) • Note: 2NF and 3NF Deal with the relationship between non-key and key

22. Second Normal Form: 2NF • A relation R is in 2NF with respect to a set of FD if it is in 1NF and every non-prime attribute is Fully dependent on the entire key in R. • Fact: 2NF is violated when a non-key is a fact about a subset of a primary key

23. Second Normal Form: 2NF • Non-prime vs. prime: A relation R with attribute A and a set of FD on attribute A is prime if A is contained in some key of R, otherwise A is non-prime

24. Second Normal Form: 2NF • Example: R(A,B,C,D) with FD A, B ---> C, D A ---> D • D partially depends on A,B • C fully depends on A,B • A&B are prime (part of key) • If A is primary key. Is this in 2NF? • If A&B is primary key. Is this in 2NF?

25. Second Normal Form: 2NF • What should we do with a relation which is not in 2NF? • Example: R(A,B,C,D) • A, B ---> C, D • A ---> D • R1 (A,B,C) • R2(A,D)

26. Second Normal Form: 2NF • Example: What is the primary key? Part, Warehouse ---> Quantity Warehouse ---> Address

27. Second Normal Form: 2NF • Problems: • Repetition of information: Changing the address W! • Unable to present information: Warehouse with no part • Inconsistency • So … R1 (Warehouse, Address) R2 (Part, Warehouse, Quantity)

28. Second Normal Form: 2NF • Example: Professor ---> Course Student ---> Degree Professor ---> Student Key? Not in 2NF R1(Student, Degree) R2(Professor, Course, Student)

29. Third Normal Form (3NF): • A relation R is 3NF with respect to a set of FD if it is in 2NF and whenever A ---> B holds, then • A --> B is a trivial FD • A is a superkey for R • B is contained in a candidate key for R • A Non-key attribute non transitively depends on the Primary Key.

30. Third Normal Form (3NF): • Example: R(A,B,C,D) A, B --->D R1(A,B,D) D ---> C R2(D,C) • Fact: 3NF is violated when a non-key is a fact about another non-key Employee ---> Dept ---> Location

31. Third Normal Form (3NF): • Example: R(Employee, Dept, Location) • Employee ---> Dept Dept ---> Location R1(Employee, Dept) R2(Dept, Location) Problems?

32. Third Normal Form (3NF): • ItemInfo (item,price, discount) • Item ---> price • Price ---> discount Item price discount I1 .99 2% I2 .80 2% I3 .10 2% I4 5 10%

33. Third Normal Form (3NF): • Employee (ID, Name, Expertise ,Age, Dept) • ID --> Name • ID --> Expertise • ID --> Age • ID --> Dept • Dept --> Expertise

34. Third Normal Form (3NF): • Example: R(A,B,C,D) • A,B ---> C • A,C ---> D • So A,B is the Primary Key • Not in 3NF • R1(A,B,C) • R2(A,C,D)

35. Boyce Codd Normal Form: • Def: A relation schema R is in BCNF with respect to a set of FD, if it is 3NF and whenever X  A holds, then X is a superkey (AX)

36. Boyce Codd Normal Form: • Most 3rd NF relations are also BCNF • A 3rd NF relation is NOT in BCNF if: • Candidate keys in the relation are composite keys (not single attribute) • There is more than one candidate key in the relation, and • The keys are not disjoint (some attributes in the keys are common)

37. Boyce Codd Normal Form: • A relation is in BCNF if every determinant is a candidate key • R(A,B,C) • FD: A,B -> C C -> A • A is prime, so it is 3rd NF • C is not candidate key (Not in BCNF) Not BCNF R1(A,B,C) R2(A,C)

38. Boyce Codd Normal Form: • S(SupplierNo, sname, status, city) FD: • SupplierNo ---> status • SupplierNo ---> city • SupplierNo ---> sname • sname ---> status • sname ---> city • sname ---> SupplierNo • It is in BCNF; Every determinate is a candidate key

39. Boyce Codd Normal Form:

40. Boyce Codd Normal Form: S(SupplierNo, sname, PartNo, Qty) FD: • SupplierNo -- sname • SupplierNo, PartNo ---> Qty • sname, PartNo ---> Qty

41. Boyce Codd Normal Form: It is in 3NF; not in BCNF; Problems: Sname or SupplierNo are not candidate keys for this relation R1(SupplierNo, sname) R2(sname, PartNo, Qty)

42. Boyce Codd Normal Form: ClientInterview (ClientNo, InterviewDate, InterviewTime, StaffID, roomNo) ClientNo,InterviewDate -> InterviewTime ClientNo, InterviewDate -> StaffID ClientNo, InterviewDate -> RoomNo Staffid, InterviewDate, InterviewTime -> ClientNo RoomNo, InterviewDate, InterviewTime -> StaffID RoomNo, InterviewDate, InterviewTime -> ClientNo StaffID, InterviewDate -> RoomNo

43. Boyce Codd Normal Form: It is in 3NF Not in BCNF (StaffID, InterviewData) is not a cadidatekey

44. Boyce Codd Normal Form: • R1(ClientNo, InterviewData, InterviewTime, StaffID) • R2(StaffID,InterviewData, RoomNo)

45. Normal Forms: Cars(Model, NoCylinders, Madeln, Tax, Fee) • Model, NoCylinders ---> Madeln • Model, NoCylinders ---> Tax • Model, NoCylinders ---> Fee • NoCylinders ---> Fee • Madeln ---> Tax

46. Normal Forms: Primary Key? Model, NoCylinders • Is it in 1NF? • Is it in 2NF?

47. Normal Forms: Cars(Model, NoCylinders, Madeln, Tax) Licensing(NoCylinders,Fee)

48. Normal Forms: • Is it in 3NF? • Cars(Model, NoCylinders, Madeln) • Taxation(Madeln, Tax) • Licensing(NoCylinders, Fee) • Assume we have FD • Madeln ---> NoCylinders • It is not in BCNF • Cars(Model, NoCylinders) • EngineSize(NoCylinders, Madeln)

49. Practice: A: PropertyNo B: PropertyAddress C: InspectionDate D: InspectionTime E: Comments F: StaffID G: StaffName H: CarRegistrationNo FD: A,C -> D,E,F,G,H A -> B F -> G F,C -> H H,C,D -> A,B,E,F,G F,C,D -> A,B,E

50. Multivalue Dependency (MVD) • Multi valued Dependency are a generalization of FD • Relation R, with x,y subset attributes of of R we say X -->-> Y • There is a multivalued dependency of y on x. Given a value for x there is a set of values for y.