PAMANTASAN NG CABUYAO

COLLEGE OF COMPUTING AND ENGINEERING

COURSE CODE: CPP 106

COURSE DESCRIPTION: SOFTWARE DESIGN

COURSE INTENDED On the completion of the course, student is expected to be able to do the
LEARNING OUTCOMES: following:

1.Describe process models of software life cycle.

2.Gather requirements for a realistic software system
3.Design software system at the architectural level and at lower levels,
using techniques such as object-oriented design or structured design.

LEARNING MATERIAL FOR 10

WEEK NUMBER:

I. TITLE: Database Systems

II. OBJECTIVES: By the end of this module you should be able to:

1. Describe the fundamental elements of relational database

management systems.

2. Familiar with basic database storage structures and access

techniques: file and page Organizations.

3. Explain the basic concepts of relational data model, entity-

relationship model, relational database design, relational algebra.

III. INTRODUCTION:
The word database is in such common use that we must begin by defining
what a database is. Our initial definition is quite general. A database is a
collection of related data.

By data, we mean known facts that can be recorded and that have implicit
meaning.

IV. CONTENTS:

Lesson Coverage:

Database Systems Introduction

LECTURE NOTES COMPILATION Page 1 of 11

2nd Semester A.Y. 2020-2021
 PAMANTASAN NG CABUYAO
COLLEGE OF COMPUTING AND ENGINEERING

Database Introduction

The preceding definition of database is quite general; for example, we may consider the collection of words that
make up this page of text to be related data and hence to constitute a database. However, the common use of the
term database is usually more restricted. A database has the following implicit properties:

• A database represents some aspect of the real world, sometimes called the miniworld or the universe of discourse
(DoD). Changes to the miniworld are reflected in the database.

• A database is a logically coherent collection of data with some inherent meaning. A random assortment of data
cannot correctly be referred to as a database.

• A database is designed, built, and populated with data for a specific purpose. It has an intended group of users
and some preconceived applications in which these users are interested.

A database can be of any size and of varying complexity. For example, the list of names and addresses referred
to earlier may consist of only a few hundred records, each with a simple structure. On the other hand, the
computerized catalog of a large library may contain half a million entries organized under different categories-by
primary author's last name, by subject, by book title-with each category organized in alphabetic order.

A database management system (DBMS) is a collection of programs that enables users to create and maintain a
database. The DBMS is hence a general-purpose software system that facilitates the processes of defining,
constructing, manipulating, and sharing databases among various users and applications.

Defining a database involves specifying the data types, structures, and constraints for the data to be stored in the
database. Constructing the database is the process of storing the data itself on some storage medium that is
controlled by the DBMS.

Manipulating a database includes such functions as querying the database to retrieve specific data, updating the
database to reflect changes in the miniworld, and generating reports from the data. Sharing a database allows
multiple users and programs to access the database concurrently.

Other important functions provided by the DBMS include protecting the database and maintaining it over a long
period of time. Protection includes both system protection against hardware or software malfunction (or crashes),
and security protection against unauthorized or malicious access. A typical large database may have a life cycle
of many years, so the DBMS must be able to maintain the database system by allowing the system to evolve as
requirements change over time. Database manipulation involves querying and updating.

A simplified View of a Database Environment

http://cs.boisestate.edu/~jhyeh/cs410/cs410_bookfigures_ch01.pdf

LECTURE NOTES COMPILATION Page 2 of 11

2nd Semester A.Y. 2020-2021
 PAMANTASAN NG CABUYAO
COLLEGE OF COMPUTING AND ENGINEERING

Characteristic of a Database Approach

A number of characteristics distinguish the database approach from the traditional approach of programming with
files. In traditional file processing, each user defines and implements the files needed for a specific software
application as part of programming the application.

In the database approach, a single repository of data is maintained that is defined once and then is accessed by
various users. The main characteristics of the database approach versus the file-processing approach are the
following:

• Self-describing nature of a database system

• Insulation between programs and data, and data abstraction

• Support of multiple views of the data

• Sharing of data and multiuser transaction processing

A fundamental characteristic of the database approach is that the database system contains not only the database
itself but also a complete definition or description of the database structure and constraints.

This definition is stored in the DBMS catalog, which contains information such as the structure of each file, the
type and storage format of each data item, and various constraints on the data. The information stored in the
catalog is called meta-data, and it describes the structure of the primary database.

