Database systems

Lesson One
The Database Environment and Development
Process
 The Database Environment and Development Process

Learning Objectives:
• Define terms
• File based systems
• Explain advantages and disadvantages of databases
• List components of database environment
• Identify categories of database applications
• Explain roles of individuals
• Explain differences between personal, multi-tiered, and
enterprise data management
• Explain three-schema architectures (external, conceptual,
internal)
 Definitions
• Database: organized collection of logically related data
• Data: stored representations of meaningful objects and
events
– Structured: numbers, text, dates
– Unstructured: images, video, documents
• Information: data processed to increase knowledge in the
person using the data
• Metadata: data that describes the properties and context of
user data
 Figure :Converting Data to Information (1 of 2)
(a) Data in context
 Figure:Converting Data to Information (2 of 2)
(b) Summarized data
 Table 1-1 Example Metadata for Class Roster
• Descriptions of the properties or characteristics of the data, including data
types, field sizes, allowable values, and data context
Name Type Length Mi Ma Description Source
n imum ximum

Course Alphanume 30 Blank Blank

Course ID and name Academic Unit
ric
Section Integer 1 1 9 Section number Registrar

Semest Alphanume 10 Blank Blank

Semester and year Registrar
er ric
Name Alphanume 30 Blank Blank
Student name Student IS
ric
ID Integer 9 Blank Blank
Student ID (SSN) Student IS

Major Alphanume 4 Blank Blank

Student major Student IS
ric
G PA Decimal 3 0.0 4.0 Student grade point Academic Unit
average
 File based systems-File processing(Before
databases)
• A file-based system is a collection of application
programs that perform services for the users wishing
to access information.
• Each program within a file-based system defines and
manages its own data. Because of this, there are limits
as to how that data can be used or transported.
• File based systems were developed as better
alternatives to paper-based filing systems.
• By having files stored on computers, the data could be
accessed more efficiently. It was common practice for
larger companies to have each of its departments
looking after its own data.
Figure: Old File Processing Systems at Pine Valley
Furniture Company
Limitations/Disadvantages of File-based
• Program-Data Dependence Approach
– Each program maintains its own set of data.
– Users of one program may be unaware of potentially useful data held by other programs.
• Duplication of data
– Same data is held by different programs.
– Wasted space and potentially different values and/or different formats for the same
item.
• Data dependence
– File structure is defined in the program code.
• Incompatible file formats/Lengthy Development Times
– Programs are written in different languages, and so cannot easily access each others
files.
• Fixed Queries/Proliferation of application programs
• Programs are written to satisfy particular functions. Any new requirement needs
a new program
• Excessive Program Maintenance
• 80% of information systems budget
 The Database Approach
• DB:
– Shared collection of logically related data (and a description of this
data), designed to meet the information needs of an organization.
– System catalog (metadata) provides description of data to enable
program–data independence.
– Logically related data comprises entities, attributes, and
relationships of an organization's information.
• DBMS:
– A software system that enables users to define, create, and maintain
the database and which provides controlled access to this database.
• DBMS is the software that interacts with the users’ application
programs and the data
 The Database Approach (2 of 2)
• Relational Databases
• Database technology involving tables (relations) representing entities and
primary/foreign keys representing relationships (see Figure 1-17)
 Functions of a DBMS

• Data Storage Management

The DBMS creates the complex structures required for
data storage. The users are freed from
defining,programming and implementing the complex
physical data characteristics.
• Multiuser Access Control
The DBMS ensures that multiple users can access the
database concurrently without compromising the
integrity of the database. Hence the database ensures
data integrity and data consistency.
• Backup and Recovery Management
The DBMS provide backup and data recovery procedures
to ensure data safety and integrity. DBMS system provide
special utilities which allow the DBA to perform routine
and special backup and restore procedures. Recovery
Management deals with the recovery of the database
after a failure
• Data Integrity Management
The DBMS promotes and enforce integrity rules to
eliminate data integrity problems, thus minimizing the
data redundancy and maximizing data consistency.
• 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. This is
especially important in multiuser database systems.
• 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.
Structured Query Language (SQL) is the de facto query language and
data access standard supported by the majority of DBMS vendors.
• Database communication interfaces.
Current-generation DBMSs accept end-user requests via multiple,
different network environments. For 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. 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.
 Advantages of DBMS

