Ch1 - Database System Concept

Database Management System(22319)
Unit – 1
Database System
Concept
By
Mrs. K. G. Kurale
Database System Concepts - 6th Edition 1.1 ©Silberschatz, Korth and Sudarshan
Outline
 Concepts of database
 Advantages and disadvantages
 Applications
 Data abstractions
 Data independence
 Overall Structure of DBMS
 Data Modeling
Concept of database
 Introduction to database
1. Hardware
2. Software
 Types of files
1. Program files
2. Data files
 Data – collection of facts ,figures ,numbers.

 Operations – manipulation of data or processing of data.
 Information- processed data.
Database Management System (DBMS)
 DBMS- Database management system
 DBMS Definition 1
A database management system (DBMS) is Collection of interrelated data

and Set of programs to access the data .
 DBMS Definition 2
A database management system (DBMS) is system software for creating

and managing databases. A DBMS makes it possible for end users to
create, read, update and delete data in a database.
 Database Applications:
 Banking ,Airlines, Universities, Sales, Online retailers,
Manufacturing,Human resources
 Databases can be very large.
 Databases touch all aspects of our lives
University Database Example
 Application program examples
 Add new students, instructors, and courses
 Register students for courses, and generate class rosters
 Assign grades to students, compute grade point averages

(GPA) and generate transcripts
 In the early days, database applications were built directly on

top of file systems
Drawbacks of using file systems to store data
 Data sharing is difficult
 Data redundancy and inconsistency
 Multiple file formats, duplication of information in different files
 Difficulty in accessing data
 Do not provide transaction support
 Maintaining security is challenging
 Difficult to enforce standards
 Integrity problems
 Integrity constraints (e.g., account balance > 0)
 Hard to add new constraints or change existing one
Drawbacks of using file systems to store data (Cont.)
 Atomicity of updates
 Failures may leave database in an inconsistent state with partial
updates carried out
 Example: Transfer of funds from one account to another should either
complete or not happen at all
 Concurrent access by multiple users
 Concurrent access needed for performance
 Uncontrolled concurrent accesses can lead to inconsistencies
 Example: Two people reading a balance (say 100) and updating it
by withdrawing money (say 50 each) at the same time
 Security problems
 Hard to provide user access to some, but not all, data
Database systems offer solutions to all the above problems
Advantages of using DBMS
 sharing of data easy.
 Reeducation in Data redundancy
 Avoid inconsistency
 Easy access of data
 provide transaction support
 Maintaining security of data
 Easy to enforce standards
 Maintain Integrity constraints
 Integrity constraints (e.g., account balance > 0) can easily

applied
Applications of DBMS
 Banking: all transactions
 Airlines: reservations, schedules
 Universities: registration, grades
 Sales: customers, products, purchases
 Manufacturing: production, inventory, orders, supply chain
 Human resources: employee records, salaries, tax deductions
 Telecommunication: keeps track of calls made, network usage,
customer details
 Education sector: used in schools and colleges to store and retrieve
student details, staff details, course details, exam details, payroll
data, attendance details, fees details etc.
 Online shopping: product information, your addresses and
preferences, credit details.
 Scientific applications and government departments: generate report
of weather forecasting ,air traffic control, space observations also in
CBI investigations
Front End and Back End
 Front End – Front End are software's that provides user interface i.e.
Screens ,dialog boxes and reports for users
 To design interface programming languages like Visual basic,

java,dotnet are used.
 Back End – Back end are also software's which handle data and
information which is stored for long time.
 For example Oracle, Access , SQL server, Syabase, Informix ,

PostgreSQL, FoxPro, FoxPro-w
RDBMS
 RDBMS – Relational Database Management System.
 RDBMS Stands for "Relational Database Management System." An

RDBMS is a DBMS designed specifically for relational databases.
Therefore, RDBMSes are a subset of DBMSes.
 A relational database refers to a database that stores data in a
structured format, using rows and columns.
 This makes it easy to locate and access specific values within the
database.
 It is "relational" because the values within each table are related to each
other. Tables may also be related to other tables.
 The relational structure makes it possible to run queries across multiple
tables at once.
 Examples of RDBMS- Example of RDBMS are mysql, postgre, sql
server, oracle,access,DB2 etc.
RDBMS Concepts
 Table - The RDBMS database uses tables to store data. A table is a

collection of related data entries and contains rows and columns to store
data. A table is the simplest example of data storage in RDBMS.
 Let's see the example of student table.
ID Name AGE COURSE
1 Ajeet 24 B.Tech
2 aryan 20 C.A
3 Mahesh 21 BCA
4 Ratan 22 MCA
5 Vimal 26 BS
 What is field
