DATABASE MANAGEMENT SYSTEMS(KCA 204)

UNIT-1
Data: - Data is raw, unorganized facts that need to be processed. Data we mean
known facts that can be recorded. Data can be represented in alphabets, digits and
special characters. Data can be defined as a representation of facts, concepts, or
instructions in a formalized manner, which should be suitable for communication,
interpretation, or processing, by human or electronic machine.
Data is represented with the help of characters such as alphabets (A-Z, a-z), digits
(0-9) or special characters (+,-,/,*,<,>,= etc.)
Information: - Information is organized or classified data, which has some
meaningful values for the receiver. Information is the processed data on which
decisions and actions are based.
Database:-The collection of data, usually referred to as the database, contains
information relevant to an enterprise. A database is an organized collection of data,
generally stored and accessed electronically from a computer system.
DBMS (Database Management System)
A database is a collection of interrelated data and a set of programs to access those
data. The primary goal of a DBMS is to provide a way to store and retrieve
database information that is both convenient and efficient. A database is a
collection of related information stored ,so that it available to many users for
different purposes.
A DBMS is a software system that allows access to data contained in the database.
It objective is to provide a convenient and effective method of defining , storing
and retrieving the information contained in the database. The DBMS interfaces
with application programs so that data contained in the database can be used by
multiple application and users.
By data, we mean known facts that can be recorded and that have implicit
meaning. A database management system (DBMS) is a collection of programs that
enables users to create and maintain a database. The DBMS is a general-purpose
software system that facilitates the processes of defining, constructing,
manipulating, and sharing databases among various users and applications

Components of DBMS
The database management system can be divided into five major components, they
are:

1. Hardware

2. Software

3. Data

4. Procedures

5. Database Access Language

Let's have a simple diagram to see how they all fit together to form a database
management system.
DBMS Components:

1.Hardware

When we say Hardware, we mean computer, hard disks, I/O channels for data, and
any other physical component involved before any data is successfully stored into
the memory.

When we run Oracle or MySQL on our personal computer, then our computer's
Hard Disk, our Keyboard using which we type in all the commands, our computer's
RAM, ROM all become a part of the DBMS hardware.

2. Software

This is the main component, as this is the program which controls everything. The
DBMS software is more like a wrapper around the physical database, which
provides us with an easy-to-use interface to store, access and update data.

The DBMS software is capable of understanding the Database Access Language

and intrepret it into actual database commands to execute them on the DB.
3. Data

Data is that resource, for which DBMS was designed. The motive behind the
creation of DBMS was to store and utilise data.

For example: When I store my Name in a database, the DBMS will store when
the name was stored in the database, what is the size of the name, is it stored as
related data to some other data, or is it independent, all this information is
metadata.

4. Procedures

Procedures refer to general instructions to use a database management system. This

includes procedures to setup and install a DBMS, To login and logout of DBMS
software, to manage databases, to take backups, generating reports etc.

5.Database Access Language

Database Access Language is a simple language designed to write commands to

access, insert, update and delete data stored in any database.

A user can write commands in the Database Access Language and submit it to the
DBMS for execution, which is then translated and executed by the DBMS.

User can create new databases, tables, insert data, fetch stored data, update data
and delete the data using the access language.

6.Users

 Database Administrators: Database Administrator or DBA is the one who

manages the complete database management system. DBA takes care of the
security of the DBMS, it's availability, managing the license keys, managing
user accounts and access etc.

 Application Programmer or Software Developer: This user group is

involved in developing and desiging the parts of DBMS.
  End User: These days all the modern applications, web or mobile, store user
data. How do you think they do it? Yes, applications are programmed in
such a way that they collect user data and store the data on DBMS systems
running on their server. End users are the one who store, retrieve, update and
delete data.

File Management System versus Database System

File-based approaches which came into being with the first commercial
applications of computers suffered from the following significant disadvantages:
1. Data Redundancy: Data Redundancy means same information is duplicated
in several files. This makes data redundancy.
2. Data Inconsistency: Data Inconsistency means different copies of the same
data are not matching. That means different versions of same basic data are
existing. This occurs as the result of update operations that are not updating
the same data stored at different places.
3. Difficulty in Accessing Data: It is not easy to retrieve information using a
conventional file processing system. Convenient and efficient information
retrieval is almost impossible using conventional file processing system.
4. Data Isolation: Data are scattered in various files, and the files may be in
different format, writing new application program to retrieve data is
difficult.
5. Integrity Problems: The data values may need to satisfy some integrity
constraints. For example, the balance field value must be greater than 5000.
We have to handle this through program code in file processing systems. But
in database we can declare the integrity constraints along with definition
itself.
6. Atomicity Problem:It is difficult to ensure atomicity in file processing
system. For example, transferring 100 Rs. From account A to account B. If a
failure occurs during execution there could be situation like 100 Rs. is
deducted from account A and not credited in account B.
7. Concurrent Access anomalies: If multiple users are updating the same data
simultaneously it will result in inconsistent data state. In file processing
system, it is very difficult to handle this using program code. This results in
concurrent access anomalies.
 8. Security Problems: Enforcing security constraints in file processing system
