Database relationship

Microsoft Access is an example of relational database. In relational data base we can join a table to more than one table which makes relationship. With this capability our access database becomes more powerful than non-relational data base category. Consistency of database become easy and easily achieve by database on automatically.  If you want to make relationship in access database then you need at least two tables and one field must be common in both tables. For example, we are going to make relationship between two tables’ student and canteen. Student table store record of student and canteen stores what the products students are having. So in this type of relationship student is master table and canteen is related table. So student table need a primary key which uniquely identified a record from student table. Canteen table must have a key which match the data type of student table primary key which further known as foreign key.

Following things consider mandatory to implement relationship

·         Primary Key

o   A key which is used to uniquely identified a record from record set. A primary key cannot have NULL.

·         Foreign Key

o   Key in related table which matched the data type from table’s primary key through which we are going to make relationship

Integrity rule

1.       The table which needs to connect with any other table must have a primary key or it must be a strong entity.

2.       Foreign key cannot have NULL

Example:

In following example we are going to make table student and canteen and make relationship to track the billing information. Advantage of this relationship is its automatically maintain consistency of data base i.e

·         All student have unique roll number

·         Only those record can be enter in canteen table which match the roll number from student table

Data Dictionary

Student Table

Sr No.	Field	Data Type	Constraint
1	Roll	Number	PK
2	Name	Text	Not Null
3	City	Text	Not Null

Canteen Table

Sr No.	Field	Data Type	Constraint
1	Roll	Number	FK
2	Product	Text	Not Null
3	Amount	Text	Not Null
4	Date	Date/Time	Not Null

Er Diagram

Download PDF

Next Topic