22CS2403

Database Management System

Scheme
Syllabus
Syllabus
Syllabus
CIE Mark Distribution
Components Maximum Marks Reduced To
MSE 1 40 15
MSE 2 40 15
AAT 10 10
Lab Exam
Writeup – 10
20 10
Output – 5
Viva - 5
Lab Record 10 10
Total 60
Course Outcome
Why DBMS?

• A solid understanding of DBMS is crucial as it provides you with

essential skills and knowledge needed for effective data
management, system design, collaboration, decision support,
and overall success in your future engineering careers.
Why DBMS?
• Data Management Skills:
• Why: Engineering projects involve handling and analyzing large datasets.
• How DBMS Helps: DBMS teaches students how to efficiently organize, store, and retrieve data, fostering valuable
data management skills.

• System Design and Implementation:

• Why: Engineers design and implement systems that require effective data storage and retrieval mechanisms.
• How DBMS Helps: DBMS principles are fundamental to system design, helping students understand how to
structure data for optimal system performance.

• Real-world Application:
• Why: Practical applications in various engineering fields often involve managing complex datasets.
• How DBMS Helps: DBMS provides hands-on experience with real-world applications, preparing students for
challenges they may encounter in their engineering careers.
Why DBMS?
• Collaboration and Concurrency:
• Why: Engineering projects often involve multiple team members working simultaneously.
• How DBMS Helps: DBMS teaches students about concurrent access and collaboration, crucial skills for
working on team projects and preventing data conflicts.

• Decision Support:
• Why: Engineers need to make informed decisions based on data analysis.
• How DBMS Helps: DBMS facilitates efficient data retrieval and analysis, enabling engineers to make informed
decisions supported by accurate and up-to-date information.

• Security Awareness:
• Why: Protecting sensitive information is critical in engineering projects.
• How DBMS Helps: DBMS introduces students to data security concepts, such as access control and
encryption, ensuring they understand how to safeguard information.
Why DBMS?
• Project Management:
• Why: Successful project management involves effective organization and utilization of project data.
• How DBMS Helps: DBMS skills contribute to efficient project management by ensuring data integrity, accessibility,
and consistency throughout the project lifecycle.

• Adaptability to Technology Trends:

• Why: The field of database technology evolves rapidly.
• How DBMS Helps: Learning DBMS equips engineering students with adaptable skills, allowing them to stay current
with emerging database technologies and trends.

• Preparation for Specialized Engineering Fields:

• Why: Different engineering disciplines, such as civil, electrical, or mechanical, often have unique data requirements.
• How DBMS Helps: DBMS education provides a foundation that can be tailored to the specific data needs of various
engineering specialties.
Unit 1

• INTRODUCTION TO DATABASE SYSTEMS :

• Introduction, Characteristics of the Database Approach, Advantages of using

DBMS Approach, Data Models, Schemas, Instances and Data Independence,
Three Schema Architecture, various components of a DBMS.

• ENTITY-RELATIONSHIP MODEL:

• Entity Types , Entity Sets , Attributes and Keys, Relationship types,

Relationship Sets , Roles and Structural Constraints; Weak Entity Types; ER
Diagrams
Basic Definitions
• Database:
• A collection of related data.
• Data:
• Data is a collection of information gathered by observations, measurements, research or
analysis
• Database Management System (DBMS):
• A software package/ system to facilitate the creation and maintenance of a computerized
database.
• Database System:
• The DBMS software together with the data itself. Sometimes, the applications are also included.
Popular DBMSs
Introduction

• Implicit properties:
• A database represents some aspect of the real world, sometimes called the
miniworld or the universe of discourse (UoD). Changes to the miniworld are reflected
in the database.
• A database is a logically coherent collection of data with some inherent meaning. A
random assortment of data cannot correctly be referred to as a database.
• A database is designed, built, and populated with data for a specific purpose. It has
an intended group of users and some preconceived applications in which these users
are interested.
Introduction

• A database can be of any size and complexity.

• A database may be generated and maintained manually or it

may be computerized.

• Defining a database involves specifying the data types,

structures, and constraints of the data to be stored in the
database.
Introduction

• The DBMS is a general-purpose software system that facilitates the

processes of defining, constructing, manipulating, and sharing databases
among various users and applications.

• The database definition or descriptive information is also stored by the

