Database Fundamentals Report: MS Access, ERD, and SQL Queries

This report delves into the core concepts of database management, focusing on the design and implementation of a relational database for a rental property business. It begins with the creation of an Entity Relationship Diagram (ERD) to visually represent the entities (owner, room/property, client, booking, and payment) and their relationships. The report then proceeds to normalize the database schema up to the Third Normal Form (3NF) to minimize data redundancy and ensure data integrity, including dependency diagrams. A database is created using Microsoft Access, and tables are designed to store relevant information. Furthermore, the report demonstrates the creation of forms for data entry and modification and illustrates the use of SQL queries to retrieve and manipulate data, including examples for selecting, inserting, and deleting records. The report concludes with a summary of the key findings and references to the sources used.

Database Fundamentals

Table of Contents
Introduction.................................................................................................................................................3
Creation of ERD with entities as well as relationship..............................................................................3
Convert ERD diagram to relational tables till 3NF as well as dependency diagram................................4
Creation of database through usage of MS-Access..................................................................................7
Create 3 queries.....................................................................................................................................10
Conclusion.................................................................................................................................................10
REFERENCES..........................................................................................................................................11

Introduction
The data structure which is accountable for storage of organized information is referred
to as database. This is systematic accumulation of data which can store as well as modify data as
per requirements. Entity relationship diagram depicts the relation between different entities that
are stored in database (Chen, 2019). This enables to have visualized view of objects or entities by
which it becomes to acknowledge how each work and how they are associated with one other.
There are distinct methodologies or languages which are used for manipulation of data so that
essential aspects can be acknowledged in better manner. This report is based on case study of
business specialist firm based in Australia who provides rental properties who want to record as
well as track all the properties, their employees, customers, rental activities along with
maintenance of properties. This report involves ERD diagrams and database is being created by
making use of Microsoft Access. Furthermore, forms will be created and entries will be made be
in database, in addition to this, queries will be utilized for making modifications.
Creation of ERD with entities as well as relationship
As per the scenario there will be five entities each with their own set of attributes that
makes them different from other and have distinct rationale (Coronel and Morris, 2016). But they
have relation with other. The entities are owner, room or property, client, booking and payment.
The ERD diagram is illustrated below:

Illustration 1: ERD Diagram
The above diagram shows the relationship among different entities which illustrates the
way in which processing for rental system is being carried out. The details of room or property
will be saved in database which is associated with client to identify which property is with
whom. Similarly, others are also associated to provide organized and structured information
related with rental system.
Convert ERD diagram to relational tables till 3NF as well as dependency diagram.
While development of relational database the essential aspect that has to be taken into
consideration is to make sure that data duplication is reduced as much as it is possible. Thus, it
will aid within reduction of storage space and also ensure that unnecessary conflicts do not arise
as multiple copies of same information are present (Elmasri and Navathe, 2017). Therefore,
when database is designed, normalization must be utilized as it will eliminate data redundancy.
The tables are segmented into sub tables and this is being done by making use of different
normalization for optimizing database in an adequate manner. The table is shown beneath:
CustN
o
Cname PropNo PAddr RntSt RntFnsh Rent Owner OName
CR86 Kay
John
PG16 5 Nova Dr, East
Meadow,
7/1/20 8/31/20 900 C093 Tina
Murphy

PG4
6 Lawrence St,
Elmont 9/1/20 9/31/20 1000 C040 Shaw
Tony
CR46 Stewart
Aline
PG4
PG36
PG16
6 Lawrence St,
Elmont
2 Manor Rd
Scarsdale
5 Nova Dr, East
Meadow.
9/1/20
8/1/20
8/1/20
10/31/20
12/30/20
9/1/20
1000
750
900
C040
C093
C093
Tina
Murphy
Shaw
Tony
Shaw
Tony
1NF (First Normal Form)
With respect to this, each table comprises of only one value that stores unique
information for avoiding repetition of information. In this form relation comprises of one row
and column that have only one value (Jensen and Snodgrass, 2018). Any attribute which is
repeated leads to creation of complications for database Each table has single value and records
must be unique. The data illustrated in table above has been normalized below:
CustN
o
Cname PropNo PAddr RntSt RntFnsh Rent Owner OName
CR86 Kay
John
PG16 5 Nova Dr, East
Meadow
7/1/20 8/31/20 1000 C093 Tina
Murphy
CR86 Kay
John
PG4 6 Lawrence St,
Elmont
9/1/20 9/31/20 1000 C040 Shaw
Tony
CR46 Stewart
Aline
PG4 6 Lawrence St,
Elmont
9/1/20 10/31/20 900 C040 Tina
Murphy
CR46 Stewart
Aline
PG36 2 Manor Rd
Scarsdale
8/1/20 12/30/20 750 C093 Shaw
Tony
CR46 Stewart
Aline
PG16 5 Nova Dr, East
Meadow.
8/1/20 9/1/20 900 C093 Shaw
Tony
2NF (Second Normal Form)