is very difficult as the application programs are added to the system in an
adhoc manner.

Difference between DBMS and File Processing System:-

DBMS File Processing System

Data Redundancy does not occur Data Redundancy occur
Data inconsistency does not exist Data inconsistency exist
Accessing database is easier Accessing files is difficult
The problem of data isolation is not The problem of data isolation is there
there.
Atomicity and integrity Problem are Atomicity and integrity Problem are
not found. found.
Security of data Security of data is not there.
Concurrent access and crash No Concurrent access and No crash
recovery recovery

Database-System Applications
Databases are widely used. Here are some representative applications:
Enterprise Information
◦ Sales: For customer, product, and purchase information.
◦Accounting: For payments, receipts, account balances, assets and other accounting
information.
◦ Human resources: For information about employees, salaries, payroll taxes, and
benefits, and for generation of paychecks.
◦Manufacturing: For management of the supply chain and for tracking production
of items in factories, inventories of items in warehouses and stores, and orders for
items.
◦Online retailers: For sales data noted above plus online order tracking, generation
of recommendation lists, and maintenance of online product evaluations.
Banking and Finance
◦Banking: For customer information, accounts, loans, and banking transactions.
◦ Credit card transactions: For purchases on credit cards and generation of monthly
statements.
◦ Finance: For storing information about holdings, sales, and purchases of financial
instruments such as stocks and bonds; also for storing real-time market data to
enable online trading by customers and automated trading by the firm.
Universities: For student information, course registrations, and grades (in addition
to standard enterprise information such as human resources and accounting).
Airlines: For reservations and schedule information. Airlines were among the first
to use databases in a geographically distributed manner.
Telecommunication: For keeping records of calls made, generating monthly bills,
maintaining balances on prepaid calling cards, and storing information about the
communication networks.

Advantages of DBMS:-
1. Controlling Redundancy
In file systems each application program has its own private files. In this case, the
duplicated copies of the same data are created in many places. In DBMS, all data
of an organization is integrated into a single database file. The data is recorded in
only one place in the database and it is not duplicated.
2. Sharing of Data
In DBMS, data can be shared by authorized users of the organization. The database
administrator manages the data and gives rights to users to access the data. Many
users can be authorized to access the same piece of information simultaneously.
The remote users can also share same data. Similarly, the data of same database
can be shared between different application programs.
3. Reduction in Data Consistency
By controlling the data redundancy, the data consistency is obtained. If a data item
appears only once, any update to its value has to be performed only once and the
updated value is immediately available to all users. If the DBMS has controlled
redundancy, the database system enforces consistency.
4. Maintenance of Integration of Data
In Database management system, data in database is stored in tables. A single
database contains multiple tables and relationships can be created between tables
(or associated data entities). This makes easy to retrieve and update data.
5. Enforcement of Standards
Integrity constraints or consistency rules can be applied to database so that the
correct data can be entered into database. The constraints may be applied to data
item within a single record or the may be applied to relationships between records.
6. Improvement of Data Security
DBMS makes it easier to enforce security restriction since the data is stored
centrally. DBMS provides security tools as username and password.
Disadvantages of DBMS:-
Problems associated with centralization:-Centralization means that data is
accessible from single source. Centralized data can be accessed by each user, so
here is no security of data from unauthorized access and data can be damaged or
lost.
Cost of Hardware and Software
A processor with high speed of data processing and memory of large size is
required to run the DBMS software. It means that you have to upgrade the
hardware used for file-based system. Similarly, DBMS software is also very costly.

Two tier and three tier Architecture

user client user

application application client

network network

application server

database system server

database system

( a) Two-tier architecture (b) Three-tier architecture

Figure 1.6 Two-tier and three-tier architectures.

In a two-tier architecture, the application resides at the client machine, where it
invokes database system functionality at the server machine through query
language statements. Application program interface standards like ODBC and
JDBC are used for interaction between the client and the server.
In contrast, in a three-tier architecture, the client machine acts as merely a front
end and does not contain any direct database calls. Instead, the client end
communicates with an application server, usually through a forms interface. The
application server in turn communicates with a database system to access data. The
business logic of the application, which says what actions to carry out under what
conditions, is embedded in the application server, instead of being distributed
across multiple clients. Three-tier applications are more appropriate for large
applications, and for applications that run on the World Wide Web.

Difference Between Two-Tier And Three-Tier Database

Architecture

Two-Tier Database Architecture Three-Tier Database Architecture

It is a Client-Server Architecture. It is a Web-based application.

In two-tier, the application logic is In three-tier, the application logic

either buried inside the user interface or process resides in the middle-
on the client or within the database tier, it is separated from the data
on the server (or both). and the user interface.

Two-tier architecture consists of two Three-tier architecture consists of

layers : Client Tier and Database three layers : Client Layer,
(Data Tier). Business Layer and Data Layer.

