Relational Database Design

Unit 5
Relational Database
A relational database is a collection of data items with pre-defined
relationships between them. These items are organized as a set of tables with
columns and rows.

Tables are used to hold information about the objects to be represented in

the database.

Each column in a table holds a certain kind of data and a field stores the
actual value of an attribute
RDBMS (relational database management system)
A relational database management system (RDBMS) is a collection of
programs and capabilities that enable IT teams and others to create, update,
administer and otherwise interact with a relational database.

RDBMS store data in the form of tables, with most commercial relational
database management systems using Structured Query Language (SQL) to
access the database.
Design Goals
● Avoid redundant data
● Ensure that relationship among attributes are represented
● Facilitate the checking of updates for violation of database integrity
constraints.
Pitfalls in Relational Database Design
Relational database design requires that we find a 􏰁”good” collection of
relation schemas. A bad design may lead to

● Repetition of Information.
● Inability to represent certain information.
Example
Consider the relation schema:
Lending-schema = (branch-name, branch-city, assests, customer-name, loan-number,
amount)
Redundancy:
Data for branch-name, branch-city, assets are repeated for each loan that a branch makes.
Wastes space
Complicates updating, introducing possibility of inconsistency of assets value
Null Values:
Another problem with the Lending-schema design is that we cannot represent directly the
information concerning a branch (branch-name, branch-city, assets) unless there exists at
least one loan at the branch. This is because tuples in the lending relation require values for
loan- number, amount, and customer-name.
One solution to this problem is to introduce null values, however, null values are difficult to
handle. If we are not willing to deal with null values, then we can create the branch
information only when the first loan application at that branch is made. Worse, we would
have to delete this information when all the loans have been paid.
Anomalies
Database anomaly is a flaw in databases because of poor planning and
storing everything in a flat database.
Anomalies occur when there is too much redundancy in the database.
Poor table design has related data scattered over various tables.
Any new change in the database should be updated in many places.
It is also possible that the information is only half present. It's there in one
table, but missing in another one.
In other words, in Database Management System (DBMS), anomaly means
the inconsistency occurred in the relational table during the operations
performed on the relational table.
How are Anomalies Caused in DBMS?
We have already seen what anomalies are in DBMS.
Now let us discuss how anomalies are caused in DBMS. When designing any
database, it is necessary to see the independent entities.
A database anomaly is a flaw that frequently results from poor planning and
storing everything in a flat database.
The normalisation process, which entails joining and splitting of tables,
typically removes this.
The normalisation process goal is to reduce the negative effects of creating
tables that would cause DB anomalies.
Example
Assume manufacturing company stores employee details in a table called as
an employee having four attributes:

● Emp_id for employee's id.

● Emp_name for employee's name.
● Emp_address for employee's address.
● Emp_dept for the department details in which the employee works.
The table will look like this. The table given below is not normalized. We will
see how problems arise when a table is not normalized.
Types of Anomalies in DBMS
Insert Anomaly: The term "insertion anomaly" is used to describe when a
new row is added to a table and it causes an inconsistency.

Update Anomaly: If there are some changes in the database, we have to

apply that change in all the rows. And if we miss any row, we will have one
more field, creating an update anomaly in the database.

Delete Anomaly: The term "deletion anomaly in the database" is used when
we delete some rows from a table and any necessary additional information
or data is also lost from the database.
Insert anomaly
If a tuple is inserted in referencing relation and referencing attribute value is
not present in referenced attribute, it will not allow inserting in referencing
relation.
Example
Assume that a new employee is joining the company under training and not
assigned to any department. Then, we would not insert the data into the
table if the emp_dept field doesn't allow nulls.
Update anomaly

If a tuple is updated from referenced relation and the referenced attribute value is
used by referencing attribute in referencing relation. In that case, it will not allow
updating the tuple from referenced relation.

Example

In the given table, we have two rows for an employee named Rick, and he belongs
to two different departments of the company. If we need to update Rick's address,
we must update the same address in two rows. Otherwise, the data will become
inconsistent.

If, in some way, we can update the correct address in one department but not the
other, then according to the database, Rick will have two different addresses,
which is not correct and would lead to inconsistent data.
Delete anomaly

If a tuple is deleted from referenced relation and the referenced attribute

value is used by referencing attribute in referencing relation, it will not allow
deleting the tuple from referenced relation.

Example

