Unit 2: Introduction to Database

Session 4: Degrees of Data Abstraction in Data Modelling

Unit 3: Database Analysis and Design

Session 5: Database system development life cycle

A collection of software
- manage different applications for a multi-user database system
- enable users to define/create and manipulate data

Basic functions

• multiple user interfaces

• controlled redundancy
• integrity control
• security: authorization &
protection
• concurrency & recovery
control
• MySQL
• PostgreSQL
• Microsoft Access
• SQL ServerFileMaker
• Oracle
• dBASE
• Clipper
• FoxPro
Data modeling / Conceptual Design is the first step in the process of
database design.

Data Model
• Collection of concepts that describe the structure of a database
• Provides means to achieve data abstraction
• Basic operations
Specify retrievals and updates on the database
• Dynamic aspect or behavior of a database application
Allows the database designer to specify a set of valid
operations allowed on database objects
• High-level / Conceptual data model
- Close to the way many users
perceive data

• Low-level / Physical data model

- Describe the details of how data is
stored on computer storage media

• Representational / Implementation
data model
- Easily understood by end users
- Similar to how data organized in
computer storage
High Level / Conceptual Data Model
Entity
Represents a real-world object or concept

Attribute
Represents some property of interest
Further describes an entity

Relationship among two or more entities

Represents an association among the entities Entity-Relationship
model
Low Level / Physical Data Model
Access path
Structure that makes the search for particular database records efficient

Index
-Example of an access path
-Allows direct access to data
using an index term or a
keyword
Database Architecture
Centralized DBMSs Architecture
All DBMS functionality,
application program
execution, and user interface
processing carried out on
one machine.
Database Architecture
Two-Tier Client/Server Architecture
Server handles Query and transaction
functionality related to SQL processing.

Client handles User interface programs and

application programs

Three-Tier Client/Server Architecture

Server handles Query and transaction functionality
related to SQL processing.

Application server or Web server

Adds intermediate layer between client and the
database server.
Runs application programs and stores business
rules.
Client handles User interface programs and
application programs
Database Architecture Levels (Views)
Three level ANSI_SPARC Architecture

External / View level

-Describes part of the
database that a
particular user group is
interested in

Conceptual level
- Describes structure of
the whole database for
a community of users

Internal level
- Describes physical
storage structure of the
database
Data Abstraction Layer
An abstraction layer or abstraction level is a way of hiding the working details of a
subsystem, allowing the separation of concerns to facilitate interoperability and
platform independence.
Schema
A schema is an overall description of a database, and it is usually represented by the
entity relationship diagram (ERD). The data is physically stored in files that may be in
unstructured form, but to retrieve it and use it, we need to put it in a structured form.
To do this, a database schema is used

• External schema
• describes the end-user interaction with the database systems.
• Conceptual / Logical schema
• is the conceptual model of the database and specifies all the logical constraints
that need to be applied to the stored data.
• represents how the data is stored in the form of tables (entities) and how the
attributes of a table are linked together (relationships).
• Physical schema
• represents how data is stored on a storage system or disk storage in the form of
Files and Indices.
• usually indicates the storage allocation, which is defined in terms of GBs or TBs
• designing a database at the physical level is called a physical schema.
Capacity to change the schema at one level of a database system without having to
change the schema at the next higher level.

Data independence helps

you to keep data separated
from all programs that make
use of it.

In DBMS there are two types

of data independence.
1. Physical data
independence
2. Logical data
independence
Any change done for the conceptual schema should not affect to the external level.
Ability to change the conceptual schema without changing;
External views
External API programmes
Any change done for the physical level should not affect to the conceptual schema
Database Development Process

Requirements

Analysis

Design

Implementation

Testing

Maintenance
Database Life Cycle
Requirements

Data Analysis

Database Design

Implementation

Testing

Maintenance
Database Life Cycle
Phase 1 - Requirements Gathering
• Detailed requirements of the
software system to be developed are
gathered from client.
• Interview users
• Provide questionnaires to users
and obtain answers
• Conduct a brainstorming session
• Document Analysis/Revie.
• Observation
• Prototyping
• Work in the target environment.

• Do the requirements feasibility test

to ensure that the requirements are
testable or not.
Database Life Cycle
Phase 2 – Data Analysis
• Analysis of the existing system data
• Analysis of the intended systems data
• Statement of data requirements
• Produces a conceptual data model
• Examples: Use Case Diagrams
ER diagrams
Database Life Cycle
Phase 3 – Database Design
As per the requirements, create the logical design of the database
Capture the hardware / software requirements – specify particular database to be used
Define the tables and constraints
Document the designs
Database Life Cycle
Phase 4 - Implementation

Construction of a Specification of Create the file index and Populating the tables
database according to the appropriate the design of the with data
specification of a logical storage schema filesystem
schema security enforcement
external schema
Database Life Cycle
Phase 5 - Testing
Test the DBMS to verify that it is built as per the specifications given by the client and
uncover errors in the design and implementation of the database.
During the database testing following Involves validating:
database activities are covered: • the schema
• Testing data integrity • database tables
• Checking data validity • columns
• Performance check relate • keys and indexes
• Triggers and Functions in the database • stored procedures triggers
• Testing various procedures • database server validations
• validating data duplication
The database testing will
ensure the ACID properties
of the transaction.
• Atomicity
• Consistency
• Isolation
• Durability
Database Life Cycle
Phase 6 - Maintenance
Process which is used to maintain the smooth functioning
of the Database.
• database integrity checks
• optimization of the index
• data backup
• implementation of scripts
• database maintenance plans
Maintenance can take three main forms:
1. Operational maintenance
• performance of the database is monitored
2. Porting and implementation maintenance
• the DBMS, the user processes, the underlying computer system or some other
aspect undergoes changes that require the database implementation to be revised
3. Requirements change
• involves restructuring and the development of changes to meet the new
requirements