Tuguegarao Archdiocesan Schools System
Saint Joseph’s College of Baggao, Inc.
Baggao, Cagayan, Philippines
Transforming Lives, Shaping the Future
Module No. 1
DATABASE ENVIRONMENT
Course Title: Advanced Database Systems Course IT 116
Code:
Instructor: SHERWIN M. CAACBAY, MIT Term & AY: 2nd Sem, AY 2020-
YSSANDREA KNERRE R. 2021
ZINAMPAN, MIT
Email wincsureq@[Link] Contact No: 09551987227
Address: yssandreaknerrezinampan@gm 09394572402
[Link]
[Link]
Good decisions require good information derived from raw facts. Data is managed most
efficiently when stored in a database and databases evolved from computer file systems and
understanding file system characteristics is important.
Databases solve many of the problems encountered in data management. It is used in
almost all modern settings involving data management in Business, Research, and
Administration. It is important to understand how databases work and interact with other
applications.
II. Intended Learning Outcome (ILOs)
Upon completion of this module, the student should be able to:
A. Define and explain the following terms: database, data, database management
system, information, metadata, data warehouse, data independence, repository,
user view and constraint;
B. Differentiate traditional file processing and database approach; and
C. Explain the advantages of the database approach, compared to traditional file
processing.
III. Learning Resources & References
1. Internet references
[Link]
[Link]
[Link]
2. Book Reference
Database System concepts, Written by Abraham Silbierschatz (Page no- 1-
2)
IV. Lecture Content/ Summary of Lesson
1.1BASIC CONCEPTS AND DEFINITION
DATA VS INFORMATION
Data are meaningful facts, text, graphics, images, sound, video segments
Information is the result of processing raw data to reveal its meaning. It requires
context to reveal meaning.
Raw data must be formatted for storage, processing and presentation.
Data are the foundation of information, which is the bedrock of knowledge
Data: building blocks of information
Information produces by processing data
Information used to revel meaning is the key to good decision making
Good decision making is the key to organizational survival
Data management: focuses on proper generation, storage and retrieval of data.
Metadata are data that describe the properties or characteristics of other data.
Introducing the database
Page 1 of 8
Tuguegarao Archdiocesan Schools System
Saint Joseph’s College of Baggao, Inc.
Baggao, Cagayan, Philippines
Transforming Lives, Shaping the Future
Database: shared, integrated computer structure that stored a collection of:
– End-user data: raw facts of interest to end user
– Metadata: data that describes data
DBMS (Database Management System) – software package that stores and
manage databases.
– Manages structure and controls access to data
DBMS Architecture
The design of a DBMS depends on its architecture. It can be centralized or
decentralized or hierarchical. The architecture of a DBMS can be seen as either single
tier or multi-tier. An n-tier architecture divides the whole system into related but
independent n modules, which can be independently modified, altered, changed, or
replaced.
In 1-tier architecture, the DBMS is the only entity where the user directly sits on the
DBMS and uses it. Any changes done here will directly be done on the DBMS itself. It
does not provide handy tools for end-users. Database designers and programmers
normally prefer to use single-tier architecture.
If the architecture of DBMS is 2-tier, then it must have an application through which
the DBMS can be accessed. Programmers use 2-tier architecture where they access
the DBMS by means of an application. Here the application tier is entirely
independent of the database in terms of operation, design, and programming.
3-tier Architecture
A 3-tier architecture separates its tiers from each other based on the complexity of
the users and how they use the data present in the database. It is the most widely
used architecture to design a DBMS.
Database (Data) Tier − At this tier, the database resides along with its
query processing languages. We also have the relations that define the data
and their constraints at this level.
Application (Middle) Tier − At this tier reside the application server and the
programs that access the database. For a user, this application tier presents
an abstracted view of the database. End-users are unaware of any existence
of the database beyond the application. At the other end, the database tier is
not aware of any other user beyond the application tier. Hence, the
application layer sits in the middle and acts as a mediator between the end-
user and the database.
User (Presentation) Tier − End-users operate on this tier and they know
nothing about any existence of the database beyond this layer. At this layer,
multiple views of the database can be provided by the application. All views
are generated by applications that reside in the application tier.
Page 2 of 8
Tuguegarao Archdiocesan Schools System
Saint Joseph’s College of Baggao, Inc.
Baggao, Cagayan, Philippines
Transforming Lives, Shaping the Future
Multiple-tier database architecture is highly modifiable, as almost all its components
are independent and can be changed independently.
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.
DBMS - Data Independence
If a database system is not multi-layered, then it becomes difficult to make any
changes in the database system. Database systems are designed in multi-layers as
we learnt earlier.
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
Page 3 of 8
Tuguegarao Archdiocesan Schools System
Saint Joseph’s College of Baggao, Inc.
Baggao, Cagayan, Philippines
Transforming Lives, Shaping the Future
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.
1.2Traditional file processing vs. database approach
What is A File Processing System?
A file processing system is a method for storing and organizing computer files and the
data they contain to make it easy to find and access them. File processing systems may
use a storage device such as a hard disk or CD-ROM and involve maintaining the physical
location of the files.
The traditional approach to information system design focuses on data processing needs
of individual department in an organization without considering the organization as a
Page 4 of 8
Tuguegarao Archdiocesan Schools System
Saint Joseph’s College of Baggao, Inc.
Baggao, Cagayan, Philippines
Transforming Lives, Shaping the Future
whole. Each computer application typically designed with its own set of data file and
application programs to meet the information requirements of particular department or a
user group.
Database approach overcomes the limitation of file-oriented system by supporting an
integrated, centralized data structure which allows to sharing database by different
applications.
Database Approach
Traditional File Approach
Use separate data file for each All Application shares a pool of
application related and integrated data.
Data redundancy – independent Minimal data redundancy –
data files included a lot of Separate data files are integrated
duplicated data. in to a single, logical structure.
Same data is recorded and stored Each occurrence of a data item is
in several files. recorded only once.
Data inconsistency – several Single version of data exist
versions of the same data may
exist.
Same update must be done in all Single update is required.
occurrences of same data item in
each file.
Users have very little opportunity A database is developed to share
to share data outside of their own the data among the user who
application. access to it
There is no centralized control for There is centralized control for
overall data in different files. overall data in database.
Data dependence – description of Data independence – the database
files, records and data items are system separates data
embedded within individual descriptions from the application
application programs. programs that use the data in it
Modification to data files requires Data structure can be modified
the programs which access that file without changing the programs
to be modified. accessing the data
High program maintenance Less program maintenance
Lack of data integration – Data are organized in to a single
accessing data in several files are logical structure with logical
difficult relationships defined between
associated data
Difficult to manipulation data Easy to manipulation data
Page 5 of 8
Tuguegarao Archdiocesan Schools System
Saint Joseph’s College of Baggao, Inc.
Baggao, Cagayan, Philippines
Transforming Lives, Shaping the Future
Traditional File System
Before the use of a computer, a manual file system was used to maintain the records
and files. Data were stored and processed using a traditional file system and it makes it
easy to find any information. In this traditional file system, each file is independent of
other file and data in the different file can be integrated only by writing an individual
program for each application. The data and application program that uses the data are
arranged that any change to data requires modification of all the programs that use the
data. Sometimes, it is not possible to identify all the programs using data and identified
on trial and error basis. All functional areas in the organization create, processes its own
files.
The files such as inventory and payroll generate separate files and do not communicate
with each other. The organization was simple to generate and had better local control
but the data of an organization is dispersed throughout the functional subsystem.
Components of Traditional File System
Hardware, Software, data manager, DB Engine, User, Data, Database, Database access
language, Server, Run Time Manager.
Applications of Traditional File System
The application is developed for a specific purpose and they will access the database
only.
Uses of Traditional File System
Language-specific run-time libraries
User programs use file system APIs to make requests of the file system
Data transfer
Positioning.
Updating metadata
Managing directories.
Managing access specifications.
Removal
Advantages of Traditional File System
Simple to use.
Less complex.
Minimal investment (Not make the investment in software because it allows us to
transport files from one to another computer).
No requirement of the specialist.
Disadvantages of Traditional File System
Data redundancy (Each application has its own data file so same data may have
to be recorded and stored in many times).
Data inconsistency (Due to the same data items that appear in more than one file
do not get updated simultaneously in each and every file).
Data dependence (Program and application in the file processing system are data
dependent but, the problem is incompatible with file format).
Limited data sharing.
The problem with security.
Page 6 of 8
Tuguegarao Archdiocesan Schools System
Saint Joseph’s College of Baggao, Inc.
Baggao, Cagayan, Philippines
Transforming Lives, Shaping the Future
Retrieval (retrieval is not easy).
Time-consuming.
Inefficient to maintain the record of the big firm having a large number of items.
Required Lots of labor work to do.
V. Learning Activities
Exercise 1. This problem is a review of Information Management subject applying Microsoft
Access which is a prerequisite of Advanced Database Systems.
Given the file structure answer the following problems:
Figure 1: tbl_project
Project_c Project_man Manager_ph Manager_ad projectBD_
ode ager one dress price
21-5Z Esmer 078-375- Carig Campus P16,833,460.
Baricaua 2188 00
25-2D Daniel 078-844- Sanchez Mira P12,500,000.
Ursulum Jr 3456 campus 00
25-5A Jake Maggay 078-846- Lasam P32,512,450.
1234 Campus 00
25-9T Dorothy 904-338- Gonzaga P21,563,234.
Ayuyang 3416 Campus 00
27-4Q Fernan 904-445- Lallo Campus P10,314,545.
Ammagui 2719 00
28-2D Macy Mags 615-227- Piat Campus P25,559,999.
1245 00
31-7P Jez Agoto 615-698- Aparri Campus P56,650,000.
9909 00
1. How many records does the file contain, and how many fields are there per record?
2. What problem would encounter if you wanted to produce a listing by city? How would
you solve this problem?
3. If you wanted to produce a listing of the file contents by last name, area code, city,
state, or zip code, how would you alter the file structure?
4. What data redundancies do you detect, and how could those redundancies lead to
anomalies?
Exercise 2. Answer the following questions briefly but with substance.
1. Why is it difficult to manage data?
2. What is the difference between bit and byte?
3. Differentiate field from record.
VI. Assessment
Quiz 1. Identification. Read and analyze the following statements carefully, then identify
what is being described and write your answer on the space provided after each item.
_________1.It is a state of operational database with data at any given time. It contains a
snapshot of the database.
_________2.It is the data about database, that is, it stores information about how data is
managed inside.
_________3.At this tier reside the application server and the programs that access the
database.
_________4. It is the power to change the physical data without impacting the
schema or logical data.
_________5.At this tier, the database resides along with its query processing languages.
_________6.It is the result of processing raw data to reveal its meaning. It requires context
to reveal meaning.
_________7.This schema pertains to the actual storage of data and its form of storage like
files, indices, etc.
_________8.These are meaningful facts, text, graphics, images, sound, video segments.
_________9.It is a method for storing and organizing computer files and the data they
contain to make it easy to find and access them.
Page 7 of 8
Tuguegarao Archdiocesan Schools System
Saint Joseph’s College of Baggao, Inc.
Baggao, Cagayan, Philippines
Transforming Lives, Shaping the Future
_________10.This schema defines all the logical constraints that need to be applied on the
data stored. It defines tables, views, and integrity constraints.
Quiz 2. Read the following statements carefully. Write “TPA” if the statement describes a
Traditional Processing Approach otherwise write “DA” if the statement describes a
Database approach. Write your answer on the space provided before each item.
________1. Data structure can be modified without changing the programs accessing the
data.
________2. Same update must be done in all occurrences of same data item in each file.
________3. All Application shares a pool of related and integrated data.
________4. Data are organized in to a single logical structure with logical relationships
defined between associated data.
________5. There is no centralized control for overall data in different files.
________6. Users have very little opportunity to share data outside of their own
application.
________7. Modification to data files requires the programs which access that file to be
modified.
________8. Difficult to manipulation data.
________9. There is centralized control for overall data in database.
________10. Each occurrence of a data item is recorded only once.
VII. Assignment
Have an advance reading on the range of advance database applications and components of
database environment.
Page 8 of 8