DBMS in the form of a database catalog or dictionary; it is called meta-data.

• Constructing the database is the process of storing the data on some

storage medium that is controlled by the DBMS.
Introduction

• Manipulating a database includes functions such as querying the database to retrieve

specific data, updating the database to reflect changes in the miniworld, and generating
reports from the data.

• Sharing a database allows multiple users and programs to access the database
simultaneously.

• An application program accesses the database by sending queries or requests for data
to the DBMS.

• A query typically causes some data to be retrieved; a transaction may cause some data
to be read and some data to be written into the database.
Introduction
• A simplified database system environment.
Introduction

• Additional DBMS Functionality

• DBMS may additionally provide:

• Protection or Security measures to prevent unauthorized access
• “Active” processing to take internal actions on data
• Presentation and Visualization of data
• Maintenance of the database and associated programs over the lifetime of
the database application
• Called database, software, and system maintenance
Example

• Mini-world for the example:

• Part of a UNIVERSITY environment.
• Some mini-world entities:
• STUDENTs
• COURSEs
• SECTIONs (of COURSEs)
• (academic) DEPARTMENTs
• INSTRUCTORs
Example

• Some mini-world relationships:

• SECTIONs are of specific COURSEs

• STUDENTs take SECTIONs

• COURSEs have prerequisite COURSEs

• INSTRUCTORs teachSECTIONs

• COURSEs are offered by DEPARTMENTs

• STUDENTs major in DEPARTMENTs
Example
Quiz

• Which of the following is NOT a key component of a typical

DBMS architecture?

• A) Database

• B) Application Programs

• C) Computer Hardware

• D) Users
Quiz

• Which of the following is NOT a key component of a typical

DBMS architecture?

• A) Database

• B) Application Programs

• C) Computer Hardware

• D) Users
Quiz

• What is the role of metadata in a database, and how does it

contribute to effective data management?

• A) Metadata is irrelevant to database management.

• B) Metadata provides security for the database.

• C) Metadata describes and provides information about data in the

database.

• D) Metadata is primarily used for data encryption.

Quiz

• What is the role of metadata in a database, and how does it

contribute to effective data management?

• A) Metadata is irrelevant to database management.

• B) Metadata provides security for the database.

• C) Metadata describes and provides information about data in the

database.

• D) Metadata is primarily used for data encryption.

Characteristics of the Database Approach

• The main characteristics of the database approach versus the

file-processing approach
• Self-describing nature of a database system

• Insulation between programs and data, and data abstraction

• Support of multiple views of the data

• Sharing of data and multi user transaction processing

Characteristics of the Database Approach
• Self-describing nature of a database system:
• A DBMS catalog stores the description of a particular database (e.g. data structures, types, and constraints)
• The description is called meta-data*.
• This allows the DBMS software to work with different database applications.

• Insulation between programs and data:

• Called program-data independence.
• Allows changing data structures and storage organization without having to change the DBMS access programs.

• * Some newer systems such as a few NOSQL systems need no meta-data: they store the data definition within its
structure making it self describing
Characteristics of the Database Approach
• Catalog for University Database
Characteristics of the Database Approach

• Data Abstraction:
• A data model is used to hide storage details and present the users with a
conceptual view of the database.
• Programs refer to the data model constructs rather than data storage
details
• Support of multiple views of the data:
• Each user may see a different view of the database, which
describes only the data of interest to that user.
Characteristics of the Database Approach

• Sharing of data and multi-user transaction processing:

• Allowing a set of concurrent users to retrieve from and to update the database.
• Concurrency control within the DBMS guarantees that each

• transaction is correctly executed or aborted

• Recovery subsystem ensures each completed transaction has its effect
permanently recorded in the database
• OLTP (Online Transaction Processing) is a major part of database applications.
This allows hundreds of concurrent transactions to execute per second.
Quiz

• What is a key characteristic of the Database Approach?

• A) Redundancy

• B) Data Independence

• C) Limited Storage

• D) No Relationships
Quiz

• What is a key characteristic of the Database Approach?

• A) Redundancy

• B) Data Independence

• C) Limited Storage

• D) No Relationships
Quiz

• Why is data redundancy considered a disadvantage in

traditional file processing systems?

• A) It simplifies data retrieval

• B) It reduces storage space

• C) It may lead to inconsistencies and errors

