ILOCOS SUR

POLYTECHNIC STATE
STA. MARIA CAMPUS
COLLEGE

MODULE

IT 169
ADVANCE DATABASE SYSTEMS
DON FRANCIS C. PRADO

Course Code: IT 169

Descriptive Title: ADVANCE DATABAS SYSTEMS
Instructor: DON FRANCIS C. PRADO
 ILOCOS SUR
POLYTECHNIC STATE
STA. MARIA CAMPUS
COLLEGE

MODULE

I. COURSE TITLE: ADVANCE DATABASE SYSTEMS

II. COURSE OVERVIEW

Programming is a foundational skill for all computing disciplines. This knowledge area
develops skills and concepts that are essential to good programming practice and problem solving. It
covers fundamental programming concepts, event-driven programming, object-oriented programming,
basic data structures, and algorithmic processes.

III. COURSE STUDY GUIDE:

This module was prepared for you, my dear student, to work on your own. To have the best in working
with this module, you are reminded to follow these simple yet effective guidelines:

1. Manage your time well. A course study schedule is prepared for you to help you study
the modules in this course. The productive use of your time and energy will help you
a lot in finishing the scheduled activities.
2. Focus your attention. The key element for better understanding is having the focus on
the things to be done.
3. Give your best. Always remember that success will be attained in everything you do
by giving extra effort in the things you are doing. Giving your best also means
observing Honesty in doing the assigned tasks you are asked to do in this module.
Never let someone do the task for you or copy the work of your classmates.
4. Submit on time. Work diligently. Do not procrastinate. Remember time is gold. Work
immediately on the task at hand for you to follow scheduled time for submission.
5. Be patient, Motivate yourself. Patience equates success. Always think of the bright
future ahead. And to get there, start moving now.
6. Answer confidently. Study hard, surf the internet, read and read and read more. The
more you know, the more confident you become.
7. Work independently. You can do it! Your future lies in your own hands and your own
decisions. So, practice working independently, trust yourself, be independent.
8. Contact me. If you have any questions, don’t hesitate to ask me through my email
donfrancisprado@gmail.com or the group chat created for this class.

IV. COURSE STUDY SCHEDULE:

To keep you on track for the best use of the modules in The Teacher and the Curriculum, the
study schedule is hereby presented for you to follow:

Course Code: IT 169

Descriptive Title: ADVANCE DATABAS SYSTEMS
Instructor: DON FRANCIS C. PRADO
 ILOCOS SUR
POLYTECHNIC STATE
STA. MARIA CAMPUS
COLLEGE

MODULE

V. COURSE EVALUATION:

To pass the course, you must:

1. Submit all course requirements to include the following:

a. Activity Notebook with answers of the in-text questions given in the four
modules;
b. Assignment Notebook or Assignment Outputs
c. Evaluation Activities (checklist or self-test)
d. Compilation of Answers to the Study Guide for the four modules.

2. Pass all examinations:

a. Pre-test and Post-test Exams; Quizzes
b. Midterm examination
c. Final examination

VI. HOW TO USE THIS MODULE:

Welcome!
The Module, “Advance Database Systems”, is a course which comprises the
knowledge, skills and attitudes required for a future educator.

DO NOT WRITE ANYTHING ON THIS

MODULE. All answers to study guide,
activities, assignments, exercises and tests
should be written on a SEPARATE SHEET.

Course Code: IT 169

Descriptive Title: ADVANCE DATABAS SYSTEMS
Instructor: DON FRANCIS C. PRADO
 ILOCOS SUR
POLYTECHNIC STATE
STA. MARIA CAMPUS
COLLEGE

MODULE

There are several symbols in this module to guide you as you study:
This tells you to take note of or to remember an important point.

This tells you there is an Intended Learning Activity for you to accomplish.

This tells you there is an assignment or research activity to be undertaken.

This tells you there is an Assessment or Self-Test.

This tells you the summary of the module.

This tells you of the study guide for you to work upon.

Course Code: IT 169

Descriptive Title: ADVANCE DATABAS SYSTEMS
Instructor: DON FRANCIS C. PRADO
 ILOCOS SUR
