DBMS UNIT-1 July 3, 2023

Database Management System

UNIT-1
Faculty Name: Sumit Tyagi

Database: DataBase is a collection of related data.

1. Database Management System

A database management system is a collection of programs that enables user to create and maintain a database. The DBMS is a
general purpose software that facilitates the processes of defining, constructing, mainipulating, and shairing database among
various users and applications.

Defining a database involves specifying the data types,relationship, structure and constraints for the data to be stored in the
database.
Constructing the database is the process of storing the data itself on some stotage medium that is controlled by the DBMS.
Manipulating a database include such functions as querying the database to retrieve specific data, updating the database, and
generating reports from the data.
Sharing a database allows multiple users and programs to access the database concurrently.
Protection includes both system protection against hardware or software malfunction and Security protection against
unathorized or malicious access.

Database Applications

 Banking: all transactions

 Airlines: reservations, schedules
 Universities: registration, grades
 Sales: customers, products, purchases
 Online retailers: order tracking, customized
recommendations
 Manufacturing: production, inventory,
orders, supply chain
 Human resources: employee records,
salaries, tax deductions

Fig-1 A simplified database system

environment

2. Advantages of Database Management System (DBMS) over the traditional file system
 Controlling redundancy and inconsistency
 Restricting Unathurized access
 Enforcing Integrity constraints
 Atomicity of updates
 Esiear to accessing data
 Concurrent-access by multiple users
 Providing Backup and Recovery

sumittyagi999@gmail.com Page 1
DBMS UNIT-1 July 3, 2023
Example:

Fig-2 A database that

stored student, course and
grade information.

3. Levels of Data Abstraction

Physical Level : The lowest levelof abstraction describes how data are actually stored. The physical level describes
complex low-level data structures in detail.
Logical Level: the next higher level of abstraction describes what data are stored in the database, what relationships are
exist among those data, and what integrity constraints are impose on data.
View level: the highest level of abstraction describes only part of the entire database. The application programs hide details
of data types. Views can also hide information (such as an employee’s salary) for security purposes.

4. Instances and Schema

Instances
The collection of information stored in the database at a particular moment is called an instance of the database.
Schema
The overall design or description of the databese is called the database schema.
The physical schema (Internal Schema) describe the database design at the physical level. The physical schema describe
the complete details of data storage and access paths for the database.
The Logical schema(conceptual schema) describes the database design at the logical level. Logical schema describes data
types, relationship, user operations, and constraints.
View (External) levels includes number of external schema or user views. Each external schema describes the part of the
database that a particular user group is interested in and hides the rest of the database from that user group.

sumittyagi999@gmail.com Page 2
DBMS UNIT-1 July 3, 2023

Fig-3 Illustrating three schema

Architeture.

Example

Fig-4 Schema diagram for the database in fig-2.

sumittyagi999@gmail.com Page 3
DBMS UNIT-1 July 3, 2023
5. Data Independence
Logical data Independence is the capacity to change the conceptual schema without having to change external
schema. We may change the conceptual schema to expand the database by adding a record type or data item, to change
constraints, or to reduce the database by removing a record type or data item.
Physical data independence is the capacity to change the internal schema without having to change the conceptual
schema.

6. Data Models

 Relational model: The relational model uses a collection of tables to represent both data and the relationship
among those data. Each table has multiple columns and each coulmns has a unique name. Each table contains
records of a particular type. Each record type defines a fixed number of fields or attributes.
 Entity-Relationship data model (mainly for database design): The entity-relationship (E-R) data model is
based on a perception of real world that consists of a collection of basic objects, called entities, and of relationship
among these objects.
 Object-based data models (Object-oriented and Object-relational): The object-oriented data model can be
seen as extending the E-R model with notation of ecapusulation, methods, and object identity.
 Semistructured Data Model: the semistructured data models permits the specification of data where individual
data items of the same type may have different set of attributes. XML is widely used to represent semistructured
data.