A general-purpose DBMS software package is not written for a specific database application, and hence it must
refer to the catalog to know the structure of the files in a specific database, such as the type and format of data it
will access. The DBMS software must work equally well with any number of database application.

An operation (also called a function or method) is specified in two parts. The interface (or signature) of an
operation includes the operation name and the data types of its arguments (or parameters). The implementation
(or method) of the operation is specified separately and can be changed without affecting the interface.

User application programs can operate on the data by invoking these operations through their names and
arguments, regardless of how the operations are implemented. This may be termed program-operation
independence.

The characteristic that allows program-data independence and program-operation independence is called data
abstraction. A DBMS provides users with a conceptual representation of data that does not include many of the
details of how the data is stored or how the operations are implemented.

Informally, a data model is a type of data abstraction that is used to provide this conceptual representation. The
data model uses logical concepts, such as objects, their properties, and their interrelationships, that may be easier
for most users to understand than computer storage concepts. Hence, the data model hides storage and
implementation details that are not of interest to most database users.

LECTURE NOTES COMPILATION Page 3 of 11

2nd Semester A.Y. 2020-2021
 PAMANTASAN NG CABUYAO
COLLEGE OF COMPUTING AND ENGINEERING

Actors on the Scene

In any organization where many persons use the same resources, there is a need for a chief administrator to
oversee and manage these resources. In a database environment, the primary resource is the database itself, and
the secondary resource is the DBMS and related software.

Administering these resources is the responsibility of the database administrator (DBA). The DBA is responsible
for authorizing access to the database, for coordinating and monitoring its use, and for acquiring software and
hardware resources as needed.

Database designers are responsible for identifying the data to be stored in the database and for choosing
appropriate structures to represent and store this data. These tasks are mostly undertaken before the database is
actually implemented and populated with data.

Database designers typically interact with each potential group of users and develop views of the database that
meet the data and processing requirements of these groups. Each view is then analyzed and integrated with the
views of other user groups. The final database design must be capable of supporting the requirements of all user
groups.

End users are the people whose jobs require access to the database for querying, updating, and generating reports;
the database primarily exists for their use. There are several categories of end users:

• Casual end users occasionally access the database, but they may need different information each time.
They use a sophisticated database query language to specify their requests and are typically middle- or
high-level managers or other occasional browsers.

• Naive or parametric end users make up a sizable portion of database end users. Their main job function
revolves around constantly querying and updating the database, using standard types of queries and
updates-called canned transactions-that have been carefully programmed and tested. The tasks that such
users perform are varied:
o Bank tellers check account balances and post withdrawals and deposits.
o Reservation clerks fur airlines, hotels, and car rental companies check availability for
a given request and make reservations.

Clerks at receiving stations for courier mail enter package identifications via bar codes and descriptive
information through buttons to update a central database of received and in-transit packages.

• Sophisticated end users include engineers, scientists, business analysts, and others who thoroughly
familiarize themselves with the facilities of the DBMS so as to implement their applications to meet their
complex requirements.

• Stand-alone users maintain personal databases by using ready-made program packages that provide easy-
to-use menu-based or graphics-based interfaces.

In addition to those who design, use, and administer a database, others are associated with the design,
development, and operation of the DBMS software and system environment. These persons are typically not
interested in the database itself. We call them the "workers behind the scene," and they include the following
categories.

• DBMS system designers and implementers are persons who design and implement the DBMS modules
and interfaces as a software package. A DBMS is a very complex software system that consists of many
components, or modules, including modules for implementing the catalog, processing query language,
processing the interface, accessing and buffering data, controlling concurrency, and handling data

LECTURE NOTES COMPILATION Page 4 of 11

2nd Semester A.Y. 2020-2021
 PAMANTASAN NG CABUYAO
COLLEGE OF COMPUTING AND ENGINEERING

recovery and security. The DBMS must interface with other system software, such as the operating system
and compilers for various programming languages.

• Tool developers include persons who design and implement tools-the software packages that facilitate
database system design and use and that help improve performance. Tools are optional packages that are
often purchased separately. They include packages for database design, performance monitoring, natural
language or graphical interfaces, prototyping, simulation, and test data generation. In many cases,
independent software vendors develop and market these tools.

• Operators and maintenance personnel are the system administration personnel who are responsible for the
actual running and maintenance of the hardware and software environment for the database system.