Field is a smaller entity of the table which contains specific
information about every record in the table. In the above example, the
field in the student table consist of id, name, age, course.
 What is row or record
A row of a table is also called record. It contains the specific
information of each individual entry in the table. It is a horizontal entity
in the table. For example: The above table contains 5 records.
1 Ajeet 24 B.Tech
 What is column
A column is a vertical entity in the table which contains all information
associated with a specific field in a table. For example: "name" is a
column in the above table which contains all information about student's
name.
Ajeet
Aryan
Mahesh
Ratan
Vimal
Difference between DBMS AND RDBMS
Sr. NO DBMS RDBMS
1) DBMS applications store data as file. RDBMS applications store data in a tabular form.
2) In DBMS, data is generally stored in either a In RDBMS, the tables have an identifier called
hierarchical form or a navigational form. primary key and the data values are stored in the
form of tables.
3) Normalization is not present in DBMS. Normalization is present in RDBMS.
4) DBMS does not apply any security with RDBMS defines the integrity constraint for the
regards to data manipulation. purpose of ACID (Atomicity, Consistency, Isolation
and Durability) property.
5) DBMS uses file system to store data, so there in RDBMS, data values are stored in the form of
will be no relation between the tables. tables, so a relationship between these data values
will be stored in the form of a table as well.
6) DBMS has to provide some uniform methods RDBMS system supports a tabular structure of the
to access the stored information. data and a relationship between them to access the
stored information.
7) DBMS does not support distributed RDBMS supports distributed database.

database.
8) DBMS is meant to be for small organization RDBMS is designed to handle large amount of
and deal with small data. it supports single data. it supports multiple users.
user.
9) Examples of DBMS are file Example of RDBMS are mysql, postgre, sql
systems, xml ,foxpro,Foxpro-w etc. server, oracle,access,DB2 etc.
DBMS Architecture
Types of DBMS Architecture
 1-Tier Architecture
The simplest of Database Architecture are 1 tier where the Client,

Server, and Database all reside on the same machine. Anytime you install a
DB in your system and access it to practice SQL queries it is 1 tier architecture.
But such architecture is rarely used in production.
 A two-tier architecture
A two-tier architecture is a database architecture where

1. Application layer/Presentation layer runs on a client (PC, Mobile, Tablet, etc)
2. Data is stored on a Server.
3-tier schema is an extension of the 2-tier architecture. 3-tier

architecture has following layers
1. GUI/Presentation layer (your PC, Tablet, Mobile, etc.)
2. Application layer (server)
3. Database Server
Data abstraction
 Process of hiding unnecessary data from the user and this process of hiding
unwanted data is called Data Abstraction.
 Process of hiding the complexity of data and representing data in user redable
format.
For ex. If we want to access any mail from our Gmail then we don't know where that
data is physically stored i.e. is the data present in India or USA or what data model
has been used to store that data? We are not concerned about these things. We are
only concerned with our email.
Data abstraction Levels
 View Level or External Schema
 This level tells the application about how the data should be shown to the user.
Example:
1. If we have a login-id and password in a university system, then as a student, we can

view our marks, attendance, fee structure, etc.
2. But the faculty of the university will have a different view. He will have options like
salary, edit marks of a student, enter attendance of the students, etc. So, both the
student and the faculty have a different view.
3. The security of the system also increases. In this example, the student can't edit his
marks but the faculty who is authorized to edit the marks can edit the student's
marks.
 Conceptual Level or Logical Level
 This level tells what the data is actually stored and structured.
 Example: Let us take an example where we use the relational model for storing the
data.
1. We have to store the data of a student, the columns in the student table will be
student_name, age, mail_id, roll_no etc.
2. We have to define all these at this level while we are creating the database.
3. Though the data is stored in the database but the structure of the tables like the
student table, teacher table, books table, etc are defined here in the conceptual
level or logical level.
 Physical Level or Internal level
 As the name suggests, the Physical level tells us that How the data is actually
stored i.e. it tells the actual location of the data that is being stored by the user.
1. The Database Administrators(DBA) decide that which data should be kept at which
particular disk drive.
2. HOW the data has to be fragmented, where it has to be stored etc.
3. They decide if the data has to be centralized or distributed.
Instance
 Definition of instance: The data stored in database at a particular moment of time
is called instance of database. Database schema defines the variable declarations
in tables that belong to a particular database; the value of these variables at a
moment of time is called the instance of that database.
 For example, lets say we have a single table student in the database, today the
