UNIT-2

❖ Introduction of DBMS (Database Management System)

A database is a collection of interrelated data that helps in the efficient retrieval,
insertion, and deletion of data from the database and organizes the data in the form
of tables, views, schemas, reports, etc. For Example, a university database
organizes the data about students, faculty, admin staff, etc. which helps in the
efficient retrieval, insertion, and deletion of data from it.
What is DBMS?
A Database Management System (DBMS) is a software system that is designed to
manage and organize data in a structured manner. It allows users to create, modify,
and query a database, as well as manage the security and access controls for that
database. DBMS provides an environment to store and retrieve data in convenient
and efficient manner.
Key Features of DBMS
• Data modeling: A DBMS provides tools for creating and modifying data
models, which define the structure and relationships of the data in a database.
• Data storage and retrieval: A DBMS is responsible for storing and
retrieving data from the database, and can provide various methods for
searching and querying the data.
• Concurrency control: A DBMS provides mechanisms for controlling
concurrent access to the database, to ensure that multiple users can access the
data without conflicting with each other.
• Data integrity and security: A DBMS provides tools for enforcing data
integrity and security constraints, such as constraints on the values of data and
access controls that restrict who can access the data.
• Backup and recovery: A DBMS provides mechanisms for backing up and
recovering the data in the event of a system failure.
• DBMS can be classified into two types: Relational Database Management
System (RDBMS) and Non-Relational Database Management System (NoSQL
or Non-SQL)
• RDBMS: Data is organized in the form of tables and each table has a set of
rows and columns. The data are related to each other through primary and
foreign keys.
• NoSQL: Data is organized in the form of key-value pairs, documents, graphs,
or column-based. These are designed to handle large-scale, high-performance
scenarios.
❖ Database Languages
• Data Definition Language
• Data Manipulation Language
• Data Control Language
• Transactional Control Language
1. Data Definition Language (DDL)
DDL is the short name for Data Definition Language, which deals with database
schemas and descriptions, of how the data should reside in the database.
• CREATE: to create a database and its objects like (table, index, views, store
procedure, function, and triggers)
• ALTER: alters the structure of the existing database
• DROP: delete objects from the database
• TRUNCATE: remove all records from a table, including all spaces allocated
for the records are removed
• COMMENT: add comments to the data dictionary
• RENAME: rename an object
2. Data Manipulation Language (DML)
DML is the short name for Data Manipulation Language which deals with data
manipulation and includes most common SQL statements such SELECT, INSERT,
UPDATE, DELETE, etc., and it is used to store, modify, retrieve, delete and update
data in a database. Data query language(DQL) is the subset of “Data
Manipulation Language”. The most common command of DQL
is SELECT statement. SELECT statement help on retrieving the data from the
table without changing anything in the table.
• SELECT: retrieve data from a database
• INSERT: insert data into a table
• UPDATE: updates existing data within a table
• DELETE: Delete all records from a database table
• MERGE: UPSERT operation (insert or update)
• CALL: call a PL/SQL or Java subprogram
• EXPLAIN PLAN: interpretation of the data access path
• LOCK TABLE: concurrency Control
3. Data Control Language (DCL)
• DCL is short for Data Control Language which acts as an access specifier to
the database.(basically to grant and revoke permissions to users in the database.
GRANT: grant permissions to the user for running DML(SELECT, INSERT,
DELETE,…) commands on the table
• REVOKE: revoke permissions to the user for running DML(SELECT,
INSERT, DELETE,…) command on the specified table
4. Transactional Control Language (TCL)
TCL is short for Transactional Control Language which acts as an manager for all
types of transactional data and all transactions. Some of the command of TCL are:
• Roll Back: Used to cancel or Undo changes made in the database
• Commit: It is used to apply or save changes in the database
• Save Point: It is used to save the data on the temporary basis in the database
Data Query Language (DQL)
Data query language(DQL) is the subset of “Data Manipulation Language”.
The most common command of DQL is the SELECT statement. SELECT
statement helps us in retrieving the data from the table without changing anything
or modifying the table. DQL is very important for retrieval of essential data from
a database.
➢ Advantages of DBMS
• Data organization: A DBMS allows for the organization and storage of data
in a structured manner, making it easy to retrieve and query the data as needed.
• Data integrity: A DBMS provides mechanisms for enforcing data integrity
constraints, such as constraints on the values of data and access controls that
restrict who can access the data.
• Concurrent access: A DBMS provides mechanisms for controlling
concurrent access to the database, to ensure that multiple users can access the
data without conflicting with each other.
• Data security: A DBMS provides tools for managing the security of the data,
such as controlling access to the data and encrypting sensitive data.
• Backup and recovery: A DBMS provides mechanisms for backing up and
recovering the data in the event of a system failure.
• Data sharing: A DBMS allows multiple users to access and share the same
data, which can be useful in a collaborative work environment.
➢ Disadvantages of DBMS
• Complexity: DBMS can be complex to set up and maintain, requiring
specialized knowledge and skills.
• Performance overhead: The use of a DBMS can add overhead to the
performance of an application, especially in cases where high levels of
concurrency are required.
• Scalability: The use of a DBMS can limit the scalability of an application,
since it requires the use of locking and other synchronization mechanisms to
ensure data consistency.
• Cost: The cost of purchasing, maintaining and upgrading a DBMS can be
high, especially for large or complex systems.
• Limited Use Cases: Not all use cases are suitable for a DBMS, some
solutions don’t need high reliability, consistency or security and may be better
served by other types of data storage.
➢ Applications of DBMS
• Enterprise Information: Sales, accounting, human resources,
Manufacturing, online retailers.
• Banking and Finance Sector: Banks maintaining the customer details,
accounts, loans, banking transactions, credit card transactions. Finance: Storing
the information about sales and holdings, purchasing of financial stocks and
bonds.
• University: Maintaining the information about student course enrolled
information, student grades, staff roles.
• Airlines: Reservations and schedules.
• Telecommunications: Prepaid, postpaid bills maintance.

❖ Difference between File System and DBMS

A file system and a DBMS are two kinds of data management systems that are used
in different capacities and possess different characteristics.
➢ File System
The file system is basically a way of arranging the files in a storage medium like a
hard disk. The file system organizes the files and helps in the retrieval of files when
they are required. File systems consist of different files which are grouped into
directories. The directories further contain other folders and files. The file system
performs basic operations like management, file naming, giving access rules,etc.
Example: NTFS(New Technology File System) , EXT(Extended File System).

File System
➢ DBMS (Database Management System)
Database Management System is basically software that manages the collection
of related data. It is used for storing data and retrieving the data effectively
when it is needed. It also provides proper security measures for protecting the
data from unauthorized access. In Database Management System the data can
be fetched by SQL queries and relational algebra. It also provides mechanisms
for data recovery and data backup.
Example:
Oracle, MySQL, MS SQL server.

DBMS

❖ Difference between File System and DBMS

Basics File System DBMS

The file system is a way of

DBMS is software for managing
arranging the files in a storage
the database.
Structure medium within a computer.

Data Redundant data can be present In DBMS there is no redundant

Redundancy in a file system. data.
Basics File System DBMS

It doesn’t provide Inbuilt It provides in house tools for

Backup and mechanism for backup and backup and recovery of data even
Recovery recovery of data if it is lost. if it is lost.

Query There is no efficient query Efficient query processing is there

processing processing in the file system. in DBMS.

There is more data consistency

There is less data consistency in
because of the process
the file system.
Consistency of normalization .

It has more complexity in

It is less complex as compared
handling as compared to the file
to DBMS.
Complexity system.

File systems provide less DBMS has more security

Security security in comparison to mechanisms as compared to file
Constraints DBMS. systems.

It has a comparatively higher cost

It is less expensive than DBMS.
Cost than a file system.

In DBMS data
independence exists, mainly of
There is no data independence. two types:
Data 1) Logical Data Independence .
Independence 2) Physical Data Independence.

