Database Implementation Report for CQUnited Property Management System

This report explains the database implementation of CQUnited Property Management system (CQUPM) using MS Access. It covers database normalization, functional dependency, ER diagram mapping, SQL queries, and report generation.

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