Database Management System B.

Tech(CSE) II Year II Sem

UNIT II
Data Models: Relational Model, Introduction to relational model, concepts of
domain, attribute, tuple, relation, importance of null values, constraints (Domain,
Key constraints, integrity constraints) and their importance. Entity Relationship
Model, Representation of entities, attributes, entity set, relationship, relationship
set, constraints, sub classes, super class, inheritance, specialization, generalization
using ER Diagrams.

Data Model: Data Model is a collection of concepts that can be used to describe the structure of
database.
Data Models are fundamental entities to introduce abstraction in a DBMS.

Entity is a real-world object that are represented in database. It can be any object,place,person or
class. Data are stored about such entities.
Entity Set:The collection of similar entities is called as Entity Set.

In DBMS we store data in the form of table containing information about entity type like
students, teachers, employees etc. In a school database, a table containing information about all
the students,students here are entities.
Entity is represented by set of Attributes which are properties used to describe an entity.
All entities in a given entity set have the same attributes. For example student have properties
like name, redgno, address etc.
Types of Data Models:
1. Entity Relationship Data Model
2. Relational Data Model
3. Network Data Model
4. Hierarchical Data Model
5. Object oriented Data Model

Relational Data Model

Relational Data Model
Relational model is the most popular model and the most extensively used model.
The relational model proposed by CODD.
The relational model represents the database as a collection of relations(tables). Every row in
the table represents a collection of related data values. These rows in the table denote a real-
world entity or relationship.

The table name and column names are helpful to interpret the meaning of values in each row.
The data are represented as a set of relations. In the relational model, data are stored as tables.
However, the physical storage of the data is independent of the way the data are logically
organized.

Prepared by Ch Samsonu, Assoc.Professor, CSED,KHIT, Guntur

Database Management System B.Tech(CSE) II Year II Sem

The Relational Database is a collection of relations with distinct relation names.

DOMAIN
• Columns in table have a unique name, often referred as attributes in DBMS. A domain is
a unique set of values permitted for an attribute in a table. For example, a domain of
month-of-year can accept January, February….December as possible values, a domain of
integers can accept whole numbers that are negative, positive and zero.

• Domain: It contains a set of atomic values that an attribute can take.

Relational Integrity Constraints

• While designing Relational Model, we define some conditions which must hold for data
present in database are called Constraints.
• These constraints are checked before performing any operation (insertion, deletion and
updation) in database.
• If there is a violation in any of constrains, operation will fail.
Constraints on the Relational database management system is mostly divided into three main
categories are:
1. Domain Constraints
2. Key Constraints
3. Referential Integrity Constraints

Domain Constraints

Domain constraints can be violated if an attribute value is not appearing in the corresponding
domain or it is not of the appropriate data type.

Prepared by Ch Samsonu, Assoc.Professor, CSED,KHIT, Guntur

Database Management System B.Tech(CSE) II Year II Sem

Domain constraints specify that within each tuple, and the value of each attribute must be
unique. This is specified as data types which include standard data types integers, real numbers,
characters, Booleans, variable length strings, etc.

Example:

Create DOMAIN CustomerName

CHECK (value not NULL)

The example shown demonstrates creating a domain constraint such that CustomerName is not
NULL

Key Constraints

An attribute that can uniquely identify a tuple in a relation is called the key of the table. The
value of the attribute for different tuples in the relation has to be unique.

Example:

In the given table, CustomerID is a key attribute of Customer Table. It is most likely to have a
single key for one customer, CustomerID =1 is only for the CustomerName =" Google".

Referential Integrity Constraints

Referential Integrity constraints in DBMS are based on the concept of Foreign Keys. A foreign
key is an important attribute of a relation which should be referred to in other relationships.
Referential integrity constraint state happens where relation refers to a key attribute of a different
or same relation. However, that key element must exist in the table.

In the example, we have 2 relations, Customer and Billing.

Tuple for CustomerID =1 is referenced twice in the relation Billing. So we know

CustomerName=Google has billing amount $300

Prepared by Ch Samsonu, Assoc.Professor, CSED,KHIT, Guntur

Database Management System B.Tech(CSE) II Year II Sem

ENTITY RELATIONSHIP MODEL:

Entity relationship diagram displays the relationships of entity set stored in a database. In other
words, we can say that ER diagrams help you to explain the logical structure of databases. At
first look, an ER diagram looks very similar to the flowchart. However, ER Diagram includes
many specialized symbols, and its meanings make this model unique.

Facts about ER Diagram Model:

• ER model allows you to draw Database Design
• It is an easy to use graphical tool for modeling data
• Widely used in Database Design
• It is a GUI representation of the logical structure of a Database
• It helps you to identifies the entities which exist in a system and the relationships
between those entities

Why use ER Diagrams?

Here, are prime reasons for using the ER Diagram
• Helps you to define terms related to entity relationship modeling
• Provide a preview of how all your tables should connect, what fields are going to be on
each table
• Helps to describe entities, attributes, relationships

Prepared by Ch Samsonu, Assoc.Professor, CSED,KHIT, Guntur

Database Management System B.Tech(CSE) II Year II Sem

• ER diagrams are translatable into relational tables which allows you to build databases
quickly
• ER diagrams can be used by database designers as a blueprint for implementing data in
specific software applications
• The database designer gains a better understanding of the information to be contained in
the database with the help of ERP diagram
• ERD is allowed you to communicate with the logical structure of the database to users

Components of the ER Diagram

This model is based on three basic concepts:
1. Entities
2. Attributes
3. Relationships

Entity

An Entity is a real-world object that are represented in database. It can be any

object,place,person or class.Data are stored about such entities.
Examples of entities:
Person: Employee, Student, Patient
Place: Store, Building
Object: Machine, product, and Car
Event: Sale, Registration, Renewal
Concept: Account, Course
Entities are represented by means of rectangles. Rectangles are named with the entity set 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 (rectangle).

Prepared by Ch Samsonu, Assoc.Professor, CSED,KHIT, Guntur

Database Management System B.Tech(CSE) II Year II Sem

If the attributes are composite, they are further divided in a tree like structure. Every node is then
connected to its attribute. That is, composite attributes are represented by ellipses that are
connected with an ellipse.

Single Valued attribute:

Attributes that can have single value at a particular instance of time are called single valued.
Multivalued attributes: A multi-valued attribute can have more than one value at one time.
These are depicted by double ellipse.

Stored attributes:

The stored attribute are such attributes which are already stored in the database and from which
the value of another attribute is derived is called stored attribute.
Derived attributes are depicted by dashed ellipse. The derived attributes are such attributes for
which the value is derived or calculated from stored attributes. Example age can be calculated
from the date_of_birth attribute.

Prepared by Ch Samsonu, Assoc.Professor, CSED,KHIT, Guntur

Database Management System B.Tech(CSE) II Year II Sem

Relationship
Relationship is nothing but an association among two or more entities. E.g., Tom works in the
Chemistry department.
Example-
‘Enrolled in’ is a relationship that exists between entities Student and Course.

Entities take part in relationships. We can often identify relationships with verbs or verb phrases.
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.

Relationship Set-
A relationship set is a set of relationships of same type.
Example- Set representation of above ER diagram is-

Prepared by Ch Samsonu, Assoc.Professor, CSED,KHIT, Guntur

Database Management System B.Tech(CSE) II Year II Sem

Degree of a Relationship Set-

The number of entity sets that participate in a relationship set is termed as the degree of that
relationship set. Thus,

Degree of a relationship set = Number of entity sets participating in a relationship set

Types of Relationship Sets-

On the basis of degree of a relationship set, a relationship set can be classified into the following
types-

1. Unary relationship set

2. Binary relationship set
3. Ternary relationship set
4. N-ary relationship set
1. Unary Relationship Set-

Unary relationship set is a relationship set where only one entity set participates in a relationship
set.
Example-
One person is married to only one person

2. Binary Relationship Set-

Binary relationship set is a relationship set where two entity sets participate in a relationship set.
Example-
Student is enrolled in a Course

Prepared by Ch Samsonu, Assoc.Professor, CSED,KHIT, Guntur

Database Management System B.Tech(CSE) II Year II Sem

3. Ternary Relationship Set-

Ternary relationship set is a relationship set where three entity sets participate in a relationship
set.
Example-

