18CSC303J DATABASE

MANAGEMENT SYSTEMS
Course Learning Rationale (CLR): The purpose of learning this course is to:

CLR-1 : Understand the fundamentals of Database Management

Systems, Architecture and Languages
CLR-2 : Conceive the database design process through ER Model and
Relational Model
CLR-3 : Design Logical Database Schema and mapping it to
implementation level schema through Database Language
CLR-4 : Features Familiarize queries using Structure Query Language
(SQL) and PL/SQL
CLR-5 : Familiarize the Improvement of the database design using
normalization criteria and optimize queries
CLR-6 : Understand the practical problems of concurrency control and
gain knowledge about failures and recovery

Department of IT
 UNIT 1-SYLLABUS
• What is Database Management System
• Advantage of DBMS over File Processing System
• Introduction and applications of DBMS
• Purpose of database system
• Views of data
• Lab 1: SQL Data Definition
• Language Commands on sample exercise* The abstract of the project to
construct database must be framed
• Database system Architecture
• Data Independence
• The evolution of Data Models
• Lab 2: SQL Data Manipulation,Language Commands * Identification
• of project Modules and functionality
• Degrees of Data Abstraction
• Database Users and DBA
• Database Languages
• Lab 3: SQL Data Control Language Commands and Transaction control
commands to the sample exercises* Identify the issues that can arise in a
business perspective for the application
 Definitions-DBMS
•
Data: Known facts that can be recorded and that have implicit
meaning

Database: Collection of related data

Ex. the names, telephone numbers and addresses of all the
people you know

Database System: A computer based record-keeping system

Data base management system: A software that can be used to

store data in a data base and manipulate it.
 DBMS
Database Management System (DBMS)
 Collection of interrelated data
 Set of programs to access the data
 An environment that is both convenient and efficient to use
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

Department of IT
PURPOSE OF DATABASE SYSTEMS
• Drawbacks of using file systems to store data:
– Data redundancy and inconsistency
• Multiple file formats, duplication of information in

different files
– Difficulty in accessing data
• Need to write a new program to carry out each new

task
– Data isolation — multiple files and formats
– Integrity problems
• Integrity constraints (e.g. account balance > 0)

become “buried” in program code rather than being

stated explicitly
• Hard to add new constraints or change existing ones

Department of IT
DRAWBACKS OF USING FILE
SYSTEMS
 Data redundancy and inconsistency
 Difficulty in accessing data
 Data isolation
 Integrity problems
 Atomicity of updates
 Concurrent access by multiple users
 Security problems

Department of IT
 PURPOSE OF DATABASE SYSTEMS
(CONT.)
• Drawbacks of using file systems (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 accessed needed for performance

• Uncontrolled concurrent accesses can lead to inconsistencies

– Example: Two people reading a balance and updating it

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

Department of IT
• ADVANTAGES OF DBMS
 CENTRALISED CONTROL
 INTEGRATED AND SHARED
 REDUNDANCY CAN BE REDUCED
 INCONSISTENCY CAN BE AVOIDED
 STANDARDS CAN BE ENFORCED
 SECURITY RESTRICTIONS
 DATA INDEPENDENCE
 INTEGRITY CAN BE MAINTAINED-CANDIDATE KEY
 PRIMARY KEY
 FORIEGN KEY
 DOMAIN INTEGRITY
 ENTITY INTEGRITY
 REFERENTIAL INTEGRITY
 LEVELS OF ABSTRACTION
• Physical level: describes how a record (e.g., customer) is
stored.
• Logical level: describes data stored in database, and the
relationships among the data.
type customer = record
customer_id : string;
customer_name : string;
customer_street : string;
customer_city : string;
end;
• View level: application programs hide details of data types.
Views can also hide information (such as an employee’s
salary) for security purposes.

Department of IT
 VIEW OF DATA
An architecture for a database system

Department of IT
 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

Department of IT
 INSTANCES AND SCHEMAS
• Similar to types and variables in programming languages
• Schema – the logical structure of the database
– Example: The database consists of information about a set of customers
and accounts and the relationship between them)
– Physical schema: database design at the physical level
– Logical schema: database design at the logical level
• Instance – the actual content of the database at a particular point in time
• Physical Data Independence – the ability to modify the physical schema
without changing the logical schema
– Applications depend on the logical schema
– In general, the interfaces between the various levels and components
should be well defined so that changes in some parts do not seriously
influence others.

Department of IT
The three schema architecture.
 Three Schema Architecture
• Defines DBMS schemas at three levels:
– Internal schema at the internal level to describe
physical storage structures and access paths.
Typically uses a physical data model.
– Conceptual schema at the conceptual level to
describe the structure and constraints for the whole
database for a community of users. Uses a
conceptual or an implementation data model.
– External schemas at the external level to describe
the various user views. Usually uses the same data
model as the conceptual level.
OVERALL SYSTEM STRUCTURE

Department of IT
 TRANSACTION MANAGEMENT

• 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.

Department of IT
DATABASE USERS
• Users are differentiated by the way they expect to interact with the
system
• Application programmers – interact with system through DML calls
• Sophisticated users – form requests in a database query language
• Specialized users – write specialized database applications that do not
fit into the traditional data processing framework
• Naïve users – invoke one of the permanent application programs that
have been written previously
– E.g. people accessing database over the web, bank tellers, clerical
staff