Only one user can access data at Multiple users can access data at a
User Access a time. time.

The users are not required to The user has to write procedures
Meaning write procedures. for managing databases

Data is distributed in many files. Due to centralized nature data

Sharing So, it is not easy to share data. sharing is easy
Basics File System DBMS

Data It give details of storage and It hides the internal details

Abstraction representation of data of Database

Integrity Integrity Constraints are Integrity constraints are easy to

Constraints difficult to implement implement

To access data in a file , user

requires attributes such as file No such attributes are required.
Attribute s name, file location.

Example Cobol , C++ Oracle , SQL Server

❖ Structure of Database Management System

DBMS means Database Management System, which is a tool or software used to
create the database or delete or manipulate the database. A software programme
created to store, retrieve, query, and manage data is known as a Database
Management System (DBMS). Data can be generated, read, updated, and destroyed
by authorized entities thanks to user interfaces (UIs).
The below diagram shows the typical structure of a database management system.
A Database Management System has three major components:

• Query Processor
• Storage Manager
• Disk Storage.
Structure of DBMS
1. Query Processor
The Query Processor receives the queries (requests) from the user and interprets
them in the form of instructions. It also executes the instructions received from the
DML Compiler. It has the following four components:

• DML Compiler: It converts the DML (Data Manipulation Language)