• D) It improves data security

Quiz

• Why is data redundancy considered a disadvantage in

traditional file processing systems?

• A) It simplifies data retrieval

• B) It reduces storage space

• C) It may lead to inconsistencies and errors

• D) It improves data security

Advantages of Using DBMS Approach

• Controlling Redundancy
• Redundancy - storing the same data multiple times. Leads to
duplication of effort, storage space waste, inconsistency,.
• In the database approach, the views of different user groups are
integrated during database design.
• Stores each logical data item in only one place in the database.

• Data normalization - ensures consistency and saves storage space

Advantages of Using DBMS Approach

• Restricting Unauthorized Access

• A DBMS should provide a security and authorization subsystem.

• Providing Persistent Storage for Program Objects

• Databases can be used to provide persistent storage for program objects and data structures.

This is one of the main reasons for object-oriented database systems

• Providing Storage Structures and Search Techniques for Efficient Query Processing
• Database systems must provide capabilities for efficiently executing queries and updates.

Example – Indexes, buffering or caching module, query processing and optimization module
Advantages of Using DBMS Approach

• Providing Backup and Recovery

• A DBMS must provide facilities for recovering from hardware or software failures.

• Providing Multiple User Interfaces

• Because many types of users with varying levels of technical knowledge use a database, a DBMS should

provide a variety of user interfaces.

• Representing Complex Relationships among Data

• A DBMS must have the capability to represent a variety of complex relationships among the data, to define

new relationships as they arise, and to retrieve and update related data easily and efficiently.

• Enforcing Integrity Constraints

• Referential integrity constraint, key or uniqueness constraint
Advantages of Using DBMS Approach

• Permitting Inferencing and Actions Using Rules and Triggers

• Deductive database systems - provide capabilities for defining deduction rules
for inferencing new information from the stored database facts.
• A trigger is a form of a rule activated by updates to the table, which results in
performing some additional operations to some other tables, sending
messages, and so on.
• More involved procedures to enforce rules are popularly called stored
procedures;
Advantages of Using DBMS Approach

• Additional Implications of Using the Database Approach

• Potential for enforcing standards:
• This is very crucial for the success of database applications in large
organizations. Standards refer to data item names, display formats,
screens, report structures, meta-data (description of data), Web page
layouts, etc.

• Reduced application development time:

• Incremental time to add each new application is reduced.
Advantages of Using DBMS Approach

• Additional Implications of Using the Database Approach

• Flexibility to change data structures:
• Database structure may evolve as new requirements are
defined.
• Availability of current information:
• Extremely important for on-line transaction systems such as
shopping, airline, hotel, car reservations.
• Economies of scale:
• Wasteful overlap of resources and personnel can be avoided by
consolidating data and applications across departments.
Quiz

• What is a primary advantage of using a DBMS in a business

environment?

• A) Decreased data security

• B) Limited data accessibility

• C) Improved data organization

• D) Reduced scalability
Quiz

• What is a primary advantage of using a DBMS in a business

environment?

• A) Decreased data security

• B) Limited data accessibility

• C) Improved data organization

• D) Reduced scalability
Quiz

• How does a DBMS contribute to data security in

comparison to traditional file systems?

• A) It exposes data to external threats

• B) It lacks authentication mechanisms

• C) It provides access control and authentication features

• D) It stores data in plain text

Quiz

• How does a DBMS contribute to data security in

comparison to traditional file systems?

• A) It exposes data to external threats

• B) It lacks authentication mechanisms

• C) It provides access control and authentication features

• D) It stores data in plain text

Data Models

• Data Model:
• A set of concepts to describe the structure of a database, the operations for
manipulating these structures, and certain constraints that the database should
obey.
• Data Model gives us an idea that how the final system will look like after its complete
implementation.
• It defines the data elements and the relationships between the data elements.
• Data Models are used to show how data is stored, connected, accessed and
updated in the database management system.
Data Models

• Data Model Structure and Constraints:

• Constructs are used to define the database structure

• Constructs typically include elements (and their data types) as well as

groups of elements (e.g. entity, record, table), and relationships among
such groups
• Constraints specify some restrictions on valid data; these constraints must
be enforced at all times
Data Models

• Data Model Operations:

• These operations are used for specifying database retrievals
and updates by referring to the constructs of the data model.
• Operations on the data model may include basic model
operations (e.g. generic insert, delete, update) and user-
defined operations (e.g. compute_student_gpa,
update_inventory)
Data Models

• Categories
• Conceptual (high-level, semantic) data models:
• Provide concepts that are close to the way many users perceive data.
• (Also called entity-based or object-based data models.)

• Physical (low-level, internal) data models:

• Provide concepts that describe details of how data is stored in the computer.

• Here, we basically save tables in memory so they can be accessed efficiently.

• Implementation (representational) data models:

• Provide concepts that fall between the above two, used by many commercial DBMS implementations (e.g. relational data

models used in many commercial systems).

• Self-Describing Data Models:

• Combine the description of data with the data values. Examples include XML, key-value stores and some NOSQL systems.
Quiz

• What is the purpose of a data model in database

management?

• A) To design user interfaces

• B)To create visualizations

• C) To represent the structure and relationships of data

• D) To manage computer hardware

Quiz

• What is the purpose of a data model in database

management?

• A) To design user interfaces

• B)To create visualizations

• C) To represent the structure and relationships of data

• D) To manage computer hardware

Schema
• Database Schema:
• The description of a database.
• Includes descriptions of the database structure, data types, and the constraints on the
database.
• Schema Diagram:
• An illustrative display of (most aspects of) a database schema.
• Schema Construct:
• A component of the schema or an object within the schema, e.g., STUDENT,
COURSE.
Schema

• Schema Diagram
Quiz

• What is the primary purpose of a database schema?

• A) To execute SQL queries

• B) To secure the database

• C) To define the structure of the database

• D) To create graphical user interfaces

Quiz

• What is the primary purpose of a database schema?

• A) To execute SQL queries

• B) To secure the database

• C) To define the structure of the database

• D) To create graphical user interfaces

Instance

• Database State:
• The actual data stored in a database at a particular
moment in time. This includes the collection of all the data
in the database.
• Also called database instance (or occurrence or snapshot).
• The term instance is also applied to individual database
components, e.g. record instance, table instance, entity instance
Data Independence

• The capacity to change the schema at one level of a database system without having to

change the schema at the next higher level.

• Logical Data Independence:
• The capacity to change the conceptual schema without having to change the external

schemas and their associated application programs.

• Physical Data Independence:

• The capacity to change the internal schema without having to change the conceptual schema.

• For example, the internal schema may be changed when certain file structures are reorganized or

new indexes are created to improve database performance

• External Schema (External Level): This is the highest level of abstraction and is concerned with how the data
appears to specific users or applications. It defines the logical structure and organization of data as seen by each
user group or application, hiding unnecessary details and providing a simplified and tailored view of the data.

• Conceptual Schema (Conceptual Level): This level represents the overall logical structure of the entire
database. It defines the entities, their attributes, and the relationships between them, without considering how
the data is physically stored or accessed. The conceptual schema provides a global view of the database that is
independent of any specific application or user perspective.

• Internal Schema (Internal Level): This is the lowest level of abstraction and deals with the physical storage of
data on the storage devices. It defines how data is stored, indexed, and organized within the database system,
including details such as data storage formats, indexing mechanisms, and access paths. The internal schema is
concerned with optimizing performance and storage efficiency.
Data Independence
Quiz

• What does the term "data independence" refer to in the

context of a Database Management System (DBMS)?

• A) The ability to store data in an independent format

• B)The separation of data from the application programs

• C) The dependence of data on external sources

• D) The integration of data with the operating system

Quiz

• What does the term "data independence" refer to in the

context of a Database Management System (DBMS)?

• A) The ability to store data in an independent format

• B)The separation of data from the application programs

• C) The dependence of data on external sources

• D) The integration of data with the operating system

Quiz

• How does data independence contribute to the flexibility of a

database system?

• A) It simplifies data retrieval

• B) It enables dynamic data encryption

• C) It reduces the need for data normalization

• D) It allows changes to the data structure without affecting applications

Quiz

• How does data independence contribute to the flexibility of a

database system?

• A) It simplifies data retrieval

• B) It enables dynamic data encryption

• C) It reduces the need for data normalization

• D) It allows changes to the data structure without affecting applications

Three-Schema Architecture

• Proposed to support DBMS characteristics of:

• Program-data independence.
• Support of multiple views of the data.

• Not explicitly used in commercial DBMS products, but has

