Database System Concept

What is Data?

• The fact and figures that can be recorded in system and that have
some special meaning is called data.

• Ex:Data of customer like name ,telephonr no, address etc.

What is Database:

• An organized collection of data and information or interrelated data

collected at one place.

• Ex:Student Table:
DBMS:

• A database Management System is an collection of software or

program which help user in creation and maintenance of a database.

• Ex:

• MySQL

• Oracle
Characteristics/Features of DBMS:

1.Data Integrity:Changes made to the database by authorizes user do

not result in a loss of data consistency and correctness.

• Ex:Student taking admission to branch which is not available in

college.
2.Data Security:

• Data in database should be given to only authorized users.

• Only authorized users should be allow to modify data.

• Authorized users are able to access data any time.

3.Data Independene:

• Data Independence can be defined as the capacity to change data at

one place without changing data kept at other locations.
4-Transaction control-rollback:

• The changes made in database can be undo with help of rollback

command.

• The changes can be saved successfully with help of commit data

command.
5. Concurrency Control:
• The data in database can be accessed by multiple user at same point
of time.
6.Data recovery backup and restore:

• Database recovery is the process of restoring the database to original

state after database failure.
Disadvantages of File Processing System:
1.Data redundancy:The same data can be present in two or more files which takes up more disc space .

2. Inconsistent Data :Due to data redundancy, same data stored at different places might not match to
each other.

3. Data Integrity Problems :The data present in the database should be consistent and correct. To
achieve this, the data should must satisfy certain constraints.
4.Difficulty in recovery of corrupt data :
Recovery or backup of lost and corrupt data is nearly impossible in case of File Processing System.

5.Unauthorized Access – Anyone who gets access to the file can read or modify the data.
Advantages of DBMS:
1. Reduction in redundancy: All the data is stored in one place, and that recorded in the database

and hence controls the redundancy in the database.

2. Avoiding Inconsistency:As the redundancy is reduced inconsistency is avoided.

3. Easy Maintenance: The centralized nature of the database helps in the easy maintenance of the

data.

4. Reduce time: It reduces the maintenance need and development time.

5. Backup: It automatically backs up data to maintain its integrity in case of failure.

6. Multiple user interfaces: It offers a number of user interface to multiple users.

File Processing System vs DBMS:
File Processing System DBMS

Redundant data can be present in a file system. In DBMS there is no redundant data.

It doesn’t provide backup and recovery of data It provides backup and recovery of data even if
if it is lost. it is lost.

It has more complexity in handling as compared

It is less complex as compared to DBMS.
to the file system.

File systems provide less security in DBMS has more security mechanisms as
comparison to DBMS. compared to file systems.

It has a comparatively higher cost than a file

It is less expensive than DBMS.
system.

There is no data independence. In DBMS data independence exists.

Only one user can access data at a time. Multiple users can access data at a time.
Application of DBMS:
1. Railway And Airline Reservation System:

• The database keeps a record of ticket booking, arrival time, departure time, delays, and even seat numbers of
airplanes or trains.

2. Banking:

• An uncountable number of bank transactions and deposits are made, thousands of new bank accounts are
created daily, and many such things.

3. Education Sector (Schools and Colleges):

• The details of each student, teacher, and even janitor are stored in a database to manage the information
easily.
Application of DBMS:

4.Social Media Sites (Instagram, Facebook, etc.)

• Social media websites like Instagram, Facebook, Whatsapp, etc., are able to
keep track of each and every detail of their users (name, phone number, photos,
email address, password, etc.) with the help of Database Management Systems.

5. Online Shopping (E-commerce Platforms Like Amazon):

All the details of the customers, products as well as order and tracking details are
stored and managed using a Database Management System.
Data Abstraction:
• It is the process of hiding the complexity of data and representing the
data which need to be shown to the
user.
1. Physical or Internal Level:

• How the data are stored.

• Lowest level of abstraction.

• Complex low-level structures described in detail.

2.Logical/Conceptual Level:

• Next highest level of abstraction.

• Describe what data are stored.

• Describes what relationship among data.

