11-04-2025

Galgotia College of Engineering and Technology

Introduction

Unit: 1

DBMS Dr. Nripendra

Kumar Singh
Course Details Assistant Professor
(MCA IIth Sem) MCA Department

DBMS Unit-1
1

Course Objective

• The objective of course is to present an introduction to database

management systems, with an emphasis on how to organize,
maintain and retrieve-efficiently, and effectively –information in
relational and non-relational Database.
• List and explain the fundamental concepts of a relational database
system
• Knowledge of DBMS, both in terms of use and
implementation/design.
• Experience with SQL and Manipulate a database using SQL
• Increased proficiency with the programming language C++.
• Experience working as part of team v Experience with analysis and
design of (DB) software
• Assess the quality and ease of use of data modeling and
diagramming tools.

1
 11-04-2025

Unit Objective

1. The main objectives of database management system are data

availability, data integrity, data security, and data independence.

2. DDL stands for Data Definition Language. DML stands for Data
Manipulation Language. DDL statements are used to create
database, schema, constraints, users, tables etc. DML statement is
used to insert, update or delete the records.

3. ER diagrams are used to analyze existing databases to find and

resolve problems in logic or deployment. Drawing the diagram
should reveal where it's going wrong. Business information systems:
The diagrams are used to design or analyze relational databases
used in business processes.

What is a DBMS? (CO1)

• A very large, integrated collection of related data.

• Models real-world enterprise.
– Entities (e.g., students, courses)
– Relationships (e.g., Madonna is taking CS564)
• A Database Management System (DBMS) is a software package
designed to define,construct and manipulate database.
• Database + Database Management System= Database System.

2
 11-04-2025

Why Use a DBMS?

• Data independence and efficient access.

• Reduced application development time.
• Data integrity and security.
• Uniform data administration.
• Concurrent access, recovery from crashes.

Why Study Databases?

⚫ Shift from computation to information

⚫at the “low end”: scramble to webspace (a mess!)
⚫ at the “high end”: scientific applications
⚫ Datasets increasing in diversity and volume.
⚫ Digital libraries, interactive video, Human Genome project, EOS
project
⚫ ... need for DBMS exploding
⚫ DBMS encompasses most of CS
⚫ OS, languages, theory, AI, multimedia, logic

3
 11-04-2025

Files vs. DBMS

• Application must stage large datasets between main memory and

secondary storage (e.g., buffering, page-oriented access, 32-bit
addressing, etc.)
• Special code for different queries
• Must protect data from inconsistency due to multiple concurrent
users
• Crash recovery
• Security and access control

Purpose of Database Systems

⚫ In the early days, database applications were built directly on top of

file systems
⚫ Drawbacks of using file systems to store data:
⚫Data redundancy and inconsistency
⚫Multiple file formats, duplication of information in different
files
⚫Difficulty in accessing data
⚫Need to write a new program to carry out each new task
⚫Data isolation — multiple files and formats
⚫Integrity problems
⚫Integrity constraints (e.g. account balance > 0) become
“buried” in program code rather than being stated explicitly
⚫Hard to add new constraints or change existing ones

4
 11-04-2025

Purpose of Database Systems (Cont.)

• Drawbacks of using file systems (cont.)

– Atomicity of updates
• Failures may leave database in an inconsistent state with
partial updates carried out
• Example: Transfer of funds from one account to another
should either complete or not happen at all
– Concurrent access by multiple users
• Concurrent access needed for performance
• Uncontrolled concurrent accesses can lead to inconsistencies
– Example: Two people reading a balance and updating it
at the same time
– Security problems
• Hard to provide user access to some, but not all, data
• Database systems offer solutions to all the above problems

Summary

• DBMS used to maintain, query large datasets.

• Benefits include recovery from system crashes, concurrent access, quick
application development, data integrity and security.
• Levels of abstraction give data independence.
• A DBMS typically has a layered architecture.
• DBAs hold responsible jobs and are well-paid! 
• DBMS R&D is one of the broadest, most exciting areas in CS.

5
 11-04-2025

Short Quiz

1. What is the full form of DBMS?

a) Data of Binary Management System
b) Database Management System
c) Database Management Service
d) Data Backup Management System

2. What is a database?
a)Organized collection of information that cannot be
accessed, updated, and managed
b) Collectionof data or information without organizing
c)Organizedcollection of data or information that can be
accessed, updated, and managed
d) Organized collection of data that cannot be updated

Topic – Data models Objective

1. Data Model gives us an idea that how the final system will look like
after its complete implementation.

2. It defines the data elements and the relationships between the data
elements.

3. Data Models are used to show how data is stored, connected,

accessed and updated in the database management system.

6
 11-04-2025

Data Models

• A data model is a collection of concepts for describing data.

• Categories of Data Models :-

– High level or Conceptual Data Model
• Concept of entities, attributes and relationships
– Low level or physical Data Model
• Details of how data is stored in computer
– Representational Data Model
• Traditional commercial DBMS so called legacy data models-
network or hierarchical models.

Data Models

⚫ A collection of tools for describing

⚫Data
⚫Data relationships
⚫Data semantics
⚫Data constraints
⚫ Relational model
⚫ Entity-Relationship data model (mainly for database design)
⚫ Object-based data models (Object-oriented and Object-relational)
⚫ Semi structured data model (XML)
⚫ Other older models:
⚫Network model
⚫Hierarchical model

7
 11-04-2025

Schemas and Instances

• Similar to types and variables in programming languages

