1 / 22

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

timothy-hanson
Download Presentation

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 Agreement 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. Mapping E-R Model to Database Schema • Entity type  Table schema Composite Attributes Multi-Value Attributes • How to maintain relationships? Primary Key and Foreign Key Many-to-Many

  3. How to maintain relationships? Foreign Key Primary key of another relation (table) Representing relationship between two relations (tables)

  4. Foreign Key: Examples Branch (Bno, Street, City, State, Zipcode, Phone) Staff (Sno, firstName, lastName, Address, Bno ) PrivateOwner (Ono, firstName, lastName, Phone (Multi-Value)) PropertyForRent (Pno, Street, City, State, Zipcode, Ono) Client (Cno, firstName, lastName, Phone, MaxRent, PrefType) Viewing (Cno, Pno, ViewDate, Comment)

  5. How to Describe a Table Schema? Database Design Language (DBDL) Branch (Bno, Street, City, State, Zipcode, Phone) PK: Bno AK: Street, City, Zipcode Phone FK: None Viewing (Rno, Pno, ViewDate, Comment) PK: Rno, ViewDate AK: None FK: Rno references Client (Cno) Pno references Property Multiple AK/FK on different lines! Viewing (Rno, Pno, ViewDate, Comment) PK: Rno, ViewDate AK: None FK: Rno, Pno One (not two!) FK! References!

  6. Mapping Strong Entities to Relation (Table) Schemas Remove composite attributes Adding new tables to remove multi-value attributes

  7. Replacing Composite Attributes with Individual Attributes Name: Composite (FirstName, LastName) Use two atriibutes: FirstName LastName Address : Compsite (Street, City, State, Zipcode) Use four attributes: Street City State Zipcode Performance issue

  8. Composite Attributes

  9. Replacing Composite Attributes with Simple Attributes

  10. Removing Multi-Value Attributes No multi-value attributes in relational databases First Order Logic No sets as set elements

  11. Multi-Value Attributes

  12. Removing Multi-Value Attributes Entity Type in E-R Model Branch branchNo . . . telNo: multi-value How to Store telNo? phoneOne phoneTwo When we know the limit of phone numbers at a branch. Staff staffNo homePhone workPhone cellPhone . . .

  13. A Branch has at Most Three Phone Numbers

  14. Home Phone, WorkPhone and Cell Phone

  15. Multi-Value Attributes Without Limit Entity Type in E-R Model Branch branchNo . . . telNo: multi-value (NO LIMIT!) More Examples Student Major Work Experience Research publications . . .

  16. Replacing Multi-Value Attributes One table  two tables Multi-Value attribute  multiple records

  17. Replacing Multi-Value Attributes Entity Type in E-R Model Branch branchNo . . . telNo: multi-value (NO LIMIT!) How to Store telNo? Remove it from Branch and create a new table! BranchPhone branchNo telPhone What is the PK? Does it have a FK?

  18. Replacing Multi-Value Attributes Entity Type in E-R Model Branch branchNo address: composite (street, city, state, zipCode) telNo: multi-value (NO LIMIT!) Table Schema (DBDL) Branch (branchNo, street, city, state, zip) PK: branchNo AK: street, city, zip FK: NONE BranchPhone (branchNo, telPhone) PK: telPhone AK: NONE FK: branchNo references Branch

  19. Removing Multi-Value Attributes Entity Type in E-R Model Staff staffNo . . . DOB workExperince: multi-value Composite (start, end, org, position) How to store workExperince? In a new Table! WorkExperience staffNo start end org position PK? Could create a new PK attribute (workExpID) FK?

  20. Storing Work Experience Entity Type in E-R Model Staff staffNo . . . DOB workExperince: multi-value Composite (start, end, org, position) Table Schema (DBDL) Staff (staffNo, . . ., DOB) PK: staffNo AK: NONE FK: NONE WorkExperience(workExpID, staffNo, start, end, org, position) PK: workExpID AK: ? FK: staffNo references Staff

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

  22. Mapping E-R Model to Relational database schema (DBDL) Branch (Bno, Street, City, State, Zipcode) PK: Bno AK: Street, City, Zipcode Phone FK: None BranchPhone (branchNo, telPhone) PK: branchNo, telPhone AK: NONE FK: branchNo references Branch Staff (Sno, firstName, lastName, Address, Bno ) PK: Sno AK: None FK: Bno references Branch WorkExperience(staffNo, start, end, org, position) PK: staffNo, start, org AK: NONE FK: staffNo references Staff No multi-value attributes, no composite attributes.

More Related