3.View Level:
• Highest level.

• Describe part of the database for a particular group of users.

• Complexity of physical as well as logical level is hidden from the user.

Instance:

• The data in the database at a particular moment of time is called an

“instance”or a database state.

• Every time we update(i.e.insert,delete or modify)the value of a data

item in record,that time one state of database changes into another
state.
Schema:
• The overall design of the database is called database schema.
• It does not show the data in the database.
• It is the definition of the database.
• Example” We take two tables emp table and dept table.
• Emp Dept
• id Dept_id
Name
Salary dname

dept
Types of schema:
1.Physical Database Schema:

• A Physical schema defines, how the data or information is stored physically in the storage systems in the
form of files & indices.

• It define how the data will be stored in a secondary storage.

2.Logical Database Schema:

• A logical database schema defines all the logical constraints that need to be applied to the stored data,
and also describes tables, views, entity relationships, and integrity constraints.

• The Logical schema describes how the data is stored in the form of tables & how the attributes of a table
are connected.
Types of schema:
3.View Database Schema:
It is a view level design which is able to define the interaction between
end-user and database.
Data Independence:

• It is ability to modify a schema in one level without affecting schema

in another level.

• There are two levels of data independence in DBMS:

1.Physical data independence

2.Logical data independence

1.Physical data independence

• Physical Data Independence can be defined as the ability to change

the physical level without affecting the logical or Conceptual level.

• Physical data independence gives us the freedom to modify the -

Storage device, File structure, location of the database, etc. without
changing the definition of conceptual or view level.
2. Logical Data Independence:

• These changes can be done at a logical level without affecting the

application program or external layer.

• Adding, deleting, or modifying the entity or relationship.

• Merging or breaking the record present in the database.

Database Users:
1. Application Programmers – They are responsible for developing
application programs or user interface.

2.Sophisticated Users – They are database developers, who write SQL

queries to select/insert/delete/update data. They do not use any
application or programs to request the database.
Database Users:

3. Specialized User: These are the users who write specialized applications.

4.Database Administrator: It is responsible for managing the whole database system.

• DBA is also responsible for providing security to the database and he allows only the
authorized users to access/modify the data base.

5.Database Designer: Data Base Designers are the users who design the structure of
database which includes tables, indexes, views, triggers, stored procedures and
constraints.
Roles of DBA:
• A Database Administrator is a person or a group of person who are responsible for managing all the
activities related to database system.

1. Database design:

• The logical design of the database is designed by the DBA. Also a DBA is responsible for physical
design, external model design, and integrity control.

2.Database accessibility:

• DBA writes subschema to decide the accessibility of database. He decides the users of the database
and also which data is to be used by which user.
Roles of DBA:
3.Managing Data Integrity:

• Data integrity should be managed accurately because it protects the data from
unauthorized use. DBA manages relationship between the data to maintain data
consistency.

4.Decides Data Recovery and Back up method:

DBA has to decide that how much data should be backed up and how frequently the back
should be taken.
Roles of DBA:

5. Monitoring performance :

• If database is working properly then it doesn’t mean that there is no task for the
DBA. Yes f course, he has to monitor the performance of the database
Components of DBMS & Overall
Structure of DBMS:
1.Query Processor

•Query Processor contains the following components –

1. DML Compiler: It processes the DML statements into low level
instruction (machine language), so that they can be executed.
2. DDL Interpreter: It processes the DDL statements into a set of table
containing meta data (data about data).
3. Embedded DML Pre-compiler: It processes DML statements embedded in
an application program into procedural calls.
4. Query Optimizer: It executes the instruction generated by DML Compiler.
2. Storage Manager:
1. Storage Manager is a program that provides an interface between the data
stored in the database and the queries received.

2. Authorization Manager: It ensures role-based access control, i.e,. checks

whether the particular person is privileged to perform the requested operation
or not.

3. Integrity Manager: It checks the integrity constraints when the database is

modified.
2. Storage Manager:
4. Transaction Manager: It ensures that the database remains in the
consistent state before and after the execution of a transaction.

