Database

Functions
CHAPTER 2. CONTEXT FOR DATABASE MANAGEMENT
Database Functions
 A Database Management Systems (DBMS)
performs several important functions that
guarantee the integrity and consistency of the
data in the database.
 Most of those functions are transparent to end
users, and most can be achieved only through the
use of a DBMS.
Database Functions
Database functions includes the following:
1. Data dictionary management
2. Data storage management
3. Data transformation and presentation
4. Security management
5. Multiuser access control
6. Backup and recovery management
7. Data integrity management
8. Database access languages and application
programming interfaces
9. Database communication interfaces
Database Functions

Each of these functions is explained

below:
1. Data dictionary management.
 TheDBMS stores definitions of the data elements
and their relationships (metadata) in a data
dictionary. In turn, all programs that access the
data in the database work through the DBMS.
 TheDBMS uses the data dictionary to look up the
required data component structures and
relationships, thus relieving you from having to
code such complex relationships in each
program.
Example: This figure shows how Microsoft SQL Server
Express presents the data definition for the CUSTOMER
table.

Illustrating
metadata with
Microsoft SQL
Server Express

METADATA
2. Data storage management
 The DBMS creates and manages the complex
structures required for data storage, thus relieving
you from the difficult task of defining and
programming the physical data characteristics.
A modern DBMS provides storage not only for the
data, but also for related data entry forms or
screen definitions, report definitions, data
validation rules, procedural code, structures to
handle video and picture formats, and so on.
 Datastorage management is also important for
database performance tuning.
Performance tuning
 relates to the activities that make the database
perform more efficiently in terms of storage and
access speed.
 although the user sees the database as a single
data storage unit, the DBMS actually stores the
database in multiple physical data files.
 Illustrating data storage management with
Oracle
In this figure, such data files may even be stored on different storage media.
Therefore, the DBMS doesn’t have to wait for one disk request to finish before the
next one starts. In other words, the DBMS can fulfill database requests concurrently.
3. Data transformation and presentation
 TheDBMS transforms entered data to conform to
required data structures.
 The DBMS relieves you of the chore of making a
distinction between the logical data format and
the physical data format.
4. Security management
The DBMS creates a security system that enforces
user security and data privacy.
Security rules determine which users can access
the database, which data items each user can
access, and which data operations (read, add,
delete, or modify) the user can perform.
5. Multiuser access control
To provide data integrity and data consistency,
the DBMS uses sophisticated algorithms to ensure
that multiple users can access the database
concurrently without compromising the integrity of
the database.
6. Backup and recovery management
The DBMS provides backup and data recovery to
ensure data safety and integrity. Current DBMS
systems provide special utilities that allow the DBA
to perform routine and special backup and
restore procedures.
7. Data integrity management
The DBMS promotes and enforces integrity rules,
thus minimizing data redundancy and maximizing
data consistency.
The data relationships stored in the data dictionary
are used to enforce data integrity.
Ensuring data integrity is especially important in
transaction-oriented database systems.
8. Database access languages and application
programming interfaces
The DBMS provides data access through a query
language.
A query language is a nonprocedural language -
one that lets the user specify what must be done
without having to specify how it is to be done.
StructuredQuery Language (SQL) is the de facto
query language and data access standard
supported by the majority of DBMS vendors.
9. Database communication interfaces
Current-generation DBMSs accept end-user
requests via multiple, different network
environments.

Example: the DBMS might provide access to the

database via the Internet through the use of
Web browsers such as Mozilla Firefox or Microsoft
Internet Explorer or Google chrome.
In this environment, communications can be
accomplished in several ways:
 End users can generate answers to queries by
filling in screen forms through their preferred Web
browser.
 The DBMS can automatically publish predefined
reports on a Website.
 The DBMS can connect to third-party systems to
distribute information via e-mail or other
productivity applications.
Database
Development
Process
CHAPTER 2. CONTEXT FOR DATABASE MANAGEMENT
 A core aspect of software engineering is the
subdivision of the development process into a series
of phases, or steps, each of which focuses on one
aspect of the development.
 The collection of these steps is sometimes referred to
as the software development life cycle (SDLC).
 The software product moves through this life cycle
(sometimes repeatedly as it is refined or
redeveloped) until it is finally retired from use.
 Ideally, each phase in the life cycle can be
checked for correctness before moving on to the
next phase.
 1 Software Development Life Cycle

Establishing
requirements

Statement of
requirements

analysis

System
specification

Waterfall design

model design
document

implementation

initial system

testing

released
system maintenance
SDLC - Waterfall
 The waterfall figure above illustrates a general
waterfall model which could apply to any computer
system development.
 It shows the process as a strict sequence of steps
where the output of one step is the input to the next
and all of one step has to be completed before
moving onto the next.
 We can use the waterfall process as a means of
identifying the tasks that are required, together with
the input and output for each activity.
The scope of the activities are important, which can
be summarized as follows:
 Establishing requirements: This involves consultation
with, and agreement among, stakeholders as to
what they want of a system, expressed as a
statement of requirements.
 Analysis: This starts by considering the statement of
requirements and finishes by producing a system
specification.
 Design: This begins with a system specification and
produces design documents, and provides a
detailed description of how a system should be
constructed.
 Implementation: This is the construction of a
computer system according to a given design
document and taking account of the environment
in which the system will be operating (for example
specific hardware or software available for the
development).
 Testing: This compares the implemented system
against the design documents and requirements
specification and produces an acceptance report
or, more usually, a list of errors and bugs that require
a review of the analysis, design and
implementation processes to correct.
 Maintenance: This involves dealing with changes in
the requirements, or the implementation
environment, bug fixing or porting of the system to
new environments.
 2 Database Life Cycle
