DBMS Unit-1

Database Management Systems ----UNIT-1
Introduction to Database Management Systems
Introduction: DBMS stands for Database Management System. (DBMS =

Database + Management System).
 Database is a collection of data and Management System is a set of programs to store and
retrieve those data.
 DBMS is a collection of inter-related data and set of programs to store & access those
data in an easy and effective manner.
Need of DBMS:
 Database systems are basically developed for dealing with huge amount of data. There
are two things that require to do are: 1) Storage of data 2) retrieval of data.
 Storage: According to the principles of database systems, the data is stored in such a
way that it acquires lot less space as the redundant data (duplicate data) has been
removed before storage.
 Retrieval of data: Along with storing the data in an optimized and systematic
manner, it is also important that we retrieve the data quickly when needed. Database
systems ensure that the data is retrieved effectively and efficiently.
 Databases are usually designed to manage large bodies of information. This involves
 Definition of structures for information storage (data modeling).
 Provision of mechanisms for the manipulation of information (File and systems
structure, query processing).
 Providing for the safety of information in the database (crash recovery and
security).
 Concurrency control if the system is shared by users.
Application of DBMS
Sector Use of DBMS
Banking For customer information, account activities,

payments, deposits, loans, etc.
Airlines For reservations and schedule information.
Y.N.S.SATYANARAYANA CSE Page 1

Universities For student information, course registrations,
colleges and grades.
Telecommunication It helps to keep call records, monthly bills,

maintaining balances, etc.
Finance For storing information about stock, sales,

and purchases of financial instruments like
stocks and bonds.
Sales Use for storing customer, product & sales

information.
Manufacturing It is used for the management of supply chain

and for tracking production of items.
Inventories status in warehouses.
HR Management For information about employees, salaries,

payroll, deduction, generation of paychecks,
etc.
Advantages of Using the DBMS Approach
The advantages of using a DBMS and the capabilities that a good DBMS
should possess are:
1. Controlling Redundancy
In traditional file processing, every user group maintains its own files
for handling its data-processing applications. This may lead to duplicate
some or all of the same data in their own files. This redundancy in storing
the same data multiple times leads to several problems.
This redundancy is prevented in a database as there is a single
database and any change in it is reflected immediately. Because of this, there
is no chance of encountering duplicate data.
2. Restricting Unauthorized Access

When multiple users share a large database, it is likely that most
users will not be authorized to access all information in the database. For
example, financial data is often considered confidential, and only authorized

persons are allowed to access such data A DBMS should provide a security
and authorization subsystem.
3. Providing Persistent Storage for Program Objects
Databases can be used to provide persistent storage for program
objects and data structures. This is one of the main reasons for object-
oriented database systems.
The persistent storage of program objects and data structures is an
important function of database systems. The data structures provided by the
DBMS were incompatible with the programming language‘s data structures.
Object-oriented database systems typically offer data
structure compatibility with one or more object-oriented programming
languages.
4. Providing Storage Structures and Search Techniques for Efficient
Query Processing
Database systems must provide capabilities for efficiently executing
queries and updates. Because the database is typically stored on disk, the
DBMS must provide specialized data structures and search techniques to
speed up disk search for the desired records
5. Providing Backup and Recovery
A DBMS must provide facilities for recovering from hardware or
software failures. The backup and recovery subsystem of the DBMS is
responsible for recovery. For example, if the computer system fails in the
middle of a complex update transaction, the recovery subsystem is
responsible for making sure that the database is restored to the state it was in
before the transaction started executing.
6. Providing Multiple User Interfaces
Because many types of users with varying levels of technical
knowledge use a data-base, a DBMS should provide a variety of user
interfaces. These include query languages for casual users, programming
language interfaces for application programmers, forms and command codes
for parametric users, and menu-driven interfaces and natural language
interfaces for standalone users.
7. Representing Complex Relationships among Data

A database may include numerous varieties of data that are
interrelated in many ways and DBMS must have the capability to represent
a variety of complex relationships among the data, to define new
relationships as they arise, and to retrieve and update related data easily and
efficiently.

8. Enforcing Integrity Constraints
Most database applications have certain integrity
constraints that must hold for the data. A DBMS should provide capabilities
for defining and enforcing these constraints. The simplest type of integrity
constraint involves specifying a data type for each data item. It is customary
to call such constraints business rules.
9. Permitting Inferencing and Actions Using Rules
Some database systems provide capabilities for
defining deduction rules for inferencing new information from the stored
database facts. Such systems are called deductive database systems.
Some additional implications of using the database approach
Potential for Enforcing Standards.

The database approach permits the DBA to define and
enforce standards among database users in a large organization. This facil-
itates communication and cooperation among various departments, projects,
and users within the organization..
Reduced Application Development Time.
A prime selling feature of the data-base approach is that
developing a new application—such as the retrieval of certain data from the
database for printing a new report—takes very little time once a database is
up and running, substantially less time is generally required to create new
applications using DBMS facilities..
Flexibility.
It may be necessary to change the structure of a database as
requirements change. Modern DBMSs allow certain types of evolutionary
changes to the structure of the database without affecting the stored data and
the existing application programs.
Availability of Up-to-Date Information.