Department of IT
DATABASE ADMINISTRATOR
• Coordinates all the activities of the database system; the
database administrator has a good understanding of the
enterprise’s information resources and needs.
• Database administrator's duties include:
– Schema definition
– Storage structure and access method definition
– Schema and physical organization modification
– Granting user authority to access the database
– Specifying integrity constraints
– Acting as liaison with users
– Monitoring performance and responding to changes in
requirements

Department of IT
 DATABASE LANGUAGES
• DATA DEFINITION LANGUAGE (DDL).
• DATA MANIPULATION LANGUAGE(DML)
1. PROCEDURAL LANGUAGE.
2. NON-PROCEDURAL LANGUAGE.
• DATA CONTROL LANGUAGE(DCL)
• DATA SUB-LANGUAGE
• QUERY LANGUAGE
 DBMS Interfaces
• Stand-alone query language interfaces.
• Programmer interfaces for embedding DML
in programming languages:
• User-friendly interfaces:
• Menu-based, popular for browsing on the web
• Forms-based, designed for naïve users
• Graphics-based (Point and Click, Drag and Drop
etc.)
• Natural language: requests in written English
• Combinations of the above
 Database System Utilities
• To perform certain functions such as:
• Loading data stored in files into a database.
Includes data conversion tools.
• Backing up the database periodically on tape.
• Reorganizing database file structures.
• Report generation utilities.
• Performance monitoring utilities.
• Other functions, such as sorting, user monitoring,
data compression, etc.
 DATA MODELS
• A collection of tools for describing:
• – data
• – data relationships
• – data semantics
• – data constraints
• Object-based logical models
• – entity-relationship model
• – object-oriented model
• – semantic model
• – functional model
• Record-based logical models
• – relational model (e.g., SQL/DS, DB2)
• – network model
• – hierarchical model (e.g., IMS)
 DATA MODELS
Relational DBMS

•Understands relations which need not be connected by you

during data definition
•Data connections follow tabular structure
•Tabular structure is data stored in rows and columns
 RELATIONAL MODEL

COLUMN

TABLE
ROW VALUE
 Attributes
RELATIONAL MODEL
customer- customer- customer- Ac_no
Customer-id
name street city
192-83-7465 Johnson Alma Palo Alto A-101
019-28-3746 Smith North Rye A-215
192-83-7465 Johnson Alma Palo Alto A-201
321-12-3123 Jones Main Harrison A-217
019-28-3746 Smith North Rye A-201

Department of IT
A SAMPLE RELATIONAL DATABASE

Department of IT
ENTITY-RELATIONSHIP MODEL
Example of schema in the entity-
relationship model

Department of IT
 DATA MODELS
HIERARCHICAL MODEL NETWORK MODEL
 DATA MODELS
Hierarchial DBMS

•Understands relations in the form of one to many data

connections made by you at the time of data definition
•It is like a tree structure
•Explores from a parent to one or more children
 DATA MODELS

ORDER NO.

CUSTOMER NO.
PRDUCT NO.
ORDER VALUE DESPATCH
STATUS
NAME STOCK
ADDRESS
NAME
OUTST
PAYMENT PRICE
DATE
 DATA MODELS
Network DBMS

•Understands relations in the form of any to any data

connections made by you at the time of data definition
•It does not expect parent-child relationship
 DATA MODELS
Network DBMS

CUSTOMER NO. NAME ADDRESS

ORDER NO. ORDER VALUE DESPATCH STAT

PRODUCT NO. NAME PRICE DATE

 ADVANCED DATA MODELS
• OBJECT ORIENTED DATA BASES.
• MULTIMEDIA DATA BASES.
• ACTIVE DATA BASES.
• DEDUCTIVE DATA BASES.
• KNOWLEDGE BASED SYSTEMS.
• SPATIAL DATA BASES.
• WEB DATA BASES .
• MOBILE DATA BASES.
• TEMPORAL DATA BASES.
• DATA WARE HOUSING AND MINING.
ENTITY RELATIONSHIP MODEL
(CONT.)
• E-R model of real world
– Entities (objects)
• E.g. customers, accounts, bank branch

– Relationships between entities

• E.g. Account A-101 is held by customer Johnson

• Relationship set depositor associates customers with accounts

• Widely used for database design

– Database design in E-R model usually converted to design in the
relational model (coming up next) which is used for storage and
processing

Department of IT
 SQL
STRUCTURED QUERY LANGUAGE

• SQL: widely used non-procedural language

– E.g. find the name of the customer with customer-id 192-83
select customer.customer-name
from customer
where customer.customer-id = ‘192-83’
– E.g. find the balances of all accounts held by the customer
with customer-id 192-83
select account.balance
from depositor, account
where depositor.customer-id = ‘192-83’
and
depositor.account-number =
account.account-number
• 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.
Department of IT
 DATA DEFINITION LANGUAGE (DDL)
 Specification notation for defining the database schema
 E.g.
create table account (
account-number char(10),
balance integer)
 DDL compiler generates a set of tables stored in a data dictionary
 Data dictionary contains metadata (i.e., data about data)

Department of IT
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
– Procedural – user specifies what data is required and how to
get those data
– Nonprocedural – user specifies what data is required without
specifying how to get those data
• SQL is the most widely used query language

Department of IT
 APPLICATION ARCHITECTURES

Two-tier architecture: E.g. client programs using ODBC/JDBC to

communicate with a database
Three-tier architecture: E.g. web-based applications, and
applications built using “middleware”

Department of IT
 REVIEW QUESTIONS
• Mention the advantages of DBMS over
file processing system
• Transactions of a database are atomic.
Justify
• What are database languages?
• What is meant by data independence
• List out the levels of abstraction
• Role of DBA

Department of IT