Establishing
We can use the waterfall cycle as the requirements
basis for a model of database
development which incorporates three Statement of
assumptions: requirements

 We can separate the development data analysis

of a database – that is, specification
and creation of a schema to define System
specification
data in a database – from the user
processes that make use of the database design A waterfall model of the
database. activities and their
outputs for database
 We can use the three-schema design development
document
architecture as a basis for
distinguishing the activities Implementation
associated with a schema.
 We can represent the constraints to initial schema
enforce the semantics of the data and database

once, within a database, rather than testing

within every user process that uses
the data. released
schema and
maintenance
database
3 Requirement Gathering

Steps that database designers need to do during

requirements gathering:
1. interview the customers (database users) to
understand the proposed system
2. obtain and document the data and functional
requirements (the result of this step is a document
including the detail requirements provided by the
users)
Requirement Gathering
cont..
 Establishing requirements involves consultation with,
and agreement among, all the users as to what
persistent data they want to store along with an
agreement as to the meaning and interpretation of
the data elements. The data administrator plays a
key role in this process as they overview the
business, legal and ethical issues within the
organization that impact on the data requirements.
Requirement Gathering
cont..
 The data requirements document is used to confirm
the understanding of requirements with users. To
make sure that it is easily understood, it should not
be overly formal or highly encoded.
 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.
4 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.
 These include properties such as the possible range
of values that can be permitted for attributes.
Example: School Database - the Student course
code, course title and credit points.
Analysis cont..
 The conceptual data model provides a shared,
formal representation of what is being
communicated between clients and developers
during database development.
 The conceptual data model then is a formal
representation of what data a database should
contain and the constraints the data must satisfy.
 This should be expressed in terms that are
independent of how the model may be
implemented. As a result, analysis focuses on
‘What is required?’ not ‘How is it achieved?
5 Logical Design
 Database design starts with a conceptual data
model and produces a specification of a logical
schema; this will determine the specific type of
database system (network, relational, object-
oriented) that is required.
 We can use a relational representation of the
conceptual data model as input to the logical
design process.
 The output of this 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.
 Use a relational representation of
the conceptual data model to
give a set of tables for a first-cut
database design
This figure summarizes
the iterative Do the tables represent the data in
(repeated) steps an acceptable way according to
the given criteria for usability,
involved in database efficiency, and so on?

design, based on the

Yes No
overview given.

Define constituents of Flex tables to produce

schema for each table such second-cut design
as:
• columns
• primary keys
• foreign keys
• constraints

Implementation

A summary of the iterative steps involved in database design

6 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 DBMSs,
database tools and operating environment.
7 Realizing the Design

 After the logical design has been created, we need

our database to be created according to the
definitions we have produced.
 For an implementation with a relational DBMS, this
will probably involve the use of SQL to create tables
and constraints that satisfy the logical schema
description and the choice of appropriate storage
schema (if the DBMS permits that level of control).
 One way to achieve this is to write the appropriate
SQL DDL statements into a file that can be executed
by a DBMS so that there is an independent record, a
text file, of the SQL statements defining the
database.
 Another method is to work interactively using a
database tool like SQL Server Management Studio
or Microsoft Access.
8 Populating the Database

 After a database has been created, there are two

ways of populating the tables – either from 1) existing
data or 2) through the use of the user applications
developed for the database.
 For some tables, there may be existing data from
another database or data files.
Example:
 In establishing a database for a hospital, you would
expect that there are already some records of all
the staff that have to be included in the database.
 Data might also be brought in from an outside
agency (address lists are frequently brought in from
external companies) or produced during a large
data entry task (converting hard-copy manual
records into computer files can be done by a data
entry agency).
 In such situations, the simplest approach to
populate the database is to use the import and
export facilities found in the DBMS.
 Facilities to import and export data in various
standard formats are usually available (these
functions are also known in some systems as loading
and unloading data).
 Importing enables a file of data to be copied
directly into a table.
 When data are held in a file format that is not
appropriate for using the import function, then it is
necessary to prepare an application program that
reads in the old data, transforms them as necessary
and then inserts them into the database using SQL
code specifically produced for that purpose.
 The transfer of large quantities of existing data into a
database is referred to as a bulk load.
 Bulk loading of data may involve very large
quantities of data being loaded, one table at a
time so you may find that there are DBMS facilities to
postpone constraint checking until the end of the
bulk loading.
Guidelines for Developing an
ER Diagram
Note: These are general guidelines that will assist in
developing a strong basis for the actual database design
(the logical model).
1. Document all entities discovered during the
information-gathering stage.
2. Document all attributes that belong to each entity.
Select candidate and primary keys. Ensure that all
non-key attributes for each entity are full-functionally
dependent on the primary key.
3. Develop an initial ER diagram and review it with
appropriate personnel. (Remember that this is an
iterative process.)
4. Create new entities (tables) for multivalued attributes
and repeating groups. Incorporate these new entities
(tables) in the ER diagram. Review with appropriate
personnel.
5. Verify ER modeling by normalizing tables.
Summary
 The database development is a process of
designing, implementing, and maintaining a
database system to meet the strategic or
operational needs of an organization or enterprise
such as improved customer support and customer
satisfaction, better production management in an
organization, better inventory management and
more accurate sales forecasting.
 Database development process is useful develop
different types of relations between data in an
organization, which may be further easily accessed
by the user or the administrator.
Activity No. 2
1. On your understanding describe the waterfall
model and list the steps.
2. What is SDLC mean, and what does an SDLC
represent?
3. What needs to be modified in the waterfall
model to accommodate database design?
4. Provide the iterative or repeated steps involved
in database design.