UNIT 1

Database Concepts: A Relational approach: Database–Relationships–DBMS

Relational Data Model–Integrity Rules–Theoretical Relational Languages.
Database Design: Data Modeling and Normalization: Data Modeling–
Dependency–Database Design– Normal forms– Dependency Diagrams-
Renormalizations–Another Example of Normalization.

DATABASE CONCEPTS: A RELATIONAL APPROACH

Basic Terms Used to describe the Structure of a Database:
 A person, place, event, or item is called an entity.
 The facts describing an entity are known as data.
 For example, if you were a registrar in a college, you would like to
have all the information about the students. Each student is an
entity in such a scenario.
 Each entity can be described by its characteristics, which are known as
attributes.
 For example, some of the likely attributes for a college student are
student identification number, last name, first name, phone number,
Social Security number, gender, birthdate, and so on.
 All the related entities are collected together to form an entity set.
 An entity set is given a singular name.
 For example, the STUDENT entity set con tains data about
students only. All related entities in the STUDENT entity set are
students.
 A database is a collection of entity sets.
 For example, a college’s database may include information about
entities such as student,faculty,course,term, course section,
building, registration information, and so on
 The entities in a database are likely to interact with other entities.
 The inter actions between the entity sets are called relationships.
 The interactions are described using active verbs.
 For example, a student takes a course section (CRSSECTION), so
the relationship between STUDENT and CRSSEC TION is takes.
A faculty member teaches in a building, so the relationship
between FACULTY and BUILDING is teaches
RELATIONSHIP
A relationship in a database defines how two entity sets (tables) are connected.
Types of Relationships
1. One-to-One (1:1)
 Each record in one entity is related to only one record in another entity,
and vice versa.
 Examples:
o One department has one chairperson.
o One employee manages one department.
Department ─── Chairperson
1 1
2. One-to-Many (1:M)
 One record in the first entity is related to many records in the second
entity.
 Each record in the second entity is related to only one record in the first
entity.
 Examples:
o One department has many employees.
o One division has many faculty members.
Department ───< Employee
1 Many
3. Many-to-Many (M:N or M:M)
 Many records in one entity are related to many records in another entity.
 Examples:
o Students take many courses, and courses have many students.
o Employees work on many projects, and projects have many
employees.
Student >───< Course
Many Many
RELATIONAL DATABASE MANAGEMENT SYSTEM
RDBMS (Relational Database Management System) is a DBMS based
on the relational model. It stores data in tables (relations) and manages both
data and the relationships between tables.
Main Functions of an RDBMS:
1. Data Management – Stores and manages data and relationships between
tables.
2. Data Dictionary – Automatically creates metadata (table names,
attributes, data types, relationships, storage details, etc.).
3. Transaction Management – Handles daily database transactions
efficiently.
4. Data Independence – Allows applications to work without knowing the
physical storage details of data.
5. Query Processing – Converts user requests into operations on physical
data and displays results in a readable form.
6. Validation Rules – Enforces data accuracy by restricting invalid values
(e.g., Gender = M or F only).
7. Security – Protects data using passwords, encryption, and user access
permissions.
8. Backup and Recovery – Provides facilities to back up data and restore it
after failures.
9. Data Sharing – Supports multiple users with data-locking mechanisms to
prevent conflicts.
[Link] and Export – Exchanges data with other databases and
spreadsheet applications.
[Link] Joining – Combines data from multiple tables, reducing
redundancy and improving data integrity.
Advantages of RDBMS:
 Reduces data redundancy.
 Improves data integrity and consistency.
 Minimizes data-entry errors.
  Provides efficient and secure data management.
 Supports easy retrieval and sharing of data.
RELATIONAL DATABASE MODEL
1. Introduction
 Data can be represented using different database models:
o Relational Model
o Hierarchical Model
o Network Model
o Object Model
 The Relational Database Model is the most widely used because it is:
o Simple to design
o Easy to understand
o Easy to retrieve and manage data

2. Relational Database Model

 Developed by E. F. Codd in 1970.
 Based on Mathematical Set Theory.
 Uses a relation as the basic building block.
 A relation is represented as a table.
Features
 Data is stored in two-dimensional tables.
 Users work with the logical structure rather than physical storage.
 Easy to establish relationships between tables.

