Database Design Project: Hot Water Spa Case Study - ITC556

Desklib provides solved assignments and past papers for students.

ITC556 - DATABASE SYSTEMS
ASSESSMENT ITEM 2

Contents
Introduction................................................................................................................................3
Part A.........................................................................................................................................4
Part B..........................................................................................................................................5
Assumptions...............................................................................................................................5
Conclusion..................................................................................................................................7
References..................................................................................................................................8
Table of Figure
Figure 1: ER Diagram................................................................................................................4

Introduction
The assignment is based on the diagram that needs to create in the provided case study which
is based on the company named Hot Water which provides spa facilities for the customers.
The provided relational diagram will show the relationships among the entities that are
present in the diagram. The diagram is created and given in the further file.

Part A
Data of the organization or large enterprises were previously stored in the file form and file
systems that are now obsolete and they were not as developed as now because of some issues
that needed to be recovered. These issues were related to the inconsistency and data fetching
that made the databases difficult to store in the file systems. There are three developments
that improvised the database system and these developments include the introduction to the
structured query language, a schema that helps in defining the structure of database and
atomicity property.
These three developments provide effective results in the databases like the atomicity of the
database helps to not accept duplicate values. It is used so that repeated data cannot be added
in the database in order to maintain the consistency and proper management and also to avoid
confusion for the admin who accesses the data. This property is one of the ACID properties
of the database management systems. Next, Query language that comprised of various
commands which helps in accessing the database more easily and frequently. These
commands when executed and written properly then the result like fetching or updating the
database can be shown within some seconds that means various changes can be made without
wasting much time. This provides easiness to access the database. The last one is the database
schema that helps in making the structure of the database so that the whole database can be
understood appropriately and easily.

Part B
Diagram
Figure 1: ER Diagram
Assumptions
There are a few assumptions that are mentioned here:
1. One customer can place more than one order so the entities customer and order will
have a one-to-many relationship with each other.
2. One manufacturer can manufacture more than one single brand so these two entities
are interconnected with each other.

3. As for every manufacturer, there should be unique identification so there is a primary
key that is assigned here named as MFT_ID in which duplicate entries cannot exist.
4. The single brand can create multiple models so the entities are connected with each
other by one-to-many relationship (Myszkorowski, 2018).
5. For one model there is a possibility to have multiple orders so they are also connected
with the one-to-many relationship (Letkowski, 2015).
6. BRND_ID is used to the unique identification of the brand so it is assigned as the
primary key in the Brand table (Tseng & Chou, 2018).
7. The BRND_ID is taking reference from Brand table and existing as a foreign key in
the Table Model. So it is shown in italic font in the Table Model.
8. For every customer, there is a different identification so the CU_ID is the customer ID
that can be store to differentiate customers (Kahng et al., 2016).
9. Table Customer contains the information regarding the customers so few attributes
are created in order to store their details.
10. In table ORDER, ORD_ID is the primary key where every id of the order is stored
uniquely and MDL_NUM and CU_ID are foreign keys here as they are taking
reference from other tables.

Conclusion
According to the case study, the diagram is created and explained as well. There are various
assumptions that are provided above according to the diagram that is created by using the
draw.io and the image of the diagram is attached in the above report. There are few entities
that are related to other by relationships.

References
Kahng, M., Navathe, S. B., Stasko, J. T., & Chau, D. H. P. (2016). Interactive browsing and
navigation in relational databases. Proceedings of the VLDB Endowment, 9(12), 1017-1028.
Tseng, F. S., & Chou, A. Y. (2018, July). Generating Social Relationships from Relational
Databases for Graph Database Creation and Social Business Intelligence Management.
In Science and Information Conference (pp. 372-393). Springer, Cham.
Myszkorowski, K. (2018). Fuzzy recursive relationships in relational databases. Information
Systems in Management, 7.
Letkowski, J. (2015). Doing database design with MySQL. Journal of Technology
Research, 6, 1.