Learner Name Thiri Yadanar

Learner Registration No. 250114023

Study Centre Name MIBA University
Qualification Title OTHM Level 5 Diploma In Information Technology
Unit Reference No. H/617/2275
Unit Title Database Systems
Word Count
Submission Date
Declaration of authenticity:
1. I declare that the attached submission is my own original work. No significant part of it has
been submitted for any other assignment and I have acknowledged in my notes and
bibliography all written and electronic sources used.
2. I acknowledge that my assignment will be subject to electronic scrutiny for academic honesty.
3. I understand that failure to meet these guidelines may instigate the centre’s malpractice
procedures and risk failure of the unit and / or qualification.

_________________
Tutor signature
Date:
_________________
Learner signature
Date: 7 April 2025
Table of Contents
Task 1........................................................................................................................................................3
Introduction...............................................................................................................................................3
A description the concept of normalization in the storage of data...................................................3
Explanation of Different Normal Forms................................................................................................3
Explanation of Data and Functional Dependencies...................................................................................6
Comparison of the Hierarchical and Relational Database Management...................................................6
Explanation of the Use of Entity Relationship Diagrams (ERDs).............................................................6
Task 2.........................................................................................................................................................8
Introduction................................................................................................................................................8
Technical Documentation for the System..................................................................................................8
Security Configuration................................................................................................................8
User Documentation..................................................................................................................................9
Recommended Improvements.................................................................................................................10
Task 1

Introduction
In modern database systems, data must be stored efficiently, consistently, and securely. Concepts
such as normalization, functional dependency, and ER diagrams help in achieving this. This report
explores these key ideas and compares different database models used in practice.

A description the concept of normalization in the storage of data

Normalization in database management is the process of organizing data to minimize redundancy and
dependency, ensuring efficiency, consistency, and integrity. This involves structuring data into smaller,
logically related tables and defining relationships between them to streamline data storage and
retrieval. Normal Forms are a set of guidelines in database normalization that define how to structure
data in tables to reduce redundancy and improve integrity. Each normal form builds on the previous
one, progressively organizing data more efficiently.

Explanation of Different Normal Forms

There are various levels of normalization. These are some of them:
 First Normal Form (1NF)
 Second Normal Form (2NF)
 Third Normal Form (3NF)
 Boyce-Codd Normal Form (BCNF)
 Fourth Normal Form (4NF)
 Fifth Normal Form (5NF)
1. First Normal Form (1NF)

If a relation contains a composite or multi-valued attribute, it violates the first normal form, or
the relation is in the first normal form if it does not contain any composite or multi-valued at-
tribute. A relation is in first normal form if every attribute in that relation is single-valued at-
tribute.

Example: Consider the below COURSES Relation:

In the above table, Courses has a multi-valued attribute, so it is not in 1NF. The Below Table is
in 1NF as there is no multi-valued attribute.
2. Second Normal Form (2NF)

Second normal form requires 1NF and ensures all non-key attributes are fully functionally depen-
dent on the entire primary key, eliminating partial dependencies.

Examples: Consider a table storing information about students, courses, and their fees:

There are many courses having the same course fee.

Here, COURSE_FEE cannot alone decide the value of COURSE_NO or STUD_NO. In 2NF, we
eliminate such dependencies by breaking the table into two separate tables:

A table that links students and courses.

A table that stores course fees.

Now, the COURSE_FEE is no longer repeated in every row, and each table is free from partial
dependencies. This makes the database more efficient and easier to maintain.

3. Third Normal Form (3NF)

A relation is in the third normal form, if there is no transitive dependency for non-prime attributes
 aswell as it is in the second normal form. A relation is in 3NF if at least one of the following condi-
tions holds in every non-trivial function dependency X –> Y.X is a super key.Y is a prime attribute
(each element of Y is part of some candidate key).

Example: Consider the below Relation,

Functional dependency Set: {CAND_NO -> CAND_NAME, CAND_NO ->CAND_STATE,

CAND_STATE -> CAND_COUNTRY, CAND_NO -> CAND_AGE}.So, Candidate key here would
be: {CAND_NO}.For the relation given here in the table, CAND_NO -> CAND_STATE and
CAND_STATE -> CAND_COUNTRY are actually true. Thus, CAND_COUNTRY depends
transitively on CAND_NO. This transitive relation violates the rules of being in the 3NF. So, if
we want to convert it into the third normal form, then we have to decompose the relation
CANDIDATE (CAND_NO, CAND_NAME, CAND_STATE, CAND_COUNTRY, CAND_AGE)
as:
CANDIDATE (CAND_NO, CAND_NAME, CAND_STATE, CAND_AGE) STATE_COUNTRY
(STATE, COUNTRY).

