IE 423 Design of Decision Support Systems

1
IE 423 Design of Decision Support Systems

• Database development Relationships and Queries

2
Table Design and Relationships
Recall our music tracking system
3
Table Design and Relationships
Recall our music tracking system
4
Table Design and Relationships

• A relational database gets its power from the
  ability to manage multiple tables of data and the
  relationships among those tables
  
• MS Access is a relational database management
  system
  
• In designing and building a relational database
  you will define
  
• the tables (the fields and their properties, and
  
  
• The relationships among these tables

5

• Splitting the previous table into two tables

6
Database Design

• Splitting the previous table into two tables
• More efficient
• We dont have to reenter the people information
  for every song
  
• If we find a song in the song table how do we
  find the owner in the people table
  
• In a relation one table has a field that is
  unique (no other record can have the same value
  
• This field is called a Primary Key
• A related table will have a field with matching
  value (as the primary field in the other table),
  but it does not have to be unique
  
• This is called a foreign key
• In our example, ID is the foreign key in the
  people table
  

7

• Splitting the previous table into two tables

8

• So finding the owner of a particular song
• We find a song in the music table,
• and get the ID value
• Go to the People table and find a record where
  the ID matches the one that we picking in the
  music table
  
• music.id people.id
• Once you have setup the database Access does all
  of this for you
  

9

• Types of relationships in a relational database
• One to many one record in table A matches
  (potentially) many records in table B
  
• One to one one record in table A matches only
  one record in table B
  
• Many to many records in table A can have
  multiple matching records in table B --- and
  vis-versa
  
• This one is tricky
• Consider an Orders table and a Products table

10

• Referential Integrity (what?!)
• If a record in one table is linked (has a
  relationship) to one or more records in another
  table..
  
• We must protect that link
• Referential Integrity the assurance that this
  link does not get broken
  
• How could we violate referential integrity?
• Delete a record on one side of the relation
  without first deleting linked records on the
  other side.
  
• If Bob moves we cant delete his record from the
  people table without first deleting his songs
  from the music table
  
• Add a record on the many side of the relation
  without the corresponding record existing on the
  one side of the relation
  
• We cant add the new guy Fernandos songs to
  the music table unless Fernandos information is
  in the people table
  

11
Database Development

• Lets go back to our House Survey Data

12
Database Development

• Lets go back to our House Survey Data

13
Neighbor08a.mdb
14
Neighbor08a.mdb
15
Neighbor08a.mdb
16
Neighbor08a.mdb

• Remember that we split our House Survey data into
  two relations (tables)
  
• so now we need to create a table for the rest of
  the data
  
• Dont forget that each record must have a unique
  house identifier
  
• and what we will use this for?

17
New tables for Neighbor08 database
18
New tables for Neighbor08 database
19
Neighbor08a.mdb

• Make a relationship between these two tables
• How do you do this?
• What kind of relationship is this?

20
Neighbor08a.mdb

• Then, we are also interested in who lives in
  these houses
  
• so create another table for people
• You will need
• PersonID
• HouseID to tell which house they live in
• FirstName
• LastName
• DOB (what data type?)
• CellPhone (use an input mask)

21
Neighbor08a.mdb
22
Neighbor08a.mdb

• Make a relationship between what two tables?
• How do you do this?
• What kind of relationship is this?

23
College.mdb

• Lets look at another database
• Suppose you need to create a course registration
  system for college courses (or workshops, etc.)
  
• What are the objects that you need to work with?
• Students, Courses, ???
• So, you have a table of students
• You have table of courses
• How do you relate these two tables?
• Does this present any problems?

24
College.mdb
25
(No Transcript)