DATA BASE MANAGEMENT SYSTEMS – II CSE II Semester – UNIT 1

UNIT 1 – DATA BASE SYSTEM APPLICATIONS

CONTENTS:
1. Database System Applications:
1.1. A Historical Perspective
1.2. File Systems versus a DBMS
1.3. The Data Model
1.4. Levels of Abstraction in a DBMS
1.5. Data Independence
1.6. Structure of a DBMS
2. Introduction to Database Design:
2.1. Database Design and ER Diagrams
2.2. Entities, Attributes, and Entity Sets,
2.3. Relationships and Relationship Sets,
2.4. Additional Features of the ER Model,
2.5. Conceptual Design With the ER Model

DATA BASE SYSTEM APPLICATIONS

DATA
• Data is a fact or figures or entity.
• When activities in the organization takes place, the effect of these activities need to be
recorded which is known as Data.
INFORMATION
GNIT

• Processed data to carrying out useful the business activities is called information
DATABASE
A database is a collection of data, typically describing the activities of one or more related
organizations.
For example, a university database might contain information about the following:
 Entitiessuch as students, faculty, courses, and classrooms.
 Relationshipsbetween entities, such as students' enrollment in courses, faculty teaching
courses, and the use of rooms for courses.
DATABASE MANAGEMENT SYSTEM
A database management system, or DBMS, is software designed to assist in maintaining and
utilizing large collections of data.

1.1 A HISTORICAL PERSPECTIVE

• From the earliest days of computers, storing and manipulating data have been a major
application focus. The first general-purpose DBMS, called the Integrated Data Store, was
designed by Charles Bachman in the early 1960s. It formed the basis for the network data
model.
• In the late 1960s, IBM developed the Information Management System (IMS) DBMS
which formed the basis for the hierarchical data model. The SABRE system for making
airline reservations was jointly developed by American Airlines and IBM around the same
time, and it allowed several people to access the same data through a computer network.
1
 DATA BASE MANAGEMENT SYSTEMS – II CSE II Semester – UNIT 1

• In 1070, EdgarCodd, at IBM's San Jose Research Laboratory, proposed the relational data
model. This sparked rapid development of several DBMSs based on the relational model.
The benefits of DBMS were widely recognized, and the use of DBMSs for managing
corporate data became standard practice.
 As the popularity of relational DBMS started increasing, soon, IBM, in early 1980s, has
