Download
1 / 25
250 likes | 357 Views
IE 423 – Design of Decision Support Systems. Database development – Relationships and Queries. Table Design and Relationships. Recall our music tracking system. Table Design and Relationships. Recall our music tracking system. Table Design and Relationships.
E N D
IE 423 – Design of Decision Support Systems Database development – Relationships and Queries
Table Design and Relationships Recall our music tracking system
Table Design and Relationships Recall our music tracking system
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
Database Design Splitting the previous table into two tables… More efficient We don’t 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
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
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
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 can’t 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 can’t add the new guy – Fernando’s songs to the music table unless Fernando’s information is in the people table
Database Development Let’s go back to our House Survey Data
Database Development Let’s go back to our House Survey Data
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 Don’t forget that each record must have a unique house identifier …and what we will use this for?
Neighbor08a.mdb Make a relationship between these two tables How do you do this? What kind of relationship is this?
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)
Neighbor08a.mdb Make a relationship between what two tables? How do you do this? What kind of relationship is this?
College.mdb Let’s 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?
