UNIT-1

CONTENT
PART-1 PART-2
Database System Applications Introduction to Database Design
File Systems versus a DBMS
Database Design and ER Diagrams,
the Data Model
Entities,
Levels of Abstraction in a DBMS
Data Independence Attributes, and Entity Sets,
Structure of a DBMS Relationships and Relationship Sets,
Advantages of DBMS Additional Features of the ER Model,
Conceptual Design With the ER Model
TEXT BOOKS:
1.Database Management Systems, Raghurama Krishnan, Johannes
Gehrke, Tata McGrawHill, 3rd Edition(UNITS-I,ІI,ІІІ).
2. Database System Concepts, Silberschatz, Korth, McGrawHill, (UNITS-
I,IV,V)
REFERENCES:
1. Database Systems design, Implementation and Management, Peter
Rob & Carlos Coronel 7th Edition.
2. Fundamentals of Database Systems, Elmasri Navrate, Pearson
Education
3. Introduction to Database Systems, C. J. Date, Pearson Education
4. Oracle for Professionals, The X Team, S.Shah and V.Shah, SPD.
 Introduction
Data : Raw Facts/Details

Database: It is collection of interrelated data(contains information relevant to an

enterprise).
It is most common way to store and manage data used by applications.

Example:
University: Faculty, Student, Course, Classrooms
Airlines: Reservations, Schedules
 A database-management system (DBMS) is a collection of interrelated data and a set of
programs to access those data.
 It is an interface between an end user and a database, allowing users to create, read,
update and delete data from the database in flexible way
 The primary goal of a DBMS is to provide a way to store and retrieve database
information that is both convenient and efficient.
 Database systems are designed to manage large bodies of information.
 Management of data involves both defining structures for storage of information and
providing mechanisms for the manipulation of information.
 It must ensure the safety of the information stored, despite system crashes or attempts at
unauthorized access.
 If data are to be shared among several users, the system must avoid possible anomalous
results as Information is so important in most organizations.
Data Base Applications
◦ Banking: all transactions like customer information, transaction, account
statements
◦ 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
DBMS Software(tool):
MS Access, Oracle, SQL Server, Mysql DB2, SYBASE, Mongo DB etc
Languages used by DBMS Software:
SQL, PL/SQL, MYSQL etc
 The Data Model
 A data model is a conceptual representation of how data is organized, stored,
and accessed in a database system.
 It defines the structure of the data, the relationships between different data

elements, and the constraints that govern the data.

 Data models serve as blueprints for designing databases and help ensure data

integrity and consistency.

 Types of Data Model
The data models can be classified into different categories:
 Hierarchical Model
 Network Model
 Entity-Relationship Model
 Relational Model
 Object-Based Data Model
 Semi-structured Data model
 Hierarchical Model
 Hierarchical Model was the first DBMS model. This model organizes the data in
the hierarchical tree structure.
 It 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.
 Depicts a set of one-to-many (1:M) relationships

Disadvantages
 Easy to design but it is quite complex to implement.
 Lacks of flexibility as the changes in the new tables.
 A deletion of one segment can lead to the Involuntary deletion of all segments

under it.
 Network Model
 It is an extension of the hierarchical model, a record can have more than one parent.
It replaces the hierarchical tree with a graph.
 It represent complex data relationships more effectively when compared to

hierarchical models, to improve database performance and standards.

 Depicts both one-to-many (1:M) and many-to-many (M:N) relationships.

Disadvantages
 Complex array of pointers.
 Structural changes require changes

in all application programs.

 Keep heavy pressure on programmers due

to the complex structure.

 Any change like updating, deletion,

insertion is very complex.

 Entity-Relationship Model
 An ER model is the logical representation of data as objects and relationships among them.
Entity-Relationship Model Components
 Entities − It is a real-world thing

 Attributes − An entity which contains a real-world property called an attribute.

 Relationship − It tells how two attributes are related.

