1 / 24

CS 3630 Database Design and Implementation

CS 3630 Database Design and Implementation. Design Methodology. A structured approach that uses procedures, techniques, tools, and documentation aids to support and facilitate the process of design. Three main phases Conceptual database design Understanding client data

dpritchard
Download Presentation

CS 3630 Database Design and Implementation

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. CS 3630 Database Design and Implementation

  2. Design Methodology A structured approach that uses procedures, techniques, tools, and documentation aids to support and facilitate the process of design. Three main phases • Conceptual database design Understanding client data E-R (EER) Model Contract between clients and designers E-R Model could be used for any database system • Logical database design Mapping E-R Model to (relational) database schema Normalization • Physical database design

  3. Attributes of Relationships E1 E2 A1B1 A2 composite (A21, A22) B2: Multi-value B3 PK: A1 PK: B1 E1 (1..*) RelatedTo (1) E2 Attributes: C1, C2, C3 Which table should the attributes C1, C2, and C3 go? Go with the FK!

  4. Attributes of Relationships E1 E2 A1B1 A2 composite (A21, A22) B2: Multi-value B3 PK: A1 PK: B1 E1 (1..*) RelatedTo (1) E2 Attributes: C1, C2, C3 Table Schemas (DBDL) E1 (A1, A21, A22, B1, C1, C2, C3) E2 (B1, B3) PK: A1 PK: B1 AK: None AK: None FK: B1 references E2 FK: None E3 (B1, B2, NewID) PK: NewID AK: None FK: B1 references E2

  5. One-to-One (1:1) Are the two entity types identical with different names? Client -------- Customer Staff ---------- Employee Can the two entity types be combined into one entity type? Client (1) Provides (1) RegstrationInfo Client (1) Has (1) Interview

  6. One-to-One (1:1) The two entity types are not identical, and it has been decided not to combine them into one entity. Do we need a new table for the relationship? NO! Copy one PK as FK. Which Direction: Three Cases • Total on one side and Partial on the other side • Partial both sides • Total on both sides

  7. Many-to-Many (*:*) Entity1 (0..*) RelatedTo (1..*) Entity2 Will Coping PK as FK work? NO! No multi-value attributes! What to do? Adding a new table!

  8. Many-to-Many (*:*) Client (0..*) Views (0..*) Property (Date, Time, Comment) New Table Viewing Attributes: Date, Time, Comment New relationships Client (1) Has (0..*) Viewing Viewing (0..*) IsFor (1) Property One (*:*) becomes two (1:*) Copy PK of both Client and Property to Viewing as FK Figure out PK or create a new ID attribute

  9. Example Original Entities (E-R Model) Property (Pno, Street, City, State, Zipcode, Ono) Client (Cno, firstName, lastName, Phone, MaxRent, PrefType) Client (0..*) Views (0..*) Property (Date, Time, Comment) Table Schemas (DBDL) Property (Pno, Street, City, State, Zipcode, Ono) PK: Pno AK: Street, City, State, Zipcode FK: Ono references Owner (not from the *:* relationship) Client (Cno, firstName, lastName, Phone, MaxRent, PrefType) PK: Cno AK: None FK: None Viewing (Cno, Pno, ViewDate, ViewTime, Comment) PK: Cno, ViewDate, ViewTime AK: None FK: Cno references Client Pno references Property

  10. Where to Store Attributes of Relationship Staff (1) Interviews (0..*) Client Attributes: date, time, comment Staff (StaffNo, …) PK: StaffNo AK: None FK: BranchNo references Branch Client (ClientNo, Name, Address, . . . InterviewStaffNo, InterviewDate, IntervieTime, InterviewResult) PK: ClientNo AK: None FK: InterviewStaffNo references Staff

  11. Where to Store Attributes of Relationship Staff (0..*) Inspects (0..*) Car Attributes: date, time, fault Staff (StaffNo, . . .) Car (CarNo, . . .) Inspection (ID, StaffNo, CarNo, Date, Time, Fault) PK: ID AK: CarNo, Date, Time StaffNo, Date, Time FK: StaffNo references Staff CarNo references Car

  12. Composite PK/FK E1 E2 A1: composite (A11, A12) B1 A2 B2 A3: composite (A31, A32, A33) B3: composite (B31, B32) PK: A1 PK: B1, B2 E1 (0..1) RelatedTo (1..*) E2 Attributes: Att1, Att2 Copy A1 to E2 as FK Copy (A11, A12) to E2 as FK!

  13. Composite PK/FK E1 E2 A1: composite (A11, A12) B1 . . . . . . PK: A1 PK: B1, B2 E1 (0..1) RelatedTo (1..*) E2 Attributes: Att1, Att2 E1 (A11, A12, A2, A31, A32, A33) PK: A11, A12 AK: None FK: None E2 (B1, B2, B31, B32, A11, A12, Att1, Att2) PK: B1, B2 AK: None FK: (A11, A12) references E1

  14. Complex Relationship(involving 3 or more entities) Client REGESTERS with Staff at Branch Add a new table Registration Registers Branch Staff Client

  15. Complex Relationship Client REGESTERS with Staff at Branch Add a new table Registration 1..1 1..1 0..* 0..* Branch Registration Staff Has CarryOut 1..1 Does 1..1 Client Is Client the same as Registration?

  16. Recursive Relationship One-to-Many (1:*) Staff (0..1) Supervises (0..*) Staff (Supervisor) (Supervisee) Add a new column (SupervisorNo) One-to-One (1:1) Add a new column Many-to-Many (*:*) Add a new table

  17. Superclass/subclass Relationship

  18. Multiple Relationships between two EntitiesStaff and Branch Branch (Bno, Street, City, State, Zipcode, Phone) Staff (Sno, firstName, lastName, Address) Branch (1) Has (1..*) Staff Staff (1) Manages (0..1) Branch Cannot have circular references. Branch (Bno, Street, City, State, Zipcode, Phone) Staff (Sno, firstName, lastName, Address, Bno, Position)

  19. Mapping Weak Entities toRelation Schema Primary Key may not exist Adding PK based on the relationship Combining weak entity into parent entity

  20. Strong and Weak Entities Staff (Sno, firstName, lastName, Address, Phone, Salary) Next_Of_Kin(firstName, lastName, Address, Phone) Staff (1..*) RelatedTo (0..1) Next_Of_Kin attribute: relationship This is a one-to-many relationship. Create PK for Next_Of_Kin and copy that with attribute relationship to Staff as FK Combine into one table Staff Same person may be Next_Of_Kin of multiple staff members Next_Of_Kin of different staff members are not related

  21. Design Methodology Three main phases • Conceptual database design Understanding client data E-R (EER) Model Contract between clients and designers E-R Model could be used for any database system • Logical database design Mapping E-R Model to (relational) database schema Normalization • Physical database design

  22. DBDL (Database Design Language) Branch (Bno, Street, City, State, Zipcode, Phone) PK: Bno AK: Street, City, State, Zipcode FK: None Staff (Sno, firstName, lastName, Address, Bno ) PK: Sno AK: None FK: Bno references Branch Client (Cno, firstName, lastName, Phone, MaxRent, PrefType) PK: Cno AK: None FK: None Viewing (ID, Rno, Pno, ViewDate, ViewTime, Comment) PK: ID AK: Rno, ViewDate, ViewTime Pno, ViewDate, ViewTime FK: Rno references Client (Cno) Pno references Property No multi-value attributes, no composite attributes. All relationships are maintained through FKs. New attribute ID for Viewing.

  23. Quiz 1

  24. Assignment 4 Due Friday, by 11 pm

More Related