UNIT OBJECTIVE

After studying this course, you should be able to:

a. Describe the key points of the waterfall model applied
to database development
b. Appreciate the roles of various development artefacts,
such as the data requirements document, conceptual
data model and such like used to communicate
between activities in the database development life
cycle
c. Understand 3 tier schema/architecture
d. Define: Data independence, instance and schema
 Introduction

Database development normally

occurs within the context of information
systems development. Information
systems development is a key
organisational process for many
organisations (Beynon-Davies, 2002).
Desirable Properties of a Database
Database Development Life Cycle

Database development is just one

part of the much wider field of software
engineering, the process of developing
and maintaining software.
SDLC and DBLC
3 assumptions based on the model

●We can separate the development of

a database – that is, specification and
creation of a schema to define data in
a database – from the user processes
that make use of the database.
3 assumptions based on the model

●We can use the three-schema

architecture as a basis for
distinguishing the activities associated
with a schema.
3 assumptions based on the model

●We can represent the constraints to

enforce the semantics of the data
once, within a database, rather than
within every user process that uses
the data.
 Requirements Gathering
●The data requirements document is
used to agree requirements with
users.
●The document should give a concise
summary of all users' requirements –
not just a collection of individuals'
requirements – as the intention is to
develop a single shared database.
Example
 Analysis
●Data analysis begins with the statement of
data requirements and then produces a
conceptual data model.
●The aim of analysis is to obtain a detailed
description of the data that will suit user
requirements so that both high and low
level properties of data and their use are
dealt with.
 Analysis
●A conceptual data model is concerned with
the meaning and structure of data, but not
with the details affecting how they are
implemented.
●The conceptual data model then is a
formal representation of what data a
database should contain and the
constraints the data must satisfy.
 Analysis
●Analysis focuses on ‘What is required?’ not
‘How is it achieved?’
●The aim of analysis is to obtain a detailed
description of the data that will suit user
requirements so that both high and low
level properties of data and their use are
dealt with.
 Analysis
The following are the steps in the Analysis
Phase.

1. Analyze the organization

2. Define any problems, possibilities or
constraints
3. Define the objectives
4. Agree on the scope
 Example
●In our previous example
Exercise
Exercise
Exercise
Exercise
 Design
●Database design starts with a conceptual data
model and produces a specification of a logical
schema; this will usually determine the specific
type of database system (network, relational,
object-oriented) that is required, but not the
detailed implementation of that design (which will
depend on the operating environment for the
database such as the specific DBMS available).
 Design
●The output of the design stage is a detailed
relational specification, the logical schema, of all
the tables and constraints needed to satisfy the
description of the data in the conceptual data
model.
3 General Points basis for Design
●First, for a given conceptual data model it is not
necessary that all the user requirements it
represents have to be satisfied by a single
database.
●Second, remember that one of the assumptions
about our database development is that we can
separate the development of a database from
the development of user processes that make
use of it.
3 General Points basis for Design
●Third, at a detailed level, many aspects of
database design and implementation depend on
the particular DBMS being used.
 Design
This phase consists of three parts: the conceptual
design, the logical design and the physical design.

a. Conceptual - The purpose of the conceptual

design phase is to build a conceptual model based
upon the previously identified requirements, but
closer to the final physical model. A commonly-
used conceptual model is called an entity-
relationship model.
Design
 Design
This phase consists of three parts: the conceptual
design, the logical design and the physical design.

b. Logical - defines HOW the system should be

implemented regardless of the DBMS. This phase
is typically created by Data Architects and Business
Analysts. The purpose is to developed technical
map of rules and data structures.
Design
 Design
This phase consists of three parts: the conceptual
design, the logical design and the physical design.

c. Physical - describes HOW the system will be

implemented using a specific DBMS system. This
model is typically created by DBA and developers.
The purpose is actual implementation of the
database.
Design
Design
 Implementation
●Implementation involves the construction of a
database according to the specification of a
logical schema. This will include the
specification of an appropriate storage schema,
security enforcement, external schema, and so
on. Implementation is heavily influenced by the
choice of available DBMS, database tools and
operating environment.
 Implementation
The following are steps in the implementation phase:

1. Install the DBMS.

2. Tune the setup variables according to the
hardware, software and usage conditions.
3. Create the database and tables.
4. Load the data.
5. Set up the users and security.
6. Implement the backup regime.
 Testing
●The aim of testing is to uncover errors in
the design and implementation of the
database, its structure, constraints and
associated user and management
support.
●Testing is usually considered to involve
two main tasks – validation and
verification.
 Testing
● Validation answers the question: has
the right database been developed to
meet the requirements?
● It attempts to confirm that the right
database has been constructed, with
the right characteristics to meet the
specified requirements.
 Testing