Advantages
 Easy to build and widely used by database designers for communicating their ideas.

 Easily convert to any other model like network model, hierarchical model etc.

 It is integrated with the dominant relational model.

Disadvantages of Entity-Relationship Model

 Information might be lost or hidden in the ER model.

 There is no Data Manipulation

 There is limited relationship representation.

 Relational Model
 It uses a collection of tables to represent both data and the relationships.
 Tables are also known as relations.
 Each table has multiple columns represent as attributes,
 Attributes are the properties which define a relation. Each row of the table

represents as Tuple, Tuple is one piece of information.

Terminologies used
 Tables, Attribute, Tuple, Relation Schema, Degree, Cardinality, Column,

Relation instance:
 Relational Model
Advantages
 Structural Independence: Structural independence is an ability that allows us to

make changes in one database structure without affecting other.

 Conceptual Simplicity: Relational models are conceptually simple to understand.

 Query Capability: Using simple query language (such as SQL) user can get

information from the database or designer can manipulate the database structure.
 Easy design, maintenance and usage: The relational models can be designed

logically hence they are easy to maintain and use.

Disadvantages
 Relational model requires powerful hardware and large data storage devices.

 May lead to slower processing time.

 Poorly designed systems lead to poor implementation of database systems.

 Object-Based Data Model
 It is combination of Object Oriented programming and Relational Database Model.
Components of Object- Based Data Model
 Objects: An object is an abstraction of a real world entity
 Attribute: An attribute describes the properties of object.
 Methods: It represents the behavior of an object.
 Class: It is a collection of similar objects with shared structure i.e. attributes and

behavior.
 Inheritance: new classes are created from the existing classes

Advantages Disadvantages
 Reduced Maintenance
• It is a complex navigational system.
 Real-World Modeling
• Slow development of standards.
 Improved Reliability and Flexibility
• High system overheads.
 High Code Reusability
• Slow transactions.
Object-Based Data Model
Semi-structured Data model
 It permits the specification of data where individual data items of
same type may have different sets of attributes. (JSON)
 The Extensible Markup Language (XML) is widely used to

represent semi-structured data model.

Advantages
 Data is not constrained by fixed schema.
 It is flexible.
 It is portable.

Disadvantage
 Queries are less efficient than

other types of data model.

 Advantages of DBMS
 Controlling Redundancy: In DBMS all the data stored in only one place ensuring
consistency.
 Data Independence: The DBMS provides an abstract view of the data that hides data
representation and storage details.
 Efficient Data Access: A DBMS utilizes a variety of sophisticated techniques to store
and retrieve data efficiently.
 Data Integrity and Security: If data is always accessed through the DBMS, the
DBMS can enforce integrity constraints.
 Example, before inserting salary information for an employee, the DBMS can check
that the department budget is not exceeded. Also, it can enforce access controls that
govern what data is visible to different classes of users.
 Advantages of DBMS
 Data Administration: When several users share the data, centralizing the
administration of data can offer significant improvements.
 Concurrent Access and Crash Recovery: A DBMS schedules concurrent accesses

to the data in such a manner that users can think of the data as being accessed by
only one user at a time. Further, the DBMS protects users from the effects of
system failures.
 Reduced Application Development Time: The high-level interface to the data,

facilitates quick application development.

 Schema
It refers to the logical structure or blueprint that defines the organization, arrangement, and
relationships of data elements within a database.
Example:
Book Table:
| book_id (PK) | title | author_id (FK) | publication_year | isbn |
|--------------|---------------|----------------|----------------
The database schema provides a structured representation of the data model, defining the
entities, attributes, and relationships that make up the database.
Types of schema
Physical Schema: Defines how data is physically stored on the storage media, such as hard
disks or solid-state drives. – data storage formats, file organization, indexing methods.
Logical Schema: Defines the logical structure of the database, including tables, columns,
keys, constraints, and relationships between entities.
 Instance