developed a SQL (Structured Query Language) for relational databases through their
SYSTEM/R project.
• Later in late 1980s, SQL was standardized and the version SQL-1999 was adopted by ANSI
(American National Standard Institute and ISO (International Standards Organization).
• During this period, the concept of concurrent execution of database programs, called
transactions, was introduced in DBMS system which allowed the user to run their programs
concurrently. Later in 1999, James Gray won Turing award for his contributions to the field
of database transaction management.
• In the late 1980s and the 1990s, advances have been made in many areas of database
systems. More emphasis is given on new data types such as images and text, and the ability
to support complex analysis of data within of an enterprise.
• Over a period of time, a new database system was created, data warehouses, consolidating
data from several databases, and for carrying out specialized analysis.
• By introduction of enterprise resource planning (ERP) and management resource
planning (MRP) packages exciting new features were added to existing database system.
• Many other packages like Baan, Oracle, PeopleSoft, SAP, and Siebel which were user
GNIT

friendly and allowed the user to carry out the task easily.
• Most significant change in DBMS is through integration of DBMS with internet which
allowed DBMS to store data accessed through web [Link] allowed the users to write their
queries through web forms, and the formatted output are generated using a markup language
such as HTML.
• Today the field is being driven by exciting visions such as multimedia databases, interactive
video, digital libraries and so on.

1.2 FILE SYSTEM vs DBMS

A File System is a collection of raw data files stored in the hard-drive. Files are created and
manipulated by writing programs so the permanent records are stored in various files.

The problems in file processing system are

 Data redundancy and inconsistency
 Difficulty in accessing data
 Data isolation
 Integrity problems
 Atomicity problems
 Security problems

2
 DATA BASE MANAGEMENT SYSTEMS – II CSE II Semester – UNIT 1

DBMS File System

DBMS is a collection of data. In DBMS, File system is a collection of data. In this system,
the user is not required to write the the user has to write the procedures for managing
procedures. the database.
Helps to easily store, retrieve and Helps to store a collection of raw data files into
manipulate data in a database the hard disk
DBMS gives an abstract view of data that File system provides the detail of the data
hides the details. representation and storage of data.
DBMS provides a crash recovery File system doesn't have a crash mechanism, i.e.,
mechanism, i.e., DBMS protects the user if the system crashes while entering some data,
from the system failure. then the content of the file will lost.
DBMS provides a good protection It is very difficult to protect a file under the file
mechanism. system.
DBMS provides higher data consistency
It has data inconsistency
using normalization
DBMS contains a wide variety of
File system can't efficiently store and retrieve the
sophisticated techniques to store and
data.
retrieve the data.
DBMS takes care of Concurrent access of In the File system, concurrent access has many
data using some form of locking. problems like redirecting the file while other
GNIT

deleting some information or updating some

information.
Ex: MySQL, MSSQL, Oracle, DB2 Ex: NFTS and Ext

ADVANTAGES OF A DBMS:

Using a DBMS to manage data has many advantages:

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.
Data integrity and security:
If data is always accessed through the DBMS, the DBMS can enforce integrity constraints on the
data before being accessed. Also, the DBMS can enforce access controlsthat govern what data is
visible to different classes of users.
Data administration:

3
 DATA BASE MANAGEMENT SYSTEMS – II CSE II Semester – UNIT 1

When several users share the data, centralized data administration can be done effectively with
DBMS.

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. This, in conjunction with the high-level interface to the data,
facilitates quick development of applications.

1.3 DATA MODEL

A Database model(or Data base model) defines the logical design and structure of a database.
Data Models are fundamental entities to introduce abstraction in a DBMS. It defines how data
will be stored, accessed and updated in a database management system.

While the Relational Model is the most widely used database model, there are other models too:
 Hierarchical Model
 Network Model
 Entity-relationship Model
GNIT

 Relational Model
 Object Oriented Data Base Model

1.3.1 Hierarchical Model

 This database model organizes data into a tree-like-structure, with a single root, to which all
the other data is linked. The hierarchy starts from the Root data, and expands like a tree,
adding child nodes to the parent nodes.
 In this model, a child node will have a single parent node.
 This model efficiently describes many real-world relationships.
 Hierarchical model represents tree-like structure with one one-to-many relationship between
two different types of data, for example, one department can have many courses, many
professors and many students.
College
Advantages:
 Simplicity: The design is simple and more
logical. Department Infrastructure
 Data Integrity: The data can always be
referred to the parent data for integrity.
Disadvantages: Course Teachers Students
 Searching for data requires the DBMS to run Fig.1.1 Hierarchical Model
through the entire model from top to bottom
until the required information is found, making queries very slow.

4
 DATA BASE MANAGEMENT SYSTEMS – II CSE II Semester – UNIT 1

 This model support only one-to-many relationships, many-to-many relationships are not
supported.

1.3.2 Network Model

 Network Database Model organizes the data using Graph like structures. It allows a record
to have more than one parent.
 In this database model, data is more related as more relationships are established in this
model. Hence accessing the data is also easier and faster. This database model was used to
map many-to-many data relationships.
 This was the most widely used database model, before Relational Model was introduced.
Advantage
College
•The network model is conceptually simple and easy to
design.
• The network model can handle the one to many and Department Infrastructure
many to many relationships.
• The data access is easier and flexible than the
hierarchical model. Students
• The network model is better than the hierarchical
model in isolating the programs from the complex Fig. 1.2 Network Model
physical storage details.
Disadvantage: GNIT

• All the records are maintained using pointers and hence the whole database structure
becomes very complex.
• The insertion, deletion and updating operations of any record require the large number of
pointers adjustments.
• The structural changes to the database are very difficult.
1.3.3 Entity-relationship Model
 This model is useful in developing a conceptual design for the database.
 Entity Relationship model is based on –
Entity – These are representation of a real world entities in code.
Attributes – These are the characteristic properties of entities
Relationship – Logical relationship between various entities involved in the Database
creation.
 One to one
 One to many
 Many to one
 Many to many

 In this database model, relationships are Fig1.3 E-R Model

created by dividing object of interest into
entity and its characteristics into attributes.

5
 DATA BASE MANAGEMENT SYSTEMS – II CSE II Semester – UNIT 1

 E-R Models(semantic data model) model used to pictorially denote entities and the
relationships among them.
 The attribute values that describe each entity become a major part of the data stored in the
database.
 It is very simple and easy to design logical view of data.
In this diagram,
 Rectangle represents the entities. Eg. Doctor and Patient.
 Ellipse represents the attributes. Eg. DocId, Dname, PId, Pname.
 Diamond represents the relationship in ER diagrams. Eg. Doctor diagnoses the Patient.

1.3.4 Relational Model

 In this model, data is organised in two-dimensional tables and the relationship is maintained
by storing a common field.
 This model was introduced by E.F Codd in 1970, and since then it has been the most widely
used database model.
 The basic structure of data in the relational model is tables. Hence, tables are also known as
relations in relational model.
 In relational model, three key terms are heavily used such as relations, attributes, and
domains. A relation nothing but is a table with rows and columns. The named columns of the
relation are called as attributes, and finally the domain(synonym - Data type) is nothing but
the set of values the attributes can take.
Advantages
eid ename salary age
GNIT

• Simple: This model is more simple as compared to 22 Abi 10000 25.0

the network and hierarchical model.
31 Bob 25000 35.5
• Scalable: This model can be easily scaled as we can
add as many rows and columns we want. 58 Akhil 45000 35.0
• The relational database supports both data Fig1.4 Relational Model
independence and structure independence concept
which makes the database design, maintenance, administration and usage much easier than
the other models.
• It is easier to maintain security as compared to other models.

Disadvantages
 Hardware Overheads: For hiding the complexities and making things easier for the user
this model requires more powerful hardware computers and data storage devices.
 Bad Design: As the relational model is very easy to design and use. So the users don't need
to know how the data is stored in order to access it. This ease of design can lead to the
development of a poor database which would slow down if the database grows.
But all these disadvantages are minor as compared to the advantages of the relational model.

1.3.5 Object Oriented Data Model

6
 DATA BASE MANAGEMENT SYSTEMS – II CSE II Semester – UNIT 1

• The real-world problems are more closely represented through the object-oriented data
model.
• In this model, both the data and relationship are present in a single structure known as an
object. In this model, two are more objects are connected through links. We use this link to
relate one object to other objects.
• This can be understood by the example given below.

Employee
Attributes
eid Department
ename Attributes
Did
Age
Salary Dname

Methods Methods
Get_Hired() Change_Dept()

Change_No()
Fig 1.5 Object Oriented Data Model

• The object-oriented data model is based on the object-oriented- programming language

concept, which is now in wide use. GNIT

• Inheritance, polymorphism, overloading. object-identity, encapsulation and information

hiding with methods to provide an interface to objects, are among the key concepts of
object-oriented programming that have found applications in data modeling.
• The following figure shows the difference between relation and object-oriented database
model.

Fig 1.6 Relation vs Object-oriented database model

Advantages
• Object-oriented databases can handle the different types of data, for example, pictures, voice
video, including text, numbers and so on.
• Object-oriented databases provide us code reusability, real world modelling, and improved
reliability and flexibility.
• The object-oriented database is having low maintenance costs because most of the tasks
within the system are encapsulated.

1.4 LEVELS OF ABSTRACTION

7
 DATA BASE MANAGEMENT SYSTEMS – II CSE II Semester – UNIT 1

Data abstraction is a process of hiding the implement details (such as how the data are stored
and maintained) and representing only the essential features to simplify user's interaction with
the system.
The major purpose of a database system is to provide users with an abstract view of the system.
The database description consists of a schema at each of these three levels of abstraction as
shown in fig 1.7:
 Conceptual schema
 Physical schema
 External schema
A physical schema can be defined as the design of a database at its physical level. In this level, it
is expressed how data is stored in blocks of storage.
The process of arriving at a good physical schema is called physical database design.

A logical schema can be defined as the design of the database at its logical level. In this level, the
programmers as well as the database administrator (DBA) work. At this level, data can be
described as certain types of data records which can be stored in the form of data structures.
However, the internal details (such as an implementation of data structure) will be remaining
hidden at this level.
The process of arriving at a good conceptual schema is called conceptual database design

View schema can be defined as the design of the database at view level which generally
describes end-user interaction with database systems.
 Highest level of abstraction. GNIT

 Describes only part of the database for a particular group of users.

 Can provide many different views of a database.

Fig.1.7 Levels of Abstraction in a DBMS

Example: University Database – Schema at the different levels of DBMS is shown below.

Type of Schema Implementation

External Schema View 1: Course info(cid:int,cname:string)

8
 DATA BASE MANAGEMENT SYSTEMS – II CSE II Semester – UNIT 1

View 2: studeninfo(id:int. name:string)

Conceptual Shema Students(id: int, name: string, login: string, age: integer)

Courses(id: int, [Link], credits:integer)

Enrolled(id: int, grade:string)

Physical Schema  Relations stored as unordered files.

 Index on the first column of Students.

1.5 DATA INDEPENDENCE

Data independence refers characteristic of being able to modify the schema at one level of the
database system without altering the schema at the next higher level.
Data independence is achieved through use of the three levels of data abstraction;
There are two types of data independence:
1. Logical Data Independence
 Logical data independence refers characteristic of being able to change the conceptual
schema without having to change the external schema.
 Logical data independence is used to separate the external level from the conceptual view.
 If we do any changes in the conceptual view of the data, then the user view of the data
would not be affected. GNIT

 Logical data independence occurs at the user interface level.

2. Physical Data Independence
 Physical data independence can be defined as the capacity to change the internal schema
without having to change the conceptual schema.
 If we do any changes in the storage size of the database system server, then the Conceptual
structure of the database will not be affected.
 Physical data independence is used to separate conceptual levels from the internal levels.
 Physical data independence occurs at the logical interface level.

1.6 STRUCTURE OF A DBMS

The DBMS accepts SQL commands generated from a variety of user interfaces, produces
query evaluation plans, executes these plans against the database, and returns the answers.

Figure 1.8 shows the structure of a typical DBMS based on the relational data model.
[

Query Evaluation Engine consists of the following components:

 Parser: The query parser breaks the query into tokens. It also ensures that the query is
syntactically and semantically correct. If the query does not have any errors, then it is
converted into an algebraic expression and passes it to the next step.
 Operator Evaluation: Operators in the query is evaluated using several alternative
techniques such as Iteration techniques, Indexing techniques and partitioning techniques.

9
 DATA BASE MANAGEMENT SYSTEMS – II CSE II Semester – UNIT 1

 Optimizer: is responsible to produce the query evaluation plan which gives the most
effective way to execute the user request
 Execution Engine: Finally executes the query as per query evaluation plan and display
the required result.
Files and access methods: It is responsible for the abstraction of file structures stored and for
creating indexes on the files for fast access.
Buffer Manager: It is responsible to bring pages in and out from disk to main memory.
Disk space manager: It manages the space on the disk by providing empty space for new
requests, deleting space allocated for existing files which are deleted by users.

GNIT

Fig 1.6 Architecture of a DBMS

Concurrency control is achieved by:

Transaction manager: It ensures that transactions request and release locks according to
a suitable locking protocol and schedules the execution transactions.
Lock manager: It keeps track of requests for locks and grants locks on database objects
whenthey become available.
Recovery manager: It is responsible for maintaining a log, and restoring the system to a
consistent state after a crash.

10
 DATA BASE MANAGEMENT SYSTEMS – II CSE II Semester – UNIT 1

System Catalog(Data Dictionary): It contains all the information about the database. As the
name suggests, it is the dictionary of all the data items. It contains description of all the tables,
view, data files, indexes, triggers etc.

2. INTRODUCTION TO DATABASE DESIGN

2.1 DATABASE DESIGN AND ER DIAGRAMS

Database design process can be divided into six steps

Requirements Analysis

Conceptual Design
ER Model
Logical Design
Relational Database Schema
Schema Refinement
Normalized Data
Physical Design
GNIT

Application/Security Design

Requirements Analysis
 Requirements Analysis is the process of determining what the database is to be used for.
 It involves interviews with user groups and other stakeholders to identify what functionality
they require from the database, what kinds of data they wish to process and the most
frequently performed operations.
 This discussion is at a non-technical level and enables the database designers to understand
the business logic behind the desired database.
 Gathered information is organized and presented using suitable tools.
Conceptual database design:
 Once the information is gathered in the requirements analysis step, a conceptual database
design is developed. This step is often carried out using the ER model, or a similar high-
level data model.
 The ER Model is used to create a simple description of the data that matches both how users
and developers think of the data by identifying entities, relationship between them,
attributes and integrity constraints.
Logical Database Design:
 In this step, the conceptual database design of a database schema is converted into logical
data base design.

11
 DATA BASE MANAGEMENT SYSTEMS – II CSE II Semester – UNIT 1

 That is logical data base design involves translating the ER diagrams into actual relational
database schema
Schema Refinement:
 The fourth step in data base design is to analyze the collection of relations in our relational
database schema to identify the feature problems such as redundancies, anomalies, etc., , and
to refine it.
Physical Database Design:
 In this step, the physical features of the database which includes form of file organization and
the internal storage structure are specified.
 This step may simply involve building indexes on some tables and clustering some tables, or
it may involve redesign of parts of the database schema obtained from the earlier design
steps.
Application and Security Design :
 Any software project that involves a DBMS must consider applications that involve
processes and identify the [Link]: Users, User groups, departments, etc,.
 We must describe the role of each entity in every [Link] a Security design, for each role,
we must identify the parts of the database that must be accessible and the parts of the
database that must not be accessible by enforcing access rules.

2.2 ENTITIES, ATTRIBUTES AND ENTITY SETS

ENTITY
An entity refers to an object in the real world that is distinguishable from other objects. An entity
GNIT

is described (in database) using a set of attributes.

ENTITY SET: A collection of similar entities, e.g., all employees.
All entities in an entity set have the same set of attributes In ER diagram,
 Attributes describe the properties of entities in the entity set. (Eg. Attributes of
Employees entity are SSN, Name,Lot, etc.,
 Each attribute has a domain.
 Based on the values of certain attributes, an entity can be identified uniquely.

Types of Entity Sets-

An entity set may be of the following two types- 1. Strong entity set
2. Weak entity set
1. Strong Entity Set
A strong entity set is an entity set that contains sufficient attributes to uniquely identify all its
entities. i.e) a primary key exists for a strong entity set.
 Primary key of a strong entity set is represented by underlining it.

2. Weak Entity Set

 A weak entity set is an entity set that does not contain sufficient attributes to uniquely
identify its entities.i.e) a primary key does not exist for a weak entity set.
 However, it contains a partial key called as a discriminator.
2.3 RELATIONSHIPS AND RELATIONSHIP SETS

12
 DATA BASE MANAGEMENT SYSTEMS – II CSE II Semester – UNIT 1

A relationship is defined as an association among several entities.

Example- ‘Works in’ is a relationship that exists between entities Employee and Department.

Employee Works_in Department

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

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,On the basis of degree of a relationship set, a relationship set can be
classified into the following types-
Types of Relationship Sets-  Unary Relationship Set / Self Relationship set
GNIT

 Binary Relationship Set

 Ternary Relationship Set
 N-ary Relationship Set

1. Unary Relationship Set-

An entity set can participate in a relationship set with itself. Entities in same set play different
roles in the relationship. Role indicators express the role.

Example-
Role Indicator

2. Binary Relationship Set-

Binary relationship set is a relationship set where two entity sets participate in a relationship set.
Example- Employeesworks in Departments

13
 DATA BASE MANAGEMENT SYSTEMS – II CSE II Semester – UNIT 1

[Link] Relationship Set-

Ternary relationship set is a relationship set where three entity sets participate in a relationship
set.
Example-
• If Each department has offices in several locations
•
• We want to record the locations at which each employee works

Descriptive Attributes:
A relationship can also have some attributes, which are called as ‘descriptive attributes. These
are used to record information about the relationship

Example: GNIT

James of ‘Employees’ entity set works in a department entity set since 1991.

4. N-ary Relationship Set-

N-ary relationship set is a relationship set where ‘n’ entity sets participate in a relationship set.
Instance:
An instance of a relationship set is a set of relationships. It is a snapshot of the relationship at
some instant of time.

2.4 ADDITIONAL
FEATURES OF ER MODEL

2.4.1 Key Constraint (Cardinality Constraint)

Cardinality is the number of entity instances to which another entity set can map under the
relationship

Types of Cardinality Ratios-

14
 DATA BASE MANAGEMENT SYSTEMS – II CSE II Semester – UNIT 1

1. Many-to-Many cardinality (m:n)

2. Many-to-One cardinality (m:1) A key constraintbetween an entity set S and a relationship set
3. One-to-Many cardinality (1:n) restricts instances of the relationship set by requiring that each
4. One-to-One cardinality (1:1 ) entity of S participate in at most one relationship.

1. Many-to-Many Cardinality-
By this cardinality constraint,
 An entity in set A can be associated with any number (zero or more) of entities in set B.
 An entity in set B can be associated with any number (zero or more) of entities in set A.
Example-
Street Dname
City DNo Budget
Name

Employee Works_in Department

An employee is allowed to work in different departments and a department is allowed to have

several employees.
2. Many-to-One Cardinality-
By this cardinality constraint, GNIT

 An entity in set A can be associated with at most one entity in set B.

 An entity in set B can be associated with any number (zero or more) of entities in set A.
Example-

Street Dname
Name City DNo Budget

Employee Works_in Department

Each employee works in at most one department. Many employees can work in same
department.
3. One-to-Many Cardinality-
By this cardinality constraint,
 An entity in set A can be associated with any number (zero or more) of entities in set B.
 An entity in set B can be associated with at most one entity in set A.
Example-
One employee can be associated with many departments, where as each department can be
associated with at most one employee as its manager.

Street Dname
Name City DNo Budget

15
Employee Manages Department
 DATA BASE MANAGEMENT SYSTEMS – II CSE II Semester – UNIT 1

4. One-to-One Cardinality-
By this cardinality constraint,
 An entity in set A can be associated with at most one entity in set B.
 An entity in set B can be associated with at most one entity in set A.

Example-
Each employee can manage at most 1 department.

Street Dname
Name City DNo Budget

Employee Manages Department

GNIT

2.4.2. Participation Constraints:

• A participation constraint between an entity set S and a relationship set specifies either each
entity of an entity set must participate in at least one relationship or not.
There are 2 types of participation constraints, which are as below.
[Link] Participation
[Link] Participation
[Link] Participation
 In total participation every entity in the entity set participates in at least one
relationship in the relationship set.
Example:
 Every department must have a manager. So “Departments” entity set has total
participation in Manages relationship
 Represented as thickened line (there is a key constraint as well)

16
 DATA BASE MANAGEMENT SYSTEMS – II CSE II Semester – UNIT 1

[Link] Participation
 In partial participation some entities may not participate in any relationship in the
relationship set.
 It is indicated by single line.
Example:
 Not every department has a manager, So “Departments” entity set has partial participation

2.4.3 Weak Entity Set

Entity types that do not have key attributes of their own are called as weak entity types.
A weak entity set can be identified uniquely only by considering the primary key of another
(Identifying owner) owner.
For any weak entity set, following restrictions must hold.
GNIT

a. The owner entity set and the weak entity set must participate in a One-to-many relationship
set, which is called as the ‘Identifying Relationship Set’ of the weak entity set.
b. The weak entity set must have total participation in the identifying relationship set.
Partial key(or discriminator) of the weak entity set:
The set of attributes of a weak entity set that uniquely identify a weak entity for a given owner
entity is called as ‘partial key of the weak entity set’.
Primary Key of Weak Entity Set = Its own Partial Key + Primary Key of Owner entity

Representation
Dark lines: to draw weak entity set & its identifying relationship set (with one-many relationship
constraint)
Broken line: to underline a partial key

17
 DATA BASE MANAGEMENT SYSTEMS – II CSE II Semester – UNIT 1

Example:
‘Dependents’ is an example of a weak entity set which is uniquely identified by (ssn, pname)
‘dpname’ is a partial key for dependents. Policy: identifying relationship set

2.4.4 Class Hierarchies

Class hierarchies organize structurally similar entities through inheritance into sub- and
superclasses using ISA symbol.
• If we declare A ISA B, every A entity is also considered to be a B entity.
Reasons for using ISA:
• To add descriptive attributes specific to a subclass.
• To identify entities participating in a relationship.
Class Hierarchy Based On Sub-super Set
Generalization
 Generalization is like a bottom-up approach in which two or
more entities of lower level combine to form a new entity at
higher level if they have some attributes in common.
 i.e., In generalization, entities are combined to form a more
generalized entity, i.e., subclasses are combined to make a
superclass.
Example:Hourly-Emps and Contract-Emps can be generalized and create a higher level entity
EMPLOYEE. GNIT

Specialization :
Specialization is a top-down approach in which one higher level entity can be broken down
into a set of lower level entities.
 It is the process of identifying the subsets of a superclass that share some special attributes.
 Normally, the superclass is defined first, the subclass and its related attributes are defined
next, and relationship set are then added.
Example: EMPLOYEE entity can be specialized as Hourly_Emps and Contract_Emps.
Class Hierarchy Based On Constraints
1. Overlap constraints(allowed/disallowed) :Overlap constraints determine whether two
subclasses are allowed to contain the same entity commonly.
Example :  Can John be both an Hourly_Emps entity and a Contract_Emps entity?...Disallowed
 Can he be both a Contract_Emps entity and a Senior_Emps entity? ...Allowed
2. Covering Constraints(Yes/No) : Covering constraints determine whether all entities in the
superclass is collectively included in at least one of the subclasses.
Example:  Should every Employee be a Hourly-Emps or Contract-Emps? ….. No.
 Does every hourly employee and contract employee are employees of this organization? … Yes

2.4.5. Aggregation:
Aggregation is the process of transforming a relationship set into an entity set for the purpose of
relating the resulting construct to other entity sets.

18
 DATA BASE MANAGEMENT SYSTEMS – II CSE II Semester – UNIT 1

It is used to model a relationship involving a relationship set

Representation: dashed box
Example:
 Consider the constraint that each sponsorship is
monitored by at most one employee
 Sponsors and Monitors are two distinct
Relationships
 Here the relationship set Sponsors can betreated
as an entity set for the purpose of relating it with
Monitors relationship set
2.5 CONCEPTUAL DESIGN USING THE ER MODEL
Design choices:
Developing an ER diagram presents several design issues, including the following:
 Should a concept be modeled as an entity or an attribute?
 Should a concept be modeled as an entity or a relationship?
 Identifying relationships: Binary or ternary? Aggregation?
Constraints in the ER Model:
 A lot of data semantics can (and should) be captured.
Entity vs Attribute
While identifying the attributes of an entity set, it is sometimes not clear whether a property
should be modeled as an attribute or as an entity set
GNIT

Example:
• Should address be:
– attribute of Employees or an entity (related to Employees)?

Depends upon use of address information, and the semantics of the data:

• If several addresses per employee, address must be an entity (since attributes cannot be set-
valued).
• If structure (city, street, etc.) is important, address must be modeled as an entity (since
attribute values are atomic).
Entity vs Relationship

19
 DATA BASE MANAGEMENT SYSTEMS – II CSE II Semester – UNIT 1

• Works_In4 does not allow an employee to work in a department for two or more periods.

 If wanting
 to record
several working
periods for an
employee in

Work_In4Introduce new entity set, Duration

GNIT

Binary vs. Ternary Relationships

Constraints:
 An employee can own several policies. Each policy can
be owned by several employee.
 Each dependent can be covered by several policies
o To specify the above given constraints, ternary
relationship is suitable.
o But to specify the below given constraints, this
design is bad design.

Constraints:
o Each policy is owned by just 1 employee,
and,
o Dependents is a weak entity set, and each
dependent is tied to the covering policy

Better design
In this example: two binary relationships are
better than one ternary relationshipto specify
the above given constraint

20
 DATA BASE MANAGEMENT SYSTEMS – II CSE II Semester – UNIT 1

Another example
The contract specifies that a supplier will
supply some quantity of a part to a
department.
To specify this constraint, a ternary
relationship is better than three binary
relationships

Example: University Database

• We have four entity sets: Professor,

Project, Graduate, Department.
• Each project is managed by one professor (principal investigator)
• Professors can manage multiple projects.
• Each project is worked on by one or more professors (co-investigators).
• Professors can work on multiple projects.
• Each project is worked on by one or more graduate students.
GNIT

• Graduate students can work on multiple projects.

• When graduate students work on multiple projects, they must have a supervisor (prof) for
each one.
• Departments have a professor who runs the department
• Professors work in one or more departments
• For each department a professor works in, a time percentage is associated with their job
• Graduate students have one major department in which they are working on their degree
• Each graduate student has another, more senior graduate 21student who advises him or her on
•
• what courses to take

21