A DBMS makes the database available to all users. As
soon as one user‘s update is applied to the database, all other users can
immediately see this update.
Economies of Scale.
The DBMS approach permits consolidation of data
and applications, thus reducing the amount of wasteful overlap between

activities of data-processing personnel in different projects or departments as
well as redundancies among applications.
Advantages of DBMS over FILE SYSTEM
Drawbacks of File system

 Data redundancy: Data redundancy refers to the duplication of data. Data redundancy
often leads to higher storage costs and poor access time.
 Data inconsistency: Data redundancy leads to data inconsistency. If the data is changed
at one place and not on all the records then this can lead to data inconsistency.
 Data Isolation: Because data are scattered in various files, and files may be in different
formats, writing new application programs to retrieve the appropriate data is difficult.
 Dependency on application programs: Changing files would lead to change in
application programs.
 Atomicity issues: Atomicity of a transaction refers to ―All or nothing‖, which means
either all the operations in a transaction executes or none. It is difficult to achieve
atomicity in file processing systems.
 Data Security: Data should be secured from unauthorised access, for example a student
in a college should not be able to see the payroll details of the teachers, such kind of
security constraints are difficult to apply in file processing systems.
Advantage of DBMS
There are several advantages of Database management system over file system.
 No redundant data: Redundancy removed by data normalization. No data duplication

saves storage and improves access time.
 Data Consistency and Integrity: As we discussed earlier the root cause of data
inconsistency is data redundancy, since data normalization takes care of the data
redundancy, data inconsistency also been taken care of as part of it
 Data Security: It is easier to apply access constraints in database systems so that only
authorized user is able to access the data. Each user has a different set of access thus data
is secured from the issues such as identity theft, data leaks and misuse of data.
 Privacy: Limited access means privacy of data.
 Easy access to data – Database systems manages data in such a way so that the data is
easily accessible with fast response times.
 Easy recovery: Since database systems keeps the backup of data, it is easier
to do a full recovery of data in case of a failure.
 Flexible: Database systems are more flexible than file processing systems.

DATA MODEL
 A data model-a collection of concepts that can be used to describe the structure of a
database
 provides the necessary means to achieve this abstraction.
 structure of a database, we mean the data types, relationships, and constraints that should
hold for the data.
 Most data models also include a set of basic operations for specifying retrievals and
 updates on the database.
Data Model gives us an idea that how the final system will look like after its complete
implementation. It defines the data elements and the relationships between the data elements.
Data M
odels are used to show how data is stored, connected, accessed and updated in the database
management system.
The Relational model is the most widely used model. Apart from the Relational model,. Some of
the Data Models in DBMS are:
1. Hierarchical Model
2. Network Model
3. Entity-Relationship Model
4. Relational Model
Hierarchical Model
Hierarchical Model was the first DBMS model. This model organises the data in the
hierarchical tree structure. The hierarchy starts from the root which has root data and then it
expands in the form of a tree adding child node to the parent node. This model easily represents
some of the real-world relationships like food recipes, sitemap of a website etc. Example: We

can represent the relationship between the shoes present on a shopping website in the following
way:
Features of a Hierarchical Model

1. One-to-many relationship: The data here is organised in a tree-like structure where the
one-to-many relationship is between the datatypes. Also, there can be only one path from
parent to any node. Example: In the above example, if we want to go to the
node sneakers we only have one path to reach there i.e through men's shoes node.
2. Parent-Child Relationship: Each child node has a parent node but a parent node can
have more than one child node. Multiple parents are not allowed.
3. Deletion Problem: If a parent node is deleted then the child node is automatically
deleted.
4. Pointers: Pointers are used to link the parent node with the child node and are used to
navigate between the stored data. Example: In the above example the 'shoes' node points
to the two other nodes 'women shoes' node and 'men's shoes' node.
Advantages of Hierarchical Model

 It is very simple and fast to traverse through a tree-like structure.
 Any change in the parent node is automatically reflected in the child node so, the
integrity of data is maintained.
Disadvantages of Hierarchical Model

 Complex relationships are not supported.
 As it does not support more than one parent of the child node so if we have some
complex relationship where a child node needs to have two parent node then that can't be
represented using this model.
 If a parent node is deleted then the child node is automatically deleted.

Network Model
This model is an extension of the hierarchical model. It was the most popular model
before the relational model. This model is the same as the hierarchical model, the only difference
is that a record can have more than one parent. It replaces the hierarchical tree with a
graph. Example: In the example below we can see that node student has two parents i.e. CSE
Department and Library. This was earlier not possible in the hierarchical model.
Features of a Network Model

1. Ability to Merge more Relationships: In this model, as there are more relationships so
data is more related. This model has the ability to manage one-to-one relationships as
well as many-to-many relationships.
2. Many paths: As there are more relationships so there can be more than one path to the
same record. This makes data access fast and simple.
3. Circular Linked List: The operations on the network model are done with the help of the
circular linked list. The current position is maintained with the help of a program and this
position navigates through the records according to the relationship.
Advantages of Network Model

 The data can be accessed faster as compared to the hierarchical model. This is because
