Transcript No Slide Title

Chapter 1
Introduction
Reference Book

Database Systems
Thomas Connolly, Carolyn Begg, Anne Strachan
Addison-Wesley 1999
ISBN: 0-201-34287-1
What is a database ?
The database
 is the underlying framework of the information
system
 fundamentally changed the way organisations
operate
Database technology is still in evolution due to
the need for more complex information
systems, handling different types of objects .
This lead to new file storage and access
algorithms.
Definitions
Database:
A shared collection of logically related data
(and a description of these data) , designed
to meet the information needs of an
organisation
DBMS:
Database Management System
Software that enables users to define ,create
and maintain the database and which
provides controlled access to the database
File Based Approach
File-based system
A collection of application programs that
perform services for the end-users such as the
production of reports. Each program defines
and manages its own data.
Program
Works well if the number of
items to be stored is small
and related to one object type
File-based Processing
Overlap
Limitations of the File-based System

Separation and isolation of data

Duplication of data

Program-Data dependence
 one-shot program needed to increase the field size
 File structure is defined in the program code.

Incompatibility of files (e.g. formats )
 Programs are written in different languages, and so cannot
easily access each others files.

Fixed queries leading to proliferation of application
programs
Database Approach
The limitations of file-based systems are
attributed to two factors:
the definition of the data is embedded in the
application program using the data , rather
than being stored or defined separately and
independently
there is no control over the access and
manipulation of data beyond that imposed
by the application programs being executed
Database Approach
Applications
DB
Explanation of the Definition
The definition of a database was:
A shared collection of logically related data (and a
description of these data) , designed to meet the
information needs of an organisation.

Shared collection means that the database:
 should not be seen as owned by one person or one department
 holds not only the the organisation’s operational data but is also
based on a common description of the data (system catalog,
data dictionary, metadata, central repository) providing programdata independence.

Logically related means that :
 not only the entities and the attributes but also the logical
relationships between entities are represented in the database
The DBMS



DBMS
Software that enables users to define ,create and
maintain the database and which provides controlled
access to the database
System software with two basic qualities:
 ability to manage persistent data
 ability to access large amounts of data efficiently

Other capabilities
 support for at least one data model providing a user view
 support for high level languages
 transaction management ( concurrency )
 access control: validation and authorization
 recovery system
Database Management System
(DBMS)
DBMS facilities


define the database through a Data Definition
Language (DDL)
insert, update, delete and retrieve data from the
database through a Data Manipulation Language (DML)
based upon the central repository
 procedural DML manipulating the database with a one-recordat-a-time logic , describing how data should be obtained
 non-procedural DML operating on sets of records , describing
what data should be obtained

controlled access to the database
 security system
 integrity system maintaining the consistency of stored data
 recovery control system
 user-accessible catalog
DBMS facilities 2

view mechanism allowing the user to have his or her
preferred view of the database
 the DDL allows views to be defined as a subset of the database
 views provide a level of security
 views provide a mechanism to customize the appearance of
the database
 views provide a level of data independence
Modern large multi-user DBMS products offer these facilities and
often much more. It are extremely complex software products
which are continually evolving and extended with new
functionalities
Components of a DBMS



Hardware
Software
Data
 names, types and sizes of data items
 names of relationships
 integrity constraints on the data
 authorization rules
 indexes and storage structures

Procedures
 log on
 use a DBMS facility or application program
 make a backup or copy
 change the structure or the physical location

People
Components of DBMS Environment
History of Database Systems

First-generation
Hierarchical and Network

Second generation
Relational

Third generation
Extended Relational
Object-Oriented
Roles in the Database Environment

Data Administrator : management of the data resource
 database planning, development, logical design, standards,
policies, procedures, …
 advisor of senior management concerning information plan

Database Administrator (DBA):physical aspects
 physical database design, security, integrity control

Logical Database Designers
 need a complete understanding of the organisation’s data, the
business rules and the company strategic plan

Physical Database Designers
 mapping logical model into a set of tables and constraints
 select storage structures


Application programmers
End-Users : Naïve and Sophisticated
Advantages of database systems













Control of data redundancy
Data consistency
More information from same amount of data
Sharing of data
Improved data integrity
Improved security
Enforcement of standards
Economy of scale
Balanced conflicting requirements
Improved data accessibility and responsiveness
Improved maintenance through data independence
Increased concurrency
Improved backup and recovery services
Disadvantages of database
systems

Complexity

Size

Cost of DBMS

Additional hardware costs

Cost of conversion

Performance

Higher impact of a failure
Classical database models

Relational databases

Hierarchical databases

Network databases
The Relational model
A relational database can be seen as a set of tables with
the following properties :
1. column homogeneous
2. all elements are numbers or character strings
3. all rows are different
4. the sequence of the rows is immaterial
5. columns have a different name
Such a table said to be in First Normal Form
Sample database: Relational
S s# sname status city
S1
S2
S4
Smith
Jones
Clark
20
10
20
London
Paris
London
P p# pname color weight city
P1
P2
P4
P5
Nut
Bolt
Screw
Cam
red
green
red
blue
12
17
14
12
London
Paris
London
Paris
Model
SP s#
p#
qty
S2
S2
S4
S4
S4
P1
P2
P2
P4
P5
300
400
200
300
400
Sample Query : Relational Model

Find Supplier numbers for
those suppliers who supply
part P2.
Do until no more
shipments;
get next shipment
where P# = P2 ;
print S# ;
end;

Find Part numbers for parts
supplied by supplier S2
Do until no more
shipments;
get next shipment
where S# = S2 ;
print P# ;
end;
Sample Database: Hierarchical Model
Links are anonymous
P1
Nut
Red
S2 Jones
12
London
10 Paris
300
P2 Bolt
green
S4 Clark
S2 Jones
P4 Screw red
S4 Clark
14
London
20 London 300
P5 Cam
blue
S4 Clark
14
London
20 London 200
10 Paris
12
400
Paris
20 London 400
Sample Query : Hierarchical Model

Find Supplier numbers for
those suppliers who supply
part P2.
Get next part where P#=P2
do until no more suppliers
under this part ;
get next supplier
under this part ;
print S# ;
end ;

Find Part numbers for parts
supplied by supplier S2
Do until no more parts ;
get next part ;
get next supplier
under this part
where S# = S2 ;
if found
then print P# ;
end;
Network model: Non-information Bearing Sets
S2 Jones
S2
P1 300
P1 Nut Red 12 London
10 Paris
S2
S4 Clark 20 London
P2 400
P2 Bolt Green 12 Paris
S4 P2 200
S4
P4
P4 Screw Red 14 London
300
S4
P5 400
P5 Cam Blue 12
Paris
Network model: Information Bearing Sets
S2 Jones
300
P1 Nut Red 12 London
10 Paris
400
P2 Bolt Green 12 Paris
S4 Clark 20 London
200
300
P4 Screw Red 14 London
400
P5 Cam Blue 12
Paris
Sample Query : Network Model

Find Supplier numbers for
those suppliers who supply
part P2.
Get next part where P#=P2 ;
do until no more connectors
under this part ;
get next connector
under this part ;
get supplier over this
connector ;
print S# ;
end ;

Find Part numbers for parts
supplied by supplier S2
Get next supplier where S#=S2 ;
do until no more connectors
under this supplier ;
get next connector
under this supplier ;
get part over this
connector ;
print P# ;
end ;