DATABASE MANAGEMENT SYSTEMS

UNIT I
Syllabus:
Introduction: Database system, Characteristics (Database Vs File System), Database
Users (Actors on Scene, Workers behind the scene), Advantages of Database systems,
Database applications. Brief introduction of different Data Models; Concepts of Schema,
Instance and data independence; Three tier schema architecture for data independence;
Database system structure, environment, Centralized and Client Server architecture for
the database.
Entity Relationship Model: Introduction, Representation of entities, attributes, entity set,
relationship, relationship set, constraints, sub classes, super class, inheritance,
specialization, generalization using ER Diagrams.
DATABASE MANAGEMENT SYSTEMS UNIT – I

Introduction

The information storage and retrieval has become very important in our day-to-day life. The
old era of manual system is no longer used in most of the places. For example, to book your
airline tickets or to deposit your money in the bank the database systems may be used. The
database system makes most of the operations automated. A very good example for this is the
billing system used for the items purchased in a super market. Obviously this is done with the
help of a database application package. Inventory systems used in a drug store or in a
manufacturing industry are some more examples of database. We can add similar kind of
examples to this list.
Apart from these traditional database systems, more sophisticated database systems are used in
the Internet where a large amount of information is stored and retrieved with efficient search
engines. For instance, [Link] is a famous web site that enables users to
search for their favorite information on the net. In a database we can store starting from text
data to very complex data like audio, video, etc.

Database Management Systems (DBMS)

A database is a collection of related data stored in a standard format, designed to be shared by

multiple users. A database is defined as “A collection of interrelated data items that can be
processed by one or more application programs”.
A database can also be defined as “A collection of persistent data that is used by the application
systems of some given enterprise”. An enterprise can be a single individual (with a small
personal database), or a complete corporation or similar large body (with a large shared
database), or anything in between.

Example: A Bank, a Hospital, a University, a Manufacturing company

Data
Data is the raw material from which useful information is derived. The word data is the plural
of Datum. Data is commonly used in both singular and plural forms. It is defined as raw facts
or observations. It takes variety of forms, including numeric data, text and voice and images.
Data is a collection of facts, which is unorganized but can be made organized into useful
information. The term Data and Information come across in our daily life and are often
interchanged.
Example: Weights, prices, costs, number of items sold etc.
Information
Data that have been processed in such a way as to increase the knowledge of the person who
uses the data. The term data and information are closely related. Data are raw material
resources that are processed into finished information products. The information as data that
has been processed in such way that it can increase the knowledge of the person who uses it.
In practice, the database today may contain either data or information.
Data Processing
The process of converting the facts into meaningful information is known as data processing.
Data processing is also known as information processing.
Metadata
Data that describe the properties or characteristics of other data.

1
DATABASE MANAGEMENT SYSTEMS UNIT – I

Data is only become useful when placed in some context. The primary mechanism for
providing context for data is Metadata. Metadata are data that describe the properties, or
characteristics of other data. Some of these properties include data definition, data structures
and rules or constraints. The Metadata describes the properties of data but do not include that
data.

Database System Applications

Databases are widely used. Here are some representative applications:

1. Banking: For customer information, accounts, and loans, and banking transactions.
2. Airlines: For reservations and schedule information. Airlines were among the first to use
databases in a geographically distributed manner - terminals situated around the world
accessed the central database system through phone lines and other data networks.
3. Universities: For student information, course registrations, and grades.
4. Credit card transactions: For purchases on credit cards and generation of monthly
statements.
5. Telecommunication: For keeping records of calls made, generating monthly bills,
maintaining balances on prepaid calling cards, and storing information about the
communication networks.
6. Finance: For storing information about holdings, sales, and purchases of financial
instruments such as stocks and bonds.
7. Sales: For customer, product, and purchase information.
8. Manufacturing: For management of supply chain and for tracking production of items in
factories, inventories of items in warehouses / stores, and orders for items.
9. Human resources: For information about employees, salaries, payroll taxes and benefits,
and for generation of paychecks.

File Systems Versus A DBMS (Characteristics)

File Oriented approach:
The traditional file oriented approach to information processing has for each application aseparate
aster file and its own set of personal file. In file oriented approach the program dependent on the files
and files become dependent on the files and files become dependents upon the programs

Disadvantages of file oriented approach:

1) Data redundancy and inconsistency:

The same information may be written in several files. This redundancy leads to higher storage and
access cost. It may lead data inconsistency that is the various copies of the same data may longer agree
for example a changed customer address may be reflected in single file but not else where in the
system.
2) Difficulty in accessing data : The conventional file processing system do not allow data to retrieved
in a convenient and efficient manner according to user choice.
3) Data isolation : Because data are scattered in various file and files may be in different formats with
new application programs to retrieve the appropriate data is difficult.

