http://cse103 u:/msu/course/cse/103 day 10, slide 1 multiple table database review what are...

http://www.cse.msu.edu/~cse103http://www.cse.msu.edu/~cse103U:/msu/course/cse/103U:/msu/course/cse/103 Day 10, Slide Day 10, Slide 11

Multiple Table Database ReviewMultiple Table Database Review

What are What are entitiesentities? ?

What are What are recordsrecords ? ?

What are What are attributesattributes??

How can you represent a How can you represent a 1:N1:N (one-to- (one-to-many) many) relationshiprelationship??

How can you represent an How can you represent an M:NM:N (many-to- (many-to-many) many) relationshiprelationship??


MegaCorp DatabaseMegaCorp DatabaseDesign a new database for inventory usageDesign a new database for inventory usageMegaCorp manufactures office MegaCorp manufactures office furniturefurniture::– Bookcases, File Cabinets, Desks, ChairsBookcases, File Cabinets, Desks, Chairs

Desks and Chairs come in three Desks and Chairs come in three stylesstyles::– Contemporary, Traditional, ClassicContemporary, Traditional, Classic

All furniture items have:All furniture items have:– ColorColor: beige, oak, walnut, mahogany, black: beige, oak, walnut, mahogany, black– SizeSize: depth, width, height (in inches, to hundredths): depth, width, height (in inches, to hundredths)– DescriptionDescription, , Item NumberItem Number

It is necessary also to keep a record of the It is necessary also to keep a record of the number number of each item in stockof each item in stock


Database DesignDatabase DesignLevel 1Level 1

What What questionsquestions do I want the database to do I want the database to answer?answer?

What What datadata do I need to answer these do I need to answer these questions?questions?

How are these data How are these data relatedrelated to one to one another?another?


Database DesignDatabase DesignLevel 2Level 2

What What entitiesentities are involved? are involved?

What What attributesattributes belong to each entity? belong to each entity?

What What datatypedatatype should be used for each of should be used for each of the different attributes?the different attributes?

What What relationshipsrelationships exist between these exist between these entities? What entities? What typetype is each of these is each of these relationships?relationships?


Database Design SequenceDatabase Design Sequence

Use a “Use a “Relational Database DiagramRelational Database Diagram” with ” with boxes and connecting lines to initially identify boxes and connecting lines to initially identify entities, attributes and relationships.entities, attributes and relationships.Next convert boxes to tables and attributes to Next convert boxes to tables and attributes to columns. Define primary keys.columns. Define primary keys.Implement each one-to-many relationship via the Implement each one-to-many relationship via the use of a foreign key in the “many” entity.use of a foreign key in the “many” entity.Implement each many-to-many relationship via Implement each many-to-many relationship via an extra “relationship table” which uses the an extra “relationship table” which uses the pairpair of foreign keys as its primary key.of foreign keys as its primary key.


General Design GuidelinesGeneral Design GuidelinesDatabase designing should be an Database designing should be an iterativeiterative process. As difficulties appear at one process. As difficulties appear at one stage, you may need to go back and refine stage, you may need to go back and refine or modify earlier stages.or modify earlier stages.

Try to Try to reducereduce redundancies and redundancies and dependencies (dependencies (normalizationnormalization).).

FinishFinish the design before entering data! the design before entering data!


Begin designBegin design

Work Work with your groupswith your groups. See . See slide 2slide 2 for specs. for specs.– Create a Relational Database Diagram (slide 5) and Create a Relational Database Diagram (slide 5) and

write out your tables on paperwrite out your tables on paper– Elect group spokesperson to share design with rest of Elect group spokesperson to share design with rest of

class, we’ll compare notes and come to agreementclass, we’ll compare notes and come to agreement

10 minutes to work without our help10 minutes to work without our help

15 more minutes after that15 more minutes after that

No need to put anything in Access yet (but you No need to put anything in Access yet (but you can if you finish the rest, just be prepared to can if you finish the rest, just be prepared to change if needed)change if needed)


MegaCorp PackagesMegaCorp Packages

MegaCorp wants to sell MegaCorp wants to sell suitessuites/groupings of /groupings of furniture items.furniture items.Each suite consists of a variety of items in Each suite consists of a variety of items in different quantities and has a suite name different quantities and has a suite name and a total price.and a total price.Modify your design to handle “suites”.Modify your design to handle “suites”.– Work on paper first, only go to Access if your Work on paper first, only go to Access if your

group has reached agreement (again, be group has reached agreement (again, be prepared to change)prepared to change)

– 10 minutes without help, then 10 more10 minutes without help, then 10 more


HomeworkHomework

Check the link on today’s classwork page Check the link on today’s classwork page for the homework for next class day.for the homework for next class day.

Highlights (check web page for full details)Highlights (check web page for full details)– Finish/check Megacorp DB designFinish/check Megacorp DB design– Import data into MegacorpImport data into Megacorp

http://cse103 u:/msu/course/cse/103 day 10, slide 1 multiple table database review what are...

Documents