Database Design and Analysis of Star Trek Games Rental Shop (IMAT2428)

This assignment focuses on the database design for a Star Trek Games Rental Shop. It begins by identifying the functional requirements and business rules of the shop. The solution then presents an initial entity-relationship diagram (ERD), followed by the normalization of various data sets, including stock enquiry, membership & rentals enquiry, reservations enquiry, rental history data, and catalogue data, demonstrating each normal form. The assignment proceeds to merge and optimize the normalized relations into a third normal form (3NF) ERD. Finally, it compares the initial data model with the 3NF ERD, highlighting and explaining the differences. The solution covers relational data analysis concepts, including determinants, dependency, and the properties of a relation, providing a comprehensive understanding of database design principles and their application in a real-world scenario.

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

1DATABASE DESIGN
Table of Contents
Functional requirements and business rules....................................................................................2
Comparison......................................................................................................................................2
Normalization..................................................................................................................................3
References........................................................................................................................................5

2DATABASE DESIGN
Functional Requirements
Functional requirements means the functions or services that are must for the successful
completion of the system. It defines what should be performed by the system. Functional
requirements are related to business. Some of the functional requirements of the Star Trek
Games Rental Shop are as follows.
 The system will display all details of the game.
 The system will permit the user to choose among their interested games.
 The system will let the customers know about the arrival of their pre-booked game
copies.
 The users can also update their profile details.
 The system will also provide all the details of the booked or ordered games.
Business rules
The given cases study on Star Trek Games rental shop has some business rules. It is
important that the case study meets the business rules. They are-
 The game rental shop has some clients who provides them games. Shop owner buys
game copies from the clients. Each game can have multiple copy of games. When copies
of the game came to the shop they provided some stock number.
 Also the games are provided with unique catalogue number. The unique catalogue
number is provided by the clients who deliver the games.
 When the customers wish to rent the game copies they must register their personal
details. After that the required game copy is handed over to the user.
 Some rules and regulations are there to get the game copy.

3DATABASE DESIGN
 For the particular game, if sufficient game copies are remained in stock then the user can
get the game copy.
 No customer can keep the copy for more than two week. If they keep it for more than two
weeks they have to bare extra charges.
 The amount of extra charges will be double of the daily charges. There is some fixed
amount for daily charges. This may vary with the demands as well.
 In some cases if customers wants to get any game copy which is out of stock then they
can make some advanced booking. As soon as stock is refilled customers can collect their
game copies.
Comparison
As per requirement two relational diagrams has been produced. They has some basic
differences between them. The first one which is done in chains foot notation. The basic table
structure and their attributes are mentioned. Relation between tables are also shown there. Tables
are not in 3nf. Normalization of the relations are also shown in another diagram which is done in
crows’ foot notation. It was possible to make the normalized relationship diagram in chains foot
notation. But for representing foreign keys it was done in crows’ foot notation as well as for clear
and logical relation. In the initial data model foreign keys were not mentioned which was clearly
mentioned in the normalized format. In the initial data model the tables were not in 3nf but in the
normalized format all tables are in 3nf. The initial data model is given below.

4DATABASE DESIGN
E.g., Initial data model
Normalisation
Normalisation is a database design technique that is used to modify the relation and to
minimise redundancy and dependency. In the initial data, model tables are not in 3nf. It is
important to change the tables in 3nf. TO be in 3nf, there are some rules. For 1nf every table,
each attribute should have only one value. If the tables are in 1nf and there exists a single value
primary key, then it can be said that the table is in 2nf. After that, if there is no functional
transitive functional dependency exist, the table is in 3nf. Each table has one value for one

5DATABASE DESIGN
attribute. There is also no multiple values. Therefore each table is in 1nf. For 2nf, there is only
one primary key in each table. For 3nf, each table should not have any functional transitive
dependency exist. In the normalised data diagram, notable has any functional transitive exist.
Therefore it can be stated that our relations are in 3nf. The normalised relationship diagram is
given below.
E.g., Normalized relationship diagram

6DATABASE DESIGN
Relational Data Analysis
UNF 1NF 2NF 3NF
Customer Customer Customer Customer
CustomerDetails CustId CustId CustId
CustName CustName CustName
CustAddress CustAddress CustAddress
CustPhone CustPhone CustPhone
(Game details and
their copies)
Game Game Game
NoofDisk GameId GameId
CatalogueNo NoofDisk NoOfDisk
CatalogueNo Supplier
CatalogurNo
GameCopies GameCopies GameCopies
PricePerCopy StockNo StockNo
PricePerCopy PricePerCopy

7DATABASE DESIGN
Game rent and
renewal of games.
Rent Rent Rent
Price RentId RentId
ReturnedDate RentDate Rentdate
Price Price
ReturnedDate
Renewal Renewal Renewal
RenewalDate RenewalId RenewalId
ReturnedDate RenewalDate RenewalDate
ReturnedDate
Late Renewal Late Renewal Late Renewal
LateReturnDate LateReturnId LateReturnId
LateReturnCharges LateReturnCharges LateReturnDate
LateReturnCharges
AdvancedBooking AdvancedBooking AdvancedBooking
BookingId BookingId
Bookingdate Bookingdate

8DATABASE DESIGN
References
Hasan, M.M., Loucopoulos, P. and Nikolaidou, M., 2014. Classification and qualitative analysis
of non-functional requirements approaches. In Enterprise, Business-Process and Information
Systems Modeling (pp. 348-362). Springer, Berlin, Heidelberg.
Connolly, T. and Begg, C., 2015. Database systems. Pearson Education UK.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Bailis, P., Fekete, A., Franklin, M.J., Ghodsi, A., Hellerstein, J.M. and Stoica, I., 2014.
Coordination avoidance in database systems. Proceedings of the VLDB Endowment, 8(3),
pp.185-196.