the data is more related in the network model and there can be more than one path to
reach a particular node. So the data can be accessed in many ways.
 As there is a parent-child relationship so data integrity is present. Any change in parent
record is reflected in the child record.
Disadvantages of Network Model

 As more and more relationships need to be handled the system might get complex. So, a
user must be having detailed knowledge of the model to work with the model.
 Any change like updation, deletion, insertion is very complex.
Entity-Relationship Model
Entity-Relationship Model or simply ER Model is a high-level data model diagram. In
this model, we represent the real-world problem in the pictorial form to make it easy for the
stakeholders to understand. It is also very easy for the developers to understand the system by
just looking at the ER diagram. We use the ER diagram as a visual tool to represent an ER
Model. ER diagram has the following three components:
 Entities: Entity is a real-world thing. It can be a person, place, or even a
concept. Example: Teachers, Students, Course, Building, Department, etc are some of the
entities of a School Management System.
 Attributes: An entity contains a real-world property called attribute. This is the
characteristics of that attribute. Example: The entity teacher has the property like teacher
id, salary, age, etc.
 Relationship: Relationship tells how two attributes are related. Example: Teacher works
for a department.
Example:
In the above diagram, the entities are Teacher and Department. The attributes of Teacher entity
are Teacher_Name, Teacher_id, Age, Salary, Mobile_Number. The attributes of
entity Department entity are Dept_id, Dept_name. The two entities are connected using the
relationship. Here, each teacher works for a department.
Features of ER Model
 Graphical Representation for Better Understanding: It is very easy and simple to
understand so it can be used by the developers to communicate with the stakeholders.
 ER Diagram: ER diagram is used as a visual tool for representing the model.

 Database Design: This model helps the database designers to build the database and is
widely used in database design.
Advantages of ER Model
 Simple: Conceptually ER Model is very easy to build. If we know the relationship
between the attributes and the entities we can easily build the ER Diagram for the model.
 Effective Communication Tool: This model is used widely by the database designers for
communicating their ideas.
 Easy Conversion to any Model: This model maps well to the relational model and can be
easily converted relational model by converting the ER model to the table. This model
can also be converted to any other model like network model, hierarchical model etc.
Disadvatages of ER Model
 No industry standard for notation: There is no industry standard for developing an ER
model. So one developer might use notations which are not understood by other
developers.
 Hidden information: Some information might be lost or hidden in the ER model. As it is
a high-level view so there are chances that some details of information might be hidden.
Relational Model
Relational Model is the most widely used model. In this model, the data is maintained
in the form of a two-dimensional table. All the information is stored in the form of row and
columns. The basic structure of a relational model is tables. So, the tables are also
called relations in the relational model. Example: In this example, we have an Employee table.
Features of Relational Model

 Tuples: Each row in the table is called tuple. A row contains all the information about
any instance of the object. In the above example, each row has all the information about
any specific individual like the first row has information about John.

 Attribute or field: Attributes are the property which defines the table or relation. The
values of the attribute should be from the same domain. In the above example, we have
different attributes of the employee like Salary, Mobile_no, etc.
Advnatages of Relational Model

 Simple: This model is more simple as compared to the network and hierarchical model.
 Scalable: This model can be easily scaled as we can add as many rows and columns we
want.
 Structural Independence: We can make changes in database structure without changing
the way to access the data. When we can make changes to the database structure without
affecting the capability to DBMS to access the data we can say that structural
independence has been achieved.
Disadvantages of Relatinal Model

 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.
Levels of abstraction
1. Data Abstraction
 The major purpose of a database system is to provide users with an
abstract view of the system. The system hides certain details of how data
is stored and maintained.
 Complexity should be hidden from database users.
 There are three levels of abstraction:
Physical Level:
 Deals with how the data are stored.
 E.g. index, B-tree, hashing.
 Lowest level of abstraction.
 Complex low-level structures described in detail.
Conceptual Level:
 Next highest level of abstraction.
 Describes what data are stored.

 Describes the relationships among data.
 Database administrator level.
View Level:
 Highest level.
 Describes part of the database for a particular group of users.
 Can be many different views of a database.
 E.g. tellers in a bank get a view of customer accounts, but not of payroll
data.
Three levels of abstraction.
Physical Data Independence
Physical data independence helps you to separate conceptual levels from the internal/physical
levels. It allows you to provide a logical description of the database without the need to specify
physical structures. Compared to Logical Independence, it is easy to achieve physical data
independence.
With Physical independence, you can easily change the physical storage structures or devices
with an effect on the conceptual schema. Any change done would be absorbed by the mapping
between the conceptual and internal levels. Physical data independence is achieved by the

