Unit III: Relational Database Design

Relational Model: Basic concepts, Attributes and Domains, CODD's Rules, Functional Dependencies:
Basic concepts, closure of set of functional dependencies, closure of attribute set, canonical cover,
Decomposition: lossless join decomposition and dependency preservation, The Process of
normalization, 1NF, 2NF,3NF, BCNF,4NF, 5NF.
Relational Model

Relational model represent as a table consisting of columns and rows.

Row is called as a tuple whereas column has a name or attribute.

Attributes and Domains :

Domain: It contains a set of atomic values for an attribute.

Attribute: It's a column name in a particular relation and each attribute must have a domain.

Each attribute contains a unique name in table.

Functional dependency

It is a relationship that between two attributes.

A→B
A : determinant
B : dependent

Let us Consider example :

Example of student table with attributes: Stud_Id, Stud_Name, Stud_Address.

Functional dependency can be written as:

Stud_Id→Stud_Name

Stud_Name is functionally dependent on Stud_Id.

Types of Functional dependency :

Trivial functional dependency

Non-trivial functional dependency

Trivial functional dependency

A → B has trivial functional dependency if B is a subset of A.

Consider a table with two columns Stud_Id and Stud_Name.

{Stud_id, Stud_Name} → Stud_Id is a trivial functional dependency as
Stud_Id is a subset of {Stud_Id, Stud_Name}.
Non-trivial functional dependency

A → B has a non-trivial functional dependency if B is not a subset of A.

Complete non-trivial
If A intersection B is NULL, then A → B is called as complete non-trivial.

Stud_ID → Stud_Name,
Stud_Name → Stud_DOB
closure of set of functional dependencies

When a set of functional dependencies of all possible functional dependencies from given set of functional
dependencies then it is called as closure of set of functional dependencies.

If R is the given relation and F is used to donate the set of functional dependencies then closure of set of
functional dependencies impied by F is demoted by F+.

Let's consider the set F of functional dependencies and R is relation mentioned below :
R={X,Y,Z,A,B}
FD
X->YZ,ZA->B,Y->A,B->X
Then F+ is as follow
X->YZ then X->Y,X->Z
X->Y, Y->A then X->A
ZA->B,B->X then ZA->X
Closure of an Attribute

Closure of an Attribute can be defined as a set of attributes that can be functionally determined from Closure of an
Attribute.

Closure of a set F of FDs is the set F+ of all FDs that can be collect from F
canonical cover

Let us consider scenario of database updation, In case of database updation its important to check whether the available
functional dependencies are break during the process of updation and rollback need to take place if functional dependencies are
break during the process of updation

A canonical cover is a set of functional dependencies is a simplified set of functional dependencies that has a similar closure as
the original set functional dependencies.
Decomposition: lossless join decomposition
and dependency preservation

Decomposition of relation is necessary if the relational model is not in suitable normal form and also it is used
to eliminate various problems like anomalies and inconsistencies.

Types of Decomposition :

-Lossless Decomposition : The decomposition is called lossless if the information is not lost from the
decomposed relation

- Dependency Preserving : In this type of decomposition, at least one decomposed table must satisfy all
dependency.
CODD's Rules

Dr. E.F. Codd developed Codd's Rules for a database.

1. Information Rule :
As database is used to store the information, the information in database must be stored in table format.

2. Guaranteed Access Rule :

Using table name, primary key value and attribute name data element is guaranteed to be accessible logically.

3. Systematic Treatment of NULL Values :

Null values has mltiple meanings like value is not available in cell, information is inappropriate, this rule
defines the systematic treatment of null values in database records.
4. Active Online Catalog :
Data dictionary is online catalog which is used to store the structure description of complete databse can be accessed by authorized users and
same query language is used by authorized users to access the catalog.

5. Comprehensive Data Sub-Language Rule :

To access the relational database, the language must be explicit as well as well defined syntax and supports comprehensive data definition,data
manipulation, integrity constraints. If the database allows access to data without any help of this language, then it is considered as a violation.

6. View Updating Rule :

all the views must be updatable by the system which can be updated theoretically.

7. High-Level (Insert, Update and delete) Rule :

