1 / 28

Important Announcements and Assignments for Database Normalization Project

There will be no class this Thursday, providing an opportunity for group work on your project. Office hours are scheduled for today from 1:00 to 1:30 PM, Wednesday from 10:00 to 11:00 AM, and Friday from 1:30 to 2:30 PM. Focus on practicing database normalization principles including the First, Second, and Third Normal Forms. Assignments include designing tables in the Third Normal Form for order fulfillment and documenting relationships in Access. Review the importance of normalization and key concepts like primary and foreign keys.

jaime-willis
Download Presentation

Important Announcements and Assignments for Database Normalization Project

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. Announcements • No class on Thursday • Time to get together with your group and work on the project • Office Hours this week: • Today: 1:00 to 1:30 • Wednesday: 10:00 to 11:00 • Friday: 1:30 to 2:30

  2. Practice ExerciseNormalize Payment Voucher Payment Voucher

  3. Payment Voucher • First Normal Form • Select and define data elements of interest • Eliminate repeating groups • Identify primary key • Business Process Assumptions

  4. Primary Key? • One from non-repeating section • PV-Num • One from each repeating section • ProdCode • RRNum

  5. Second Normal Form • Is the FNF table in Second Normal Form? • Why or why not? • If not, what are the functional dependencies? • What business process assumptions did you make?

  6. Second Normal Form • Required since concatenated primary key • Seven combinations • PVNum ProdCode RRNum • PVNum + ProdCode • PVNum + RRNum • RRNum + ProdCode • PVNum + ProdCode + RRNum

  7. Functional Dependencies

  8. Corresponding Documents Table • Is this table in second normal form?

  9. Third Normal Form • Are the SNF tables in Third Normal Form? • Why or why not? • If not, what are the transitive dependencies? • What business process assumptions did you make?

  10. SNF Tables

  11. Third Normal Form • Transitive dependencies? • Supplier Information • PVNum identifies Supplier Name • Supplier Name identifies Supplier Shipping Address, CSZ?

  12. PV Normalized Schema

  13. Common Tables? • ZipCityState (all 3) • Product (all 3) • Supplier Shipping Location (PO & RR) • Supplier Sales Office (PO & PV) • Receiving Report Detail (RR & PV)

  14. Database Schema for Purchasing Cycle • These are the tables and relationships used to generate the Purchase Order, Receiving Report, and Payment Voucher

  15. Assignment • Design Third Normal Form Datadase for Order Fulfillment of CRC • You must be able to create these reports from data within the tables. • Sales Order Confirmation • Bill of Lading • Backorder Notice • Focus on the data, not the process • Do not worry about what triggers the Backorder Notice, just be able to create the report from the underlying tables

  16. Assignment • Once you have designed the tables: • Use Access to document the relationships between the tables and as your data dictionary • Create empty tables in Access • Enter the abbreviated field names, specify the data type and size, enter the long name as the caption • Set the primary key fields

  17. Assignment • Use the “relationships” feature in Access to define the relationships between tables • Always set to enforce referential integrity

  18. Assignment • Provide a description of the processes that you used to design the third normal form tables. Specifically: • You must justify your selection of fields for the primary keys • You must describe how you know that each table is in third normal form. • Provide all assumptions you had to make regarding the nature of the business operations to support the placement of the various data elements within the tables.

  19. Review • What is normalization and why is it important to normalize a database? • Distinguish between First, Second and Third normal forms. • What are the characteristics of a “primary key”?  • What does it mean when there is a “one to many” relationship? • What is a "foreign key"?

More Related