Assume that if the company closes the department D890, then deleting the
rows that have emp_dept as D890 would also delete the information of
employee Maggie since she is assigned only to this department.
Removal of Anomalies
To prevent anomalies, we need to normalize the database by efficiently
organizing the data in a database. Normalization is a systematic approach to
eliminate data redundancy and Insertion, Modification, and Deletion
Anomalies by decomposing tables.
Functional Dependencies
Functional dependencies (FD) are are type of constraint that is based on keys.
A functional dependency (FD) is a relationship between two attributes,
typically between the PK and other non-key attributes within a table.
For any relation R, attribute Y is functionally dependent on attribute X (usually
the PK), if for every valid instance of X, that value of X uniquely determines the
value of Y. This relationship is indicated by the representation below :
X ———–> Y
The left side of the above FD diagram is called the determinant, and the right
side is the dependent. Here are a few examples.
In the first example, below, SIN determines Name, Address and Birthdate. Given SIN,
we can determine any of the other attributes within the table.
SIN ———-> Name, Address, Birthdate

For the second example, SIN and Course determine the date completed
(DateCompleted). This must also work for a composite PK.
SIN, Course ———> DateCompleted
The third example indicates that ISBN determines Title.

ISBN ———–> Title

Example
From the above table we can conclude some valid functional
dependencies:
roll_no → { name, dept_name, dept_building },→ Here, roll_no can determine values
of fields name, dept_name and dept_building, hence a valid Functional dependency
roll_no → dept_name , Since, roll_no can determine whole set of {name, dept_name,
dept_building}, it can determine its subset dept_name also.
dept_name → dept_building , Dept_name can identify the dept_building accurately,
since departments with different dept_name will also have a different dept_building
More valid functional dependencies: roll_no → name, {roll_no, name} ⇢ {dept_name,
dept_building}, etc.
Here are some invalid functional dependencies:
name → dept_name Students with the same name can have different
dept_name, hence this is not a valid functional dependency.

dept_building → dept_name There can be multiple departments in the same

building. Example, in the above table departments ME and EC are in the same
building B2, hence dept_building → dept_name is an invalid functional
dependency.

More invalid functional dependencies: name → roll_no, {name, dept_name} →

roll_no, dept_building → roll_no, etc.
How to Denote a Functional Dependency
An "arrow" represents a functional dependency. A B symbolizes the functional dependence of A on B.

An example is a relation with the four attributes A, B, C, and D.

● R (ABCD)
● A → BCD
● B → CD

Attributes B, C, and D are functionally reliant on attribute A in the first functional dependency

A->BCD

Function dependency B->CD two qualities, C and D depend on attribute B to function.

Types of Functional Dependencies in DBMS
● Trivial Functional Dependency
● Non-Trivial Functional Dependency
● Multivalued functional Dependency
● Transitive functional Dependency
Trivial Functional Dependency
In Trivial Functional Dependency, a dependent is always a subset of the determinant. i.e.
If X → Y and Y is the subset of X, then it is called trivial functional dependency

Example:

● Here, {roll_no, name} → name is a trivial functional

dependency, since the dependent name is a subset of
determinant set {roll_no, name}.
● Similarly, roll_no → roll_no is also an example of trivial
functional dependency.
Non-trivial Functional Dependency
In Non-trivial functional dependency, the dependent is strictly not a subset of
the determinant. i.e. If X → Y and Y is not a subset of X, then it is called
Non-trivial functional dependency.

Example:

● Here, roll_no → name is a non-trivial

functional dependency, since the dependent
name is not a subset of determinant roll_no.
● Similarly, {roll_no, name} → age is also a
non-trivial functional dependency, since age
is not a subset of {roll_no, name}
Multivalued Functional Dependency
In Multivalued functional dependency, entities of the dependent set are not
dependent on each other. i.e. If a → {b, c} and there exists no functional
dependency between b and c, then it is called a multivalued functional
dependency.

For example,

● Here, roll_no → {name, age} is a

multivalued functional dependency,
since the dependents name & age are
not dependent on each other(i.e.
name → age or age → name doesn’t
exist !)
Transitive Functional Dependency
In transitive functional dependency, dependent is indirectly dependent on
determinant. i.e. If a → b & b → c, then according to axiom of transitivity, a →
c. This is a transitive functional dependency.
 For example,
Here, enrol_no → dept and dept → building_no.

Hence, according to the axiom of transitivity,

enrol_no → building_no is a valid functional
dependency.

This is an indirect functional dependency, hence

called Transitive functional dependency.
Normalization
Normalization is the process to eliminate data redundancy and enhance data
integrity in the table.
Normalization also helps to organize the data in the database.
It is a multi-step process that sets the data into tabular form and removes the
duplicated data from the relational tables.
Normalization organizes the columns and tables of a database to ensure that
database integrity constraints properly execute their dependencies.
It is a systematic technique of decomposing tables to eliminate data
redundancy (repetition) and undesirable characteristics like Insertion, Update,
and Deletion anomalies.
Database Normal Forms
Here is a list of Normal Forms in SQL:

● 1NF (First Normal Form)