3. Relational Database Management System (RDBMS)

An RDBMS is software that manages relational databases.
Examples
 Oracle9i
  Microsoft Access 2000
 Microsoft Visual FoxPro 6.0

4. Table (Relation)
A table consists of rows and columns.
 Row → Represents an entity (record)
 Column → Represents an attribute (field)
Example:
ProjNo Location Customer
1 Miami Stocks
2 Orlando Allen
3 Trenton Smith

5. Important Terminology
Entity Set
A collection of similar entities.
Example:
 EMPLOYEE
 PARTS
 DEPARTMENT

Entity
A single row in a table.
Example:
EmpNo Name
101 Carter
This row represents one employee.
Attribute
A column in a table.
Example:
 EmpNo
 Name
 Salary
 DeptNo

Tuple
Another name for a row in a relational table.
Example:
|101|Carter|25000|
is one tuple.

Degree of a Relation
The number of columns in a table.
Example:
| EmpNo | Name | Salary | DeptNo |
There are 4 columns.
Degree = 4

6. Order of Rows and Columns

Rows
 No predefined order.
 No two rows should contain exactly the same values.
Columns
 Order is not important.
  Names of columns are important.

7. Domain
A domain is the set of all possible values a column can contain.
Example
Column Domain
EmpNo Employee numbers
DeptNo Department numbers
PartNo Part numbers
Even though all may contain numbers, their meanings are different.

8. Database Terminology vs File System Terminology

Relational Database File System
Table / Relation / Entity Set File
Row / Tuple / Entity Record
Column / Attribute Field

9. Keys in Relational Database

A key is a column (or set of columns) used to uniquely identify each row.

(a) Primary Key

A single column that uniquely identifies each row.
Example:
PARTS
PartNo PartDesc
11 Nut
22 Bolt
Primary Key = PartNo
Example:
 EMPLOYEE → EmpNo
 DEPARTMENT → DeptNo
 PROJ2002 → ProjNo

(b) Composite Primary Key (Composite Key)

A combination of two or more columns that uniquely identifies a row.
Example:
PRJPARTS
ProjNo PartNo Qty
1 11 20
2 33 5
1 22 10
Neither ProjNo nor PartNo alone is unique.
Together,
(ProjNo + PartNo)
uniquely identifies each row.

(c) Secondary Key

Used mainly for searching records.
It may not uniquely identify rows.
Examples:
 Employee Name
 Vendor Name
 Book Title
 Author Name
Purpose:
 Easier for users to remember than ID numbers.

(d) Surrogate Key

An artificial key added by the database designer.
Used when no suitable primary key exists.
Examples:
 CustomerID
 VendorID
 StudentID
 TermID

(e) Foreign Key

A column in one table that refers to the primary key of another table.
Purpose:
 Creates relationships between tables.
Example:
PARTS
PartNo Vendor
11 Richards
PRJPARTS
ProjNo PartNo
1 11
Here,
PartNo in PRJPARTS is a Foreign Key.
It references PartNo in PARTS.

10. Relationship Between Tables

Tables are connected using Foreign Keys.
Example:
PARTS
--------
PartNo (PK)

↑
|
PRJPARTS
---------
PartNo (FK)
This relationship allows us to find:
 Which parts belong to a project.
 Which vendor supplies those parts.

11. Oracle Key Rules

In Oracle:
 PRIMARY KEY uniquely identifies each row.
 FOREIGN KEY references the PRIMARY KEY of another table.
 Secondary keys are used only for searching and are not part of the table
definition.

12. Table Notation

Primary keys are underlined in notation.
Examples:
PROJ2002(ProjNo, Loc, Customer)

PARTS (PartNo, PartDesc, Vendor, Cost)

DEPARTMENT (DeptNo, DeptName)

EMPLOYEE (EmpNo, Ename, DeptNo, ProjNo, Salary)

PRJPARTS (ProjNo, PartNo, Qty)
 ProjNo, PartNo, DeptNo, and EmpNo are primary keys in their
respective tables.
 In PRJPARTS, (ProjNo, PartNo) together form the composite primary
key.

