DBMS(19CS3504)
By:-Prof.-Vaidehi Verma
Introduction: Purpose of Database System-–Views of data–data models, database
management system, three-schema architecture of DBMS, components of DBMS. E/R
Model - Conceptual data modelling - motivation, entities, entity types, attributes,
relationships, relationship types, E/R diagram notation, examples.
What is Data and Information
Data: Raw or unprocessed material
Information: knowledge, intelligence, a particular piece of data with a special
meaning or function.
Information is often the result of combining, comparing, analyzing or
performing calculations on data.
Think about test scores, for example.
In one class, if every student receives a numbered score, the scores can be
calculated to determine a class average. The class averages can be calculated
to determine the school average.
Data: Each student’s test score is one piece of data.
Information: The class’s average score or the school’s average score.
Data: telephone directory (names, addresses, and phone numbers)
Information: names and phone numbers of florists in your neighborhood - a
useful subset of the data
Data: details collected on a government population census survey
Information: totals of people in the country grouped by age, occupation,
state, or district they live in; useful summary calculations from the data
Database
A database is a centralized and structured set of data stored on a computer
system.
It provides facilities for retrieving, adding, modifying, and deleting the data
when required.
It also provides facilities for transforming retrieved data into useful
information.
A database is usually managed by a Database Administrator (DBA)
An Example –
University Database: Data about students, faculty, courses, research-
laboratories, course registration/enrolment etc.
Reflects the state of affairs of the academic aspects of the university
Purpose: To keep an accurate track of the academic activities of the
university.
Database Management System (DBMS)
Database management system is a collection of programs that enables us to
create and maintain a database.
It is a general-purpose software system that facilitates the process of
defining, constructing and manipulating databases for various applications.
Database Management Systems allows users to do the following:
Define Data – Allows the users to create, modify and delete the definitions
which define the organization of the database.
Update Data – Provides access to the users to insert, modify and delete data
from the database.
Retrieve Data – Allows the users to retrieve data from the database based on
the requirement.
Administration of users – Registers the users and monitors their action,
enforces data security, maintains data integrity, monitors performance and
deals with concurrency control.
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
Purpose of Database
Drawbacks of using file systems to store data:
Data redundancy and inconsistency
Multiple file formats, duplication of information in different
Difficulty in accessing data
Need to write a new program to carry out each new
Data isolation
Purpose of Database
Integrity problems
Hard to add new constraints or change existing ones
Atomicity of updates
Concurrent access by multiple users
Security problems
Database systems offer solutions to all the above problems.
A general-purpose software system enabling:
Creation of large disk-resident databases.
Posing of data retrieval queries in a standard manner.
Retrieval of query results efficiently.
Concurrent use of the system by a large number of users in a consistent
manner.
Guaranteed availability of data irrespective of system failures.
Schemas
A database schema defines its entities and the relationship among them.
It gives us an overall description of the database.
A database schema defines how the data is organized using the schema diagram.
schema- name-university
View of Data
Physical Level Schema
Describes the physical structure of data in terms of record formats, file structures,
indexes etc.
Logical Level Schema
Describes the logical structure of the entire database.
No physical level details are given.
View Level Schema
Each view describes an aspect of the database relevant to a particular group of users.
For instance, in the context of a library database:
Books Purchase Section
Issue/Returns Management Section
Users Management Section
custID Loan No Amout
101 566788 50000
The three level of DBMS architecture is depicted.
• The External view is how the Customer, Jack views it.
• The Conceptual view is how the DBA (Database
Administrator) views it.
• The Internal view is how the data is actually stored
Data Model
Collection of conceptual tools to describe the database at a certain level of
abstraction.
Conceptual Data Model
Representational Data Model
Physical Data Model
Conceptual data model
Conceptual data model, describes the database at a very high level and is
useful to understand the needs or requirements of the database.
E/R (Entity/Relationship) Model
The University Database Context
Entities: student, faculty member, course, departments etc.
Relationships: enrollment relationship between student & course, employment
relationship between faculty member, department etc.
Attributes: name, RollNumber, address etc., of student entity, name, empNumber,
phoneNumber etc., of faculty entity etc.
Representational Level Data Model
Relational Model: Provides the concept of a relation.
In the context of university database:
1.Entity Identification-student,professor,exam
2.Relationship
3.cardinality
4.Identify attribute
E-R Model
In this database model, relationships are created by dividing object of interest
into entity and its characteristics into attributes.
Entity
Attributes
ER Models
An entity-relationship (ER) model is a tool for database design.
Entities
Entities: the “Things”
• Represented using rectangles.
• Examples:
Types of Entity
Strong Entity-The strong entity has a primary key
Weak Entity-The weak entity in DBMS do not have a primary key and are
dependent on the parent entity. It mainly depends on other entities.
Example
University
Want to store data about:
Employees
Students
Courses
Departments
Attributes
Associated with entities are attributes that describe them. (Attributes – set of properties
or characteristics of an entity).
Types of Attributes
Derived attribute
age
degree
Working emp jod
year
Id salary
Name
dob
Street no House no Near
landmark
Address
pincode
Primary key
Relationships Between Entities
Relationships between entities are represented using diamonds that are connected to the
relevant entity sets.
• For example: students are enrolled in courses
• Another example: courses meet in rooms
Cardinality Constraints
The presence or absence of cardinality constraints divides relationships into three types:
• many-to-one (M:1)
• one-to-many (1:N)
• one-to-one (1:1)
• many-to-many (M:N)
Translating relationship into table (many
to many Relationship)
• Create a table for the relationship set.
• Add all primary keys of the participating entity sets as fields of the table.
• Add a field for each attribute of the relationship.
• Declare a primary key using all key fields from the entity sets.
• Declare foreign key constraints for all these fields from the entity sets.
DBMS Architecture
Components of DBMS
Query Processor
Storage manager
Database Users
Database Administrator
