Designing Databases
❑ What is System Design?
❑ Databases
❑ Database Design
❑ File Organization
❑ Relational Database Model
❑ Database Normalization
❑ Database Relationships
❑ Guidelines for Master File or
Database Relation Design
❑ Integrity Constraints
❑ Make Use of the Database
What is System Design?
➢ The systems analyst and the user develop a
concrete understanding of how the system will
operate
➢ Process of defining the architecture, components,
modules, interfaces, and data for a system to
satisfy specified requirements
Databases
➢ Integrated collection of stored data that is
centrally managed and controlled
➢ Managed and controlled by a database
management system (DBMS)
➢ Effectiveness objectives of the database are:
• Ensures that data can be shared among users for a variety
of applications.
• Maintains data that are both accurate and consistent.
• Ensures that all data required for current and future
applications will be readily available.
Database Design
➢ Five purposes of database design:
1. Structure the data in stable structures, called normalized
tables, that are not likely to change over time and that
have minimal redundancy.
2. Develop a logical database design that reflects the actual
data requirements that exist in the forms (hard copy and
computer displays) and reports of an information system.
3. Develop a logical database design from which we can do
physical database design.
4. Translate a relational database model into a technical file
and database design that balances several performance
factors.
5. Choose data storage technologies (i.e., CD-ROM or optical
disk) that will efficiently, accurately, and securely process
database activities.
Database Design
➢ A logical database model is developed, which
describes data using a notation that corresponds
to a data organization used by a database
management system.
• Relational database model
➢ A physical database model is developed that
provides these specifications.
File Organization
➢ File Types:
• Master Files
o Collection of records pertaining to one of the main subjects
of an information system
• Table Files
o Include data that is used to calculate more data or
performance measures
• Transaction Files
o Collection of records
• Work Files
o Sometimes make a program run more efficiently
• Report Files
o Describe how a report is printed
Relational Database Model
➢ Represents data in the form of related tables, or relations
➢ Relation
• Named, two-dimensional table of data
➢ Example:
Stud_ID Name Course
1001 Juan Dela Cruz BSECE
1002 Tony Sy BSCE
1003 Hannah Reyes BSCS
1004 Maria Santos BSBA
1005 Leah Meneses BSIT
1006 Carol Mina BSN
STUDENT(Stud_ID, Name, Course)
Relational Database Model
➢ Properties of relations that differentiate them
from non-relational tables:
1. Entries in cells are simple. An entry at the
intersection of each row and column has a single
value.
2. Entries in a given column are from the same set of
values.
3. Each row is unique. Uniqueness is guaranteed since
the relation has a non-empty primary key value.
4. The sequence of columns can be interchanged
without changing the meaning or use of the relation.
5. The rows may be interchanged or stored in any
sequence.
Relational Database Model
➢ Steps to follow in creating a relational database schema:
1. Create a table for each entity type.
2. Choose a primary key for each table (invent one, if
necessary).
3. Add foreign keys to represent one-to-many
relationships.
4. Create new tables to represent many-to-many
relationships.
5. Define referential integrity constraints.
6. Evaluate schema quality and make necessary
improvements.
7. Choose appropriate data types and value
restrictions (if necessary) for each field.
Database Normalization
➢ First Normal Form (1NF)
• A relation is in first normal form it is contains no
repeating fields or group of fields.
Database Normalization
➢ Example:
Cust_ID First Name Surname Tel_Num
101 Juan Dela Cruz 891-8959
891-8960
102 Tony Sy 874-5612
874-5613
103 Hannah Reyes 887-4526
Cust_ID First Name Surname Tel_Num
101 Juan Dela Cruz 891-8959
101 Juan Dela Cruz 891-8960
102 Tony Sy 874-5612
102 Tony Sy 874-5613
103 Hannah Reyes 887-4526
Database Normalization
➢ Functional Dependency (FD)
• This occurs when one attribute in a relation uniquely
determines another attribute.
• This can be written as A → B which would be the same as
stating "B is functionally dependent upon A."
• Example:
o Name is functionally dependent upon SSN (or SSN →
name)
Database Normalization
➢ Second Normal Form (2NF)
• A relation is in second normal form if it is in first normal
form and if each non-key element is functionally
dependent on the entire primary key.
Database Normalization
➢ Example:
Cust_ID Tel_Num
Cust_ID First Name Surname 101 891-8959
101 Juan Dela Cruz 101 891-8960
102 Tony Sy 102 874-5612
103 Hannah Reyes 102 874-5613
103 887-4526
Customer Table Customer_Telephone Table
Database Normalization
➢ Third Normal Form (3NF)
• A normalized relation is in the third normal form if it is in
second normal form and if no non-key element is
functionally dependent on any other non-key element.
Database Normalization
➢ Example:
Stud_ID Stud_Name Stud_Add
1010 Roselle Perez Laguna
1020 Sarah Basco Antipolo
1030 Michelle Reyes Rizal
Student Table
Stud_ID Course_ID Instructor_ID
1010 SYSAD 01
1020 COMORG 02
1030 DATACOM 03
Enrollment Table
Database Normalization
Course_ID Course_Title
SYSAD Systems Analysis and Design
COMORG Computer Organization
DATACOM Data Communication
Course Table
Instructor_ID Instructor_Name
01 Benet Tanyag
02 Rolly Torio
03 Tanya Torres
Instructor Table
Database Relationships
➢ One-to-One Relationship
• Occurs when there is exactly one record in the first table
that corresponds to exactly one record in the related
table
Example:
Database Relationships
➢ One-to-Many Relationship
• Primary key table contains only one record that relates to
none, one, or many records in the related table
Example:
Database Relationships
➢ Many-to-Many Relationship
• Relationship between two tables in which one record in
either table can have many matching records in the
other table
Example:
Guidelines for Master File or Database
Relation Design
➢ Each separate data entity should create a master database
table. Do not combine two distinct entities on one file.
➢ A specific data field should exist only on one master table.
➢ Every master table or database relation should have programs
to Create, Read, Update, and Delete (CRUD) the records. If
possible, only one program should add new records and only
one program should delete specified records.
`
Integrity Constraints
➢ Entity integrity
• Rules that manage the composition of primary keys
➢ Referential integrity
• Describes a consistent state among foreign key and
primary key values
➢ Domain integrity
• Used to validate the data, such as table, limit, range, and
other validation checks
Make Use of the Database
1. Choose a relation from the database.
• Accomplished by keeping a directory of user views as a
memory aid
2. Join two relations together.
• Intended to obtain two relations and put them
together to create a larger relation
• Example:
CUSTOMER (CUST_NUM, CUST_NAME, WAREHOUSE_NUM)
and
WAREHOUSE (WAREHOUSE_NUM, WAREHOUSE-LOC)
Make Use of the Database
3. Project columns from the relation.
• Process of extracting certain columns from a relational
table
• Example:
CUSTOMER-WAREHOUSE-LOCATION (CUST_NUM, CUST_NAME,
WAREHOUSE_NUM), WAREHOUSE_LOC
Make Use of the Database
4. Select rows from the relation.
• Creates a new (smaller) relation by extracting records that
consist of an attribute meeting a particular condition
Make Use of the Database
5. Derive new attributes.
• Involves the manipulation of the existing data plus
some additional parameters (if necessary) to derive
new data
6. Index or sort rows.
• Indexing - is the logical ordering of rows in a relation
according to some key.
• Sorting - is the physical ordering of a relation.
Make Use of the Database
7. Calculate totals and performance measures.
• Done when the proper subset of data is defined and
the rows of the relation are ordered in the required
way
8. Present data.
• Final step in the retrieval of data