5. File Manager: It manages the file space and the data structure used
to represent information in the database.

6. 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 –
1. Data Files: It stores the data.

2. Data Dictionary: It contains the information about the structure of

any database object.

3. Indices: It provides faster retrieval of data item.

DBMS Architecture:

• DBMS Architecture helps users to get their requests done while connecting to the
database.

• We choose database architecture depending on several factors like the size of the
database, number of users, and relationships between the users

• Hence it is important to select the correct architecture for efficient data management.

• Client : PC or workstation on which users run applications.

• Server:It is powerful computers dedicated to managing printers or network traffic.

Types of DBMS Architecture:

• There are several types of DBMS Architecture that we use according

to the usage requirements.
a) 1-Tier Architecture
b) 2-Tier Architecture
c) 3-Tier Architecture
1-Tier Architecture:

• In 1-Tier Architecture the database is directly available to the user, the user can
directly sit on the DBMS and use it that is, the client, server, and Database are all
present on the same machine.
2-Tier Architecture:

• The 2-tier architecture is similar to a basic client-server model.

• On the client side, the user interfaces and application programs are run.

• The server side is responsible for providing query processing and transaction
management functionalities.
2-Tier Architecture:

• Advantages of 2-Tier Architecture:

• Understanding and maintenance is easier.

• Dis-Advantages of 2-Tier Architecture:

• Performance will be reduces when there are more users.
3-Tier Architecture:

• In 3-Tier Architecture, the communication take place from client to

application server and then application server to database system to access
the data.

• The application layer is sometimes called “middle layer”.

• The middle level which processes applications and databse server process the
queries.

• This type of communication system is used in the large application .

3-Tier Architecture:
3-Tier Architecture:

• Advantages:
• Easy to modify without affecting other modules.
• Fast communication.
Data Models in DBMS:
• Data models are used to describe the structure of a database.
• A set of symbols and text is used to represent them so that all the
members of an organization can understand how the data is
organized.
Types of Data Models in DBMS:
1. Hierarchical Model.
2. Network Model
3. Relational Model
4. Entity-Relationship Model (ER Model)
1. Hierarchical Model:

• In this data model, the data is organized in a hierarchical tree-like structure.

• In this model, a child node will only have a single parent node.

• Data is organized into a tree-like structure with a one-to-many

relationship between two different types of data, for example,
one department can have many courses, many teachers, and of course
many students(like shown in the diagram below).
1. Hierarchical Model:
2.Network Model:

• The Network Model is an extension of the Hierarchical model.

• In this model, data is organized more like a graph, and allowed to have more than one parent

node.

• In the network database model, data is more related as more relationships are established in

this database model.

• Also, as the data is more related, hence accessing the data is also easier and fast.

• This database model uses many-to-many data relationships.

2.Network Model:
3.Relational Model :

• In this database model, data is organized in two-dimension tables called relations.

• Different entities are related using relationships.

• You can design tables, normalize them to reduce data redundancy, and use
Structured Query language or SQL to access data from the tables.

• Some of the most popular databases are based on this database model. For
example, Oracle, MySQL, etc.
3.Relational Model :
Compare Hierarchical and Network
Model:
Hierarchical Network
1.It is based on tree like structure with one root. 1.It is based on records and links.

2.Supports one to many relationships. 2.Supports many to many relationships.

3.Less Popular. 3.More Popular.

4.It does not uses client server architecture. 4.It uses client server architecture.

6.Uses pointers to relate data. 6.Uses links to relate data.

Compare Network and Relational Model:
Network Relational
1.It is based on records and links. 1.It is collection of rows and column.

2.Supports many to many relationships & one to many 2.Supports many to many relationships & one to one
relationship. relationship.

3.It is not much Popular. 3.It is much Popular.

4.There is partial data independence in this model. 4.This model provides data independence

5.Invented by Charles bachman. 5. Invented by E.F.Codd.

Compare Hierarchical and Network
Model:
Hierarchical Relational.
1.It is based on tree like structure with one root. 1.It is collection of rows and column.

