Notesale: Turn your study into money

Document Preview

Extracts from the notes are below, to see the PDF you'll receive please use the links above

---

1

LECTURE NOTES
UNIT - 1

2

CHAPTER 1
INTRODUCTION
Overview of DBMS (Database Management System)
DBMS is generally defined as a collection of logically related data and a set of programs
to access the data
...
e
...

USER QUERIES

Query Processing
Software

DBMS
Storage Management
Software

DATABASE
SYSTEM
Schema
Definition

DATA

DATABASE

DATABASE A Database is a collection of logically related data that can be recorded
...

The aspect represented by the database is called its “Mini-world”
...

(c)
The repository of data must be designed, developed and implemented for a
specific purpose
...


3
A Database System will have the following major organs:-

Sources of information, from where it derives its data
...

Some intended users, who would be interested in its data
...
The real-world events affecting the information in the database will be
admissions, exams, results & placements etc
...

Database Management System (DBMS)
A Database Management System (DBMS) refers to a set of programs for defining,
creation, maintenance and manipulation of a database
...
e
...
It should
also facilitate specifying the access rights of authorized users
...


-

Protection of a database The DBMS must protect the database against
unauthorized/ malicious access
...


File Processing System
Before the evolution of DBMS, dedicated systems known as “File-Processing
Systems” were in vogue to handle the data repositories of organizations
...
The structure of the
files used to be hard-coded in the application programs
...
Also, the same information used to be stored at multiple places, in
different formats, on different machines, which were not even interconnected
...

(ii)
Difficulty in Accessing of Data
Suppose there exists some information in the
files, but the existing set of application programs do not support extraction of that
information
...

(iii)
Data Isolation
The information is scattered over a large number of files,
on a number of stand-alone (not networked) machines, making it very difficult to process
certain queries, which need information to be extracted from multiple locations
...
The redundancy of information makes this task all the more difficult
...

(vi)
Difficulty in Concurrency Control:
It is complex to build in the
concurrency control features at the application programs level
...


Features of a Database System
A DBMS will support the following features:(a)

Data Dictionary A Database System will support a Data Dictionary (or Data
Directory or DBMS Catalog), which contains information like Data Types,
Relationships amongst the data and Data Constraints of the underlying
database
...
Since, this information defines the
nature of the data stored in the database, it is called metadata (data about
data)
...
When a need arises to change the structure of data, no
changes need to be made to the DBMS software; only the dictionary is
updated to reflect the changes
...
Also, this feature makes the
DBMS software generic
...
This feature of DBMS is
generally referred to as ‘Self Describing Nature of a Database’, since the
information stored in the Data Dictionary fully describes the nature of the data
stored in the Database
...
Also, it supports a Buffer
Manager to manage the memory buffers, used for processing database
information
...


(c)

Language Interfaces
DBMS supports language interfaces with 4GL
languages like PL/SQL for data manipulation applications
...
A Transaction, when executed transforms the database from one
consistent state to another consistent state
...
If a Transaction fails during its execution, then the log file is
used to rollback the transaction during recovery of the database
...


(e)

Concurrency control
DBMS will support concurrency control tools for
permitting multiple users or application programs to access the database
concurrently, while preserving the consistency of database
...
The authorized Access Rights are explicitly stored in the Data
Dictionary
...
This will protect
database against the authorized/ malicious access
...
For example, if a Transaction fails during its execution, it is rolled
back to initial state; thus reverting back to the consistent state that existed
prior to the commencement of the failed transaction
...
Also, DBMS will support taking
of periodic backups, which are used to recover databases in case of
catastrophic failures; like Disk Crash
...
It comprises a collection of
conceptual tools for describing the Data, the Data Relationships, the Data Semantics and
the Data Integrity Constraints
...

Record Based Logical Models
...

The Object Based Logical Models view the universe as a collection of objects
...


-

An Entity refers to a real-world ‘object’ or a ‘concept’ that is distinguishable
from other objects and other concepts in the real-world
...


-

An Entity will have a set of properties, known as Attributes; for example, the
Entity “Account” may have attributes like “Account-Number”, “CurrentBalance” etc

-

Each attribute will have a set of permitted values, called its Domain; for
example the domain of Balance of an account can be the set of +ve real
numbers
...


-

A relationship refers to the association amongst entities
...


-

A set of Relationships of the same kind, having the same set of attributes is
called a Relationship Set
...