• Controlling Redundancy
• In file systems each application program has its own private files. In this
case, the duplicated copies of the same data is created in many places.
In DBMS, all data of an organization is integrated into a single database
file. The data is recorded in only one place in the database and it is not
duplicated.

• Sharing of Data
• In DBMS, data can be shared by authorized users of the organization.
The database administrator manages the data and gives rights to users
to access the data. Many users can be authorized to access the same
piece of information simultaneously. The remote users can also share
same data. Similarly, the data of same database can be shared between
different application programs.
• Data Consistency
• By controlling the data redundancy, the data consistency is
obtained. If a data item appears only once, any update to its
value has to be performed only once and the updated value is
immediately available to all users. If the DBMS has controlled
redundancy, the database system enforces consistency.

• Integration of Data
• In Database management system, data in database is stored in
tables. A single database contains multiple tables and
relationships can be created between tables (or associated data
entities). This makes easy to retrieve and update data.
• Integration Constraints
• Integrity constraints or consistency rules can be applied to database so
that the correct data can be entered into database. The constraints may
be applied to data item within a single record or the may be applied to
relationships between records.
• Data Security
• Form is very important object of DBMS. You can create forms very
easily and quickly in DBMS. Once a form is created, it can be used many
times and it can be modified very easily. The created forms are also
saved along with database and behave like a software component. A
form provides very easy way (user-friendly) to enter data into database,
edit data and display data from database. The non-technical users can
also perform various operations on database through forms without
going into technical details of a fatabase.
• Report Writers
• Most of the DBMSs provide the report writer tools used to create
reports. The users can create very easily and quickly. Once a report is
created, it can be used may times and it can be modified very easily. The
created reports are also saved along with database and behave like a
software component.
• Control Over Concurrency
• In a computer file-based system, if two users are allowed to access data
simultaneously, it is possible that they will interfere with each other. For
example, if both users attempt to perform update operation on the same
record, then one may overwrite the values recorded by the other. Most
database management systems have sub-systems to control the
concurrency so that transactions are always recorded with accuracy.
• Backup and Recovery Procedures
• In a computer file-based system, the user creates the backup of
data regularly to protect the valuable data from damage due to
failures to the computer system or application program. It is very
time consuming method, if amount of data is large. Most of the
DBMSs provide the 'backup and recovery' sub-systems that
automatically create the backup of data and restore data if
required.
• Data Independence
• The separation of data structure of database from the application
program that uses the data is called data independence. In DBMS,
you can easily change the structure of database without modifying
the application program.
 The Main disadvantages of DBMS
• Cost of Hardware and Software
• A processor with high speed of data processing and memory of large
size is required to run the DBMS software. It means that you have to
up grade the hardware used for file-based system. Similarly, DBMS
software is also very costly,.
• Cost of Data Conversion
• When a computer file-based system is replaced with database
system, the data stored into data file must be converted to database
file. It is very difficult and costly method to convert data of data file
into database. You have to hire database system designers along
with application programmers. Alternatively, you have to take the
services of some software house. So a lot of money has to be paid
for developing software.
• Cost of Staff Training
• Most database management system are often complex systems so the training
for users to use the DBMS is required. Training is required at all levels, including
programming, application development, and database administration. The
organization has to be paid a lot of amount for the training of staff to run the
DBMS.
• Appointing Technical Staff
• The trained technical persons such as database administrator, application
programmers, data entry operations etc. are required to handle the DBMS. You
have to pay handsome salaries to these persons. Therefore, the system cost
increases.
• Database Damage
• In most of the organization, all data is integrated into a single database. If
database is damaged due to electric failure or database is corrupted on the
storage media, the your valuable data may be lost forever.
Components of the Database Environment
• Data modeling and design tools – automated tools used to design
databases and application programs
• Repository – centralized storehouse of metadata
• Database Management System (DBMS) – software for managing the
database
• Database – storehouse of the data
• Application Programs – software using the data
• User Interface – text, graphical displays, menus, etc. for user
• Data/Database Administrators – personnel responsible for maintaining
the database
• System Developers – personnel responsible for designing databases
and software
• End Users – people who use the applications and databases
Figure: Components of the Database
Environment
 Evolution of Database Systems