It refers to a specific occurrence or snapshot of the data stored in a database at a
particular point in time.
It represents the actual data values stored in the database
Example:

Instance of relation is :
Levels of Abstraction in a DBMS or Three Schema Architecture
 “levels of abstraction” refers to layers of understanding a system or concept, each
hiding unnecessary details while emphasizing essential features.
 The database description consists of a schema at each of these three levels of

abstraction: the conceptual, physical, and external.

Physical level:
The lowest level of abstraction describes how
the data are actually stored.
Logical level or conceptual schema:
Describes what data are stored in the database,
and what relationships exist among those data.
External Schema
The view level of abstraction exists to simplify
their interaction with the system.
 Example: University Database
• External Schema(view level):
• Course info(cid, enrollment)
• Conceptual Schema:
• Students(sid, name, age, gpa)
• Courses(cid, cname, credits)
• Enrolled(sid,cid,grade)
• Physical Schema:
• Relation stored as files
• Index of first column of
students
Data Independence
 The ability to modify the schema in one level without affecting
the schema in next higher level is called data independence.

 Logical data independence: The ability to modify the logical

schema without affecting the schema in next higher level
( external schema.)

 Physical Data Independence – The ability to modify the physical

schema without changing the logical schema
 Structure of DBMS
DBMS is software that allows access to data stored in a database and provides an
easy and effective method of
 Defining the information.
 Storing the information.
 Manipulating the information.
 Protecting the information from system crashes or data theft.
 Differentiating access permissions for different users.

DBMS is crucial because they relieve applications and end users of the need to
comprehend the semantic structures and the physical location of the data.
Structure of
DBMS

Three components:
 Query Processor
 Storage Manager
 Disk Storage
Structure of DBMS
Database Users
There are four different types of database-system users, differentiated by the way they
expect to interact with the system.
 Naive users are unsophisticated users who interact with the system by invoking one of

the application programs that have been written previously.

Example: a clerk in the university who needs to add a new instructor
to department A invokes a program called new_hire.
◦ Naive users may also simply read reports generated from the database.
 Application programmers - Computer professionals who write application programs,can

choose from many tools to develop user interfaces.

 Sophisticated users - Interact with the system without writing programs. Instead, they

form their requests either using a database query language or by using tools such as data
analysis software. Analysts who submit queries to explore data in the database fall in
this category.
Structure of DBMS
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.

Components of Query Processor:

 DDL Interpreter- It handles the schema definition i.e., storing metada into data

dictionaries
 DML Compiler- Converts DML statements like select, update, and delete into

low-level instructions or simply machine-readable object code, to enable

execution.
 Query Evaluation Engine - Evaluates the SQL, commands used to access the

database's contents before returning the result of the query.

Structure of DBMS
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.
Components of Storage Manager
File Manager: It manages the file space and the data structure used to represent
information in the database(os).
Buffer Manager: It is responsible for cache memory and the transfer of data between the
secondary storage and main memory
Authorization and Integrity Manager: It checks whether the particular person is privileged
to perform the requested operation or not. It checks the integrity constraints when the
database is modified.
Transaction Manager: Handles all the transactions ensuring data consistency. It controls
concurrent access by performing the operations in a scheduled and controlled way.
Structure of DBMS
 Disk Storage: A DBMS can use various kinds of Data Structures as a part of
physical system implementation in the form of 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: These indices are used to access and retrieve the data in a very fast and

efficient (key values)

Introduction to Database Design
 Database design is the process of creating a structured representation of data to
efficiently store, organize, and manage information within a database system.
 It involves into several key steps

◦ Requirement analysis - what data to be stored in the database

what applications should be built on top of it
◦ Conceptual database design – Based on information gathered, develop a high level description
of data to be stored in database using constraints
◦ Logical database design – Choose an a DBMS to implement our database design from
conceptual to database schema
◦ Schema refinement – To analyze the collection of relations, finds the problems and refine it
◦ Physical database design – Selects storage structures, indexing strategies and partitioning
schemes.
◦ Application and security design – Developing an application and implementing the security
like authorization, encryption, data integrity etc
Database Design
 The primary goal of conceptual database design is to