7

-

E-R Model also specifies certain constraints, like Mapping Cardinalities i
...

whether the relationship is one-to-one, one-to-many, many-to-one or many-tomany
...

S_Name
S-Address

Roll_No

Sub_Cod
e

Marks

STUDENT

(ii)

Sub_Title

RESULT

COURSE

Object-Oriented Model
...
An Object Body
encapsulates Data (Variables) as well as Methods (Functions) to
manipulate the Data (Variables)
...
Thus, a Class may be viewed as a Type Definition of the Objects
...
“A” can accomplish this by
making calls to the methods of “B”, through B’s Interface
...


Variables

Functions

8

Interface
OBJECT
The structure of an object-oriented database is modeled as a set of classes
and database will comprise of objects belonging to those classes
...

These models describe data at the Logical
level, as a collection of fixed-format Records of different types
...

Use of fixed-length Records simplifies the Physical Level implementation of a database
...
This is one of the oldest models, dating back to
1960s
...
At one time, it was the
most used DBMS
...
The relationships among
the data are represented by Links, which can be viewed as pointers
...
Thus, it
permits modeling of only one-to-many relationship (not many-to-many
relationship) amongst the Records
...

Course
Offered By

Attended By
Student

Teacher
HIERARCHICAL MODEL

It does not indicate the relationships “ What are the courses being offered
by a faculty”, “What are courses being attended by a student”, “who are the
students being taught by a faculty” and “who are the faculty teaching a student”
...

(ii)
Network Model
...
Thus a Record can have any number
of parent records; and thus supports many-to-many relationship amongst records
...
Since, this
Model supports many-to-many relationship amongst the records, it is considered
more versatile as compared to Hierarchical Model
...
It
contains additional information i
...
relationship “Offers” from “Teacher” to
“Course” and relationship “Attends” from “Student” to “Course”
...
Also, it depicts
relationships “Teaches” and “Taught By” between “Teacher” and “Taught”
...
This is most modern and most commonly used
model amongst the Record Based Models
...
The
Relational Model models a database as a collection of Tables to represent both
data and the relationships amongst the data
...
Each relation has a number of Columns,
representing the Fields (or Attributes) of the relation
...
A Relation (or Table) can have an un-limited number of Rows and each
Row represents an Instance of the Relation
...

Each Tuple will be unique in a Relation
...
The relationships amongst the tables will be modeled as
Foreign Key- Primary Key Relationships
...
e
...
e
...


STUDENT
Roll_No

S_Name

COURSE
Sub_Code Sub_Title
TEACHER
Fac_Code

Branch

Semester Section

Semester Branch

Fac_Name Desig

Dept

S_Address

Contact_Hrs

Fac_Address

COURSE-TEACHER
Sub_Code Fac_Code
COURSE-STUDENT
Sub_Code Roll_No
TEACHER-STUDENT
Fac_Code Roll_No

The Relational Model has become extremely popular because:(a)
(b)
(c)

It is extremely simple and easy to implement
...

It has been highly standardized
...

Database Schema refers to the overall structure of a database
...
A Database System will have several Schemas,
partitioned according to the levels of its abstraction
...

It refers to the actual collection of data (a Snapshot of data) existing in the
database at a particular moment of time
...

DATA ABSTRACTION & VARIOUS SCHEMAS OF A DATABASE
There are three levels of data abstraction in a database; and each level is described by a
schema as explained below:(a)
Physical Level
...
At this level, a
Physical Schema describes “how data is physically stored”
...

(b)
Logical Level
...
At this
level, a Logical Schema (or Conceptual Schema) would describe “what data is stored in
the database” and “what are the relationships amongst the data”
...
It
would describe the logical structure of database, data types and integrity constraints
...
But, the implementation of these simple structures may be
quite complex at the Physical Level
...

(c )
View Level
...
At this level, there will be
many Views, defined for different categories of users
...
A view
will describe only a subset of the underlying database
...
There may be many Views, tailored to the
specific needs of various users
...
So, the interface at this level is
made as simple and user-friendly as possible
...

DATA INDEPENDENCE
The ability of a DBMS to modify its Schema definition at one level, without affecting a
Schema definition at the next higher level, is called Data Independence
...

It is the ability of DBMS to modify the
Physical Schema without causing any changes in the schema at the logical level and at
the view level
...

