Maintaining Computerized Databases: Detailed Notes

This document provides comprehensive notes on maintaining computerized databases, covering

user requirements, design, object creation, and data manipulation.

3.1 Computerized Database User Requirements Collection

3.1.1 Introduction to Database

A database is an organized collection of structured information, or data, typically

stored electronically in a computer system. It is designed to efficiently store,
retrieve, and manage large amounts of information. Databases make data
management easy.

[Link] Key Concepts

 Data: Raw facts and figures, e.g., "John," "25," "Nairobi."

 Information: Processed, organised, and structured data, providing context
and meaning, e.g., "John is 25 years old and lives in Nairobi."
 Database Management System (DBMS): Software that interacts with end-
users, applications, and the database itself to capture and analyse data. A
DBMS facilitates the definition, creation, querying, updating, and
administration of databases. Examples include MySQL, Oracle, SQL Server,
PostgreSQL, MongoDB, Microsoft Access.
 Entity: A person, place, object, event, or concept about which data is stored.
For example, "Student," "Course," "Book."
 Attribute: A characteristic or property of an entity. For example, for the
entity "Student," attributes might include "StudentID," "Name," "Age,"
"Major."
 Record (Row/Tuple): A single entry in a table, representing a complete set
of attributes for a particular entity.
 Field (Column/Attribute): A single piece of information about a record,
representing a specific attribute.

[Link] Database Organization

Databases are typically organized into tables, also known as relations. Each table consists of
rows (records) and columns (fields).

Illustration: Simple Table Organisation

StudentID Name Age Major
001 Alice 20 Computer Science
002 Bob 22 Engineering
003 Carol 21 Business

 Table Name: Students

 Columns (Fields): StudentID, Name, Age, Major
 Rows (Records): Each horizontal entry represents a student.

[Link] Database Relationships

Relationships define how data in different tables is connected. They are

crucial for maintaining data integrity and avoiding redundancy.
 One-to-One (1:1): A single record in one table is related to a single record in another
table.
o Example: A Student table and a StudentDetails table (if some details are
sensitive and rarely accessed). One student has one set of sensitive details, and
one set of sensitive details belongs to one student.
 One-to-Many (1:M): A single record in one table can be related to multiple records in
another table. This is the most common type of relationship.
o Example: A Department table and an Employee table. One department can have
many employees, but each employee belongs to only one department.

Illustration: One-to-Many Relationship

Department Table Employee Table

+-----------------+ +-----------------------+
| DepartmentID (PK)| | EmployeeID (PK) |
| DepartmentName |----<---| EmployeeName |
+-----------------+ | DepartmentID (FK) |
+-----------------------+

 Many-to-Many (M:M): Multiple records in one table can be related to multiple records
in another table. This type of relationship requires an intermediary (junction/associative)
table to resolve.
o Example: A Student table and a Course table. One student can enroll in many
courses, and one course can have many students.

Illustration: Many-to-Many Relationship (resolved with a Junction Table)

Student Table Enrollment (Junction) Table Course Table

+-----------------+ +-------------------------+
+-----------------+
 | StudentID (PK) |----<---| StudentID (FK) |>---| CourseID
(PK) |
| StudentName | | CourseID (FK) |<---| CourseName
|
+-----------------+ | EnrollmentDate |
+-----------------+
+-------------------------+

[Link] Database Operations

These are fundamental actions performed on data within a database. Often referred to as CRUD
operations:

 Create (Insert): Adding new records to a table.

 Read (Retrieve/Select): Fetching existing data from a table based on specified criteria.
 Update: Modifying existing data in a table.
 Delete: Removing records from a table.

Other operations include:

 Sorting: Arranging data in a specific order (ascending/descending).

 Filtering: Displaying only records that meet certain conditions.
 Joining: Combining data from multiple tables based on related columns.

3.1.2 Collection of User Requirements

This is the most critical initial phase. Failing to accurately gather user requirements can lead to a
database that doesn't meet the organisation's needs. INSTANCE OF HEF

Steps for User Requirements Collection:

