Workshop_6

Notice that at the bottom of the window under

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: ey button in the Design tab on Windows or right click and select Primary Key on a Mac. In reality, this last example may not be completely correct. As an exercise, discuss why this may be so, identify the potential problem, and propose a solution. Relating Tables Why relate tables? When a database contains two tables with related information, it is a good idea to create relationships between the tables. Creating relationships between tables helps to avoid mistakes when entering data, and allows complex queries that involve information in more than one table. Examples of the need for relationships: 1. In a business database, it is better to have separate tables for customer information and orders than to enter the same information every time you have an order from the same customer. If all the information is in one table, you would need to go through every single record if a customer’s information changed. 2. It should not be possible to add a student ID to the Sign ­ups table unless the student is in the Student Info table. 3. When tables are related, their information can be used simultaneously in a query. Suppose that you need to know how many junior students are taking 2 ­unit courses. This requires a relationship between the tables for a multi ­table query Rules for Relating Tables 1. The two tables must share a field with matching information. The fields must have the same data type, but field names can be different. 12 Workshop 6: MS Office Access and NeoOffice 2. One of the fields must be a primary key. 3. Tables must be closed, not just minimized, when relating them. 4. Remember to enforce referential integrity when relating tables. Note: Follow these rules at all times when relating tables! You cannot properly relate tables without having all four rules in effect. If you ever have trouble with creating relationships, use the rules to double ­check your steps. Referential integrity refers to the consistency between linked tables in a relational database. It involves the concepts of primary key and foreign key. A foreign key is a column in a table that is a primary key in another table. Referential integrity can restrict the column designated as foreign key to contain only values present in the primary key of the parent table. In the example below, SSN in the COURSE SIGN ­UPS table is the foreign key for the SSN in the STUDENT INFO table. When referential integrity is enforced, a SSN that is not in the STUDENT INFO cannot appear in the COURSE SIGN ­UPS, to reflect the fact that students...
View Full Document

This note was uploaded on 06/03/2012 for the course PLS 21 taught by Professor Lieth during the Summer '08 term at UC Davis.

Ask a homework question - tutors are online