Foundations of Database Systems

1. Foundations of Database Systems ER and Database Designs Instructor: Zhijun Wang DBMS and Applications

2. Announcements Quiz #1 will be given in the last hour of the class Assignment 1 is due today DBMS and Applications

3. Keys • Candidate keys • Primary keys • Foreign keys DBMS and Applications

4. An example • A company has a number of employees. The attributes of employee include employee_id (identifier), name, address, and date of birth. The company also has several projects. Attributes of project include project identifier, name and start date. Each employee may be assigned to one or more projects, or may not be assigned to a project. A project must have at least one employeeassigned, and may have any number of employeesassigned. An employee's billing rate may vary by project, and the company wishes to record the applicable billing rate for each employee when assigned to a particular project. DBMS and Applications

5. ERD DBMS and Applications

6. An example • A company has a number of employees. The attributes of employee include employee_id (identifier), name, address, and date of birth. The company also has several projects. Attributes of project include project identifier, name and start date. Each employee may be assigned to one or more projects, or may not be assigned to a project. A project must have at least one employeeassigned, and may have any number of employeesassigned. An employee's billing rate may vary by project, and the company wishes to record the applicable billing rate for each employee when assigned to a particular project. DBMS and Applications

7. ERD DBMS and Applications

8. Steps for Transforming aData Model into a Database Design N DBMS and Applications

9. Create a Table for Each Entity EMPLOYEE (EmployeeNumber, EmployeeName, Phone, Email, HireDate, ReviewDate, EmpCode) Primary key is designated by key symbol Note shadowless table DBMS and Applications

10. Select the Primary Key • The ideal primary key is short, numeric and fixed • Surrogate keys meet the ideal, but have no meaning to users -surrogate key is a DBMS-supplied identifier of each row of a table. DBMS and Applications

11. Specify Candidate (Alternate) Keys • The terms candidate key and alternate key are synonymous • Candidate keys are alternate identifiers of unique rows in a table • ERwin uses AKn.m notation, where n is the number of the alternate key, and m is the column number in that alternate key DBMS and Applications

12. Specify Candidate (Alternate) Keys DBMS and Applications

13. Specify Column Properties: Null Status • Null status indicates whether or not the value of the column can be NULL DBMS and Applications

14. Specify Column Properties: Data Type • Generic Data Types: • CHAR(n) • VARCHAR(n) • DATE • TIME • MONEY • INTEGER • DECIMAL DBMS and Applications

15. Specify Column Properties: Default Value • A default value is the value supplied by the DBMS when a new row is created DBMS and Applications

16. Specify Column Properties: Data Constraints • Data constraints are limitations on data values: • Domain constraint - Column values must be in a given set of specific values • Range constraint - Column values must be within a given range of values • Intrarelation constraint– Column values are limited by comparison to values in other columns in the same table • Interrelation constraint - Column values are limited by comparison to values in other columns in other tables [Referential integrity constraints on foreign keys] DBMS and Applications

17. Create Relationships: 1:1 Strong Entity Relationships • Place the key of one entity in the other entity as a foreign key: • Either design will work – no parent, no child • Minimum cardinality considerations may be important: • O-M will require a different design than M-O, and • One design will be very preferable DBMS and Applications

18. Create Relationships:1:1 Strong Entity Relationships DBMS and Applications

19. Create Relationships: 1:N Strong Entity Relationships • Place the primary key of the table on the one side of the relationship into the table on the many side of the relationship as the foreign key • The one side is the parent table and the many side is the child table, so “Place the key of the parent in the child” DBMS and Applications

20. Create Relationships:1:N Strong Entity Relationships DBMS and Applications

21. Create Relationships:N:M Strong Entity Relationships • In an N:M strong entity relationship there is no place for the foreign key in either table: • A COMPANY may supply many PARTs • A PART may be supplied by many COMPANYs DBMS and Applications

22. Create Relationships:N:M Strong Entity Relationships • The solution is to create an intersection table that stores data about the corresponding rows from each entity • The intersection table consists only of the primary keys of each table which form a composite primary key • Each table’s primary key becomes a foreign key linking back to that table COMPANY_PART_INT (CompanyName, PartNumber) DBMS and Applications