Instructions into Machine Language (low-level language).
• DDL Interpreter: It interprets the DDL (Data Definition Language)
Instructions and stores the record in a data dictionary (in a table containing
meta-data)
• Query Optimizer: It executes the DML Instructions and picks the lowest
cost evaluation plan out of all the alternatives present.
• Embedded DML Pre-compiler: It translates the DML statements embedded
in Application Programs into procedural function calls.
2. Storage Manager
Storage manager acts as the interface between the data stored in the database and the
queries received from the end-user. This component in the Structure of DBMS is
responsible for the constraints applied to the data so that it remains consistent. It also
executes the DCL (Data Control Language). It encapsulates the following modules:
 • Authorization and Integrity Manager: It checks the authority of various
users who access data and the Integrity Constraints of the database.
• Transaction Manager: Its job is to assure the system remains in a proper
state during the transaction process. It also ensures that concurrent
transactions are executed without any conflict.
• File Manager: It manages the space allocation of files in disk and data
structures which stores information in the database.
• Buffer Manager: It manages the transfer of data between the secondary and
main memory. It also decides what data should be cached in the memory.
3. Disk Storage
The Disk Storage in the Structure of DBMS represents the space where data is stored.
It has the following components:

• Files: These are responsible for storing the data.

• Data Dictionary: It is the repository that maintains the information of the
database object and maintains the metadata.
• Indices: These are the keys that are used for faster retrieval of data.

❖ People who deal with databases

People who deal with databases generally have various roles, each contributing to
different aspects of database management, design, and usage. Here are some
common roles and the responsibilities associated with them:
1. Database Administrator (DBA): A Database Administrator is responsible for
the overall management, maintenance, and optimization of databases. They ensure
that databases run efficiently, remain secure, and are always available for users.
Tasks include database installation, configuration, backup and recovery,
performance tuning, and ensuring compliance with security protocols.
2. Database Developer: A Database Developer focuses on creating and managing
database objects like tables, views, indexes, and stored procedures. They write SQL
queries and ensure that applications can efficiently interact with databases. They also
work closely with application developers to ensure seamless integration between the
database and software.
3. Data Architect: Data Architects design the structure of databases and data
management systems. They plan the architecture, create data models, and determine
how data should be stored, accessed, and manipulated across systems. Their work
ensures that data is organized efficiently and scalable as business needs grow.
4. Data Analyst: Data Analysts are responsible for interpreting data stored in
databases to generate meaningful insights. They use SQL and other querying
languages to extract data, create reports, and analyze trends. Their insights help
guide business decisions and strategies. They typically work with BI tools and
reporting platforms for data visualization.
5. Data Scientist: A Data Scientist works with large datasets stored in databases and
applies machine learning, statistical models, and advanced analytics to uncover
patterns and predict future trends. They often extract and process raw data from
databases, clean it, and transform it for use in models or algorithms for data-driven
decision-making.
6. Business Intelligence (BI) Developer: A BI Developer designs and builds
systems that aggregate data from various sources, including databases, into data
warehouses or data marts. They create dashboards, reports, and visualizations that
enable business users to analyze trends and make data-driven decisions. They also
manage the ETL (Extract, Transform, Load) processes that prepare data for analysis.
7. Cloud Database Engineer: Cloud Database Engineers are responsible for
managing and optimizing databases in cloud environments such as AWS, Azure, or
Google Cloud. They ensure the database infrastructure is scalable, secure, and cost-
effective while ensuring proper integration with cloud services. They may also
handle database automation and monitoring in the cloud.
8. Big Data Engineer: Big Data Engineers specialize in the design and maintenance
of databases and systems capable of processing large volumes of data (big data).
They work with technologies like Hadoop, Spark, and NoSQL databases to manage
unstructured data and perform complex queries on distributed systems.
10. Database Security Specialist: Database Security Specialists ensure that
databases are protected from unauthorized access, data breaches, and cyber threats.
They implement encryption, access control policies, and auditing measures to
safeguard sensitive data. They also monitor databases for potential security risks and
comply with regulatory requirements like GDPR or HIPAA.
11. Database Tester (Quality Assurance): Database Testers, or Quality Assurance
specialists, focus on ensuring that databases function as expected. They test database
performance, data integrity, and security, and verify that database queries and
processes meet the required specifications. They also write test cases and ensure that
any issues are identified and resolved before deployment.
12. Application Developer: Application Developers build software applications
that interact with databases. They write code to create, read, update, and delete data
in databases through SQL or ORM (Object-Relational Mapping) tools. Their work
ensures that databases integrate seamlessly with the front-end and back-end
components of applications.