It is easy to build and maintain. It is complex to build and maintain.

Two-tier architecture runs slower. Three-tier architecture runs faster.

It is less secured as client can It is secured as client is not allowed

communicate with database directly. to communicate with database
 directly.

It results in performance loss

whenever the system is run on
Internet but gives more
It results in performance loss performance than two-tier
whenever the users increase rapidly. architecture.

Example – Designing registration

Example – Contact Management form which contains text box,
System created using MS-Access or label, button or a large website on
Railway Reservation System, etc. the Internet, etc.

Three-Schema Architecture
OR
Types of Schemas
The goal of the three-schema architecture is to separate the user applications from
the physical database. In this architecture, schemas can be defined at
the following three levels:
1. Internal level or physical level- The internal level has an internal
schema, which describes the physical storage structure of the database. The
internal schema uses a physical data model and describes the complete
details of data storage and access paths for the database.
2. Conceptual level - The conceptual level has a conceptual schema, which
describes the structure of the whole database for a community of users. The
conceptual schema hides the details of physical storage structures and
concentrates on describing entities, data types, relationships, user operations,
and constraints. Usually, a representational data model is used to describe
the conceptual schema when a database system is implemented. This
implementation conceptual schema is often based on a conceptual schema
design in a high-level data model.
3. External level or View level -The external or view level includes a
number of external schemas or user views. Each external schema describes
the part of the database that a particular user group is interested in and hides
the rest of the database from that user group. As in the previous level, each
external schema is typically implemented using a representational data
model, possibly based on an external schema design in a high-level data
model.
 Data independence
Data independence can be defined as the capacity to change the schema at one
level of a database system without having to change the schema at the next
higher level. We can define two types of data independence:
1. Logical data independence - Logical data independence is the
capacity to change the conceptual schema without having to change
external schemas or application programs. We may change the
conceptual schema to expand the database (by adding a record type or
data item), to change constraints, or to reduce the database(by removing a
record type or data item).In the last case, external schemas that refer only
to the remaining data should not be affected. Only the view definition
and the mappings need to be changed in a DBMS that supports logical
data independence.
2. Physical data independence - Physical data independence is the
capacity to change the internal schema without having to change the
conceptual schema. Hence, the external schemas need not be changed as
well. Changes to the internal schema may be needed because some
physical files were reorganized—for example, by creating additional
access structures—to improve the performance of retrieval or update. If
the same data as before remains in the database, we should not have to
change the conceptual schema.
Logica Data Independence Physical Data Independence

Logical Data Independence is mainly concerned Mainly concerned with the storage of the data.
with the structure or changing the data
definition.

It is difficult as the retrieving of data is mainly It is easy to retrieve.

dependent on the logical structure of data.

Compared to Logic Physical independence it is Compared to Logical Independence it is easy to

difficult to achieve logical data independence. achieve physical data independence.

You need to make changes in the Application A change in the physical level usually does not
program if new fields are added or deleted from need change at the Application program level.
the database.

Modification at the logical levels is significant Modifications made at the internal levels may
whenever the logical structures of the database or may not be needed to improve the
are changed. performance of the structure.

Concerned with conceptual schema Concerned with internal schema

Example: Add/Modify/Delete a new attribute Example: change in compression techniques,

hashing algorithms, storage devices, etc

Database Languages
A database system provides a data definition language, which specifies the
database schema, and a data manipulation language, which expresses database
queries and updates.
1. Data-Definition Language (DDL): DDL is used to define the conceptual
schema of the database. It is a set of SQL command used to create, modify and
delete database structures but not data. These commands are normally not used
by a general user, who should be accessing the database via an application.
They are generally used by the DBA.
A data dictionary contains metadata i.e. data about data. The schema of table is
an example of metadata. A database system consults the data dictionary before
reading or modifying actual data.
2. Data-Manipulation Language (DML): Data manipulation includes retrieval
of data stored in database, insertion of new data into the database, deletion of
data from the database and modification of data stored in the database.
Data manipulation language enables users to access or manipulate data as
organized by different data model.
DMLs are of two types:
 Procedural DML: It requires a user to specify what data are needed and how
to get those data.
 Nonprocedural DML: It requires a user to specify what data are needed
without specifying how to get those data.
3. Data Control Language
o DCL stands for Data Control Language. It is used to retrieve the stored or
saved data. Grant and Revoke

4. Transaction Control Language

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

Here are some tasks that come under TCL:

o Commit: It is used to save the transaction on the database.

o Rollback: It is used to restore the database to original since the last Commit.
 DBMS Interfaces
User-friendly interfaces provided by a DBMS may include the following:
Menu-Based Interfaces for Web Clients or Browsing. These interfaces
pre-sent the user with lists of options (called menus) that lead the user through the
formulation of a request. Menus do away with the need to memorize the specific
commands and syntax of a query language; rather, the query is composed step-by-
step by picking options from a menu that is displayed by the system. Pull-down
menus are a very popular technique in Web-based user interfaces. They are also
often used in browsing interfaces, which allow a user to look through the contents
of a database in an exploratory and unstructured manner.