2.Supports one to many relationships. 2.Supports many to many relationships & one to one
relationship.
3.Less Popular. 3.It is much Popular.

4.The main application of hierarchical data model is in 4.There are many application of relational model
the mainframe database system. which are unlimited.
ER-Model:
• ER Diagram is a visual representation of data that describes how data is related to
each other.

• Creation of ER diagram helps the designer to understand & specify the desired

components of database & relationships among those components.

Symbol and Notations of ER-Model:
Components of ER-Model:
1. Entity:

• An entity may be any object, class, person or place. In the ER diagram, an entity can be
represented as rectangles.

• Consider an organization as an example- manager, product, employee, department etc. can be

taken as an entity.
Components of ER-Model:
a) Weak Entity :

• Weak entity is an entity that depends on another entity. Weak entity doesn't have
anay key attribute of its own.

• Double rectangle is used to represent a weak entity.

b) Strong Entity :

An entity that have sufficient attributes to form a primary key is called as strong
entity set.
Components of ER-Model:
• Attributes: An Attribute describes a property or characterstic of an
entity.
• For example, Name, Age, Address etc can be attributes of a Student.
An attribute is represented using eclipse.
Components of ER-Model:
Types of Attributes:
1. Single-value attribute − Single-value attributes contain single value. For example − age of

employee.

2. Multi-value attribute − Multi-value attributes may contain more than one values. For example, a

person can have more than one phone number, email_address, etc.

3. Simple attribute − Simple attributes are atomic values, which cannot be divided further. For

example, a student's phone number is an atomic value of 10 digits.

Components of ER-Model:

3. Composite attribute − Composite attributes are made of more than one simple attribute. For

example, a student's complete name may have first_name and last_name.

4. Derived attribute − Derived attributes are the attributes that do not exist in the physical

database, but their values are derived from other attributes present in the database.

5. Key attribute : Key attribute represents the main characterstic of an Entity. It is used to

represent a Primary key. Ellipse with the text underlined, represents Key Attribute.
Components of ER-Model:
ER-Diagram for hospital management system :
ER-Diagram for Library management
system :
Relationship/Cardinality Constraints:
• Relationships are represented by diamond-shaped box. Name of the relationship
is written inside the diamond-box.

• All the entities (rectangles) participating in a relationship, are connected to it by a

line.

1. One-to-one − When only one instance of an entity is associated with the

relationship, it is marked as '1:1'. It depicts one-to-one relationship.
Relationship/Cardinality Constraints:

2. One-to-many − When more than one instance of an entity is

associated with a relationship, it is marked as '1:N'.
Relationship/Cardinality Constraints:

3.Many-to-one − When more than one instance of entity is associated

with the relationship, it is marked as 'N:1'. It depicts many-to-one
relationship.
Relationship/Cardinality Constraints:

4.Many-to-many − The following image reflects that more than one

instance of an entity on the left and more than one instance of an
entity on the right can be associated with the relationship.
It depicts many-to-many relationship.
Questions:
• Two Marks Question:
1. List disadvantages of typical file processing system.
2. Define i) data abstraction, ii) data redundancy.
3. State any two advantages of DBMS over file processing system
4. Draw three level architecture of DBMS.
5. Define :– i) Data Abstraction ii) Instance.
6. State any two advantages of DBMS.
7. Define data model. List its types.
8. State types of database user.
9. State the components used in E-R diagram
Questions:
Q. 4 and 6 Marks Question.
1. Explain advantages of DBMS over file processing system.
2. With neat diagram explain three level architecture of database
system.
3. Distinguish between network model and hierarchical model (any 4
points.
4. Explain the four roles of database administrator.
5. Draw an E-R diagram of library management system considering issue
and return, fine calculation facility, also show primary key, weak entity
and strong entity.
Questions:
6.Explain strong and weak entity set.
7.Draw an ER diagram for library management system. (Use Books,
Publisher & Member entities) .
8.Draw E-R diagram of Banking system considering deposite, withdrawl
facility. Also show primary key, weak entity, strong entity.
9. Draw the overall architecture of DBMS. Explain storage manager and
query processor components.