ITM(SLS) BARODA UNIVERSITY, VADODARA

Bachelor of Computer Application

Course Code: C4261C3

Semester- II
Subject Name: Database Management System

Unit -1

Data Independence:
Data Independence means users and data should not directly interact with each
other. The user should be at a different level and the data should be present at some
other level.
- Data Independence is achieved through use of the three levels of data abstraction.
Consider an Example of a University Database. At the different levels this is how
the implementation will look like:

Type of Schema Implementation

View 1: Course info(cid:int,cname:string)
External Schema
View 2: studeninfo(id:int. name:string)
Students(id: int, name: string, login: string,
age: integer)
Conceptual/logical
Courses(id: int, cname:string, credits:integer)
Shema
Enrolled(id: int, grade:string)

Physical/internal 1) Index on the first column of Students.

2) Relations stored as unordered files.
Schema

Logical Data Independence

- It stores information about how data is managed inside. For example, a table
(relation) stored in the database and all its constraints, applied on that relation.
 Examples of changes under Logical Data Independence
Due to Logical independence, any of the below change will not affect the
external layer.

1. Add/Modify/Delete a new attribute, entity or relationship is possible

without a rewrite of existing application programs
2. Merging two records into one
3. Breaking an existing record into two or more records

Physical Data Independence

- The conceptual schema insulates users from changes in physical storage details.
This property is referred to as “Physical Data Independence”. All the schemas
are logical, and actual data is stored, in bit format on the disk.
- It has power to change the physical data without impacting the schema or logical
data.
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

Example:

Suppose that we created a database with 10,000 records in mind but the
database is growing bigger. In this case we need to upgrade the physical level
and optimize it for performance.

Here are a few common changes that we do on the physical level to optimize it.

- Change the operating system Or File System Or Storage device etc.

Queries in a DBMS:
 - The ease with which information can be obtained from a database, often
determines its value to a user.
- Here are some questions a user might ask:
1. What is the name of the student with student ID 1234?
2. What is the average salary of professors who teach course CS564?
Such questions involving the data stored in a DBMS are called, Queries. A
DBMS provides a specialized language is called “Query Language”, in which
queries can be posed.
- A DBMS enables users to create, modify and query data through a “data
manipulation language (DML) “ .
Transaction Management:
- Consider a database that holds information about airline reservations. It is
possible that several travel agents are looking up information about available
seats on various flights and making new seat reservations.
- When several users access database concurrently, the DBMS must order their
requests carefully, to avoid conflicts.
- EX: When one travel agent look up flight 100 on some given day and finds an
empty seat, another travel agent may simultaneously, be making a reservation for
that seat, thereby making the information seen by the first agent obsolete.
- Another example, is bank database. While one user’s application program is
computing the total deposits, another application may transfer money from an
account that the first application has just ‘seen’ to an account that has not yet
been, thereby causing the total to appear larger than it should be.
- Clearly, disallowing concurrent access can degrade performance.
- The DBMS must protect users from the effects of system failures by ensuring
that all data is restored to a consistent state when the system is restarted after a
crash.
- A transaction is a any one execution of a user program in a DBMS. Partial
transactions are not allowed and effect of a group of transactions is equivalent to
some serial execution of all transactions.
- Concurrent execution of transaction
- Incomplete transaction and system crashes.
- Locking control – shared and exclusive lock.
Structures of DBMS:
- The DBMS accepts SQL commands generated from a variety of user interfaces,
produces query evaluation plans, executes these plans against the database, and
returns the answers.

1. Query Processor: It interprets the requests (queries) received from end user via
an application program into instructions. It also executes the user request which is
received from the DML compiler.
Query Processor contains the following components –
DML Compiler: It processes the DML statements into low level instruction
(machine language), so that they can be executed.
DDL Interpreter: It processes the DDL statements into a set of table containing
meta data (data about data).
Embedded DML Pre-compiler: It processes DML statements embedded in an
application program into procedural calls.
Query Optimizer: It executes the instruction generated by DML Compiler.
2. Storage Manager: Storage Manager is a program that provides an interface
between the data stored in the database and the queries received.
It is also known as Database Control System. It maintains the consistency and
integrity of the database by applying the constraints and executing the DCL
statements. It is responsible for updating, storing, deleting, and retrieving data in the
database.
It contains the following components –
Authorization Manager: It ensures role-based access control, i.e,. checks whether
the particular person is privileged to perform the requested operation or not.
Integrity Manager: It checks the integrity constraints when the database is
modified.
Transaction Manager: It controls concurrent access by performing the operations
in a scheduled way that it receives the transaction. Thus, it ensures that the database
remains in the consistent state before and after the execution of a transaction.
File Manager: It manages the file space and the data structure used to represent
information in the database.
Buffer Manager: It is responsible for cache memory and the transfer of data
between the secondary storage and main memory.
3. Disk Storage: It contains the following components –
Data Files: It stores the data.
Data Dictionary: It contains the information about the structure of any database
object. It is the repository of information that governs the metadata.
Indices: It provides faster retrieval of data item.
The structure of a Database Management System (DBMS) can be divided into three
main components: the Internal Level, the Conceptual Level, and the External Level.
Internal Level: This level represents the physical storage of data in the database. It
is responsible for storing and retrieving data from the storage devices, such as hard
drives or solid-state drives. It deals with low-level implementation details such as
data compression, indexing, and storage allocation.
Conceptual Level: This level represents the logical view of the database. It deals
with the overall organization of data in the database and the relationships between
them. It defines the data schema, which includes tables, attributes, and their
relationships. The conceptual level is independent of any specific DBMS and can be
implemented using different DBMSs.
External Level: This level represents the user’s view of the database. It deals with
how users access the data in the database. It allows users to view data in a way that
makes sense to them, without worrying about the underlying implementation details.
The external level provides a set of views or interfaces to the database, which are
tailored to meet the needs of specific user groups.
The three levels are connected through a schema mapping process that translates
data from one level to another. The schema mapping process ensures that changes
made at one level are reflected in the other levels.
In addition to these three levels, a DBMS also includes a Database Administrator
(DBA) component, which is responsible for managing the database system. The
DBA is responsible for tasks such as database design, security management, backup
and recovery, and performance tuning.
Overall, the structure of a DBMS is designed to provide a high level of abstraction
to users, while still allowing low-level implementation details to be managed
effectively. This allows users to focus on the logical organization of data in the
database, without worrying about the physical storage or implementation details.