• Schema – the logical structure of the database
– Example: The database consists of information about a set of
customers and accounts and the relationship between them
– Analogous to type information of a variable in a program

• Instance – the actual content of the database at a particular point

in time
– Analogous to the value of a variable

Levels of Abstraction

• Physical Schema/Level: describes how a record (e.g., customer) is

stored.
• Logical Schema/Level: describes data stored in database, and the
relationships among the data.
type customer = record
customer_id : string;
customer_name : string;
customer_street : string;
customer_city : string;
end;
• External Schema/Level: application programs hide details of data
types. Views can also hide information (such as an employee’s
salary) for security purposes.

8
 11-04-2025

Levels of Abstraction

• Three Schema Architecture for a Database System

Example: University Database

⚫ Physical schema:
Relations stored as unordered files.
⚫
⚫ Index on first column of Students.
⚫ Conceptual schema:
⚫ Students(sid: string, name: string, login: string, age: integer, gpa:real)
Courses(cid: string, cname:string, credits:integer)
⚫
⚫ Enrolled(sid:string, cid:string, grade:string)
⚫ External Schema (View):
⚫ View1- Course_info(cid:string,enrollment:integer)
⚫ View2-studeninfo(id:int. name:string)

9
 11-04-2025

Data Independence

• Data Independence is defined as a property of DBMS that helps you

to change the Database schema at one level of a database system
without requiring to change the schema at the next higher level.

• Data independence helps you to keep data separated from all

programs that make use of it.

• In DBMS there are two types of data independence

– Physical data independence
– Logical data independence.

One of the most important benefits of using a DBMS!

Physical Data Independence

• Physical data independence can be defined as the capacity to

change the internal schema without having to change the
conceptual schema.

• If we do any changes in the storage size of the database system

server, then the Conceptual structure of the database will not be
affected.

• Physical data independence is used to separate conceptual levels

from the internal levels.

• Physical data independence occurs at the logical interface level.

10
 11-04-2025

Physical Data Independence (contd..)

• Due to Physical independence, any of the below change will not

affect the conceptual layer.
– Using a new storage device like Hard Drive or Magnetic Tapes
– Modifying the file organization technique in the Database
– Switching to different data structures.
– Changing the access method.
– Modifying indexes.
– Changes to compression techniques or hashing algorithms.
– Change of Location of Database from say C drive to D Drive

Logical Data Independence

• Logical data independence refers characteristic of being able to

change the conceptual schema without having to change the
external schema.

• Logical data independence is used to separate the external level

from the conceptual view.

• If we do any changes in the conceptual view of the data, then the

user view of the data would not be affected.

• Logical data independence occurs at the user interface level.

11
 11-04-2025

Logical Data Independence (contd..)

• Due to Logical independence, any of the below change will not

affect the external layer.
– Add/Modify/Delete a new attribute, entity or relationship is
possible without a rewrite of existing application programs
– Merging two records into one
– Breaking an existing record into two or more records

Short Quiz

1. Which of the following is known as a set of entities of the same type that
share same properties, or attributes?
a) Relation set
b) Tuples
c) Entity set
d) Entity Relation model

2. The values appearing in given attributes of any tuple in the referencing

relation must likewise occur in specified attributes of at least one tuple
in the referenced relation, according to
integrity constraint.
a) Referential
b) Primary
c) Referencing
d) Specific

12
 11-04-2025

Topic- Database Language Objective

1. DDL stands for Data Definition Language. DML stands for Data
Manipulation Language. DDL statements are used to create database,
schema, constraints, users, tables etc. DML statement is used to insert,
update or delete the records.

2. DCL (Data Control Language) includes commands like GRANT and

REVOKE, which are useful to give “rights & permissions.”

3. Transaction control language or TCL commands deal with the

transaction within the database.

4. Data Query Language (DQL) is used to fetch the data from the database.

DATA BASE LANGUAGE (CO1)

• A DBMS has appropriate languages and interfaces to express

database queries and updates.
• Database languages can be used to read, store and update the data
in the database.
• Two classes of languages
– Procedural – user specifies what data is required and how to get
those data
– Declarative (nonprocedural) – user specifies what data is
required without specifying how to get those data
• SQL is the most widely used query language

13
 11-04-2025

DATA BASE LANGUAGE

Data Definition Language (DDL)

• DDL stands for Data Definition Language. It is used to define

database structure or pattern.
• It is used to create schema, tables, indexes, constraints, etc. in the
database.
• Using the DDL statements, you can create the skeleton of the
database.
• Data definition language is used to store the information of
metadata like the number of tables and schemas, their names,
indexes, columns in each table, constraints, etc.

14
 11-04-2025

Data Definition Language (DDL)

• Here are some tasks that come under DDL:

– Create: It is used to create objects in the database.
– Alter: It is used to alter the structure of the database.
– Drop: It is used to delete objects from the database.
– Truncate: It is used to remove all records from a table.
– Rename: It is used to rename an object.
– Comment: It is used to comment on the data dictionary.

These commands are used to update the database schema that's why
they come under Data definition language.

Data Manipulation Language (DML)

• DML stands for Data Manipulation Language. It is used for accessing

and manipulating data in a database. It handles user requests.
• Here are some tasks that come under DML:
– Select: It is used to retrieve data from a database.
– Insert: It is used to insert data into a table.
– Update: It is used to update existing data within a table.
– Delete: It is used to delete all records from a table.
– Merge: It performs UPSERT operation, i.e., insert or update
operations.
– Call: It is used to call a structured query language or a Java
subprogram.
– Explain Plan: It has the parameter of explaining data.
– Lock Table: It controls concurrency.