Advantage of Using DBMS

Redundancy in storing the same data multiple times leads to several problems.

First, there is the need to perform a single logical update-such as entering data on a new student-multiple times:
once for each file where student data is recorded. This leads to duplication of effort.

Second, storage space is wasted when the same data is stored repeatedly, and this problem may be serious for
large databases.

Third, files that represent the same data may become inconsistent. This may happen because an update is applied
to some of the files but not to others.

Even if an update-such as adding a new student-is applied to all the appropriate files, the data concerning the
student may still be inconsistent because the updates are applied independently by each user group.

However, in practice, it is sometimes necessary to use controlled redundancy for improving the performance of
queries.

Restricting Unauthorized Access

When multiple users share a large database, it is likely that most users will not be authorized to access all
information in the database. Hence, the type of access operation-retrieval or update-must also be controlled.
Typically, users or user groups are given account numbers protected by passwords, which they can use to gain
access to the database.

A DBMS should provide a security and authorization subsystem, which the DBA uses to create accounts and to
specify account restrictions. The DBMS should then enforce these restrictions automatically. Notice that we can
apply similar controls to the DBMS software. For example, only the DBA's staff may be allowed to use certain
privileged software, such as the software for creating new accounts.

Providing Storage Structures for Efficient Query Processing

Database systems must provide capabilities for efficiently executing queries and updates. Because the database
is typically stored on disk, the DBMS must provide specialized data structures to speed up disk search for the
desired records. Auxiliary files called indexes are used for this purpose. Indexes are typically based on tree data
structures or hash data structures, suitably modified for disk search.

LECTURE NOTES COMPILATION Page 5 of 11

2nd Semester A.Y. 2020-2021
 PAMANTASAN NG CABUYAO
COLLEGE OF COMPUTING AND ENGINEERING

The query processing and optimization module of the DBMS is responsible for choosing an efficient query
execution plan for each query based on the existing storage structures. The choice of which indexes to create and
maintain is part of physical database design and tuning, which is one of the responsibilities of the DBA staff.

Providing Backup and Recovery

A DBMS must provide facilities for recovering from hardware or software failures. The backup and recovery
subsystem of the DBMS is responsible for recovery. For example, if the computer system fails in the middle of a
complex update transaction, the recovery subsystem is responsible for making sure that the database is restored
to the state it was in before the transaction started executing. Alternatively, the recovery subsystem could ensure
that the transaction is resumed from the point at which it was interrupted so that its full effect is recorded in the
database.

WHEN NOT TO USE A DBMS

In spite of the advantages of using a DBMS, there are a few situations in which such a system may involve
unnecessary overhead costs that would not be incurred in traditional file processing. The overhead costs of using
a DBMS are due to the following:

• High initial investment in hardware, software, and training

• The generality that a DBMS provides for defining and processing data
• Overhead for providing security, concurrency control, recovery, and integrity functions

Additional problems may arise if the database designers and DBA do not properly design the database or if the
database systems applications are not implemented properly.

Hence, it may be more desirable to use regular files under the following circumstances:

• The database and applications are simple, well defined, and not expected to change.
• There are stringent real-time requirements for some programs that may not be met because of DBMS overhead.
• Multiple-user access to data is not required.

The Entity Relationship Model (ERM)

• ER model forms the basis of an ER diagram

• ERD represents conceptual database as viewed by end user

• ERDs depict database’s main components:

– Entities
– Attributes
– Relationships

Entities

• Refers to entity set and not to single entity occurrence

• Corresponds to table and not to row in relational environment

• In Chen and Crow’s Foot models, entity is represented by rectangle with entity’s name

• Entity name, a noun, written in capital letters

LECTURE NOTES COMPILATION Page 6 of 11

2nd Semester A.Y. 2020-2021
 PAMANTASAN NG CABUYAO
COLLEGE OF COMPUTING AND ENGINEERING

Attributes

• Characteristics of entities

• Chen notation: attributes represented by ovals

connected to entity rectangle with a line
– Each oval contains the name of attribute it
represents

• Crow’s Foot notation: attributes written in attribute

box below entity rectangle

https://quizlet.com/250791411/database-applications-2-flash-cards/

• Required attribute: must have a value

• Optional attribute: may be left empty

• Domain: set of possible values for an attribute

– Attributes may share a domain
– Is usually described in the Data Dictionary