❖ Data Models in DBMS

Data Model is the modeling of the data description, data semantics, and consistency
constraints of the data. It provides the conceptual tools for describing the design of
a database at each level of data abstraction. Therefore, there are following four data
models used for understanding the structure of the database:
1) Relational Data Model: This type of model designs the data in the form of rows
and columns within a table. Thus, a relational model uses tables for representing data
and in-between relationships. Tables are also called relations. This model was
initially described by Edgar F. Codd, in 1969. The relational data model is the widely
used model which is primarily used by commercial data processing applications.
2) Entity-Relationship Data Model: An ER model is the logical representation of
data as objects and relationships among them. These objects are known as entities,
and relationship is an association among these entities. This model was designed by
Peter Chen and published in 1976 papers. It was widely used in database designing.
A set of attributes describe the entities. For example, student_name, student_id
describes the 'student' entity. A set of the same type of entities is known as an 'Entity
set', and the set of the same type of relationships is known as 'relationship set'.
3) Object-based Data Model: An extension of the ER model with notions of
functions, encapsulation, and object identity, as well. This model supports a rich type
system that includes structured and collection types. Thus, in 1980s, various
database systems following the object-oriented approach were developed. Here, the
objects are nothing but the data carrying its properties.
4) Semistructured Data Model: This type of data model is different from the other
three data models (explained above). The semistructured data model allows the data
specifications at places where the individual data items of the same type may have
different attributes sets. The Extensible Markup Language, also known as XML, is
widely used for representing the semistructured data. Although XML was initially
designed for including the markup information to the text document, it gains
importance because of its application in the exchange of data.

❖ What is Normalization in DBMS?

• Normalization is the process of organizing the data in the database.
• Normalization is used to minimize the redundancy from a relation or set of
relations. It is also used to eliminate undesirable characteristics like Insertion,
Update, and Deletion Anomalies.
• Normalization divides the larger table into smaller and links them using
relationships.
• The normal form is used to reduce redundancy from the database table.
➢ Advantages of Normalization
• Normalization helps to minimize data redundancy.
• Greater overall database organization.
• Data consistency within the database.
• Much more flexible database design.
• Enforces the concept of relational integrity.
➢ Disadvantages of Normalization
• You cannot start building the database before knowing what the user needs.
• The performance degrades when normalizing the relations to higher normal
forms, i.e., 4NF, 5NF.
• It is very time-consuming and difficult to normalize relations of a higher
degree.
• Careless decomposition may lead to a bad database design, leading to serious
problems.
➢ Types of Normal Forms:
Normalization works through a series of stages called Normal forms. The normal
forms apply to individual relations. The relation is said to be in particular normal
form if it satisfies constraints.
 Normal Form Description

1NF A relation is in 1NF if it contains an atomic value.

A relation will be in 2NF if it is in 1NF and all non-key attributes are fully
2NF
functional dependent on the primary key.

3NF A relation will be in 3NF if it is in 2NF and no transition dependency exists.

BCNF A stronger definition of 3NF is known as Boyce Codd's normal form.

A relation will be in 4NF if it is in Boyce Codd's normal form and has no multi-
4NF
valued dependency.

A relation is in 5NF. If it is in 4NF and does not contain any join dependency,
5NF
joining should be lossless.

1. First Normal Form (1NF)

o A relation will be 1NF if it contains an atomic value.
o It states that an attribute of a table cannot hold multiple values. It must hold
only single-valued attribute.
o First normal form disallows the multi-valued attribute, composite attribute,
and their combinations.

Example: Relation EMPLOYEE is not in 1NF because of multi-valued attribute

EMP_PHONE.
EMPLOYEE table:

EMP_ID EMP_NAME EMP_PHONE EMP_STATE

 7272826385,
