Module 3 Notes

Semester: 4B
Course Name: DBMS
Course Code:BCS403
Credit Points: 4
Name of the Course Instructor: Dr. Jansi Rani J
Refered BooK:
1. Fundamentals of Database Systems, Ramez Elmasri and Shamkant B. Navathe, 7th
Eclititln, 2017, Pearson
2. Database management systems, Ramakrishnan, and Gehrke, 3rd Edition, 2014,
McGraw Hill
Module 3 Topics
Normalization: Database Design Theory – (Text BOOK1 Ch 14.1 to
14.7)
 Introduction to Normalization using Functional and Multivalued
Dependencies:
 Informal design guidelines for relation schema,
 Functional Dependencies
 Normal Forms based on Primary Keys,
 Second and Third Normal Forms,
 Boyce-Codd Normal Form,
 Multivalued Dependency and Fourth Normal Form,
 Join Dependencies and Fifth Normal Form.
SQL-(Text BOOK1 Ch 16.1 to 6.5)
 SQL data definition and data types,
 Schema change statements in SQL,
 specifying constraints in SQL,
 retrieval queries in SQL,
 INSERT, DELETE, and UPDATE statements in SQL,

By Dr. Jansi Rani J, Dr. TTIT,KGF

  Additional features of SQL

 Introduction to Normalization using Functional and Multivalued

Dependencies:

1. Define Normalization:
Database normalization is a technique of organizing the data in the database.
Normalization is a systematic approach of decomposing tables
 To eliminate data redundancy and
 To eliminate undesirable characteristics like insertion, update and deletion
anomalies.
2. Explain the informal guidelines used to determine the quality of
relation schema design.
Four informal guidelines help ensure a database is efficient, meaningful,
and error-free.
 Making sure that the semantics of the attributes is clear in the schema.
 Reducing the redundant information in tuples.
 Reducing the NULL values in tuples.
 Disallowing the possibility of generating spurious tuples.

Guideline 1:
 Design a relation schema so that it is easy to explain its meaning.
 Do not combine attributes from multiple entity types and relationship types into a
single relation

i. Imparting Clear Semantics to Attributes in Relations.

 Each relation (table) should represent one entity or one relationship
only.
 Avoid mixing different entities in a single table.
 A tuple (row) should describe one real-world object clearly.
 If the conceptual design done carefully and the mapping procedure is
followed systematically, the relational schema design should have a clear
meaning.

EMP_DEPT(EmpName, DeptName, DeptManager)

Mixed different entities in a single table. Good design of table should have
single entity like
By Dr. Jansi Rani J, Dr. TTIT,KGF
 EMPLOYEE(EmpName, DeptID)

DEPARTMENT(DeptID, DeptName, Manager)

ii Redundant Information in Tuples and Update Anomalies

 minimize the storage space used by the base relations such Avoid
storing the same data repeatedly.
 Redundant data leads to update anomalies.

1. Insertion Anomaly

 Cannot insert data without other unnecessary data.

Example:

 Cannot add a new department unless an employee exists.

2. Deletion Anomaly

 Deleting a tuple causes loss of important data.

Example:

 Deleting last employee → department info lost.

3. Update Anomaly

 Same data must be updated in multiple places.

Example:

 Changing department manager requires updating all employee rows.

iii. Minimize NULL Values

 If many of the attributes do not apply to all tuples in the relation, we end up with many
NULLs,
 This can waste space at the storage level.
 May lead to problems with understanding the meaning of the attributes.
 May also lead to problems with specifying JOIN operations.
Example:
STUDENT(Name, Phone, HostelRoom)
Many students may not have hostel → NULL values
Design the table as
STUDENT(Name, Phone)
HOSTEL(StudentID, Room)
iv. Avoid Spurious Tuples:

v. Spurious tuples are unintended and erroneous rows that can be generated when two tables are
joined improperly.
By Dr. Jansi Rani J, Dr. TTIT,KGF
 vi. This typically occurs when there is an incorrect or incomplete join condition, often due to the
