ICT152

Computing Skills
Fundamentals II

Relationships
Lecture 7
 Lecture Outline
• Flat File Databases
• Problems
• Solution
• Relationships
• Building a Database

2/8/2024 2
 Flat File Problems

• Same piece of information may be repeated several times in

the table leading to redundancy
• e.g. Entry for a product every time it is sold

• Problems with Update and Delete

• Several repetitive changes needed e.g. if product name
needs to be changed
• Data entry
• Extra time required e.g. entering data about the same
product several times for each order

2/8/2024 3
 Flat File Example

• Students Table (Part 1)

• Student details and Adviser details
• NB: Jenkins is repeated for 3 students, changes to e.g.
Surname will require modification of 3 records in the table

2/8/2024 4
 Flat File Example

• Students Table(Part 2)
• (continuation showing adviser details)
• NB: basic Adviser information repeated several times e.g.
email, office number

2/8/2024 5
 Using More than one Table

• Most applications have several related tables

• Reduces need to e.g. store a student’s name with each
examination or student adviser details with each student

• Advantages
• Data entry is simplified
• Reporting is simplified
• Decrease in redundant data

2/8/2024 6
 Solution: Multiple Tables
• Use multiple related tables each storing data about one thing
• Simplify data entry by decreasing the input of redundant
data
Students: Information about students only

Advisers: Information about advisers

2/8/2024 7
 Normalization
• Normalization is a process of organizing the data in database to
avoid data redundancy, insertion anomaly, update anomaly &
deletion anomaly.
• Segmentation of data in one table into multiple tables leads to
• Significant reduction in design and work time

• Redundancy is reduced e.g. each adviser is associated with just

one record in the Advisers table
• Data Entry is simplified e.g. enter data about Jenkins just
once

• Update is simplified e.g. if Jenkins office details change, an

2/8/2024 8
update can just be done in one location
 Multiple Tables

• Establish relationships between tables

• i.e. create a link between the two tables

➢ for the relationship to exist there must be a common

field in the two tables that will take part in the
relationship e.g. AdviserID

➢ Fields to be linked should have the same type of

structure and data

2/8/2024 9
 Relationships Types

• One-to-One
• One record in one table is related to only one record in
the other table e.g. Husband and wife

• One-to-Many/Many-to-One
• One record in one table is related to 2 or more records
in the other table E.g. Mother and child relationship

• Many-to-Many
• e.g. Students and Courses. NB: can not be represented
directly in Access
2/8/2024 10
 Relationships

• Link a field in one table (Primary table), to a field in

another table (Related table)

• Often use Primary key to link to a field with the same

names e.g. StudentID

• Updating data in one table automatically updates data in

the other linked table/tables

• The link field in the second table that refers back to the
table that is the source of the Primary key is known as a
Foreign Key.
2/8/2024 11
 Relationships

• Example: One-to-Many
• one record in the Advisers table is related to many
records in the Students table
Primary key Foreign Key

2/8/2024 12
 Relationships Variables

• Referential Integrity
• Ensures valid and consistent relationships between records
by synchronizing data

• Ensures that very record on the “many” side has a

corresponding record on the “one side”

• Prevents addition of link value in the related table if

primary table does not have matching record
• E.g. can not add a Student with an AdviserID that
does not exist in the Advisers table

• Eliminates problem of “orphaned records”

• Prevents deletion of records on the “one” or parent
2/8/2024 side if related records exist on the “many”
13 side
 Relationship Variables

• Cascading Update/Delete Operations

• Ensure that changes in the primary table are

reflected in the related table
• Automatic removal of related records in the
related table if records are deleted in the
primary table e.g. removal of Adviser record will
remove all related Student records

• Changing primary key in the primary table will

update records in the related records i.e. changing
AdviserID in Advisers will change AdviserID in
2/8/2024 Students 14
 Building a Database

1: Overall System Design

2: Report Design (Output)

3: Data Design (Fields)

4: Table Design (Relationships)

5: Field Design (Validation)

6: Form Design (Input)

7: Menu Design (Automation)

2/8/2024 15
 Building a Database

• First determine the purpose or overall objective of the

database
• Start each new step by reviewing the overall objectives

• Step 1: Overall Design

• What will meet the needs of the end user and what are
those needs?
• This process is iterative

• Step 2: Report Design

• Determine the layout of the report (fields) in parallel with
2/8/2024 determining data items, text. 16
 Building a Database

• Step 3: Data Design

• Take an inventory of all data fields needed to accomplish
the output e.g. list data items in each report.
• Group data items logically.
• Step 4: Table Design
• Establish relationships between tables using common
fields

2/8/2024 17
 Building a Database

• Step 5: Field Design

• Fields and tables in greater detail, including data validation
rules for each field. Name each field and determine a data
type. Table lookups are designed e.g. department code.

• Test data is created: good data (for output) and bad data (for
data entry) e.g. an existing customer number.

2/8/2024 18
 Building a Database

• Step 6: Form Design

• Design based on forms from manual systems. Include labels,
textboxes, special controls and graphical enhancements
• Step 7: Menu Design
• Make use of Switchboards (forms with buttons) and Menus
(lists of commands at the top of a window).

2/8/2024 19
 Other Types of Databases
• Hierarchical (mainframes)
• Organizes data into tree-like structure
• Data stored as records which are connected to
each other through links

2/8/2024 20
 Network Databases

• Similar to the hierarchical model

• Difference records can have many “parents” or
“children”

2/8/2024 21
Thank You

QUESTIONS??