MODULE – I DATABASE SYSTEM CONCEPTS AND ARCHITECTURE

Database Systems: Overview of DBMS -Three tier Architecture -Data Models & its types- Entity-
Relationship (E/R) Model: Entities, Entity Types, Attributes, Relationships- Relationship Types
And E/R Diagram Notation- Extended ER Model: Generalization – Specialization – Aggregations-
Mapping E/R Models to Relational Schema.

Database:
 The database is a collection of inter-related data which is used to retrieve, insert and delete
the data efficiently. It is also used to organize the data in the form of a table, schema, views,
and reports, etc.
 A database is a computer generated software program which can be used to access the data
stored in database in an organised manner. Using the database, you can easily retrieve,
insert, and delete the information.
For example: The college Database organizes the data about the admin, staff, students and faculty
etc.

Introduction of DBMS
DBMS (Database Management System) is a software system that manages, stores, and retrieves data
efficiently in a structured format.
 It allows users to create, update, and query databases efficiently.
 Ensures data integrity, consistency, and security across multiple users and applications.
 Reduces data redundancy and inconsistency through centralized control.
 Supports concurrent data access, transaction management, and automatic backup.

Database Management System

o Database management system is a software which is used to manage the database.
For example: MySQL, Oracle, etc are a very popular commercial database which
is used in different applications.
o DBMS provides an interface to perform various operations like database creation,
storing data in it, updating data, creating a table in the database and a lot more.
 o It provides protection and security to the database. In the case of multiple users,
it also maintains data consistency.
DBMS allows users the following tasks:
o Data Definition: It is used for creation, modification, and removal of definition
that defines the organization of data in the database.
o Data Updation: It is used for the insertion, modification, and deletion of the
actual data in the database.
o Data Retrieval: It is used to retrieve the data from the database which can
be used by applications for various purposes.
o User Administration: It is used for registering and monitoring users, maintain
data integrity, enforcing data security, dealing with concurrency control,
monitoring performance and recovering information corrupted by unexpected
failure.
Purpose of Database
There are so many drawbacks in using the file system. These are mentioned below −
 Data redundancy and inconsistency: Different file formats, duplication of
information in different files.
 Difficulty in accessing data: To carry out new task we need to write a new program.
 Data Isolation − Different files and formats.
 Integrity problems.
 Atomicity of updates − Failures leave the database in an inconsistent state. For
example, the fund transfer from one account to another may be incomplete.
 Concurrent access by multiple users.
 Security problems.
Advantages of DBMS
 Redundancy problem can be solved.
In the File System, duplicate data is created in many places because all the programs have
their own files which create data redundancy resulting in wastage of memory. In DBMS,
all the files are integrated in a single database. So there is no chance of duplicate data.
For example: A student record in a library or examination can contain duplicate values,
but when they are converted into a single database, all the duplicate values are removed.
 Has a very high security level.
Data security level is high by protecting your precious data from unauthorized access.
Only authorized users should have the grant to access the database with the help of
credentials.
 Presence of Data integrity.
 Data integrity makes unification of so many files into a single file. DBMS allows data
integrity which makes it easy to decrease data duplicity Data integration and reduces
redundancy as well as data inconsistency.
 Support multiple users.
DBMS allows multiple users to access the same database at a time without
any conflicts.
 Avoidance of inconsistency.
 DBMS controls data redundancy and also controls data consistency. Data
consistency is nothing but if you want to update data in any files then all
the files should not be updated again.
 In DBMS, data is stored in a single database so data becomes more
consistent in comparison to file processing systems.
 Shared data
Data can be shared between authorized users of the database in DBMS. All the users
have their own right to access the database. Admin has complete access to the database. He
has a right to assign users to access the database.
 Enforcement of standards
As DBMS have central control of the database. So, a DBA can ensure that all the
applications follow some standards such as format of data, document standards etc. These
standards help in data migrations or in interchanging the data.
 Any unauthorized access is restricted