The DBA is responsible for following critical tasks:

- Design of the Conceptual and Physical Schemas:
- Security and Authorization:
- Data Availability and Recovery from Failures:
- Database Tuning (Database tuning describes a group of activities used to optimize and
homogenize the performance of a database.)

Database design:
Database design can be divided into six steps.
1) Requirement Analysis:
- We must find out what the users want from the database. Ie what data is to be
stored in the database, what applications must be built on top of it, and what
operations are most frequent and subject to performance requirements.
2) Conceptual database design:
- It describes the main data entities, attributes, relationships, and constraints of a
given problem. This step is often carried out using the ER model.
- The ER model is one of several high-level, or semantic, data models used in
database design. The goal is to create a simple description of the data that closely
matches how users and developers think of the data (and the people and processes
to be represented in the data)
3) Logical database design:
- To convert the conceptual representation to the logical structure of the database,
which includes designing the relations. Ie. the task in the logical design step is to
convert an ER schema into a relational database schema.
4) Schema refinement:
- Database design is to analyze the collection of relations in our relational database
schema to identify potential problems, and to refine it ie normalization.
5) Physical Database design
- It simply involves building indexes on some tables and clustering some tables, or
it may involve a substantial redesign of parts of the database schema obtained
from the earlier design steps. Ie how decide how the logical structure is to be
physically implemented (as base relations) in the Database Management System
(DBMS).
6) Application and Security Design:
- It includes Design methodologies like UML try to address the complete software
design and development cycle.
- We must identify the entities (e.g., users, user groups, departments) and processes
involved in the application.
- We must describe the role of each entity in every process that is reflected in some
application task, as part of a complete workflow for that task.
- For each role, we must identify the parts of the database that must be accessible
and the parts of the database that must not be accessible, and we must take steps
to ensure that these access rules are enforced.
ENTITIES, ATTRIBUTES, AND ENTITY SETs
- An entity is an ‘object’ in the ‘actual world’, and that can be identified from
the other objects. Here, how an entity is different from other entity? For this, we
need to know how an entity is expressed.

- Well, an entity in a database is represented as a set of attributes. Each entity

has a different set of values for some set of attributes and this is how an entity
seems to be distinct from other entities. In a relational model, an entity is
represented as a row, tuple or a record.

- For example, consider a group of students is there, each student in the group is a
separate entity. Now, how we can identify a particular student in that group?
Simple, each student will have a different name, roll number, or course etc.

- If you call for a student among a group no one will come until you specify
either his name or another distinguishable attribute.

- An entity may be concrete like a student, a book, or abstract like a holiday or a

particular concept, job, bank account.
- An entity is represented by a set of attributes.

Entity Set :
- An entity set in DBMS is a collection of similar entities.
- Example 1, the entity set of Article Writer (all content creators for
stackoverflow) and the entity set of Article Reader (all students who read the
article of stackoverflow) may have members in common.
- Example 2, the entity set employee (all employees of a bank) and the entity
set customer (all customers of the bank) may have members in common.
- Example 3, the collection of toy department employees and the collection of
appliance department employees may both contain employee John Doe (who
happens to work in both departments). We could also define an entity set called
Employees that contains both the toy and appliance department employee sets.

- So, the collection of all the entities in the relation of RDBMS is called an entity
set.
- An entity set is represented by a rectangle, and an attribute is represented by an
oval.

Relationship and Relationships Sets:

- A relationship is an association among two or more entities.
- A diamond-shaped box represents relationships. All the entities (rectangle-
shaped) participating in a relationship get connected using a line.
- A relationship can also have descriptive attributes. Descriptive attributes are
used to record information about the relationship.