POLYTECHNIC STATE
STA. MARIA CAMPUS
COLLEGE

MODULE
Policies on the Use of this Module
1. The preparation and reproduction of this module entailed much effort and cost hence, you must give
value to it. Strictly speaking, the module is a property of the college and you are not allowed to fold or
tear nor put any markings or writings on any of its pages for it will be of future use.
2. The very purpose of this module is to transfer learning to you my dear students amidst this COVID-19
pandemic. Thus, you must read, study its content, comply with the required activities, and perpetuate
the project ask for and have to submit them on time. Observe promptness and know how to value your
time.
3. The issuance of your next module solely depends upon your submission of the required activities and
project of your previous module. In short, no activities/project submitted no next module.
4. It is expected that you work by yourself with the different activity and project for you are the one whom
we are transferring knowledge and not anybody else. Be diligent and honest enough.
5. In doing the learning activities, assessments and assignments for each lesson of each module, please
use a separate sheet of short size bond paper for each and compile all of this in one short size folder.
This will serve as your portfolio for your module.
6. If there are part/s of the lesson which seem not clear or hard for you, you may seek assistance or shoot
questions that will give clarity to your concern thru the Group Chat or GC created for this purpose.
7. Submission of learning activities, assessment, assignment, project and the module will be during the
scheduled monthly face to face meeting and at the same time you will be given a summative test anent
the module just finished.
8. The adopted rubrics below will be used in evaluating your activities, assignments and projects:
CRITERIA Excellent Very Satisfactory Satisfactory Unsatisfactory
91-100 81-90% 71-80% 61-70%
Assignment:
a. Creativity- 40 All the One of the criteria Two of the criteria Evidently fail to
b. Timeliness- 25 criteria has has not been met has not been met meet the criteria
c. Quality - 35 been met satisfactorily satisfactorily
Assessment:
a. Creativity- 30 All the One of the criteria Two of the criteria Evidently fail to
b. Timeliness- 20 criteria has has not been met has not been met meet the criteria
c. Quality - 20 been met satisfactorily satisfactorily
d. Relevance-30
Activity:
a. Organization- 30 All the One of the criteria Two of the criteria Evidently fail to
b. Content/Relevance-40 criteria has has not been met has not been met meet the criteria
c. Vocabularies – 20 been met satisfactorily satisfactorily
d. Neatness – 10

9. During this pandemic, we make use of the grading system 70% class standing and 30% term exam.
However, we were given the academic freedom to decide the components of your class standing. For
this subject, you will be graded in your class standing accordingly:
a. 35% - quizzes/assessment/summative test
b. 35% - activities and assignments
c. 30% - project/s
100%

Course Code: IT 169

Descriptive Title: ADVANCE DATABAS SYSTEMS
Instructor: DON FRANCIS C. PRADO
 ILOCOS SUR
POLYTECHNIC STATE
STA. MARIA CAMPUS
COLLEGE

MODULE

Module 1
The Database Environment
I. INTENDED LEARNING OUTCOMES
 Intro to Database Management System
 Definition of terms
 Explain growth and importance of databases
 Name limitations of conventional file processing
 Identify five categories of databases
 Explain advantages of databases
 Identify costs and risks of databases
 List components of database environment
 Describe evolution of database systems

II. CONTENT
A database management system (DBMS) refers to the technology for creating and managing
databases. DBMS is a software tool to organize (create, retrieve, update, and manage) data in a
database.
The main aim of a DBMS is to supply a way to store up and retrieve database information that
is both convenient and efficient. By data, we mean known facts that can be recorded and that have
embedded meaning. Usually, people use software such as DBASE IV or V, Microsoft ACCESS, or
EXCEL to store data in the form of a database. A datum is a unit of data. Meaningful data
combined to form information. Hence, information is interpreted data - data provided with
semantics. MS. ACCESS is one of the most common examples of database management software.

Why Use DBMS

 To develop software applications in less time.
 Data independence and efficient use of data.
 For uniform data administration.
 For data integrity and security.
 For concurrent access to data, and data recovery from crashes.
 To use user-friendly declarative query language.