13. Advantages of the Relational Database Model

 Easy to understand.
 Data is stored in simple tables.
 Reduces data redundancy.
 Easy to retrieve data using queries.
 Supports relationships between tables.
 Maintains data integrity through keys.
 Flexible and widely used in business applications.

INTEGRITY RULES
Integrity rules ensure that the data stored in a relational database is accurate,
consistent, and reliable.
There are two main integrity rules:
1. Entity Integrity
 States that no primary key column can contain a NULL value.
 Every row must have a unique and valid primary key.
 A NULL means the value is unknown, missing, undefined, or not
applicable.
 0 (zero) or a blank space is not considered NULL.
 RDBMS does not allow records without a valid primary key.
Example:
EmpNo (PK) Name
101 Carter
102 Albert
❌ Invalid:
EmpNo (PK) Name
NULL Carter

2. Referential Integrity
 States that a foreign key must either:
o Contain a NULL value, or
o Match an existing primary key value in the referenced table.
 Ensures relationships between tables remain valid.
 Prevents invalid or orphan records.
Example:
DEPARTMENT
DeptNo (PK) DeptName
10 Production
20 Supplies

EMPLOYEE
EmpNo Name DeptNo (FK)
101 Carter 10
102 Albert 30

NULL Values
 A NULL value means:
 o Unknown
o Not entered
o Undefined
o Not applicable
 It is different from 0 or a blank space.

Oracle and Integrity Rules

 Oracle Database strictly enforces:
o Entity Integrity
o Referential Integrity
 A foreign key must reference an existing primary key or be NULL.

Default Values
 A default value can be assigned to a column.
 If the user does not enter a value, the default value is stored
automatically.

Why Avoid NULL Values?

 NULL values increase search and query complexity.
 Extra conditions are needed to include or exclude NULL values.
 However, NULL values may be necessary in some situations, such as:
o Employee has no middle name.
o Employee has not yet been assigned to a department.
o Student's major is not yet decided.
THEORETICAL RELATIONAL LANGUAGES
E. F. Codd proposed two theoretical languages for relational databases:
1. Relational Algebra (Procedural Language)
2. Relational Calculus (Non-Procedural Language)
These languages are the foundation of SQL (Structured Query Language)
used in modern Relational Database Management Systems (RDBMS).

Difference Between Relational Algebra and Relational Calculus

Relational Algebra Relational Calculus
Procedural language Non-procedural language
Tells how to retrieve data Tells what data is required
Uses operations in sequence Uses logical conditions
Basis for SQL execution Basis for SQL query formulation

What is Relational Algebra?

 Relational Algebra is a procedural query language.
 It performs operations on one or more tables and produces another table as
the result.
For example,
Suppose we have the following table.

EMPLOYEE
EmpNo Name Dept Salary
101 Carter Production 25000
102 Albert Supplies 37000
103 Breen Marketing 50000
104 Gould Supplies 23000
Different operations can be performed on this table.

1. Union ( ∪ )
Definition
The Union operation combines all rows from two tables.
 Duplicate rows are removed.
 Both tables must be union compatible.
Example
PROJ2002
PROJ2003 ProjNo Location Customer

ProjNo Location Customer 1 Miami Stocks

1 Miami Stocks 2 Orlando Allen

3 Trenton Smith 3 Trenton Smith

5 Phoenix Robins

Operation
PROJ2002 ∪ PROJ2003

Result
ProjNo Location Customer
1 Miami Stocks
2 Orlando Allen
3 Trenton Smith
5 Phoenix Robins
Notice:
Rows 1 Miami Stocks and 3 Trenton Smith appear only once.
2. Intersection ( ∩ )
Definition
 Returns only the rows common to both tables.
Operation
PROJ2002 ∩ PROJ2003
3. Difference ( − )
Definition
 Returns rows that exist in the first table but not in the second.
Operation
PROJ2002 − PROJ2003

4. Projection ( π )
Definition
 Projection selects only the required columns.
 It gives vertical slices.

PARTS Table
PartNo PartDesc Vendor Cost
11 Nut Richards 19.95
22 Bolt Black 5.00
33 Washer Mobley 55.99
Operation
π(PartDesc, Cost)
Result
PartDesc Cost
Nut 19.95
Bolt 5.00
PartDesc Cost
Washer 55.99
Only selected columns appear.
Real-life Example
Student table
Roll Name Phone Address
If only Name and Phone are required,
Projection returns
|Name|Phone|

