http:www'cse'msu'educse103

1
Multiple Table Database Review

• What are entities?
• What are records ?
• What are attributes?
• How can you represent a 1N (one-to-many)
  relationship?
• How can you represent an MN (many-to-many)
  relationship?

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

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?

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?

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.

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!

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, well 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)

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

9
Homework

• Check the link on todays 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