Database Management Systems

DEPARTMENT OF COMPUTER SCIENCE

Bachelor Computer Applications

1
COURSE OUTLINE

CHAPTER TOPICS PAGE

1 Databases and Database Users 3 – 24

2 Database System Concepts and Architecture 25 – 42

3 Data Modeling Using the Entity-Relationship 43 – 78

(ER) Model

4 The Relational Data Model and Relational 79 – 101

Database Constraints

5 Relational Algebra 102 – 123

6 Functional Dependencies and Normalization 124 - 153

for Relational Databases

2
Chapter 1
Databases and Database Users

3
Overview
Chapter-1: Databases and Databases Users
1. Introduction : Types of databases and functionalities
1.2 An Example (University Database)
1.3 Characteristics of the Database Approach
1.3.1 Self-Describing Nature of a Database System
1.3.2 Insulation between Programs and Data, and Data Abstraction
1.3.3 Support of Multiple Views of the Data
1.3.4 Sharing of Data and Multiuser Transaction Processing
1.4 Actors on the Scene
1.4.1 Database Administrators
1.4.2 Database Designers
1.4.3 End Users
1.4.4 System Analysts and Application Programmers (Software Engineers)
1.5 Workers behind the Scene
1.6 Advantages of Using the DBMS Approach 4
Types of Databases and Database
Applications
 Traditional Applications:
 Numeric and Textual Databases
 More Recent Applications:
 Multimedia Databases
 Geographic Information Systems (GIS)
 Data Warehouses
 Real-time and Active Databases
 Many other applications
 We will focus on traditional applications of
database to solve mini-world problems.
5
Basic Definitions
 Database:
 A collection of related data.

 Data:
 Known facts that can be recorded and have an implicit

meaning.
 Mini-world or the Universe of discourse (UoD) :
 Represents some aspects /parts of the real world about which

data is stored in a database. For example, student registration

system at a university, Patients record system in a hospital.
 Database Management System (DBMS):
 A collection of programs/software package to facilitate the

creation and maintenance of a database.

 Its facilitates the processes of defining, constructing,
manipulating, and sharing databases among various users and
applications.
6
Typical DBMS Functionality
 Define a particular database in terms of its data types,
structures, and constraints
 Construct or Load the initial database contents on a
secondary storage medium
 Manipulating the database:
 Retrieval: Querying, generating reports
 Modification: Insertions, deletions and updates to its
content
 Generating reports from the data
 Processing and Sharing a database allows multiple users
and programs to access the database simultaneously

7
Typical DBMS Functionality (continued)
 Application program: accesses the database by sending queries
or requests for data to the DBMS.
 A query typically causes some data to be retrieved; a transaction
may cause some data to be read and some data to be written into
the database.
 Protection includes system protection against hardware or software
malfunction (or crashes), unauthorized or malicious access.
 DBMS must be able to maintain the database system by allowing
the system to evolve as requirements change over time.
 Database system: To complete our initial definitions, we will call the
database and DBMS software together a database system.
 Figure I.I illustrates some of the concepts we have discussed so far.

8
Simplified database system environment

9
Example of a Database
(with a Conceptual Data Model)
 Mini-world for the example:
 Part of a UNIVERSITY environment.
 Some mini-world entities:
 STUDENTs
 COURSEs
 SECTIONs (of COURSEs)
 (academic) DEPARTMENTs
 INSTRUCTORs
Example of a Database
(with a Conceptual Data Model)
 Some mini-world relationships:
 SECTIONs are of specific COURSEs
 STUDENTs take SECTIONs
 COURSEs have prerequisite COURSEs
 INSTRUCTORs teach SECTIONs
 COURSEs are offered by DEPARTMENTs
 STUDENTs major in DEPARTMENTs

 Note: The above entities and relationships are typically

expressed in a conceptual data model, such as the
ENTITY-RELATIONSHIP data model (see Chapters 3, 4)
Example of a simple database
Main Characteristics of the Database
Approach
 Self-describing nature of a database system:
 A DBMS catalog stores the description of a particular
database (e.g. data structures, types and constraints on the
data)
 The information stored in the catalog is called meta-
data, and it describes the structure of the primary
database.
 This allows the DBMS software to work with different
database applications.
 Insulation between programs and data:
 Called program-data independence.
 Allows changing data structures and storage organization
without having to change the DBMS access programs.
Example of a simplified database catalog
Main Characteristics of the Database
Approach (continued)
 Data Abstraction:
 The characteristic that allows program-data
independence and program-operation independence is
called data abstraction. Informally, A data model is
used to hide storage details and present the users with
a conceptual view of the database.
 Support of multiple views of the data:
 Each user may see a different view (subset or
virtual data) of the database, which describes only
the data of interest to that user.
Main Characteristics of the Database
Approach (continued)
 Sharing of data and multi-user
transaction processing:
 Allowing a multiple users to retrieve/access from
and update to the database
 Concurrency control within the DBMS
guarantees that each transaction is correctly
executed or aborted
 These types of applications are generally called
online transaction processing (OLTP)
applications.
Database Users
 Users may be divided into
 Those who actually use and control the database
