RDBMS_Unit-2.1

Unit-I		Unit-II	Unit-III	Unit-IV	Unit-V
Part-I	Part-II	Part-I	Part-I	Part-I	Total
Part-III	Part-IV	Part-II	Part-II	Part-II
Part-V		Part-III

The Keys

1. Explain Different Keys in DBMS?

Ans:

We need to be able to identify one or more attributes (called relational keys) that uniquely identifies each tuple in a relation.

Superkey

A superkey uniquely identifies each tuple within a relation.  It may be an attribute, or set of attributes, that uniquely identifies a tuple within a relation. However, a superkey may contain additional attributes that are not necessary for unique identification.

Candidate key

A Candidate key is a superkey such that no proper subset is a superkey within the relation.

A candidate key K for a relation R has two properties:

• Uniqueness. In each tuple of R, the values of K uniquely identify that tuple.

• Irreducibility. No proper subset of K has the uniqueness property.

There may be several candidate keys for a relation. When a key consists of more than one attribute, we call it a composite key. 

    

For example, from the Figure the  suitable candidate key for the Staff relation would be IName, the employee’s surname.

Primary key

Because a relation has no duplicate tuples, it is always possible to identify each row uniquely. This means that a relation always has a primary key. In the worst case, the entire set of attributes could serve as the primary key, but usually some smaller subset is sufficient to distinguish the tuples. 

   Alternate keys

The candidate keys that are not selected to be the primary key are called alternate keys.

For the Branch relation, if we choose branchNo as the primary key, postcode would then be an alternate key.

Foreign key

An attribute, or set of attributes, within one relation that matches the candidate key of some relation. When an attribute appears in more than one relation, its appearance usually represents a relationship between tuples of the two relations. 

For example, the inclusion of branchNo in both the Branch and Staff relations is it links each branch to the details of staff working at that branch. In the Branch relation, branchNo is the primary key. However, in the Staff relation, the branchNo attribute exists to match staff to the branch office they work in. In the Staff relation, branchNo is a foreign key.

  

2. Explain Integrity Constraints?

There are primarily two integrity constraints: the entity integrity constraint and the referential integrity constraint.

Ans:

Entity Integrity

Entity integrity is a constraint that states that in a base relation no attribute of a primary key can be null.

For example, as branchNo is the primary key of the Branch relation, we should not be able to insert a tuple into the Branch relation with a null for the branchNo attribute. 

Referential Integrity

If a foreign key exists in a relation, either the foreign key value must match a candidate key value of some tuple in its home relation or the foreign key value must be wholly null.

For example, branchNo in the Staff relation is a foreign key targeting the branchNo attribute in the Branch relation, Branch. It should not be possible to create a staff record with branch number B025, for example, unless there is already a record for branch number B025 in the Branch relation. However, we should be able to create a new staff record with a null branch number to allow for the situation 

  where a new member of staff has joined the company but has not yet been assigned to a particular branch office. Database modifications can cause violations of referential integrity. 

 

Delete

During the deletion of a record two cases can occur:

Deletion of record in relation having the foreign key: In such a case simply delete the desired tuple.

Deletion of the target of a foreign key reference: If there is matching column for the record as foreign key reference in both relations the below operations possible.

RESTRICT – The delete operation is “restricted” to only the case where there are no such matching tuples.

CASCADE – The delete operation “cascades” to delete those matching tuples also. 

 2. What is Functional Dependency Explain with example? Ans:

Functional Dependency

Consider a relation R that has two attributes A and B. The attribute B of the relation is functionally dependent on the attribute A .

In other words, the value of attribute A uniquely determines the value of attribute B and if there were several tuples that had the same value of A then all these tuples will have an identical value of attribute B.

The FD between the attributes can be written as:

R.A->R.B or simply A->B

if B is functionally dependent on A (or A functionally determines B).

For example, the relation in Figure 3, whose dependencies are shown in Figure 4, can be written as:

Enrolmentno -> Sname

Enrolmentno -> Address

Cno -> Cname

Cno -> Instructor

Instructor->Office

 These functional dependencies imply that there can be only one student name for each Enrolmentno, only one address for each student and only one subject name for each Cno. 

   If we consider Cno à Instructor, the dependency implies that no subject can have more than one instructor . Functional dependencies therefore place constraints on what information the database may store

Sname -> Enrolmentno (1)

Cname -> Cno (2) 

 For example, consider the following E-R diagram:

In the ER diagram as above, the following FDs exist:

FDs in Entities:

Student entity:

Enrolment number à Student name, Address

Course Entity

Course code à Course name

Instructor/ Teacher Entity:

Instructor à Office

FDs in Relationships:

Enrols Relationship: None as it is many to Many  Teaches Relationship:

Course code -> Instructor

Instructor ->Course code 

  

3 . Explain Normalization and types of normal forms?

Ans:

Normalisation involves decomposition of a relation into smaller relations based on the concept of functional dependence to overcome undesirable anomalies.