7. Database Languages
a. Data Manipulation Language (DML)
Language for accessing and manipulating the data organized by the appropriate data model. DML also known as
query language.
The type of access are
 Insertion
 Deletion
 Retrieval
 Modification
Two classes of languages are
Procedural DMLs require a user to specify what data are required and how to get those data.
Declarative (nonprocedural) DMLs require a user specify what data are needed without specifying how to get
those data.
SQL is the most widely used query language

b. Data Definition Language (DDL)

DDL is used by the DBA and database designer to define conceptual schema of the database. DDL
compiler generates a set of tables stored in a data dictionary.Data dictionary contains metadata (i.e., data
about data). The DDL also defines
 Integrity constraints
 Domain constraints
 Referential integrity (e.g. branch_name must correspond to a valid branch in the branch table)
 Authorization
c. Storage Definition language (SDL)
 Specifies the storage structure and access methods used

sumittyagi999@gmail.com Page 4
DBMS UNIT-1 July 3, 2023
8. Entity-Relationship model
7.1 Entity
An entity is an object that exists and is distinguishable from other objects.For example, each person in an
enterprise is an entity. An entity has a set of attributes, and the values for some set of attributes may uniquely
identify an entity. For instance, a person may have a person-id attribute whose value uniquely identifies that
person. Thus, the value 677-89-9011 for person-id would uniquely identify one particular person in the
enterprise.

7.2 Entity Set or Entity Type

An entity set is a set of entities of the same type that share the same attributes. The set of all persons who are
customers at a given bank, for example, can be defined as the entity set customer. Similarly, the entity set
loan might represent the set of all loans awarded by a particular bank. Each records in customer and loan
entity type is an individual entity.

Fig 5 customer and loan entity sets

7.3 Attributes
An entity is represented by a set of attributes. For example, an employee entity may be described by the
employee's name, age, address, salary, and job. A particular entity will have a value for each of its attributes. The
attribute values that describe each entity become a major part of the data stored in the database.
Each entity has a value for each of its attributes. The employee entity e1 has four attributes: Name, Address, Age,
and HomePhone; their values are "John Smith," "2311 Kirby, Houston, Texas 77001," "55," and "713-749-2630,"
respectively.

sumittyagi999@gmail.com Page 5
DBMS UNIT-1 July 3, 2023

Fig. 6

 Simple and Composit attribute: Attributes that are not divisible are called simple or atomic
attributes. Composite attributes, on the other hand, can be divided into subparts (that is, other
attributes). For example, an attribute name could be structured as a composite attribute consisting of
first-name, middle-initial, and last-name. The Address attribute of the employee entity can be
subdivided into StreetAddress, City, State, and Zip,3 with the values "2311 Kirby," Houston,"
"Texas," and "77001."
 Single valued and multivalued attribute: Most attributes have a single value for a particular entity;
such attributes are called single-valued. For example, Age is a single-valued attribute of a person. In
some cases an attribute can have a set of values for the same entity-for example, a Colors attribute
for a car, or a CollegeDegrees attribute for a person. Cars with one color have a single value,
whereas two-tone cars have two values for Colors. Similarly, one person may not have a college
degree, another person may have one, and a third person may have two or more degrees; therefore,
different persons can have different numbers of values for the CollegeDegrees attribute. Such
attributes are called multivalued.
 Derived attribute. The value for this type of attribute can be derived from the values of other related
attributes or entities. suppose that the customer entity set has an attribute age, which indicates the
customer’s age. If the customer entity set also has an attribute date-of-birth, we can calculate age
from date-of-birth and the current date. Thus, age is a derived attribute. In this case, date-of-birth
may be referred to as a base attribute, or a stored attribute. The value of a derived attribute is not
stored, but is computed when required.

7.4 Relationship Sets