Unauthorized persons are not allowed to access the database because of security
credentials.
 Provide backup of data
Data loss is a big problem for all the organizations. In the file
system users have to back up the files in regular intervals which lead to waste of time and
resources. DBMS solves this problem of taking backup automatically and recovery of the
database.
Disadvantages of DBMS
 Database systems are complex, difficult, and time-consuming to design
 Substantial hardware and software start-up costs
 Damage to database affects virtually all applications programs
 Extensive conversion costs in moving form a file-based system to a database system
 Initial training required for all programmers and users
Popular DBMS Software
 MySQL
 Microsoft Access
 Oracle
 PostgreSQL
 dBASE
 FoxPro
 SQLite
 IBM DB2
 Microsoft SQL Server
Application of DBMS

Sector Use of DBMS

Banking For customer information, account activities, payments, deposits,

loans, etc.
Airlines For reservations and schedule information.

Universities For student information, course registrations, colleges, and grades.

Telecommunication It helps to keep call records, monthly bills, maintain balances, etc.

For storing information about stock, sales, and purchases of

Finance financial instruments like stocks and bonds.

Sales Use for storing customer, product & sales information.

Manufacturing It is used to manage the supply chain and track the production
of items. Inventories status in warehouses.

For information about employees, salaries, payroll, deduction,

HR Management generation of paychecks, etc.
 DIFFERENCE BETWEEN DBMS & FILE SYSTEM

Basis DBMS Approach File System Approach

Meaning DBMS is a collection of data. In The file system is a collection of data. In this
DBMS, the user is not required to system, the user has to write the procedures for
write the procedures. managing the database.
Sharing of data Due to the centralized Data is distributed in many files, and it may be of
approach, data sharing is easy. different formats, so it isn't easy to share data.
Data DBMS gives an abstract view of The file system provides the detail of the data
Abstraction data that hides the details. representation and storage of data.
Securityand DBMS provides a good It isn't easy to protect a file under the file
Protection protection mechanism. system.

Recovery DBMS provides a crash recovery The file system doesn't have a crash mechanism,
Mechanism mechanism, i.e., DBMS protects i.e., if the system crashes while entering some
the user from system failure. data, then the content of the file will be lost.
Manipulation DBMS contains a wide variety of The file system can't efficiently store and
Techniques sophisticated techniques to store retrieve the data.
and retrieve the data.

Concurrency DBMS takes care of Concurrent In the File system, concurrent access has many
Problems access of data using some form of problems like redirecting the file while deleting
locking. some information or updating some information.
Where to use Database approach used in large File system approach used in large systems
systems which interrelate many which interrelate many files.
files.
Cost The database system is The file system approach is cheaper to design.
expensive to design.
Data Due to the centralization of the In this, the files and application programs are
Redundancy and database the problems of data created by different programmers so that there
Inconsistency redundancy and inconsistency are exists a lot of duplication of data which may lead
controlled. to inconsistency.
Structure The database structure is The file system approach has a simple structure.
complex to design.
Data In this system, Data Independence In the File system approach, there exists no Data
Independence exists, and it can be of two types. Independence.
o Logical Data Independence
o Physical Data Independence
Integrity Integrity Constraints are easy to Integrity Constraints are difficult to implement in
Constraints apply. file system.
Data Models In the database approach, 3 types In the file system approach, there is no concept of
of data models exist: data models exists.
o Hierarchal data models
o Network data models
o Relational data models
Flexibility Changes are often a necessity to The flexibility of the system is less as compared to
the content of the data stored in the DBMS approach.
any system, and these changes are
more easily with a database
approach.
Examples Oracle, SQL Server, Sybase etc. Cobol, C++ etc.

