Database Management System Project: ERD and SQL Queries

Desklib provides solved assignments and past papers to help students succeed.

Table of Contents
Introduction................................................................................................................................2
Task A........................................................................................................................................3
Data Normalization................................................................................................................3
Functional Dependencies.......................................................................................................4
3rd Normal Form.....................................................................................................................4
Relational Database................................................................................................................5
Queries...................................................................................................................................7
Implementation Report...........................................................................................................9
Conclusion................................................................................................................................10
References................................................................................................................................11
List of Figures
Figure 1: Entity Relationship Diagram......................................................................................3
Figure 2: Manager Table............................................................................................................5
Figure 3: Property Table............................................................................................................5
Figure 4: Application Table.......................................................................................................5
Figure 5: Owner Table...............................................................................................................5
Figure 6: Customer Table...........................................................................................................6
Figure 7: Job_Details Table.......................................................................................................6
Figure 8: Reception Table..........................................................................................................6
Figure 9: Emp_Category Table..................................................................................................6
Figure 10: External_Employee Table........................................................................................6
Figure 11: Staff Table................................................................................................................7
Figure 12: Q1.............................................................................................................................7
Figure 13: Q2.............................................................................................................................7
Figure 14: Q3.............................................................................................................................8
Figure 15: Q4.............................................................................................................................8
Figure 16: Q5.............................................................................................................................9

Introduction
The entire report is based on the topic of Database Management System which is being made
or created using the MS Access software and also the use of the MySQL Workbench is being
shown in the report properly. Different queries are performed in the assignment and also the
functional dependencies, as well as the normalization, are properly shown in the report.

Task A
Data Normalization
Normalization of data can be defined as normalizing the data for the removal of the repetition
of the data from the provided data and also anomalies are being resolved in the present
database. In order to show the normalization of the database, the Entity-Relationship is being
created as:
Figure 1: Entity Relationship Diagram
Few of the Normalized tables present here are:
STAFF (S_ID, S_Name, S_Address, TypeOfStaff)
RENTAL (R_ID, P_ID, C_ID, M_ID, ContractStartDate, ContractEndDate)
OWNER (O_ID, NameOfOwner, AddressOfOwner, ContactNoOfOwner)
CUSTOMER (Cstmr_ID, Cstmr_Name, Cstmr_Address, Cstmr_Contact, P_ID)

Functional Dependencies
Functional dependencies that are mainly being encountered here is the Multi-valued
dependencies among almost each of the table columns. The two of them are shown below as:
OWNER: In the table named as Owner, different attributes that are named as
NameOfOwner, AddressOfOwner, and ContactNoOfOwner are independent of each other
and are dependent upon the single attribute that is O_ID.
O_ID-> NameOfOwner
O_ID-> AddressOfOwner
O_ID-> ContactNoOfOwner
STAFF: In the table named as Staff, different attributes that are named as S_Name,
S_Address, and TypeOfStaff are independent of each other and are dependent upon the single
attribute that is S_ID.
S_ID-> S_Name
S_ID-> S_Address
S_ID-> TypeOfStaff
3rd Normal Form
Normalization has 4 different forms and out of which 3rd Normal Form is the form where
transitive dependencies are to be removed and for being in 3NF, the data must be in 2NF as
well as in 1NF. There are no transitive dependencies present here in the ERD created above.
Two of the tables shown below as the sample:
OWNER: In the table named as Owner, different attributes that are named as
NameOfOwner, AddressOfOwner, and ContactNoOfOwner and O_ID is the Primary Key
present. So, the table is shown below as:
OWNER (O_ID, NameOfOwner, AddressOfOwner, ContactNoOfOwner)
STAFF: In the table named as Staff, different attributes that are named as S_Name,
S_Address and TypeOfStaff and S_ID is the Primary Key present. So, the table is shown
below as:
STAFF (S_ID, S_Name, S_Address, TypeOfStaff)

Relational Database
Figure 2: Manager Table
Figure 3: Property Table
Figure 4: Application Table
Figure 5: Owner Table

Figure 6: Customer Table
Figure 7: Job_Details Table
Figure 8: Reception Table
Figure 9: Emp_Category Table
Figure 10: External_Employee Table

Figure 11: Staff Table
Queries
Q1
SELECT PROPERTY.ADDRESS, PROPERTY.PROPERTYTYPE,
PROPERTY.LISTEDWEEKLYRENTAL, OWNER.O_NAME
FROM OWNER INNER JOIN PROPERTY ON OWNER.O_ID = PROPERTY.O_ID
WHERE PROPERTY.LISTEDWEEKLYRENTAL =0;
Figure 12: Q1
Q2
SELECT MANAGER.M_NAME, MANAGER.Managed_Property
FROM MANAGER
ORDER BY MANAGER.Managed_Property DESC;
Figure 13: Q2

Q3
SELECT REPAIR_JOB.J_ID, REPAIR_JOB.JOB_DESCRIPTION,
REPAIR_JOB.DATEOFCOMPLETION, REPAIR_JOB.TOTALCHARGES,
REPAIR_JOB.DONEBY
FROM REPAIR_JOB;
Figure 14: Q3
Q4
SELECT PROPERTY.P_ID, PROPERTY.ADDRESS,
PROPERTY.LISTEDWEEKLYRENTAL, PROPERTY.TotalApplications
FROM PROPERTY LEFT JOIN APPLICATION ON PROPERTY.P_ID =
APPLICATION.P_ID
WHERE PROPERTY.TotalApplications>1;
Figure 15: Q4
Q5
SELECT CUSTOMER.CSTMR_NAME, PROPERTY.ADDRESS,
Max(PROPERTY.LISTEDWEEKLYRENTAL) AS MaxOfLISTEDWEEKLYRENTAL
FROM PROPERTY INNER JOIN CUSTOMER ON PROPERTY.P_ID =
CUSTOMER.P_ID
GROUP BY CUSTOMER.CSTMR_NAME, PROPERTY.ADDRESS;

Figure 16: Q5
Implementation Report
Few of the things had been learned from the assignment and that is mentioned as:
1. Performing the queries on MS Access using the SQL commands is the first thing that I
have learned from the assignment along with it the normalization process is also clearly
mentioned in the report.
2. Various difficulties had been faced in showing the proper relationship between the three
tables which on later stage got resolved.

Conclusion
The entire report created above is done using the MS Access as well as MySQL Workbench
software. Different queries are also performed in the report. Functional dependencies and
normalization of the data are also shown properly in the report. The difficulties that are being
faced are also mentioned in the report.

References
Zani, C.L. and Carroll, A.R., 2017. Database for rapid dereplication of known natural
products using data from MS and fast NMR experiments. Journal of natural products, 80(6),
pp.1758-1766.
Liu, C., Springer, D., Li, Q., Moody, B., Juan, R.A., Chorro, F.J., Castells, F., Roig, J.M.,
Silva, I., Johnson, A.E. and Syed, Z., 2016. An open access database for the evaluation of
heart sound algorithms. Physiological Measurement, 37(12), p.2181.
Anselma, L., Piovesan, L. and Terenziani, P., 2016. A 1NF temporal relational model and
algebra coping with valid-time temporal indeterminacy. Journal of Intelligent Information
Systems, 47(3), pp.345-374.