● 2NF (Second Normal Form)
● 3NF (Third Normal Form)
● BCNF (Boyce-Codd Normal Form)
● Fourth Normal Form (4NF)
● Fifth Normal Form (5NF)
Summary:
1NF: Remove repeating groups; atomic values.
2NF: Remove partial dependencies.
3NF: Remove transitive dependencies.
BCNF: Superkeys should determine every dependency.
4NF: No multi-valued dependencies.
5NF: No join dependencies.
1st Normal Form (1NF)
Definition:
● A table is in First Normal Form (1NF) if:
○ All attributes (columns) contain atomic, indivisible values.
○ Each record in the table is unique and identified by a primary key.
○ There are no repeating groups or arrays in any column.
Problem:

The column PhoneNumbers contains multiple values, violating the atomic rule
of 1NF.
Solution (1NF Applied):
We split the data into separate rows:

EmployeeID EmployeeName PhoneNumber

101 Alice 9876543210
101 Alice 9856420132
102 Bob 9812345678
Second Normal Form (2NF)
A table is in Second Normal Form (2NF) if:

● It is in 1NF.
● There is no partial dependency of any column on the primary key, i.e.,
non-prime attributes must be fully dependent on the whole primary key.
● This rule applies when the table has a composite primary key (a key
composed of more than one column).
Partial dependency occurs when a non-prime attribute is dependent on only a part (subset)
of a composite primary key, rather than the whole primary key.

Key Concepts to Understand Partial Dependency:

Composite Primary Key: A primary key that consists of two or more attributes (columns). For
example, if a table has a primary key as {A, B}, both A and B together uniquely identify the
rows in the table.

Prime Attribute: An attribute that is part of the primary key is called a prime attribute.

Non-Prime Attribute: An attribute that is not part of the primary key is referred to as a
non-prime attribute.

What is Partial Dependency?

A partial dependency occurs when:

A non-prime attribute (which is not part of the primary key) depends only on a part of the
composite key rather than the entire composite key.
Example:

Consider the table below that is in 1NF:

ProjectID ProjectName EmployeeID EmployeeName

1 Website 101 Alice
2 MobileApp 101 Alice
3 Website 102 Bob

Problem:
Here, EmployeeName is partially dependent on EmployeeID, not on ProjectID.
Solution:
We divide the table into two:

Project Table:

ProjectID ProjectName
1 Website
2 MobileApp

Employee Table:

EmployeeID EmployeeName
101 Alice
102 Bob
Third Normal Form (3NF)
A table is in Third Normal Form (3NF) if:

● It is in Second Normal Form (2NF).

● There are no transitive dependencies, which means that non-prime
attributes (attributes that are not part of any candidate key) should not
depend on other non-prime attributes.

Remember: In simpler terms, every non-key column must depend only on the
primary key, and not on any other non-key column.
A transitive dependency occurs when a non-prime attribute depends on
another non-prime attribute, which in turn depends on the primary key. This
type of dependency is undesirable in a well-normalized database.
Key Concepts to Understand Transitive Dependency:
● Primary Key: The attribute (or set of attributes) that uniquely identifies
each record in a table.
● Prime Attribute: An attribute that is part of the primary key.
● Non-Prime Attribute: An attribute that is not part of the primary key.
● Transitive Dependency: A dependency in which one non-prime attribute
depends on another non-prime attribute, and that non-prime attribute is
dependent on the primary key.
A transitive dependency occurs when:

A non-prime attribute is dependent on another non-prime attribute rather

than being directly dependent on the primary key.
Consider a table of employees and their departments:

Emp_ID Emp_Name Dept_ID Dept_Name Dept_Location

101 John D01 HR New York
102 Jane D02 IT San Francisco
103 Mark D01 HR New York

● Primary Key: Emp_ID

● Non-Prime Attributes: Emp_Name, Dept_ID, Dept_Name, Dept_Location
In this case:
● Dept_Name and Dept_Location depend on Dept_ID, not directly on the
primary key Emp_ID.
● Dept_ID depends on the primary key Emp_ID, and through this
dependency, both Dept_Name and Dept_Location are indirectly
dependent on Emp_ID.
● This indirect dependency between the primary key (Emp_ID) and
non-prime attributes (Dept_Name, Dept_Location) via Dept_ID is a
transitive dependency.
Example:

EmployeeID EmployeeName DepartmentID DepartmentName

101 Alice 10 IT
102 Bob 20 HR

Problem:
DepartmentName depends on DepartmentID, which is not the primary key.
There is a transitive dependency between DepartmentID and
DepartmentName.
Analysis:
● Primary Key: EmployeeID (since it uniquely identifies each record).
● Non-prime attributes: EmployeeName, DepartmentID, DepartmentName.
Problem:
● The attribute DepartmentName depends on DepartmentID, which is not a
part of the primary key. This is a transitive dependency, meaning
DepartmentName depends on DepartmentID, and DepartmentID
depends on EmployeeID. This violates 3NF.
Solution (3NF Applied):
We split the table into two:

Employee Table
EmployeeID EmployeeName DepartmentID
101 Alice 10
102 Bob 20

Department Table
DepartmentID DepartmentName
10 IT
20 HR
Both functional and transitive dependency seem same but
To remove functional dependencies, we must split the data into multiple
tables, each dealing with independent entities so that no attribute is
dependent on another attribute that is not part of the primary key.
The table already satisfies 1NF(Normalize it further)
Student_ID Student_Name Course_IDCourse_Name Instructor_ID Instructor_Name
S101 Alice C01 Math I001 Prof. Smith
S102 Bob C02 Physics I002 Prof. Johnson
S101 Alice C02 Physics I002 Prof. Johnson
S103 Carol C01 Math I001 Prof. Smith
S104 Dave C03 Chemistry I003 Prof. Brown
Boyce-Codd Normal Form (BCNF)
Definition:
● A table is in Boyce-Codd Normal Form (BCNF) if:
● It is in Third Normal Form (3NF).
● For every functional dependency (X → Y), X must be a superkey.
● In simpler terms, in BCNF, all the determinants (the left-hand side of a
functional dependency) must be superkeys. A superkey is an attribute or
set of attributes that can uniquely identify a row in a table.
BCNF is a stricter version of 3NF. A table can be in 3NF but not in BCNF when
there are dependencies where a non-superkey functionally determines
another attribute.
Practise
Our table is in 1NF
2NF
Cusotmer Table
Rental Table
Proprty_Owner Table
In 3NF
Cusotmer Table
Rental Table
Property_for_rent table
Owner relation
StudentNam StudentPhone DeptNo DeptName AdvisorName AdvisorPhone AdvisorDept Term
e
StudentNam StudentPhone DeptNo DeptName AdvisorName AdvisorPhone AdvisorDept Term
e
Decomposition
● Decomposition is the process of breaking down in parts or elements.
● It replaces a relation with a collection of smaller relations.
● It breaks the table into multiple tables in a database.
● Careless decomposition, however, may lead to another form of bad
design.
● Careless decomposition may lead to the loss of information and such
decomposition is called lossy- decomposition, or a lossy-join
decomposition. Lossy-decomposition is a bad database design.
● A decomposition that is not a lossy-join decomposition is a lossless-join
decomposition.
● When we decompose a relation into a number of smaller relations, it is
crucial that the decomposition be lossless.
Properties of Decomposition-
Following are the properties of Decomposition,

1. Lossless Decomposition

2. Dependency Preservation

3. Lack of Data Redundancy

Loss Less Decomposition
If the information is not lost from the relation that is decomposed, then the
decomposition will be lossless.

The lossless decomposition guarantees that the join of relations will result in
the same relation as it was decomposed.

The relation is said to be lossless decomposition if natural joins of all the

decomposition give the original relation.
For example,
Consider EMPLOYEE_DEPARTMENT table,
Now, we will decompose this into two relations – EMPLOYEE and
DEPARTMENT.

EMPLOYEE relation –
DEPARTMENT table –
Now, if you perform natural JOIN operation on EMPLOYEE and DEPARTMENT,
resultant will be –
Which is same as EMPLOYEE_DEPARTMENT. So, it’s Loss Less Join
Decomposition.
The decomposition is lossless when it satisfies the following statement −

● If we union the sub Relation R1 and R2 then it must contain all the
attributes that are available in the original relation R before
decomposition.
● Intersections of R1 and R2 cannot be Null. The sub relation must contain a
common attribute. The common attribute must contain unique data.
Example:
Here,

R = (A, B, C)

R1 = (A, B)

R2 = (B, C)

The relation R has three attributes A, B, and C. The relation R is decomposed into two
relation R1 and R2. . R1 and R2 both have 2-2 attributes. The common attributes are B.

The Value in Column B must be unique. if it contains a duplicate value then the Lossless-join
decomposition is not possible.
Example
Draw a table of Relation R with Raw Data −
Now, we can check the first condition for Lossless-join decomposition.
The union of sub relation R1 and R2 is the same as relation R.
R1U R2 = R
We get the following result −
Dependency Preserving
In dependency preserving, at-least one decomposed table must satisfy every
dependency.

If a relation R is decomposed into R1 and R2, then, Either R1 or R2 must satisfy

all dependency. or, combination of functional dependencies of R1 and R2
must derive dependencies of R1.
For example,

Let’s assume there is a relation R (A, B, C, D) with functional dependency set

(A->BC). The relation R is decomposed into R1(ABC) and R2(AD).

This is dependency preserving because FD A->BC is a part of relation R1(ABC)