A relationship is an association among several entities. A relationship set is a set of relationships of the same type.
Formally, it is a mathematical relation on n ≥ 2 (possibly nondistinct) entity sets. If E1, E2, . . .,En are entity sets, then
a relationship set R is a subset of {(e1, e2, . . . , en) | e1 ∈ E1, e2 ∈ E2, . . . , en ∈ En} where (e1, e2, . . . , en) is a
relationship.
Consider the two entity sets customer and loan in Figure 5. We define the relationship set borrower to denote the
association between customers and the bank loans that the customers have.
The association between entity sets is referred to as participation; that is, the entity sets E1, E2, . . .,En participate in
relationship set R. As an illustration, the individual customer entity Hayes, who has customer identifier 677-89-9011,
and the loan entity L-15 participate in a relationship instance of borrower.

sumittyagi999@gmail.com Page 6
DBMS UNIT-1 July 3, 2023
9. Mapping Cardinalities
Mapping cardinalities, or cardinality ratios, express the number of entities to which another entity can be
associated via a relationship set.
For a binary relationship set R between entity sets A and B, the mapping cardinality
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. (See Figure 7.a.)
 One to many. An entity in A is associated with any number (zero or more) of entities in B. An entity in B,
however, can be associated with at most one entity in A. (See Figure 7.b.)
 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 (zero or more) of entities in A. (See Figure 8.a.)
 Many to many. An entity in A is associated with any number (zero or more) of entities in B, and an entity in
B is associated with any number (zero or more) of entities in A.( See Figure 8.b.)

Fig 7.a one to one Fig 7.b many to one Fig 8.a one to many Fig. 8.b many to many

10. Participation Constraints

There are two types of participation constraints- total and partial which we illustrate by example. If a company policy
states that every employee must work for a department, then an employee entity can exist only if it participates in at
least one WORKS_FOR relationship instance. Thus, the participation of EMPLOYEE in WORKS_FOR is called total
participation, meaning that every entity in "the total set" of employee entities must be related to a department entity
via WORKS_FOR. Total participation is also called existence dependency. In department manager relationship we do
not expect every employee to manage a department, so the participation of EMPLOYEE in the MANAGES
relationship type is partial, meaning that some or "part of the set of" employee entities are related to some department
entity via MANAGES, but not necessarily all.

11. Key
A super key of an entity set is a set of one or more attributes whose values uniquely determine each entity. For
example, the customer-id attribute of the entity set customer is sufficient to distinguish one customer entity from
another. Thus, customer-id is a superkey. Similarly, the combination of customer-name and customer-id is a super key
for the entity set customer. The customer-name attribute of customer is not a superkey, because several people might
have the same name.
A candidate key of an entity set is a minimal super key.
Customer_id is candidate key of customer
account_number is candidate key of account

Although several candidate keys may exist, one of the candidate keys is selected to be the primary key. We shall use
the term primary key to denote a candidate key that is chosen by the database designer as the principal means of
identifying entities within an entity set.

sumittyagi999@gmail.com Page 7
DBMS UNIT-1 July 3, 2023

12. Symbols Used in E-R Notation

13. Weak Entity Sets

An entity set may not have sufficient attributes to form a primary key. Such an entity set is termed a weak entity set.
An entity set that has a primary key is termed a strong entity set.
As an illustration, consider the entity set payment, which has the three attributes: payment-number, payment-date, and
payment-amount. Payment numbers are typically sequential numbers, starting from 1, generated separately for each

sumittyagi999@gmail.com Page 8
DBMS UNIT-1 July 3, 2023
loan. Thus, although each payment entity is distinct, payments for different loans may share the same payment
number. Thus, this entity set does not have a primary key; it is a weak entity set.
For a weak entity set to be meaningful, it must be associated with another entity set, called the identifying or owner
entity set. The relationship associating the weak entity set with the identifying entity set is called the identifying
relationship.
A weak entity set does not have a primary key. The discriminator of a weak entity set is a set of attributes that allows
this distinction to be made. For example, the discriminator of the weak entity set payment is the attribute payment-
number, since, for each loan, a payment number uniquely identifies one single payment for that loan. The
discriminator of a weak entity set is also called the partial key of the entity set.
The primary key of a weak entity set is formed by the primary key of the identifying entity set, plus the weak entity
set’s discriminator. In the case of the entity set payment, its primary key is {loan-number, payment-number}, where
loan-number is the primary key of the identifying entity set, namely loan, and payment-number distinguishes payment
entities within the same loan.