presence of the internal level of the database and then the transformation from the conceptual
level of the database to the internal level.
Examples of changes under Physical Data Independence
Due to Physical independence, any of the below change will not affect the conceptual layer.
 Using a new storage device like Hard Drive or Magnetic Tapes

 Modifying the file organization technique in the Database
 Switching to different data structures.
 Changing the access method.
 Modifying indexes.
 Changes to compression techniques or hashing algorithms.
 Change of Location of Database from say C drive to D Drive
Logical Data Independence

Logical Data Independence is the ability to change the conceptual scheme without changing
1. External views
2. External API or programs
Any change made will be absorbed by the mapping between external and conceptual levels.
When compared to Physical Data independence, it is challenging to achieve logical data

independence.
Examples of changes under Logical Data Independence
Due to Logical independence, any of the below change will not affect the external layer.
 Add/Modify/Delete a new attribute, entity or relationship is possible without a rewrite of

existing application programs
 Merging two records into one
 Breaking an existing record into two or more records
Database Administrators
Administering database is the responsibility of the database administrator (DBA). The

DBA is responsible for authorizing access to the database, coordinating and monitoring
its use, and acquiring software and hardware resources as needed.
The DBA is accountable for problems such as security breaches and poor system
response time.

Database Designers
 Database designers are responsible for identifying the data to be stored in the data-base
and for choosing appropriate structures to represent and store this data
 Database designers typically interact with each potential group of users and
develop views of the database that meet the data and processing requirements of these
groups.
End Users
 End users are the people whose jobs require access to the database for
querying, updating, and generating reports; the database primarily exists for
their use. There are several categories of end users:
Casual end users occasionally access the database, but they may need
different information each time. They use a sophisticated database query
language to specify their requests and are typically middle- or high-level
managers or other occasional browsers.
 Naive or parametric end users make up a sizable portion of database

end users. Their main job function revolves around constantly querying and
updating the database, using standard types of queries and updates—
called canned transactions—that have been carefully programmed and
tested.
Sophisticated end users include engineers, scientists, business analysts,
and others who thoroughly familiarize themselves with the facilities of the
DBMS in order to implement their own applications to meet their complex
requirements.
Standalone users maintain personal databases by using ready-made pro-

gram packages that provide easy-to-use menu-based or graphics-based
interfaces. An example is the user of a tax package that stores a variety of
personal financial data for tax purposes.
System Analysts and Application Programmers(Software Engineers)

 System analysts determine the requirements of end users, especially naive
and parametric end users, and develop specifications for standard canned
transactions that meet these requirements.
 Application programmers implement these specifications as programs;
then they test, debug, document, and maintain these canned transactions.
DBA- Database Administrator:

Short for database administrator, a DBA designs, implements, administers, and
monitors data management systems and ensures design, consistency, quality, and
security.
Database administration involves the installing, configuring, monitoring, maintaining,
and improving the performance of databases and data stores.
While design of databases would be part of solution architecture, the implementation
and maintenance of development and production database environments would be the
work of the DBA.
Responsibilities of DBA:
The day-to-day activities that a DBA perform include:
 Creating and maintaining database standards and policies

 Supporting database design, creation, and testing activities
 Managing the database availability and performance, including incident and
problem management
 Administering database objects to achieve optimum utilization
 Defining and implementing event triggers that will alert on potential database
performance or integrity issues
 Performing database housekeeping, such as tuning, indexing, etc.
 Monitoring usage, transaction volumes, response times, concurrency levels, etc.
 Identifying reporting, and managing database security issues, audit trails, and
forensics
 Designing database backup, archiving, and storage strategy
Workers behind the Scene:
 In addition to those who design, use, and administer a database, others are associated
with the design, development, and operation of the DBMS software and
system environment.
 DBMS system designers and implementers design and implement the DBMS modules
and interfaces as a software package.

 Tool developers design and implement tools—the software packages that facilitate
database modeling and design, database system design, and improved performance.
Database Architecture:
The architecture of a database system is greatly influenced by the underlying computer
system on which the database system runs.
Database systems can be centralized, or client-server, where one server machine
executes work on behalf of multiple client machines.
Database systems can also be designed to exploit parallel computer architectures.
Distributed databases span multiple geographically separated machines.

Database System Architecture
A database system is partitioned into modules that deal with each of the responsibilities
of the overall system. The functional components of a database system can be broadly
divided into the storage manager and the query processor components.
The storage manager is important because databases typically require a large amount of
storage space. The query processor is important because it helps the database system
simplify and facilitate access to data.
It is the job of the database system to translate updates and queries written in a
nonprocedural language, at the logical level, into an efficient sequence of operations at
the physical level.
Database applications are usually partitioned into two or three parts, as in Figure 1.4. In a
two-tier architecture, the application resides at the client machine, where it invokes
database system functionality at the server machine through query language statements.
Application program interface standards like ODBC and JDBC are used for interaction
between the client and the server.
In contrast, in a three-tier architecture, the client machine acts as merely a front end and
does not contain any direct database calls. Instead, the client end communicates with an
application server, usually through a forms interface.
The application server in turn communicates with a database system to access data. The
business logic of the application, which says what actions to carry out under what
conditions, is embedded in the application server, instead of being distributed across
multiple clients. Three-tier applications are more appropriate for large applications, and
for applications that run on the WorldWideWeb.
Query Processor:
The query processor components include
· DDL interpreter, which interprets DDL statements and records the definitions in the data
dictionary.

