1 / 18

Chapter 4 Logical Database Design and the Relational Model

Chapter 4 Logical Database Design and the Relational Model. Jason C. H. Chen, Ph.D. Professor of MIS School of Business Administration Gonzaga University Spokane, WA 99258 chen@gonzaga.edu. #1-I(a), p.193. #1-I(b). Fig. 2-11: An associative entity (p. 78). #1-I (c).

media
Download Presentation

Chapter 4 Logical Database Design and the Relational Model

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 4 Logical Database Design andthe Relational Model Jason C. H. Chen, Ph.D. Professor of MIS School of Business Administration Gonzaga University Spokane, WA 99258 chen@gonzaga.edu

  2. #1-I(a), p.193 #1-I(b)

  3. Fig. 2-11: An associative entity (p. 78) #1-I (c) (a) Attribute on a relationship (Link Attribute/Associative)

  4. Answer to: #1-I (c)

  5. #1-I (c) EMPLOYEE EmployeeiD EmployeeName BirthDate CERTIFICATE EmployeeID CourseID DateCompleted COURSE CourseID CourseTitle

  6. #1-III(c) 3NF

  7. #2-III-a (p.193) – from Fig3-6b (p. 121)

  8. #2-III-b (p.193) from Fig. 3-7a (p.122)

  9. #2-III-c (p.193) – from Fig.3-9 (p.124)

  10. #2-III-d (p.193) – from Fig. 3-10 (p.125)

  11. 3.The normal form for the relations are: (make sure that you know why and how to transform it to 3NF) a.   3NF b.    3NF c.    2NF CLASS (CourseNo, SectionNo, Room) ROOM (Room, Capacity) d.   1NF COURSE (CourseNo, CourseName) CLASS (CourseNo, SectionNo, Room) ROOM (Room, Capacity)

  12. Vendor_Name Part_No Address Unit_Cost Description Logic Chip Logic Chip Memory Chip Memory Chip Memory Chip 1234 1234 5678 5678 5678 Fast Chips Smart Chips Fast Chips Quality Chips Smart Chips Cupertino Phoenix Cupertino Austin Phoenix 10.00 8.00 3.00 2.00 5.00 Part_No Vendor_Name Part_No, Vendor_Name Description Address Unit_Cost 7. Transforming Table 4-3 to relations: (p194) a) PART SUPPLIER b) c) Insert anomaly: we cannot insert a new vendor unless we also include a part number. Delete anomaly: if we delete part information, we also lose information about a vendor who supplies that part. Modification anomaly: if a vendor address changes, we have to modify all records (or rows) for that vendor.

  13. Part_No Description Vendor_Name Address Unit_Cost PART SUPPLIER Part_No Description Part_No Vendor_Name Unit_Cost Vendor_Name Address 7. d) e) 1NF f)

  14. 7. g)

  15. 8. a) partial dep. partial dep. transitive dep. b) 1NF

  16. 8. c) 3NF after remove partial & transitive dep.

  17. 3NF after remove partial & transitive dep. 8. d)

  18. 8. e) Using MS/Visio

More Related