Database Management Systems & Programming

1. Database Management Systems & Programming LIS 558 - Week 5 ER Model Transformation Normalization Faculty of Information & Media Studies Summer 2000

2. Class Outline • E-R Transformation • E-R Transformation Exercises • Break • Normalization • Normalization Exercises

3. Steps to E-R ModelTransformation 1. Identify entities 2. Identify relationships 3. Determine relationship type 4. Determine level of participation 5. Assign an identifier for each entity 6. Draw completed E-R diagram 7. Deduce a set of preliminary skeleton tables along with a proposed primary key for each table (using cases provided) 8. Develop a list of all attributes of interest (not already listed and systematically assign each to a table in such a way to achieve a 3NF design (i.e., no repeating groups, no partial dependencies, and no transitive dependencies)

4. Transforming an E-R Model • General Rules Governing Relationships among Tables 1. All primary keys must be defined as NOT NULL. 2. Define all foreign keys to conform to the following requirements for binary relationships. • 1:M Relationship • M:N Relationship • 1:1 Relationship • Weak Entity

5. Transforming an E-R Model • 1:M Relationships • Create the foreign key by putting the primary key of the “one” (parent) in the table of the “many” (dependent). • Foreign Key Rules:

6. Transforming an E-R Model • Weak Entity • Put the key of the parent table (strong entity) in the weak entity. • The weak entity relationship conforms to the same rules as the 1:M relationship, except foreign key restrictions: NOT NULL ON DELETE CASCADE ON UPDATE CASCADE • M:N Relationship • Convert the M:N relationship to a composite (bridge) entity consisting of (at least) the parent tables’ primary keys.

7. Transforming an E-R Model • 1:1 Relationships • If both entities are in mandatory participation in the relationship and they do not participate in other relationships, it is most likely that the two entities should be part of the same entity.

8. Transforming an E-R Model • Case 1: M:N, Both sides MANDATORY

9. Transforming an E-R Model • Case 2: M:N, Both sides OPTIONAL

10. Transforming an E-R Model • Case 3: M:N, One side OPTIONAL

11. Transforming an E-R Model • Cases 1-3: M:N 1 M N 1 PATIENT prescribed DRUG PATIENT (PATIENT_ID, PATIENT_LNAME, PATIENT_PHYSICIAN,...) DRUG (DRUG_ID, DRUG_NAME, DRUG_MANUFACTURER, ...) PRESCRIBE(PATIENT_ID, DRUG_ID, DOSAGE, DATE…) NOTE: The relationship may have its own attributes.

12. Example of decomposing entitieswith a binary M:N relationship Students:Classes have an M:N relationship, therefore, decompose to three tables. bridge table

13. Transforming an E-R Model • Case 4: 1:M, Both sides MANDATORY 1 M EMPLOYEE PRODUCT checks EMPLOYEE (EMP_ID, EMP_DEPT, …) PRODUCT (PROD_ID, PROD_NAME, PROD_%FIBRE, EMP_ID... )

14. Transforming an E-R Model • Case 5: 1:M, Both sides OPTIONAL M 1 has PHYSIOTHERAPIST CLIENTS PHYSIOTHERAPIST (PT_ID, PT_LNAME, ...) CLIENT (CLIENT_ID, CLIENT_LNAME, CLIENT_OHIP#, …PT_ID)

15. Transforming an E-R Model • Case 6: 1:M, Many side OPTIONAL, one side MANDATORY 1 M MACHINE PARTS contains MACHINE (MACH_ID, MACH_NAME, MACH_DEPT, ...) PART (PART_ID, PART_NAME, PART_CATEGORY, …, MACH_ID)

16. Transforming an E-R Model • Case 7: 1:M, One side OPTIONAL, many side MANDATORY 1 M BAND MUSICIAN accepts BAND (BAND_ID, BAND_NAME, MUSIC_TYPE...) MUSICIAN (MUSICIAN_ID, MUSICIAN_INSTRUMENT, … BAND_ID)

17. Transforming an E-R Model • Case 8: 1:1, Both Sides MANDATORY

18. PLUMBER (PLUMBER_ID, PLUMBER_LNAME,…BUILDING_ID) BUILDING (BUILDING_ID, BUILDING_ADDRESS,...) Transforming an E-R Model • Case 8: 1:1, Both Sides MANDATORY PLUMBER BUILDING 1 1 assigned EMPLOYEE JOB-DESCRIPTION 1 1 has a EMPLOYEE (EMP_NUM, EMP_LNAME,…, JOB_DESC)

19. Transforming an E-R Model • Case 9: 1:1, Both Sides OPTIONAL TRAINER 1 1 has EXERCISER EXERCISER (EXERCISER_ID, EXERCISER_LNAME, …TRAINER_ID) TRAINER (TRAINER_ID, TRAINER_LNAME, ...)

20. Transforming an E-R Model • Case 10: 1:1, One Side OPTIONAL, One Side MANDATORY 1 1 EMPLOYEE AUTO has EMPLOYEE (EMP_ID, EMP_LNAME, EMP_PHONE,…) AUTO (LIC_NUM, SERIAL_NUM, MAKE, MODEL,, …, EMP_ID)

21. Transforming an E-R Model • Case 11: Weak Entity (Foreign key located in weak entity)

22. 1 M contains UNIT HOSPITAL Case 11. Decomposing Weak Entities • When the relationship type of a binary relationship is 1:M between an entity and its weak entity, two tables are required: one for each entity, with the entity key from each entity serving as the primary key for the corresponding table. • Additionally, the entity that has a dependency on the existence of another entity has a primary key that is partially or totally derived from the parent entity of the relationship. • Weak entities must be deleted when the strong entity is deleted. HOSPITAL (HOSP_ID, HOSP_NAME, HOSP_ADDRESS, ...) UNIT (HOSP_ID, UNIT_NAME, HEAD_NURSE, ...)

23. Transforming an E-R Model • Case 12: Multivalued Attributes

24. Decomposing an IS-A Relationship Entity CLIENT contains ClientNumber ClientName Address AmountDue SocialInsuranceNumber TaxIdentificationNumber ContactPerson Phone CLIENT 1 INDIVIDUAL CORPORATE CLIENT CLIENT Problem: Too many NULL values Solution: Separate into CLIENT entity plus several subtypes

25. Decomposing an IS-A Relationship • Create a table for the parent entity and for each of the child entities or subtypes • Move the associated attributes from the parent entity into the child table to which they correspond • From the parent entity take the entity key and add it as the primary key to the corresponding table for each child entity • In the event a table corresponding to a child entity already has a primary key then simply add the entity key from the parent entity as an attribute of the table corresponding to the child entity CLIENT CLIENT (CLIENT_ID, AMOUNT_DUE, …) INDIVIDUAL_CLIENT (CLIENT_ID, SIN#, …) CORPORATE_CLIENT(CLIENT_ID, GST#, …) 1 INDIVIDUAL CORPORATE CLIENT CLIENT

26. Transforming Recursive Relationships 1:1 - create a foreign key field (duplicate values not allowed) that contains the domain of primary key 1:M - create a foreign key field (duplicate values allowed) that contains the domain of primary key

27. Transforming M:N Recursive Relationships M:N - create a second relation that contains two foreign keys: one for each side of the relationship “course requires course.”

28. Decomposing Ternary relationships • When a relationship is three-way (ternary) four preliminary tables are required: one for each entity, with the entity key from each entity serving as the primary key for the corresponding table, and one for the relationship. • The table corresponding to the relationship will have among its attributes the entity keys from each entity • Similarly, when a relationship is N-way, N+1 preliminary tables are required.

29. Transforming an E-R Diagram • Converting an E-R Model into a Database Structure • A painter might paint many paintings. The cardinality is (1,N) in the relationship between PAINTER and PAINTING. • Each painting is painted by one (and only one) painter. • A painting might (or might not) be exhibited in a gallery; i.e., the GALLERY is optional to PAINTING.

31. Case 4 Case 7 Transforming an E-R Model • Transformed schema for ARTIST database PAINTER(PRT_NUM, PRT_LASTNAME, PRT_FIRSTNAME, PRT_INITIAL, PTR_AREACODE, PRT_PHONE) PAINTING(PNTG_NUM, PNTG_TITLE, PNTG_PRICE, PTR_NUM, GAL_NUM) GALLERY(GAL_NUM, GAL_OWNER, GAL_AREACODE, GAL_PHONE, GAL_RATE)

32. A Data Dictionary for the ARTIST Database

33. writes publishes PUBLISHER (Pub_ID, ___, ___, ___, ___, …) BOOK (ISBN, Pub_ID, ___, ___, ___, ___, …) AUTHOR (Author_ID, ___, ___, ___, ___, …) WRITES(ISBN, Author_ID, ___, ___, ___, ___, …) Case 6 Case 2 PUBLISHER Library Database Example AUTHOR BOOK M N M 1

34. takes STUDENT COURSE taught by Case 6 ENROLL (StudID, CourseID, ___, ...) FACULTY STUDENT (StudID, ___, ___, FacID, …) COURSE (CourseID, ___, ___, ___, …) FACULTY (FacID, ___, ___, ___, ___, …) Case 2 Case 2 TEACH (FacID, CourseID,…) University Example M N M M advises N 1

35. E-R Modeling & Transformation Exercise

36. E-R Modeling & Transformation Exercise Create an E-R model and define its table structures for the following requirements. - An INVOICE is written by a SALESREP. Each sales representative can write many invoices, but each invoice is written by a single sales representative. - The INVOICE is written for a single CUSTOMER. However, each customer may have many invoices. - An INVOICE may include many detail lines (LINE) which describe the products bought by the customer. - The product information is stored in a PRODUCT entity. - The product's vendor information is found in a VENDOR entity.

37. E-R Modeling & Transformation Exercise

38. E-R Modeling & Transformation Exercise • Keep in mind that the preceding E-R diagram reflects a set of business rules that may easily be modified • For example, if customers are supplied via a commercial customer list, many of the customers on that list will not (yet!) have bought anything, so INVOICE would be optional to CUSTOMER • We are assuming here that a product can be supplied by many vendors and that each vendor can supply many products. The PRODUCT may be optional to VENDOR if the vendor list includes potential vendors from which you have not (yet) ordered anything. • Some products may never sell, so LINE is optional to PRODUCT... because an unsold product will never appear in an invoice line. • LINE may be shown as weak to INVOICE, because it borrows the invoice number as part of its primary key and it is existence-dependent on INVOICE • The design depends on the exact nature of the business rules.

39. E-R Modeling & Transformation Exercise

40. E-R Modeling & Transformation Exercise CUSTOMER (CustomerID, …) INVOICE (InvoiceID, CustomerID, SalesRepID,…) LINE (InvoiceID, LineID, ProdID,…) PRODUCT (ProductID, …) SALESREP (SalesRepID, …) VENDOR (VendorID,…) ORDER (OrderID, ProductID, VendorID,…)

41. Further E-R Transformation Exercises

42. ER Modeling I handout - Q1 DIVISION (DivisionID,…ManagerID) DEPARTMENT (DeptID,…DivisionID) EMPLOYEE (EmpID, …DeptID) PROJECT (ProjectID,…) EMPLOYEE_PROJECT (EmpID, ProjectID,…) not null null allowed

43. ER Modeling I - Q2 INSTRUCTOR (InstructorID, HighestDegree, …) COURSE (CourseID, ClassTitle, …) CLASS (ClassID, CourseID, InstructorID, Term…) TRAINEE (TraineeID, …) ENROLL (TraineeID, ClassID, Term…)* * Optionally, create an EnrollmentID attribute to use as primary key. All foreign keys not null.

44. ER Modeling I - Q3 CUSTOMER (CustomerID, …) INVOICE (InvoiceID, CustomerID, SalesRepID,…) LINE (InvoiceID, LineID, ProdID,…) PRODUCT (ProductID, …) SALESREP (SalesRepID, …) VENDOR (VendorID,…) SHIP (ShipID, ProductID, VendorID,…) All foreign keys not null

45. ER Modeling I - Q4 AGENT (AgentID, LName, Region…) CLIENT (ClientID, LName,…) MUSICIAN (MusicianID, AgentID, Name, DaysAvailable,…) EVENT (EventID, ClientID, MusicianID, Date, Time, Location…) INSTRUMENT (InsturmentID, …) MUSICIAN_INSTRUMENT (MusicianID, InstrumentID, YearsExperience…) All foreign keys not null.

46. ER Modeling I - Q5 CITY (CityID, …) TEAM (TeamID, CoachID, CityID, …) PLAYER (PlayerID, TeamID,…) COACH (CoachID, TeamID,…) GAME (GameID, HomeTeamID, VisitorTeamID,…) All foreign keys not null.

47. ER Modeling II - Q1 COMPANY (CompanyID, …) DEPARTMENT (DepartmentID, CompanyID…) EMPLOYEE (EmployeeID, DepartmentID, …) DEPENDENT (EmployeeID, DependentID, …) EMPLOYEE_HISTORY (EmployeeID, HistoryID, …) All foreign keys are not null

48. ER Modeling II - Q2 MEMBER (MemberID, …) WORKOUT (WorkoutID, MemberID, Date…) EXERCISE (ExerciseID…) WORKOUT_EXERCISE (WorkoutID, ExerciseID, NumberSets, NumberReps,…)

49. ER Modeling II - Q3 EMPLOYEE (EmployeeID, Name…PositionID) PART_TIME_EMPLOYEE (EmployeeID, HourlyRate…) FULL_TIME_EMPLOYEE (EmployeeID, Salary, OfficeRoom, …) POSITION (PositionID, Title, Job_Description…) All foreign keys not null.

50. ER Modeling II - Q4 USER (UserID, Name, Department,…) PROBLEM (ProblemID, TimeSpent, UserID, ResolverID,…) HARDWARE (ProblemID, Description, Solution…) SOFTWARE (ProblemID, SoftwareVersion, …) RESOLVER (ResolverID, Name, Phone, Level, …) All foreign keys not null.