- Example:
- LAD ie last access date is descriptive/relationship attribute. A relationship can
have zero or more attributes.
Example:
- Suppose that each department has offices in several locations and we want to
record the locations at which each employee works. This relationship is ternary
because we must record an association between an employee, a department, and
a location.
- This relationship is ternary because we must record an association between an
employee, a department, and a location. When there are exactly three entity sets
participating in a relationship then such type of relationship is called ternary
relationship.
ADDITIONAL FEATURES OF THE ER MODEL
Key constraint
Consider the Works_In relationship. An employee can work in several departments,
and a department can have several employees.
Now consider another relationship set called Manages between the Employees and
Departments entity sets such that each department has at most one manager,
although a single employee is allowed to manage more than one department. The
restriction that each department has at most one manager is an example of a key
constraint.
This restriction is indicated in the ER diagram by using an arrow from Departments
to Manages. Intuitively, the arrow states that given a Departments entity, we can
uniquely determine the Manages relationship in which it appears.
If we add the restriction that each employee can manage at most one department to
the Manages relationship set, which would be indicated by adding an arrow from
Employees to Manages, we have a one-to-one relationship set.
 Participation constraints:
- Every department is required to have a manager. This requirement is an
example of a participation constraint; the participation of the entity set
Departments in the relationship set Manages is said to be total. A participation
that is not total is said to be partial.
- As an example, the participation of the entity set Employees in Manages is
partial, since not every employee gets to manage a department.
- Revisiting the Works..ln relationship set, it is natural to expect that each
employee works in at least one department and that each department has at least
one employee. This means that the participation of both Employees and
Departments in Works..ln is total.
- If the participation of an entity set in a relationship set is total, the two are
connected by a thick line.

In ternary relationship, Each employee works in at most one department and at a

single location. An instance of the Works_In relationship set.
Note that each department can be associated with several employees and locations
and each location can be associated with several departments and employees;
however, each employee is associated with a single department and location.
Weak Entities
- A weak entity is an entity set that does not have sufficient attributes for Unique
Identification of its records.
Example:
- Suppose that employees can purchase insurance policies to cover their
dependents. "we wish to record information about policies, including who is
covered by each policy, but this information is really our only interest in the
dependents of an employee.
- If an employee quits, any policy owned by the employee is terminated and we
want to delete all the relevant policy and dependent information from the
database.
- Dependents is an example of a weak entity set.
- A double rectangle is used for representing a weak entity set
- The double diamond symbol is used for representing the relationship between
a strong entity and a weak entity which is known as identifying relationship.

Example:

- A loan entity can not be created for a customer if the customer doesn’t exist.

Example

A dependents list entity cannot be created if the employee doesn’t exist

 Partial Key:

The set of attributes that are used to uniquely identify a weak entity set is called
the Partial key. The partial Key of the weak entity set is also known as a
discriminator.

Class hierarchies
It breaks an entity into multiple entities from higher level (super class) to lower
level (subclass).
Class hierarchy can be viewed one of two ways
 Specialization (Top Down Approach)
 Generalization (Bottom Up Approach)
Specialization
Specialization is a process of identifying subsets of an entity that shares different
characteristics. It breaks an entity into multiple entities from higher level (super
class) to lower level (subclass).

 Specialization is a top-down approach. In specialization, one higher level entity

can be broken down into two lower level entities.
 Specialization is used to identify the subset of an entity set that shares some
distinguishing characteristics.
 Normally, the superclass is defined first, the subclass and its related attributes
are defined next, and relationship set are then added.
 Example:
In an Employee management system, EMPLOYEE entity can be specialized as
TESTER or DEVELOPER based on what role they play in the company.

Example:
The class vehicle can be specialized into Car, Truck and Motorcycle (Top Down
Approach)
Hence, vehicle is the superclass and Car, Truck, Motorcycle are subclasses. All
three of these inherit attributes from vehicle.
 Generalization
Generalization is a process of generalizing an entity which contains generalized
attributes or properties of generalized entities. The entity that is created will
contain the common features. Generalization is a Bottom up process.

 Generalization is like a bottom-up approach in which two or more entities of

lower level combine to form a higher level entity if they have some attributes
in common.
 In generalization, an entity of a higher level can also combine with the entities
of the lower level to form a further higher level entity.
 Generalization is more like subclass and superclass system, but the only
difference is the approach. Generalization uses the bottom-up approach.
 In generalization, entities are combined to form a more generalized entity, i.e.,
subclasses are combined to make a superclass.
Examples:
 A Car, Bike, and Truck – all three are certainly different types of vehicles but
can be generalized into one single entity as Vehicle.
 A Developer and a Tester, both are different sub-entities having some special
attributes which can be clubbed into single-general super-entity Employee.
 A part-time and full-time employee in an organization can be generalized as an
Employee itself where part-time and full-time employees are sub-entities and an
Employee is a parent entity.
 In a bank there are two different types of accounts – Current and Savings,
combine to form a super entity Account.
 With emerging technologies, we have moved from using conventional phones
or telephones to using mobile phones, which can be considered as sub-entities
that can be generalized to a superclass Phone entity.
Example:

Faculty and Student entities can be generalized and create a higher level entity
Person.