● Verification answers the question: has the
database design been implemented correctly?
● Verification ensures that the processing steps,
constraints and other ‘programmed’
components of the database (security,
backup, recovery, audit trails, etc.) have been
correctly implemented and contain no errors
in program logic or execution sequences.
 Testing
The following are the steps in the testing phase:

1. Test the performance

2. Test the security
3. Test the data integrity
4. Fine-tune the parameters or modify the logical
or physical designs in response to the tests.
 Maintenance
● Databases are one of the more enduring
software engineering artefacts; it is not
uncommon to find database implementations
whose use can be traced back for 15 years or
more.
● Consequently, maintenance of the database
is a key issue.
 Maintenance
3 Forms of Maintenance:
● Operational maintenance, where the
performance of the database is monitored.
● Porting and implementation maintenance,
in which the DBMS, the user processes, the
underlying computer system or some other
aspect undergoes changes that require the
database implementation to be revised.
 Maintenance
3 Forms of Maintenance:
● Requirements change, where the original
requirement specification changes, usually
because databases are frequently used for
purposes for which they were not originally
designed.
 Maintenance
The following are the steps in the maintenance
phase:

1. Maintain the indexes

2. Maintain the tables
3. Maintain the users
4. Change passwords
5. Backup
6. Restore backups
7. Change the design to meet new requirements
 3 Tier Architecture
●The main objective of this architecture
is to have an effective separation
between the user interface and the
physical database. So, the user never
has to be concerned regarding the
internal storage of the database and it
has a simplified interaction with the
database system.
 3 Tier Architecture
The three-schema architecture defines
the view of data at three levels:

●Physical level (internal level)

●Logical level (conceptual level)
●View level (external level)
3 Tier Architecture
 3 Tier Architecture
The physical or the internal level
schema describes how the data is
stored in the hardware. It also
describes how the data can be
accessed. The physical level shows the
data abstraction at the lowest level and
it has complex data structures. Only the
database administrator operates at this
level.
 3 Tier Architecture
Here, the data is stored in the form of
the entity set, entities, their data types,
the relationship among the entity sets,
user operations performed to retrieve
or modify the data and certain
constraints on the data.
It is the developer and database
administrator who operates at the
logical or the conceptual level.
 3 Tier Architecture
It is the highest level of data
abstraction and exhibits only a part
of the whole database. It exhibits
the data in which the user is
interested. The view level can
describe many views of the same
data. Here, the user retrieves the
information using different
application from the database.
3 Tier Architecture
3 Tier Architecture
 3 Tier Architecture - Example
Database: College
Entity: Student, Lecturer, Department,
Course and so on…
Entity sets Student, Lecturer, Department, Course will be
stored in the storage as the consecutive blocks of the
memory location. This is the physical or internal level
and is hidden from the programmers but the database
administrator is it aware of it.
 3 Tier Architecture - Example
Database: College
Entity: Student, Lecturer, Department,
Course and so on…

At the logical level, the programmers define the entity sets

and relationship among these entity sets using a programming
language like SQL. So, the programmers work at the logical
level and even the database administrator also operates at this
level.
 3 Tier Architecture - Example
Database: College
Entity: Student, Lecturer, Department,
Course and so on…

At the view level, the users have the set of

applications which they use to retrieve the data they
are interested in.
 Data Independence
Data independence defines the
extent to which the data schema
can be changed at one level
without modifying the data
schema at the next level.

Can be classified into (2) two:

● Logical Data Independence
● Physical Data Independence
 Data Independence
● Logical Data Independence - describes the
degree up to which the logical or conceptual
schema can be changed without modifying the
external schema.
● Changes to data schema at the logical level are
made either to enlarge or reduce the database
by adding or deleting more entities, entity sets,
or changing the constraints on data.
 Data Independence
● Physical Data Independence - defines the
extent up to which the data schema can be
changed at the physical or internal level without
modifying the data schema at logical and view
level.
● Physical schema is changed if we add additional
storage to the system or we reorganize some
files to enhance the retrieval speed of the
records.
 Instance
What is an instance?

An instance is the retrieval of information

from the database at a certain point of time.
An instance in a database keeps on
changing with time.
 Schema
In its literal meaning, the term schema is
called “form” or “shape” of the database
which shows that how the data is organized
in database.

A schema is a representation of design or

idea in the shape of model. A database may
contain one or multiple schemas.
 Schema
● A database schema consists of objects like
tables, views and stored procedure etc. In other
words, we can say that database schema is a
container of objects.
● Database schema is the skeleton of a
database.
● Database schema is designed before creation
of database. A database schema does not
contain any information or data.
 Schema Diagram

It shows three tables, along

with their data types,
relationships between the
tables and primary keys as
well as foreign keys.
Any questions?