14 John UP
9064738238

20 Harry 8574783832 Bihar

7390372389,
12 Sam Punjab
8589830302

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

EMP_ID EMP_NAME EMP_PHONE EMP_STATE

14 John 7272826385 UP

14 John 9064738238 UP

20 Harry 8574783832 Bihar

12 Sam 7390372389 Punjab

12 Sam 8589830302 Punjab

2. Second Normal Form (2NF)

o In the 2NF, relational must be in 1NF.
o In the second normal form, all non-key attributes are fully functional dependent
on the primary key

Example: Let's assume, a school can store the data of teachers and the subjects they
teach. In a school, a teacher can teach more than one subject.
TEACHER table

TEACHER_ID SUBJECT TEACHER_AGE

25 Chemistry 30
 25 Biology 30

47 English 35

83 Math 38

83 Computer 38

In the given table, non-prime attribute TEACHER_AGE is dependent on

TEACHER_ID which is a proper subset of a candidate key. That's why it violates the
rule for 2NF.
To convert the given table into 2NF, we decompose it into two tables:
TEACHER_DETAIL table:

TEACHER_ID TEACHER_AGE

25 30

47 35

83 38

TEACHER_SUBJECT table:

TEACHER_ID SUBJECT

25 Chemistry

25 Biology

47 English

83 Math
 83 Computer

3. Third Normal Form (3NF)

o A relation will be in 3NF if it is in 2NF and not contain any transitive partial
dependency.
o 3NF is used to reduce the data duplication. It is also used to achieve the data
integrity.
o If there is no transitive dependency for non-prime attributes, then the relation
must be in third normal form.

A relation is in third normal form if it holds atleast one of the following conditions for
every non-trivial function dependency X → Y.
1. X is a super key.
2. Y is a prime attribute, i.e., each element of Y is part of some candidate key.
Example:
EMPLOYEE_DETAIL table:

EMP_ID EMP_NAME EMP_ZIP EMP_STATE EMP_CITY

222 Harry 201010 UP Noida

333 Stephan 02228 US Boston

444 Lan 60007 US Chicago

555 Katharine 06389 UK Norwich

666 John 462007 MP Bhopal

Super key in the table above:

1. {EMP_ID}, {EMP_ID, EMP_NAME}, {EMP_ID, EMP_NAME, EMP_ZIP}..
..so on
Candidate key: {EMP_ID}
Non-prime attributes: In the given table, all attributes except EMP_ID are non-
prime.
Here, EMP_STATE & EMP_CITY dependent on EMP_ZIP and EMP_ZIP
dependent on EMP_ID. The non-prime attributes (EMP_STATE, EMP_CITY)
transitively dependent on super key(EMP_ID). It violates the rule of third normal
form.
That's why we need to move the EMP_CITY and EMP_STATE to the new
<EMPLOYEE_ZIP> table, with EMP_ZIP as a Primary key.
EMPLOYEE table:

EMP_ID EMP_NAME EMP_ZIP

222 Harry 201010

333 Stephan 02228

444 Lan 60007

555 Katharine 06389

666 John 462007

EMPLOYEE_ZIP table:

EMP_ZIP EMP_STATE EMP_CITY

201010 UP Noida

02228 US Boston

60007 US Chicago

06389 UK Norwich
 462007 MP Bhopal

4. Boyce Codd normal form (BCNF)

o BCNF is the advance version of 3NF. It is stricter than 3NF.
o A table is in BCNF if every functional dependency X → Y, X is the super key
of the table.
o For BCNF, the table should be in 3NF, and for every FD, LHS is super key.

Example: Let's assume there is a company where employees work in more than one
department.
EMPLOYEE table:

EMP_ID EMP_COUNTRY EMP_DEPT DEPT_TYPE EMP_DEPT_NO

264 India Designing D394 283

264 India Testing D394 300

364 UK Stores D283 232

364 UK Developing D283 549

In the above table Functional dependencies are as follows:

1. EMP_ID → EMP_COUNTRY
2. EMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}
Candidate key: {EMP-ID, EMP-DEPT}
The table is not in BCNF because neither EMP_DEPT nor EMP_ID alone are keys.
To convert the given table into BCNF, we decompose it into three tables:
EMP_COUNTRY table:

EMP_ID EMP_COUNTRY

264 India

264 India

EMP_DEPT table:
 EMP_DEPT DEPT_TYPE EMP_DEPT_NO

