http:www.cse.msu.educse103

1
Day 15 Review

• What datatype would be an appropriate choice for
• A cell phone number
• A favorite color
• A birthday or annivesary
• What was the primary key for tbl_People? Why?
• What did we do about missing data?

2
Day 16 Intermediate DesignUpdating
single-table designs

• What information in your contacts database is
  stored poorly?
• Many of the records have NULL
• Some records contain the same value
• Can we group some of this information together
  somehow?
• If we move data to another table, how do we
  relate it back to tbl_People?

3
Contacts Database

• The database contacts.mdb currently consists of a
  single table.
• We now need to accommodate a wide variety of
  email data
• Some friends have school, work, personal accounts
• Some family members don't have any email at all!
• How should we change the contacts database to
  best handle this new data?
• What type of relationship is needed?
• Assume no one shares an e-mail address

4
Four Steps to Redesign the contacts database

• Design the new tbl_Emails table
• Fields, Names, Datatypes
• We will make the assumption that an email address
  can belong only to one person
• (without this assumption, we would need several
  extra steps)
• Transfer the existing data to the new table and
  CHECK YOUR RESULTS
• Delete the unneeded column(s) from the original
  table (But once theyre gone)
• Enter any new email information

5
First Step

• Design the new tbl_Emails table
• Fields, Names, Data types
• Remember This table is to describe the Emails
  entity, not People
• Information not related to an email address
  doesn't belong here!
• What is an appropriate primary key?
• What foreign key(s) might you need to use?
• When you make a new table, it should (almost
  always) have a foreign key from at least one
  other table

6
Next Steps

• Transfer the existing data to the new table using
  action queries
• CHECK THE RESULTS!!!
• Delete the unneeded column(s) from the tbl_People
  table (AFTER checking)
• Enter any new phone information in the new table
  tbl_Phones

7
Redesign debrief

• What is the primary key in the new tbl_Phones
  table?
• Is there a foreign key in the tbl_Phones table?
  If so, what one?
• How was the data transferred?
• What column(s) needed to be removed?
• What difficulties were encountered?

8
Updating database exercise

• Note the size of your contacts database file now
  and again after these steps.
• Update the data type and length of each field in
  both tables to what is actually needed to
  accommodate the data.
• Use HELP to find out how to compact a database.
  Compact your contacts db.
• How was the size of your file affected?

9
musicDay07 database

• In classical music and jazz, many artists perform
  together on a single album but do not actually
  form a group.
• What type of relationship does this imply exists
  between artists and albums?
• How does our database musicDay07.mdb accommodate
  this?
• How can we change the database to handle this
  situation?

10
Four Steps to Redesign the musicDay07 database

• Make a new table tbl_PerformsIn
• Transfer the existing data into the new table and
  CHECK THE RESULTS
• Delete the redundant info from the original
  tables (AFTER checking)
• Enter any new data into the new table
• This new data is in the Classical_Music07.xls
  file in todays AFS space (see next slide)

11
Homework

• Work on the classical_music07.xls import
• What problems do you run into?
• How can you solve these?
• If you cant finish, come prepared to discuss
  your problems on Day 08.
• Check the link on todays classwork page for
  homework to do for Day 08
• Transfer addresses like phones
• Finish redesigns from class (Phones, PerformsIn)