http:www'cse'msu'educse103

1
Debrief Homework

• Import data from the text file CanadianUniversitie
  s06.txt into a temporary table.
• What is the primary key for this table?
• Construct an APPEND query to copy select data
  from this table into the appropriate fields in
  table tbl_Schools

2
Relational Databases

• What are entities? How are they stored?
• What are attributes? How are they stored?
• What is a 1N relationship?
• MN relationship?
• 11 relationship?
• What is a foreign key?

3
Relationships in the music_Day07 database

• Copy the music_Day07 database and open it in
  Access
• Identify relationships between the tables.
• How did you find each?
• Classify each relationship as MN, 1N, or 11.

4
Contacts Database

• The database contacts.mdb currently consists of a
  single table.
• We now need to accommodate a wide variety of
  phone data.
• How should we change the contacts database to
  best handle this new data?
• What type of relationship is needed?
• (Assume one phone number is associated with
  exactly one person.)

5
Four Steps to Redesign the contacts database

• Design the new tbl_Phones table
• Fields, Names, Datatypes
• We will make the assumption that a phone number
  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 phone information

6
First Step

• Design the new tbl_Phones table
• Fields, Names, Data types
• Remember This table is to describe the Phones
  entity, not People
• What is an appropriate primary key?
• What foreign key(s) might you need to use?
• In a relational database, 99 of the time, when
  you make a new table, it should have a foreign
  key from at least one other table

7
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

8
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?

9
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?

10
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?

11
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)

12
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)