· DML compiler, which translates DML statements in a query language into an evaluation plan
consisting of low-level instructions that the query evaluation engine understands.
A query can usually be translated into any of a number of alternative evaluation plans
that all give the same result. The DML compiler also performs query optimization, that
is, it picks the lowest cost evaluation plan from among the alternatives.
Query evaluation engine, which executes low-level instructions generated by the DML
compiler.
Storage Manager:
A storage manager is a program module that provides the interface between the lowlevel
data stored in the database and the application programs and queries submitted to the
system. The storage manager is responsible for the interaction with the file manager.
The raw data are stored on the disk using the file system, which is usually provided by a
conventional operating system. The storage manager translates the various DML
statements into low-level file-system commands. Thus, the storage manager is
responsible for storing, retrieving, and updating data in the database.
The storage manager components include:
· Authorization and integrity manager, which tests for the satisfaction of integrity constraints
and checks the authority of users to access data.
·
· Transaction manager, which ensures that the database remains in a consistent (correct) state
despite system failures, and that concurrent transaction executions proceed without conflicting.
·
· File manager, which manages the allocation of space on disk storage and the data structures
used to represent information stored on disk.
·
· Buffer manager, which is responsible for fetching data from disk storage into main memory,
and deciding what data to cache in main memory. The buffer manager is a critical part of the
database system, since it enables the database to handle data sizes that are much larger than the
size of main memory.
Transaction Manager:A transaction is a collection of operations that performs a single logical

function in a database application. Each transaction is a unit of both atomicity and consistency.
Thus, we require that transactions do not violate any database-consistency constraints. That is, if
the database was consistent when a transaction started, the database must be consistent when the
transaction successfully terminates. Transaction - manager ensures that the database remains in
a consistent (correct) state despite system failures (e.g., power failures and operating system
crashes) and transaction failures.

Entity-Relationship Model
 An entity-relationship model (ERM) is a data model which represents data as a
collection of entities and their relationships.
 In this, data is represented as a collection of entities and relationships among the entities.
 An entity is a thing or object in the real world that is distinguishable from other objects.
 Each entity is described by set of attributes.
 E.g. Customer entity is described by the attributes cust_no, cust_name, and address.
 A relationship is an association among several entities.
 e.g. A CustAcct relationship associates a Customer entity with Account entity.
 The set of all entities of the same type is called the entity set and the set of relationships
of the same type is called relationship set.
 The overall logical structure of a database can be expressed graphically by an E-R
diagram:
Different notations used for ER Diagram

 rectangles: represent entity sets.

 ellipses: represent attributes.
 diamonds: represent relationships among entity sets.
 lines: link attributes to entity sets and entity sets to relationships.
Figure 1.2 illustrates Entity-Relationship model.

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).
 Attributes are categorized into following types:
 Simple attribute – is an attribute that cannot be divided into sub parts.
 E.g. AccountNumber
 Composite attribute – is an attribute that can be divided into sub parts.
 E.g. Name can be divided into sub parts as first_ name, middle _name and last_ name.
 Single valued and Multi valued attributes – if an attribute takes only one value then it is
single valued attribute. if an attribute takes more than one value then it is multi valued
attribute.
 E.g. AccountNumber is single valued attribute and PhoneNumber is multi valued
attribute.
 Derived attribute – the value for this type of attribute can be derived from the values of
other related attributes.
 E.g. Age is a derived attribute as its value can be derived from Date_of_birth attribute.
Key attribute
 Key is a attribute or set of attributes that uniquely identify an entity set from set of
entities.
 E.g. suppose student entity consists of the attributes (Roll_no, Name, DOB, Address)
then the attribute Roll_no identifies uniquely a student from set of students. Hence
Roll_no is the key for student entity.

Relationship Types
 Relationship is an association among two or more entities.
 There are three kinds of relationships
 Unary Relationship – is a relationship that associates an entity with the
same entity. E.g. Figure 1.8 shows Unary Relationship(Reports_To)
Figure 1.8 An example Unary Relationship.
 Binary Relationship - is a relationship between any two entities.
Figure 1.9 An example Binary Relationship.

 Mapping Cardinalities - Mapping cardinalities, or cardinality ratios,
express the number of entities to which another entity can be associated
via a relationship set.
 Mapping cardinalities are most useful in describing binary relationship
sets.
 For a binary relationship R between entity sets A and B, the mapping
cardinalities must be one of the following:
 One to one. 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.

Figure1.10 An example one to one relationship

 One to many. An entity in A is associated with any number of
entities in B. An entity in B, however, can be associated with at most
one entity in A.
Figure1.11 An example one to many relationship.

 Many to one. An entity in A is associated with at most one entity in
B. An entity in B, however, can be associated with any number of
entities in A.
Figure 1.12 An example many to one relationship.

 Many to many. An entity in A is associated with any number of
entities in B, and an entity in B is associated with any number of
entities in A.

