 |
| Data Integrity and Constraints |
Data Integrity and Constraints
DBMS Tutorial
DBMS Introduction
Data integrity:
Data integrity is referring to maintaining and assuring the accuracy and consistency of data over its entire life-cycle. Database Systems ensure data integrity through constraints which are used to restrict data that can be entered or modified in the database.
Database Systems offer three types of integrity constraints:
Integrity Types
|
Definition
|
Enforced Through
|
Entity Integrity
|
Each table must have a column or a set of columns through which we can uniquely identify a row. These column(s) cannot have empty (null) values.
|
PRIMARY KEY
|
Domain Integrity
|
All attributes in a table must have a defined domain i.e. a finite set of values which have to be used. When we assign a data type to a column we limit the values that it can contain. In addition, we can also have value restriction as per business rules e.g. Gender must be M or F.
|
DATA TYPES,
CHECK CONSTRAINT
|
Referential Integrity
|
Every value of a column in a table must exist as a value of another column in a different (or the same) table.
|
FOREIGN KEY
|
Candidate Key:
A Candidate Key is a minimal set of columns/attributes that can be used to uniquely identify a single tuple in a relation. Candidate Keys are determined during database design based on the underlying business rules of the database.
Consider the following relation in the context of a business firm:
Employee(EmployeeNo, Name, AadharNo, Salary, DateOfBirth)
Let us try to identify some candidate keys for this relation:
Key
|
Rationale
|
EmployeeNo
|
This seems to be a good candidate key as companies usually issue a unique number for each employee.
|
AadharNo
|
This seems to be a good candidate key for a company based in India. However, we have assumed that every employee has an Aadhar number which may not be true. In addition to a multinational firm with employees across the globe, this will not work at all.
|
Name, DateOfBirth
|
This might work for a small firm of 10 people as a combination of Name and Date of Birth is likely to be unique.
|
Salary
|
This is not a good candidate as salary is generally paid uniformly to people at the same level.
|
EmployeeNo, DateOfBirth
|
It is not a candidate key as EmployeeNo alone is unique. By definition, only a minimal set of attributes can be a candidate key.
|
Thus the choice of candidate key depends upon the business context.
Primary key:
A Primary Key is the candidate key that is selected to uniquely identify a tuple in a relation.
The mandatory and desired attributes for a primary key are:
Mandatory
|
Desired
|
must uniquely identify a tuple
|
should not change with time
|
must not allow NULL values
|
should have short size e.g. numeric data types
|
Let us identify the primary key from the previous example:
Employee(EmployeeNo, Name, AadharNo, Salary, DateofBirth)
Key
|
Rationale
|
EmployeeNo
|
The good candidate as it is numeric, cannot be null and does not change with time.
|
AadharNo
|
It will be null for people who do not have an Aadhar number. Hence it cannot be considered as a primary key.
|
Name, DateOfBirth
|
Both Name and DateOfBirth cannot be null. However, even if uniqueness is guaranteed, it is not a good choice due to the large size.
|
When two or more columns together identify the unique row then it's referred to as Composite Primary Key. The combination of Name and DateOfBirth if selected as a primary key would be a composite primary key.
Foreign Key:
A Foreign Key is a set of one or more columns in the child table whose values are required to match with corresponding columns in the parent table. Foreign key establishes a relationship between these two tables. Foreign key columns on child tables must be the primary key or unique on the parent table. The child table can contain NULL values.
Let us take Employee and Computer tables as provided below:
The computer is the parent table with CompId as the primary key. The employee is the child table with Id as the primary key. If we want to allocate a maximum of one computer to an employee then CompId must be made the foreign key in the Employee table. It can only contain values that are present in the Computer table or no values at all. We cannot allocate a computer that does not exist to an employee.
Additionally, multiple rows in the child table can link to the same row of the parent table depending upon the type of relationship.
Quiz 13: The relationship between the two tables are created using __________
a) Candidate Key
b) Primary Key
c) Foreign Key ---
d) Check Constraint
Quiz 14: Which of the following statement(s) is/are FALSE about a primary key?
a) A primary key uniquely identifies a row
b) There can be more than one primary key for a table ---
c) Primary key column(s) in one table can be referenced by foreign key column(s) in another tabled) Columns with string data types cannot be made Primary Key because they are large ---
Quiz 15: The Entity Customer has three candidate keys: a) CustId b) Email and c) ContactNo. Suggest the best primary key for this entity
a) CustId ---
b) Email
c) ContactNo
d) CustId and ContactNo
RechargePlan
RechargePayment
Quiz 16: Identify the candidate key for the RechargePlan table
a) Operator
b) Plans
c) Operator, RechargeType
d) Operator, Plans ---
RechargePlan
RechargePayment
Quiz 17: Identify the primary key for table RechargePayment.
a) Operator
b) Plans
c) RechargeId ---
d) Operator, Plans
RechargePlan
RechargePayment
Quiz 18: Identify the foreign key for the table RechargePayment.
a) Operator
b) Plans
c) RechargeId, Operator
d) Operator, Plans ---
Exercise 1: Keys
Consider an application for an educational institute. Every department has several instructors but an instructor can be associated with only one department. One among the instructors would act as head of the department. Instructors are allocated to take classes in multiple courses. There are many instructors for a given course. Various relations and their sample data are provided below:
Department(DeptId,DName,HODId)
Course(CId,CName,Credit)
Instructor(InstrId,Name,DeptId,EMail)
Allocation(CId,InstrId)
Requirement:
1. Identify the candidate key, primary key, and foreign keys.
Collaborative Exercise 2: Keys
Consider a banking application where customers have accounts. Every customer has a unique customer id. A customer can have multiple accounts in the bank. Multiple customers can share the same account number (joint account). Accounts can be of various types like saving account, current account; recurring account, etc. Minimum balance must be maintained in each of these account types. Customers can perform deposit and withdrawal transactions through various means like online, cheque, cash, etc. A transaction can be initiated by a single customer. A customer must have a name, address and email id. Contact number is optional.
Some relations for this banking application are provided below.
1. Customer(CustId, Name, Address, EMail, ContactNumber, AcctNumber, AcctType, OpenDate)
2. AccountType(AcctType, MinBalance)
3. Transaction(TId, TranType, TDate, ChequeNum, AccNum, Amount, CustId)
Requirements:
1. Identify the candidate key(s), primary key and foreign key(s) for the above relations.
Data Integrity and Constraints
DBMS Tutorial 1
Back Next
---
I hope you likable this nice post. Do not forget to share it together with your friends, the Sharing Button is below the post. Apart from this, if there's any downside within the intermediate, don't hesitate to request the Comment Box. we are going to be happy to help you.
I will continue to write more and more on this blog, so do not forget to make our blog BlogLearner as a bookmark (Ctrl + D) on your mobile or computer and subscribe to us to get all the posts in your email. Do not forget to share these posts, if you like it. You can facilitate us reach additional individuals by sharing it on social networking sites like Facebook or Twitter.
 |
| Data Integrity and Constraints - DBMS Tutorial 1 |
No comments:
Post a Comment