content, and those who design, develop and
maintain database applications (called “Actors on
the Scene”), and
 Those who design and develop the DBMS
software and related tools, and the computer
systems operators (called “Workers Behind the
Scene”).
Database Users
 Actors on the scene
 Database administrators:
 Responsible for authorizing access to the database,
for coordinating and monitoring its use, acquiring
software and hardware resources, controlling its use
and monitoring security and efficiency of
operations.
 Database Designers:
 Responsible to define the content, the structure, the
constraints, and functions or transactions against
the database. They must communicate with the
end-users and understand their needs.
Categories of End-users
 Actors on the scene
 End-users: They use the data for queries, reports
and some of them update the database content.
End-users can be categorized into:
 Casual: access database occasionally when
needed
 Naïve or Parametric: Constantly querying and
updating the database, using standard types of
queries and updates-called canned transactions.
 Examples are bank-tellers or university secretaries who
do this activity for an entire shift of operations.
Categories of End-users (continued)
 Sophisticated:
 These include business analysts, scientists,
engineers, others thoroughly familiar with the system
capabilities.
 Stand-alone:
 Mostly maintain personal databases using ready-to-
use
packaged applications.
 E.g.:
 (i) User of a tax package that stores a variety of personal
financial data for tax purposes,
 (ii) A scientists that creates a database for
its own
experiments,
 (iii) A user that maintains an address book
 You may become sophisticated or stand-alone
Categories of End-users (continued)
 System analysts: determine the requirements of end
users, and develop specifications for standard canned
transactions that meet these requirements.
 Application programmers: implement these
specifications as programs; they debug,
then
document, and maintain these canned test,
transactions. Such
analysts and programmers commonly referred to as
software developers or software engineers.
Workers behind the Scene
 DBMS system designers and implementers design
and implement the DBMS modules and interfaces as a
software package.
 Tool developers design and implement tools- that
facilitate database modeling and design, database system
design, and improved performance.
 Operators and maintenance personnel (system
administration personnel) are responsible for the actual
running and maintenance of the hardware and software.
Advantages of Using the Database
Approach
 Interacting easily with data using high-level dedicated tools
 Controlling redundancy in data storage and in

development and maintenance efforts.

 Sharing of data among multiple users.

 Ensuring consistency of data.

 Restricting unauthorized access to data.

 Providing storage structures (e.g. indexes) for efficient

query processing
 Providing backup and recovery services.

 Providing multiple interfaces to different classes of users.

 Representing complex relationships among data.

 Enforcing integrity constraints on the

database.
Review questions
1. Define the following terms: data, database, DBMS, database system, database
catalog, UoD, data model, program-data independence, DBA, User view, canned
transaction, meta-data, transaction-processing application etc.
2. What are the responsibilities of the DBA and the Database designers?
3. List down the advantages of using DBMS approach.
4. Discuss the differences between system Analysts and Application programmers.
5. What do you understand by database management systems (DBMS)?
6. Briefly explain the advantages of using DBMS approach.
7. What four main types of actions involve databases? Briefly discuss each.
8. What are the different types of database end users? Discuss the main activities
of each.
9. Discuss the main characteristics of the database approach and how it differs
from traditional file systems.
10. Draw a simplified diagram of a database system environment.
11. Draw a schema of a mini world database.
12. Explain the workers behind the scene.
24
Chapter 2
Database System Concepts and
Architecture

25
Overview
Chapter-2 : Database System Concepts and Architecture
2. Introduction
2.1 Data models, Schemas and Instances
2.1.1 Categories of Data Models
2.1.2 Schemas, Instances, and Database State
2.2 Three schema architecture and data independence
2.2.1 The Three-Schema Architecture
2.2.2 Data Independence
2.3 Database languages and interfaces
2.3.1 DBMS Languages
2.3.2 DBMS Interfaces
2.4 DBMS system environment
2.4.1 DBMS Component Modules
2.4.2 Database System Utilities
2.4.3 Tools, Application Environments
In-Class Exercise & Assignment 26
Data Model & Data Abstraction
 Data Model:
 A data model is a collection of concepts that can
be used to describe the structure of a database-
provides the necessary means to achieve this
abstraction. By structure of a database we mean
the data types, relationships, and constraints
that apply to the data.
 Data abstraction:
 It generally refers to the hiding of details of data
organization and storage, and the highlighting of
the essential features for an improved
understanding of data.
27
Categories of Data Models
 High-level or conceptual data models: provide concepts
that are close to the way many users perceive data.
 Low-level or physical data models: provide concepts that
describe the details of how data is stored on the computer’s disk.
 An access path is a structure that makes the search for particular
database records efficient.
 An index is an example of an access path that allows direct access
to data using an index term or a keyword.
Between these two extremes we have,
 Representational or implementation data models provide
concepts that may be easily understood by end users but that are
not too far removed from the way data is organized in computer
storage (hard disks).
Copyright © 2015 Ramez Elmasri and Shamkant B. Navathe
Categories of Data Models (Continued)
 Conceptual data models use concepts such as