Figure1.13 An example many to many relationship.

 Ternary Relationship A ternary relationship is when three entities
participate in the relationship.
Figure 1.14 An example ternary relationship.

Participation constraints. Participation constraints can be divied into two
types
 Total paticipation - Every entity in the set is involved in some association of the
relationship.
 Partial participation – only some of the entities in entity set are involved in the
relationship.
Weak entity set – Entity set without primary key is termed as a weak entity
set.
 A weak entity set can be identified uniquely only by considering some of its
attributes in conjunction with the primary key of another entity, called the owner set.
 Owner entity set and weak entity set must participate in a one-to-many relationship
set.
 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.

Strong entity set - An entity set that has a primary key is called as a
strong entity set.
E.g. Figure 1.15 shows weak and strong entity sets. Here, Dependents is a weak entity set
and Employees is a strong entity set. Primary key for Employees is ssn and primary key for
Dependents is {ssn, dname}.
Figure1.15 Weak entity set.

Enhanced ER modeling
 Enhanced features of ER model are specialization and Generalization.
 Specialization - Specialization is the process of identifying subsets of an entity set (the
superclass) that share some distinguishing characteristic.
 Generalization - Generalization consists of identifying some common characteristics of
a collection of entity sets and creating a new entity set that contains entities possessing
these common characteristics.
 We can inherit the properties of super class into sub class by ISA relationship.
Figure 1.16 An example of Specialization and Generalization.

 We can specify two kinds of constraints with respect to ISA hierarchies, namely, overlap
and covering constraints. Overlap constraints determine whether two subclasses are

allowed to contain the same entity. Covering constraints determine whether the entities in
the subclasses collectively include all entities in the superclass.
E.g. Figure 1.16 shows specialization and generalization
Aggregation
 The E-R model cannot express relationships among relationships.
 Aggregation is used to express relationships among relationships
 As an example consider the following figure. Here, we treat the relationship
set work and the entity sets employee and project as a higher-level entity
set called work and this aggregate component can be related with machinery entity
using uses relationship. It means that employee working on a project uses machinery.
 Transforming an E-R diagram with aggregation into tabular form is easy. We create a
table for each entity and relationship set as before.
 The table for relationship set uses contains a column for each attribute in the primary key
of machinery and work.
Reduction to Relational Schemas( Conceptual design using ER diagrams)

 We can represent a database that conforms to an E-R database schema by a collection of
tables.
 For each entity set and for each relationship set in the database, there is a unique table to
which we assign the name of the corresponding entity set or relationship set.
 Each table has multiple columns, each of which has a unique name.

 Both the E-R model and the relational-database model are abstract, logical
representations of real-world enterprises.
 Converting a database representation from an E-R diagram to a table format is the way
we arrive at a relational-database design from an E-R diagram.
Converting ER Diagrams to Tables-
 ER diagram is converted into the tables in relational model.

 This is because relational models can be easily implemented by RDBMS like MySQL ,
Oracle etc.
Rule-01: For Strong Entity Set With Only Simple Attributes-
A strong entity set with only simple attributes will require only one table in relational model.
 Attributes of the table will be the attributes of the entity set.
 The primary key of the table will be the key attribute of the entity set.
Example-
Roll_no Name Gender
Schema : Student ( Roll_no , Name , Gender )

Rule-02: For Strong Entity Set With Composite Attributes-
A strong entity set with any number of composite attributes will require only one table in
relational model.
 While conversion, simple attributes of the composite attributes are taken into account and not
the composite attribute itself.
Example-

Roll_no First_name Last_name House_no Street City
Schema : Student ( Roll_no , First_name , Last_name , House_no , Street , City )
Rule-03: For Strong Entity Set With Multi Valued Attributes-
A strong entity set with any number of multi valued attributes will require two tables in relational
model.
 One table will contain all the simple attributes with the primary key.
 Other table will contain the primary key and all the multi valued attributes.

Example-
Roll_no City
Roll_no Mobile_no
Rule-04: Translating Relationship Set into a Table-
A relationship set will require one table in the relational model.

Attributes of the table are-
 Primary key attributes of the participating entity sets
 Its own descriptive attributes if any.
Set of non-descriptive attributes will be the primary key.

Example-
Emp_no Dept_id since
Schema : Works in ( Emp_no , Dept_id , since )
NOTE-
If we consider the overall ER diagram, three tables will be required in relational model-
 One table for the entity set ―Employee‖
 One table for the entity set ―Department‖
 One table for the relationship set ―Works in‖
Rule-05: For Binary Relationships With Cardinality Ratios-
The following four cases are possible-
Case-01: Binary relationship with cardinality ratio m:n

Case-02: Binary relationship with cardinality ratio 1:n
Case-03: Binary relationship with cardinality ratio m:1
Case-04: Binary relationship with cardinality ratio 1:1

Case-01: For Binary Relationship With Cardinality Ratio m:n
Here, three tables will be required-

1. A ( a1 , a2 )
2. R ( a1 , b1 )
3. B ( b1 , b2 )
Case-02: For Binary Relationship With Cardinality Ratio 1:n
Here, two tables will be required-