4. Boyce-Codd Normal Form (BCNF)

 A stricter version of 3NF.

 Every determinant must be a candidate key.
5. Higher Normal Forms (4NF, 5NF)
 Deal with multivalued and join dependencies, used in complex and specialized databases.

Explanation of Data and Functional Dependencies

Data Dependency
Refers to the relationship where a data item is dependent on another. This helps define the structure of
the database and supports normalization.

Functional Dependency
A functional dependency exists when one attribute uniquely determines another.
Notation: A → B (If you know A, you can determine B)

Example:
In a student table, StudentID → StudentName means each StudentID is associated with exactly one
StudentName.

Functional dependencies help in identifying the best way to normalize tables and ensure data integrity.

Comparison of the Hierarchical and Relational Database

Management
Feature Hierarchical DBMS Relational DBMS
Data Model Tree-like structure Tabular structure with rows and
columns
Relationships One-to-many only One-to-one, one-to-many,
many-to-many
Flexibility Rigid structure High flexibility with SQL
support
Query Language Proprietary navigational access Standardized (SQL)
Data Redundancy More likely due to structure Reduced through normalization
Examples IBM IMS MySQL, Oracle, SQL Server
The university administration hierarchy, where each department (node) reports to a dean (parent
node), and each faculty member (child node) belongs to a department.

Explanation of the Use of Entity Relationship Diagrams (ERDs)

Uses of entity relationship diagramsER diagrams are used to model and design relational databases, in
terms of logic and business rules (in a logical data model) and in terms of the specific technology to be
implemented (in a physical data model.)

Understanding core concepts like normalization, functional dependencies, and ER diagrams is

essential for designing efficient and reliable databases. Choosing the right DBMS model, such as
relational over hierarchical, and properly applying normal forms ensures the integrity and scalability of
systems.
References
 Elmasri, R. & Navathe, S. (2017). Fundamentals of Database Systems.
 Date, C. J. (2003). An Introduction to Database Systems.
 Oracle Documentation: https://docs.oracle.com
 Microsoft Learn: https://learn.microsoft.com
 https://www.geeksforgeeks.org/
Task 2
Introduction
The EasyTravel Group database system supports the management of customers, travel brands,
destinations, bookings, marketing campaigns, advertisements, payments, and reviews for its diverse
travel portfolio. This report provides technical documentation for database administrators, user
documentation for end-users (e.g., marketing and customer service teams), and recommendations
for improvements to ensure the system’s continued effectiveness. The documentation ensures
operational efficiency, while the recommendations address scalability, performance, and security to
support EasyTravel’s growth.

Technical Documentation for the System

The technical documentation is intended for database administrators and developers, detailing the
database schema, constraints, security measures, and maintenance procedures.
Database Schema
The database comprises eight tables, designed to minimize redundancy and ensure data integrity:
Customers: Stores customer details (customer_id, full_name, email, phone, country).
Travel_Brands: Manages travel brands (brand_id, brand_name, country, website).
Destinations: Lists travel destinations (destination_id, brand_id, destination_name, country, price,
description).
Bookings: Tracks bookings (booking_id, customer_id, destination_id, booking_date, status).
Marketing_Campaigns: Manages campaigns (campaign_id, campaign_name, start_date, end_date,
budget).
Advertisements: Stores ads (ad_id, campaign_id, customer_id, ad_text, ad_type).
Payments: Records payments (payment_id, booking_id, amount, payment_date, payment_status).
Reviews: Captures customer reviews (review_id, customer_id, destination_id, rating, review_text).
Key Constraints:

 Primary Keys: Unique identifiers (e.g., customer_id, booking_id).

 Foreign Keys: Enforce referential integrity with ON DELETE RESTRICT (e.g., Bookings.cus-
tomer_id references Customers.customer_id).
 CHECK Constraints: Ensure valid data (e.g., Payments.amount > 0, Reviews.rating BE-
TWEEN 1 AND 5).
 UNIQUE Constraints: Prevent duplicates (e.g., Customers.email, Travel_Brands.website).
 NOT NULL: Mandatory fields (e.g., Bookings.booking_date, Advertisements.ad_text).