(b)
Logical Data Independence
...
Modifications at Logical Level are necessitated by need to alter the Logical
Structure of the database
...

DATABASE LANGUAGES
A DBMS will support two kinds of languages; one called Data Definition Language
(DDL) to specify the Database Schema and the other called Data Manipulation Language
(DML) to enable accessing and manipulation of the data stored in the database
...
A database schema is specified by a set of definitions expressed in DDL
...
This data stored in Data Dictionary is called Metadata i
...
data about data
...
Thus the data dictionary is accessed during
processing of each query
...
The result of interpretation of these definitions will be a set of
physical schema structures and a set of access methods supported by the system
...

(b)
DML
...
A DML query is a statement specifying information to be accessed
for retrieval or insert/update/delete
...
The goal of a DML is to provide an efficient and
friendly human interface for the following operations in a database:(i)
(ii)
(iii)
(iv)

Retrieval on information stored in the database
...

Deletion of information from the database
...


13
There are two types of DMLs:(i)
Procedural DMLs
...

(ii)
Non-Procedural DMLs
...

Non-procedural DMLs are easier to learn and to use than the procedural DMLs
...
This limitation of Non-Procedural DMLs
is overcome by performing query optimization at the System Level
...

Creation of Conceptual Schema and its periodic update to adapt to the changed
requirements
...


Implementation of efficient Storage Structure and Access Methods
...

Liaise with the Users to ensure that the information required by the Users is made
available
...

Ensure system security, through Grant and Revoke of Access Rights to the Users
...

5
...

6
...


7
...


8
...
In case of degradation in system performance,
perform tuning procedures
...

9
...
If needed, upgrade the Disk
Drives to meet the increased requirements
...

To liaise with the DBMS vendor to obtain necessary technical supports and to
obtain the necessary tools & software upgrades, whenever made available by the vendor
...
Multiple users of the same set
of data will create replicated sets of files, specific to their respective applications
...
On the other hand, in a database approach, a single
repository of data is maintained, which is defined once and then accessed by various
users of the data
...
This additional information is called meta-data, which is stored in
a file called Data-Dictionary (also called DBMS Catalog)
...
This
additional information makes the DBMS software independent of its
applications
...
This feature
enables the DBMS software to be adapted for any application
...
Whereas in a traditional
file processing system, the application programs would need major changes
while shifting from one application to another
...
Whereas in a Database System, the application programs are
insulated from the data stored in the database
...
As long as the contents of data remain
unchanged, the database structure can be changed, without affecting the
existing application programs
...


(iii)

Support for Multiple Views of the Data
Depending on different
needs and different levels of authorizations, different users would be provided
different perspectives of the same data, called Views
...
e
...
A View is not explicitly stored in the Database; only its Definition
is stored in the DBMS Catalog
...
Next time, when the same view is again accessed, it is
re-computed fresh
...
This is achieved by
including Concurrency Control Software in the DBMS, to ensure that
database remains consistent, despite access by multiple users concurrently
...
These rights are stored in the data dictionary itself
...


(vi)

Support for efficient Recovery
...


Advantages of using a DBMS vis-à-vis File Processing System
(a)

Controlling Redundancy
While designing a database, various Views
of different users are integrated into a single database, thus controlling
redundancy
...
Also, it
ensures database consistency, in case of updates
...
Whenever, any query is received from any user, it is
checked for valid access rights
...
This prevents unauthorized access of
data
...
Whenever, some data is
inserted/updated/deleted, the data constraints are automatically applied to the
related data items and invalid operations are rejected
...
Despite concurrent access by multiple users, database
consistency is maintained
...


(g)

Reduced Application Development Time
Development time of a new
application using DBMS is of the order of 15 – 25% as compared to the time
needed in development of equivalent applications in a traditional file
processing system
...


(i)

Potential for enforcing Standards
It
permits
the
Database
Administrator (DBA) to define & enforce standards among the database users
...


18

Exercises
Ex
...
1

Explain three level of data abstraction
...
Which is more difficult to achieve
and why?

Ex
...
2

Explain the characteristics of DBMS that distinguish it from a File
Processing System
...


Ex
...
3
Compare the three data models: Hierarchical, Network and Relational
...
1
...
1
...
1
...
1
...
1
...

Ex
...
8

What is meant by “Self-describing nature of a database”?