• Identifiers: one or more attributes that uniquely identify each entity instance
– Composite identifier: primary key composed of more than one attribute

DATA MODELS, SCHEMAS, AND INSTANCES

One fundamental characteristic of the database approach is that it provides some level of data abstraction by
hiding details of data storage that are not needed by most database users. A data model-a collection of concepts
that can be used to describe the structure of a database-provides the necessary means to achieve this abstraction.

By structure of a database, we mean the data types, relationships, and constraints that should hold for the
data. Most data models also include a set of basic operations for specifying retrievals and updates on the database.

In addition to the basic operations provided by the data model, it is becoming more common to include concepts
in the data model to specify the dynamic aspect or behavior of a database application. This allows the database
designer to specify a set of valid user defined operations that arc allowed on the database objects.

Categories of Data Models

Many data models have been proposed, which we can categorize according to the types of concepts they use to
describe the database structure. High-level or conceptual data models provide concepts that are close to the way
many users perceive data, whereas low-level or physical data models provide concepts that describe the details
of how data is stored in the computer.

Concepts provided by low-level data models are generally meant for computer specialists, not for typical end
users. Between these two extremes is a class of representational (or implementation) data models, which provide
concepts that may be understood by end users but that are not too far removed from the way data is organized
within the computer.

LECTURE NOTES COMPILATION Page 7 of 11

2nd Semester A.Y. 2020-2021
 PAMANTASAN NG CABUYAO
COLLEGE OF COMPUTING AND ENGINEERING

Conceptual data models use concepts such as entities, attributes, and relationships. An entity represents a real-
world object or concept, such as an employee or a project, that is described in the database. An attribute
represents some property of interest that further describes an entity, such as the employee's name or salary. A
relationship among two or more entities represents an association among two or more entities, for example, a
works-on relationship between an employee and a project.

These include the widely used relational data model, as well as the so-called legacy data models-the network and
hierarchical models-that have been widely used in the past.

Representational data models represent data by using record structures and hence are sometimes called record-
based data models. We can regard object data models as a new family of higher-level implementation
data models that are closer to conceptual data models.

Schemas, Instances, and Database State

In any data model, it is important to

distinguish between the description of the
database and the database itself. The
description of a database is called the
database schema, which is specified during
database design and is not expected to
change frequently.

A displayed schema is called a schema

diagram.

A sample schema for a database.

http://velasquezmajanelleanne.weebly.com/database-schema.html

Generally, the term database application refers to a particular database and the associated programs that
implement the database queries and updates. We present the modeling concepts of the Entity-Relationship (ER)
model, which is a popular high, level conceptual data model. This model and its variations are frequently used
for the conceptual design of database applications, and many database design tools employ its concepts.

USING HIGH-LEVEL CONCEPTUAL DATA MODELS FOR DATABASE DESIGN

The Figure shows a simplified description of the database design process. The first step shown is requirements
collection and analysis. Outing this step, the database designers interview prospective database users to
understand and document their data requirements.

LECTURE NOTES COMPILATION Page 8 of 11

2nd Semester A.Y. 2020-2021
 PAMANTASAN NG CABUYAO
COLLEGE OF COMPUTING AND ENGINEERING

These requirements should be specified in as detailed and

complete a form as possible. In parallel with specifying the
data requirements, it is useful to specify the known
functional requirements of the application.

These consist of the user-defined operations (or

transactions) that will be applied to the database, including
both retrievals and updates.

In software design, it is common to use data flow diagrams,

sequence diagrams, scenarios, and other techniques for
specifying functional requirements.

https://kelvin.ink/2018/10/06/DatabaseSystem/

The ER model describes data as entities, relationships, and attributes. In the next sections we introduce the
concepts of entities and their attributes.

Entities and Their Attributes. The basic object that the ER model represents is an entity, which is a "thing" in
the real world with an independent existence. An entity may be an object with a physical existence. Each entity
has attributes-the particular properties that describe it.

Several types of attributes occur in the ER model: simple versus composite, single-valued versus, multivalued,
and stored versus derived.

Composite attributes can be divided into smaller subparts, which represent more basic attributes with
independent meanings.