Where is a Database Management Systems (DBMS) being used?

 Airlines: reservations, schedules, etc
 Telecom: calls made, customer details, network usage, etc
 Universities: registration, results, grades, etc
 Sales: products, purchases, customers, etc
 Banking: all transactions etc
Definitions

Course Code: IT 169

Descriptive Title: ADVANCE DATABAS SYSTEMS
Instructor: DON FRANCIS C. PRADO
 ILOCOS SUR
POLYTECHNIC STATE
STA. MARIA CAMPUS
COLLEGE

MODULE
 Data: stored representations of meaningful objects and events; raw facts; building blocks of
information
- Structured: numbers, text, dates
- Unstructured: images, video, documents
- Think of data as a "raw material" - it needs to be processed before it can be turned into
something useful. Hence the need for "data processing". On its own - it is not very useful.
- Examples of Data
Think of the data that is created when you buy a product from a retailer. This includes:
 Time and date of transaction (e.g. 10:05 Tuesday 12 November 2013)
 Transaction value (e.g. P55.00)
 Facts about what was bought (e.g. hairdryer, cosmetics pack, shaving foam) and how
much was bought (quantities)
 How payment was made (e.g. credit card, credit card number and code)
 Which employee recorded the sale

 Information: data processed to increase knowledge in the person using the data; data
processed to be useful in decision making.

- Characteristics of information: accurate, relevant, and timely

- Examples of Information
 The data about the transaction is processed to create "information" - in this case a
receipt.
 The data concerning all shop transactions in the day needs to be captured, and then
processed into a management report.

 Information Technology (IT) / Information and Communications Technology (ICT): A

contemporary term that describes the combination of computer technology (hardware and
software) with telecommunications technology (data, image, and voice networks).
 Information Systems: An arrangement of people, data, processes, communications, and
information technology that interact to support and improve day-to-day operations in a
business, as well as support the problem-solving and decision-making needs of management
and users.
 Metadata: data that describes the properties and context of user data
 Database: organized collection of logically related data, stored in a single logical data
repository

Course Code: IT 169

Descriptive Title: ADVANCE DATABAS SYSTEMS
Instructor: DON FRANCIS C. PRADO
 ILOCOS SUR
POLYTECHNIC STATE
STA. MARIA CAMPUS
COLLEGE

MODULE

Examples of Metadata

Files and File Systems

 In the early days, database applications were built on top of file systems; many separate and
unrelated files
Collection of file folders kept in file cabinet
- Organization within folders based on data’s expected use (ideally logically related)
- System adequate for small amounts of data with few reporting requirements
- Finding and using data in growing collections of file folders became time-consuming and
cumbersome

Conversion from manual to computer system:

- Could be technically complex, requiring hiring of data processing (DP) specialists
- Resulted in numerous “home-grown” systems being created
- Initially, computer files were similar in design to manual files

Disadvantages
- Program-Data Dependence
o All programs maintain metadata for each file they use
o Problems with data dependency
 Each application programmer must maintain his/her own data
 Each application program needs to include code for the metadata of each file
 Each application program must have its own processing routines for reading,
inserting, updating, and deleting data
 Lack of coordination and central control
 Non-standard file formats
- Data Redundancy (Duplication of data)
o Different systems/programs have separate copies of the same data
o Problems with data redundancy

Course Code: IT 169

Descriptive Title: ADVANCE DATABAS SYSTEMS
Instructor: DON FRANCIS C. PRADO
 ILOCOS SUR
POLYTECHNIC STATE
STA. MARIA CAMPUS
COLLEGE

MODULE
 Waste of space to have duplicate data
 Causes more maintenance headaches
 The biggest problem:
 Data changes in one file could cause inconsistencies
 Compromises in data integrity
- Limited Data Sharing
o No centralized control of data
- Lengthy Development Times
o Programmers must design their own file formats
- Excessive Program Maintenance
o 80% of of information systems budget
Database systems offer solutions to all the above problems

The Database Approach

 Central repository of shared data
 Data is managed by a controlling agent
 Stored in a standardized, convenient form
 Require a Database Management System (DBMS) - a software system that is used to create,
maintain, and provide controlled access to user databases; a collection of programs that
manages database structure and controls access to data
 Possible to share data among multiple applications or users
 Makes data management more efficient and effective