Security Configuration
User Roles:

 admin_user: Full privileges for schema and data management.

 readonly_user: SELECT access for reporting.
 marketing_user: SELECT, INSERT, UPDATE on Marketing_Campaigns and Advertise-
ments.

Authentication: Strong password policy (8+ characters, mixed case, numbers, special characters) en-
forced via MySQL’s validate_password plugin.
Encryption: Sensitive data (e.g., Customers.email) encrypted using AES in application logic; Trans-
parent Data Encryption (TDE) enabled for data at rest.

Auditing: Audit_Log table tracks changes (e.g., UPDATE on Bookings) via triggers.

Maintenance Procedures

 Backup: Daily backups using mysqldump (e.g., mysqldump -u db_admin -p easytravel >
backup_$(date +%F).sql).
 Indexing: Indexes on customer_id, booking_id, and destination_id for query performance.
 Monitoring: Regular checks for constraint violations and query execution times using
MySQL’s EXPLAIN.

Sample Sql:

-- Backup database

mysqldump -u db_admin -p easytravel > backup_2025-05-03.sql;

-- Add index for performance

CREATE INDEX idx_customer_id ON Bookings(customer_id);

User Documentation
The user documentation targets end-users, such as marketing and customer service teams, providing
guidance on common tasks using a simplified interface (assumed to be a web application) or direct
SQL queries for advanced users.

Accessing the System

 Login: Use provided credentials (e.g., marketing_team for marketing users). Contact IT for pass-
word resets.
 Roles：Customer Service view customer and booking details, update booking status. Marketing
manage campaigns and ads, view customer data for targeting.

Common Tasks

View Customer Bookings:

Web Interface navigate to “Bookings” tab, filter by customer name or date.

SQL:
Create a Marketing Campaign:

Web Interface Go to “Campaigns” tab, enter name, dates, and budget.

SQL:

Update Booking Status:

Web Interface: Select booking, change status (e.g., Pending to Confirmed).

SQL:

Best Practices

 Data Entry: Ensure accurate data to avoid constraint violations (e.g., valid email format).
 Security: Do not share credentials; log out after use.
 Support: Contact IT for errors or access issues.

Recommended Improvements
To ensure the database system’s continued effectiveness, the following improvements are proposed:
1. Performance Optimization
 Partitioning: Partition Bookings and Payments tables by date (e.g., monthly parti-
tions) to improve query performance for large datasets.
 Caching: Implement a caching layer (e.g., Redis) for frequently accessed data like
destination prices.

2. Scalability Enhancements
 Sharding: Distribute data across multiple servers based on country (e.g., UK vs. US
customers) to handle global growth.
 Cloud Migration: Transition to a cloud-based DBMS (e.g., Amazon RDS, Google
Cloud SQL) for auto-scaling and high availability.
 Benefit: Supports EasyTravel’s expansion to 15,000+ travel combinations.
3. Security Strengthening
 Row-Level Security: Restrict access to specific rows (e.g., marketing users only see
campaigns they manage).
 SQL Injection Protection: Implement parameterized queries in the application layer.
4. Data Quality Improvements
 Automated Validation: Add triggers to validate data (e.g., ensure Payments.amount
matches Destinations.price).
 Data Cleansing: Schedule scripts to remove outdated records (e.g., Cancelled book-
ings older than 1 year).
5. User Experience Enhancements: Provide user training sessions to improve SQL and
interface proficiency

The EasyTravel database system is well-documented for technical and user audiences, ensuring
efficient operation and usability. The proposed improvements—performance optimization, scalability,
enhanced security, data quality, and user experience—will ensure the system remains effective as
EasyTravel grows. Implementing these recommendations will support seamless management of its
expanding travel portfolio.

References

Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems. 7th ed. Pearson.

Connolly, T., & Begg, C. (2014). Database Systems: A Practical Approach to Design, Implementation,
and Management. 6th ed. Pearson.

MySQL Documentation. (2023). MySQL 8.0 Reference Manual. Oracle.