Designing D394 283

Testing D394 300

Stores D283 232

Developing D283 549

EMP_DEPT_MAPPING table:

EMP_ID EMP_DEPT

D394 283

D394 300

D283 232

D283 549

Functional dependencies:
1. EMP_ID → EMP_COUNTRY
2. EMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}
Candidate keys:
For the first table: EMP_ID
For the second table: EMP_DEPT
For the third table: {EMP_ID, EMP_DEPT}
Now, this is in BCNF because left side part of both the functional dependencies is a
key.
5. Fourth normal form (4NF)
o A relation will be in 4NF if it is in Boyce Codd normal form and has no multi-
valued dependency.
o For a dependency A → B, if for a single value of A, multiple values of B exists,
then the relation will be a multi-valued dependency.
Example
STUDENT

STU_ID COURSE HOBBY

21 Computer Dancing

21 Math Singing

34 Chemistry Dancing

74 Biology Cricket

59 Physics Hockey

The given STUDENT table is in 3NF, but the COURSE and HOBBY are two
independent entity. Hence, there is no relationship between COURSE and HOBBY.
In the STUDENT relation, a student with STU_ID, 21 contains two
courses, Computer and Math and two hobbies, Dancing and Singing. So there is a
Multi-valued dependency on STU_ID, which leads to unnecessary repetition of data.
So to make the above table into 4NF, we can decompose it into two tables:
STUDENT_COURSE

STU_ID COURSE

21 Computer

21 Math

34 Chemistry

74 Biology

59 Physics

STUDENT_HOBBY

STU_ID HOBBY
 21 Dancing

21 Singing

34 Dancing

74 Cricket

59 Hockey

6. Fifth normal form (5NF)

o A relation is in 5NF if it is in 4NF and not contains any join dependency and
joining should be lossless.
o 5NF is satisfied when all the tables are broken into as many tables as possible in
order to avoid redundancy.
o 5NF is also known as Project-join normal form (PJ/NF).

Example

SUBJECT LECTURER SEMESTER

Computer Anshika Semester 1

Computer John Semester 1

Math John Semester 1

Math Akash Semester 2

Chemistry Praveen Semester 1

In the above table, John takes both Computer and Math class for Semester 1 but he
doesn't take Math class for Semester 2. In this case, combination of all these fields
required to identify a valid data.
Suppose we add a new Semester as Semester 3 but do not know about the subject and
who will be taking that subject so we leave Lecturer and Subject as NULL. But all three
columns together acts as a primary key, so we can't leave other two columns blank.
So to make the above table into 5NF, we can decompose it into three relations P1, P2
& P3:
P1

SEMESTER SUBJECT

Semester 1 Computer

Semester 1 Math

Semester 1 Chemistry

Semester 2 Math

P2

SUBJECT LECTURER

Computer Anshika

Computer John

Math John

Math Akash

Chemistry Praveen

P3
 SEMSTER LECTURER

Semester 1 Anshika

Semester 1 John

Semester 1 John

Semester 2 Akash

Semester 1 Praveen

❖ Introduction of ER Model
Peter Chen developed the ER diagram in 1976. The ER model was created to
provide a simple and understandable model for representing the structure and logic
of databases. The Entity Relational Model is a model for identifying entities to be
represented in the database and representation of how those entities are related.
The ER data model specifies enterprise schema that represents the overall logical
structure of a database graphically.
The Entity Relationship Diagram explains the relationship among the entities
present in the database. ER models are used to model real-world objects like a
person, a car, or a company and the relation between these real-world objects. In
short, the ER Diagram is the structural format of the database.
➢ Why Use ER Diagrams In DBMS?
• ER diagrams represent the E-R model in a database, making them easy to
convert into relations (tables).
• ER diagrams provide the purpose of real-world modeling of objects which
makes them intently useful.
• ER diagrams require no technical knowledge and no hardware support.
• These diagrams are very easy to understand and easy to create even for a
naive user.
• It gives a standard solution for visualizing the data logically.
➢ 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:
• Rectangles: Rectangles represent Entities in the ER Model.
• Ellipses: Ellipses represent Attributes in the ER Model.
• Diamond: Diamonds represent Relationships among Entities.
• Lines: Lines represent attributes to entities and entity sets with other
relationship types.
• Double Ellipse: Double Ellipses represent Multi-Valued Attributes.
• Double Rectangle: Double Rectangle represents a Weak Entity.