table has 100 records, so today the instance of the database has 100 records. Lets
say we are going to add another 100 records in this table by tomorrow so the
instance of database tomorrow will have 200 records in table. In short, at a particular
moment the data stored in database is called the instance, that changes over time
when we add or delete data from the database.
Schema
 Definition of instance: Overall design of a database is called the schema. Schema
is of three types: Physical schema, logical schema and view schema.
Student Schema
Physical Schema -The design of a schema at physical level is called physical schema,
how the data stored in blocks of storage is described at this level. Physical Schema is
below logical schema. It can be easily changed without affecting application program.
Logical Schema - The design of a schema at logical level is called logical schema,
Logical schema are very important as it may directly effect on the applications.
The reason behind this is program are constructed on basis of logical schema.
View Schema /Subschema -Design of database at view level is called view schema.
This generally describes end user interaction with database systems.
Data independance
 Data independence - Ability to modify the schema at one level of the
database system without altering the schema at the next higher level.
 Physical Data Independence – The ability to modify the physical schema
without changing the logical schema is called physical data independence.
 Logical Data Independence- The ability to modify the logical schema
without changing the application program to be rewritten is called logical data
independence.
It is harder to achieve because application programs are depend on logical
structure of data
Overall Structure of DBMS
Data Models
 Data model - A Database model defines the logical design and
structure of a database and defines how data will be stored,
accessed and updated in a database management system.
 Hierarchical model
 Network model
 Entity-Relationship data model
 Relational Model
Hierarchical Model
 This database model organizes data into a tree-like-structure, with a
single root, to which all the other data is linked. The hierarchy starts
from the Root data, and expands like a tree, adding child nodes to
the parent nodes.
 In this model, a child node will only have a single parent node.
 This model efficiently describes many real-world relationships like
index of a book, recipes etc.
 In hierarchical model, data is organized into tree-like structure with
one one-to-many relationship between two different types of data,
for example, one department can have many courses, many
professors and of-course many students.
Network model
 This is an extension of the Hierarchical model. In this model data is
organised more like a graph, and are allowed to have more than one
parent node.
 In this 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 was used to map many-to-many data relationships.
 This was the most widely used database model, before Relational
Model was introduced.
Relational Model
 In this model, data is organised in two-dimensional tables and the relationship is maintained by storing a common field.
 All the data is stored in various tables.
 Example of tabular data in the relational model
Columns
Rows
A Sample Relational Database
Entity-Relationship data model
An Entity–relationship model (ER model) describes the
structure of a database with the help of a diagram, which is known
as Entity Relationship Diagram (ER Diagram). An ER model is a
design or blueprint of a database that can later be implemented as a
database.
Components of a ER Diagram
Entity-Relationship Symbol
Rectangle: Represents Entity sets.
Ellipses: Attributes
Diamonds: Relationship Set
Lines: They link attributes to Entity Sets and Entity sets to Relationship Set
Double Ellipses: Multivalued Attributes
Dashed Ellipses: Derived Attributes
Double Rectangles: Weak Entity Sets
Double Lines: Total participation of an entity in a relationship set
Entity-Relationship Symbol
 As shown in the above diagram, an ER diagram has three main components:

1. Entity
2. Attribute
3. Relationship
 1. Entity
 An entity is an object or component of data. An entity is represented as rectangle in
an ER diagram.
For example: In the following ER diagram we have two entities Student and College
and these two entities have many to one relationship as many students study in a
single college. We will read more about relationships later, for now focus on entities.
 2. Attribute
 An attribute describes the property of an entity. An attribute is represented as Oval
in an ER diagram.
 3. Relationship
 A relationship is represented by diamond shape in ER diagram, it shows the
relationship among entities.
 There are four types of attributes:
 1. Key attribute
2. Composite attribute
3. Multivalued attribute
4. Derived attribute
 1. Key attribute:
 A key attribute can uniquely identify an entity from an entity set. For example,
student roll number can uniquely identify a student from a set of students. Key
attribute is represented by oval same as other attributes however the text of key
attribute is underlined.
 2. Composite attribute:
 An attribute that is a combination of other attributes is known as composite attribute.
For example, In student entity, the student address is a composite attribute as an
address is composed of other attributes such as pin code, state, country.
 3. Multivalued attribute:
An attribute that can hold multiple values is known as multivalued attribute. It is

represented with double ovals in an ER Diagram. For example – A person can
have more than one phone numbers so the phone number attribute is multivalued.
 4. Derived attribute:
A derived attribute is one whose value is dynamic and derived from another
attribute. It is represented by dashed oval in an ER Diagram. For example – Person
age is a derived attribute as it changes over time and can be derived from another
attribute (Date of birth).
 3. Relationship
 A relationship is represented by diamond shape in ER diagram, it shows the
relationship among entities. There are four types of relationships:
1. One to One
2. One to Many
3. Many to One
4. Many to Many
 1. One to One Relationship
 When a single instance of an entity is associated with a single instance of another
entity then it is called one to one relationship. For example, a person has only one
passport and a passport is given to one person.
 2. One to Many Relationship
 When a single instance of an entity is associated with more than one instances of
another entity then it is called one to many relationship. For example – a customer
can place many orders but a order cannot be placed by many customers.
 3. Many to One Relationship

 When more than one instances of an entity is associated with a single instance of
another entity then it is called many to one relationship. For example – many
students can study in a single college but a student cannot study in many colleges
at the same time.
 4. Many to Many Relationship
 When more than one instances of an entity is associated with more than one
instances of another entity then it is called many to many relationship. For example,
a can be assigned to many projects and a project can be assigned to many
students.
Data Definition Language (DDL)
 Specification notation for defining the database schema
Example: create table instructor (
ID char(5),
name varchar(20),
dept_name varchar(20),
salary numeric(8,2))
 DDL compiler generates a set of table templates stored in a data dictionary
 Data dictionary contains metadata (i.e., data about data)
 Database schema
 Integrity constraints
 Primary key (ID uniquely identifies instructors)
 Authorization
 Who can access what
 Specification notation for defining the database schema
Example: create table instructor (
ID char(5),
name varchar(20),
dept_name varchar(20),
salary numeric(8,2))
 DDL compiler generates a set of table templates stored in a data dictionary
 Data dictionary contains metadata (i.e., data about data)
 Database schema
 Integrity constraints
 Primary key (ID uniquely identifies instructors)
 Authorization
 Who can access what
Data Manipulation Language (DML)
 Language for accessing and manipulating the data organized
by the appropriate data model
 DML also known as query language
 Two classes of languages
 Pure – used for proving properties about computational
power and for optimization
 Relational Algebra
 Tuple relational calculus
 Domain relational calculus
 Commercial – used in commercial systems
 SQL is the most widely used commercial language
SQL
 The most widely used commercial language
 SQL is NOT a Turing machine equivalent language
 SQL is NOT a Turing machine equivalent language
 To be able to compute complex functions SQL is usually
embedded in some higher-level language
 Application programs generally access databases through one of
 Language extensions to allow embedded SQL
 Application program interface (e.g., ODBC/JDBC) which allow
SQL queries to be sent to a database
Database Design
The process of designing the general structure of the database:
 Logical Design – Deciding on the database schema.

Database design requires that we find a “good” collection of
relation schemas.
 Business decision – What attributes should we record in
the database?
 Computer Science decision – What relation schemas
should we have and how should the attributes be
distributed among the various relation schemas?
 Physical Design – Deciding on the physical layout of the
database
Database Design (Cont.)
 Is there any problem with this relation?
Design Approaches
 Need to come up with a methodology to ensure that each of the
relations in the database is “good”
 Two ways of doing so:
 Entity Relationship Model (Chapter 7)
 Models an enterprise as a collection of entities and
relationships
 Represented diagrammatically by an entity-relationship
diagram:
 Normalization Theory (Chapter 8)
 Formalize what designs are bad, and test for them
Object-Relational Data Models
 Relational model: flat, “atomic” values
 Object Relational Data Models
 Extend the relational data model by including object orientation
and constructs to deal with added data types.
 Allow attributes of tuples to have complex types, including non-
atomic values such as nested relations.
 Preserve relational foundations, in particular the declarative
access to data, while extending modeling power.
 Provide upward compatibility with existing relational languages.
XML: Extensible Markup Language
 Defined by the WWW Consortium (W3C)
 Originally intended as a document markup language not a
database language
 The ability to specify new tags, and to create nested tag structures
made XML a great way to exchange data, not just documents
 XML has become the basis for all new generation data interchange
formats.
 A wide variety of tools is available for parsing, browsing and
querying XML documents/data
Database Engine
 Storage manager
 Query processing
 Transaction manager
Storage Management
 Storage manager is a program module that provides the interface
between the low-level data stored in the database and the application
programs and queries submitted to the system.
 The storage manager is responsible to the following tasks:
 Interaction with the OS file manager
 Efficient storing, retrieving and updating of data
 Issues:
 Storage access
 File organization
 Indexing and hashing