Ex
...
9
Compare Procedural DMLs and Non-Procedural DMLs from the
viewpoints of (i)
User Friendliness (ii) Query Optimization
...

Entity
An Entity is an object (like a “CAR”) or a concept (like an “ACCOUNT”)
from the real world, which is distinguishable from other objects and other concepts
...
For example entity
“ACCOUNT” may be defined by Attributes like “ACCOUNT-NUMBER”, “BRANCHNAME” and “BALANACE” etc
...
Each
entity in an Entity-Set will have the same set of attributes and the set of attributes will
distinguish it from other Entity Sets
...
Some of the attributes of an entity set may overlap with other entity sets
...
Like there
may be relationship “DEPOSITOR” between Entity Set “CUSTOMER” and Entity Set
“ACCOUNT”
...
e
...
A Relationship Set will inherit
some of the Attributes (properties) of the associating Entity Sets
...
In addition, a Relationship Set may have some of its
own attributes called “Descriptive Attributes”; for example the relationship set
“DEPOSITOR” may have a descriptive attribute “DATE-OF-OPERATION”, indicating
the date on which a customer has last operated an account
...

A database will consist of a set of Entity-sets and Relationship-Sets, each of
which will contain a number of entities of the same type or Relationships of the same
type
...

Attribute Types:(i)
Simple Vs Composite Attributes
...
On the other hand, a Composite
attribute is the one, which can be divided into sub-parts like ‘DATE-OF-BIRTH’,
which may be divided into ‘birth-date’, ‘birth-month’ & ‘birth-year’
...

An attribute, which
can assume one value at a time, is called Single-Valued attribute; like ‘name’ of
an EMPLOYEE entity
...


21

(iii)
Null Attribute
...

(e) If value is applicable, but not specified; like TEL#- an employee may not
be owning a Telephone
...

Null value can only be assigned to an Attribute, if assigning value to that attribute
is optional (not mandatory)
...

(iv)
Derived Attribute Vs Stored Attribute
...

Degree of Relationship Sets
...
Most of the relationships are binary
...


Ellipse

represents an attribute
...


Line

links an attribute to an entity set or an entity set to a
relation set
...

Dashed Ellipse

indicates derived attribute
...


Double Rectangle

indicates weak entity set
...


RELATIONSHIP constraints
-

Mapping Cardinalities
Participation Constraint

Mapping Cardinalities
...
An entity in A is associated with at most one entity in B and an
entity in B is associated with at most one entity in A
...

(b)
One-to-many
...
It is represented in E-R Model as follows:A

R

B

(c)
Many-to-one
...
It is represented in E-R Model as follows:A

R

B

23
(d)
Many-to-many
...
It is represented in E-R Model as follows:A

R

B

Example:One-to-One relationship from CUSTOMER to ACCOUNT implies that each customer
can have only one account and each account has to be Single
...


CUSTOMER

DEPOSITOR

ACCOUNT

(One-to-Many Relationship)

Many-to-One relationship from CUSTOMER to ACCOUNT implies that each customer
can have only one accounts, but each account can be Joint (held by one or more)
...


CUSTOMER

DEPOSITOR

ACCOUNT

24
(Many-to-Many Relationship)
Participation Constraints in Relationship Sets
-

Total Participation
Partial Participation

Total Participation
An Entity Set E is said to have total participation in relationship set R if each entity in E
is participating at least in one relationship through R
...

Partial Participation
An Entity Set E is said to have partial participation in relationship set R if some of the
entities in E are not participating in any relationship through R
...

Example:Suppose Entity Sets “CUSTOMER” and “ACCOUNT” are related by
Relationship Set “DEPOSITOR” and Entity Sets “CUSTOMER” and “LOAN” are
related by Relationship Set “BORROWER”
...
A Super Key of an Entity Set or Relationship Set refers to the set of
attributes, which when taken collectively, will uniquely determine an entity within the
Entity Set or a Relationship within the Relationship Set
...
So, a Super Key
may have some extraneous (unnecessary) attributes, which if removed, the balance set
may still form a Super Key of R
...
This implies that no two students can have same ROLL-NO
...
In this, the attribute NAME is
extraneous; which if removed, the balance set i
...
{ROLL-NO} still forms a Super Key of
STUDENT
...

A Super Key, whose no proper subset forms a Super Key, is called
a Candidate Key
...
e
...
An Entity Set may have more than one Candidate Keys
...
e
...