2
DATABASE MANAGEMENT SYSTEMS UNIT – I
4) Integrity Problems: Developers enforce data validation in the system by adding appropriate code in
the various application program. How ever when new constraints are added, it is difficult to change the
programs to enforce them.
5) Atomicity:

It is difficult to ensure atomicity in a file processing system when transaction failure occurs due to over
failure, networking problems etc. (atomicity: either all operations of the transaction are reflected
properly in the database or non are)
6) Concurrent access:

In the file processing system it is not possible to access a same file for transaction at same time
7) Security problems: There is no security provided in file processing system to secure the data from
unauthorized user access.

Database Vs File system

Database File system

[Link] is easy to design 1. It is expansive to design
2. DBMS is a software for managing the 2. File system is a software that manages
database. and organizes the files in a storage
medium
within a computer.
3. In DBMS there is no redundant data. 3. Redundant data can be present in a file
system.
4. It provides backup and recovery of data even 4. It doesn’t provide backup and
if it is lost. recovery of data if it is lost.
5. Efficient query processing is there in DBMS. 5. There is no efficient query processing in
file system.
6. There is more data consistency because of the 6. There is less data consistency in file
process of normalization. system.
7. DBMS has more security mechanisms as 7. File systems provide less security in
compared to file system. comparison to DBMS.

3
DATABASE MANAGEMENT SYSTEMS UNIT – I

Database Users and User Interfaces: There are four different types of database-system
users, differentiated by the way they expect to interact with the system. Different types
of user interfaces have been designed for the different types of users.

Naive users are unsophisticated users who interact with the system by using
predefined user interfaces, such as web or mobile applications. The typical user
interface for naıve users is a forms interface, where the user can f ill in appropriate
fields of the form. Naıve users may also view read reports generated from the
database. As an example, consider a student, who during class registration period,
wishes to register for a class by using a web interface. Such a user connects to a web
application program that runs at a web server. The application first verifies the
identity of the user and then allows her to access a form where she enters the desired
information. The form information is sent back to the web application at the server,
which then determines if there is room in the class (by retrieving information from the
database) and if so adds the student information to the class roster in the database.
Application programmers are computer professionals who write application
programs. Application programmers can choose from many tools to develop user
interfaces.

Sophisticated users interact with the system without writing programs. Instead, they
form their requests either using a database query language or by using tools such as
data analysis software. Analysts who submit queries to explore data in the database
fall in this category.

Database Administrator One of the main reasons for using DBMSs is to have central
control of both the data and the programs that access those data. A person who has
such central control over the system is called a database administrator (DBA). The
functions of a DBA include:

Schema definition. The DBA creates the original database schema by executing a set
of data definition statements in the DDL.

Storage structure and access-method definition. The DBA may specify some
parameters pertaining to the physical organization of the data and the indices to be
created. Schema and physical-organization modification. The DBA carries out changes
to the schema and physical organization to reflect the changing needs of the
organization, or to alter the physical organization to improve performance.

Granting of authorization for data access. By granting different types of

authorization, the database administrator can regulate which parts of the database
various users can access. The authorization information is kept in a special system
structure that the database system consults whenever a user tries to access the data in
the system.

4
DATABASE MANAGEMENT SYSTEMS UNIT – I
Routine maintenance. Examples of the database administrator's routine maintenance
activities are: Periodically backing up the database onto remote servers, to prevent loss
of data in case of disasters such as flooding. Ensuring that enough free disk space is
available for normal operations, and upgrading disk space as required. Monitoring
jobs running on the database and ensuring that performance is not degraded by very
expensive tasks submitted by some users.

Advantages of DBMS

One of the main advantages of using a database management system is that the organization
can exert via the DBA, centralized management and control over the data. The database
administrator is the focus of the centralized control.

The following are the major advantages of using a Database Management System (DBMS):
Data independence: Application programs should be as independent as possible from details
of data representation and storage. The DBMS can provide an abstract view of the data to
insulate application code from such details.

Efficient data access: A DBMS utilizes a variety of sophisticated techniques to store and
retrieve data efficiently. This feature is especially important if the data is stored on external
storage devices.

Data integrity and security: The DBMS can enforce integrity constraints on the data. The
DBMS can enforce access controls that govern what data is visible to different classes of users.

Data administration: When several users share the data, centralizing the administration of data
can offer significant improvements. It can be used for organizing the data representation to
minimize redundancy and for fine-tuning the storage of the data to make retrieval efficient.

Concurrent access and crash recovery: A DBMS schedules concurrent accesses to the data in
such a manner that users can think of the data as being accessed by only one user at a time.
Further, the DBMS protects users from the effects of system failures. .