• Driven by four main objectives:
– Need for program-data independence in order to reduce
maintenance
– Desire to manage more complex data types and structures
– Ease of data access for less technical personnel
– Need for more powerful decision support platforms
 Examples of Database Applications:
• Banking: transactions
• Airlines: reservations, schedules
• Universities: registration, grades
• Sales: customers, products, purchases
• Online retailers: order tracking, customized
recommendations
• Manufacturing: production, inventory, orders, supply
chain
• Human resources: employee records, salaries, tax
deductions
 The Range of Database Applications
• Personal Databases
– Typical size in the megabytes
– Intended for one user
• Departmental Multi-Tiered Client/Server Databases
– Typical size in the gigabytes
– Intended for several users, usually doesn’t exceed 100,
department-wide
• Enterprise Applications
– Typical size in the gigabytes, terabytes, or even petabytes
– Intended for a very large user base, company wide
Figure: Multi-Tiered Client/Server Database
Architecture
 Types of Enterprise Applications
• Enterprise Systems (typically involve relational databases)
– Backbone of an organization
– Enterprise resource planning (ERP)
– Customer relationship management
– Supply chain management
– Human resource management and payroll
• Data Warehouses (typically involve relational databases)
– Integrates data from multiple data sources
– Maintain historical data
– Help identify patterns and trends
• Data Lakes (often don’t involve relational databases)
– Large integrated repository for internal and external data that does not follow
a predefined schema
 Views

• A view mechanism.
– Provides users with only the data they want or
need to use.
• Allows each user to have his or her own view
of the database.
• A view is essentially some subset of the
database.
 Benefits include:

– Reduce complexity;
– Provide a level of security;
– Provide a mechanism to customize the
appearance of the database;
– Present a consistent, unchanging picture of the
structure of the database, even if the underlying
database is changed.
ANSI SPARC 3 Level Database Architecture

• ANSI SPARC is an acronym for the American

National Standard Institute Standard Planning
and Requirements Committee. A standard
three level approach to database design has
been agreed.

- External level
- Conceptual level
- Internal level (includes physical data storage)
• The 3 Level Architecture has the aim of enabling
users to access the same data but with a
personalised view of it. The distancing of the
internal level from the external level means that
users do not need to know how the data is
physically stored in the database. This level
separation also allows the Database
Administrator (DBA) to change the database
storage structures without affecting the users'
views.
• External Level (User Views)

A user's view of the database describes a part of the database that is relevant to a
particular user. It excludes irrelevant data as well as data which the user is not
authorised to access.

Conceptual Level

The conceptual level is a way of describing what data is stored within the whole
database and how the data is inter-related. The conceptual level does not specify
how the data is physically stored.

Internal Level

The internal level involves how the database is physically represented on the
computer system. It describes how the data is actually stored in the database and
on the computer hardware.
 Database Schema
• The database schema provide an overall description of the
database structure (not actual data). There are three types
of schema which relate to the 3 Level Database
Architecture.

External Schemas or sub schemas relate to the user views.

The Conceptual Schema describes all the types of data that
appear in the database and the relationships between data
items. Integrity constraints are also specified in the
conceptual schema. The Internal Schema provides
definitions for stored records, methods of representation,
data fields, indexes, and hashing schemes etc...
 Users and their Roles

• Database Designers - designs conceptual and logical database

• Application Developers - writes application programs that
use the database
• Data and Database Administrator
• End - user - interacts with the system from an on-line
terminal by using Query Languages etc.
• Data and Database Administration
• Data Administrator – Is a business manager responsible for
controlling the overall corporate data resources
• Database Administrator (DBA) – Is a technical person
responsible for development of the total system
 The Database Development Process