provide a clear and intuitive representation of the data
requirements of an organization or application
 Conceptual database design involves creating a high-level

conceptual model of the database schema, focusing on

understanding and representing the essential entities,
attributes, and relationships between data elements.
Entity-Relationship Model
It represents the logical structure of the database graphically and explains the
relationship among the entities used to model the real world objects more
precisely.
Components of ER Diagram
Symbols used in ER Diagram
Entity:
• Real-world object distinguishable from other objects.
• Entities represent the major data elements in the system, such as Student, Course, Instructor, and
Registration.
• It is described (in DB) using a set of attributes.
Entity Set:
• A collection of similar entities. E.g., all employees.
• All entities in an entity set have the same set of attributes.
• Each entity set has a key.(minimal set of attributes whose values uniquely identify entity in set)
 Strong Entity Set :
It is an entity that has its own existence
and is independent

Weak Entity Set :

It is an entity that makes reliance over
another entity is called a weak entity.
It is represented with double rectangle in
ER Diagram.
Attribute :
• Describes the property of entity
• Representation of attribute is eclipse
• Example: id, name, age of student entity

Key Attribute: uniquely identifies the

entity from entity set Eg: st_id, emp_id
Composite Attribute: Composed of several
attributes Eg: first name, last name
Multivalued Attribute: An attribute can
contain multiple values. Eg: phone no
Derived Attribute: It can derived from
other attributes. Eg: age
Descriptive Attribute: describes the
relationship existence
Relationship
• It refers to the association between entities or tables within a database schema.
• Relationships are used to define how data in one table is related to data in another table.
Types of Relationships:
One-to-One (1:1) Relationship – Single entity is
associated with single entity
Eg: Student has only one identification card
One-to-Many (1:N) Relationship - Single entity is
associated with more than one element of another
entity
Eg: Customer can place many orders
Many-to-One (M:1) Relationship – More than one
element of an entity is related to a single element
Many-to-Many (M:N) Relationship - More than one
element of an entity is related to a more than one
element of another entity
Relationship Set
• An attribute can also be property of a relationship set.
• For instance, the depositor relationship set between entity sets customer and account may have the
attribute access-date
Additional Features of ER Model
Degree of Relationship
Degree refers to number of entity sets that participate in a relationship set.
Types of degree
Based on number of linked entity types, there are 4 types of degree relationships.

Unary relationship –In a relation only one entity set

is participating. Eg: in a students, we can also have
monitors
Binary relationship – when two entities are
participating with a relation. Eg: Teacher and
Subject
Degree of Relationship
Ternary relationship – three entities
are associated with relation
Eg: teacher, course, class
N-ary relationship – N- type of
entities are associated with relation
Eg: Teacher, Class, Location,
Salary, Course
Constraints on Relationships

Cardinality Ratio: It specifies the

maximum number of relationship
instance that an entity can participate
Participation Constraint: It specifies
the minimum number of relationship
instance that an entity can participate
Participation Constraints
 Define whether an entity must participate in a relationship or if its
participation is optional.
 It refer to rules that dictate the minimum and maximum participation of

entities in a relationship.
Two types of participation constraints
Total participation - every entity in one
entity set must participate in at least one
relationship instance in the relationship
set
Partial participation - participation in
the relationship is optional for the
entity.
EXAMPLE
Class Hierarchies
 Hierarchy are similar to inheritance
 It can be viewed in one of two ways

◦ Generalization
◦ Specialization

Generalization
◦ It 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.
◦ 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, ie, subclasses
are combined to make a superclass.
Specialization
 Specialization is a top-down approach, and it is opposite to Generalization.
 In specialization, one higher level entity can be broken down into two

lower level entities.

 It 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.
