Database Design and Development Project - Semester 1, University Name

This assignment is a database design and development project. Part A includes an Entity Relationship Diagram (ERD) illustrating the relationships between entities like Manager, Customer, Staff, Owner, External_Employee, Property, Rental, Repairjob, and Application. The implementation section discusses the challenges and solutions encountered, particularly with SQL queries and integrity constraints. The assignment also covers functional dependencies and normalization, explaining 1NF, 2NF, and 3NF. The project includes the implementation of several SQL queries to retrieve specific information from the database, such as finding properties not currently rented, counting properties managed by staff, and finding handyman repair jobs. The project also includes a query to display customer and rental information sorted by weekly rental in descending order.

Running head: DATABASE DESIGN & DEVELOPMENT
Database Design & Development
Name of the Student
Name of the University
Author Note

1DATABASE DESIGN & DEVELOPMENT
PART A
Entity Relationship Diagram
Figure 1: Entity Relationship Diagram
Source: created by author
Manager
Manager (ManagerID, Qualification)
Customer
Customer (CustomerID, Fullname, Address, phone)
Staff
Staff (StaffID, FullName, Address, Phone, StaffType,)

2DATABASE DESIGN & DEVELOPMENT
E_EMPPL_Category
E_EMPPL_Category (CategoryID, CategoryName, HourlyRate)
Owner
Owner (OwnerID, FullName, Address, Phone)
External_Employee
External_Employee (E_EmployeeID, TradeLicenseno, CategoryID)
Foreign key (CategoryID) references E_EMPPL_Category (CategoryID)
Property
Property (PropertyID, PropertyType, Address, ListedWeeklyRental, NumBedrooms,
AvailableDate, ManagerID, OwnerID)
Foreign key (OwnerID) references owner (OwnerID)
Foreign key (ManagerID) references manager (ManagerID)
Rental
Rental (RentalID, ContractStartDate, ContractEndDate, WeeklyRental ProID, CustID)
Foreign key (CustID) references Customer (Customer_ID)
Foreign key (ProID) references Property (PropertyID)
Repairjob
Repairjob (JobID, JobDescription, CompletedDate, Charge, ProID, EmpID)
Foreign key (EmpID) references External_Employee (E_EmployeeID)

3DATABASE DESIGN & DEVELOPMENT
Foreign key (ProID) references Property (PropertyID)
Application
Application (ApplicationID, Reference, SubmitDate, ProcessedDate, Comments, CustID, ProID)
Foreign key (CustID) references customer (CustomerID)
Foreign key (ProID) references Property (PropertyID)
Supervisor
Supervisor (SupervisorID, ID)
Foreign key (ID) references Manager (ManagerID)
Implementation
The Implementation of the tables and relations was a great task. However, the Integrity
Constraints and the Queries were the most interesting part in the whole implementation. The
repair jobs which were done by the Handymen in last 6 months, were troubled for some time.
The issue was resolved establishing a ‘where’ condition in the SQL query with two ‘criteria’.
Along with that, The Weekly rentals sorted in the descending manner using ‘order by’ in SQL
along with ‘DESC’.
The difficulties were occurred due to the way of joined tables. For fetching single information
from two tables who were joined through multiple tables, it took so much of joining.

4DATABASE DESIGN & DEVELOPMENT
Functional Dependencies and Normalization
Rental (RentalID, ContractStartDate, ContractEndDate, WeeklyRental ProID, CustID)
Application (ApplicationID, Reference, SubmitDate, ProcessedDate, Comments, CustID,
ProID)
1NF
The First normalisation condition states that the attributes should have only on atomic
values so that the entire row can not be repeated. This refers to the idea of using Primary key in
tables. In Application and Rental tables, the primary keys are ApplicationID and RentalID
respectively which is unique. This will help in differentiating the identical rows. For example, in
some cases the date can be same in multiple rows for application and rental table.
2NF
Second Normal form says that table should be in 1NF and it should not consist any partial
dependencies between the attributes. For eliminating the dependencies, the tables can be split
into sub tables. In application and rental tables, there is no presence of partial dependencies as
the schema is already in the 3NF normalised.
3NF
Third form states that it should be in 2NF and no transitive functional dependencies are
allowed here. In 3NF the many to many relationships are solved by introducing a third table
between 2 tables having the both foreign keys of the tables.

5DATABASE DESIGN & DEVELOPMENT
Integrity Constraint
Integrity 1
Integrity 2

6DATABASE DESIGN & DEVELOPMENT
Integrity 3
Integrity 4
Query 1
SELECT DISTINCT owner.fullname, property.propertyid, property.type, property.address,
property.listedweeklyrental
FROM property, owner
WHERE (((owner.ownerid)=[property].[ownerid]) AND ((Exists (select proid
from rental where property.propertyid = rental.proid))=False));

7DATABASE DESIGN & DEVELOPMENT
Query 2
SELECT fullname, count(propertyid) AS [count]
FROM (SELECT fullname, propertyid FROM (staff INNER JOIN manager ON staff.staffid =
manager.managerid) INNER JOIN property ON property.managerid = manager.managerid) AS
a
GROUP BY fullname
ORDER BY count(propertyid);
Query 3

8DATABASE DESIGN & DEVELOPMENT
SELECT RepairJob.JobID, RepairJob.JobDesription, E_EMPL_category.CategoryName,
staff.FullName AS staff
FROM ((E_EMPL_category RIGHT JOIN External_Employee ON
E_EMPL_category.categoryID = External_Employee.CategoryID) RIGHT JOIN RepairJob ON
External_Employee.E_EmployeeID = RepairJob.EmpID) LEFT JOIN staff ON
External_Employee.E_EmployeeID = staff.StaffID
WHERE (((E_EMPL_category.CategoryName)="handyman"));
Query 4
SELECT Property.PropertyID, Property.Address, Property.ListedWeeklyRental,
Count(Application.ApplicationID) AS CountOfApplicationID, Property.AvailableDate
FROM Property RIGHT JOIN Application ON Property.PropertyID = Application.ProID
GROUP BY Property.PropertyID, Property.Address, Property.ListedWeeklyRental,
Property.AvailableDate
HAVING (((Count(Application.ApplicationID))>1) AND
((Property.AvailableDate)>=#3/1/2019#));

9DATABASE DESIGN & DEVELOPMENT
Query 5
SELECT Customer.FullName, Property.Address, Rental.WeeklyRental
FROM Property RIGHT JOIN (Customer RIGHT JOIN Rental ON Customer.CustomerID =
Rental.CustID) ON Property.PropertyID = Rental.ProID
ORDER BY Rental.WeeklyRental DESC;

10DATABASE DESIGN & DEVELOPMENT