A database should support high-level relational operations such as insert, update, and delete and also supports union, intersection and minus
operation in the database system.
8. Physical Data Independence Rule :
Data which is stored in a database must be physically independent to access the database. If physical structure of database is
changed, it will not show any effect on external applications that are accessing the data from the database.

9. Logical Data Independence :

The logical data in a database must be independent of its application, change in logical data must not affect the applications using
it.

10. Integrity Independence Rule :

To maintain integrity, All entered values should not be changed or rely on any external application.

11. Distribution Independence Rule :

If data is stored in different locations and it is used by different end-users, database that must work properly.

12. Non Subversion Rule :

If a system has other than SQL to access the database system, it should not bypass integrity to transform data.
Normalization

- It is the process of organizing the data in the database.

- Redundancy from a relation can be minimize using Normalization .

- It is also used to eliminate Insertion, Update and Deletion Anomalies.

- In th process of normalization, larger table divides into the smaller table and can be links them using relationship.

- In normalization the normal form is used to reduce redundancy from the database table.
Normalization

1NF (First Normal Form):

In 1NF each attribute must contain only a single value from its already defined domain.

Example: Relation Student is not in 1NF because of multi-valued attribute Stud_Contact.

Student table:

Stud_id Stud_Name Stud_Contact

1 ABC 123
456
2 XYZ 123

3 PQR 123
456
Normalization

The decomposition of the Student table into 1NF has been shown below:

Stud_id Stud_Name Stud_Contact

1 ABC 123
1 ABC 456
2 XYZ 123
3 PQR 123
3 PQR 456
Normalization

2NF (Second Normal Form):

Below mentioned are the conditions of 2NF
- In the 2NF, relational must be in 1NF
- All non-key attributes are fully functional dependent on the primary key attribute
- Consider below mentioned example

Stud_id Faculty_id Stud_Name Faculty_Name

1 101 Rahul SDK

2 201 Rakesh VDP
3 301 Rajesh PNM
Normalization

- In the given table Stud_id and Faculty_id are key attributes whereas Stud_Name and
Faculty_Name are Non key attributes

-To convert the given table into 2NF, we decompose it into two tables:

Stud_id Stud_Name Faculty_id Faculty_Name

101 SDK
1 Rahul 201 VDP
2 Rakesh 301 PNM
3 Rajesh
Normalization

3NF (Third Normal Form):

Below mentioned are the conditions of 2NF
- In the 2NF, relational must be in 2NF
- 3NF reduces the data duplication
- The relation is in 3NF if there is no transitive dependency for non-prime attributes
- Consider below mentioned example

Stud_id Stud_Name City Pincode

1 Rahul Pune 411017
2 Rakesh Dhule 424005
3 Rajesh Nashik 422222
Normalization

- In above example Stud_id is primary key attribute, Stud_name can be identified by Stud_id and
City can be identified by pincode as well as Stud_id. In given relation transitive dependency is
exists Stud_id->pincode->city

-To convert the given table into 3NF, we decompose it into two tables Student and Pincode :

Stud_id Stud_Na City Pincode City

me
1 Rahul Pune 411017 Pune
2 Rakesh Dhule 424005 Dhule
3 Rajesh Nashik 422222 Nashik
Normalization

Boyce-Codd Normal Form

It is an extension of Third Normal Form

For any non-trivial functional dependency, A → B, A must be a super-key.

In the , Stud_id is the super-key in the given relation of Student and pincode is the super-key in the relation Pincode. So,

Stud_id → Stud_name, pincode

and

pincode → City

Which confirms that both the relations are in BCNF.

Normalization

4NF (Fourth normal form) :

Below mentioned are the conditions for 4NF

- A relation will be in 4NF if it is in Boyce Codd normal form

- And it has no multi-valued dependency.
Normalization

5NF (Fifth normal form) :

Below mentioned are the conditions for 5NF

- A relation will be in 5NF if it is in 4NF

- In 5NF all the tables are broken into maximum possible tables in order to avoid redundancy
References :
https://dev.mysql.com
https://beginnersbook.com/
https://www.tutorialspoint.com/
https://www.javatpoint.com/

https://www.geeksforgeeks.org
www.c-sharpcorner.co
m
https://dotnettutorials.net