lack of proper primary and foreign key constraints or missing relationships between the tables.
vii. Spurious tuples can lead to incorrect query results and data anomalies.

Functional Dependencies
A functional dependency, denoted by X → Y means that the values of the Y are determined by the
values of X .
X = determinant
Y = dependent attribute
X → Y means, Value of Y depends on value of X
Ex: a. ssn→ename
b. pnumber →{pname, plocation}
c. {ssn, pnumber}→hours
 These functional dependencies specifies that
(a) the value of an employee‘s social security number (ssn) uniquely determines the employee
name (ename),
(b) the value of a project‘s number (pnumber) uniquely determines the project name (pname)
and location (plocation),
(c) combination of ssn and pnumber values uniquely determines the number of hours the
employee currently works on the project per week (hours).
Types of functional dependency
1. Full functional dependency- A functional dependency X → Y is called a
Full Functional Dependency if Y depends on the entire set of attributes in X. If
you remove even one attribute from X → then not full dependency.
Example
{Ssn, Pnumber} → Hours
if you remove:
 Ssn → Hours (not valid)
 Pnumber → Hour (not valid) both together are required.

2. Partial functional dependency- A functional dependency X → Y is called a Partial

Functional Dependency if Y depends on only a part of X, not on the whole set of
attributes.
By Dr. Jansi Rani J, Dr. TTIT,KGF
 Example
{Ssn, Pnumber} → Ename
Since Ename depends only on Ssn and not on Pnumber, this is a Partial Functional
Dependency.

3. Transitive dependency- A functional dependency X → Y is called a Transitive

Dependency if there exists another attribute Z such that:
X→Z
Z→Y
Then automatically:
X → Y (indirectly)
Dependency happens through another attribute. X determines Z, and Z determines
Y. So, X indirectly determines Y.
Transitive dependency occurs when A non-key attribute depends on another non-
key attribute
Example
 Ssn → Dnumber
 Dnumber → Dmgr_ssn

Therefore:

 Ssn → Dmgr_ssn (Transitive Dependency)

4. Trivial Dependency: A functional dependency X → Y is said to be trivial if Y is a

subset of X.
Example:
{Ssn, Ename} → Ssn
 Here, Ssn is already part of {Ssn, Ename}
So, it is a Trivial Dependency

By Dr. Jansi Rani J, Dr. TTIT,KGF

 Difference Between Types of Functional Dependency

Full Partial Transitive Trivial

Functional Functional Dependency Dependency
Dependency Dependency
Y depends on Y depends X depends Y is a subset
the entire X on part of X on Y through of X
another
attribute Z
Removing Dependency X → Z and {Ssn, Ename}

any attribute still holds Z→Y → Ssn

from X even after

breaks removing
dependency part of X
Example: Example: Example: Example:
Ssn, Ssn → {Ssn,
Pnumber} → Ssn → Dnumber → Ename} →
Hours Ename Dmgr_ssn Ssn
(from {Ssn,
Pnumber} →
Ename)
Need all Need some Indirect No new info
attributes attributes dependency

Normal Forms Based on Primary Keys

 Normalization of Relations:
Normalization is the process of organizing data in a database to Reduce
redundancy (duplicate data) Avoid anomalies (errors during operations).
By Dr. Jansi Rani J, Dr. TTIT,KGF
  Normalization was introduced by E. F. Codd (1972), He proposed:
1NF (First Normal Form), 2NF (Second Normal Form), 3NF (Third Normal Form)
BCNF (Boyce-Codd Normal Form) and Later added 4NF (Fourth Normal Form) , 5NF
(Fifth Normal Form).

 Normalization is based on Functional Dependencies (FDs) and Primary Keys.

Normalization is Data cleaning process like filtering or purification and Makes

database design Cleaner, Effient and Error-free.
Definition
The normal form of a relation is the highest level of normalization that the relation
satisfies.
Normalization is the process of organizing relations based on functional dependencies
to reduce redundancy and eliminate anomalies.

Definitions of Keys and Attributes Participating in Keys:

A Super Key is a set of one or more attributes that can uniquely identify each
row in a table.
Possible Super Keys:
{Ssn}
{Ssn, Ename}
{Ssn, Bdate}
{Ssn, Ename, Bdate}

First Normal Form:

Definition: A relation (table) is in First Normal Form (1NF) if all its attributes
contain only atomic (indivisible) values and no repeating groups..

By Dr. Jansi Rani J, Dr. TTIT,KGF

 The attribute Dlocations contains multiple values (e.g., Bellaire, Sugarland,
Houston)
This is violates 1NF rule (each field must have only single value)
To Convert to 1NF, remove the Multiple values in DLocation and make as single
value as based on Dname, Dnumber attributes
Example:

Second Normal Form (2NF)

Definition: A relation is in Second Normal Form (2NF) if:
1. It should follow the rule of 1NF, and
2. All non-key attributes are fully dependent on the whole primary key.
3. There should be no partial dependency
4. If any partial dependency appears split the table that ensure the values are
fully dependent .

By Dr. Jansi Rani J, Dr. TTIT,KGF

 Example:
Dnumber, Dlocation are composite keys and Dname and Dmgr_ssn are
Partial Dependency.

Because Dname depends only on Dnumber and Dmgr_ssn depends only on

Dnumber not depend on both Dnumber, Dlocation.
To Convert to 2NF, Split the table to remove partial dependency:

Now the above tables is in fully dependency. And 2NF.

Third Normal Form (3NF):

Definition:

By Dr. Jansi Rani J, Dr. TTIT,KGF

 A relation is in 3NF if It should satisfy the rule of 2NF, and there is no transitive
dependency.
1. Transitive dependency - A functional dependency X Y in a
relation schema R is a transitive dependency if there exists another
non key attribute Z such that:
X→Z
Z→Y
Example
EmpID DeptID DeptName
C001 4 CSE
C002 4 CSE
E001 2 ECE
M002 1 MECH
EmpID → DeptID → DeptName
The table have transitive dependency, to make direct dependency should split the
table as
EMPLOYEE
| EmpID | DeptID |
DEPARTMENT
| DeptID | DeptName
EmpID DeptID
C001 04
E001 03
M002 01
|
DeptID DeptName
04 CSE
03 ECE
01 MECH
By Dr. Jansi Rani J, Dr. TTIT,KGF
 Boyce-Codd Normal Form (BCNF):
Definition:
 A relation is in BCNF if:
For every functional dependency X → Y, X must be a super key.

 A super key is a set of one or more attributes that can uniquely identify each row in a table.
 A super key may contain extra attributes.
 Stronger version of 3NF
Removes anomalies not handled by 3NF
Example
Student Course Instructor

John DBMS Jansi Rani

David ADA Ashwini

James Micro Controller Ashwini Priyanka

Course → Instructor
Student + Course → Instructor
Course is not a key → violates BCNF
Split into
COURSE(Course, Instructor)
Course Instructor

DBMS Jansi Rani

ADA Ashwini

Micro Controller Ashwini Priyanka

ENROLLMENT(Student, Course)
Student Course

By Dr. Jansi Rani J, Dr. TTIT,KGF

 Student Hobby Language
John Cricket English
John Cricket Hindi
John Music English
John Music Hindi
John DBMS

David ADA

James Micro Controller

Fourth Normal Form (4NF):

A relation is in 4NF if it should satisfy the rule of BCNF and It has no multivalued
dependency (MVD).
Multivalued Dependency means One attribute determines multiple independent values
of another attribute.
Example of 4NF
Table: STUDENT

 John can have many hobbies

 John can know many languages

By Dr. Jansi Rani J, Dr. TTIT,KGF

  Hobby and Language are independent
 In the above table student attributes determines multiple independent values
such as Student↠Hobby Student ↠ Language
 So the table is not in 4NF.
Convert to 4NF

STUDENT_HOBBY
Stude Hobb
nt y

Cricke
John
t

John Music