1. A ( a1 , a2 )
2. BR ( a1 , b1 , b2 )
NOTE- Here, combined table will be drawn for the entity set B and relationship set R.

Case-03: For Binary Relationship With Cardinality Ratio m:1

1. AR ( a1 , a2 , b1 )
2. B ( b1 , b2 )
NOTE- Here, combined table will be drawn for the entity set A and relationship set R.
Case-04: For Binary Relationship With Cardinality Ratio 1:1
Here, two tables will be required. Either combine ‗R‘ with ‗A‘ or ‗B‘
Way-01: Way-02:
1. A ( a1 , a2 ) 1.AR ( a1 , a2 , b1 )
2. BR ( a1 , b1 , b2 ) 2. B ( b1 , b2 )

Thumb Rules to Remember
While determining the minimum number of tables required for binary
relationships with given cardinality ratios, following thumb rules must be kept in
mind-
 For binary relationship with cardinality ration m : n , separate and
individual tables will be drawn for each entity set and relationship.
 For binary relationship with cardinality ratio either m : 1 or 1 : n , always
remember ―many side will consume the relationship‖ i.e. a combined table
will be drawn for many side entity set and relationship set.
 For binary relationship with cardinality ratio 1 : 1 , two tables will be
required. You can combine the relationship set with any one of the entity
sets.
Rule-06: For Binary Relationship With Both Cardinality Constraints and Participation
Constraints-
 Cardinality constraints will be implemented as discussed in Rule-05.

 Because of the total participation constraint, foreign key acquires NOT NULL constraint
i.e. now foreign key can not be null.
Case-01: For Binary Relationship With Cardinality Constraint and Total Participation
Constraint From One Side-
Because cardinality ratio = 1 : n , so we will combine the entity set B and relationship set R.
Then, two tables will be required-
1. A ( a1 , a2 )
2. BR ( a1 , b1 , b2 )
Because of total participation, foreign key a1 has acquired NOT NULL constraint, so it can‘t be
null now.

Case-02: For Binary Relationship With Cardinality Constraint and Total Participation
Constraint From Both Sides-
If there is a key constraint from both the sides of an entity set with total participation, then that
binary relationship is represented using only single table.
ARB(a1,a2,b1,b2)
-
Rule-07: For Binary Relationship With Weak Entity Set-
Weak entity set always appears in association with identifying relationship with total
participation constraint.

1. A ( a1 , a2 )
2. BR ( a1 , b1 , b2 )

PRACTICE PROBLEMS BASED ON CONVERTING ER DIAGRAM TO
TABLES-
Problem-01:
Find the minimum number of tables required for the following ER diagram in relational model-
Solution-
Applying the rules, minimum 3 tables will be required-
 MR1 (M1 , M2 , M3 , P1)
 P (P1 , P2)
 NR2 (P1 , N1 , N2)
Problem-02:
Find the minimum number of tables required to represent the given ER diagram in relational
model-
Solution-
 AR1R2 (a1 , a2 , b1 , c1)
 B (b1 , b2)
 C (c1 , c2)
 R3 (b1 , c1)

Problem-03:
model-
Solution-
 BR1R4R5 (b1 , b2 , a1 , c1 , d1)

 A (a1 , a2)
 R2 (a1 , c1)
 CR3 (c1 , c2 , d1)
 D (d1 , d2)
Problem-04:
model-
Solution-
 E1 (a1 , a2)
 E2R1R2 (b1 , b2 , a1 , c1 , b3)
 E3 (c1 , c2)

Problem-05:
model-
Solution-
Applying the rules that we have learnt, minimum 6 tables will be required-
 Account (Ac_no , Balance , b_name)
 Branch (b_name , b_city , Assets)
 Loan (L_no , Amt , b_name)
 Borrower (C_name , L_no)
 Customer (C_name , C_street , C_city)
 Depositor (C_name , Ac_no)

CASE STUDY:
Database Design For Banking Enterprise

 To design the database for Banking Enterprise, first the requirements of Banking
Enterprise are gathered and specified. From this requirement specification, the conceptual
design of database is created. Here are the major characteristics of the banking enterprise;
 The bank is organized into branches. Each branch is located in a particular city
and is identified by a unique name. The bank monitors the assets of each branch.
 Bank customers are identified by their customer-id values. The bank stores each
customer‘s name, and the street and city where the customer lives. Customers
may have accounts and can take out loans. A customer may be associated with a
particular banker, who may act as a loan officer or personal banker for that
customer.
 Bank employees are identified by their employee-id values. The bank
administration stores the name and telephone number of each employee, the
names of the employee‘s dependents, and the employee-id number of the
employee‘s manager. The bank also keeps track of the employee‘s start date and,
thus, length of employment
 The bank offers two types of accounts—savings and checking accounts. Accounts
can be held by more than one customer, and a customer can have more than one
account. Each account is assigned a unique account number. The bank maintains a
record of each account‘s balance, and the most recent date on which the account
was accessed by each customer holding the account. In addition, each savings
account has an interest rate, and overdrafts are recorded for each checking
account.
 A loan originates at a particular branch and can be held by one or more customers.