15
 11-04-2025

Data Control Language (DCL)

• DCL stands for Data Control Language. It is used to retrieve the

stored or saved data.
• The DCL execution is transactional. It also has rollback parameters.
(But in Oracle database, the execution of data control language does
not have the feature of rolling back.)

• Here are some tasks that come under DCL:

– Grant: It is used to give user access privileges to a database.
– Revoke: It is used to take back permissions from the user.
There are the following operations which have the authorization of
Revoke:

• CONNECT, INSERT, USAGE, EXECUTE, DELETE, UPDATE and SELECT.

Transaction Control Language (TCL)

• TCL is used to run the changes made by the DML statement. TCL
can be grouped into a logical transaction.

• Here are some tasks that come under TCL:

– Commit: It is used to save the transaction on the database.
– Rollback: It is used to restore the database to original since the
last Commit.

16
 11-04-2025

Short Quiz

1. The ability to query data, as well as insert, delete, and alter tuples, is
offered by
a) TCL (Transaction Control Language)
b) DCL (Data Control Language)
c) DDL (Data Definition Langauge)
d) DML (Data Manipulation Langauge)

2. Which command is used to remove a relation from an SQL?

a) Drop table
b) Delete
c) Purge
d) Remove

Topic- Relational Model and SQL Objective

1. The purpose of the relational model is to provide a declarative method

for specifying data and queries: users directly state what information
the database contains and what information they want from it.

2. One of the major goals of Object relational data model is to close the
gap between relational databases and the object oriented practises
frequently used in many programming languages such as C++, C#, Java
etc.

3. Be able to write SQL statements that edit existing data. Be able to write
SQL statements that create database objects.

4. Understand the structure and design of relational databases.

Understand the importance and major issues of database security and
the maintenance of data integrity.

17
 11-04-2025

Relational Model

• Example of tabular data in the relational model

Attributes

A Sample Relational Database

18
 11-04-2025

SQL

• SQL: widely used non-procedural language

– Example: Find the name of the customer with customer-id 192-
83-7465
select customer.customer_name
from customer
where customer.customer_id = ‘192-83-7465’
– Example: Find the balances of all accounts held by the customer
with customer-id 192-83-7465
select [Link]
from depositor, account
where depositor.customer_id = ‘192-83-7465’ and
depositor.account_number = account.account_number

SQL

• Application programs generally access databases through one of

– Language extensions to allow embedded SQL
– Application program interface (e.g., ODBC/JDBC) which allow
SQL queries to be sent to a database

19
 11-04-2025

Database Users

Users are differentiated by the way they expect to interact with

the system
• Application programmers – interact with system through DML calls
• Sophisticated users – form requests in a database query language
• Specialized users – write specialized database applications that do
not fit into the traditional data processing framework
• Naïve users – invoke one of the permanent application programs
that have been written previously
– Examples, people accessing database over the web, bank tellers,
clerical staff

Database Administrator

• Coordinates all the activities of the database system

– has a good understanding of the enterprise’s information
resources and needs.
• Database administrator's duties include:
– Storage structure and access method definition
– Schema and physical organization modification
– Granting users authority to access the database
– Backing up data
– Monitoring performance and responding to changes
• Database tuning

20
 11-04-2025

Data storage and Querying

• The database system is divided into three components

– Storage management
– Query processing
– Transaction processing

Storage Management

⚫ Storage manager is a program module that provides the interface

between the low-level data stored in the database and the
application programs and queries submitted to the system.
⚫ The storage manager is responsible to the following tasks:
⚫Interaction with the file manager
⚫Efficient storing, retrieving and updating of data
⚫ Issues:
⚫Storage access
⚫File organization
⚫Indexing and hashing

21
 11-04-2025

Query Processing

1. Parsing and translation

2. Optimization
3. Evaluation

Query Processing (Cont.)

• Alternative ways of evaluating a given query

– Equivalent expressions
– Different algorithms for each operation
• Cost difference between a good and a bad way of evaluating a
query can be enormous
• Need to estimate the cost of operations
– Depends critically on statistical information about relations
which the database must maintain
– Need to estimate statistics for intermediate results to compute
cost of complex expressions

22
 11-04-2025

Transaction Management

• A transaction is a collection of operations that performs a single

logical function in a database application
• Transaction-management component ensures that the database
remains in a consistent (correct) state despite system failures (e.g.,
power failures and operating system crashes) and transaction
failures.
• Concurrency-control manager controls the interaction among the
concurrent transactions, to ensure the consistency of the database.

Database Architecture (CO1)

The architecture of a database systems is greatly influenced by the

underlying computer system on which the database is running:
⚫ Centralized
⚫ Client-server
⚫ Parallel (multiple processors and disks)
⚫ Distributed

23
 11-04-2025

Overall System Structure

Database Application Architectures

24
 11-04-2025

Database Design (CO1)

• Conceptual design: (ER Model is used at this stage.)

