Logical Database Design

1. Logical Database Design Lecture 6 Instructor Anna Sidorova BCIS4610 - Fall 2008

2. Agenda • Schedule review • HW 2 due • What to expect on the exam • Review of the Analysis phase • Introduction to the design • Interim report discussion reminder • Logical database design BCIS4610 - Fall 2008

3. Schedule BCIS4610 - Fall 2008

4. What to expect on the Exam • See Exam guide handout • Solution will be posted by Monday BCIS4610 - Fall 2008

5. Interim report • Title page and table of contents • Executive summary • Discussion of the problem – from the proposal • Description of system requirements with references to the appendix • Discussion of requirements collection • Description of the process to be supported by the system (ref to DFDs and Process maps) • Description of data to be processed and used by the system (ref to ERDs, CRUD) • Description of user interface requirements • Other requirements • Verbal discussion of the proposed system with references to the appendix • Definition of work for Stage 2 (forms, reports, plan) BCIS4610 - Fall 2008

6. Interim report cont’d • Appendix • Level 0 and Level 1 DFDs • Business Process Maps • ERD • Crud Matrix • Functional Hierarchy Diagram • Entity Model Reference Report • Function Definition Report • Dataflow and data store definition reports BCIS4610 - Fall 2008

7. Review of the analysis phase • Plan requirements collection • Collect system requirements • Document system requirements • Model processes • Model data • Model business rules • Model user interface requirements - usually combined with design • Manage change in system requirements BCIS4610 - Fall 2008

8. We will switch to some design and implementation issues now • Logical DB design • SQL • PL SQL • Oracle Developer tools BCIS4610 - Fall 2008

9. Logical Database Design and the Relational Model BCIS4610 - Fall 2008

10. Objectives • Definition of terms • List five properties of relations • State two properties of candidate keys • Transform E-R and EER diagrams to relations • Create tables with entity and relational integrity constraints BCIS4610 - Fall 2008

11. Relation • A relationis a named, two-dimensional table of data • Table is made up of rows (records), and columns (attribute or field) • Not all tables qualify as relations • Requirements: • Every relation has a unique name. • 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) • Attributes (columns) in tables have unique names • The order of the columns is irrelevant • The order of the rows is irrelevant BCIS4610 - Fall 2008

12. Primary Key Foreign Key (implements 1:N relationship between customer and order) Combined, these are a composite primary key (uniquely identifies the order line)…individually they are foreign keys (implement M:N relationship between order and product) Figure 5-3 -- Schema for four relations (Pine Valley Furniture) BCIS4610 - Fall 2008

13. Integrity Constraints • Domain Constraints • Allowable values for an attribute. See Table 5-1 • Entity Integrity • No primary key attribute may be null. All primary key fields MUST have data • Action Assertions • Business rules. Recall from Ch. 4 BCIS4610 - Fall 2008

14. Integrity Constraints • Referential Integrity – rule that states that any foreign key value (on the relation of the many side) MUST match a primary key value in the relation of the one side. (Or the foreign key can be null) • For example: Delete Rules • Restrict – don’t allow delete of “parent” side if related rows exist in “dependent” side • Cascade – automatically delete “dependent” side rows that correspond with the “parent” side row to be deleted • Set-to-Null – set the foreign key in the dependent side to null if deleting from the parent side  not allowed for weak entities BCIS4610 - Fall 2008

15. Referential integrity constraints (Pine Valley Furniture) Referential integrity constraints are drawn via arrows from dependent to parent table BCIS4610 - Fall 2008

16. Transforming EER Diagrams into Relations Mapping Regular Entities to Relations • Simple attributes: E-R attributes map directly onto the relation • Composite attributes: Use only their simple, component attributes • Multi-valued Attribute - Becomes a separate relation with a foreign key taken from the superior entity BCIS4610 - Fall 2008

17. Mapping a regular entity (a) CUSTOMER entity type with simple attributes (b) CUSTOMER relation BCIS4610 - Fall 2008

18. Mapping a composite attribute (a) CUSTOMER entity type with composite attribute (b) CUSTOMER relation with address detail BCIS4610 - Fall 2008

19. Transforming EER Diagrams into Relations Mapping Binary Relationships • One-to-Many - Primary key on the one side becomes a foreign key on the many side • Many-to-Many - Create a new relation with the primary keys of the two entities as its primary key • One-to-One - Primary key on the mandatory side becomes a foreign key on the optional side BCIS4610 - Fall 2008

20. Example of mapping a 1:M relationship (a) Relationship between customers and orders BCIS4610 - Fall 2008

21. Mapping the relationship Again, no null value in the foreign key…this is because of the mandatory minimum cardinality Foreign key BCIS4610 - Fall 2008