been useful in explaining database system organization
Three-Schema Architecture

• Defines DBMS schemas at three levels:

• Internal schema at the internal level to describe physical storage structures and
access paths (e.g indexes).
• Typically uses a physical data model.
• Conceptual schema at the conceptual level to describe the structure and
constraints for the whole database for a community of users.
• Uses a conceptual or an implementation data model.
• External schemas at the external level to describe the various user views.
• Usually uses the same data model as the conceptual schema.
Three-Schema Architecture
Three-Schema Architecture

• Mappings among schema levels are needed to transform

requests and data.
• Programs refer to an external schema, and are mapped by
the DBMS to the internal schema for execution.
• Data extracted from the internal DBMS level is reformatted to
match the user’s external view (e.g. formatting the results of an
SQL query for display in a Web page)
Various Components of a DBMS
Various Components of a DBMS

• Parts

• The top part of the figure refers to the various users of the
database environment and their interfaces.

• The lower part shows the internal modules of the DBMS

responsible for storage of data and processing of transactions.
Various Components of a DBMS

• The database and the DBMS catalog are usually stored on disk.

• Access to the disk is controlled primarily by the operating system (OS),

which schedules disk read/write.

• Buffer management module - schedule disk read/write.

• A higher-level stored data manager module of the DBMS controls access

to DBMS information that is stored on disk, whether it is part of the
database or the catalog.
Various Components of a DBMS

• The DBA staff works on defining the database and tuning it by making changes to its

definition using the DDL and other privileged commands.

• Casual users and persons with occasional need for information from the database

interact using the interactive query interface.

• Application programmers who create programs using some host programming

languages

• Parametric users who do data entry work by supplying parameters to predefined

transactions.
Various Components of a DBMS

• The DDL compiler processes schema definitions, specified in the

DDL, and stores descriptions of the schemas (meta-data) in the
DBMS catalog.

• The catalog includes information such as the names and sizes of

files, names and data types of data items, storage details of each
file, mapping information among schemas, and constraints.
Various Components of a DBMS

• Query compiler – compiles queries into an internal form.

• Query optimizer - concerned with the rearrangement and

possible reordering of operations, elimination of redundancies,
and use of efficient search algorithms during execution.

• Precompiler - extracts DML commands from an application

program written in a host programming language.
Various Components of a DBMS

• Runtime database processor executes

• (1) the privileged commands

• (2) the executable query plans

• (3) the canned transactions with runtime parameters.

• Concurrency control and backup and recovery systems are

integrated into the working of the runtime database processor for
purposes of transaction management.
Entity Relationship Model
• ER model develops a conceptual view of the data hence it can be used as a blueprint to implement the

database in the future.

• Developers can easily understand the system just by looking at ER diagram.

• Components of an ER diagram.
• Entity - Anything that has an independent existence about which we collect the data. To learn more about Entity in

DBMS click here.

• They are represented as rectangles in the ER diagram. For example - Car, house, employee.

• Entity Set - A set of the same type of entities is known as an entity set. For example - Set of students studying in a

college.

• Attributes - Properties that define entities are called attributes. They are represented by an ellipse shape.

• Relationships - A relationship in DBMS is used to describe the association between entities. They are represented as
Entity Types
• A database can be modeled as:

• a collection of entities,

• relationship among entities.

• Entity : An entity is an object that exists and is distinguishable from other objects.

• Example: specific person, company, event, course.

Entity Sets

• The collection of all entities of a particular entity type in the

database at any point in time is called an entity set or entity
collection.
• The entity set is usually referred to using the same name as the
entity type, even though they are two separate concepts.
Entity Sets

• Two entity types, EMPLOYEE and COMPANY, and some

member entities of each.
Attributes
• Entities have attributes

• Example: people have names and addresses

• Attributes : Attributes are characteristics of entity.

Example:
• Student Attributes : roll no , studentname

• Project Attributes : projectno , projectname ,Dept.

Attributes

• An entity is represented by a set of attributes, that is descriptive

properties possessed by all members of an entity set.

Example:
1) customer = (customer-id, customer-name,
customer-street, customer-city)

2) loan = (loan-number, amount)

Attributes
Attributes

• Simple Attribute
Simple attributes are those that are atomic (Not Divisible).

Example: Roll_no, Class and Age.