Aggregation
 In aggregation, the relation between two entities is treated as a single entity. i.e.

relationship with its corresponding entities is aggregated into a higher level

entity.
 For example: "Centre offers Course" act as a single entity in the relationship

which is in a relationship with another entity visitor.

 In the real world, if a visitor visits a coaching centre then he will never enquiry

about the Course only or just about the Centre instead he will ask the enquiry
about both.
Steps in Designing an Entity-Relationship Schema
[Step 1] - Identify entity types (entity type vs. attribute)
[Step 2] - Identify relationship types
[Step 3] - Identify and associate attributes with entity and
relationship types
[Step 4] - Determine attribute domains
[Step 5] - Determine primary key attributes for entity types
[Step6] - Associate (refined) cardinality ratio(s) with relationship
types
[Step7] - Design generalization/specialization hierarchies
including constraints (includes natural language statements as
well)
Construct an E-R diagram for a car insurance company whose customers own one
or more cars each. Each car has associated with it zero to any number of recorded
accidents. Each insurance policy covers one or more cars, and has one or more
premium payments associated with it. Each payment is for a particular period of
time, and has an associated due date, and the date when the payment was received
Construct an E-R diagram for a hospital with a set of patients and a set
of medical doctors. Associate with each patient a log of the various tests
and examinations conducted.
Consider the E-R diagram, which models an online bookstore.
a. List the entity sets and their primary keys.
b. Suppose the bookstore adds Blu-ray discs and downloadable video to its collection. The same item may be
present in one or both formats, with differing prices. Extend the E-R diagram to model this addition, ignoring
the effect on shopping baskets.
c. Now extend the E-R diagram, using generalization, to model the case where a shopping basket may contain
any combination of books, Blu-ray discs, or downloadable video.
Conceptual Design With the ER
 Conceptual Design Using the ER
Model
• Design choices:
– Should a concept be modeled as an entity or an attribute?
– Should a concept be modeled as an entity or a
relationship?
– What are the relationship sets and their participating entity sets?
– Should we use binary or ternary relationships?
– Shoud we use Aggregation?
• Constraints in the ER Model:
– A lot of data semantics can (and should) be captured.
– But some constraints cannot be captured in ER diagrams.

64
 Entity vs.
Attribute
• Should address be an attribute of Employees or an
entity (connected to Employees by a relationship)?
• Depends upon the use we want to make of address
information, and the semantics of the data:
• If we have several addresses per employee,
address must be an entity (since attributes
cannot be set-valued).
• If the structure (city, street, etc.) is important, e.g.,
we want to retrieve employees in a given city,
address must be modeled as an entity (since
attribute values are atomic). 65
Entity vs Attribute
 Entity vs.
Relationship
• Suppose that each
department manager is
given a discretionary
budget since dbudget
name dname
• What if a manager ssn lot did budget
manages more than one
department and Employees Departments
Manages2
discretionary
budget is a sum of all
Departments managed?
– Redundancy: dbudget
stored for each dept
managed by manager.
– Misleading: Suggests
dbudget associated
with department- mgr
combination.

67
 Aggregation v/s ternary relationship
• The choice between using aggregation or name
ssn lot
ternary relationship is mainly determined
by existence of a relationship that relates
Employees
relationship set to entity set.

Monitors until

started_on
since dname
pid
pbudget did
budget

Projects Departments
Sponsors

 The choice may also be guided by certain integrity constraints

that we want to express.

68
 Conceptual design for large
• For large enterprises
enterprise the design may require efforts ofmore
than one designer and span data and application code used
by number of user groups.
• ER diagrams for Conceptual design offers additional
advantage that high level design can be diagramatically
represented and easily understood by many people.
2 approaches:
• Usual approach: requirements of various user groups are
considered,any confl icting requirements are somehow
resolved and single set of global requirements is generated at
the end of requirements phase
• Alternative approach: is to develop separate conceptual
schemas for different user groups and then integrate these
conceptual schemas

69