Forms-Based Interfaces. A forms-based interface displays a form to each

user. Users can fill out all of the form entries to insert new data, or they can fill out
only certain entries, in which case the DBMS will retrieve matching data for the
remaining entries. Forms are usually designed and programmed for naive users as
inter-faces to canned transactions. Many DBMSs have forms specification
languages, which are special languages that help programmers specify such
forms.
Graphical User Interfaces. A GUI typically displays a schema to the user in
diagrammatic form. The user then can specify a query by manipulating the
diagram. In many cases, GUIs utilize both menus and forms. Most GUIs use
a pointing device, such as a mouse, to select certain parts of the displayed schema
diagram.
Natural Language Interfaces. These interfaces accept requests written
in English or some other language and attempt to understand them. A natural
language interface usually has its own schema, which is similar to the database
conceptual schema,
Speech Input and Output. Limited use of speech as an input query and
speech as an answer to a question or result of a request is becoming commonplace.
Applications with limited vocabularies such as inquiries for telephone directory,
flight arrival/departure, and credit card account information are allowing speech
for input and output to enable customers to access this information. The speech
input is detected using a library of predefined words and used to set up the
parameters that are supplied to the queries. For output, a similar conversion from
text or numbers into speech takes place.
 Interfaces for Parametric Users. Parametric users, such as bank tellers,
often have a small set of operations that they must perform repeatedly. For
example, a teller is able to use single function keys to invoke routine and repetitive
transactions such as account deposits or withdrawals, or balance inquiries. Systems
analysts and programmers design and implement a special interface for each
known class of naive users.
Interfaces for the DBA. Most database systems contain privileged
commands that can be used only by the DBA staff. These include commands for
creating accounts, setting system parameters, granting account authorization,
changing a schema, and reorganizing the storage structures of a database.

Overall Database Architecture

A DBMS is a complex software system.
Database Users
Depending on different type of uses, there are different types of users and for each
type of user different types of interfaces have been designed.
 Naive users / Parametric users: There are unsophisticated users who
interact with the system by invoking one of the application programs that
have been written previously. They also read reports from the database.
Example: bank teller, a person checking his balance through internet.
 Application Programmers: These are computer professionals who write
application programs to develop user interfaces. Using Rapid application
development (RAD) tool application programmers construct forms and
reports rapidly without writing programs.
 Sophisticated Users: These users interact with the system without writing
programs. They from their requests in a database query language and
submit it to the query processor. Examples: engineers, scientists, business
analytical processing (OLAP), Data mining that views them the
summarized data in different ways.
 Casual End Users: These users occasionally access the database, but they
may need different information each time. They use a sophisticated
database query language to specify their requests and are typically middle
or high-level managers or other occasional browsers
 Database Administrators
DBA has the central control over the system. The function / role of the DBA
include:
 Schema Definition: The DBA creates the original database schema by a set
of data definition statements in the DDL.
 Storage structure and access-method definition: DBA decides what
structure to be used to store the data and what method to be used to access
that.
 Schema and physical-organization modification: According to the need
of the organization the DBA carries out changes to the schema and physical
organization to improve the performance.
 Granting of authorization for data access: The DBA administrator
decides which user will access which part of the database.
  Routine maintenance: DBA does some of the routine maintenance like
periodically backing up the database, ensure that enough free disk space
available for normal operations, monitors jobs running on the database etc.

Some common components of DBMS are as below:

1. DML Pre-compiler: It converts DML statement embedded in an application
program to normal procedure calls in the host language. The pre-compiler
must interact with the query processor in order to generate the appropriate
code.
2. DDL Interpreter: The DDL interpreter converts the data definition
statements into a set of tables. These tables contain information concerning
the database and are in a form that can be used by other components of the
DBMS.
3. File Manager: File manager manages the allocation of space on disk storage
and the data structure used to represent information stored on disk.
4. Database Manager: A database manager is a program module, which
provides the interface between the low-level data stored in the database and
the application programs and queries submitted to the system. It is
responsible for interfacing with file system.
5. Query Processor: Query processor translates statements in a query
language into low-level instructions the database manager understands.
6. Data Files: Store the database itself.
7. Data dictionary: Stores information about the structure of the database. It is
used heavily. Great emphasis should be placed on developing a good design
and efficient implementation of the dictionary.
DATA MODEL OR Types of Databases
There are four structural types of database management systems:
 Hierarchical databases.
 Network databases.
 Relational databases.
 Object-oriented databases
 Entity Relational model

Hierarchical Data Model:In the Hierarchical Database Model we

have to learn about the databases. It is very fast and simple. In a
hierarchical database, records contain information about their groups of
parent/child relationships, just like as a tree structure. The structure
implies that a record can have also a repeating information. In this
structure Data follows a series of records, it is a set of field values
attached to it. It collects all records together as a record type. These
record types are the equivalent of tables in the relational model, and with
the individual records being the equivalent of rows. To create links
between these record types, the hierarchical model uses these type
Relationships.