– What are the entities and relationships in the enterprise?
– What information about these entities and relationships should
we store in the database?
– What are the integrity constraints or business rules that hold?
– A database `schema’ in the ER Model can be represented
pictorially (ER diagrams).
– Can map an ER diagram into a relational schema.

Short Quiz

1. After groups have been established, SQL applies predicates in the

clause, allowing aggregate functions to be used.
a) Where
b) Having
c) Group by
d) With

2. Why the following statement is erroneous?

SELECT dept_name, ID, avg (salary) FROM instructor GROUP BY

dept_name;

a) Dept_id should not be used in group by clause

b) Group by clause is not valid in this query
c) Avg(salary) should not be selected
d) None

25
 11-04-2025

Topic- ER Model Objective

1. An entity relationship diagram gives a snapshot of how these

entities relate to each other. You could call it the blueprint that
underpins your business architecture, offering a visual
representation of the relationships between different sets of data
(entities).

2. An Entity Relationship (ER) Diagram is a type of flowchart

that illustrates how “entities” such as people, objects or concepts
relate to each other within a system.

Introduction of ER Model

• A database can be modeled as:

– a collection of entities,
– relationship among entities.
• An entity is an object that exists and is distinguishable from other
objects.

– Example: specific person, company, event, plant

• Entities have attributes
– Example: people have names and addresses

• An entity set is a set of entities of the same type that share the
same properties.
– Example: set of all persons, companies, trees, holidays

26
 11-04-2025

Entity Sets customer and loan

customer_id customer_ customer_ customer_ loan_ amount

name street city number

Attributes
• An entity is represented by a set of attributes, that is descriptive
properties possessed by all members of an entity set.
Example: customer = (customer_id, customer_name, customer_street,
customer_city )
loan = (loan_number, amount )

• In ER diagram, attribute is represented by an oval.

• Domain – the set of permitted values for each attribute

27
 11-04-2025

Attribute Type

• Key Attribute – The attribute which uniquely identifies each entity

in the entity set is called key attribute. For example, Roll_No will be
unique for each student. In ER diagram, key attribute is represented
by an oval with underlying lines.

• Composite Attribute – An attribute composed of many other

attribute is called as composite attribute. For example, Address
attribute of student Entity type consists of Street, City, State, and
Country. In ER diagram, composite attribute is represented by an
oval comprising of ovals.

Attribute Type

• Multivalued Attribute – An attribute consisting more than one

value for a given entity. For example, Phone_No (can be more than
one for a given student). In ER diagram, multivalued attribute is
represented by double oval.

• Derived Attribute – An attribute which can be derived from other

attributes of the entity type is known as derived attribute. e.g.; Age
(can be derived from DOB). In ER diagram, derived attribute is
represented by dashed oval.

28
 11-04-2025

Attribute Type

• The complete entity type Student with its attributes can be

represented as:

Relationship Type

• A relationship type represents the association between entity

types.

• For example, ‘Enrolled in’ is a relationship type that exists between

entity type Student and Course.

• In ER diagram, relationship type is represented by a diamond and

connecting the entities with lines.

29
 11-04-2025

Relationship Set

• A set of relationships of same type is known as relationship set.

• The following relationship set depicts S1 is enrolled in C2, S2 is

enrolled in C1 and S3 is enrolled in C3.

Degree of Relationship Set

• The number of different entity sets participating in a relationship

set is called as degree of a relationship set.

– Unary Relationship
– Binary Relationship
– n-ary Relationship

30
 11-04-2025

Unary Relationship

• When there is only ONE entity set participating in a relation, the

relationship is called as unary relationship.

• For example, one person is married to only one person.

Binary Relationship

• When there are TWO entities set participating in a relation, the

relationship is called as binary relationship.

• For example, Student is enrolled in Course.

31
 11-04-2025

n-ary Relationship

• When there are n entities set participating in a relation, the

relationship is called as n-ary relationship.

Mapping Cardinality

• The number of times an entity of an entity set participates in a

relationship set is known as cardinality.

• Cardinality can be of different types:

– One to one
– One to many
– Many to one
– Many to many

32
 11-04-2025

One to one

• When each entity in each entity set can take part only once in the
relationship, the cardinality is one to one. Let us assume that a
male can marry to one female and a female can marry to one male.
So the relationship will be one to one.

• Using Sets, it can be represented as:

Many to one

• When entities in one entity set can take part only once in the
relationship set and entities in other entity set can take part more
than once in the relationship set, cardinality is many to one

• Using Sets, it can be represented as:

33
 11-04-2025

Many to many

• When entities in all entity sets can take part more than once in the
relationship cardinality is many to many. Let us assume that a
student can take more than one course and one course can be
taken by many students. So the relationship will be many to many.

• Using sets, it can be represented as:

Participation Constraint

• Total Participation – Each entity in the entity set must participate in

the relationship. If each student must enroll in a course, the
participation of student will be total. Total participation is shown by
double line in ER diagram.
• Partial Participation – The entity in the entity set may or may NOT
participate in the relationship. If some courses are not enrolled by
any of the student, the participation of course will be partial.

34
 11-04-2025

Participation Constraint (contd..)

• Using set, it can be represented as

• Every student in Student Entity set is participating in relationship

but there exists a course C4 which is not taking part in the
relationship.

Weak Entity Type & Identifying Relationship

• An entity type has a key attribute which uniquely identifies each

entity in the entity set.
• But there exists some entity type for which key attribute can’t be
defined. These are called Weak Entity type.
• For example, A company may store the information of dependents
(Parents, Children, Spouse) of an Employee.
• But the dependents don’t have existence without the employee.
• So Dependent will be weak entity type and Employee will be
Identifying Entity type for Dependent.

35
 11-04-2025

Weak Entity Type & Identifying Relationship

• A weak entity type is represented by a double rectangle. The

participation of weak entity type is always total. The relationship
between weak entity type and its identifying strong entity type is
called identifying relationship and it is represented by double
diamond.

36
 11-04-2025

Keys in DBMS

• KEYS in DBMS is an attribute or set of attributes which helps you to

identify a row(tuple) in a relation(table).
• They allow you to find the relation between two tables.
• Keys help you uniquely identify a row in a table by a combination of
one or more columns in that table.
Example:-
Employee ID FirstName LastName
11 Andrew Johnson
22 Tom Wood
33 Alex Hale

• In the above-given example, employee ID is a primary key because

it uniquely identifies an employee record. In this table, no other
employee can have the same employee ID.

Why we need a Key?

Here are some reasons for using SQL key in the DBMS system.
• Keys help you to identify any row of data in a table.
• In a real-world application, a table could contain thousands of
records. Moreover, the records could be duplicated.
• Keys in RDBMS ensure that you can uniquely identify a table record
despite these challenges.
• Allows you to establish a relationship between and identify the
relation between tables
• Help you to enforce identity and integrity in the relationship.

74

37
 11-04-2025

Types of Keys in DBMS

There are mainly Eight different types of Keys in DBMS and each key
has it’s different functionality:
– Super Key – A super key is a group of single or multiple keys
which identifies rows in a table.
– Primary Key – is a column or group of columns in a table that
uniquely identify every row in that table.
– Candidate Key – is a set of attributes that uniquely identify
tuples in a table. Candidate Key is a super key with no
repeated attributes.
– Alternate Key – is a column or group of columns in a table that
uniquely identify every row in that table.

Types of Keys in DBMS (contd..)

– Foreign Key – is a column that creates a relationship between

two tables. The purpose of Foreign keys is to maintain data
integrity and allow navigation between two different instances
of an entity.
– Compound Key – has two or more attributes that allow you to
uniquely recognize a specific record. It is possible that each
column may not be unique by itself within the database.
– Composite Key – is a combination of two or more columns
that uniquely identify rows in a table. The combination of
columns guarantees uniqueness, though individual uniqueness
is not guaranteed.
– Surrogate Key – An artificial key which aims to uniquely
identify each record is called a surrogate key. These kind of key
are unique because they are created when you don’t have any
natural primary key.

76

38
 11-04-2025

Super key

• A superkey is a group of single or multiple keys which identifies

rows in a table. A Super key may have additional attributes that are
not needed for unique identification.
Example:
EmpSSN EmpNum Empname
9812345098 AB05 Shown
9876512345 AB06 Roslyn
199937890 AB07 James

• In the above-given example, EmpSSN and EmpNum name are

superkeys.

77

Primary Key

• PRIMARY KEY in DBMS is a column or group of columns in a table

that uniquely identify every row in that table. The Primary Key can’t
be a duplicate meaning the same value can’t appear more than
once in the table. A table cannot have more than one primary key.
Example:
StudID Roll No First Name LastName Email
1 11 Tom Price abc@[Link]
2 12 Nick Wright xyz@[Link]
3 13 Dana Natan mno@[Link]

• In the following example, StudID is a Primary Key.

78

39
 11-04-2025

Primary Key (contd..)

Rules for defining Primary key:

• Two rows can’t have the same primary key value
• It must for every row to have a primary key value.
• The primary key field cannot be null.
• The value in a primary key column can never be modified or
updated if any foreign key refers to that primary key.

79

Alternate key

• ALTERNATE KEYS is a column or group of columns in a table that

uniquely identify every row in that table. A table can have multiple
choices for a primary key but only one can be set as the primary
key. All the keys which are not primary key are called an Alternate
Key.
Example:
StudID Roll No First Name LastName Email
1 11 Tom Price abc@[Link]
2 12 Nick Wright xyz@[Link]
3 13 Dana Natan mno@[Link]
• In this table, StudID, Roll No, Email are qualified to become a
primary key. But since StudID is the primary key, Roll No, Email
becomes the alternative key.

80

40
 11-04-2025

Candidate Key

• CANDIDATE KEY in SQL is a set of attributes that uniquely identify

tuples in a table. Candidate Key is a super key with no repeated
attributes. The Primary key should be selected from the candidate
keys. Every table must have at least a single candidate key. A table
can have multiple candidate keys but only a single primary key.
Example:
StudID Roll No First Name LastName Email
1 11 Tom Price abc@[Link]
2 12 Nick Wright xyz@[Link]
3 13 Dana Natan mno@[Link]

• Candidate key Example: In the given table Stud ID, Roll No, and
email are candidate keys which help us to uniquely identify the
student record in the table.

81

Candidate Key (contd..)

Properties of Candidate key:

• It must contain unique values
• Candidate key in SQL may have multiple attributes
• Must not contain null values
• It should contain minimum fields to ensure uniqueness
• Uniquely identify each record in a table

82

41
 11-04-2025

Foreign key
• FOREIGN KEY is a column that creates a relationship between two
tables. The purpose of Foreign keys is to maintain data integrity and
allow navigation between two different instances of an entity. It
acts as a cross-reference between two tables as it references the
primary key of another table.
Example:
DeptCode DeptName
001 Science
002 English
005 Computer

Teacher ID Fname Lname

B002 David Warner
B017 Sara Joseph
B009 Mike Brunton
83

Foreign key (contd..)

• In this example, we have two tables, teacher and department in a

school. However, there is no way to see which teacher work in
which department.

• In this table, adding the foreign key Deptcode to the Teacher, we

can create a relationship between the two tables.

Teacher ID DeptCode Fname Lname

B002 002 David Warner
B017 002 Sara Joseph
B009 001 Mike Brunton

• This concept is also known as Referential Integrity.

84

42
 11-04-2025

Compound key
• COMPOUND KEY has two or more attributes that allow you to uniquely
recognize a specific record. It is possible that each column may not be
unique by itself within the database. However, when combined with
the other column or columns the combination of composite keys
become unique.
Example:
OrderNo PorductID Product Name Quantity
B005 JAP102459 Mouse 5
B005 DKT321573 USB 10
B005 OMG446789 LCD Monitor 20
B004 DKT321573 USB 15
B002 OMG446789 Laser Printer 3

• In this example, OrderNo and ProductID can’t be a primary key as it

does not uniquely identify a record. However, a compound key of Order
ID and Product ID could be used as it uniquely identified each record.
85

Composite key

• COMPOSITE KEY is a combination of two or more columns that

uniquely identify rows in a table. The combination of columns
guarantees uniqueness, though individually uniqueness is not
guaranteed. Hence, they are combined to uniquely identify records
in a table.

• The difference between compound and the composite key is that

any part of the compound key can be a foreign key, but the
composite key may or maybe not a part of the foreign key.

86

43
 11-04-2025

Surrogate key

• SURROGATE KEYS is An artificial key which aims to uniquely identify

each record is called a surrogate key.
• This kind of partial key in dbms is unique because it is created when
you don’t have any natural primary key.
• They do not lend any meaning to the data in the table. Surrogate
key in DBMS is usually an integer.
• A surrogate key is a value generated right before the record is
inserted into a table.
• Surrogate keys in sql are allowed when
– No property has the parameter of the primary key.
– In the table when the primary key is too big or complicated.

87

Surrogate key (contd..)

Example:

Fname Lastname Start Time End Time

Anne Smith 09:00 18:00
Jack Francis 08:00 17:00
Anna McLean 11:00 20:00
Shown Willam 14:00 23:00

Above, given example, shown shift timings of the different employee.

In this example, a surrogate key is needed to uniquely identify each
employee.

44
 11-04-2025

Difference b/w Primary key & Foreign key

Primary Key Foreign Key

Helps you to uniquely identify a record in It is a field in the table that is the primary
the table. key of another table.

A foreign key may accept multiple null

Primary Key never accept null values.
values.

A foreign key cannot automatically create

Primary key is a clustered index and data
an index, clustered or non-clustered.
in the DBMS table are physically organized
However, you can manually create an
in the sequence of the clustered index.
index on the foreign key.

You can have the single Primary key in a You can have multiple foreign keys in a
table. table.

Short Quiz

Q. Given the basic ER and relational models, which of the following is

INCORRECT?
answer choices
1. An attribute of an entity can have more than one value.
2. An attribute of an entity can be composite.
3. In a row of a relational table, an attribute can have more than one
value.
4. In a row of a relational table, an attribute can have exactly one value
or a NULL value.

Q. Which type of entity cannot exist in the database unless another type
of entity also exists in the database, but does not require that the
identifier of that other entity be included as part of its own identifier?

1. Weak entity
2. Strong entity
3. ID-dependent entity
4. ID- independent entity

45
 11-04-2025

Topic- Generalization, Specialization and

Aggregation Objective

1. Generalization of Entities helps in establishing relationships between the

tables, as they have common attributes to be identified. It makes the
structuring of the database management simpler and easy to identify all the
relevant attributes.

2. Specialization helps in defining the set of subclasses of the entity type. Also,
it helps in establishing extra specific attributes with all subclasses and added
specific relationship types between each subclass and the other entity types
or the other subclasses.

3. Inheritance is an important feature of Generalization and Specialization.

It allows lower-level entities to inherit the attributes of higher-level entities.
For example, the attributes of a Person class such as name, age, and gender
can be inherited by lower-level entities such as Student or Teacher.

Generalization
• Generalization is the process of extracting common properties from
a set of entities and create a generalized entity from it.

• It is a bottom-up approach in which two or more entities can be

generalized to a higher level entity if they have some attributes in
common.

• For Example, STUDENT and FACULTY can be generalized to a higher

level entity called PERSON as shown in Figure.

• In this case, common attributes like P_NAME, P_ADD become part

of higher entity (PERSON) and specialized attributes like S_FEE
become part of specialized entity (STUDENT).

46
 11-04-2025

Example:

Specialization

• In specialization, an entity is divided into sub-entities based on their

characteristics.

• It is a top-down approach where higher level entity is specialized

into two or more lower level entities.

• For Example, EMPLOYEE entity in an Employee management system

can be specialized into DEVELOPER, TESTER etc. as shown in Figure.

• In this case, common attributes like E_NAME, E_SAL etc. become

part of higher entity (EMPLOYEE) and specialized attributes like
TES_TYPE become part of specialized entity (TESTER).

47
 11-04-2025

Example:

Aggregation
• An ER diagram is not capable of representing relationship between
an entity and a relationship which may be required in some
scenarios.
• In those cases, a relationship with its corresponding entities is
aggregated into a higher level entity.
• Aggregation is an abstraction through which we can represent
relationships as higher level entity sets.
• For Example, Employee working for a project may require some
machinery. So, REQUIRE relationship is needed between
relationship WORKS_FOR and entity MACHINERY. Using
aggregation, WORKS_FOR relationship with its entities EMPLOYEE
and PROJECT is aggregated into single entity and relationship
REQUIRE is created between aggregated entity and MACHINERY.

48
 11-04-2025

Example:

Topic –
Reduction of an ER diagrams to tables
Objectives

1. Mapping an Entity Relationship (ER) model gives a good overview of

the design of a system with the goal of making the system easier to
understand at a technical level.

2. The ER diagrams can be mapped to a relation schema, which means

we can clearly display the relationship between its members.

3. An Entity Relationship (ER) Diagram is a type of flowchart

that illustrates how “entities” such as people, objects or concepts
relate to each other within a system.

49
 11-04-2025

Reduction of an ER diagrams to tables

Rule-01: For Strong Entity Set With Only Simple Attributes

• A strong entity set with only simple attributes will require only one
table in relational model.
– Attributes of the table will be the attributes of the entity set.
– The primary key of the table will be the key attribute of the
entity set.

Schema : Student ( Roll_no , Name , Sex )

RULE-02

Rule-02: For Strong Entity Set With Composite Attributes

• A strong entity set with any number of composite attributes will
require only one table in relational model.
• While conversion, simple attributes of the composite attributes are
taken into account and not the composite attribute itself.

Schema : Student ( Roll_no , First_name , Last_name , House_no , Street , City )

50
 11-04-2025

RULE-03

Rule-03: For Strong Entity Set With Multi Valued Attributes

• A strong entity set with any number of multi valued attributes will
require two tables in relational model.
– One table will contain all the simple attributes with the primary
key.
– Other table will contain the primary key and all the multi valued
attributes.
•

Roll_no City Roll_no Mobile_no

RULE-04
Rule-04: Translating Relationship Set into a Table
• A relationship set will require one table in the relational model.
• Attributes of the table are-
– Primary key attributes of the participating entity sets
– Its own descriptive attributes if any.
– Set of non-descriptive attributes will be the primary key.

51
 11-04-2025

RULE-05

Rule-05: For Binary Relationships With Cardinality Ratios

• The following four cases are possible-

– Case-01: Binary relationship with cardinality ratio m:n

– Case-02: Binary relationship with cardinality ratio 1:n

– Case-03: Binary relationship with cardinality ratio m:1

– Case-04: Binary relationship with cardinality ratio 1:1

RULE-05 (contd..)

Case-01: For Binary Relationship With Cardinality Ratio m:n

• Here, three tables will be required-

1. A ( a1 , a2 )
2. R ( a1 , b1 )
3. B ( b1 , b2 )

52
 11-04-2025

RULE-05 (contd..)

Case-02: For Binary Relationship With Cardinality Ratio 1:n

• Here, two tables will be required-

1. A ( a1 , a2 )
2. BR ( a1 , b1 , b2 )

NOTE- Here, combined table will be drawn for the entity set B and
relationship set R.

RULE-05 (contd..)

Case-03: For Binary Relationship With Cardinality Ratio m:1

• Here, two tables will be required-

1. AR ( a1 , a2 , b1 )
2. B ( b1 , b2 )

NOTE- Here, combined table will be drawn for the entity set A and
relationship set R.

53
 11-04-2025

RULE-05 (contd..)

Case-04: For Binary Relationship With Cardinality Ratio 1:1

• Here, two tables will be required. Either combine ‘R’ with ‘A’ or ‘B’

• Way-01:
1. AR ( a1 , a2 , b1 )
2. B ( b1 , b2 )
• Way-02:
1. A ( a1 , a2 )
2. BR ( a1 , b1 , b2 )

RULE-06

Rule-06: For Binary Relationship With Both Cardinality Constraints

and Participation Constraints-

• Case-01: For Binary Relationship With Cardinality Constraint and

Total Participation Constraint From One Side

• Case-02: For Binary Relationship With Cardinality Constraint and

Total Participation Constraint From Both Sides-

54
 11-04-2025

RULE-06 (contd..)
Case-01: For Binary Relationship With Cardinality Constraint and
Total Participation Constraint From One Side

• Because cardinality ratio = 1 : n , so we will combine the entity set B

and relationship set R.
• Then, two tables will be required-
1. A ( a1 , a2 )
2. BR ( a1 , b1 , b2 )
• Because of total participation, foreign key a1 has acquired NOT
NULL constraint, so it can’t be null now.

RULE-06 (contd..)

Case-02: For Binary Relationship With Cardinality Constraint and

Total Participation Constraint From Both Sides
• If there is a key constraint from both the sides of an entity set with
total participation, then that binary relationship is represented
using only single table.

• Here, Only one table is required.

1. ARB ( a1 , a2 , b1 , b2 )

55
 11-04-2025

RULE-07

Rule-07: For Binary Relationship With Weak Entity Set

• Weak entity set always appears in association with identifying
relationship with total participation constraint.

• Here, two tables will be required-

1. A ( a1 , a2 )
2. BR ( a1 , b1 , b2 )

Examples of ER Diagram

56
 11-04-2025

ER Diagram For Department

ER Diagram for Company

57
 11-04-2025

Codd’s 12 Rule

Ashish DBMS Unit-1 115

Mathur

Codd’s 12 Rule
Rule 0: The Foundation Rule
The database must be in relational form. So that the system can handle the
database through its relational capabilities.
Rule 1: Information Rule
A database contains various information, and this information must be stored in
each cell of a table in the form of rows and columns.
Rule 2: Guaranteed Access Rule
Every single or precise data (atomic value) may be accessed logically from a
relational database using the combination of primary key value, table name, and
column name.
Rule 3: Systematic Treatment of Null Values
This rule defines the systematic treatment of Null values in database records. The
null value has various meanings in the database, like missing the data, no value in
a cell, inappropriate information, unknown data and the primary key should not
be null.

58
 11-04-2025

Codd’s 12 Rule
Rule 4: Active/Dynamic Online Catalog based on the relational model
It represents the entire logical structure of the descriptive database that must be
stored online and is known as a database dictionary. It authorizes users to access
the database and implement a similar query language to access the database.
Rule 5: Comprehensive Data SubLanguage Rule
The relational database supports various languages, and if we want to access the
database, the language must be the explicit, linear or well-defined syntax,
character strings and supports the comprehensive:
Data definition
View definition
Data manipulation
Integrity constraints
Transaction boundaries (begin, commit and rollback)
Rule 6: View Updating Rule
All views table can be theoretically updated and must be practically updated by
the database systems.

Codd’s 12 Rule
Rule 8: Physical Data Independence Rule
All stored data in a database or an application must be physically independent to
access the database. Each data should not depend on other data or an
application. If data is updated or the physical structure of the database is
changed, it will not show any effect on external applications that are accessing
the data from the database.
Rule 9: Logical Data Independence Rule
It is similar to physical data independence. It means, if any changes occurred to
the logical level (table structures), it should not affect the user's view
(application). For example, suppose a table either split into two tables, or two
table joins to create a single table, these changes should not be impacted on the
user view application.
Rule 10: Integrity Independence Rule
A database must maintain integrity independence when inserting data into
table's cells using the SQL query language. All entered values should not be
changed or rely on any external factor or application to maintain integrity. It is
also helpful in making the database-independent for each front-end application.

59
 11-04-2025

Codd’s 12 Rule
Rule 11: Distribution Independence Rule
The distribution independence rule represents a database that must work
properly, even if it is stored in different locations and used by different end-users.
Suppose a user accesses the database through an application; in that case, they
should not be aware that another user uses particular data, and the data they
always get is only located on one site. The end users can access the database, and
these access data should be independent for every user to perform the SQL
queries.
Rule 12: Non Subversion Rule
The non-submersion rule defines RDBMS as a SQL language to store and
manipulate the data in the database. If a system has a low-level or separate
language other than SQL to access the database system, it should not subvert or
bypass integrity to transform data.

Summary of Conceptual Design

• Conceptual design follows requirements analysis,

– Yields a high-level description of data to be stored
• ER model popular for conceptual design
– Constructs are expressive, close to the way people think about
their applications.
• Basic constructs: entities, relationships, and attributes (of entities
and relationships).
• Some additional constructs: weak entities, ISA hierarchies, and
aggregation.
• Note: There are many variations on ER model.

60
 11-04-2025

Summary of ER (Contd.)

• ER design is subjective. There are often many ways to model a

given scenario! Analyzing alternatives can be tricky, especially for a
large enterprise. Common choices include:
– Entity vs. attribute, entity vs. relationship, binary or n-ary
relationship, whether or not to use ISA hierarchies, and whether
or not to use aggregation.
• Ensuring good database design: resulting relational schema should
be analyzed and refined further. FD information and normalization
techniques are especially useful.

Daily Quiz

• Define Database System.

• What do you understand by data independence?
• Explain types of attributes in ER diagram.
• What is generalization.
• What do you understand by primary key?
• Explain participation cardinality.

61
 11-04-2025

Weekly Assignment

• Define: Data, Database, and Database System.

• What is data independence? What are differences between Logical
Data Independence and Physical Data Independence?
• Give example of Simple, Composite, Single –valued and Multi-
valued attributes of an entity.
• What is the difference between Generalization & Specialization
with respect to Database?
• What is difference between total and partial participation? Explain
by suitable example.
• Construct an E-R diagram for a hospital with a set of patients and a
set of medical doctors. Associated with each patient, a log of the
various tests and examinations conducted.

Topic Links

1. [Link]
2. [Link]
3. [Link]
questions/
4. [Link]
questions-set-2/

62
 11-04-2025

MCQ s

• The entity relationship set is represented in E-R diagram as

a) Double diamonds
b) Undivided rectangles
c) Dashed lines
d) Diamond

• The Rectangles divided into two parts represents

a) Entity set
b) Relationship set
c) Attributes of a relationship set
d) Primary key

MCQ s

• An entity set that does not have sufficient attributes to form a

primary key is termed a __
a) Strong entity set
b) Variant set
c) Weak entity set
d) Variable set

• What term is used to refer to a specific record in your music

database; for instance; information stored about a specific album?
a) Relation
b) Instance
c) Table
d) Column

63
 11-04-2025

Glossary Questions

1. What are the features of Database language?

2. What do database languages do?
3. Define database model.
4. What is SQL?
5. Enlist the various relationships of database.
6. Define DDL and DML.
7. Enlist some commands of DDL.

Old Question Papers

• [Link]
[Link]
• [Link]
[Link]
• [Link]
[Link]
• [Link]
[Link]

64
 11-04-2025

Expected Questions for University Exam

• Explain types of attributes in ER diagram.

• Explain the difference between a weak and a strong entity set with
example.
• Discuss three level of abstractions or schemas architecture of
DBMS.
• Define constraint and its types in DBMS.
• Compare Generalization, Specialization and aggregation with
suitable examples.
• Draw overall structure of DBMS and explain its components in brief.
• Construct an E-R diagram for a hospital with a set of patients and a
set of medical doctors. Associated with each patient, a log of the
various tests and examinations conducted.
•

Recap of Unit

• Knowledge of database architecture.

• Knowledge of Entity Relationship model and its concepts.

• Databse V/S File system.

65
11-04-2025

66