Reduced application development time: Clearly, the DBMS supports many important
functions that are common to many applications accessing data stored in the DBMS.

Data Models
Data Model is a collection of high-level data description constructs that hide many low-level
storage details. A DBMS allows a user to define the data to be stored in terms of a data model. Most
database management systems today are based on the Relational data model. Relational models
include – IBM’s DB2, Informix, Oracle, Sybase, Microsoft’s Access, Foxbase, Paradox, Tandem and
Teradata.
Data Model is a collection of conceptual tools for describing data, data relationships, data semantics,
and consistency constraints.
The data models can be classified into four different categories:
Relational Model
Entity-Relationship Model
Semi-structured Data Model.
Object-Based Data Model.

Relational Model: The relational model uses a collection of tables to represent both data and the
5
DATABASE MANAGEMENT SYSTEMS UNIT – I
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, and a vast majority of currentdatabase
systems are based on the relational model.

Entity-Relationship Model.:The entity-relationship (ER) 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.
Semi-structured Data Model: The semi-structured data model permits the specification of data where
individual data items of the same type may have different sets of attributes. This is in contrast to the
data models mentioned earlier, where every data item of a particular type must have the same set of
attributes.
JSON and Extensible Markup Language (XML) are widely used semi-structured data representations.
Object-Based Data Model: Object-oriented programming (especially in Java, C++, or C#) has
become the dominant software-development methodology. This led initially to the development of a
distinct object-oriented data model, but today the concept of objects is well integrated into relational
databases. Standards exist to store objects in relational tables. Database systems allow procedures to
be stored in the database system and executed by the database system. This can be seen as extending
the relational model with notions of encapsulation, methods, and object identity.

Data Abstraction and three levels of data abstraction

For the system to be usable, it must retrieve data efficiently. The need for efficiency has led database
system developers to use complex data structures to represent data in the database. Since many
database-system users are not computer trained, developers hide the complexity from users through
several levels of data abstraction, to simplify users' interactions with the system:
Physical level: The lowest level of abstraction describes how the data are actually stored. The physical
level describes complex low-level data structures in detail.
Logical level: The next-higher level of abstraction describes what data are stored in the database, and
what relationships exist among those data. The logical level thus describes the entire database in terms
of a small number of relatively simple structures. Although implementation of the simple structures at
the logical level may involve complex physical-level structures, the user of the logical level does not
need to be aware of this complexity. This is referred to as physical data independence. Database
administrators, who must decide what information to keep in the database, use the logical level of
abstraction.
Viewlevel: The highest level of abstraction describes only part of the entire database. Even though the
logical level uses simpler structures, complexity remains because of the variety of information stored in
a large database. Many users of the database system do not need all this information; instead, they need
to access only a part of the database. The view level of abstraction exists to simplify their interaction
with the system. The system may provide many views for the same database. Below figure shows the
relationship among the three levels of abstraction.

6
DATABASE MANAGEMENT SYSTEMS UNIT – I

Instances and Schemas

Databases change over time as information is inserted and deleted. The collection of information stored in the
database at a particular moment is called an instance of the database. The overall design of the database is called
the database schema. The concept of database schemas and instances can be understood by analogy to a program
written in a programming language. A database schema corresponds to the variable declarations (along with
associated type definitions) in a program. Each variable has a particular value at a given instant. The values of the
variables in a program at a point in time correspond to an instance of a database schema.
Database systems have several schemas, partitioned according to the levels of abstraction. The physical schema
describes the database design at the physical level, while the logical schema describes the database design at the
logical level. A database may also have several schemas at the view level, sometimes called subschemas, that
describe different views of the database

Data Independence
Application programs are insulated from changes in the way the data is structured and stored.
Data independence is achieved through use of the three levels of data abstraction.
Logical data independence: users can be shielded from changes in the logical structure of the
data, or changes in the choice of relations to be stored. This is the independence to change the
conceptual schema without having to change the external schemas and their application
programs.
Physical data independence: the conceptual schema insulated users from changes in physical
storage details. This is the independence to change the internal schema without having to change
the conceptual schema.

1.1 Architecture of a DBMS

The functional components of a database system can be broadly divided into query processor
components and storage manager components. The query processor includes:
1. DML Compiler: It translates DML statements in a query language into low-level instructions that
the query evaluation engine understands.
2. Embedded DML Pre-compiler: It converts DML statements embedded in an application
program to normal procedure calls in the host language. The pre-compiler must interact with
the DML compiler to generate the appropriate code.
3. DDL Interpreter: It interprets DDL Stateline its and records them in a set of tables containing

7
DATABASE MANAGEMENT SYSTEMS UNIT – I

metadata.
4. Transaction Manager: Ensures that the database remains in a consistent (correct) state despite
system failures, and that concurrent transaction executions proceed without conflicting.
5. File Manager: Manages the allocation of space on disk storage and the data structures used to
represent information stored on disk.
6. Buffer Manager: Is responsible for fetching data from disk storage into main memory and
deciding what data to cache in memory.
Also some data structures are required as part of the physical system implementation:
1. Data Files: The data files store the database by itself.

2. Data Dictionary: It stores metadata about the structure of the database, as it is used heavily.

3. Indices: It provides fast access to data items that hold particular values.

4. Statistical Data: It stores statistical information about the data in the database. This
information used by the query processor to select efficient ways to execute a query.

1.2 People Who Deal With Databases

Quite a variety of people are associated with the creation and use of databases. Obviously, there
are database implementors, who build DBMS software, and end users who wish to store and use
data in a DBMS.
Database implementors work for vendors such as IBM or Oracle. End users come from a diverse
and increasing number of fields.

8
DATABASE MANAGEMENT SYSTEMS UNIT – I

In addition to end users and implementors, two other classes of people are associated with a
DBMS: application programmers and database administrators (DBAs).
Database application programmers develop packages that facilitate data access for end users,
who are usually not computer professionals, using the host or data languages and software tools
that DBMS vendors provide.

The task of designing and maintaining the database is entrusted to a professional called the
database administrator.

The DBA is responsible for many critical tasks:

 Design of the conceptual and physical schemas: The DBA is responsible for interacting with
the users of the system to understand what data is to be stored in the DBMS and how it is
likely to be used. Based on this knowledge, the DBA must design the conceptual schema
(decide what relations to store) and the physical schema (decide how to store them).
 Security and authorization: The DBA is responsible for ensuring that unauthorized data
access is not permitted. In general, not everyone should be able to access all the data. In a
relational DBMS, users can be granted permission to access only certain views and relations.
 Data availability and recovery from failures: The DBA must take steps to ensure that if the
system fails, users can continue to access as much of the uncorrupted data as possible.
 Database tuning: The needs of users are likely to evolve with time. The DBA is responsible
for modifying the database, in particular the conceptual and physical schemas, to ensure
adequate performance as user requirements change.

Database Environment
A database management system (DBMS) is a collection of programs that enables users to create
and maintain a database. The DBMS is hence a general-purpose software system that facilitates the
processes of defining, constructing, manipulating, and sharing databases among various users and
applications.

Defining a database involves specifying the data types, structures, and constraints for the data to
be stored in the database.

Constructing the database is the process of storing the data itself on some storage medium that is
controlled by the DBMS.

Manipulating a database includes such functions as querying the database to retrieve specific
data, updating the database to reflect changes in the mini world, and generating reports from the
data.

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

Other important functions provided by the DBMS include protecting the database and maintaining
it over a long period of time.

Protection includes both system protection against hardware or software malfunction (or crashes),
and security protection against unauthorized or malicious access. A typical large database may
have a life cycle of many years, so the DBMS must be able to maintain the database system by
allowing the system to evolve as requirements change over time. We can call the database and
DBMS software together a database system.

9
DATABASE MANAGEMENT SYSTEMS UNIT – I

Three Tier Schema Architecture for data Independence

We now consider the architecture of applications that use databases as their backend.
Database applications can be partitioned into two or three parts, as shown in Figure
Earlier-generation database applications used a two-tier architecture, where the
application resides at the client machine, and invokes database system functionality at the
server machine through query language statements. Below Figure showsTwo-tier and
three-tier architectures. In contrast, modern database applications use a three tier
architecture, where the client machine acts as merely a front end and does not contain any
direct database calls; web browsers and mobile applications are the most commonly used
application clients today. The front end communicates with an application server. 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 provide better security as well as better
performance than two-tier applications.

10
DATABASE MANAGEMENT SYSTEMS UNIT – I

Database System Structure

Below figure shows the architecture of a database system that runs on a centralized server
machine. The figure summarizes how different types of users interact with a database, and
how the different components of a database engine are connected to each other. The
centralized architecture shown in Figure is applicable to shared-memory server architectures,
which have multiple CPUs and exploit parallel processing, but all the CPUs access a common
shared memory. To scale up to even larger data volumes and even higher processing speeds,
parallel databases are designed to run on a cluster consisting of multiple machines. Further,
distributed databases allow data storage and query processing across multiple
geographically separated machines.

11
DATABASE MANAGEMENT SYSTEMS UNIT – I

12
DATABASE MANAGEMENT SYSTEMS UNIT – I

Client/Server Architecture:
The client/server architecture was developed to deal with computing environments in which a
large number of PCs, workstations, file servers, printers, database servers, Web servers, and other
equipment are connected via a network. The idea is to define specialized servers with specific
functionalities.

The resources provided by specialized servers can be accessed by many client machines. The
client machines provide the user with the appropriate interfaces to utilize these servers, as well as
with local processing power to run local applications. This concept can be carried over to
software, with specialized software-such as a DBMS or a CAD (computer-aided design) package
being stored on specific server machines and being made accessible to multiple clients.

The concept of client/server architecture assumes an underlying framework that consists of

many PCs and workstations as well as a smaller number of mainframe machines, connected via
local area networks and other types of computer networks. A client in this framework is typically
a user machine that provides user interface capabilities and local processing. When a client

13
DATABASE MANAGEMENT SYSTEMS UNIT – I
requires access to additional functionality-such as database access-that does not exist at that machine, it
connects to a server that provides the needed functionality.

A server is a machine that can provide services to the client machines, such as file access, printing,
archiving, or database access. In the general case, some machines install only client software, others only
server software, and still others may include both client and server software. However, it is more
common that client and server software usually run on separate machines.

In client/server architecture, the user interface programs and application programs can run on the
client side. When DBMS access is required, the program establishes a connection to the DBMS (which is on the
server side); once the connection is created, the client program can communicate with the DBMS. A standard
called Open Database Connectivity (ODBC) provides an application programming interface (API), which
allows client-side programs to call the DBMS, as long as both client and server machines have the
necessary software installed. Most DBMS vendors provide ODBC drivers for their systems.

Introduction E-R Data Model

The entity-relationship (ER) data model allows us to describe the data involved in a real-world
enterprise in terms of objects and their relationships and is widely used to develop an initial
database design.
The ER model is important primarily for its role in database design. It provides useful concepts
that allow us to move from an informal description of what users want from their database to a
more detailed and precise, description that can be implemented in a DBMS.
Even though the ER model describes the physical database model, it is basically useful in the
design and communication of the logical database model.

Entities, Attributes and Entity Sets

Entity: An entity is an object in the real world that is distinguishable from other objects.
Entity set: An entity set is a collection of similar entities. The Employees entity set with attributes
ssn, name, and lot is shown in the following figure.

14
DATABASE MANAGEMENT SYSTEMS UNIT – I

Attribute: An attribute describes a property associated with entities. Attribute will have a name
and a value for each entity.
Domain: A domain defines a set of permitted values for an attribute
Entity Relationship Model: An ERM is a theoretical and conceptual way of showing data
relationships in software development. It is a database modeling technique that generates an
abstract diagram or visual representation of a system's data that can be helpful in designing a
relational database.
ER model allows us to describe the data involved in a real-world enterprise in terms of objects and
their relationships and is widely used to develop an initial database design.

Representation of Entities and Attributes

ENTITIES: Entities are represented by using rectangular boxes. These are named with the entity
name that they represent.

ATTRIBUTES: Attributes are the properties of entities. Attributes are represented by means of
ellipses. Every ellipse represents one attribute and is directly connected to its entity.

Types of attributes:
 Simple attribute − Simple attributes are atomic values, which cannot be divided further. For
example, a student's roll number is an atomic value.

15
DATABASE MANAGEMENT SYSTEMS UNIT – I
 Composite attribute − 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 − Derived attributes are the attributes that do not exist in the physical
database, but their values are derived from other attributes present in the database. For
example, average_salary in a department should not be saved directly in the database, instead
it can be derived. For another example, age can be derived from data_of_birth.

 Single-value attribute − Single-value attributes contain single value. For example −

Social_Security_Number.
 Multi-value attribute − Multi-value attributes may contain more than one values. For example,
a person can have more than one phone number, email_address, etc.

Relationship and Relationship set

Relationships are represented by diamond-shaped box. Name of the relationship is written inside the
diamond-box. All the entities (rectangles) participating in a relationship, are connected to it by a line.
Types of relationships:

Degree of Relationship is the number of participating entities in a relationship defines the degree of the
relationship. Based on degree the relationships are categorized as

 Unary = degree 1
 Binary = degree 2

16
DATABASE MANAGEMENT SYSTEMS UNIT – I

 Ternary = degree 3
 n-array = degree
Unary Relationship: A relationship with one entity set. It is like a relationship among 2 entities of
same entity set. Example: A professor ( in-charge) reports to another professor (Head Of the Dept).

Binary Relationship: A relationship among 2 entity sets. Example: A professor teaches a course
and a course is taught by a professor.

Ternary Relationship: A relationship among 3 entity sets. Example: A professor teaches a course
in so and so semester.

n-array Relationship: A relationship among n entity sets.

Cardinality:
Defines the number of entities in one entity set, which can be associated with the number of
entities of other set via relationship set. Cardinality ratios are categorized into 4. They are.

1. One-to-One relationship: When only one instance of entities is associated with the
relationship, then the relationship is one-to-one relationship. Each entity in A is associated
with at most one entity in B and each entity in B is associated with at most one entity in A.

17
DATABASE MANAGEMENT SYSTEMS UNIT – I

2. One-to-many relationship: When more than one instance of an entity is associated with a
relationship, then the relationship is one-to-many relationship. Each entity in A is associated
with zero or more entities in B and each entity in B is associated with at most one entity in A.

3. Many-to-one relationship: When more than one instance of entity is associated with the
relationship, then the relationship is many-to-one relationship. Each entity in A is associated
with at most one entity in B and each entity in B is associated with 0 (or) more entities in A.

4. Many-to-Many relationship: If more than one instance of an entity on the left and more than
one instance of an entity on the right can be associated with the relationship, then it depicts
many-to-many relationship. Each entity in A is associated with 0 (or) more entities in B and
each entity in B is associated with 0 (or) more entities in A.

18
DATABASE MANAGEMENT SYSTEMS UNIT – I

Relationship Set:
A set of relationships of similar type is called a relationship set. Like entities, a relationship too can
have attributes. These attributes are called descriptive attributes.

Participation Constraints:
 Total Participation − If Each entity in the entity set is involved in the relationship then the
participation of the entity set is said to be total. Total participation is represented by double
lines.
 Partial participation − If, Not all entities of the entity set are involved in the relationship then
such a participation is said to be partial. Partial participation is represented by single lines.
Example:

Additional Features Of The ER Model

Key Constraints

Consider a relationship set called Manages between the Employees and Departments entity sets
such that each department has at most one manager, although a single employee is allowed to
manage more than one department. The restriction that each department has at most one manager
is an example of a key constraint, and it implies that each Departments entity appears in at most
one Manages relationship in any allowable instance of Manages. This restriction is indicated in the
ER diagram of below Figure by using an arrow from Departments to Manages. Intuitively, the
arrow states that given a Departments entity, we can uniquely determine the Manages relationship
in which it appears.

19
DATABASE MANAGEMENT SYSTEMS UNIT – I

Key Constraints for Ternary Relationships

If an entity set E has a key constraint in a relationship set R, each entity in an instance of E appears
in at most one relationship in (a corresponding instance of) R. To indicate a key constraint on entity
set E in relationship set R, we draw an arrow from E to R.

Below figure show a ternary relationship with key constraints. Each employee works in at most one
department, and at a single location.

Weak Entities

Strong Entity set: If each entity in the entity set is distinguishable or it has a key then such an entity
set is known as strong entity set.

Weak Entity set: If each entity in the entity set is not distinguishable or it doesn't has a key then
such an entity set is known as weak entity set.

eno is key so it is represented by solid underline. dname is partial key. It can't distinguish the
tuples in the Dependent entity set. so dname is represented by dashed underline.
Weak entity set is always in total participation with the relation. If entity set is weak then the
relationship is also known as weak relationship, since the dependent relation is no longer needed
when the owner left.
Ex: policy dependent details are not needed when the owner (employee) of that policy left or fired
from the company or expired. The detailed ER Diagram is as follows.

20
DATABASE MANAGEMENT SYSTEMS UNIT – I

The cardinality of the owner entity set is with weak relationship is 1 : m. Weak entity set is
uniquely identifiable by partial key and key of the owner entity set.
Dependent entity set is key to the relation because the all the tuples of weak entity set are
associated with the owner entity set tuples.

Dependents is an example of a weak entity set. A weak entity can be identified uniquely only by
considering some of its attributes in conjunction with the primary key of another entity, which is
called the identifying owner.
The following restrictions must hold:
 The owner entity set and the weak entity set must participate in a one-to-many relationship
set (one owner entity is associated with one or more weak entities, but each weak entity has
a single owner). This relationship set is called the identifying relationship set of the weak
entity set.
 The weak entity set must have total participation in the identifying relationship set

E-R Diagrams Implementation

Now we are in a position to write the ER diagram for the Company database which was introduced
in the beginning of this unit. The readers are strictly advised to follow the steps shown in this unit
to design an ER diagram for any chosen problem.

Step 1: Identify the Strong and Weak Entity Sets

After careful analysis of the problem we come to a conclusion that there are four possible entity sets
as shown below:
1. Employees Strong Entity Set
2. Departments Strong Entity Set
3. Projects Strong Entity Set
4. Dependents Weak Entity Set

Step 2: Identify the Relevant Attributes

The next step is to get all the attributes that are most applicable for each entity set. Do this work by
considering each entity set in mind and also the type of attributes. Next job is to pick the primary
key for strong entity sets and partial key for weak entity sets.

Example: Following are the attributes:

1. Employees SSN. Name, Addr, DateOfBirth, Sex, Salary
2. Departments DNo. DName, DLocation
3. Projects PNo. PName, PLocation
4. Dependents (weak) DepName, DateOf Birth, Sex, Relationship

21
DATABASE MANAGEMENT SYSTEMS UNIT – I

The underlined attributes are the primary keys and DepName is the partial key of Dependents.
Also, DLocation may be treated as a multivalued attribute.

Step 3: Identify the Relationship Sets

In this step we need to find all the meaningful relationship sets among possible entity sets. This
step is very tricky, as redundant relationships may lead to complicated design and in turn a bad
implementation.

Example: Let us show below what the possible relationship sets are:
1. Employees and Departments WorksFor
2. Employees and Departments Manages
3. Departments and Projects Controls
4. Projects and Employees WorksOn
5. Dependents and Employees Has
6. Employees and Employees Supervises

Some problems may not have recursive relationship sets but some do have. In fact, our Company
database has one such relationship set called Supervises. You can complete this step adding
possible descriptive attributes of the relationship sets (Manages has StartDate and WorksOn has
Hours).

Step 4: Identify the Cardinality Ratio and Participation Constraints

This step is relatively a simple one. Simply apply the business rules and your common sense. So,
we write the structural constraints for our example as follows:

1. WorksFor N: 1 Total on either side

2. Manages 1: 1 Total on Employees and Partial on Departments side
3. Controls 1: N Total on either side
4. WorksOn M: N Total on either side
5. Has 1: M Total on Dependents and Partial on Employees

Step 5: Identify the IS-A and Has-A Relationship Sets

The last step is to look for “is-a” and “has-a” relationships sets for the given problem. As far as
the Company database is concerned, there are no generalization and aggregation relationships
in the Company database.

The complete single ER diagram by combining all the above five steps is shown in figure

22
DATABASE MANAGEMENT SYSTEMS UNIT – I

Class Hierarchies
To classify the entities in an entity set into subclass entity is known as class hierarchies. Example,
we might want to classify Employees entity set into subclass entities Hourly-Emps entity set and
Contract-Emps entity set to distinguish the basis on which they are paid. Then the class hierarchy
is illustrated as follows.

This class hierarchy illustrates the inheritance concept. Where, the subclass attributes ISA (read as
: is a) super class attributes; indicating the “is a” relationship (inheritance concept).Therefore, the

23
DATABASE MANAGEMENT SYSTEMS UNIT – I

attributes defined for a Hourly-Emps entity set are the attributes of Hourly-Emps plus attributes
of Employees (because subclass can have superclass properties). Likewise the attributes defined
for a Contract-Emps entity set are the attributes of Contract-Emps plus attributes of Employees.

Class Hierarchy based on Sub-super Set

1. Specialization: Specialization is the process of identifying subsets (subclasses) of an entity set

(superclass) that share some special distinguishable characteristic. Here, the superclass
(Employee) is defined first, then the subclasses (Hourly-Emps, Contract-Emps, etc.) are defined
next.
In short, Employees is specialized into subclasses.
2. Generalization: Generalization is the process of identifying (defining) some generalized
(common) characteristics of a collection of (two or more) entity sets and creating a new entity
set that contains (possesses) these common characteristics. Here, the subclasses (Hourly-Emps,
Contract-Emps, etc.) are defined first, then the Superclass (Employee) is defined, next.
In shortly, Hourly-Emps and Contract-Emps are generalized by Employees.

Class Hierarchy based on Constraints

1. Overlap constraints: Overlap constraints determine whether two subclasses are allowed
to contain the same entity.

Example: Can Akbar be both an Hourly-Emps entity and a Contract-Emps entity?

The answer is, No.
Other example, can Akbar be both a Contract-Emps entity and a Senior-Emps entity (among
them)?
The answer is, Yes. Thus, this is a specialisation hierarchy property. We denote this by
writing “Contract-Emps OVERLAPS Senior-Emps”.

2. Covering Constraints: Covering constraints determine whether the entities in the

subclasses collectively include all entities in the superclass.

Example: Should every Employee be a Hourly-Emps or .Contract-Emps?

The Answer is, No. He can be a Daily-Emps.
Other example, should every Motor-vehicle (superclass) be a Bike (subclass) or a Car
(subclass)?
The Answer is YES. Thus generalization hierarchies property is that every instance of a
superclass is an instance of a subclass.
We denote this by writing “ Bikes and Cars COVER Motor-vehicles”.

Aggregation
Aggregation allows us to indicate that a relationship set (identified through a dashed box)
participates in another relationship sets. That is, a relationship set in an association between entity
sets. Sometimes we have to model a relationship between a collection of entities and relationships.

Example: Suppose that we have an entity set called Project and that each Project entity is sponsored
by one or more departments. Thus, the sponsors relationship set captures this information but, a
department that sponsors a project, might assign employees to monitor the sponsorship.
Therefore, Monitors should be a relationship set that associates a sponsors relationship (rather

24
DATABASE MANAGEMENT SYSTEMS UNIT – I

than a Project or Department entity) with an Employees entity. However, again we have to define
relationships to associate two or more entities.

Use of Aggregation
We use an aggregation, when we need to express a relationship among relationships. Thus, there
are really two distinct relationships, Sponsors and Monitors, each with its own attributes.

Conceptual Database Design With The ER Model (ER Design Issues)

The following are the ER design issues:

1. Use entry sets attributes

2. Use of Entity sets or relationship sets

3. Binary versus entry relationship sets

4. Aggregation versus ternary relationship.

1. Use of Entity Sets versus Attributes

Consider the relationship set (called Works In2) shown in Figure

Intuitively, it records the interval during which an employee works for a department. Now
suppose that it is possible for an employee to work in a given department over more than one
period.

25
DATABASE MANAGEMENT SYSTEMS UNIT – I

This possibility is ruled out by the ER diagram’s semantics. The problem is that we want to record
several values for the descriptive attributes for each instance of the Works_In2 relationship. (This
situation is analogous to wanting to record several addresses for each employee.) We can address
this problem by introducing an entity set called, say, Duration, with attributes from and to, as
shown in Figure

2. Entity versus Relationship

Consider the relationship set called Manages that each department manager is given a
discretionary budget (dbudget), as shown in below figure, in which we have also renamed the
relationship set to Manages2.

There is at most one employee managing a department, but a given employee could manage
several departments; we store the starting date and discretionary budget for each manager-
department pair. This approach is natural if we assume that a manager receives a separate
discretionary budget for each department that he or she manages.

But what if the discretionary budget is a sum that covers all departments managed by that
employee? In this case each Manages2 relationship that involves a given employee will have the
same value in the dbudget field. In general such redundancy could be significant and could cause
a variety of problems. Another problem with this design is that it is misleading.

We can address these problems by associating dbudget with the appointment of the employee as
manager of a group of departments. In this approach, we model the appointment as an entity set,
say Mgr_Appts, and use a ternary relationship, say Man ages3, to relate a manager, an
appointment, and a department. The details of an appointment (such as the discretionary budget)
are not repeated for each department that is included in the appointment now, although there is
still one Manages3 relationship instance per such department. Further, note that each department
has at most one manager, as before, because of the key constraint. This approach is illustrated in
below Figure.

26
DATABASE MANAGEMENT SYSTEMS UNIT – I

3. Binary versus Ternary Relationships

Consider the ER diagram shown in below figure. It models a situation in which an employee can
own several policies, each policy can be owned by several employees, and each dependent can be
covered by several policies.

Suppose that we have the following additional requirements:

 A policy cannot be owned jointly by two or more employees.

 Every policy must be owned by some employee.
 Dependents is a weak entity set, and each dependent entity is uniquely identified by
taking pname in conjunction with the policyid of a policy entity (which, intuitively,
covers the given dependent).

The first requirement suggests that we impose a key constraint on Policies with respect to Covers,
but this constraint has the unintended side effect that a policy can cover only one dependent. The
second requirement suggests that we impose a total participation constraint on Policies. This
solution is acceptable if each policy covers at least one dependent. The third requirement forces us
to introduce an identifying relationship that is binary (in our version of ER diagrams, although
there are versions in which this is not the case).

Even ignoring the third point above, the best way to model this situation is to use two binary
relationships, as shown in below figure.

27
DATABASE MANAGEMENT SYSTEMS UNIT – I

4. Aggregation versus Ternary Relationships

The choice between using aggregation or a ternary relationship is mainly determined by
the existence of relationship that relates a relationship set to an entity set (or second
relationship set). The choice may also be guided by certain integrity constraints to we
want to express.

Consider the constraint that each sponsorship (of a project by a department) be

monitored by at most one employee. We cannot express this constraint in terms of the
Sponsors2 relationship set. Also we can express the constraint by drawing an arrow
from the aggregated relationship. Sponsors to the relationship Monitors. Thus, the
presence of such a constraint serves as another reason for using aggregation rather than
a ternary relationship set.

28