Concept of Database
To store and manage data efficiently in the database let us understand some key terms:
1. Database Schema: It is a design of the database. Or we can say that it is a
skeleton of the database that is used to represent the structure, types of data will be
stored in the rows and columns, constraints, relationships between the tables.
2. Data Constraints: In a database, sometimes some restrictions on the table that
what type of data can be stored in one or more columns of the table, it can be done
by using constraints. Constraints are defined while we are creating a table.
3. Data dictionary or Metadata: Metadata is known as the data about the data. Or
we can say that the database schema along with different types of constraints on the
data is stored by DBMS in the dictionary is known as metadata.
4. Database instance: In a database, a database instance is used to define the complete
database environment and its components. Or we can say that it is a set of memory
structures and background processes that are used to access the database files.
 5. Query: In a database, a query is used to access data from the database. So users
have to write queries to retrieve or manipulate data from the database.
6. Data manipulation: In a database, we can easily manipulate data using the
three main operations that is Insertion, Deletion, and updation.
7. Data Engine: It is an underlying component that is used to create and manage
various database queries.

Database Abstraction
A major purpose of a database is to provide the user with only as much information as is
required of them. This means that the system does not disclose all the details of the data, rather it
hides some details of how the data is stored and maintained. The complexity of databases is hidden
from them which, if necessary, are ordered through multiple levels of abstraction to facilitate their
interaction with the system. The different levels of the database are implemented through three
layers:
1. Internal Level (Physical Level): The lowest level of abstraction, the internal level, is closest
to physical storage. It describes how the data is stored concretely on the storage medium.
2. Conceptual Level: This level of
abstraction describes what data is
concretely stored in the database. It
also describes the relationships that
exist between the data. At this level,
databases are described logically in
terms of simple data structures. Users
at this level are not concerned with
how these logical data structures will
be implemented at the physical level.
3. External Level (View Level): It is the
level closest to users and is related to the way the data is viewed by individual users.
DBMS Architecture

o The DBMS design depends upon its architecture. The basic client/server
architecture is used to deal with a large number of PCs, web servers, database
servers and other components that are connected with networks.
o The client/server architecture consists of many PCs and a workstation which
are connected via the network.
o DBMS architecture depends upon how users are connected to the database to
get their request done.
Types of DBMS Architecture

Database architecture can be seen as a single tier or multi-tier. But logically, database
architecture is of two types like: 2-tier architecture and 3-tier architecture.

1- Tier Architecture
o In this architecture, the database is directly available to the user. It means the
user can directly sit on the DBMS and uses it.
o Any changes done here will directly be done on the database itself. It doesn't
provide a handy tool for end users.
o The 1-Tier architecture is used for development of the local application,
where programmers can directly communicate with the database for the quick
response.
Advantages of 1-Tier Architecture
Below mentioned are the advantages of 1-Tier Architecture.
 Simple Architecture: 1-Tier Architecture is the most simple
architecture to set up, as only a single machine is required to maintain it.
  Cost-Effective: No additional hardware is required for
implementing 1-Tier Architecture, which makes it cost-effective.
 Easy to Implement: 1-Tier Architecture can be easily deployed, and
hence it is mostly used in small projects.

2- Tier Architecture
o The 2-Tier architecture is same as basic client-server. In the two-tier
architecture, applications on the client end can directly communicate with the
database at the server side. For this interaction, API's like: ODBC, JDBC are
used.
o The user interfaces and application programs are run on the client-side.
o The server side is responsible to provide the functionalities like: query
processing and transaction management.
o To communicate with the DBMS, client-side application establishes a
connection with the server side.

Fig: 2-tier Architecture

Advantages of 2-Tier Architecture
 Easy to Access: 2-Tier Architecture makes easy access to the database,
which makes fast retrieval.
 Scalable: We can scale the database easily, by adding clients or upgrading hardware.
 Low Cost: 2-Tier Architecture is cheaper than 3-Tier Architecture
and Multi-Tier Architecture.
 Easy Deployment: 2-Tier Architecture is easier to deploy than 3-Tier Architecture.
 Simple: 2-Tier Architecture is easily understandable as well as simple