5. Selection ( σ )
Definition
Selection chooses rows satisfying a condition.
It gives horizontal slices.

PARTS
PartNo PartDesc Cost
11 Nut 19.95
22 Bolt 5.00
33 Washer 55.99
Operation
σ(Cost > 10)
Result
PartNo PartDesc Cost
11 Nut 19.95
33 Washer 55.99
Only expensive parts are selected.
Real-life Example
Students scoring above 80 marks.

6. Product (Cartesian Product)

Definition
Combines every row of one table with every row of another table.
Formula
Rows = x × y

Columns = m + n
Employee
Name
Carter
Albert

Department
Dept
Production
Marketing
Supplies

Operation
Employee × Department
Result
Name Department
Carter Production
Carter Marketing
Name Department

Carter Supplies
Albert Production
Albert Marketing
Albert Supplies
2 × 3 = 6 rows

Real-life Example
Every student chooses every subject.

7. Assignment ( ← or =)
Definition
Stores the result of an operation in a new table.
Example
TABLE_A = PROJ2002 ∪ PROJ2003
Now TABLE_A contains all projects.

Real-life Example
Saving exam results into a new table called RESULT.

8. Join ( ⋈ )
Definition
Join combines rows from two tables based on a common column.
This is the most frequently used operation.

EMPLOYEE
EmpNo Name DeptNo
101 Carter 10
102 Albert 20

DEPARTMENT
DeptNo DeptName
10 Production
20 Supplies

Operation
EMPLOYEE ⋈ DEPARTMENT
Condition
DeptNo = DeptNo

Result
EmpNo Name DeptName
101 Carter Production
102 Albert Supplies
Now employee information and department information appear together.

Real-life Example
Student table
|Roll|DeptID|
Department table
|DeptID|DeptName|
Join gives
|Roll|DeptName|
9. Division ( ÷ )
Definition
 Division finds rows related to every value in another table.
 It is the most difficult relational algebra operation.
PROJ
ProjNo
1
2
3

PRJPARTS
ProjNo PartNo
1 11
2 11
3 11
1 22
2 33

Operation
PRJPARTS ÷ PROJ
Result
PartNo
11
Why?
Part 11 is used in
Project 1 ✓
Project 2 ✓
Project 3 ✓
No other part is used in all projects.
DATA MODELING
Definition
 Data Modeling is the process of creating a logical representation of
data, its entities, attributes, and relationships before designing a
database.
 It acts as a blueprint for database design.
Objectives of Data Modeling
 Represent real-world data in a simple form.
 Identify entities, attributes, and relationships.
 Reduce redundancy and errors.
 Improve communication between users and database designers.
 Help create an efficient database.
ER Model
 The Entity-Relationship Model (ER Model) is a conceptual model for
designing a database.
 This model represents the logical structure of a database, including
entities, their attributes, and relationships between them.
 Entity: An object that is stored as data.
E.g: Student, Course, or Company.
 Attribute: Properties that describe an entity. E.g: StudentID,
CourseName, or EmployeeEmail.
 Relationship: A connection between entities. E.g: Student enrolls in
a Course.
 The graphical representation of this model is called an Entity-Relation
Diagram (ERD).
ER Model in Database Design Process
We typically follow the below steps for designing a database for an
application.
1. Gather the requirements (functional and data) by asking questions to the
database users.
2. Create a logical or conceptual design of the database. This is where ER
model plays a role. It is the most used graphical representation of the
conceptual design of a database.
3. After this, focus on physical database design (like indexing) and external
design (like views).
Uses of ER Diagrams in DBMS
 ER diagrams represent the E-R model in a database, making them easy to
convert into relations (tables).
 These diagrams serve the purpose of real-world modeling of objects
which makes them intently useful.
 Unlike technical schemas, ER diagrams require no technical knowledge
of the underlying DBMS used.
 They visually model data and its relationships, making complex systems
easier to understand.
Symbols Used in ER Model
 ER Model is used to model the logical view of the system from a
data perspective which consists of these symbols:
Entity
 It represents a real-world object, concept or thing about which data
is stored in a database.
 It act as a building block of a database.
 Tables in relational database represent these entities.