1. Identify Stakeholders: Determine who will use the database, who will provide data, and
who will manage it. (e.g., department heads, employees, customers, IT staff).
2. Define the Purpose and Scope: Clearly articulate what the database is intended to
achieve. What problems will it solve? What data will it manage?
3. Conduct Interviews and Workshops: Directly engage with users to understand their
daily tasks, current data handling methods, pain points, and desired functionalities. Ask
open-ended questions.
4. Observe Existing Systems/Processes: Understand how data is currently handled, even if
it's manual. This helps identify inefficiencies and required data points.
5. Gather Sample Documents and Reports: Collect any forms, spreadsheets, reports, or
documents currently used. These provide concrete examples of data and desired outputs.
6. Identify Data Entities and Attributes: From the collected information, list the key
entities (people, things, events) and the attributes associated with each.
7. Determine Data Flow and Business Rules: Understand how data moves through the
organisation and what rules govern its use (e.g., "A student must have a unique ID," "An
order must have a valid customer").
8. Prioritise Requirements: Categorise requirements as essential, desirable, or optional.
 9. Document Requirements: Create a formal document detailing all collected
requirements. Use clear, unambiguous language. This document will serve as a blueprint
for the database design.
10. Validate Requirements: Review the documented requirements with stakeholders to
ensure accuracy and completeness. Get formal sign-off.

Advantages of Thorough User Requirements Collection:

 Ensures the database meets the actual needs of the users and the organisation.
 Reduces the likelihood of costly rework later in the development process.
 Improves user satisfaction and adoption.
 Provides a clear scope for the project.
 Helps in identifying potential challenges and risks early.

3.2 Design Computerised Database Schema

Database design involves translating the collected user requirements into a logical and physical
structure for the database.

3.2.1 Creating Database Models

Database models are conceptual tools used to represent the structure of a database.

[Link] ERD Models (Entity-Relationship Diagram Models)

ERDs are high-level conceptual data models that depict entities and their relationships. They are
excellent for communicating the database structure to non-technical stakeholders.

Key Components of an ERD:

 Entities: Represented by rectangles.

 Attributes: Represented by ovals, connected to their respective entities. Key attributes
(primary keys) are underlined.
 Relationships: Represented by diamonds, connecting entities.
 Cardinality (Multiplicity): Indicates the number of instances of one entity that can be
associated with instances of another entity. Notations include:
o One-to-one (1:1)
o One-to-many (1:M or 1:*)
o Many-to-many (M:N or .)

Illustration: Simple ERD for a Student and Course System

+-------------+ +--------------+
| Student | | Course |
+-------------+ +--------------+
 | StudentID (PK) ----o | CourseID (PK) ---o
| Name | | CourseName |
| Age | | Credits |
| Major | +------------------+
+-------------+ |
| |
| enrolls_in | (Many-to-Many relationship, resolved by junction
table later)
| |
+----------------+

(Note: For a true M:M, an associative entity "Enrollment" would be added between
Student and Course in a detailed ERD, as shown in the Database Relationships section.)

Steps to Create an ERD:

1. Identify Entities: List all the main objects/concepts that the database needs to store
information about.
2. Identify Attributes: For each entity, list all the relevant characteristics or properties.
3. Identify Primary Keys: Choose a unique identifier for each entity.
4. Identify Relationships: Determine how entities are related to each other.
5. Determine Cardinality: Specify the number of instances involved in each relationship.
6. Draw the Diagram: Use appropriate symbols to represent entities, attributes, and
relationships.
7. Review and Refine: Ensure the ERD accurately reflects the requirements and is clear.

Advantages of ERDs:

 Provides a clear, high-level conceptual view of the database.

 Easy to understand for both technical and non-technical stakeholders.
 Helps in identifying entities, relationships, and attributes early in the design process.
 Facilitates communication and collaboration.

[Link] Relational Models

The relational model is the most widely used database model today. It represents data in two-
dimensional tables (relations) and uses mathematical set theory for data manipulation.

Key Principles:

 Data stored in tables: Each table represents an entity.

 Rows are records, columns are fields: As discussed earlier.
 Unique primary keys: Each row in a table is uniquely identified by a primary key.
 Foreign keys for relationships: Relationships between tables are established using
foreign keys, which are primary keys from one table appearing as attributes in another.
 Normalization: A process of organising the columns and tables of a relational database
to minimise data redundancy and improve data integrity.
Illustration: Relational Model (derived from ERD)

Student Table:

StudentID (PK) Name Age Major

001 Alice 20 Computer Science
002 Bob 22 Engineering
Export to Sheets

Course Table:

CourseID (PK) CourseName Credits

CS101 Introduction to CS 3
ENG200 Thermodynamics 4
Export to Sheets

Enrollment Table (Junction Table for M:M relationship):

EnrollmentID (PK) StudentID (FK) CourseID (FK) EnrollmentDate

1 001 CS101 2024-09-01
2 001 ENG200 2024-09-01
3 002 CS101 2024-09-05
Export to Sheets

Advantages of Relational Models:

 Simplicity: Easy to understand and implement.

 Flexibility: Easily adaptable to changes in data requirements.
 Data Integrity: Through the use of keys and normalisation, data consistency is
maintained.
 SQL (Structured Query Language): Powerful and widely adopted language for
interacting with relational databases.
 Foundation for most modern databases: Widely supported by commercial DBMS.

Steps to Design a Relational Schema (after ERD):

1. Map Entities to Tables: Each entity in the ERD becomes a table.

2. Map Attributes to Columns: Each attribute of an entity becomes a column in its
respective table.
3. Define Primary Keys: Designate a primary key for each table.
4. Map Relationships:
o 1:1 Relationships: Integrate the primary key of one table as a foreign key in the
other, or combine entities if logical.
o 1:M Relationships: The primary key of the "one" side becomes a foreign key in
the "many" side table.
 o M:M Relationships: Create a new junction table with composite primary key
composed of the primary keys of the two related tables, which act as foreign keys.
5. Apply Normalisation: Systematically reduce data redundancy and improve data
integrity by applying normal forms (1NF, 2NF, 3NF, BCNF, etc.).

3.3 Creation of Computerised Database Objects

Once the database schema is designed, the next step is to create the actual database objects
within a DBMS.

3.3.1 Database Objects

These are the fundamental building blocks of a database.

[Link] Tables

 Definition: The primary storage units in a relational database. They organise data into
rows (records) and columns (fields). Each table holds data about a specific entity.
 Purpose: To store structured data in an organised manner, ensuring data integrity and
relationships.
 Illustration (Conceptual):
 Table: Customers
 ----------------------
 | CustomerID | Name | Email | Phone |
 ----------------------
 Steps to Create a Table (General DBMS Steps):
1. Open DBMS: Launch your database management system (e.g., MySQL
Workbench, SQL Server Management Studio, Microsoft Access).
2. Connect to Database: Connect to the specific database where you want to create
the table.
3. Use SQL (or GUI):
 SQL:

SQL

CREATE TABLE Students (

StudentID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Age INT,
Major VARCHAR(50)
);

 GUI: Navigate to the "Tables" section, right-click, select "New Table,"

and then define column names, data types, and properties.
 4. Define Columns: Specify each column's name, data type (e.g., INT, VARCHAR,
DATE, BOOLEAN), and constraints (e.g., PRIMARY KEY, NOT NULL,
UNIQUE, DEFAULT).
5. Set Primary Key: Designate one or more columns as the primary key.
6. Save Table: Save the table with a meaningful name.