14. Extended E-R Features

14.1 Subclasses and Super class

An entity set may include subgroupings of entities that are distinct in some way from other entities in the set.
For instance, a subset of entities within an entity set may have attributes that are not shared by all the entities
in the entity set. For example, the entity type EMPLOYEE describes the type (that is, the attributes and
relationships) of each employee entity, and also refers to the current set of EMPLOYEE entities in the
COMPANY database. the entities that are members of the EMPLOYEE entity type may be grouped further
into SECRETARY, ENGINEER, MANAGER, TECHNICIAN, SALARIED_EMPLOYEE,
HOURLY_EMPLOYEE, and so on. The set of entities in each of the latter groupings is a subset of the
entities that belong to the EMPLOYEE entity set, meaning that every entity that is a member of one of these
subgroupings is also an employee. We call each of these subgroupings a subclass of the EMPLOYEE entity
type, and the EMPLOYEE entity type is called the superclass for each of these subclasses.

14.2 Specialization

Specialization is the process of defining a set of subclasses of an entity type; this entity type is called the
super class of the specialization. The set of subclasses that form a specialization is defined on the basis of
some distinguishing characteristic of the entities in the super class. For example, the set of subclasses
{SECRETARY, ENGINEER, TECHNICIAN} is a specialization of the super class EMPLOYEE that
distinguishes among employee entities based on the job type of each employee entity.

14.3 Generalization

We can think of a reverse process of specialization, in which we identify their common features, and
generalize them into a single super class of which the original entity types are special subclasses. For
example, the entity types CAR and TRUCK, they have several common attributes, can be generalized into the
entity type VEHICLE.

Constraints on Generalizations and Specialization

 Condition-defined
 User-defined
 Disjoint.
 Overlapping
 Total generalization or specialization
 Partial generalization or specialization.

sumittyagi999@gmail.com Page 9
DBMS UNIT-1 July 3, 2023

Fig. 9. Show the superclass/subclass and specialization.

sumittyagi999@gmail.com Page 10
DBMS UNIT-1 July 3, 2023
Fig 10. Show the Generalization.

Fig. Example of disjoint generalization

Fig example of overlapping generalization

sumittyagi999@gmail.com Page 11
DBMS UNIT-1 July 3, 2023

14.4 Attribute Inheritance

A crucial property of the higher- and lower-level entities created by specialization and generalization is
attribute inheritance. The attributes of the higher-level entity sets are said to be inherited by the lower-level
entity sets. For example, CAR and TRUCK inherit the attributes of VEHICLE. Thus, CAR is described by its
VehicleId, Price, and LicennsePlateNo, and additionally NoOfPassenger and MaxSpeed; TRUCK is
described by its VehicleId, Price, and LicennsePlateNo, and additionally NoOfAxles and Tonnage attributes.

15. Designing Of E-R Diagram

Fig 11. E-R Diagram for banking system

sumittyagi999@gmail.com Page 12
DBMS UNIT-1 July 3, 2023

16. Reduction of an E-R Schema to Tables

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.

16.1 Tabular Representation of Strong Entity Sets

Let E be a strong entity set with descriptive attributes a1, a2, . . . , an. We represent this entity by a table called E
with n distinct columns, each of which corresponds to one of the attributes of E. Each row in this table
corresponds to one entity of the entity set E.
As an illustration, consider the entity set loan of the E-R diagram in Figure 11. This entity set has two attributes:
loan-number and amount. We represent this entity set by a table called loan, with two columns. The attribute
loan-number is a key attribute of loan table.

Fig.12. The loan Table for loan entity set.