For example, the Address attribute of the employee entity shown in Figure A can be subdivided into
StreetAddress, City, State, and Zip,3 with the values "2311 Kirby," "Houston," "Texas," and "77001." Attributes
that are not divisible are called simple or atomic attributes. Composite attributes can form a hierarchy; for
example, StreetAddress can be further subdivided into three simple attributes: Number, Street, and
ApartmentNumber, as shown in Figure B. The value of a composite attribute is the concatenation of the values
of its constituent simple attributes.

Figure A Figure B

https://docs.google.com/document/d/1fu2iNV0uiou07CedoHZs9tcYpWV8AED9Ip2lgGWIhWY/edit

LECTURE NOTES COMPILATION Page 9 of 11

2nd Semester A.Y. 2020-2021
 PAMANTASAN NG CABUYAO
COLLEGE OF COMPUTING AND ENGINEERING

Composite attributes are useful to model situations in which a user sometimes refers to the composite attribute
as a unit but at other times refers specifically to its components. If the composite attribute is referenced only as a
whole, there is no need to subdivide it into component attributes. For example, if there is no need to refer to the
individual components of an address (zip code, street, and so on), then the whole address can be designated as a
simple attribute.

Single-Valued versus Multivalued Attributes. Most attributes have a single value for a particular entity; such
attributes are called single-valued. Such attributes are called multivalued. A multivalued attribute may have lower
and upper bounds to constrain the number of values allowed for each individual entity.

Stored versus Derived Attributes. In some cases, two (or more) attribute values are related-for example, the
Age and BirthDate attributes of a person. The Age attribute is hence called a derived attribute and is said to be
derivable from the BirthDate attribute, which is called a stored attribute. Some attribute values can be derived
from related entities.

Null VaIues. In some cases a particular entity may not have an applicable value for an attribute. For example, the
ApartmentNumber attribute of an address applies only to addresses that are in apartment buildings and not to
other types of residences, such as single-family homes.

The meaning of the former type of null is not applicable, whereas the meaning of the latter is unknown. The
"unknown" category of null can be further classified into two cases. The first case arises when it is known that
the attribute value exists but is missing-for example, if the Height attribute of a person is listed as null. The second
case arises when it is not known whether the attribute value exist.
Complex Attributes. Notice that composite and multivalued attributes can be nested in an arbitrary way. We can
represent arbitrary nesting by grouping components of a composite attribute between parentheses () and separating
the components with commas, and by displaying multivalued attributes between braces n. Such attributes are
called complex attributes.

LECTURE NOTES COMPILATION Page 10 of 11

2nd Semester A.Y. 2020-2021
 PAMANTASAN NG CABUYAO
COLLEGE OF COMPUTING AND ENGINEERING

V. REFERENCES: Sommerville, I. (2016). Software Engineering (Global Edition). USA: Pearson

Education, Inc. Retrieved from www.foxebook.net

Karyavin B (2015). Signal and image processing for remote sensing. USA: Pearson
Education Inc.

VI. ASSESSMENT TASK:

Assessment task is posted as scheduled in our MS Team.

DISCLAIMER

Every reasonable effort is made to ensure the accuracy of the information used in the creation of this
reference material, without prejudice to the existing copyrights of the authors. As an off-shoot of the innumerable
difficulties encountered during these trying times, the authors endeavored to ensure proper attribution of the
esteemed original works, by way of footnotes or bibliography, to their best abilities and based on available
resources, despite the limited access and mobility due to quarantine restrictions imposed by the duly constituted
authorities.

We make no warranties, guarantees or representations concerning the accuracy or suitability of the

information contained in this material or any references and links provided here. Links to other materials in our
CPOD and CAM was made in good faith, for non-commercial teaching purposes only to the extent justified for
the purpose, and consistent with fair use under Sec. 185 of Republic Act No. 8293, otherwise known as the
Intellectual Property Code of the Philippines.

COPYRIGHT NOTICE

Materials contained in the learning packets have been copied and conveyed to you by or on behalf of
Pamantasan ng Cabuyao pursuant to Section IV - The Copyright Act (RA) 8293 of the Intellectual Property Code
of the Philippines.

You are not allowed by the Pamantasan ng Cabuyao to reproduce or convey these materials. The content
may contain works which are protected by copyright under RA 8293. You may be liable to copyright infringement
for any copying and/ or distribution of the content and the copyright owners have the right to take legal action
against such infringement.

Do not remove this notice.

LECTURE NOTES COMPILATION Page 11 of 11

2nd Semester A.Y. 2020-2021