[Link] Records (Rows)

 Definition: A single entry in a table, representing a complete set of values for all fields
for a particular instance of an entity.
 Purpose: To store individual data entries.
 Illustration: In the Students table, (001, Alice, 20, Computer Science) is a
record.

[Link] Fields (Columns)

 Definition: A single piece of information about a record, representing a specific attribute.

Each field has a defined data type.
 Purpose: To categorise and store specific attributes of an entity.
 Illustration: In the Students table, StudentID, Name, Age, and Major are fields.

[Link] Keys

Keys are crucial for establishing relationships and ensuring data integrity.

 Primary Key (PK):

o Definition: A field or a set of fields that uniquely identifies each record in a table.
It cannot contain NULL values and must be unique for every record.
o Purpose: To provide a unique identifier for each row, enabling efficient data
retrieval and linking with other tables.
o Illustration: StudentID in the Students table.
 Foreign Key (FK):
o Definition: A field (or collection of fields) in one table that refers to the primary
key in another table.
o Purpose: To establish and enforce a link between two tables, maintaining
referential integrity.
o Illustration: DepartmentID in the Employee table (referencing DepartmentID in
the Department table).
 Composite Key: A primary key that consists of two or more attributes (fields) that
together uniquely identify each record.
o Illustration: In the Enrollment table, (StudentID, CourseID) could form a
composite primary key.
 Candidate Key: Any field or combination of fields that can uniquely identify a record in
