Database Implementation Report: COIT20247 (T2 2019) Analysis

This report details the database implementation of the CQUnited Property Management (CQUPM) system. It showcases database normalization, functional dependencies, and the use of MS Access as a relational database management system. The report includes an ER diagram, mapping of entities, and the application of normalization principles to achieve 3NF. It further presents SQL queries designed to retrieve specific data, such as properties not currently rented, properties managed by each staff member, repair jobs completed by handymen, applications exceeding a certain threshold, and properties with the highest weekly rental. The report also includes a rental report and highlights various learning features, including database reports, date functions, and complex features like subqueries and inner joins. It concludes by emphasizing the report's role in facilitating a comprehensive understanding of database design and implementation concepts, from basic to advanced levels, referencing relevant academic resources.

COIT20247 Database Design and
Development (T2 2019)
September 25
201
9
Student ID:
Student Name:
Module Tutor:
Assessment Item 2 –
Assignment 2

Implementation Report
Introduction
The database implementation of CQUnited Property Management system (CQUPM)
is being shown in the report. The report is showing the full concept of database
normalization, functional dependency etc.
The CQUPM database is implemented into the MS Access. The MS Access is very
useful and attractive relational database management system.
Normalization
ER Diagram Mapping
customer (customerID, customerName, address, phone)
staff (staffID, staffName, address, phone, staffType)
manager (managerID, qualification)
Foreign Key (managerID) References staff (staffID)
e_empl_category (categoryID, categoryName, hourlyRate)
external_employee (e_employeeID, tradeLicenseNo, categoryID)
Foreign Key (e_employeeID) References staff (staffID)
Foreign Key (categoryID) References category (categoryID)
owner (ownerID, ownerName, address, phone)
supervisor (supervisorID, managerID)
Foreign Key (supervisorID) References manager (managerID)
Foreign Key (managerID) References manager (managerID)
property (propertyID, propertyType, address, listedWeeklyRental, numBedRoom,
numBath, availableDate, managerID, ownerID)
Foreign Key (managerID) References manager (managerID)
Foreign Key (ownerID) References owner (ownerID)
2 | P a g e

rental (rentalID, contractStartDate, contractEndDate, weeklyRental, customerID,
propertyID)
Foreign Key (customerID) References customer (customerID)
Foreign Key (propertyID) References property (propertyID)
application (applicationID, reference, submittedDate, processedDate, comments,
customerID, propertyID)
Foreign Key (customerID) References customer (customerID)
Foreign Key (propertyID) References property (propertyID)
repair_job (jobID, jobDescription, completedDate, charge, e_employeeID,
propertyID)
Foreign Key (e_employeeID) References external_employee (e_employeeID)
Foreign Key (propertyID) References property (propertyID)
Functional Dependency in Tables
Customer
customer (customerID, customerName, address, phone)
customerName  customerID
address  customerID
phone  customerID
Staff
staff (staffID, staffName, address, phone, staffType)
staffName  staffID
address  staffID
phone  staffID
staffType  staffed
3 | P a g e

Normalisation
customer (customerID, customerName, address, phone)
1. The field customerID is the primary key. There is only one name, one address
and one phone number for the customer id. So, there is no repeated group in
the customer table. Hence, the customer table is in 1NF.
2. The customerID is the only candidate key which is uniquely identifying the
records in the table because name, address etc. cannot be unique. So, there is
no partial dependency in the customer table. Hence, the customer table is in 2
NF.
3. A name, address, phone etc cannot be used to functionally determine any other
attribute in the table because they cannot be unique. So, there is no transitive
dependency in the customer table. Hence, the customer table is in 3 NF.
(Watt A. n.d.)
(Tutorialspoint n.d.)
SQL Queries
Query 1
SELECT property.address, property.propertyType, property.listedWeeklyRental,
Owner.ownerName FROM Owner INNER JOIN property ON Owner.ownerID =
property.ownerID WHERE (((property.[propertyID]) Not In (select propertyID from
rental)));
4 | P a g e

Query 2
SELECT staffName AS ManagerName, count(propertyID) AS NumOfProperty
FROM property INNER JOIN staff ON property.managerID = staff.staffID GROUP
BY staffName ORDER BY count(propertyID) DESC;
Query 3
SELECT repair_job.jobID, repair_job.jobDescription, repair_job.completedDate,
repair_job.charge, staff.staffName FROM e_empl_category INNER JOIN (staff
INNER JOIN (external_employee INNER JOIN repair_job ON
external_employee.e_employeeID=repair_job.e_employeeID) ON
staff.staffID=external_employee.e_employeeID) ON
e_empl_category.categoryID=external_employee.categoryID
WHERE repair_job.completedDate>=DateAdd("m",-6,Date()) AND
e_empl_category.categoryName="handyman";
Query 4
SELECT property.propertyID, property.address, property.listedWeeklyRental,
Count(application.propertyID) AS NoOfApplications FROM property INNER JOIN
application ON property.propertyID=application.propertyID GROUP BY
property.propertyID, property.address, property.listedWeeklyRental, submittedDate
HAVING count(application.propertyID)>1 and submittedDate>=DateAdd("m",-
6,Date());
5 | P a g e

Query 5
SELECT TOP 1 customer.customerName, property.address, rental.weeklyRental
FROM property INNER JOIN (customer INNER JOIN rental ON
customer.customerID = rental.customerID) ON property.propertyID =
rental.propertyID ORDER BY rental.weeklyRental DESC;
Report Query
SELECT customer.customerName, customer.phone, rental.contractStartDate,
rental.contractEndDate, property.address, rental.weeklyRental FROM property
INNER JOIN (customer INNER JOIN rental ON
customer.customerID=rental.customerID) ON property.propertyID=rental.propertyID
WHERE DateDiff("m",contractStartDate,contractEndDate)>=6;
Rental Report
6 | P a g e

Learning Features
The assignment is containing so many learning features as mentioned below-
- Database report
- Different date functions
- Database normalization
Complex Features
The assignment is containing so many learning features but there are some complex
features as well-
- Use of Sub queries
- Use of inner joins
- Use of report wizard
Conclusion
A user can easily understand the whole concept of database design and
implementation after studying the report. The report is giving complete knowledge
about basic to complex database concepts.
7 | P a g e

References
Watt A. (n.d.). Chapter 12 Normalization. [online]. Retrieved from:
https://opentextbc.ca/dbdesign01/chapter/chapter-12-normalization/
Tutorialspoint (n.d.). Relational Data Model. Retrieved from:
http://www.tutorialspoint.com/dbms/relational_data_model.htm
8 | P a g e