4. N-ary Relationship Set-

N-ary relationship set is a relationship set where ‘n’ entity sets participate in a relationship set.

Binary Relationship and Cardinality

A relationship where two entities are participating is called a binary relationship. Cardinality is
the number of instance of an entity from a relation that can be associated with the relation.
• One-to-one − When only one instance of an entity is associated with the relationship, it
is marked as '1:1'. The following image reflects that only one instance of each entity
should be associated with the relationship. It depicts one-to-one relationship.

• One-to-many − When more than one instance of an entity is associated with a

relationship, it is marked as '1:N'. The following image reflects that only one instance of
entity on the left and more than one instance of an entity on the right can be associated
with the relationship. It depicts one-to-many relationship.

Prepared by Ch Samsonu, Assoc.Professor, CSED,KHIT, Guntur

Database Management System B.Tech(CSE) II Year II Sem

• Many-to-one − When more than one instance of entity is associated with the
relationship, it is marked as 'N:1'. The following image reflects that more than one
instance of an entity on the left and only one instance of an entity on the right can be
associated with the relationship. It depicts many-to-one relationship.

• Many-to-many − The following image reflects that 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. It depicts many-to-many relationship.

Participation Constraints
• Total Participation − Each entity is involved in the relationship. Total participation is
represented by double lines.
• Partial participation − Not all entities are involved in the relationship. Partial
participation is represented by single lines.

Weak entity and strong entity

Strong Entity
The strong entity has a primary key. Weak entities are dependent on strong entity. Its existence is
not dependent on any other entity.
Strong Entity is represented by a single rectangle:

Prepared by Ch Samsonu, Assoc.Professor, CSED,KHIT, Guntur

Database Management System B.Tech(CSE) II Year II Sem

Weak Entity
The weak entity in DBMS do not have a primary key and are dependent on the parent entity. It
mainly depends on other entities.
Weak Entity is represented by double rectangle:

Example of Strong and Weak Entity

The example of strong and weak entity can be understood by the below figure.

The Strong Entity is Professor, whereas Dependent is a Weak Entity.

ID is the primary key (represented with a line) and Name in Dependent entity is called Partial
Key (represented with a dotted line).
The Strong Entity is Professor, whereas Dependent is a Weak Entity.
ID is the primary key (represented with a line) and Name in Dependent entity is called Partial
Key (represented with a dotted line)

A member of a strong entity set is called dominant entity and member of weak entity set
is called as subordinate entity.

Prepared by Ch Samsonu, Assoc.Professor, CSED,KHIT, Guntur

Database Management System B.Tech(CSE) II Year II Sem

Partial Key :
The set of attributes that are used to uniquely identify a weak entity set is called the Partial key.
Only a bunch of the tuples can be identified using the partial keys. The partial Key of the weak
entity set is also known as a discriminator.
It is just a part of the key as only a subset of the attributes can be identified using it. It is
partially unique and can be combined with other strong entity set to uniquely identify the
tuples.

Partial Key apartment number is shown with a dashed line.

Here we have an apartment as a weak entity and building as a strong entity type connected vi a
‘belongs to’ relationship set. Apartment number is not globally unique i.e. more than one

Prepared by Ch Samsonu, Assoc.Professor, CSED,KHIT, Guntur

Database Management System B.Tech(CSE) II Year II Sem

apartment may have same number globally but it is unique for a particular building since a
building may not have same apartment number. Thus apartment number cannot be primary key
of entity Apartment but it is a partial key shown with a dashed line.

Enhanced Entity Relationship Model (EER Model)

EER is a high-level data model that incorporates the extensions to the original ER model.

It is a diagrammatic technique for displaying the following concepts

• Sub Class and Super Class

• Specialization and Generalization
• Union or Category
• Aggregation
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.
Inheritance
Inheritance is basically the process of basing a class on another class i.e to build a class on a
existing class. The new class contains all the features and functionalities of the old class in
addition to its own.
The class which is newly created is known as the subclass or child class and the original class is
the parent class or the superclass.
Sub class and Super class relationship leads the concept of Inheritance.
• Super Class
Super class is an entity type that has a
relationship with one or more subtypes.
An entity cannot exist in database merely by
being member of any super class.
For example: Shape super class is having sub
groups as Square, Circle, Triangle.
• Sub Class
Sub class is a group of entities with unique
attributes.
Sub class inherits properties and attributes from
its super class.
For example: Square, Circle, Triangle are the sub class of Shape super class.