Example of entities:
 Real-World Objects: Person, Car, Employee etc.
 Concepts: Course, Event, Reservation etc.
 Things: Product, Document, Device etc.
 The entity type defines the structure of an entity, while individual
instances of that type represent specific entities.
Types of Entity
There are two main types of entities:
1. Strong Entity
 A type of entity that has a key Attribute that can uniquely identify
each instance of the entity.
 A Strong Entity does not depend on any other Entity in the
Schema for its identification.
 It has a primary key that ensures its uniqueness and is represented
by a rectangle in an ER diagram.
2. Weak Entity
 It cannot be uniquely identified by its own attributes alone.
 It depends on a strong entity to be identified.
 A weak entity is associated with an identifying entity (strong
entity), which helps in its identification.
 A weak entity are represented by a double rectangle.
 The participation of weak entity types is always total.
 The relationship between the weak entity type and its identifying
strong entity type is called identifying relationship and it is
represented by a double diamond.
Example:
A company may store the information of dependents (Parents, Children,
Spouse) of an Employee. But the dependents can't exist without the
employee. So dependent will be a Weak Entity Type and Employee will
be identifying entity type for dependent, which means it is Strong Entity
 Type.

Attribute types
Simple attribute: cannot be subdivided (e.g., city, gender).
Composite attribute: can be subdivided (e.g., FullName → first, middle, last).
Single-valued attribute: one value (e.g., EmployeeID, DOB).
Multivalued attribute: multiple values (e.g., degree codes, course
registrations)

DEPENDENCY
In RDBMS, dependency usually means functional dependency between
attributes (columns).
It is the basis for normalization (2NF, 3NF, BCNF) and helps avoid
redundancy and update anomalies.
1) Functional Dependency (FD)
If attribute B depends on attribute A, we write:
A→B
Meaning: If two rows have the same A value, they must have the same B value.
Example
EMP(EmployeeID, EmployeeName, DeptNo)
EmployeeID → EmployeeName
Because one EmployeeID determines one employee name.
2) Types of Dependencies (common in normalization)
A) Full (Total) Functional Dependency
 A non-key column depends on the entire composite key (not part of
it).
Example:
ENROLL(StudentID, CourseID, Grade) with PK = (StudentID,
CourseID)
(StudentID, CourseID) → Grade (full)
B) Partial Dependency
 A non-key column depends on part of a composite key.
Example:
ENROLL(StudentID, CourseID, StudentName, Grade)
PK = (StudentID, CourseID)
StudentID → StudentName (partial)
(because StudentName depends only on StudentID)
Partial dependencies violate 2NF.
C) Transitive Dependency
 A non-key column depends on another non-key column.
Example:
EMP(EmployeeID, DeptNo, DeptName)
with PK = EmployeeID
EmployeeID → DeptNo
DeptNo → DeptName
So EmployeeID → DeptName indirectly (transitively)
Transitive dependencies violate 3NF.
Dependencies help:
 Reduce data redundancy
 Prevent anomalies:
 Update anomaly (same fact updated in many rows)
 Insert anomaly (can’t insert without unrelated data)
  Delete anomaly (deleting rows removes important facts)
Database design is the process of planning and creating the structure of a
relational database so data is:

 stored with minimum redundancy,

 kept accurate/consistent (integrity),
 easy to query and update.

Main Steps in Database Design

1) Requirement analysis
 Identify what data must be stored and what outputs/reports are needed.
 Collect business rules (e.g., “one customer can have many invoices”).

2) Conceptual design (ER Modeling)

Identify:
Entities (e.g., CUSTOMER, INVOICE, ITEM)
Attributes (e.g., CustNo, CustName)
Relationships + cardinality (1:1, 1:M, M:N)

3) Logical design (Relational schema)

 Convert ER model into tables:
 Choose candidate keys
 Pick primary keys (use minimal columns; avoid names as PK)
 Define foreign keys for relationships
 Split composite attributes (Name → FirstName, LastName)

4) Normalization (improvement/analysis)

 Reduce redundancy and anomalies:

 1NF: PK defined, single value per cell, no multivalued columns
 2NF: 1NF + no partial dependency (important with composite
keys)
 3NF: 2NF + no transitive dependency