22. The Supplies relationship will need to become a separate relation Example of mapping an M:N relationship (a) ER diagram (M:N) BCIS4610 - Fall 2008

23. Composite primary key Foreign key Foreign key Three resulting relations New intersection relation BCIS4610 - Fall 2008

24. Mapping a binary 1:1 relationship (a) Binary 1:1 relationship BCIS4610 - Fall 2008

25. Resulting relations BCIS4610 - Fall 2008

26. Transforming ER Diagrams into Relations Mapping Associative Entities • Identifier Not Assigned • Default primary key for the association relation is composed of the primary keys of the two entities (as in M:N relationship) • Identifier Assigned • It is natural and familiar to end-users • Default identifier may not be unique BCIS4610 - Fall 2008

27. BCIS4610 - Fall 2008

28. BCIS4610 - Fall 2008

29. Mapping an associative entity (a) Associative entity BCIS4610 - Fall 2008

30. Three resulting relations BCIS4610 - Fall 2008

31. Transforming EER Diagrams into Relations Mapping Unary Relationships • One-to-Many - Recursive foreign key in the same relation • Many-to-Many - Two relations: • One for the entity type • One for an associative relation in which the primary key has two attributes, both taken from the primary key of the entity BCIS4610 - Fall 2008

32. Mapping a unary 1:N relationship (a) EMPLOYEE entity with Manages relationship (b) EMPLOYEE relation with recursive foreign key BCIS4610 - Fall 2008

33. Mapping a unary M:N relationship (a) Bill-of-materials relationships (M:N) (b) ITEM and COMPONENT relations BCIS4610 - Fall 2008

34. Transforming EER Diagrams into Relations Mapping Ternary (and n-ary) Relationships • One relation for each entity and one for the associative entity • Associative entity has foreign keys to each entity in the relationship BCIS4610 - Fall 2008

35. Figure 5-19: Mapping a ternary relationship (a) Ternary relationship with associative entity BCIS4610 - Fall 2008

36. Mapping the ternary relationship Remember that the primary key MUST be unique BCIS4610 - Fall 2008

37. Transforming ER Diagrams into Relations Mapping Supertype/Subtype Relationships • One relation for supertype and for each subtype • Supertype attributes (including identifier and subtype discriminator) go into supertype relation • Subtype attributes go into each subtype; primary key of supertype relation also becomes primary key of subtype relation • 1:1 relationship established between supertype and each subtype, with supertype as primary table BCIS4610 - Fall 2008

38. Figure 5-20: Supertype/subtype relationships BCIS4610 - Fall 2008

39. Figure 5-21: Mapping Supertype/subtype relationships to relations BCIS4610 - Fall 2008

40. Normalization BCIS4610 - Fall 2008

41. Learning Objectives • Define Normalization • Define 1st, 2nd and 3rd Normal Forms • Discuss normalization process BCIS4610 - Fall 2008

42. Normalization: Definitions • The process of converting complex data structures into simple, stable data structures • Well-Structured Relation • A relation that contains a minimum amount of redundancy and allows users to insert, modify and delete the rows without errors or inconsistencies 9.50 BCIS4610 - Fall 2008

43. Normalization Process • The goal is to bring each relation into the Third Normal Form. • The process bringing a relation into the 3rd Normal Form Goes through stages. • 1st Normal Form • 2nd Normal Form • 3rd Normal Form BCIS4610 - Fall 2008

44. Functional Dependencies • Functional Dependency • A particular relationship between two attributes. For a given relation, attribute B is functionally dependent on attribute A if, for every valid value of A, that value of A uniquely determines the value of B • Instances (or sample data) in a relation do not prove the existence of a functional dependency • Knowledge of problem domain is most reliable method for identifying functional dependency 9.52 BCIS4610 - Fall 2008

45. Functional Dependencies: Notations in Problems BCIS4610 - Fall 2008

46. Functional Dependencies • We can draw functional dependencies between attributes of a relation as follows: BCIS4610 - Fall 2008

47. Important Definitions • Multivalued Attributes (repeating groups) – non-key attributes or groups of non-key attributes the values of which are not uniquely identified by (directly or indirectly) (not functionally dependent on) the value of the Primary Key (or its part). BCIS4610 - Fall 2008

48. Important Definitions • A relation is unnormalized (not in the 1st Normal Form) if it has multivalued attributes or repeating groups. Repeating Group BCIS4610 - Fall 2008

49. Important Definitions • A relation is in the 1st Normal Form if it has no multivalued attributes or repeating groups. BCIS4610 - Fall 2008

50. Important Definitions • Partial Dependency – when an non-key attribute is determined by a part, but not the whole, of a COMPOSITE primary key. Partial Dependency BCIS4610 - Fall 2008