Primary Key
...
In the ER Diagram, the Primary Key Attributes are underlined with a firm line
...
Let K1 and K2 be the
respective Primary Keys of E1 and E2
...

PK (R)
= PK (E2)
i
...
Primary Key of “Many-Side” Entity-Set
...

PK (R)
= PK (E1)
i
...
Primary Key of “Many-Side” Entity-Set
...
e
...
e
...
e
...
On the other hand, an entity set having a primary key of its own is
called a Strong Entity Set
...
Then Entity Set E 2 is said to be
“Existence-Dependent” on E1 and E1 is said to be the “Owner Entity Set” of E 2
...
The Weak Entity
Set E2 will have a set of attributes called its “Discriminator”, which together with the
Primary Key of E1 will form the Primary Key of E2
...
e
...
This situation can be best modeled as
follows:EMP-NAME

D-NAME

EMP-ID

RELATIO
N
DOB

SALARY

EMPLOYEE

Owner Entity Set

DEPENDENT

Identifying Relationship

Weak Entity Set

The Weak Entity Set DEPENDENT is Existence Dependent on the Strong Entity
Set EMPLOYEE
...
In E-R Diagram, the Discriminator (also called Partial Key) of
a weak entity set is marked by underlining with a broken line
...

(ii) The Participation of Owner Entity Set in the Identifying Relationship will be
partial and the participation of the Weak Entity Set in the Identifying Relationship
will be Total
...
The multi-valued attribute can be used
to indicate the names of the dependents of employees
...
In this case, the Weak Entity approach will be
the ideal choice, since then the weak entity set DEPENDENT can have any number of
attributes
...