16.2 Tabular Representation of Weak Entity Sets

Let A be a weak entity set with attributes a1, a2, . . . , am. Let B be the strong entity set on which A depends. Let
the primary key of B consist of attributes b1, b2, . . . , bn. We represent the entity set A by a table called A with
one column for each attribute of the set:
{a1, a2, . . . , am} ∪ {b1, b2, . . . , bn}
As an illustration, consider the entity set payment in the E-R diagram of Figure 11. This entity set has three
attributes: payment-number, payment-date, and payment-amount. The primary key of the loan entity set, on
which payment depends, is loan-number. Thus, we represent payment by a table with four columns labeled loan-
number, paymentnumber, payment-date, and payment-amount, as in Figure 13than.

Fig. 13. The payment table

sumittyagi999@gmail.com Page 13
DBMS UNIT-1 July 3, 2023
In second example consider the dependent week entity in ER diagram of company. The tabular representation of
dependent is

DEPENDENT
ESSN DEPENDENTNAME GENDER BDATE RELATIONSHIP

16.3 Tabular Representation of Relationship Sets

Let R be a relationship set, let a1, a2, . . . , am be the set of attributes formed by the union of the primary keys of
each of the entity sets participating in R, and let the descriptive attributes (if any) of R be b1, b2, . . . , bn. We
represent this relationship set by a table called R with one column for each attribute of the set:
{a1, a2, . . . , am} ∪ {b1, b2, . . . , bn}
The set of attributes {a1, a2, . . . , am} is a primary key of Relationship set.

16.4 Combination of Tables

Consider a many-to-one relationship set AB from entity set A to entity set B. Using our table-construction scheme
outlined previously, we get three tables: A, B, and AB. Suppose further that the participation of A in the
relationship is total; that is, every entity a in the entity set A must participate in the relationship AB. Then we can
combine the tables A and AB to form a single table consisting of the union of columns of both tables.

As an illustration, consider the E-R diagram of Figure 14. The double line in the E-R diagram indicates that the
participation of account in the account-branch is total. Hence, an account cannot exist without being associated
with a particular branch. Further, the relationship set account-branch is many to one from account to branch.
Therefore, we can combine the table for account-branch with the table for account and require only the
following two tables:
• account, with attributes account-number, balance, and branch-name
• branch, with attributes branch-name, branch-city, and assets.

Fig. 14 E-R Diagram

16.5 Composite Attributes

We handle composite attributes by creating a separate attribute for each of the component attributes; we do not
create a separate column for the composite attribute itself. Suppose address is a composite attribute of entity set
customer, and the components of address are street and city. The table generated from customer would then
contain columns address-street and address-city; there is no separate column for address.

16.5 Multivalued Attributes

We have seen that attributes in an E-R diagram generally map directly into columns for the appropriate tables.
Multivalued attributes, however, are an exception; new tables are created for these attributes.
For a multivalued attribute M, we create a table T with a column C that corresponds to M and columns
corresponding to the primary key of the entity set of which M is an attribute. As an illustration, consider the E-R
diagram in Figure 11. The diagram includes the multivalued attribute dependent-name. For this multivalued
attribute, we create a table dependent-name, with columns dname, referring to the dependent-name attribute of
employee, and employee-id, representing the primary key of the entity set employee. Each dependent of an
employee is represented as a unique row in the table.

sumittyagi999@gmail.com Page 14
DBMS UNIT-1 July 3, 2023

16.6 Tabular Representation of Generalization:

Create a table for the higher-level entity set. For each lower-level entity set, create a table that includes a column
for each of the attributes of that entity set plus a column for each attribute of the primary key of the higher-level
entity set. Thus, for the E-R diagram of Figure 2.17, we have three tables:
• account, with attributes account-number and balance
• savings-account, with attributes account-number and interest-rate
• checking-account, with attributes account-number and overdraft-amount

Fig 15 Generalization and Specialization

sumittyagi999@gmail.com Page 15