realcode4you
- Jul 5, 2021
- 7 min read

Database Design Implementation Assignment Help

Table of Contents

Introduction
Main body
Justification
Reflection
References

Table of Figures and Tables

Table 1 Information regarding Movie Rental system

Table 1 Customer Table

Table 3 Rental Table

Table 4 Film Table

Table 5 Film_Rental Table

Figure 1 ER Diagram

Introduction

Database Design is a group of steps that help from the creation of a data management system to maintaining one. A well-designed database includes a large quantity of information placed in a framework, making it easier to provide access to precise and up-to-date information (Connolly and Begg, 2015).

The aim of this project is to implement database design steps in a Movie Rental business, with the purpose of storing relevant data about the customers and the movies they have rented. The business has a system, represented in Table 1, that “In its current form, the table is a traditional database. Should you keep it that way?” The answer to this question triggers the project and yes, it should be kept that way. Traditional databases, also known as relational databases, organize the data into tables, which can be related based on common data (Halpin, 2001), enabling a better understanding of the relationships among the entities. Relational databases have several advantages like the process of normalisation, less redundancy, the use of SQL and DDL, more flexibility when changing or adding data, being transactional which makes it easier to backup.

The project advances with the process of implementing the database, followed by the justification of the database design steps.

Implementing the database using SQL

Normalisation:

The initial table was in 1NF. In order to be in 2NF redundant data was moved to a separate table, remaining the information shown above in Table 2, which was already in 3NF since every field depended on the primary key.

Considering the remaining data, it was clear that it would be easier to group the data in tables where the fields were dependent on the primary key, creating Tables 3 and 4. They were already in 3NF, achieving all the requirements for 1NF and 2NF

Almost all of the data was separated in tables, only “quantity” was missing, so the best way to incorporate that data and connect it to the rest of the database was to make a relationship with the Rental Table and the Film Table, with 2 Foreign Keys creating a Composite Primary Key.

Entities and Attributes

Entities :

Customer
Rental
Film

Attributes:

custID
custFName
custLName
custEmail
rentalID
rentalDate
totalPrice_£
filmName
filmPrice_£
quantity

ER Diagram

Customer Table was in a one-to-many relation with the Rental Table and connected by a Foreign Key, custID. The relationship established between the Rental and Film Table was a many-tomany relationship established with a pair of one-to-many relationships connected by a linked entity formed by two Foreign Keys, creating a Composite Primary Key.

SQL commands used to create and populate the tables

--Customer data

CREATE TABLE customer ( custid INT PRIMARY KEY, custfname CHAR(50) NOT NULL, custlname CHAR(50) NOT NULL, custemail VARCHAR(50) NOT NULL );

INSERT INTO customer (custid, custfname, custlname, custemail) VALUES (456123, 'Daniel', 'Smith', 'ds@gmail.com'); INSERT INTO customer (custid, custfname, custlname, custemail)

VALUES (827162, 'Roger', 'Williams', 'rw@gmail.com'); INSERT INTO customer (custid, custfname, custlname, custemail) VALUES (198256, 'Jon', 'Snow', 'js@gmail.com');

INSERT INTO customer (custid, custfname, custlname, custemail)

VALUES (049821, 'Angela', 'Jones', 'aj@gmail.com');

--Rental data

CREATE TABLE rental ( rentalid INT PRIMARY KEY, customerid INT REFERENCES customer(custid), rentaldate DATE NOT NULL, totalprice_£ DECIMAL(5, 2) NOT NULL );

INSERT INTO rental (rentalid, customerid, rentaldate, totalprice_£)

VALUES (2594, 456123, To_date('12/09/2019', 'DD/MM/YYYY'), 23.1);

INSERT INTO rental (rentalid, customerid, rentaldate, totalprice_£)

VALUES (3412, 827162, To_date('05/01/2020', 'DD/MM/YYYY'), 2.1);

INSERT INTO rental (rentalid, customerid, rentaldate, totalprice_£)

VALUES (8972, 198256, To_date('26/08/2020', 'DD/MM/YYYY'), 17.3);

INSERT INTO rental (rentalid, customerid, rentaldate, totalprice_£)

VALUES (5119, 049821, To_date('18/03/2020', 'DD/MM/YYYY'), 12.2);

--film data

CREATE TABLE film ( filmname VARCHAR(50) PRIMARY KEY, filmprice_£ DECIMAL(5, 2) NOT NULL );

INSERT INTO film (filmname, filmprice_£) VALUES ('Gamer', 3.2); INSERT INTO film (filmname, filmprice_£)

VALUES ('Dark Waters', 2.6);

INSERT INTO film (filmname, filmprice_£)

VALUES ('Inception', 4.9);

INSERT INTO film (filmname, filmprice_£)

VALUES ('A Space Odyssey', 2.1);

INSERT INTO film (filmname, filmprice_£)

VALUES ('Taxi Driver', 1.9);

INSERT INTO film (filmname,9 filmprice_£)

VALUES ('The Dark Knight', 3.5);

INSERT INTO film (filmname, filmprice_£)

VALUES ('Forrest Gump', 3.9);

--Film_rental

CREATE TABLE film_rental ( rentalid INT NOT NULL REFERENCES rental(rentalid), filmname VARCHAR(50) NOT NULL REFERENCES film(filmname), quantity INT, PRIMARY KEY(filmname, rentalid) );

INSERT INTO film_rental (rentalid, filmname, quantity)

VALUES (2594, 'Gamer', 1);

INSERT INTO film_rental (rentalid, filmname, quantity)

VALUES (2594, 'Dark Waters', 2);

INSERT INTO film_rental (rentalid, filmname, quantity)

VALUES (2594, 'Inception', 3);

INSERT INTO film_rental (rentalid, filmname,10 quantity)

VALUES (3412 , 'A Space Odyssey' , 1 ) ;