Tuesday, 28 March 2017

Database relationship

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
Student Canteen Relationship ER Diagram