Symbols used in ER Diagram

➢ Components of ER Diagram
ER Model consists of Entities, Attributes, and Relationships among Entities in a
Database System.
 Components of ER Diagram

1. Entity:
An entity may be any object, class, person or place. In the ER diagram, an entity can be
represented as rectangles.
Consider an organization as an example- manager, product, employee, department etc.
can be taken as an entity.

a. Weak Entity
An entity that depends on another entity called a weak entity. The weak entity doesn't
contain any key attribute of its own. The weak entity is represented by a double
rectangle.

2. Attribute
The attribute is used to describe the property of an entity. Eclipse is used to represent
an attribute.
For example, id, age, contact number, name, etc. can be attributes of a student.
a. Key Attribute
The key attribute is used to represent the main characteristics of an entity. It represents
a primary key. The key attribute is represented by an ellipse with the text underlined.

b. Composite Attribute
An attribute that composed of many other attributes is known as a composite attribute.
The composite attribute is represented by an ellipse, and those ellipses are connected
with an ellipse.
c. Multivalued Attribute
An attribute can have more than one value. These attributes are known as a multivalued
attribute. The double oval is used to represent multivalued attribute.
For example, a student can have more than one phone number.

d. Derived Attribute
An attribute that can be derived from other attribute is known as a derived attribute. It
can be represented by a dashed ellipse.
For example, A person's age changes over time and can be derived from another
attribute like Date of birth.
3. Relationship
A relationship is used to describe the relation between entities. Diamond or rhombus
is used to represent the relationship.

➢ Types of relationship are as follows:

a. One-to-One Relationship
When only one instance of an entity is associated with the relationship, then it is known
as one to one relationship.
For example, A female can marry to one male, and a male can marry to one female.

b. One-to-many relationship
When only one instance of the entity on the left, and more than one instance of an entity
on the right associates with the relationship then this is known as a one-to-many
relationship.
For example, Scientist can invent many inventions, but the invention is done by the
only specific scientist.

c. Many-to-one relationship
When more than one instance of the entity on the left, and only one instance of an entity
on the right associates with the relationship then it is known as a many-to-one
relationship.
For example, Student enrolls for only one course, but a course can have many students.

d. Many-to-many relationship
When more than one instance of the entity on the left, and more than one instance of an
entity on the right associates with the relationship then it is known as a many-to-many
relationship.
For example, Employee can assign by many projects and project can have many
employees.

❖ Relational Model in DBMS

Relational model makes the query much easier than in hierarchical or network
database systems. In 1970, E.F Codd has been developed it. A relational database is
defined as a group of independent tables which are linked to each other using some
common fields of each related table. This model can be represented as a table with
columns and rows. Each row is known as a tuple. Each table of the column has a
name or attribute. It is well knows in database technology because it is usually used
to represent real-world objects and the relationships between them. Some popular
relational databases are used nowadays like Oracle, Sybase, DB2, MySQL Server
etc.

Relational Model Terminologies:

Following are the terminologies of Relational Model:

Relation Table

Tuple Row, Record

 Attribute Column, Field

Domain It consists of set of legal values

Cardinality It consists of number of rows

Degree It contains number of columns

Let's explain each term one by one in detail with the help of example:
Example: STUDENT Relation

Stu_No S_Name PHONE_NO ADDRESS Gender

10112 Rama 9874567891 Islam ganj F

12839 Shyam 9026288936 Delhi M

33289 Laxman 8583287182 Gurugram M

27857 Mahesh 7086819134 Ghaziabad M

17282 Ganesh 9028939884 Delhi M

Relation: A relation is usually represented as a table, organized into rows and