a table. A primary key is chosen from the candidate keys.
 Super Key: A set of attributes that uniquely identifies a tuple (row) in a relation. A
candidate key is a minimal super key.
[Link] Forms

 Definition: Graphical interfaces designed for easy data entry, viewing, and editing. They
often resemble paper forms.
 Purpose: To provide a user-friendly interface for interacting with database tables,
simplifying data manipulation and reducing errors. They can display data from one or
more tables.
 Illustration (Conceptual):
 +--------------------------------+
 | Student Entry Form |
 +--------------------------------+
 | Student ID: [____001_________]|
 | Name: [____Alice_______]|
 | Age: [____20__________]|
 | Major: [Computer Science]|
 | |
 | [ Save ] [ New ] [ Delete ] |
 +--------------------------------+
 Steps to Create a Form (General DBMS Steps, e.g., Microsoft Access):
1. Select Table/Query: Choose the table or query that will be the source of data for
the form.
2. Use Form Wizard/Design View:
 Wizard: Guides you through selecting fields, layout, and style.
 Design View: Provides full control to drag and drop controls (text boxes,
labels, buttons) and connect them to fields.
3. Add Controls: Place text boxes for data entry, labels for field names, buttons for
actions (save, new record, delete), etc.
4. Set Properties: Configure properties for each control (e.g., data source, format,
validation rules).
5. Add Logic (Optional): Use macros or VBA code for advanced functionality
(e.g., calculations, conditional formatting).
6. Save Form: Save the form with a descriptive name.

[Link] Queries

 Definition: Requests for data or actions on data in a database. They are used to retrieve,
manipulate, or analyse data.
 Purpose: To extract specific information from one or more tables, perform calculations,
update records, delete records, or create new tables.
 Illustration (SQL Example):

SQL

SELECT Name, Major FROM Students WHERE Age > 20;

(This query retrieves the 'Name' and 'Major' of students older than 20.)

 Types of Queries:
o Select Query: Retrieves data from tables. (Most common)
 o Action Query: Performs operations that change the data in the database (e.g.,
INSERT, UPDATE, DELETE, CREATE TABLE).
o Parameter Query: Prompts the user for input before running.
o Cross-tab Query: Summarises data in a spreadsheet-like format (rows and
columns).
 Steps to Create a Query (General DBMS Steps):

1. Open Query Design View/SQL Editor:

 Design View (GUI): Drag and drop tables, select fields, and set criteria.
 SQL Editor: Write SQL statements directly.
2. Select Tables/Queries: Choose the data sources for your query.
3. Specify Fields: Select the columns you want to display or use in the query.
4. Set Criteria (Filtering): Define conditions to filter the records.
5. Sort Order: Specify how the results should be sorted.
6. Run Query: Execute the query to see the results.
7. Save Query: Save the query for future use.

[Link] Reports

 Definition: Formatted summaries of data from a database, designed for printing or

viewing. They present data in a professional and easy-to-read layout.
 Purpose: To present data for analysis, decision-making, or distribution. They can include
calculations, groupings, and charts.
 Illustration (Conceptual):
 +--------------------------------+
 | Student Roster |
 | (as of 2025-06-22) |
 +--------------------------------+
 | Student ID | Name | Major |
 +--------------------------------+
 | 001 | Alice | Comp Sci |
 | 002 | Bob | Eng |
 | 003 | Carol | Business |
 +--------------------------------+
 | Total Students: 3 |
 +--------------------------------+
 Steps to Create a Report (General DBMS Steps, e.g., Microsoft Access):