Attributes

• Composite Attributes
• Composite attributes are those that can be further subdivided into simpler parts. Here, the
attributes “Name” and “Address” are composite attributes as they are composed of many other
simple attributes.
Attributes

• Single Valued Attribute

• Single valued attributes are those attributes which can take only one value for a given entity from
an entity set.

• Here, all the attributes are single valued attributes as they can take only one specific value for each
entity.
Attributes

• Multi-Valued Attributes
• Multi valued attributes are those attributes which can take more than one value for a given entity from an
entity set.

• Here, the attributes “Mob_no” and “Email_id” are multi valued attributes as they can take more than one
values for a given entity.
Attributes

• Derived Attributes
• Derived attributes are those attributes which can be derived from other attribute(s).

• Here, the attribute “Age” is a derived attribute as it can be derived from the attribute
“DOB”.
Attributes

• Key Attributes
• Key attributes are those attributes which can identify an entity uniquely in an entity set.

• Here, the attribute “Roll_no” is a key attribute as it can identify any student uniquely.
Attributes
• Domain:
• Domain of an attribute is the set of all values that the attribute can take.

Ex : AGE of an employee can take values from 20 to 60 .

Domain set of AGE attribute for the employee is {20,21. …. 59,60}.

Entity - EMPLOYEE

Attributes - ENO,ENAME,AGE,DNO

Domain of AGE - {34,45,26,24}

Relationship Types and Sets

• A relationship type R among n entity types E 1, E2, . . . , En defines a set of

associations—or a relationship set—among entities from these entity types.

• Similar to the case of entity types and entity sets, a relationship type and its
corresponding relationship set are customarily referred to by the same name, R.

• Mathematically, the relationship set R is a set of relationship instances r i, where

each ri associates n individual entities (e 1, e2, . . . , en), and each entity ej in ri is a

member of entity set Ej , 1 ≤ j ≤ n.

Relationship Types and Sets
• Some instances in the WORKS_FOR relationship set, which represents a relationship type WORKS_FOR
between EMPLOYEE and DEPARTMENT.
Relationship Types and Sets
Relationship Types and Sets

• The relationship can be described by certain characteristics.

• Degree of the relationship.

• Role.

• Recursive Relationship.

• Cardinality.

• Participation Constraint.
Relationship Types and Sets

• Degree of The Relationship

• The total number of entities participating in the relation is called degree of the
Relationship.

[Link] Relationship.

2. Binary Relationship.

3. Ternary Relationship.
Relationship Types and Sets

• Unary Relationship
• When there is a relationship between two entities of the same type, it is known as a unary or recursive
relationship. This means that the relationship is between different instances of the same entity type.

• When both participants in the relationship are the same entity.

Ex: Subjects may be prerequisites for other subjects

Relationship Types and Sets

• Binary Relationship

• When there is a relationship between two different entities, it is

known as a binary relationship.
Relationship Types and Sets

• Ternary Relationship
• When there is a relationship between three different entities, it is known as a ternary
relationship. An example of a ternary relationship can be shown as follows
Relationship Types and Sets

• Role
• Role is the function an entity plays in the relationship.

• Roles are optional.

• They help in clarifying the semantics of a relationship.

• EMPLOYEE entity is playing the role of worker.

Relationship Types and Sets

• Recursive Relationship
• When the relationship occurs within the same [Link] is called Recursive Relationship.

• An entity related to itself is the recursive relationship.

• The same entity performs different roles.

• EMPLOYEE entity is playing the role of worker.

• EMPLOYEE entity is playing the role of manager.

Mapping Cardinalities

• The number of instances of an entity in the entity set related

with the number of instances of another entity in its entity set
through a relationship set is called Cardinality ratio.

• Express the number of entities to which another entity can be

associated via a relationship set.

• Most useful in describing binary relationship sets.

Cardinality Ratios
Mapping Cardinalities

One to One to
one many
Mapping Cardinalities

Many to Many to
one many
Cardinality Ratio

One to One (1:1) : one instance of an entity is related with one

instance of another entity is called One-to-One relationship.

Cardinality ratio is 1:1

Cardinality Ratio

• One to Many (1:M) : one instance of an entity is related with many

instance of another entity is called One-to-Many relationship.

• Cardinality ratio is 1:M

Cardinality Ratio