columns. A relationship consists of multiple records. For example: student relation
which contains tuples and attributes.
Tuple: The rows of a relation that contain the values corresponding to the attributes
are called tuples. For example: in the Student relation there are 5 tuples.
The value of tuples contains (10112, Rama, 9874567891,islam ganj, F) etc.
Data Item: The smallest unit of data in the relation is the individual data item. It is
stored at the intersection of rows and columns are also known as cells. For
Example: 10112, "Rama" etc are data items in Student relation.
Domain: It contains a set of atomic values that an attribute can take. It could be
accomplish explicitly by listing all possible values or specifying conditions that all
values in that domain must be confirmed. For example: the domain of gender
attributes is a set of data values "M" for male and "F" for female. No database
software fully supports domains typically allowing the users to define very simple
data types such as numbers, dates, characters etc.
Attribute: The smallest unit of data in relational model is an attribute. It contains
the name of a column in a particular table. Each attribute Ai must have a domain,
dom(Ai). For example: Stu_No, S_Name, PHONE_NO, ADDRESS, Gender are
the attributes of a student relation. In relational databases a column entry in any row
is a single value that contains exactly one item only.
Cardinality: The total number of rows at a time in a relation is called the cardinality
of that relation. For example: In a student relation, the total number of tuples in this
relation is3 so the cardinality of a relation is 3. The cardinality of a relation changes
with time as more and more tuples get added or deleted.
Degree: The degree of association is called the total number of attributes in a
relationship. The relation with one attribute is called unary relation, with two
attributes is known a binary relation and with three attributes is known as ternary
relation. For example: in the Student relation, the total number of attributes is 5, so
the degree of the relations is 5. The degree of a relation does not change with time
as tuples get added or deleted.
Relational instance: In the relational database system, the relational instance is
represented by a finite set of tuples. Relation instances do not have duplicate tuples.
Relational schema: A relational schema contains the name of the relation and name
of all columns or attributes.
Relational key: In the relational key, each row has one or more attributes. It can
identify the row in the relation uniquely.

❖ Integrity Constraints
• Integrity constraints are a set of rules. It is used to maintain the quality of
information.
 • Integrity constraints ensure that the data insertion, updating, and other processes
have to be performed in such a way that data integrity is not affected.
• Thus, integrity constraint is used to guard against accidental damage to the
database.
➢ Types of Integrity Constraint

1. Domain constraints
o Domain constraints can be defined as the definition of a valid set of values
for an attribute.
o The data type of domain includes string, character, integer, time, date,
currency, etc. The value of the attribute must be available in the corresponding
domain.

Example:

2. Entity integrity constraints

o The entity integrity constraint states that primary key value can't be null.
 o This is because the primary key value is used to identify individual rows in
relation and if the primary key has a null value, then we can't identify those
rows.
o A table can contain a null value other than the primary key field.

Example:

3. Referential Integrity Constraints

o A referential integrity constraint is specified between two tables.
o In the Referential integrity constraints, if a foreign key in Table 1 refers to the
Primary Key of Table 2, then every value of the Foreign Key in Table 1 must
be null or be available in Table 2.

Example:
4. Key constraints
o Keys are the entity set that is used to identify an entity within its entity set
uniquely.
o An entity set can have multiple keys, but out of which one key will be the
primary key. A primary key can contain a unique and null value in the
relational table.

Example:

❖ SQL Aggregate functions

What is SQL?
• SQL stands for Structured Query Language
• SQL lets you access and manipulate databases
• SQL became a standard of the American National Standards Institute (ANSI)
in 1986, and of the International Organization for Standardization (ISO) in
1987
What Can SQL do?
• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
 • SQL can create views in a database
• SQL can set permissions on tables, procedures, and views
An aggregate function is a function that performs a calculation on a set of values,
and returns a single value.
Aggregate functions are often used with the GROUP BY clause of
the SELECT statement. The GROUP BY clause splits the result-set into groups of
values and the aggregate function can be used to return a single value for each group.
The most commonly used SQL aggregate functions are:
• MIN() - returns the smallest value within the selected column
• MAX() - returns the largest value within the selected column
• COUNT() - returns the number of rows in a set
• SUM() - returns the total sum of a numerical column
• AVG() - returns the average value of a numerical column
Aggregate Functions in SQL
Below is the list of SQL aggregate functions, with examples
1. Count():
• Count(*): Returns the total number of records .i.e 6.
• Count(salary): Return the number of Non-Null values over the column salary.
i.e 5.
• Count(Distinct Salary): Return the number of distinct Non-Null values over
the column salary .i.e 5.
2. Sum():
• sum(salary): Sum all Non-Null values of Column salary i.e., 3120.
• sum(Distinct salary): Sum of all distinct Non-Null values i.e., 3120..
3. Avg():
• Avg(salary) = Sum(salary) / count(salary) = 3120 / 5 = 624
• Avg(Distinct salary) = sum(Distinct salary) / Count(Distinct Salary) = 3120 /
5 = 624
4. Min():
• Min(salary): Minimum value in the salary column except NULL i.e., 403.
 5. Max():
• Max(salary): Maximum value in the salary i.e., 802.