• SDLC
– System Development Life Cycle
– Detailed, well-planned development process
– Time-consuming, but comprehensive
– Long development cycle
• Prototyping
– Rapid application development (RAD)
– Cursory attempt at conceptual data modeling
– Define database during development of initial prototype
– Repeat implementation and maintenance activities with new
prototype versions
Systems Development Life Cycle (SDLC)
• The traditional methodology used to develop, maintain, and replace
information systems
• Five main steps:
1. Planning – preliminary understanding of business situation. Enterprise
model and conceptual data modeling.
2. Analysis – thorough analysis of business situation, leading to
functional requirements. Detailed conceptual data modeling.
3. Design – logical and physical database design, to develop technology
and organization.
4. Implementation – writing programs, building databases, testing,
installing, training, and documenting.
5. Maintenance – monitoring, repairing, and enhancing.
 ASSIGNMENT
Discuss the Data Models Below
• Flat Model
• Hierarchical Model
• Network Model
• Relational Model
• Object-Oriented Model
• Object/Relational Model
• Semi-structured Model
 Database Schema

• A database schema is the skeleton structure that

represents the logical view of the entire database. It
defines how the data is organized and how the relations
among them are associated. It formulates all the
constraints that are to be applied on the data.
• A database schema defines its entities and the
relationship among them. It contains a descriptive detail
of the database, which can be depicted by means of
schema diagrams. It’s the database designers who
design the schema to help programmers understand
the database and make it useful.
• A database schema can be divided broadly into two
categories −
• Physical Database Schema − This schema pertains
to the actual storage of data and its form of storage
like files, indices, etc. It defines how the data will be
stored in a secondary storage.
• Logical Database Schema − This schema defines all
the logical constraints that need to be applied on
the data stored. It defines tables, views, and
integrity constraints.
 Database Instance

• It is important that we distinguish these two terms

individually. Database schema is the skeleton of database. It
is designed when the database doesn't exist at all. Once the
database is operational, it is very difficult to make any
changes to it. A database schema does not contain any data
or information.
• A database instance is a state of operational database with
data at any given time. It contains a snapshot of the
database. Database instances tend to change with time. A
DBMS ensures that its every instance (state) is in a valid
state, by diligently following all the validations, constraints,
and conditions that the database designers have imposed.
 Data Independence

• A database system normally contains a lot of data

in addition to users’ data. For example, it stores
data about data, known as metadata, to locate
and retrieve data easily. It is rather difficult to
modify or update a set of metadata once it is
stored in the database. But as a DBMS expands, it
needs to change over time to satisfy the
requirements of the users. If the entire data is
dependent, it would become a tedious and highly
complex job.
• Metadata itself follows a layered architecture, so that when we change
data at one layer, it does not affect the data at another level. This data
is independent but mapped to each other.
• Logical Data Independence
• Logical data is data about database, that is, it stores information about
how data is managed inside. For example, a table (relation) stored in
the database and all its constraints, applied on that relation.
• Logical data independence is a kind of mechanism, which liberalizes
itself from actual data stored on the disk. If we do some changes on
table format, it should not change the data residing on the disk.
• Physical Data Independence
• All the schemas are logical, and the actual data is stored
in bit format on the disk. Physical data independence is
the power to change the physical data without
impacting the schema or logical data.
• For example, in case we want to change or upgrade the
storage system itself − suppose we want to replace
hard-disks with SSD − it should not have any impact on
the logical data or schemas.
 DBMS Interface

• A DBMS interface is the abstraction of a piece

of functionality of a DBMS.
• It usually refers to the communication
boundary between the DBMS and clients or to
the abstraction provided by a component
within a DBMS.
• A DBMS interface hides the implementation of
the functionality of the component it
encapsulates.
• DBMS interfaces can be external or internal
• The external DBMS interface is the communication boundary
between the DBMS and clients.
• The external DBMS interface enables clients to access internal
DBMS functionality without exposing the mechanisms of how
this functionality is implemented.
• Well-known external DBMS interfaces are SQL, XPath, and
XQuery. There are many different types of external DBMS
interfaces, for example, stand-alone languages (such as SQL),
extensions to existing languages with features from SQL (such as
JDBC), and integration into middle-tier programming languages
(such as PHP).