1 / 9

Multiple Table Database Review

Multiple Table Database Review. What are entities ? What are records ? What are attributes ? How can you represent a 1:N (one-to-many) relationship ? How can you represent an M:N (many-to-many) relationship ?. MegaCorp Database. Design a new database for inventory usage

mbower
Download Presentation

Multiple Table Database Review

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. Multiple Table Database Review • What are entities? • What are records ? • What are attributes? • How can you represent a 1:N (one-to-many) relationship? • How can you represent an M:N (many-to-many) relationship? http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  2. MegaCorp Database • Design a new database for inventory usage • MegaCorp manufactures office furniture: • Bookcases, File Cabinets, Desks, Chairs • Desks and Chairs come in three styles: • Contemporary, Traditional, Classic • All furniture items have: • Color: beige, oak, walnut, mahogany, black • Size: depth, width, height (in inches, to hundredths) • Description, Item Number • It is necessary also to keep a record of the number of each item in stock http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  3. Database DesignLevel 1 • What questions do I want the database to answer? • What data do I need to answer these questions? • How are these data related to one another? http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  4. Database DesignLevel 2 • What entities are involved? • What attributes belong to each entity? • What datatype should be used for each of the different attributes? • What relationships exist between these entities? What type is each of these relationships? http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  5. Database Design Sequence • Use a “Relational Database Diagram” with boxes and connecting lines to initially identify entities, attributes and relationships. • Next convert boxes to tables and attributes to columns. Define primary keys. • Implement each one-to-many relationship via the use of a foreign key in the “many” entity. • Implement each many-to-many relationship via an extra “relationship table” which uses the pair of foreign keys as its primary key. http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  6. General Design Guidelines • Database designing should be an iterative process. As difficulties appear at one stage, you may need to go back and refine or modify earlier stages. • Try to reduce redundancies and dependencies (normalization). • Finish the design before entering data! http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  7. Begin design • Work with your groups. See slide 2 for specs. • Create a Relational Database Diagram (slide 5) and write out your tables on paper • Elect group spokesperson to share design with rest of class, we’ll compare notes and come to agreement • 10 minutes to work without our help • 15 more minutes after that • No need to put anything in Access yet (but you can if you finish the rest, just be prepared to change if needed) http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  8. MegaCorp Packages • MegaCorp wants to sell suites/groupings of furniture items. • Each suite consists of a variety of items in different quantities and has a suite name and a total price. • Modify your design to handle “suites”. • Work on paper first, only go to Access if your group has reached agreement (again, be prepared to change) • 10 minutes without help, then 10 more http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  9. Homework • Check the link on today’s classwork page for the homework for next class day. • Highlights (check web page for full details) • Finish/check Megacorp DB design • Import data into Megacorp http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

More Related