Types of Normal forms

The First Normal Form (1NF)

Let us first define 1NF:

Definition: A relation (table) is in 1NF if

1. There are no duplicate rows or tuples in the relation.

The first requirement above means that the relation must have a key. The key may be single attribute or composite key. It may even, possibly, contain all the columns.

2. Each data value stored in the relation is single-valued

3. Entries in a column (attribute) are of the same kind (type).  

In 1NF relation the order of the tuples (rows) and attributes (columns) does not matter.

Example 1

Relation STUDENT in table 1 is not in 1NF because of multi-valued attribute STUD_PHONE. Its decomposition into 1NF has been shown in table 2.

 

Second Normal Form

 

To be in second normal form, a relation must be in first normal form and relation must not contain any partial dependency. A relation is in 2NF if it has No Partial Dependency, i.e., no non-prime attribute (attributes which are not part of any candidate key) is dependent on any proper subset of any candidate key of the table.

  

Partial Dependency – If proper subset of candidate key determines non-prime attribute, it is called partial dependency.

 

Example 1 – In relation STUDENT_COURSE given in Table 3,

 

FD set: {COURSE_NO->COURSE_NAME}

Candidate Key: {STUD_NO, COURSE_NO}

 

In FD COURSE_NO->COURSE_NAME, COURSE_NO (proper subset of candidate key) is determining COURSE_NAME (non-prime attribute). Hence, it is partial dependency and relation is not in second normal form.

 

To convert it to second normal form, we will decompose the relation STUDENT_COURSE (STUD_NO, COURSE_NO, COURSE_NAME) as :

 

STUDENT_COURSE

(STUD_NO, COURSE_NO)

COURSE (COURSE_NO, COURSE_NAME)

Third Normal Form

 

Transitive dependency – If A->B and B->C are two FDs then A->C is called transitive dependency.

 

A relation is in third normal form, if there is no transitive dependency for non-prime attributes is it is in second normal form.

A relation is in 3NF if at least one of the following condition holds in every non-trivial function dependency X –> Y

 

1.     X is a super key.

 

2.     Y is a prime attribute (each element of Y is part of some candidate key).

Example 1 – In relation STUDENT given in Table 4,

FD set: {STUD_NO -> STUD_NAME,

STUD_NO -> STUD_STATE,

STUD_NO -> STUD_COUNTRY,

STUD_NO -> STUD_AGE,

STUD_STATE -> STUD_COUNTRY}

Candidate Key: {STUD_NO}

 

For this relation in table 4,

STUD_NO -> STUD_STATE and

STUD_STATE -> STUD_COUNTRY are true. So STUD_COUNTRY is transitively dependent on STUD_NO. It violates third normal form.

To convert it in third normal form, we will decompose the relation STUDENT (STUD_NO, STUD_NAME, STUD_PHONE,STUD_STATE,STUD_COUNTRY_STUD_AGE) as:

 

STUDENT(STUD_NO,STUD_NAME,STUD_PHONE, STUD_STATE, STUD_AGE)

 

STATE_COUNTRY(STATE, COUNTRY)

Boyce-Codd Normal Form (BCNF)

 

A relation is in BCNF if and only if every determinant is a candidate key.  A relational schema R is considered to be in Boyce–Codd normal form (BCNF) if, for every one of its dependencies X → Y, one of the following conditions holds true:

•X → Y is a trivial functional dependency (i.e., Y is a subset of X)( If a functional dependency  X → Y holds, where Y is a subset of X, then it is called a trivial FD)

•X is a superkey for schema R

 

1) The relation is said to be  in BCNF if it is in 3NF.

2) Remove the functional dependency.

3) Identify the candidate key.

4) The left hand side of the every dependency is a  “candidate key”.

5) A relation is in 3NF it is almost in BCNF. 

Example

emp_id	emp_nationality	emp_dept	dept_type	dept_no_of_emp
1001	Austrian	Planning	D001	200
1001	Austrian	Stores	D001	250
1002	American	Design	D134	100
1002	American	Purchasing	D134	600

Functional dependencies in the table above:
emp_id->emp_nationality
emp_dept -> {dept_type, dept_no_of_emp}

The above functional dependency diagram “emp_id”, and  “emp_dept” are candidate keys.

Candidate key: {emp_id, emp_dept}

Emp_nationality table:

emp_id	emp_nationality
1001	Austrian
1002	American

Emp_dept table:

emp_dept	dept_type	dept_no_of_emp
Production and planning	D001	200
Stores	D001	250
design and technical support	D134	100
Purchasing department	D134	600

emp_dept_mapping table:

emp_id	emp_dept
1001	planning
1001	stores
1002	design
1002	Purchasing

 Functional Dependencies:

emp_id->emp_nationality
emp_dept -> {dept_type, dept_no_of_emp}

Candidate Keys:
For first table: emp_id
For second table: emp_dept
For third table: {emp_id, emp_dept}

This is now in BCNF as in both the functional dependencies left side  part is a key.