because of only two components.

 3- Tier Architecture
o The 3-Tier architecture contains another layer between the client and
server. In this architecture, client can't directly communicate with the server.
o The application on the client-end interacts with an application server
which further communicates with the database system.
o End user has no idea about the existence of the database beyond the
application server. The database also has no idea about any other user beyond
the application.
o The 3-Tier architecture is used in case of large web application.

Data Models in DBMS

A Data Model in a Database Management System (DBMS) is a set of concepts and tools used to
describe the structure of a database. It provides a clear view of how data is designed, stored, and
accessed, helping in the creation and implementation of a database.
A data model can be defined as an integrated collection of concepts describing and manipulating
data, as well as constraints on the data within an organization.
Common Types of Data Models
 Hierarchical Model
 Hierarchical model was developed by IBM and North American Rockwell known as Information
Management System.
 It represents the data in a hierarchical tree structure.
 This model is the first DBMS model.
 In this model, the data is sorted hierarchically.
 It uses pointer to navigate between the stored data.

Relational Model
 Relational model is based on first-order predicate logic.
 This model was first proposed by E. F. Codd.
 It represents data as relations or tables.
 Relational database simplifies the database structure by making use of tables and columns.

Network Database Model

 Network Database Model is same like Hierarchical Model, but the only difference is that it
allows a record to have more than one parent.
 In this model, there is no need of parent to child association like the hierarchical model.
 It replaces the hierarchical tree with a graph.
 It represents the data as record types and one-to-many relationship.
 This model is easy to design and understand.

Entity Relationship Model

 Entity Relationship Model is a high-level data model.
 It was developed by Chen in 1976.
 This model is useful in developing a conceptual design for the database.
 It is very simple and easy to design logical view of data.
 The developer can easily understand the system by looking at an ER model constructed.
 Rectangle represents the entities. Eg. Doctor and Patient.
 Ellipse represents the attributes. Eg. DocId, Dname, PId, Pname. Attribute describes each entity
becomes a major part of the data stored in the database.
 Diamond represents the relationship in ER diagrams. Eg. Doctor diagnoses the Patient.

Object-Oriented Data model

The object-oriented data model is a combination of object-oriented programming and
relational data model. In this data model, the data and their relationship are
represented in a single structure which is known as an object.
Since data is stored as objects we can easily store audio, video, images, etc in the
database which was very difficult and inconvenient to do in the relational model. As
shown in the image below two objects are connected with each other through links.
 In the above image, we have two objects that are Employee and Department in which
all the data is contained in a single unit (object).

Object Relational Data Model

The object-relational data model is an integration of the object- oriented model and
the relational model. Since it inherits properties from both of the models it supports
objects, classes, etc like object-oriented models, and tabular structures like the
relational model. It provides data structures and operations used in the relational
model and also provides features of object-oriented models like classes, inheritance,

etc. The only drawback of this data model is that it is complex and quite difficult to
handle.
Semi-Structured Data Model
A semi-structured data model is a generalized form of the relational model, which
allows representing data in a flexible way, hence we can not differentiate between
data and schema in this model because, in this model, some entities have a missing
attribute(s) and on the other hand, some entities might have some extra attribute(s)
which in turn makes it easy to update the schema of the database.
For example - We can say a data model is semi-structured if in some attributes we
are storing both atomic values (values that can't be divided further, for example,
Roll_No) as well as a collection of values.

Associative Data Model

The Associative Data Model is a distinct database architecture that structures all data into two
fundamental components: items (entities with independent existence) and links (associations or
relationships between items). This differs significantly from the table-based structure of an RDBMS
 Items: Each item has a unique identifier and a name.
 Links: Each link has a unique identifier, a source (item or link), a verb (the nature of the
relationship), and a target (item or link).
 Structure: Data is often visualized as a network of nodes (items) and vectors (links), allowing for
