1 / 23

Design Methodology

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

ricky
Télécharger la présentation

Design Methodology

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. 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

  2. How to Represent Relationship? Primary Key and Foreign Key! 1:1 1:* (1..m) *:* (m..n) Complex relationship Recursive relationship Superclass / Subclass Relationship

  3. One-to-Many (1:*) Owner (1) Owns (0..*) PropertyForRent Parent entity: one side Owner Child entity: many side PropertyForRent Do we need a table for the relationship? NO! Copy PK of parent entity to child entity as FK Copy Ono to PropertyForRent Does it work to copy Pno to Owner?

  4. One-to-Many (1:*) E-R Model Owner (Ono, firstName, lastName, Phone) PropertyForRent (Pno, Street, City, State, Zipcode, Rent) Owner (1) Owns (0..*) PropertyForRent Table Schemas (DBDL) Owner (Ono, firstName, lastName, Phone) PK: Ono AK: FK: None PropertyForRent (Pno, Street, City, State, Zipcode, Rent, Ono) PK: Pno AK: Street, City, Zipcode FK: Ono references Owner

  5. One-to-One (1:1) Do we need a new table for the relationship? NO! Copy one PK as FK. Which Direction: Three Cases • Total on both sides • Partial both sides • Total on one side and Partial on the other side

  6. One-to-One (1:1) Total on One Side and Partial on the Other Side Entity1 (1..1) RelatedTo (0..1) Entity2 Partial Total Copy PK of Entity2 to Entity1 Some records of Entity1 will have null value for FK Partial Copy PK of Entity1 to Entity2 Each record of Entity2 will have a non-null value for FK Total Do it this way!

  7. One-to-One (1:1) Total on Both Sides Entity1 (1..1) RelatedTo (1..1) Entity2 What does it mean? Duplicate Entity? Combined into one entity. If not Duplicate Entity Copy PK of one entity to another entity as FK.

  8. One-to-One (1:1) Partial on Both Sides Entity1 (0..1) RelatedTo (0..1) Entity2 Either way Which side is more partial? Performance issue

  9. 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!

  10. Many-to-Many (*:*) Client (0..*) Views (0..*) Property (Date, Comment) New Table Viewing Attributes: Date, Comment New relationships Client (1..1) Has (0..*) Viewing (0..*) BeViewed (1..1) Property One (*:*) becomes two (1:*) Copy PK of both Client and Property to Viewing as FK Figure out PK

  11. Many-to-Many (*:*) 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, Comment) Table Schemas (DBDL) Property (Pno, Street, City, State, Zipcode, Ono) PK: Pno AK: Street, City, 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, Comment) PK: Cno, ViewDate AK: None FK: Cno references Client Pno references Property

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

  13. Complex Relationship Client REGESTERS with Staff at Branch Add a new table Registration Has CarryOut Branch Registration Staff Does Client

  14. Complex Relationship(involving 3 or more entities) Client REGESTERS with Staff at Branch Add a new table Registration New relationships Client (1..1) Does (1..1) Registration Staff (1..1) Carryout (0..*) Registration Branch (1..1) Has (0..*) Registration Is Client the same as Registration?

  15. Recursive Relationship One-to-many (1:*) Staff (0..1) Supervises (0..*) Staff (Supervisor) (Supervisee) Add a new column (SupervisorNo) Staff (staffNo, fName,…, SupervisorNo) Column SupervisorNo stores staffNo of the supervisor Could have null values Could have a new entity

  16. Recursive Relationship Many-to-Many (*:*) Add a new entity

  17. Recursive Relationship One-to-One (1:1) Add a new column Add a new entity? Could, but not required.

  18. Superclass/subclass Relationship

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

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

  21. Strong and Weak EntitiesStaff and Next_Of_Kin 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

  22. DBDL (Database Design Language) Branch (Bno, Street, City, State, Zipcode, Phone) PK: Bno AK: Street, City, Zipcode Phone 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 (Rno, Pno, ViewDate, Comment) PK: Rno, ViewDate AK: None FK: Rno references Client (Cno) Pno references Property No multi-value attributes, no composite attributes. All relationships are maintained through FKs.

  23. Assignment 4 Group with two from the same section Due Wednesday

More Related