Query Processing
1. Parsing and translation
2. Optimization
3. Evaluation
Query Processing (Cont.)
 Alternative ways of evaluating a given query
 Equivalent expressions
 Different algorithms for each operation
 Cost difference between a good and a bad way of evaluating a
query can be enormous
 Need to estimate the cost of operations
 Depends critically on statistical information about relations
which the database must maintain
 Need to estimate statistics for intermediate results to compute
cost of complex expressions
Transaction Management
 What if the system fails?
 What if more than one user is concurrently updating the same
data?
 A transaction is a collection of operations that performs a single
logical function in a database application
 Transaction-management component ensures that the
database remains in a consistent (correct) state despite system
failures (e.g., power failures and operating system crashes) and
transaction failures.
 Concurrency-control manager controls the interaction among
the concurrent transactions, to ensure the consistency of the
database.
Database Users and Administrators
Database
Database System Internals
Database Architecture
The architecture of a database systems is greatly influenced by

the underlying computer system on which the database is running:
 Centralized
 Client-server
 Parallel (multi-processor)
 Distributed
History of Database Systems
 1950s and early 1960s:
 Data processing using magnetic tapes for storage
 Tapes provided only sequential access
 Punched cards for input
 Late 1960s and 1970s:
 Hard disks allowed direct access to data
 Network and hierarchical data models in widespread use
 Ted Codd defines the relational data model
 Would win the ACM Turing Award for this work
 IBM Research begins System R prototype
 UC Berkeley begins Ingres prototype
 High-performance (for the era) transaction processing
History (cont.)
 1980s:
 Research relational prototypes evolve into commercial systems
 SQL becomes industrial standard
 Parallel and distributed database systems
 Object-oriented database systems
 1990s:
 Large decision support and data-mining applications
 Large multi-terabyte data warehouses
 Emergence of Web commerce
 Early 2000s:
 XML and XQuery standards
 Automated database administration
 Later 2000s:
 Giant data storage systems
 Google BigTable, Yahoo PNuts, Amazon, ..
End of Chapter 1

Ch1 - Database System Concept

Uploaded by

Copyright:

Available Formats

Ch1 - Database System Concept

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Ch1 - Database System Concept

Uploaded by

Copyright:

Available Formats

Database Management System(22319)

 Data – collection of facts ,figures ,numbers.

A database management system (DBMS) is Collection of interrelated data

A database management system (DBMS) is system software for creating

 Application program examples

 Add new students, instructors, and courses

 Register students for courses, and generate class rosters

 Assign grades to students, compute grade point averages

 In the early days, database applications were built directly on

 Data redundancy and inconsistency

 Multiple file formats, duplication of information in different files

 Difficulty in accessing data

 Do not provide transaction support

 Maintaining security is challenging

 Difficult to enforce standards

 Integrity constraints (e.g., account balance > 0)

 Hard to add new constraints or change existing one

Database systems offer solutions to all the above problems

 sharing of data easy.

 Reeducation in Data redundancy

 Easy access of data

 provide transaction support

 Maintaining security of data

 Easy to enforce standards

 Maintain Integrity constraints

 Integrity constraints (e.g., account balance > 0) can easily

 To design interface programming languages like Visual basic,

 For example Oracle, Access , SQL server, Syabase, Informix ,

 RDBMS Stands for "Relational Database Management System." An

 Table - The RDBMS database uses tables to store data. A table is a

 Let's see the example of student table.

ID Name AGE COURSE

7) DBMS does not support distributed RDBMS supports distributed database.

The simplest of Database Architecture are 1 tier where the Client,

A two-tier architecture is a database architecture where

3-tier schema is an extension of the 2-tier architecture. 3-tier

 View Level or External Schema

1. If we have a login-id and password in a university system, then as a student, we can

2. HOW the data has to be fragmented, where it has to be stored etc.

3. They decide if the data has to be centralized or distributed.

 Physical Data Independence – The ability to modify the physical schema

without changing the logical schema is called physical data independence.

 Logical Data Independence- The ability to modify the logical schema

without changing the application program to be rewritten is called logical data

It is harder to achieve because application programs are depend on logical

 Entity-Relationship data model

Rectangle: Represents Entity sets.

Diamonds: Relationship Set

Double Ellipses: Multivalued Attributes

Dashed Ellipses: Derived Attributes

Double Rectangles: Weak Entity Sets

Double Lines: Total participation of an entity in a relationship set

 As shown in the above diagram, an ER diagram has three main components:

An attribute that can hold multiple values is known as multivalued attribute. It is

 3. Many to One Relationship

 Logical Design – Deciding on the database schema.

The architecture of a database systems is greatly influenced by

You might also like