Access Chapter 3 (Reading)

Table Relationships/Relationships Tool
The Relationships Tool is used to define relationships between tables based on common fields. Relationships defined using the Relationships Tool are important as they help ensure integrity of data and they provide us with default join criteria for queries involving more than one table. 

There are three types of relationships between the data you are likely to encounter at this stage in the design: one-to-one, one-to-many, and many-to-many. To be able to identify these relationships, you need to examine the data and have an understanding of what business rules apply to the data and tables.

Example of One-to-One Join Type

In a one-to-one relationship, one record in a table is associated with one and only one record in another table. For example, in a school database, each student has only one student ID, and each student ID is assigned to only one person.

Example of One-to-Many Join Type

Here are some other examples of one-to-many relationships: People-Addresses (Each person can live at one address, but each address can house one or more people.) Owners-Pets (Each pet has one owner, but each owner can have one or more pets.)

Example of Many-to-Many Join Type

A typical example of a many-to many relationship is one between students and classes. A student can register for many classes, and a class can include many students. 

Primary Key Versus Foreign Key

Primary key is used to identify data uniquely therefore two rows can't have the same primary key. It cannot be blank..  Examples include data fields like student ID, social security number, client number.  It cannot be a person's name, because many people share the same name. 

On the other hand, foreign key is used to maintain relationship between two tables. Primary key of one table act as foreign key in the other table.  A primary key constrain is a column that uniquely identifies every row in the table of the relational database management system, while foreign key is a column that creates a relationship between two tables. 

Primary Key never accepts null values whereas foreign key may accept multiple null values.

Data Redundancy
Data redundancy is defined as the storing of the same data in multiple locations. An example of data redundancy is saving the same file three times to three different locations.  For example, data can be stored in a variety of media means in today's world.

Disadvantages of Data Redundancy
Data redundancy Increases the size of the database unnecessarily, potentially causes data inconsistency and decreases efficiency of database. It may also cause data corruption.

Is Redundancy Good or Bad?
Redundant data is a bad idea because when you modify data (update/insert/delete), then you need to do it in more than one place. This opens up the possibility that the data becomes inconsistent across the database. The reason redundancy is sometimes necessary is for performance reasons. 

Example:  If a clients phone number and email are store in a database, in multiple tables, then the user needs to remember to update that information in any table it appears.  This can be time-consuming and mistakes may be made.  If you have ever experienced trying to change your data in a business enterprise, it is possible this has taken a couple efforts, due to the changes not being made in all the places it needed changed.

During these week, students will be building table relationships between more than one table.