flexible, dynamic relationships that are difficult to model in a fixed schema relational system without
complex restructuring.
Example:
For the sentence John works at Google since 2015
 Items: John, Google, 2015

Types of Data Models

Data models are broadly classified into three types:
1. Conceptual Data Model
2. Representational (Logical) Data Model
3. Physical Data Model
It is basically classified into 3 types:-

Data Models

1. Conceptual Model in DBMS

The conceptual data model in DBMS is a high-level interface. This model will not explain the
technical details of the data. This will only help the user to know and understand the concept. The
concepts were explained in attributes and relationships with the help of an Entity-Relationship (ER)
diagram. The ER diagram will be helpful to let the stakeholders know the hierarchy and structure of
the data.
Example: If the database wants to store the banking data, the conceptual model will create an Entity
Relationship diagram (ER) with the manager, transactions, account, and user ID. This entity diagram
will allow the stakeholders and users to understand the structure of the database.
Example of ER:

Logical Model in DBMS

The Logical data model in DBMS helps decide the elements that are present in the database. This
model will decide the primary keys, foreign keys, attributes, and the relationship between them. It
will ensure normalization and data integrity. This model will represent data in a table format, but
neither of the entities will depend on a specific database.
Example: In a banking system, the user will have their own user ID and transaction ID, which will
be connected to their loans in a particular bank.
Physical Model in DBMS
The physical data model in DBMS is the actual implementation of the database. It will show the user
how the data is stored and managed in a database. The physical model will represent and decide the
details of table structures, where they will be stored, the constraints that can be used, the table
indexes, the partitions of tables or data, and the type of data that can be used to make the database
more efficient. This model will transform the logical design into a working schema with the help of
the SQL language. SQL will optimize the query performance by indexing and storing data
efficiently. Example: MySQL is very efficient for implementing the Physical Model.
 Key Features of Data Modeling Tools
Some of the key features of Data Modeling tools in DBMS are: Creating ER Diagram: An ER
diagram is used to visually represent the entities, attributes, and their relationships in a database. It
helps in understanding the flow of data and the strategies of design clearly.
1. Using Schema: Schemas are used to convert an ER diagram into an actual database structure
having tables, keys, and relationships. It makes sure that there is consistency without manually
writing the SQL queries.
2. Reverse Engineering: Reverse Engineering is used to create models from existing databases,
which are used to improve or redesign them. It also helps in optimizing and upgrading databases
without losing data integrity.
3. Error Prevention and Data Consistency: Data modeling tools are used to check for missing
constraints, duplicates, and rule violations. It makes sure the database is structured and consistent,
and there are no errors. Security and Access Control: This is used to protect sensitive data that is
contained inside the database. It ensures compliance with security standards like HIPAA, GDPR,
and PCI-DSS.
Advantages of Data Models in DBMS
1. Improves Data Understanding: Shows how the data are related and organized, which helps
developers and users to understand the data more clearly
2. Reduces Redundancy: Removes duplicate data and keeps the database consistent.
3. Enhances Performance: Improves the speed of data storage, queries, and retrieval.
4. Simplifies Maintenance: Enables easier ways to maintain, modify, and extend the database.
5. Improves Communication: Helps the technical teams and the business users to understand the data
clearly.
Disadvantages of Data Models in DBMS
1. Complex to Design: Detailed conceptual, logical, and physical models require effort and time to
develop.
2. Difficult to Modify: After setting up the model, it is difficult to make the changes, as it may affect
the database.
3. Tool Dependency: Some features depend on specialized tools, which can be difficult or expensive
to use
4. Limited flexibility: Some models (like the hierarchical or network) are hard to adjust and make
changes.
5. Performance overhead: Too many rules or strict designs can make data retrieval slower.
Comparison of the Data Models in DBMS
Aspect Conceptual Model Logical Model Physical Model
Use Case Shows the overall Shows the detailed structure Shows how the database
structure of the database of data, including the will actually function,
so that everyone can have relationships between the including storage capacity,
a high-level view, like the tables, and provides indexes, table layouts, and
stakeholder or business information about primary performance information.
user. keys, foreign keys, and
consistency rules.
Advantages Simple for non-technical Ensures accuracy and Makes the database fast and
users to understand, helps integrity of the data, efficient, allows indexing,
to structure the database minimizes duplication, and constraints, and ensures the
clearly, and shows makes the data ready for the queries run efficiently.
relationships between actual database creation.
entities.
Disadvantages Cannot be utilized Not tied to a particular It can be a complicated
directly to create a database system, may have to process to design and
database, does not include make modifications when difficult to modify after
any technical or storage implementing in practice. implementation.
details.
Key Features Uses the ER diagrams to Defines the tables, columns, Defines tables, columns,
display entities like primary and foreign keys, indexes, constraints,
Customer, Account and and normalizes the database partitions, and generates
their attributes like Name, to avoid duplicates. SQL scripts for the
ID, along with the database.
relationships between
them.
Examples Entity relationship Tables containing users, MySQL tables with
diagram for Customer, accounts, and transactions, indexes, constraints, and
Account, Transaction. with keys linking them. partitions for a banking
system.
 ENTITY RELATIONSHIP MODEL