Prepared by Ch Samsonu, Assoc.Professor, CSED,KHIT, Guntur

Database Management System B.Tech(CSE) II Year II Sem

3.Specialization and Generalization

Generalization
• Generalization is the process of generalizing the entities which contain the properties
of all the generalized entities.
• It is a bottom approach, in which two lower level entities combine to form a higher
level entity.
• Generalization is the reverse process of Specialization.
• It defines a general entity type from a set of specialized entity type.
• It minimizes the difference between the entities by identifying the common features.

Specialization
• Specialization is a process that defines a group entities which is divided into sub groups
based on their characteristic.
• It is a top down approach, in which one higher entity can be broken down into two lower
level entity.
• It maximizes the difference between the members of an entity by identifying the unique
characteristic or attributes of each member.
It defines one or more sub class for the super class and also forms the superclass/subclass
relationship.

Prepared by Ch Samsonu, Assoc.Professor, CSED,KHIT, Guntur

Database Management System B.Tech(CSE) II Year II Sem

3.Category or Union
• Relationship of one super or sub class with more than one super class.
• It can be a total or partial participation.

Aggregation
• Aggregation is a process that represent a relationship between a whole object and its
component parts.
• It abstracts a relationship between objects and viewing the relationship as an object.
• It is a process when two entity is treated as a single entity.
• In this example, the relation between College and Course is acting as an Entity in
Relation with Student

Prepared by Ch Samsonu, Assoc.Professor, CSED,KHIT, Guntur

Database Management System B.Tech(CSE) II Year II Sem

Draw E-R diagram for Hospital management System:

Step 1: E-R Diagram

Step 2: Converting the E-R Diagram into Tables:

Hospital
Hosp-id Primary Key
HCity
HAddress
Hos-Name
Pat-id Foreign key references to Pat-id of Patient table
Doc-id Foreign key references to Doc-id of Doctor table

Prepared by Ch Samsonu, Assoc.Professor, CSED,KHIT, Guntur

Database Management System B.Tech(CSE) II Year II Sem

Patient
Pat-id Primary Key
PName
PAddress
PDiagnosis
Record-id Foreign key references to Record-id of Medical Record table
Hosp-id Foreign key references to Hosp-id of Hospital table

Medical Record
Record-id Primary Key
Problem
Date_of_examination
Pat-id Foreign key references to Pat-id of Patient table

Doctor
Doc-id Primary Key
DName
Qualification
Salary
Hosp-id Foreign key references to Hosp-id of Hospital table

Step 3: Mapping of Attributes to Entites

Step 4: Mapping of Relationships
b. Foreign Key approach
Hosp_patient
Pat-id Hospital table makes foreign key references to Pat-id of Patient table

Hosp-id Patient table makes foreign key references to Hosp-id of Hospital table
Hosp_Doctor
Hosp-id Doctor table makes foreign key references to Hosp-id of Hospital table

Doc-id Hospital table makes foreign key references to Doc-id of Doctor table
PatiPPatient_MedicalRecord
Pat-id Medical Record table makes foreign key references to Pat-id of Patient table

Record-id Patient table makes foreign key references to Record-id of Medical Record table
Step 5: Identifying the relationships
a. Hospital has a set of patients.
Therefore the relations is 1……..N.

Prepared by Ch Samsonu, Assoc.Professor, CSED,KHIT, Guntur

Database Management System B.Tech(CSE) II Year II Sem

b. Hospital has a set of doctors.

Therefore the relations is 1……..N.
c. Doctor are associated with each patient.
Therefore the relations is N……..1.
d. Each patient has record of various test and examination conducted.

Therefore the relations is 1……..N.

Prepared by Ch Samsonu, Assoc.Professor, CSED,KHIT, Guntur

Database Management System B.Tech(CSE) II Year II Sem

EMPLY E-R DIAGRAM:

STUDENT E-R DIAGRAM

Prepared by Ch Samsonu, Assoc.Professor, CSED,KHIT, Guntur