Advantages of the Database Approach

• Program-Data Independence
– Metadata stored in DBMS, so applications don’t need to worry about data formats
– Data queries/updates managed by DBMS so programs don’t need to process data access
routines
– Results in: increased application development and maintenance productivity
• Minimal Data Redundancy
– Leads to increased data integrity/consistency
• Improved Data Sharing
– Different users get different views of the data
• Enforcement of Standards
– All data access is done in the same way
• Improved Data Quality
– Constraints, data validation rules
• Better Data Accessibility/ Responsiveness
– Use of standard data query language (SQL)
• Security, Backup/Recovery, Concurrency
– Disaster recovery is easier

DBMS - a software system that is used to create, maintain, and provide controlled access to user
databases; a collection of programs that manages database structure and controls access to data

Course Code: IT 169

Descriptive Title: ADVANCE DATABAS SYSTEMS
Instructor: DON FRANCIS C. PRADO
 ILOCOS SUR
POLYTECHNIC STATE
STA. MARIA CAMPUS
COLLEGE

MODULE

Order Filing
System

Central database
Invoicing
DBMS Contains employee,
System
order, inventory,
pricing, and
Payroll customer data
System

DBMS Examples
 Proprietary
- Oracle, SQL Server (Microsoft), DB2 (IBM), Access (Microsoft), Informix, PostgreSQL,
Interbase, Sybase 11, Foxpro, Firebird
 Open Source
- MySQL, MariaDB, MaxDB, Ingress

Costs and Risks of the Database Approach

 New, specialized personnel
 Installation and management cost and complexity
 Conversion costs
 Need for explicit backup and recovery
 Organizational conflict

Components of the Database System

• Database system is composed of five main parts:
– Hardware
– Software
• Operating system software
• DBMS software
• Application programs and utility software - software using the data
– People
– Procedures
– Database
Database Administrator - coordinates all the activities of the database system; the database
administrator has a good understanding of the enterprise’s information resources and needs.

DBMS Functions
• DBMS performs functions that guarantee integrity and consistency of data
– Data dictionary management - defines data elements and their relationships
– Data storage management - stores data and related data entry forms, report definitions,
etc.
– Data transformation and presentation - translates logical requests into commands to
physically locate and retrieve the requested data
– Security management - enforces user security and data privacy within database

Course Code: IT 169

Descriptive Title: ADVANCE DATABAS SYSTEMS
Instructor: DON FRANCIS C. PRADO
 ILOCOS SUR
POLYTECHNIC STATE
STA. MARIA CAMPUS
COLLEGE

MODULE
– Multiuser access control - uses sophisticated algorithms to ensure multiple users can
access the database concurrently without compromising the integrity of the database
– Backup and recovery management - provides backup and data recovery procedures
– Data integrity management - promotes and enforces integrity rules
– Database access languages and application programming interfaces - provide data access
through a query language
– Database communication interfaces - allow database to accept end-user requests via
multiple, different network environments

Types of Databases
Can be classified by number of users:
• Single-user: Supports only one user at a time
– Desktop: Single-user database running on a personal computer
• Multi-user: Supports multiple users at the same time
– Workgroup: Multi-user database that supports a small group of users or a single
department
– Enterprise: Multi-user database that supports a large group of users or an entire
organization
Can be classified by location:
• Centralized: Supports data located at a single site
• Distributed: Supports data distributed across several sites
Can be classified by use:
• Transactional (or production): (OL Transaction P)
– Supports a company’s day-to-day operations
• Data warehouse: (OL Analytical P)
– Stores data used to generate information required to make tactical or strategic decisions
– Often used to store historical data
– Structure is quite different

Evolution of DB Systems
• File systems - 1960s - 1980s
• Hierarchical – 1970s - 1990s
• Network – 1970s - 1990s
• Relational – 1980s - present
• Object-oriented – 1990s - present
• Object-relational – 1990s - present
• Data warehousing – 1980s - present
• Web-enabled – 1990s - present

Course Code: IT 169

Descriptive Title: ADVANCE DATABAS SYSTEMS
Instructor: DON FRANCIS C. PRADO