1 / 38

Database Design – Lecture 17

Database Design – Lecture 17. Database Design Strategies: Top Down/Bottom UP. Lecture Objectives. Top Down Database Design Bottom Up Database Design. Top Down Database Design. Done from a business overview - narrative Conceptual Database Design Relational: Entity Relationship Diagram

taffy
Download Presentation

Database Design – Lecture 17

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. Database Design– Lecture 17 Database Design Strategies: Top Down/Bottom UP

  2. Lecture Objectives • Top Down Database Design • Bottom Up Database Design

  3. Top Down Database Design • Done from a business overview - narrative • Conceptual Database Design • Relational: Entity Relationship Diagram • Object Oriented: Object Oriented Data Model • Components: • Entities • Attributes • Relationship

  4. Top Down Database Design • Top Down Design Steps • Define possible entities and relationships • Define assumptions about relationships • Create initial ERD or OODM, including relationships and multiplicity • Define the possible attributes for each entity • If OODM, convert to a class diagram and then convert class diagram to relational • Normalize and create the relational schema

  5. Top Down Database Design • Conceptual Model Examples • Entity Relationship Diagram (with attributes and relationships)

  6. Top Down Database Design • ERD - Relational Schema

  7. Top Down Database Design • Conceptual Model Examples: • Object Oriented Data Model

  8. Top Down Database Design • Class Diagram

  9. Top Down Database Design • Tables: • PLAN (PLAN_ID (pk), TITLE) • CLIENT (CLIENT_ID (pk), ACCOUNT, TITLE, ADDRESS, TELEPHONE, FAX, PLAN_ID (fk))

  10. Bottom UP Database Design - Relational • Done from examples of reports or screens or just data • Take data samples and convert them to entities with attributes and relationships

  11. Bottom UP Database Design - Relational • Bottom Up Design Steps • Collect list of attributes • Define functional dependencies • Define unique identifier(s) • Make sure you have 1NF • Define partial dependencies • Normalize to 2NF • Define transitive dependencies • Normalize to 3NF

  12. Bottom UP Database Design - Relational • Bottom Up Design Steps • Define relationships and connectivity types • Create ERD • Make sure attributes are atomic and other relational database modeling techniques (I.e. description – long text) • Review ERD and complete relational database model

  13. Bottom UP Database Design - Relational • Bottom Up Design Steps – Data only example • Given these attributes: EMP_CODE 1003 LAST_NAME Willaker EDUCATION HS, BBA, MBA DEPT_CODE MKTG DEPARTMENT Marketing DEPT_MANAGER Jill H. Martin JOB_CLASS 23 TITLE Sales Agent DEPENDENTS Gerald (spouse), Mary (daughter), John (son) BIRTH_DATE 12/23/65 HIRE_DATE 10/14/94 TRAINING Level 1, Level 2 BASE_SALARY $32, 255

  14. Bottom UP Database Design - Relational • Given these attributes, we created tables: EMPLOYEE (EMP_CODE (pk), LAST_NAME, BIRTH_DATE, HIRE_DATE, DEPT_CODE (fk), JOB_CLASS (fk)) DEPARTMENT (DEPT_CODE (pk), DEPARTMENT, DEPT_MANAGER) JOB (JOB_CLASS (pk), TITLE, BASE_SALARY) DEPENDENT_TYPE (DEP_TYPE (pk), ROLE) DEPENDENT (DEP_CODE (pk), EMP_CODE (pk, fk), FIRST_NAME, DEP_TYPE (fk)) TRAINING (TRAIN_ID (pk), TRAINING_NAME) EMP_TRAINING (EMP_CODE (pk, fk), TRAIN_ID (pk, fk), DATE) EDUCATION (EDUC_CODE (pk), EDUC_NAME) EMP_EDUCATION (EMP_CODE (pk, fk), EDUC_CODE (pk, fk))

  15. Bottom UP Database Design – New Approach • Main Form/Sub Form approach • Consider main form/sub form as the entities in 1NF • List the attributes for each entity and normalize • Make sure attributes are atomic and other relational database modeling techniques (I.e. multi-valued dependencies) • Consider relationships and connectivity types • Create complete relational database model

  16. Main Form/Sub Form Approach • Example Form: Rogers Cable Invoice Invoice Date: Sep. 24, 2001 Customer Name Account: 230-16-161 Due date: October 17, 2001 New Charges Information Services Rogers@Home Service 39.95 Rogers@Home Outlet 10.00 Rogers@Home Cable Modem Rental 0.00 Tax GST#89223 3.50 Total New Charges 53.45 Main Form Sub Form

  17. Main Form/Sub Form Approach • Consider main form/sub form as the entities in 1NF • Main form: INVOICE (INVOICE_NUM, DATE, ACCOUNT_NUM, CUSTOMER_NAME, DUE_DATE) *Do not include tax and total, both of which are derived. *Assign a unique identifier • Sub form: INVOICE_CHARGES (SERVICE_NAME, CHARGE) *Need to recognize that this is for a specific invoice AND there could be multiple (therefore duplicate) services on an invoice.

  18. Main Form/Sub Form Approach • List the attributes for each entity and normalize 1NF 2NF 3NF INVOICE_NUM DATE ACCOUNT_NUM CUSTOMER_NAME DUE_DATE

  19. Main Form/Sub Form Approach • List the attributes for each entity and normalize 1NF: INVOICE (INVOICE_NUM (pk), DATE, ACCOUNT_NUM, CUSTOMER_NAME, DUE_DATE) 2NF (NONE) 3NF INVOICE (INVOICE_NUM (pk), DATE, ACCOUNT_NUM (fk), DUE_DATE) CUSTOMER (ACCOUNT_NUM (pk), CUSTOMER_NAME)

  20. Main Form/Sub Form Approach • List the attributes for each entity and normalize 1NF 2NF 3NF INVOICE_NUM LINE_NUM SERVICE_NAME CHARGE

  21. Main Form/Sub Form Approach • List the attributes for each entity and normalize 1NF: INVOICE_CHARGES (INVOICE_NUM (pk), LINE_NUM (pk), SERVICE_NAME, CHARGE) 2NF (NONE) 3NF (NONE) INVOICE_CHARGES (INVOICE_NUM (pk), LINE_NUM (pk), SERVICE_NAME (fk)) SERVICE (SERVICE_NAME (pk), CHARGE)

  22. Main Form/Sub Form Approach • Make sure attributes are atomic and other relational database modeling techniques • INVOICE (INVOICE_NUM (pk), DATE, ACCOUNT_NUM (fk), DUE_DATE) • CUSTOMER (ACCOUNT_NUM (pk), CUST_FNAME, CUST_LNAME) • INVOICE_CHARGES (INVOICE_NUM (pk), LINE_NUM (pk), SERVICE_ID (fk)) • SERVICE (SERVICE_ID (pk), SERVICE_NAME, CHARGE)

  23. Main Form/Sub Form Approach • Consider relationships and connectivity types (already taken into account) INVOICE (INVOICE_NUM (pk), DATE, ACCOUNT_NUM (fk), DUE_DATE) CUSTOMER (ACCOUNT_NUM (pk), CUST_FNAME, CUST_LNAME) INVOICE_CHARGES (INVOICE_NUM (pk, fk), LINE_NUM (pk), SERVICE_ID (fk)) SERVICE (SERVICE_ID (pk), SERVICE_NAME, CHARGE) *Note, what is not obvious here is the relationship between CUSTOMER and SERVICE and the fact that a customer can have more than one service and vice versa.

  24. Main Form/Sub Form Approach • Create complete relational database model

  25. Main Form/Sub Form Approach • Main Form/Sub Form approach • The hotel Blue Moon has a number of branches across the country. All of them are in need of furniture. The hotel has a few suppliers who specialize in some particular kind of furniture. The Head Office wants to keep track of all purchases using the following type of reports.

  26. Line # Invoice # Invoice Date Furniture ID – Title Number Of Items Item Price Total Paid Paid by chq # 1 111 01/29/11 B11 – Bed 10 200 2000 123-123-444 2 C11 – Chair 25 100 2500 3 D11 – Desk 2 350 700 4 Total for Invoice $5200.00 5 6 122 02/26/01 C11 – Chair 5 100 500 123-123-545 7 B12 – King Bed 10 600 6000 8 Total for Invoice $6500.00 Main Form/Sub Form Approach • Sample Purchasing Report: Supplier: Fine Furniture Customer: Blue Moon at Morin City Address: 15 Main Street Address: 1 Front Street Morin City, Y6F 7R9 Morin City, Y6F 4P8 Contact: Steve Good Tel: 444-888-777 Tel: 444-555-6666

  27. Line # Invoice # Invoice Date Furniture ID – Title Number Of Items Item Price Total Paid Paid by chq # 1 111 01/29/11 B11 – Bed 10 200 2000 123-123-444 2 C11 – Chair 25 100 2500 3 D11 – Desk 2 350 700 4 Total for Invoice $5200.00 5 6 122 02/26/01 C11 – Chair 5 100 500 123-123-545 7 B12 – King Bed 10 600 6000 8 Total for Invoice $6500.00 Main Form/Sub Form Approach • Sample Purchasing Report: Supplier: Fine Furniture Customer: Blue Moon at Morin City Address: 15 Main Street Address: 1 Front Street Morin City, Y6F 7R9 Morin City, Y6F 4P8 Contact: Steve Good Tel: 444-888-777 Tel: 444-555-6666 Main Form Sub Form (excluding totals)

  28. Main Form/Sub Form Approach • Step 1: Consider main form/sub form as the entities in 1NF • Main Form • Assume there are two distinct tables in the main form  SUPPLIER, CUSTOMER SUPPLIER (SUPPLIER_NAME, SUPPLIER_ADDRESS, SUPPLIER_CONTACT, SUPPLIER_TEL) CUSTOMER (CUSTOMER_NAME, CUSTOMER_ADDRESS, CUSTOMER_TEL)

  29. Main Form/Sub Form Approach • Step 1: Consider main form/sub form as the entities in 1NF • Sub Form: • Get rid of repeating groups • Rows 2, 3 and 7 contain empty cells. They assume the values of the rows above them • Rows 4 and 8 contain values for another business entity – total for invoice; a derived value. Ignore these rows.

  30. Main Form/Sub Form Approach • Step 1: Consider main form/sub form as the entities in 1NF Sub Form: • Get rid of repeating groups PAYMENT (INV_NUM, INV_DATE, FURN_ID_TITLE, QTY, ITEM_PRICE, TOTAL, CHEQUE_NUM)

  31. Main Form/Sub Form Approach • Step 2: List the attributes for each entity and normalize • Step 3: Make sure attributes are atomic and other relational database modeling techniques (I.e. multi-valued dependencies)

  32. Main Form/Sub Form Approach SUPPLIER (SUPPLIER_NAME, SUPPLIER_ADDRESS, SUPPLIER_CONTACT, SUPPLIER_TEL) • Need to create a unique identifier • Need to make attributes atomic (SUPPLIER_ADDRESS and SUPPLIER_CONTACT) SUPPLIER (SUPPLIER_ID (pk), SUPPLIER_NAME, SUPPLIER_STREET_NUMBER, SUPPLIER_STREET, SUPPLIER_CITY, SUPPLIER_PROVINCE, SUPPLIER_POSTAL_CODE, SUPPLIER_CONTACT_LNAME, SUPPLIER_CONTACT_FNAME, SUPPLIER_TEL)

  33. Main Form/Sub Form Approach CUSTOMER (CUSTOMER_NAME, CUSTOMER_ADDRESS, CUSTOMER_TEL) • Need to create a unique identifier • Need to make attributes atomic (CUSTOMER_ADDRESS)  CUSTOMER (CUSTOMER_ID (pk), CUSTOMER_NAME, CUSTOMER_STREET_NUMBER, CUSTOMER_STREET, CUSTOMER_CITY, CUSTOMER_PROVINCE, CUSTOMER_POSTAL_CODE, CUSTOMER_TEL)

  34. Main Form/Sub Form Approach PAYMENT (INV_NUM, INV_DATE, FURN_ID_TITLE, QTY, ITEM_PRICE, TOTAL, CHEQUE_NUM • Need to make attributes atomic (FURN_ID_TITLE  FURN_ID, FURN_NAME) • TOTAL is a derived attribute – ignore it • Need to create a unique identifier  PAYMENT (INV_NUM (pk), INV_DATE, FURN_ID (pk), FURN_NAME, QTY, ITEM_PRICE, CHEQUE_NUM)

  35. Main Form/Sub Form Approach • Step 2: Normalize PAYMENT INVOICE(INV_NUM (pk), INV_DATE, CHEQUE_NUM) FURNITURE (FURN_ID (pk), FURN_NAME, ITEM_PRICE) INV_DETAIL (INV_NUM (pk, fk), FURN_ID (pk, fk), QTY, ITEM_PRICE) 2NF 1NF INV_NUM INV_DATE FURN_ID FURN_NAME QTY ITEM_PRICE CHEQUE_NUM

  36. Main Form/Sub Form Approach • Step 4: Consider relationships and connectivity types • Relationships: CUSTOMER:INVOICE – 1:M SUPPLIER:INVOICE – 1:M INVOICE:INVOICE_DETAIL – 1:M INVOICE – INVOICE_DETAIL is strong/weak entity

  37. Main Form/Sub Form Approach • Step 5: Create complete relational database model SUPPLIER (SUPPLIER_ID (pk), SUPPLIER_NAME, SUPPLIER_STREET_NUMBER, SUPPLIER_STREET, SUPPLIER_CITY, SUPPLIER_PROVINCE, SUPPLIER_POSTAL_CODE, SUPPLIER_CONTACT_LNAME, SUPPLIER_CONTACT_FNAME, SUPPLIER_TEL) CUSTOMER (CUSTOMER_ID (pk), CUSTOMER_NAME, CUSTOMER_STREET_NUMBER, CUSTOMER_STREET, CUSTOMER_CITY, CUSTOMER_PROVINCE, CUSTOMER_POSTAL_CODE, CUSTOMER_TEL)

  38. Main Form/Sub Form Approach • Step 5: Create complete relational database model (cont’d) INVOICE(INV_NUM (pk), INV_DATE, CHEQUE_NUM, SUPPLIER_ID (fk), CUSTOMER_ID (fk)) FURNITURE (FURN_ID (pk), FURN_NAME, ITEM_PRICE) INV_DETAIL (INV_NUM (pk, fk), LINE_NUM (pk), FURN_ID (fk), QTY, ITEM_PRICE) • LINE_NUM (pk) added to ensure integrity of sequence of date • INV_NUM made pk and fk (existence dependence)

More Related