1. Select Table/Query: Choose the data source for your report.
2. Use Report Wizard/Design View:
 Wizard: Guides you through selecting fields, grouping levels, sorting
options, and layout.
 Design View: Provides full control over report sections (header, detail,
footer), controls, and formatting.
3. Add Fields and Labels: Place fields from your data source and add descriptive
labels.
4. Group and Sort: Define how records should be grouped and sorted for better
readability.
5. Add Calculations (Optional): Include sums, averages, counts, or other
calculations.
 6. Format: Apply formatting (fonts, colours, borders) for a professional look.
7. Preview and Adjust: Review the report and make any necessary layout
adjustments.
8. Save Report: Save the report with a meaningful name.

3.4 Data Manipulation

Data manipulation refers to the process of adding, retrieving, modifying, and deleting data within
a database. This is primarily done using SQL (Structured Query Language) in relational
databases.

3.4.1 Inserting Records

 Purpose: To add new rows (records) of data into a table.

 SQL Command: INSERT INTO
 Syntax:

SQL

INSERT INTO TableName (Column1, Column2, Column3, ...)

VALUES (Value1, Value2, Value3, ...);

Or, if inserting values for all columns in their defined order:

SQL

INSERT INTO TableName

VALUES (Value1, Value2, Value3, ...);

 Illustration (Example):

SQL

INSERT INTO Students (StudentID, Name, Age, Major)

VALUES (004, 'David', 19, 'Physics');

3.4.2 Retrieving Records

 Purpose: To fetch specific data from one or more tables based on certain criteria.
 SQL Command: SELECT
 Syntax:

SQL

SELECT Column1, Column2, ...

 FROM TableName
WHERE Condition;

o SELECT * retrieves all columns.

o WHERE clause is optional and used for filtering.
o ORDER BY for sorting.
o GROUP BY for aggregation.
o JOIN for combining data from multiple tables.
 Illustration (Examples):
o Get all students:

SQL

SELECT * FROM Students;

o Get names and majors of students aged 20:

SQL

SELECT Name, Major FROM Students WHERE Age = 20;

o Get students ordered by name:

SQL

SELECT * FROM Students ORDER BY Name ASC;

o Get students and their enrolled courses (using a JOIN):

SQL

SELECT [Link], [Link]

FROM Students S
JOIN Enrollment E ON [Link] = [Link]
JOIN Courses C ON [Link] = [Link];

3.4.3 Deleting Records

 Purpose: To remove existing rows (records) from a table.

 SQL Command: DELETE FROM
 Syntax:

SQL

DELETE FROM TableName WHERE Condition;

CAUTION: If WHERE clause is omitted, all records in the table will be deleted!
  Illustration (Example):

SQL

DELETE FROM Students WHERE StudentID = 003; -- Deletes the student with
ID 003

3.4.4 Updating Records

 Purpose: To modify existing data in one or more columns of a table.

 SQL Command: UPDATE
 Syntax:

SQL

UPDATE TableName
SET Column1 = NewValue1, Column2 = NewValue2, ...
WHERE Condition;

CAUTION: If WHERE clause is omitted, the SET operation will apply to all records in the
table!

 Illustration (Example):

SQL

UPDATE Students
SET Major = 'Data Science'
WHERE StudentID = 001; -- Changes the major of student 001 to 'Data
Science'

3.4.5 Printing Database Objects

While you don't "print" database objects in the same way you print a document, you can print the
output generated by these objects.

[Link] Tables

 Method: You typically retrieve data from a table using a SELECT query, and then export
the query results to a format like CSV, Excel, or PDF, which can then be printed. Many
DBMS GUIs also allow direct printing of the current table view.
 Steps:
1. Execute a SELECT * FROM TableName; query.
2. In your DBMS tool (e.g., MySQL Workbench, SQL Server Management Studio,
Access), look for an "Export" or "Print" option on the results grid.
3. Choose the desired output format (e.g., PDF, Excel).
4. Print the generated file.
[Link] Forms

 Method: Forms are primarily for interactive data entry and viewing. You generally don't