ER Diagrams in DBMS

ER diagrams are used to represent the E-R model in a database, which makes them easy to be
converted 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.

Components of ER Diagram

ER Model consists of Entities, Attributes, and Relationships among Entities in a Database

System.

Entity

An Entity may be an object with a physical existence – a particular person, car,

house, or employee – or it may be an object with a conceptual existence – a company,
a job, or a university course.
Entity Set: An Entity is an object of Entity Type and a set of all entities is called an
entity set. For Example, E1 is an entity having Entity Type Student and the set of all
students is called Entity Set. In ER diagram, Entity Type is represented as:
1. Strong Entity

A Strong Entity is a type of entity that has a key Attribute. Strong Entity does not
depend on other Entity in the Schema. It has a primary key, that helps in
identifying it uniquely, and it is represented by a rectangle. These are called Strong
Entity Types.
2. Weak Entity

An Entity type has a key attribute that uniquely identifies each entity in the entity set.
But some entity type exists for which key attributes can’t be defined. These are called
Weak Entity types.
For Example, A company may store the information of dependents (Parents,
Children, Spouse) of an Employee. But the dependents don’t have existed
without the employee. So Dependent will be

a Weak Entity Type and Employee will be Identifying Entity type for Dependent,
which means it is Strong Entity Type.

A weak entity type is represented by a Double Rectangle. The participation of weak

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

Strong Entity and Weak Entity

Attributes

Attributes are the properties that define the entity type. For example, Roll_No, Name,
DOB, Age, Address, and Mobile_No are the attributes that define entity type Student.
In ER diagram, the attribute is represented by an oval.
1. Key Attribute

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

2. Composite Attribute

An attribute composed of many other attributes is called a composite attribute. For

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

3. Multivalued Attribute

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

Multivalued Attribute

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

Derived Attribute

The Complete Entity Type Student with its Attributes can be represented as:

Relationship

A relationship is used to describe the relation between entities. Diamond or rhombus

is used to represent the relationship.
 Relationship Type and Relationship Set

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, the relationship type is represented by a diamond and connecting the
entities with lines.

Entity-Relationship Set

A set of relationships of the same type is known as a relationship set. The

following relationship set depicts S1 as enrolled in C2, S2 as enrolled in C1, and S3 as
registered in C3.

Relationship Set

Degree of a Relationship Set

The number of different entity sets participating in a relationship set is called

the degree of a relationship set.
1. Unary Relationship: When there is only ONE entity set participating in
a relation, the relationship is called a unary relationship. For example, one
person is married to only one person.
 Unary Relationship