An entity set E may include some sub-groups of entities (say E 1,
E2, …
...
There will be some attributes that will be common to all subgroups
...
En
...
En are called “Sub Classes” or “Lower Level Entity Sets” of E
...
And the distinct
attributes of each sub entity set are represented with the sub entity set
...
It is read as “is a”
...
Like in the above case, each lower entity set will inherit attributes A 1
and A2 of the Super Class E
...
The Entity Set ACCOUNT may be specialized into different types of accounts
like SAVINGS-ACCOUNT, CURRENT-ACCOUNT, FIXED-DEPOSIT (FD) and
RECURRING-DEPOSIT (RD)
...
Similarly, FD
and RD have distinct attributes of their own
...

It implies that an entity does not belong to more than one lowerlevel entity set i
...
an account is either savings-account or current-account but not
both
...
In overlapping generalizations, an entity may belong to more than
one lower-level entity sets within a single generalization
...

Partial
...


Generalization
...
Generalization refers to the process of fusing several distinct
entity sets into a single Higher Level Entity Set, on the basis of commonality of their
attributes
...
The common
attributes of the Lower Level Entity Sets will be assigned to the Higher Level Entity Set
...
The higher-level entity set is termed as super-class and lower level
entity set is termed as sub-class
...


Aggregation
...
Aggregation provides a solution in this case
...
For
example the relationship between R1 and E3 as indicated below
...
This Higher Level Entity Set is participating
in a Relationship R2 with Entity Set E3
...

Example:Suppose, we have Entity Sets “EMPLOYEE”, “BRANCH” and “JOB”
which are related through a Relationship “EBJ” which indicates, “which employee” is
performing “what jobs” at “which branch”
...
Suppose, we want to relate another Entity Set “MANAGER” to indicate:(i)The set of Employees managed by a manager
...

(iii)The Branches managed by a manager (assume a manager can manages only
one branch)
...


Reduction of E-R Schema to Tables
An E-R Diagram can be reduced to a set of Tables, as explained below:(a)
Tabular representation of a Strong Entity Set
...
The Table will have columns as follows:(i)
Simple, Single-valued Attributes There will be a column for each
simple, single-valued attribute of Entity Set E
...
For example for NAME comprising of First Name (FN), Middle Name
( MN) and Last Name (LN) there will be three columns for FN, MN and LN
...
If NAME needs to be produced, it can
be done by combining the sub-parts
...


33
(iv)
Multi-Valued Attribute
Each Multi-Valued Attribute (say M) will be
represented by a separate Table (say named E-M) which will have a column each
for the primary key attributes of E and a column for Attribute M
...

Let E be a Strong Entity Set with simple single-valued attributes a1,a2,……,an
...
Let D1,D2,…Dn be the domains of attributes
a1,a2,…
...
The Table E will comprise of a set of rows, which will be a
subset of the Cartesian Product D1 X D2 X……
...


Age

Example

DOB

Tel_N
o

Name

Univ_Roll_No

Cit
y
HNo

STUDENT

Stree

Pin

t
Addres
s

The derived attribute Age will not be represented in the STUDENT table
...

The Tel-No will be represented in a separate table (say named STUDENT-TEL-NO),
which will have a column for Primary Key of STUDENT i
...
Roll-No and a column for
Tel-No
...

The Above E-R Diagram will be reduced to following two Tables:STUDENT
Univ_Roll_No

Name

DOB

H-No

Street

City

Pin

34
STUDENT-TEL-NO
Univ_Roll_No
Tel_No

(b)
Tabular representation of Relationship Sets
...
am be the set of attributes formed by the union of the primary keys of all the
Entity Sets participating in Relation R and let the descriptive attributes of R (if any) be
b1,b2,…
...
Then the Relation R will be represented by a Table named say “R”, which
will (m+n) columns, each column representing one of the attributes from the set {a 1, a2,
……am} U {b1,b2,…
...

Date-of-Operation

Example
CAddress

C-Id

Account-No

Branch-Name

Balance

C-Name

CUSTOMER

DEPOSITOR

ACCOUNT

The Relationship Set DEPOSITOR will be represented by a table named DEPOSITOR
...
In addition, the
DEPOSITOR table will have a column for its Descriptive Attribute “date-of-Operation”
...
Depending on the Cardinality
Mapping of the participating Entity Sets, the Descriptive Attributes of the Relationship
set can be shifted to one of the participating Entity Sets
...
Under this condition, it is
possible to shift the descriptive attributes of the relationship set to any of the participating
Entity Sets and also it is possible to merge the table of the Relationship Set with the table
of any of the participating Entity Sets, without loss of any information
...
e
...
e
...

CUSTOMER
C-Id
C-Name
C-address
C-001
Ajay
320, Sector-26, Noida
C-220
Vijay
110,Sector-8, RKP
C-310
Ram
120,Sector-25, Noida
C-505
Shyam
303,Sector-22, RKP

36

ACCOUNT
Account-Number
A-101
A-203
A-305
A-310

Balance
10000
30000
50000
25000

DEPOSITOR
C-Id
C-001
C-220
C-310
C-505

Account-Number
A-310
A-101
A-203
A-305

Branch-Name
Sec-18
Sec-26
CP
RKP

Date-of-Operation
10-Jan-2007
23-Dec-2006
03-Feb-2007
27-Dec-2007

As obvious, the rows in DEPOSITOR table are having one-to-one mapping with the rows
in the CUSTOMER Table and also with the rows in the ACCOUNT Table
...
Thus, the descriptive attribute Date-Of-Operation of the
Relationship Set DEPOSITOR can be shifted to either CUSTOMER or ACCOUNT
...
The combined table will
have union of the columns of the two merged tables
...
The resulting set of tables will then
be:CUSTOMER
C-Id
C-Name
C-001
C-220
C-310
C-505

Ajay
Vijay
Ram
Shyam

ACCOUNT
Account-Number
A-101
A-203
A-305
A-310

C-address

AccountNumber
320, Sector-26, Noida A-310
110,Sector-8, RKP
A-101
120,Sector-25, Noida A-203
303,Sector-22,RKP
A-305
Balance
10000
30000
50000
25000

Branch-Name
Sec-18
Sec-26
CP
RKP

Date-ofOperation
10-Jan-2007
23-Dec-2006
03-Feb-2007
27-Dec-2007

37

The combined CUSTOMER Table now includes the Primary Key (AN) of ACCOUNT
and descriptive attribute Date_Of_Operation of DEPOSITOR
...
e
...

Date-of-Operation

Example
CAddress

C-Id

Account-No

Branch-Name

Balance

C-Name

CUSTOMER

DEPOSITOR

CUSTOMER
C-Id
C-001
C-220
C-310
C-505

C-Name
Ajay
Vijay
Ram
Shyam

ACCOUNT
Account-Number
A-101
A-203
A-305
A-310
A-550
A-670

Balance
10000
30000
50000
25000
35000
60000

DEPOSITOR
C-Id
C-001
C-220
C-310
C-505
C-101
C-310

Account-Number
A-310
A-101
A-203
A-305
A-550
A-670

C-address
320, Sector-26, Noida
110,Sector-8, RKP
120,Sector-25, Noida
303,Sector-22,RKP
Branch-Name
Sec-18
Sec-26
CP
RKP
CP
Sec-18
Date-of-Operation
10-Jan-2007
23-Dec-2006
03-Feb-2007
27-Dec-2007
22-Dec-2006
01-Jan-2007

ACCOUNT

38

The rows in the DEPOSITOR table have one-to-one mapping onto the rows in
ACCOUNT Table i
...
with the “Many-Side Entity Set” Table
...
Thus, the
descriptive attribute Date-Of-Operation can be shifted to ACCOUNT (The “Many-Side”
Entity Set) and the DEPOSITOR Table can be with the ACCOUNT Table (i
...
with the
table of the “Many-Side” Entity Set), without losing any information
...
The resulting set of tables will then
be:CUSTOMER
C-Id
C-001
C-220
C-310
C-505
ACCOUNT
AccountNumber
A-101
A-203
A-305
A-310
A-550
A-670

C-Name
Ajay
Vijay
Ram
Shyam

C-address
320, Sector-26, Noida
110,Sector-8, RKP
120,Sector-25, Noida
303,Sector-22,RKP

Balance
10000
30000
50000
25000
35000
60000

Branch-Name
Sec-18
Sec-26
CP
RKP
CP
Sec-18

Customer_Id
C-220
C-310
C-505
C-101
C-101
C-310

Date_of_Operation
23-Dec-2006
03-Feb-2007
27-Dec-2007
10-Jan-2007
22-Dec-2006
01-Jan-2007

(3)
Many-to-One Relationship Suppose there is many-to-one relationship between
CUSTOMER and ACCCOUNT, which implies that each account can be “Joint” but each
customer can hold only one account
...

Date-of-Operation

Example
C-Id

CAddress

Account-No

Balance

C-Name

CUSTOMER

Branch-Name

DEPOSITOR

ACCOUNT

39

CUSTOMER
C-Id
C-001
C-220
C-310
C-505

C-Name
Ajay
Vijay
Ram
Shyam

C-address
320, Sector-26, Noida
110,Sector-8, RKP
120,Sector-25, Noida
303,Sector-22, RKP

ACCOUNT
Account-Number
Balance
A-101
10000
A-203
30000

Branch-Name
Sec-18
Sec-26

DEPOSITOR
C-Id
Account-Number
Date-of-Operation
C-001
A-101
10-Jan-2007
C-220
A-203
23-Dec-2006
C-310
A-101
03-Feb-2007
C-505
A-203
27-Dec-2007
The rows in the DEPOSITOR table have one-to-one mapping onto the rows in
CUSTOMER Table i
...
with the “Many-Side Entity Set” Table
...
The resultant CUSTOMER table will also include the Primary Key
Account_Number of ACCOUNT table and descriptive attribute DOO of the
DEPOSITOR table
...
In this case, the table DEPOSITOR cannot be
combined with any Entity Set and it has be created as a separate table
...


40

Date-of-Operation

Example
CAddress

C-Id

Account-No

Branch-Name

Balance

C-Name

CUSTOMER

DEPOSITOR

CUSTOMER
C-Id
C-001
C-220
C-310
C-505

C-Name
Ajay
Vijay
Ram
Shyam

ACCOUNT
Account-Number
A-101
A-203
A-305
A-310

Balance
10000
30000
50000
25000

DEPOSITOR
C-Id
C-001
C-220
C-310
C-505
C-101
C-505

Account-Number
A-101
A-203
A-101
A-203
A-305
A-310

ACCOUNT

C-address
320, Sector-26, Noida
110,Sector-8, RKP
120,Sector-25, Noida
303,Sector-22, RKP
Branch-Name
Sec-18
Sec-26
CP
RKP
Date-of-Operation
10-Jan-2007
23-Dec-2006
03-Feb-2007
27-Dec-2007
30-Dec-2007
02-Jan-2007

Now, the rows in the DEPOSITOR table do not have one-to-one mapping with
CUSTOMER table and also with the ACCOUNT table
...
Thus, there has to
be a separate table for DEPOSITOR as indicated above
...

(c)
Tabular representation of Weak Entity Sets
...
Let B be the Strong Entity Set on which A is
existence dependent
...
bn
...
bn}
...
e
...

The Primary Key of table PAYMENT will be
{Loan-No, Payment-No} where the attribute Payment-No is called a “Discriminator” or
“Partial Key” of the table PAYMENT
...
e
...
Thus, no table needs to be created for an Identifying Relationship
...
e
...

(e)

Tabular representation of Generalization
...
The table for lower-level entity set will include its own attributes plus all the
Primary-Key attributes of its higher-level entity set
...
e
...
The table ACCOUNT will have
columns Account-Number and Balance; and table SAVINGS-ACCOUNT will have
columns Account-Number and Interest Rate; and table CURRENT-RATE will have
columns Account-Number and Over-Draft
...

Combining of Tables in Generalization If a generalization is “Total”, which implies
that each entity in the super-class (higher-level entity set) is a member of at least one subclass (lower-level entity set), no table is required to be created for the higher-level entity
set
...
For example, the table SAVINGS-ACCOUNT will also have columns AccountNumber, Balance and Interest-Rate; and the table CURRENT-ACCOUNT will also have
the columns Account-Number, Balance and Over-Draft
...


(f)

Tabular representation of Aggregation
...
There will be one table for Relationship Set EBJM having Attributes
E#, B#, J# and Mgr-Id
...

EBJM
E#

B#

J#

Mgr-Id

E-R DIAGRAM OF AN AIRLINE RESERVATION SYSTEM
CAPACITY
AC_NO

AC_TYPE

AIRCRAFT
TO_PLACE

44

FROM_PLAC
E

DATE

ETD

FLT_N
O

ATD

CREW_NAM
E

ATA

ETA

DESIGNATION

FLT_SCHEDULE

FLIGHT

CREW-ID

CREW

FLT_
CREW
C_DATE

CONFIRMED

RESERVATION

CANCELLATION

SEAT_NO

AMOUNT

TICKET_N
O

ISSUE_DATE

VOUCHER
_NO

FARE

TICKET
REFUND
P_ADDRES
S

P_TEL_NO

P_NAME

PASSENGER
Where ETD: Estimated Time of Departure (i
...
scheduled take-off time)
...
e
...
Initially, it will have a NULL value
...

ATA: Actual Time of Arrival
...
Its value will
get defined only after aircraft actually lands at the destination
...
Even the CANCELLATION table
can be combined with RESERVATION table, since there is many-to-one relationship
between RESERVATION and REFUND
...


E-R DIAGRAM FOR VEHICLE INSURANCE
O_ADDRESS
O_TEL_NO
O-NAME

OWNER

PREMIUM

COLOR

EXPIRY_DATE

MODEL

REG_NO
MAKE

POLICY_NO

BONUS

46

INSURANCE_POLICY

VEHICLE

P_DAT
E

A_DATE
A_REPORT_NO
PLACE

PAYMENT
_VOUCHE
R_NO

P_AMOUN
T

ACCIDENT

CLAIM_PAYMENT

S_REPORT_NO

ASSESSED
_DAMAGE
SURVEYOR REPORT
REPAIR_ITEM

COST

REF_NO

REPAIRS

Exercises
Ex
...
1

Explain the concept of Entity Sets and Relationship Sets
...
2
...
2
...
Explain the determination of Primary Key of a binary Relationship set
...
2
...

Explain how a multi-valued attribute can be better modeled as a Weak Entity Set
...
2
...
What are the
different types constraints involved in specialization? Distinguish between Total &
Partial Specialization and between Disjoint & Overlapping Specialization
...
2
...

Ex
...
7 Draw E-R diagrams to indicate the following relationships between entity set
Operator and entity set Machine:(a)

Each Machine can be operated by many Operators but each Operator can
operate only one machine
...


Ex
...
7 Make E-R Diagrams for the following real-world situations (Indicate clearly
entity sets, relationship sets, cardinalities, attributes and candidate Keys
...
) Also reduce the
E-R diagrams to Tables:(a)

An organization having a set of employees to execute a set of projects
...


(b)

A Tourist Management System catering to booking of hotels, taxies &
guides
...


(c)

Preparation of time table of an Engineering College, catering for a number
of Sections (Year/Branch/Section), a number of courses, a number of
faculty members teaching the courses and a number of class rooms (ignore
labs)
...




---