entities, attributes, and
relationships.
 Entity represents a real-world object or concept, such as
an
 Attribute represents some property of interest that further
employee or a project from the mini-world.
describes an entity, such as the employee's name or salary.
 Relationship among two or more entities represents
association
an among the entities,
 Example, a works-on relationship between an employee and a
project. (See chapter-3 : Entity-Relationship model).

Project
Employee works-
on
Schemas, Instances, and Database State
 Schemas: The description of a database is called the database
schema.
 Schema diagram: A displayed schema is called a
schema
diagram. (See Figure 2.1)
Schemas, Instances, and Database State
 Schema construct: Each object in the schema - such as
STUDENT or COURSE- is a schema construct.
 Database state or Snapshot: The data in the database at a
particular moment in time is called a database state or
snapshot . It is also called the current set of occurrences or
instances.
 The distinction between database schema and database
state is very important.
 When we define a new database, we specify its database
schema only to the DBMS. At this point, the corresponding
database state is the empty state with no data. We get
the initial state of the database when the database is first
populated or loaded with the initial data.
Copyright © 2015 Ramez Elmasri and Shamkant B. Navathe
Three-Schema Architecture and
Data Independence
 Three of the four important characteristics of the database
approach:
(1) Use of a catalog to store the database description (schema) so as
to make it self-describing,
(2) Insulation of programs and data (program-data and program-
operation independence), and
(3) Support of multiple user views.
The Three-Schema Architecture
The Three-Schema Architecture
1. Internal schema (Internal level) at the internal level to
describe physical storage structures and access paths.
Typically uses a physical data model.

2. Conceptual schema (Conceptual level) describes the

structure of the whole database. It hides the details of physical
storage structures and concentrates on describing entities, data
types, relationships, user operations, and constraints.

3. External schema (view level), describes the part of the

database that a particular user group is interested in and hides
the rest of the database from that user group.
Data Independence
 Logical data independence is the capacity to change the
conceptual schema without having to change external schemas
or application programs.
 We may change the conceptual schema to expand the database
(by adding a record type or data item), to change constraints, or
to reduce the database (by removing a record type or data item).
 Physical data independence is the capacity to change
the internal schema without having to change the conceptual
schema. Hence, the external schemas need not be changed as
well.
DBMS Languages
 Data Definition Language (DDL): Used by the DBA
and database designers to specify the conceptual schema of a
database. In many DBMSs, the DDL is also used to define
internal and external schemas (views). In some DBMSs,
separate Storage Definition Language (SDL) and View
Definition Language (VDL) are used to define internal and
external schemas.
 Data Manipulation Language (DML): Used to
specify database retrievals and updates.
 DML commands (data sublanguage) can be embedded in a
general-purpose programming language (host language), such
as COBOL, C or an Assembly Language.
DBMS Languages (Continued)
 High Level or Non-procedural e.g.,
SQL, are set-oriented
Languages : and specify what data to retrieve
than how to retrieve. Also called declarative languages.

 Low Level or Procedural Languages: record-at-a-

time; they specify how to retrieve data and include constructs
such as looping.
DBMS Interfaces
 User-friendly interfaces:
 Menu-based, popular for browsing on the web
 Forms-based, designed for naïve users
 Graphical User Interface (Point and Click, Drag and Drop
etc.)
 Natural language: requests in written English
 Speech as Input (?) and Output
 Parametric interfaces (e.g., bank tellers) using function
keys.
 Interfaces for the DBA: Creating accounts, granting
authorizations, Setting system parameters, Changing
schemas or access path
The Database System Environment
DBMS Component Modules
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.
 Database storage reorganization reorganizes a set
of database files into different file organizations, and
create new access paths to improve performance.
 Performance monitoring utility monitors database
usage and provides statistics to the DBA. such as
whether or not to reorganize files or whether to add or
drop indexes to improve performance.
Tools and Application Environments
 Data dictionary / repository : Used to store schema
descriptions and other information such as design
decisions, application program descriptions, user
information, usage standards, etc.
Active data dictionary is accessed by DBMS software


and users/DBA.
 Passive data dictionary is accessed by users/DBA

only.
 Application Development Environments and CASE
(computer-aided software engineering) tools:
 Examples – Power builder (Sybase), Builder (Borland)

41
Copyright © 2015 Ramez Elmasri and Shamkant B. Navathe
Review questions
1. Define the following terms: data model, database schema, database state,
internal schema, conceptual schema, external schema, data independence,
access path, DDL, DML, SDL, VDL, query language, host language, data
sublanguage, database utility etc.
2. List down the main categories of data models.
3. What is the difference between a database schema and a database state?
4. What is the difference between logical data independence and physical data
independence?
5. Draw the diagram of three-schema architecture.
6. What is the difference between procedural and nonprocedural DMLs?
7. List down the different types of user-friendly interfaces.
8. With what other computer system software does a DBMS interact?
9. Discuss the main categories of data models.
10. Explain the three-schema architecture with the help of its diagram.
11. Discuss the different types of user-friendly interfaces and the types of users who
typically use each.
12. Discuss some types of database utilities and tools and their functions. 42