23. Create Relationships:N:M Strong Entity Relationships COMPANY_PART_INT (CompanyName, PartNumber) DBMS and Applications

24. Relationships Using ID-Dependent Entities:Four Uses for ID-Dependent Entities • M-N Relationships • Association Relationships • Multivalued Attributes • Archtype/Instance Relationships • Occur when one entity represents a manifestation or an instance of another entity DBMS and Applications

25. Relationships Using ID-Dependent Entities:Association Relationships • An intersection table: • Holds the relationships between two strong entities in an N:M relationship • Contains only the primary keys of the two entities: • As a composite primary key • As foreign keys • An association table: • Has all the characteristics of an intersection table • PLUSit has one or more columns of attributes specific to the associations of the other two entities DBMS and Applications

26. Relationships Using ID-Dependent Entities:Association Relationships QUOTATION(CompanyName, PartNumber, Price) DBMS and Applications

27. Relationships Using ID-Dependent Entities:Multivaled Attributes As a data model As a set of tables DBMS and Applications

28. Relationships Using ID-Dependent Entities:Archetype/Instance Pattern As a data model As a set of tables DBMS and Applications

29. Relationships Using Weak Entities:Archetype/Instance Pattern As a data model As a set of tables DBMS and Applications

30. Mixed Entity Relationships: The Line-Item Pattern As a data model DBMS and Applications

31. Mixed Entity Relationships: The Line-Item Pattern As a set of tables DBMS and Applications

32. Mixed-Entity Relationships As a set of tables As a data model DBMS and Applications

33. Subtype Relationships As a data model As a set of tables DBMS and Applications

34. Recursive Relationships:1:1 Recursive Relationships As a data model As a table DBMS and Applications

35. Recursive Relationships:1:N Recursive Relationships As a data model As a table DBMS and Applications

36. Recursive Relationships:N:M Recursive Relationships As a data model As a set of tables DBMS and Applications

37. Design Issues Client has (possibly vague) idea of what he/she wants. You must design a database that represents these thoughts and only these thoughts. Avoid redundancy = saying the same thing more than once. Example Good: name name addr ManfBy 1 N Beers Manfs DBMS and Applications

38. Example Bad: repeats manufacturer address for each beer they manufacture. Bad: manufacturer’s name said name manf Manf addr Beers twice. name name manf addr ManfBy N 1 Beers Manfs DBMS and Applications

39. Use Schema to Enforce Constraints • The design schema should enforce as many constraints as possible. • Don't rely on future data to follow assumptions. Example • If registrar wants to associate only one instructor with a course, don't allow sets of instructors and count on departments to enter only one instructor per course. DBMS and Applications

40. Entity Sets Vs. Attributes You may be unsure which concepts are worthy of being entity sets, and which are handled more simply as attributes. Wrong: Right: name name ManfBy Beers Manfs name manf Beers DBMS and Applications

41. Intuitive Rule for E.S. Vs. Attribute Make an entity set only if it either: • Is more than a name of something; i.e., it has non-key attributes or relationships with a number of different entity sets, or • Is the “many” in a many-one relationship. DBMS and Applications

42. Example The following design illustrates both points: • Manfs deserves to be an E.S. because we record addr, a nonkey attribute. • Beers deserves to be an E.S. because it is at the “many” end. • If not, we would have to make “set of beers” an attribute of Manfs – something we avoid doing, although some may tell you it is OK in E/R model. name name addr ManfBy Beers Manfs DBMS and Applications

43. Other Design Issues • Use of entities vs. relationship sets • Possible guideline is to designate a relationship set to describe an action that occurs between entities • Binary versus n-ary relationship sets • Although it is possible to replace any nonbinary (n-ary, for n > 2) relationship set by a number of distinct binary relationship sets, • a n-ary relationship set shows more clearly that several entities participate in a single relationship. • Placement of relationship attributes DBMS and Applications