2. Binary Relationship: When there are TWO entities set participating in a

relationship, the relationship is called a binary relationship. For example, a
Student is enrolled in a Course.

Binary Relationship

4. n-ary Relationship: When there are n entities set participating in

a relation, the relationship is called an n-ary 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

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.

Using sets, one-to-many cardinality can be represented as:

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.

Steps to Draw ER Diagram

 The very first step is Identifying all the Entities, and place them in a
Rectangle, and labeling them accordingly.
 The next step is to identify the relationship between them and pace
them accordingly using the Diamond, and make sure that, Relationships
are not connected to each other.
 Attach attributes to the entities properly.

 Remove redundant entities and relationships.

 Add proper colors to highlight the data present in the database.

Step 1: Identify Your Database's Entities

Which entities make up your database? Represent each one with a rectangle, and space them apart
from each other so you have room to add attributes and relationships to your diagram in the next
steps.
Step 2: Add Attributes to Each Entity
Each entity should have at least one attribute – a group of data that belongs to the entity. Sometimes,
attributes are listed below the entity name within the same rectangle, but to make the structure
clearer, we’ll represent attributes by drawing and labeling each one inside its own oval.
Then, connect each attribute to its corresponding entity with a solid line.

Step 3: Represent Relationships Between Entities

Think through the relationships, or “verbs,” that occur within your system. The easiest way to do
this is to look at each entity and connect it to another by saying, “What does [entity one] do with
[entity two]?”
For example, a customer purchases a phone. The cell service creates a bill, and the customer
then pays that bill.
Relationships are typically represented by diamonds located in between the two entities that are
connected.

Step 4: Add Cardinality to Every Relationship

The final step to creating a basic ER diagram is to define the amount of data that comes from each
entity. This information is described by cardinality – a simple notation that tells your ERD reader
at a glance how many connections exist between two connected entities (zero, one, many, or some
combination of these).
For example, a customer can purchase one or many phones. The cell service
maintains many phones. The customer pays one bill.
Here’s how to represent a few of the most common types of cardinalities that you may include in
your diagram:
 One to one: Use a line with a dash on each end.
 One to many: Use a crow’s foot line.
 Many to many: Use a double crow’s foot line
Extended Entity-Relationship (EE-R) Model
EER is a high-level data model that incorporates the extensions to the original ER model. Enhanced
ERD are high level models that represent the requirements and complexities of complex database.
 Subclasses and Super classes.
 Specialization and Generalization.
 Category or union type.
 Aggregation.
 These concepts are used to create EE-R diagrams.

1. Sub classes and Super class

 Super class is an entity that can be divided into further subtype.
For example :consider Shape super class.

 Super class shape has sub groups: Triangle, Square and Circle.
 Sub classes are the group of entities with some unique attributes.
  Sub class inherits the properties and attributes from super class.

2. Specialization and Generalization

Generalization is a process of generalizing an entity which contains generalized
attributes or properties of generalized entities.

It is a Bottom up process i.e. consider we have 3 sub entities Car, Truck and Motorcycle.
Now these three entities can be generalized into one super class named as Vehicle.

Specialization is a process of identifying subsets of an entity that share some different

characteristic. It is a top down approach in which one entity is broken down into low level entity.
In above example Vehicle entity can be a Car, Truck or Motorcycle.

3. Category or Union
Relationship of one super or sub class with more than one super class.

Owner is the subset of two super class: Vehicle and House.

4. Aggregation
 Represents relationship between a whole object and its component.
 An ER diagram is not capable of representing the 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.

Example: An Employee working on a project may require some machinery. So, REQUIRE
relationship is needed between the relationship WORKS_FOR and entity MACHINERY. Using
aggregation, WORKS_FOR relationship with its entities EMPLOYEE and PROJECT is aggregated
into a single entity and relationship REQUIRE is created between the aggregated entity and
MACHINERY.