A loan is identified by a unique loan number. For each loan, the bank keeps track
of the loan amount and the loan payments. Although a loanpayment number does
not uniquely identify a particular payment among those for all the bank‘s loans, a
payment number does identify a particular payment for a specific loan. The date
and amount are recorded for each payment

 the bank would keep track of deposits and withdrawals from savings and checking
accounts.
Keeping in mind the above requirements, the design of the database proceeds
as follows:
Step1: Identify the entity sets and their attributes

 The entity sets that could be identified for Banking Enterprise are:
 branch entity set, with attributes branch-name, branch-city, and assets
 customer entity set, with attributes customer-id, customer-name, customer-street, and
customer-city
 employee entity set, with attributes employee-id, employee-name, telephone-number,
salary, and manager. Additional descriptive features are the multi-valued attribute
dependent-name, the base attribute start-date, and the derived attribute employment-
length
 Two account entity sets—savings-account and checking-account—with the common
attributes of account-number and balance; in addition, savings-account has the attribute
interest-rate and checking-account has the attribute overdraft-amount
 The loan entity set, with the attributes loan-number, amount, and originating-branch
 The weak entity set loan-payment, with attributes payment-number, payment-date, and
payment-amount.
Step2: Identify the relationships and their cardinalities

 borrower, a many-to-many relationship set between customer and loan
 loan-branch between loan and branch, a many-to-one relationship set that indicates in which
branch a loan originated
 loan-payment, a one-to-many relationship from loan to payment, which documents that a
payment is made on a loan
 depositor, with relationship attribute access-date, a many-to-many relationship set between
customer and account, indicating that a customer owns an account

 cust-banker, with relationship attribute type, a many-to-one relationship set expressing that a
customer can be advised by a bank employee, and that a bank employee can advise one or
more customers
 works-for, a relationship set between employee entities with role indicators manager and
worker; the mapping cardinalities express that an employee works for only one manager and
that a manager supervises one or more employees.
Step3: Draw the E-R diagram with the identified entity sets and their
relationships


DBMS Unit-1

Uploaded by

Copyright:

Available Formats

DBMS Unit-1

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

DBMS Unit-1

Uploaded by

Copyright:

Available Formats

Database Management Systems ----UNIT-1

Introduction to Database Management Systems

Introduction: DBMS stands for Database Management System. (DBMS =

Sector Use of DBMS

Banking For customer information, account activities,

Airlines For reservations and schedule information.

Y.N.S.SATYANARAYANA CSE Page 1

Telecommunication It helps to keep call records, monthly bills,

Finance For storing information about stock, sales,

Sales Use for storing customer, product & sales

Manufacturing It is used for the management of supply chain

HR Management For information about employees, salaries,

Advantages of Using the DBMS Approach

2. Restricting Unauthorized Access

Y.N.S.SATYANARAYANA CSE Page 2

7. Representing Complex Relationships among Data

Y.N.S.SATYANARAYANA CSE Page 3

Some additional implications of using the database approach

Potential for Enforcing Standards.

Availability of Up-to-Date Information.

Y.N.S.SATYANARAYANA CSE Page 4

Advantages of DBMS over FILE SYSTEM

Drawbacks of File system

 No redundant data: Redundancy removed by data normalization. No data duplication

Y.N.S.SATYANARAYANA CSE Page 5

Y.N.S.SATYANARAYANA CSE Page 6

Features of a Hierarchical Model

Advantages of Hierarchical Model

Disadvantages of Hierarchical Model

Y.N.S.SATYANARAYANA CSE Page 7

Features of a Network Model

Advantages of Network Model

Y.N.S.SATYANARAYANA CSE Page 8

Y.N.S.SATYANARAYANA CSE Page 9

Features of Relational Model

Y.N.S.SATYANARAYANA CSE Page 10

Advnatages of Relational Model

Disadvantages of Relatinal Model

Y.N.S.SATYANARAYANA CSE Page 11

Physical Data Independence

Y.N.S.SATYANARAYANA CSE Page 12

Examples of changes under Physical Data Independence

 Using a new storage device like Hard Drive or Magnetic Tapes

Logical Data Independence

When compared to Physical Data independence, it is challenging to achieve logical data

Examples of changes under Logical Data Independence

 Add/Modify/Delete a new attribute, entity or relationship is possible without a rewrite of

Administering database is the responsibility of the database administrator (DBA). The

Y.N.S.SATYANARAYANA CSE Page 13

 Naive or parametric end users make up a sizable portion of database

Standalone users maintain personal databases by using ready-made pro-

System Analysts and Application Programmers(Software Engineers)

Y.N.S.SATYANARAYANA CSE Page 14

DBA- Database Administrator:

 Creating and maintaining database standards and policies

Workers behind the Scene:

Y.N.S.SATYANARAYANA CSE Page 15

Y.N.S.SATYANARAYANA CSE Page 16

Database System Architecture