STUDENT_LANGUAGE
Student Language
John English
John Hindi

Now the both tables are in 4NF.

Fifth Normal Form (5NF)
A relation is in 5NF if it should satisfy the rule of 4NF and It
cannot be decomposed further without losing information. It should not have join
dependency.
Example of 5NF
Table: SUPPLIER_PART_PROJECT

Supplier Part Project

S1 P1 J1
S1 P2 J1
S2 P1 J1
By Dr. Jansi Rani J, Dr. TTIT,KGF
` This table Supplier Project
describing about S1 J1
 Supplier S2 J1 supplies
the Parts
 Parts are used in Projects
 Supplier works on Projects
Sometimes redundancy exists because all information can be obtained
from smaller tables.
Convert to 5NF
Decompose into 3 Tables
SUPPLIER_PART

Supplier Part
S1 P1
S1 P2
S2 P1
SUPPLIER_PROJECT

PART_PROJECT

Part Project
P1 J1
P2 J1

By Dr. Jansi Rani J, Dr. TTIT,KGF

 We can join them back and get the original table correctly.

Difference Between 1NF, 2NF, 3NF, BCNF, 4NF and 5NF

Normal
Rule Removes
Form
No repeating groups
Repeating
1NF or multivalued
data
attributes
Must be in 1NF and
Partial
2NF no partial
dependency
dependency
Must be in 2NF and
Transitive
3NF no transitive
dependency
dependency
Every determinant
Dependency
BCNF must be a candidate
anomalies
key
Must be in BCNF
Multivalued
4NF and no multivalued
dependency
dependency
Must be in 4NF and Join
5NF
no join dependency dependency

By Dr. Jansi Rani J, Dr. TTIT,KGF

 Consider the order_product database and normalize the tableby 1NF, 2NF,
3NF, 4NF and 5 NF.
Order Customer Customer Product Product Supplier Supplier Quantity Project
ID Name City ID Name ID Name ID

O1 Ravi Chennai P1 Keyboard S1 Dell 2 J1

O1 Ravi Chennai P2 Mouse S2 HP 1 J1
O2 Anu Madurai P1 Keyboard S1 Dell 3 J2
O2 Anu Madurai P2 Monitor S3 Lenovo 1 J2

Functional Dependencies
OrderID →CustomerName , CustomerCityProductID → ProductName , SupplierID

SupplierID → SupplierName(OrderID , ProductID)→ Quantity , ProjectID

Candidate Key:
( OrderID , ProductID )
1NF (First Normal Form)
Rule
 No repeating groups
 Atomic values only
The given table already contains:
 single values
 no multivalued attributes
So it is already in 1NF.

By Dr. Jansi Rani J, Dr. TTIT,KGF

 OC C P P S S Q P
ruu r r u
I uur OrderID Customer Name Customer City
ORCP KS D2J
ORCP
1MeS 1He 1J O1 Ravi Chennai
OAM2 o S2 D
P K P 3J
OAMP
1e 1L
M S e 1J O2 Anu Madurai
2o 3e

2NF (Second Normal Form)

Rule
 Must be in 1NF
 No partial dependency
Compositekey is:
(OrderID, ProductID)
Partial Dependencies
OrderID →CustomerName , CustomerCityProductID → ProductName , SupplierID

These depend only on part of the composite key.

Convert into 2NF
Split the table
CUSTOMER_ORDER

PRODUCT
Product
ProductID SupplierID
Name
P1 Keyboard S1
P2 Mouse S2
By Dr. Jansi Rani J, Dr. TTIT,KGF
 Product
ProductID SupplierID
Name

P3 Monitor S3

ORDER_ITEM

OrderID ProductID Quantity ProjectID

O1 P1 2 J1
O1 P2 1 J1
O2 P1 3 J2
O2 P3 1 J2
SUPPLIER
SupplierID SupplierName
S1 Dell
S2 HP
S3 Lenovo
3NF (Third Normal Form)
Rule
 Must be in 2NF
 No transitive dependency

SupplierID → SupplierName Already removed into SUPPLIER table.

So the relations are already in 3NF.

BCNF (Boyce-Codd Normal Form)

Rule
For every FD:
X →Y

By Dr. Jansi Rani J, Dr. TTIT,KGF

 X must be a super key.

Check the Relations of the value

In CUSTOMER_ORDER table
OrderID →CustomerName , CustomerCity
Order ID is super Key

In PRODUCT table
ProductID → ProductName , SupplierID
ProductID is key
SUPPLIER
SupplierID → SupplierName
SupplierID is key
ORDER_ITEM
(OrderID , ProductID)→ Quantity , ProjectID
Composite key determines all
So all relations are in BCNF.
4NF (Fourth Normal Form)
Rule
 Must be in BCNF
 No multivalued dependency
Assume:
 A supplier can supply many products independently
 A project can use many products independently
Possible multivalued dependencies:
SupplierID ↠ ProductID ProjectID ↠ ProductID

Decomposition into 4NF

SUPPLIER_PRODUCT

SupplierID ProductID
S1 P1
By Dr. Jansi Rani J, Dr. TTIT,KGF S2 P2
S3 P3
 PROJECT_PRODUCT

ProjectID ProductID
J1 P1
J1 P2
J2 P1
J2 P3
ORDER_ITEM
OrderID ProductID Quantity
O1 P1 2
O1 P2 1
O2 P1 3
O2 P3 1
Other tables remain same.
Now multivalued dependencies are removed.
5NF (Fifth Normal Form / PJNF)
Rule
 Must be in 4NF
 Remove join dependencies
 No further lossless decomposition possible
Suppose relationship exists among:
 Supplier
 Product
 Project
Then decompose into:
By Dr. Jansi Rani J, Dr. TTIT,KGF
 SupplierID ProductID
ProductID ProjectID

| SupplierID | ProjectID |

These relations can be joined losslessly to reconstruct the original relation.

No redundancy remains in the above table
Hence relation is in 5NF (PJNF).

SQL:
SQL Data Definition and Data Type

Data Definition Language (DDL) in SQL is used to define and manage the
structure of a database.
Data Type
SQL data types specify the kind of data that can be stored in a column.
. Numeric Data Types
The Data consist of numeric value can be represent as following
 INT – Integer values
Example:
CREATE TABLE Student (Age INT);
 FLOAT / REAL – Floating-point numbers
Example:
INSERT INTO Student VALUES (102, 76.25);
 DECIMAL(p, s) – Fixed precision numbers (e.g., money).
Example:
o CREATE TABLE Product (ProductID INT,Price DECIMAL(6,2));
Character/String Data Types

By Dr. Jansi Rani J, Dr. TTIT,KGF

 The Data consist of character value can be represent as following
CHAR(n) – Fixed-length string
Example: CREATE TABLE Student (RollNo INT, Gender CHAR(1));
VARCHAR(n) – Variable-length string
Example: CREATE TABLE Employee (Name VARCHAR(50));
TEXT – used to store large amounts of text as Addresses,
Comments
Descriptions, Articles, Notes.
Example: CREATE TABLE Student (RollNo INT, Name VARCHAR(50),
Address TEXT);

BIT-STRING

 The data types of fixed length n is BIT(n).

 The varying length is BIT VARYING(n), where n is the maximum
number of bits.

 Literal bit strings are placed between single quotes but preceded by a B to
distinguish them from character strings; for example, B‗10101‘.

It is commonly used for Flags, True/False values, Binary operations, Permission

settings.

Example: CREATE TABLE Light (Switch BIT);

BOOLEAN

 The data type has the traditional values of TRUE or FALSE. In SQL, because
of the presence of NULL values, a three-valued logic is used, so a third

possible value for a Boolean data type is UNKNOWN.

Example: CREATE TABLE Employee (EmpID INT, Active BOOLEAN);

DATE

 The data type has ten positions, and its components are YEAR, MONTH,
and DAY in the form YYYY-MM-DD.
By Dr. Jansi Rani J, Dr. TTIT,KGF
  The TIME data type has at least eight positions, with the components
HOUR, MINUTE, and SECOND in the form HH:MM:SS. for example,

Example: CREATE TABLE Student (RollNo INT, Name VARCHAR(50), DOB DATE);

TIMESTAMP

 The data type (TIMESTAMP) includes the DATE and TIME fields,
 Example: CREATE TABLE ClassSchedule (Subject VARCHAR(50), StartTime
TIME);

Basic Retrieval Queries in SQL

SELECT Clause:

 This clause is used to specify the columns that you want to retrieve from
the database. It can also include expressions, functions, and even
subqueries.
 Example: SELECT Name, Age
FROM Student;

FROM Clause:

 This clause specifies the table(s) from which to retrieve the data. It can
also include joins to combine data from multiple tables.
 Syntax: SELECT column_name
FROM table_name;
 SELECT Name, Age
FROM Student;

WHERE Clause:

 This clause is used to filter records that meet certain conditions. It can
include various operators and conditions.
 Example: SELECT Name FROM Student WHERE Age > 15;

By Dr. Jansi Rani J, Dr. TTIT,KGF

 Unspecified WHERE Clause and Use of the Asterisk

 SQL returns all rows from the table

 SELECT *
FROM Student
WHERE Age > 15;

Substring Pattern Matching and Arithmetic Operators

Substring pattern matching is used to search for specific patterns in text

values.

SQL uses the LIKE operator with special symbols:

Example: SELECT * FROM Student WHERE Name LIKE 'R%';

GROUP BY Clause:

 This clause groups rows that have the same values in specified columns
into summary rows. It is often used with aggregate functions like
COUNT, SUM, AVG, etc.

Example: SELECT Department, COUNT(*)

FROM Employee

GROUP BY Department;

HAVING Clause:

 This clause is used to filter groups created by the GROUP BY clause. It

operates like the WHERE clause but is applied after the grouping.
 Example: SELECT Department, COUNT(*)
FROM Employee
GROUP BY Department
HAVING COUNT(*) > 5;

ORDER BY Clause:

By Dr. Jansi Rani J, Dr. TTIT,KGF

  This clause is used to sort the result set by one or more columns. It can
sort the data in ascending (ASC) or descending (DESC) order.
 Example: SELECT *
FROM Student
ORDER BY Name ASC;

Unspecified WHERE Clause and Use of the Asterisk

Types of constraints in SQL

In SQL, constraints are rules enforced on data columns in a table to

ensure data integrity and reliability. The different types of constraints in SQL:
1. NOT NULL Constraint: Ensures that a column cannot have a NULL value.
CREATE TABLE Students (
StudentID int NOT NULL,
Name varchar(255) NOT NULL
);

2. UNIQUE Constraint: Ensures that all the values in a column are different.
CREATE TABLE Students (
StudentID int NOT NULL,
Email varchar(255) UNIQUE
);

3. PRIMARY KEY Constraint: Uniquely identifies each record in a

table. It is a combination of NOT NULL and UNIQUE.
CREATE TABLE Students (
StudentID int PRIMARY KEY,
Name varchar(255) NOT NULL

4. FOREIGN KEY Constraint: Prevents actions that would destroy

links between tables. It

By Dr. Jansi Rani J, Dr. TTIT,KGF

 CREATE TABLE Enrollments (
EnrollmentID int PRIMARY KEY,
StudentID int,
CourseID int,
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

5. CHECK Constraint: Ensures that the value in a column meets a

specific condition.
CREATE TABLE Students (
StudentID int PRIMARY KEY,
Name varchar(255) NOT NULL,
Age int,
CHECK (Age >= 18)
);

6. DEFAULT Constraint: Provides a default value for a column

when none is specified.
CREATE TABLE Students (
StudentID int PRIMARY KEY,
Name varchar(255) NOT NULL,
EnrollmentDate date DEFAULT GETDATE()
);

By Dr. Jansi Rani J, Dr. TTIT,KGF