Advantage: Hierarchical database can be accessed and updated rapidly

because in this model structure is like as a tree and the relationships
between records are defined in advance. This feature is a two-edged.
Disadvantage:This type of database structure is that each child in the
tree may have only one parent, and relationships or linkages between
children are not permitted, even if they make sense from a logical
standpoint. Hierarchical databases are so in their design. it can add a
new field or record requires that the entire database be redefined.

Network Data Model:A network databases are mainly used on a

large digital computer. It more connections can be made between
different types of data; network databases are considered more
efficiency It contains limitations must be considered when we have to
use this kind of database. It is Similar to the hierarchical databases,
network databases. Network databases are similar to hierarchical
databases by also having a hierarchical structure.
In network databases, children are called members and parents are called
occupier. The difference between each child or member can have more
than one parent.

Advantages: The major advantages of network model are –

1. Conceptual simplicity-Just like the hierarchical model,the
network model is also conceptually simple and easy to design.
 2. Capability to handle more relationship types-The network
model can handle the one to many and many to many relationships
which is real help in modeling the real-life situations.
3. Ease of data access-The data access is easier and flexible than the
hierarchical model.
4. Data integrity- The network model does not allow a member to
exist without an owner.
Disadvantages: The disadvantages of network model are –
1. System complexity- All the records are maintained using pointers
and hence the whole database structure becomes very complex.
2. Operational Anomalies-The insertion,deletion and updating
operations of any record require large number of pointers
adjustments.
3. Absence of structural independence- Structural changes to the
database is very difficult.
Relational Model The relational model uses a collection of tables to
represent both data and the relationships among those data. Each table
has multiple columns, and each column has a unique name. Tables are
also known as relations. The relational model is an example of a record-
based model. Record-based models are so named because the database is
structured in fixed-format records of several types. Each table contains
records of a particular type. Each record type defines a fixed number of
fields, or attributes. The columns of the table correspond to the attributes
of the record type. The relational data model is the most widely used
data model,
Object-Based Data Model Object-oriented programming (especially in
Java, C++, or C#) has become the dominant software-development
methodology. This led to the development of an object-oriented data
model that can be seen as extending the E-R model with notions of
encapsulation, methods (functions), and object identity. The object-
relational data model combines features of the object-oriented data
model and relational data model.

Entity-Relationship Model The entity-relationship (E-R) data model

uses a collection of basic objects, called entities, and relationships
among these objects. An entity is a “thing” or “object” in the real world
that is distinguishable from other objects. The entity-relationship model
is widely used in database design
Entity-Relationship (E-R) Modeling or E R Diagram:-
• E R model is based on the perception of real world objects that
consists of collection of objects called entities.
• E R Modeling was developed to facilitate database design by
allowing specification of an enterprise schema that represents the
overall logical structure of a database.
• The E-R model is very useful in mapping the meanings and
interactions of real-world enterprises onto a conceptual schema.
• The E-R data model employs three basic concepts: entity sets,
relationship sets, and attributes.
• Entity–relationship modeling was developed for database and
design by Peter Chen and published in a 1976 paper
• ER model stands for an Entity-Relationship model. It is a high-
level data model. This model is used to define the data elements
and relationship for a specified system.
• It develops a conceptual design for the database. It also develops a
very simple and easy to design view of data.
 Components of ER Diagram

Entity
• An entity is an object that exists and is distinguishable from other
objects. Anything about which we store information is called an
entity.
• An entity has a set of properties, and the values for some set of
properties may uniquely identify an entity.
• It includes all those things about which data is collected. An entity
may be tangible object such as student, place etc
 It may be non-tangible object such as event, job title or customer
account. For instance, a person may have a person id property
whose value uniquely identifies that person

Entity Set
• It is a set of entities of the same type that share the same
properties, or attributes.
For example, a Students set may contain all the students of a
college; set of persons who are customers at given bank

Types of Entities:-
• Strong Entity Set: A strong entity is one that has a complete
identifier whose values may be used to identify its instances
uniquely.
• An entity set that has a primary key is termed a strong entity set.
• For example- The employee has an identifier EmployeeId. The
attribute name is not its full identifier because there are two
employees which have the same name.
• The Strong Entity is the one whose existence does not depend on
the existence of any other entity in a schema.
• It is denoted by a single rectangle.
• A strong entity always has the primary key in the set of attributes
that describes the strong entity.
• For Example à Loan Entity, Student Entity . These entities have
contains an attribute that has primary key.

Weak Entity Set:

• An entity set that does not have sufficient attributes to form a
primary key is termed a weak entity set.
• Some entity types may not have any key attributes of their own;
these are called weak entity types.
• A weak entity is dependent on a strong entity type, i.e. it cannot be
identified without the existence of the associated strong entity.
• Every weak entity must be associated with an identifying entity;
that is, the weak entity set is said to be existence dependent on the
identifying entity set
Difference between strong and weak entity set:-
Strong entity set Weak entity set
It has its own primary key. It does not have sufficient attributes to
form a primary key on its own.
It is represented by a rectangle. It is represented by a double rectangle.
The member of strong entity set is called The member of weak entity set is called
as dominant entity. as subordinate entity.
The line connection strong entity set with The line connecting weak entity set with
the relationship is single. the identifying relationship is double.
The relationship between two strong The relationship between one strong and a
entity set is represented by a diamond weak entity set is represented by a double
symbol. diamond sign. It is known as identifying
relationship.
The primary key is one of its attribute The primary key of weak entity set is a
which uniquely identifies its member. combination of partial key and primary
 key of the strong entity set.
Total participation in the relationship may Total participation in the identifying
or may not exist. relationship always exists.

Attributes
Entities are represented by means of their properties, called attributes. All
attributes have values. Attributes are units that describe the properties of entities.
For example, a student entity may have name, class, and age as attributes.
There exists a domain or range of values that can be assigned to attributes. For
example, a student's name cannot be a numeric value. It has to be alphabetic. A
student's age cannot be negative, etc.

Types of Attribute
 Simple Attribute: Simple attributes are atomic values, which cannot be
divided further. For example, a student's phone number is an atomic value of
10 digits.
 Composite Attribute: Composite attribute which can be divided into sub
parts. Composite attributes are made of more than one simple attribute. For
example, a student's complete name may have first_name and last_name.
  Derived Attribute: The value for this type of attribute can be derived from
the values of other related attributes or entities. For example, age can be
derived from birth date and current date.
 Thus, age is derived attribute and birth date is base attribute or stored
attribute.
 Stored Attribute: The stored attribute are such attribute which are already
stored in the database. For example birth date.

Derived Attribute
 Single-Value Attribute: Single-value attributes contain single value. For
example − Age, Roll_no
 Multi-Valued Attribute: Multi-value attributes may contain more than one
value. For example, a person can have more than zero, one or several mobile
number, email address, etc.
Multi valued attributes are depicted by double ellipse.
 Multi valued Attribute

Relationship
The association among two or more entities is called a relationship. For example,
an employee works_at a department, a student enrolls in a course. Here,
Works_at and Enrolls are called relationship. It is represented by diamond
symbol.
Mapping cardinalities express the number of entities to which another entity can
be associated via a relationship set.It may be one to one (1:1), one-to-many (1:
M), many-to-many (M: M), many-to-one (M: 1).
Relationship Set
A relationship set is a set of relationships of the same type. Like entities, a
relationship too can have attributes. These attributes are called descriptive
attributes.
Degree of Relationship
• The number of participating entities in a relationship defines the degree of
the relationship.
• Binary = degree 2
• Ternary = degree 3
• n-ary = degree
Notation for ER Diagram
Mapping Cardinality Constraints
• Express the number of entities to which another entity can be associated via
a relationship set.
• Most useful in describing binary relationship sets.
• For a binary relationship set the mapping cardinality must be one of the
following types:
• One to one
• One to many
• Many to one
• Many to many
One-to-One (1:1):-
• One-to-One (1:1):- One entity E1 is related with almost one entity E2 and
E2 is related to E1.
• For example, a person has only one passport and a passport is given to one
person.

One-to-Many (1: M):-

• An entity in A is associated with any number (zero or more) of entities in B.
An entity in B, however, can be associated with at most one entity in A.
For example – a customer can place many orders but a order cannot be placed by
many customers.

Many-to-one:
• An entity in A is associated with at most one entity in B. An entity in B,
however, can be associated with any number (zero or more) of entities in A.
• For example – many students can study in a single college but a student
cannot study in many colleges at the same time.

Many-to-many
 • An entity in A is associated with any number (zero or more) of entities in B,
and an entity in B is associated with any number (zero or more) of entities
in A.
• For example, a can be assigned to many projects and a project can be
assigned to many students.

Keys
• It is used to uniquely identify any record or row of data from the table. It is
also used to establish and identify relationships between tables.
• For example: In Student table, ID is used as a key because it is unique for
each student.
Database supports the following types of keys.
• Primary Key
• Super Key
• Candidate Key
• Alternate Key
• Composite Key
• Foreign Key
 Super Key:-
• Super key is a set of one or more than one keys that can be used to uniquely
identify the record in table. A Super key for an entity is a set of one or more
attributes whose combined value uniquely identifies the entity in the entity
set. Super Key is superset of candidate key. If anyone adds additional
attributes to primary key the resulting would still identify the entity.
• Example:-

Candidate Key
• Candidate Key − A minimal super key is called a candidate key. An entity
set may have more than one candidate key.
 • A Candidate key is an attribute or set of attributes that uniquely identifies a
record in a table.
• Among the set of candidate, one candidate key is chosen as Primary Key.
So a table can have multiple candidate keys but each table can have
maximum one primary key. For Example Roll_No & Student_name are
candidate keys and Roll_No is also candidate key.
• Example of Candidate key:-

Primary Key
• Primary Key − A primary key is one of the candidate keys chosen by the
database designer to uniquely identify the entity set.
• Primary key is a set of one or more fields (columns) of a table that uniquely
identify a record in table. It cannot contain Null value and duplicate value.
• A table can have only one primary key and one candidate key can select as a
primary key.
• The primary key should be chosen such that its attributes are never or rarely
changed, for example,

Rules for defining Primary key:-

• All the values in the column chosen as the primary key must be unique.
 • Each and every table can have only one primary key.
• No value in the primary key column can be NULL.
• You cannot insert a new row with a pre-existing primary key.

Alternate Key:-
• Alternate Key or Secondary Key is the key that has not been selected to be
the primary key, but are candidate keys. However, it is considered a
candidate key for the primary key.
• Alternate keys are candidate keys that are not selected as primary key.
Alternate key is also called “Secondary Key.
Example:

Composite Key:-
Composite key is a combination of more than one attributes that can be used to
uniquely identity each record. It is also known as “Compound” key. A composite
key may be a candidate or primary key.
Example:
Foreign key
• Foreign keys are the column of the table which is used to point to the
primary key of another table.
• In a company, every employee works in a specific department, and
employee and department are two different entities. So we can't store the
information of the department in the employee table. That's why we link
these two tables through the primary key of one table.
• We add the primary key of the DEPARTMENT table, Department_Id as a
new attribute in the EMPLOYEE table.
• Now in the EMPLOYEE table, Department_Id is the foreign key, and both
the tables are related.

Enhanced Entity relationship (EER) & Object Modeling:-

The EER Model includes all modeling concept of ER Model and the concepts of
specialization, generalization, higher- and lower-level entity sets, attribute
inheritance, and aggregation. EER is a high-level data model that incorporates the
extensions to the ER model. Enhanced entity-relationship models, also known as
extended entity-relationship models. Enhanced ER Model is high-level models that
represent the requirements and complexities of complex databases.

Features of EER Model:-

 EER creates a design more accurate to database schemas.
  It reflects the data properties and constraints more precisely.
 It includes all modeling concepts of the ER model.
 Diagrammatic technique helps for displaying the EER schema.
 It includes the concept of specialization and generalization.
 It is used to represent a collection of objects that is union of objects of
different of different entity types.

Super Class
 Super class is an entity type that has a relationship with one or more
subtypes.
 The general entity (super class) can be sub divide into several entities
(subclass).
For example: Shape super class is having sub groups as Square, Circle, and
Triangle.
 Higher- and lower-level entity sets also may be designated by the terms
super class and subclass, respectively.
2. Sub Class
 Sub class is a group of entities with unique attributes. It is member of super
class.
 Sub class inherits properties and attributes from its super class.
For example: Square, Circle, Triangle are the sub class of Shape super
class.

Generalization
  Generalization is like a bottom-up approach in which two or more entities of
lower level combine to form a higher level entity if they have some
attributes in common.
 In generalization, an entity of a higher level can also combine with the
entities of the lower level to form a further higher level entity.
 Generalization is more like subclass and super class system, but the only
difference is the approach. Generalization uses the bottom-up approach.
 In generalization, entities are combined to form a more generalized entity,
i.e., subclasses are combined to make a super class.
 Generalization is a simple inversion of specialization.
 For example, Employee and Student entities can be generalized and create a
higher level entity Person.

person
ID
name address

employee student
Generalization
salary tot_credit

instructor Secretary
rank hours_per_week

Generalization

Specialization
 Specialization is a top-down approach, and it is opposite to Generalization.
In specialization, one higher level entity can be broken down into two lower
level entities.
 Specialization is used to identify the subset of an entity set that shares some
distinguishing characteristics.
 Normally, the super class is defined first, the subclass and its related
attributes are defined next, and relationship set are then added.
  The process of designating sub groupings within an entity set is called
specialization.

For example: person entity can be specialized as employee or customer.

Specialization

Attribute Inheritance
Inheritance is an important feature of Generalization and Specialization. It
allows lower-level entities to inherit the attributes of higher-level entities.

A lower-level entity set inherits all the attributes and relationship participation of
the higher-level entity set to which it is linked.

A crucial property of the higher- and lower-level entities created by specialization

and generalization is attribute inheritance. The attributes of the higher-level entity
sets are said to be inherited by the lower-level entity sets.
For example, the attributes of a Person class such as name, age, and gender can be
inherited by lower-level entities such as Student or Teacher.

Aggregation
In aggregation, the relation between two entities is treated as a single entity. In
aggregation, relationship with its corresponding entities is aggregated into a higher
level entity. Aggregation is an abstraction through which relationships are treated
as higher-level entities.

For example: We create a binary relation between works_on and manager.

Employee works in branch and employee works on job is treated as a single entity
and which relates to manager entity.

Reduction of an ER Diagrams to Tables

• The database can be represented using the notations, and these notations can be reduced to
a collection of tables.
• In the database, every entity set or relationship set can be represented in tabular form.
• Reduce the following the E-R diagram into tables:
 So, the collection of relations is as follows:
• Song ( Sgld, STitle, MusDir, Film, Lyrics, Singers )
• Participant ( PId, PName, PPIace, Age )
• Judge ( JId, JName, Exp )
• Performs ( Sgld, PId )
• Judgement ( JId, PId, Grade, Rem )
From the E-R diagram
 The entity sets 'Song', 'Participant' and 'Judge' will be reduced to three relations.
 The relationship sets 'Performs' and 'Judgment' are reduced to the fourth and fifth relations.
 The attributes 'Sgld', 'STitle', 'MusDir', 'Singers', 'Film' and 'Lyrics' will be reduced as
the columns of relation 'Song'. The attribute 'Sgld' will be underlined in the relation.
 The attributes 'PId', 'PName', 'PPlace' and 'Age' will be reduced to the columns of relation
'Participant'. The column name 'PId' will be underlined because it is primary key.
 The attributes Jid', 'JName' and 'Exp' will be reduced as the columns of relation 'Judge'. The
column name 'JId' will be underlined in the relation.
 The attributes 'Sgld' and 'PId' will be reduced to the column names of relation 'Performs'. The
composition of column names 'Sgld' and 'PId' becomes the primary key attribute of the
relation ‘Performs'.
 The attributes 'Grade' and 'Rem' will be reduced to the columns names of relation 'Judgment
in addition to 'PId' of 'Participant' and 'JId' of 'Judge'. The composition of column names 'PId'
and 'JId' becomes the primary key attribute of the relation 'Judgment'.

Relationship of higher degree

 • Relationship is an association between two or more entities it is represented by diamond
symbol. All relations have 3 components :
• Name:-It is the title or entity identifier such goods bought
• Degree: It represents the number of attributes associated with the table.
Cardinality: It defines the maximum number of relationship instances in which an entity can participate

• The degree of relationship can be defined as the number of occurrences in one entity that is
associated with the number of occurrences in another entity.
• There is the three degree of relationship:
One-to-one (1:1) One-to-many (1:M) Many-to-many (M:N)

1. One-to-one
• In a one-to-one relationship, one occurrence of an entity relates to only one occurrence in
another entity.
• A one-to-one relationship rarely exists in practice.
• For example: if an employee is allocated a company car then that car can only be driven by that
employee.
One-to-many
• In a one-to-many relationship, one occurrence in an entity relates to many occurrences in
another entity.
• For example: An employee works in one department, but a department has many employees.
• Therefore, department and employee have a one-to-many relationship.
Many-to-many
• In a many-to-many relationship, many occurrences in an entity relate to many occurrences in
another entity.
• Same as a one-to-one relationship, the many-to-many relationship rarely exists in practice.
• For example: At the same time, an employee can work on several projects, and a project has a
team of many employees.
• Therefore, employee and project have a many-to-many relationship.
Degree of Relationship: It is the number of entities associated in relationship.
 Unary or Recursive Relationship: - It is the relationship type is between
entities in a single entity. For Example:-

 Binary Relationship: - It is relationship between two entities.

 Ternary Relationship:-It is relationship between three entities.

SOLVED Question of ER diagram

Q1. Construct an E-R diagram for a car insurance company whose customers own
one or more cars each. Each car has associated with it zero to any number of
recorded accidents.
Solution:-

ER Diagram of CAR Insurance Company

Question Draw ER diagram for Employee project Management
Solution

Q2. Design an E-R diagram for keeping track of exploits of your favorite sports
team. You should store the matches played, the scores in each match, the players in
each match, and individual player statistics for each match. Summary statistics
should be modeled as derived attributes.
Answer:

Q3 A university registrar’s office maintains data about the following entities: (a)
courses, including number, title, credits, syllabus, and prerequisites; (b) course
offerings, including course number, year, semester, section number, instructor(s),
timings, and classroom; (c) students, including student-id, name, and program;
and (d) instructors, including identification number, name, department, and title.
Further, the enrollment of students in courses and grades awarded to students in
each course they are enrolled for must be appropriately modeled. Construct an E-
R diagram for the registrar’s office. Document all assumptions that you make
about the mapping constraints.
Solution:-

Q4. Consider a database used to record the marks that students get in different
exams of different course offerings.
a. Construct an E-R diagram that models exams as entities, and uses a ternary
relationship, for the above database.
b. Construct an alternative E-R diagram that uses only a binary relationship
between students and course-offerings. Make sure that only one relationship exists
between a particular student and course-offering pair, yet you can represent the
marks that a student gets in different exams of a course offering.

Ans 4A)
Ans: 4B)

Q5. Construct an E-R diagram for a hospital with a set of patients and a set of
medical doctors. Associate with each patient a log of the various tests and
examinations conducted.
Q6. Construct E R Diagram for ONLINE Book Store

Q7.Construct ER digram for bank database.

Q8.Construct ER digram for Library management database.