• Many to One (N:1) : Many instances of an entity is related with one

instance of another entity is called Many-to-One relationship.

• Cardinality ratio is N:1

Cardinality Ratio
Many to Many (M:N) : Many instances of an entity is related
with Many instances of another entity is called Many-to-Many
relationship.

Cardinality ratio is M:N

 Participation Constraint

• Participation Constraint : Participation Constraint describe the participation of an entity in the relationship
set.

• Participation is of Two Types

⮚ Total Participation
⮚ Partial Participation

• Total Participation − Each entity is involved in the relationship. Total participation is represented by
double lines.

• Partial participation − Not all entities are involved in the relationship. Partial participation is represented
by single lines.
•Double line between the entity set “Student” and
relationship set “Enrolled in” signifies total
participation.
•It specifies that each student must be enrolled in at
least one course.
• Single line between the entity set “Course” and relationship set “Enrolled in” signifies partial participation.

• It specifies that there might exist some courses for which no enrollments are made.
Structural Constraints

• cardinality ratio and participation constraints, taken together, as

the structural constraints of a relationship type.
Notations of ER Diagram
Primary a Key

• What is a Primary Key?

• A primary key is a unique identifier for a specific row in a table

in a database. It is used to ensure data integrity and to prevent
duplicate entries. It can be one column or a combination of
multiple columns.
Foreign Key

• What is a Foreign Key?

• A foreign key is a field in a database table that is used to

uniquely identify a row in another table. Foreign keys are used
to establish and enforce relationships between tables in a
database, such as the relationship between a customer and
an order or a product and a supplier.
ER Diagram

• The CAR entity type with two key attributes, Registration and
Vehicle_id.
(a) ER diagram notation. (b) Entity set with three entities.
ER Diagram

• Preliminary design of entity types for the COMPANY database.

Some of the shown attributes will be refined into relationships.
ER Diagram
• ER diagrams for the
company schema, with
structural constraints
specified using (min,
max) notation and role
names.
Weak Entity Types
• An entity that does not have a key attribute and that is identification- dependent on another entity type.
• A weak entity must participate in an identifying relationship type with an owner or identifying entity
type
• Entities are identified by the combination of:
• A partial key of the weak entity type
• The particular entity they are related to in the identifying relationship type
• Example:
• A DEPENDENT entity is identified by the dependent’s first name, and the specific EMPLOYEE with whom the dependent
is related
• Name of DEPENDENT is the partial key
• DEPENDENT is a weak entity type
• EMPLOYEE is its identifying entity type via the identifying relationship type DEPENDENT_OF
Practice
• Library Management System

• Entity Identification:
• Draw an ER diagram for a Library Management System. Identify the main entities and their attributes.

• Relationships:
• Establish relationships between entities such as "Book," "Author," and "Borrower" in the context of a Library
Management System.

• Attributes and Keys:

• Define the attributes for entities like "Book" and determine the primary key for each entity.

• Cardinality and Participation Constraints:

• Specify the cardinality and participation constraints for relationships like "Borrower borrows Book" and "Book written
by Author."
Practice
• Hospital Management System:

• Entity Identification:
• Draw an ER diagram for a Hospital Management System. Identify the main entities, such as "Patient," "Doctor," and "Appointment,"
along with their attributes.

• Relationships:
• Establish relationships between entities in the context of a Hospital Management System, considering connections like "Patient is
treated by Doctor" and "Appointment scheduled for Patient."

• Attributes and Keys:

• Define the attributes for entities like "Patient" and determine the primary key for each entity in the Hospital Management System.

• Cardinality and Participation Constraints:

• Specify the cardinality and participation constraints for relationships like "Doctor treats Patient" and "Patient has multiple
Appointments."
Practice
• Online Shopping System:

• Entity Identification:
• Draw an ER diagram for an Online Shopping System. Identify the main entities, such as "Customer," "Product," and "Order," along
with their attributes.

• Relationships:
• Establish relationships between entities in the context of an Online Shopping System, considering connections like "Customer
places Order" and "Product is part of Order."

• Attributes and Keys:

• Define the attributes for entities like "Product" and determine the primary key for each entity in the Online Shopping System.

• Cardinality and Participation Constraints:

• Specify the cardinality and participation constraints for relationships like "Customer places multiple Orders" and "Order contains
multiple Products."
Identifying the Relations