print a "form" itself with its input fields, but rather the data that the form displays or
allows you to enter. Some DBMS (like Access) allow printing a single record displayed
in a form.
 Steps (e.g., Microsoft Access):
1. Open the form and navigate to the specific record you wish to print.
2. Go to File > Print or use the print icon.
3. Select "Selected Records" or "Current Record" if available to print only what's
displayed.

[Link] Queries

 Method: The results of a query (the "datasheet view") can be printed.

 Steps:
1. Run the query to display its results.
2. In the DBMS interface, find the "Print" or "Export" option for the query results.
3. Export to a printable format (e.g., PDF) or print directly.

[Link] Reports

 Method: Reports are specifically designed for professional printing and presentation.
 Steps:
1. Open the report in "Print Preview" mode to see how it will look when printed.
2. Make any necessary adjustments to layout, page breaks, or formatting.
3. Use the "Print" command within the report viewer to send it to a printer.
4. Alternatively, export the report to a PDF for easy sharing and printing.

Advantages of Maintaining a Computerised Database:

 Reduced Data Redundancy: Data is stored once, eliminating duplication and

inconsistencies.
 Improved Data Integrity: Ensures accuracy, consistency, and reliability of data through
constraints (keys, data types, validation rules).
 Enhanced Data Security: Allows for granular control over who can access and
manipulate data.
 Faster Data Retrieval and Manipulation: Queries can quickly retrieve and process
large volumes of data.
 Better Data Organisation: Structured storage makes data easy to find and understand.
 Data Sharing: Multiple users and applications can access and share the same data
concurrently.
 Backup and Recovery: Easier to back up and restore data in case of system failures or
data loss.
 Scalability: Can handle growing amounts of data and increasing user demands.
  Reporting and Analysis: Facilitates the generation of complex reports and enables data
analysis for informed decision-making.
 Concurrency Control: Manages simultaneous access by multiple users to prevent data
corruption.

General Steps of Setting Up a Computerised Database:

1. Define Requirements:
o Conduct thorough user requirements collection (as detailed in 3.1.2).
o Determine the purpose, scope, and specific data needs.
2. Design Database Schema:
o Create conceptual models (ERDs) to represent entities and relationships ([Link]).
o Translate the ERD into a logical relational model, defining tables, columns,
primary keys, and foreign keys ([Link]).
o Apply normalisation to optimise the schema.
3. Choose a DBMS:
o Select appropriate database management system software (e.g., MySQL,
PostgreSQL, SQL Server, Oracle, MongoDB, Access) based on requirements
(scale, budget, complexity, existing infrastructure).
4. Create the Database:
o Set up a new database instance within the chosen DBMS. This is the container for
all your objects.
5. Create Database Objects:
o Tables: Define and create all necessary tables with their columns, data types, and
constraints (primary keys, foreign keys, not null) ([Link]).
o Relationships: Establish relationships between tables using foreign keys.
o Views (Optional but Recommended): Create virtual tables based on queries for
simplified data access or security.
o Indexes (Optional but Recommended): Create indexes on frequently searched
columns to improve query performance.
o Other Objects: Create forms, queries, reports, and other application-specific
objects as needed ([Link] - [Link]).
6. Populate Data:
o Insert initial data into the tables (3.4.1). This can be done manually, via import
from existing sources, or through applications.
7. Test the Database:
o Thoroughly test all aspects of the database: data entry, queries, updates, deletions,
forms, reports, security, and performance.
o Test edge cases and error conditions.
8. Implement Security:
o Set up user accounts, roles, and permissions to control data access and
manipulation.
9. Develop Applications (if applicable):
o If the database is part of a larger system, develop client applications (web,
desktop, mobile) that interact with the database.
10. Documentation:
 o Document the database schema, design decisions, user manuals, and maintenance
procedures.
11. Deployment:
o Deploy the database to the production environment.
12. Maintenance and Monitoring:
o Regularly back up the database.
o Monitor performance.
o Perform routine updates and optimisations.
o Troubleshoot issues as they arise.
o Adapt to evolving user requirements.