The relation between entities is in 2NF only in case if this is in 1NF and each non-
primary key attributes are entirely functionally dependent on their primary keys (Maiyya and et.
al, 2018). This involves that table has to be in 1NF and there must be single column primary key.
With respect to this, tables are specified below:
Client or Customer Relations
CustNo Cname
CR86 Kay John
CR46 Stewart Aline
Rental Relation
CustNo PropNo RentStart RentFinish
CR86 PG16 7/1/20 8/31/20
CR86 PG4 9/1/20 9/31/20
CR46 PG4 9/1/20 10/31/20
CR46 PG36 8/1/20 12/30/20
CR46 PG16 8/1/20 9/1/20
Property Owner Relation
PropNo PAddr Rent Owner OName
PG16 5 Nova Dr, East Meadow 900 C093 Tina Murphy
PG4 6 Lawrence St, Elmont 1000 C040 Shaw Tony
PG4 6 Lawrence St, Elmont 1000 C040 Tina Murphy
PG36 2 Manor Rd Scarsdale 750 C093 Shaw Tony
PG16 5 Nova Dr, East Meadow. 900 C093 Shaw Tony
3NF (Third Normal Form)

A relation is said to be in 3NF when it is in 2NF and here, there are no non-primary key
attributes which transitively depends on primary key. This involves that the table needs to be in
2nd normal form and their needs not to be transitive functional dependencies. Functional
dependencies in 2NF are:
 Customer: CustNo –> Cname
 Rental: CustNo, PropNo -> CustNo, RentFinish
RentStart -> CustNo, RentFinish
 PropertyOwner: PropNo -> Paddr, OwnerNo, Rent, OName
OwnerNo -> Oname
Client or Customer Relations
CustNo Cname
CR86 Kay John
CR46 Stewart Aline
Rentals Relation
CustNo PropNo RentStart RentFinish
CR86 PG16 7/1/20 8/31/20
CR86 PG4 9/1/20 9/31/20
CR46 PG4 9/1/20 10/31/20
CR46 PG36 8/1/20 12/30/20
CR46 PG16 8/1/20 9/1/20
Property Owner Relation
PropNo PAddr Rent OwnerNo
PG4 6 Lawrence St, Elmont 1000 C040
PG16 5 Nova Dr, East Meadow 900 C093
PG36 2 Manor Rd Scarsdale 750 C093

These tables are in 3NF in which data redundancy is eliminated along with this functional
dependencies are also removed.
Creation of database through usage of MS-Access
Table 1: Client
Table 2: Property/Room

Table 3: Booking
Table 4: Payment
Table 5: Owner

Create 3 queries
Some queries are illustrated below:
1. SELECT Client_ID FROM Booking
WHERE rent>900
This will lead to select clients from booking table who pay rent more than 900.
2. INSERT INTO Client
VALUE (CR78, PG4, ‘SOPHIE’, 7 Elizabeth Street, ‘7898765562’,
‘[e-mail]’
This query is used to insert value into table client and all the values are inserter within
parenthesis.
3. DELETE FROM Property
WHERE Room_ID = PG36
This will delete the particular information from the table like room_P36 will be deleted
from property.
These are some queries which are being utilized for making modification within database
as per the requirements.

Conclusion
From above it can be concluded that, database management aids firms within storing
information easily in large amount. By making use of Entity relationship diagram relation among
distinct entities can be determined which will further lead to acknowledge entire system and their
working. Normalization is being used to eliminate anomalies which are present within data so
that unnecessary space can be eliminated completely and also ensure that invalid data is also not
added. This implies that repetitions within data can be eliminated, it can be updated and
information can also be retrieved by making use of adequate queries.

REFERENCES
Books & Journals
Chen, X., 2019. Design of a Database Graduate Course as a Leveling Class for Non-CS Major
Graduate Students. The Journal of Computing Sciences in Colleges, p.32.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Elmasri, R. and Navathe, S., 2017. Fundamentals of database systems (Vol. 7). Pearson.
Jensen, C.S. and Snodgrass, R.T., 2018. Temporal Database.
Maiyya, S. and et. al, 2018. Database and distributed computing fundamentals for scalable, fault-
tolerant, and consistent maintenance of blockchains. Proceedings of the VLDB
Endowment, 11(12), pp.2098-2101.