5) Physical design (implementation)

 Select data types, indexes, constraints

NORMAL FORMS
 Normal forms are a set of progressive rules (or design checkpoints)
for relational schemas that reduce redundancy and prevent data
anomalies.

Benefits of using Normal Forms

 Reduce duplicate data and wasted storage.
 Prevent insert, update, and delete anomalies.
 Improve data consistency and integrity.
 Make the schema easier to maintain and evolve

[Link] Normal Form (1NF): Eliminating Duplicate Records

A table is in 1NF if it satisfies the following conditions:

 All columns contain atomic values (i.e., indivisible values).
 Each row is unique (i.e., no duplicate rows).
 Each column has a unique name.
 The order in which data is stored does not matter.

Example of 1NF Violation:

If a table has a column "Phone Numbers" that stores multiple
phone numbers in a single cell, it violates 1NF. To bring it into 1NF, you
need to separate phone numbers into individual rows.

[Link] Normal Form (2NF): Eliminating Partial Dependency

A relation is in 2NF if it satisfies the conditions of 1NF and

additionally, contains no partial dependency exists, meaning every non-
prime attribute (non-key attribute) must depend on the entire primary key,
not just a part of it.

Example:
 For a composite key (StudentID, CourseID), if the "StudentName"
depends only on "StudentID" and not on the entire key, it violates
2NF.

 To normalize, move StudentName into a separate table where it

depends only on "StudentID".

[Link] Normal Form (3NF): Eliminating Transitive Dependency

 A relation is in 3NF if it satisfies 2NF and additionally, there are

no transitive dependencies.
  In simpler terms, non-prime attributes should not depend on other
non-prime attributes.
Example:
Consider a table with (StudentID, CourseID, Instructor). If Instructor
depends on "CourseID", and "CourseID" depends on "StudentID", then
Instructor indirectly depends on "StudentID", which violates 3NF. To resolve
this, place Instructor in a separate table linked by "CourseID".

4. Boyce-Codd Normal Form (BCNF): The Strongest Form of 3NF

BCNF is a stricter version of 3NF where for every non-trivial

functional dependency (X → Y), X must be a superkey (a unique
identifier for a record in the table).

Example:
 If a table has a dependency (StudentID, CourseID) → Instructor,
but neither "StudentID" nor "CourseID" is a superkey, then it
violates BCNF.
 To bring it into BCNF, decompose the table so that each
determinant is a candidate key.

5. Fourth Normal Form (4NF): Removing Multi-Valued Dependencies

 A table is in 4NF if it is in BCNF and has no multi-valued

dependencies.
 A multi-valued dependency occurs when one attribute determines
another, and both attributes are independent of all other attributes
in the table.
Example:
Consider a table where (StudentID, Language, Hobby) are
attributes. If a student can have multiple hobbies and languages, a multi-
valued dependency exists. To resolve this, split the table into separate
tables for Languages and Hobbies.

6. Fifth Normal Form (5NF): Eliminating Join Dependency

 5NF is achieved when a table is in 4NF and all join dependencies

are removed.
 This form ensures that every table is fully decomposed into smaller
tables that are logically connected without losing information.
Example:
If a table contains (StudentID, Course, Instructor) and there is a
dependency where all combinations of these columns are needed for a
 specific relationship, you would split them into smaller tables to remove
redundancy.

DENORMALIZATION
Denormalization is the process of adding redundant data to one or
more tables in a database to improve the speed of data retrieval (queries). It is
the opposite of normalization.
While normalization removes redundancy to maintain data integrity,
denormalization intentionally introduces some redundancy to reduce the number
of tables joins and improve performance.
Definition
Denormalization is a database optimization technique in which normalized
tables are combined or duplicate data is stored to improve query performance.
Why is Denormalization Used?
 Improves query performance.
 Reduces the number of JOIN operations.
 Speeds up data retrieval.
 Useful in data warehouses and reporting systems.
 Suitable for read-heavy applications.
Advantages
 Faster retrieval of data.
 Fewer table joins.
 Better performance for complex queries.
 Improves reporting and analytics.
Disadvantages
 Increases data redundancy.
 Wastes storage space.
 Makes updates more difficult.
 Can lead to data inconsistency if duplicate data is not updated
everywhere.