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 ) ;


INSERT INTO film_rental (rentalid , filmname , quantity)

VALUES (8972 , 'Dark Waters' , 1 ) ;


INSERT INTO film_rental (rentalid , filmname , quantity)

VALUES (8972 , 'Taxi Driver' , 2 ) ;


INSERT INTO film_rental (rentalid , filmname , quantity)

VALUES (8972 , 'The Dark Knight' , 2 ) ;


INSERT INTO film_rental (rentalid , filmname , quantity)

VALUES (8972 , 'Forrest Gump' , 1 ) ;


INSERT INTO film_rental (rentalid , filmname , quantity)

VALUES (5119 , 'Dark Waters' , 1 ) ;


INSERT INTO film_rental (rentalid , filmname ,11 quantity)

VALUES (5119, 'Gamer', 3);



Queries

The code explanation for each query is provided in comments (--- “code explanation”)


Query 1

--- Selects three fields from the customer table

SELECT customer.custid,

customer.custfname,

customer.custlname

FROM customer


--- Joins specific needed data from all four tables

JOIN rental

ON customer.custid = rental.customerid

JOIN film_rental

ON rental.rentalid = film_rental.rentalid

JOIN film

ON film_rental.filmName = film.filmName


--- Applies a condition returning films with price equal to £2.6


WHERE film.filmprice_£ = 2.6;



Query 2

--- Selects three fields from the customer table and discards the repeating outputs


SELECT DISTINCT

customer.custid,

customer.custfname,

customer.custlname

FROM customer


--- Joins specific needed data from all four tables

JOIN rental

ON customer.custid = rental.customerid

JOIN film_rental

ON rental.rentalid = film_rental.rentalid

JOIN film

ON film_rental.filmName = film.filmName


--- Applies two conditions returning the customer name and ID if they rented a film for less than £3 but spent more than £15 on the total rental


WHERE film.filmprice_£ < 3 AND rental.totalprice_£ > 15;



Query 3

--- Selects three fields from the customer table and discards the repeating outputs


SELECT DISTINCT

customer.custid,

customer.custfname,

customer.custlname

FROM customer


--- Joins specific needed data from all four tables

JOIN rental

ON customer.custid = rental.customerid

JOIN film_rental

ON rental.rentalid = film_rental.rentalid

JOIN film

ON film_rental.filmName = film.filmName


--- Applies a condition returning the name and ID of the customers that rented the film “Gamer”

WHERE film.filmName = 'Gamer';



Query 4

--- Selects one field from the film table and discards the repeating outputs


SELECT DISTINCT film.filmName

FROM film


--- Joins the filmName from the film table with the filmName from the film_rental table JOIN film_rental

ON film.filmName = film_rental.filmName;



Query 5

--- Selects the field filmName to be outputted

SELECT filmName


--- Adds every rental of all movies to determine which movie(s) has(have) been rented the most times

FROM (SELECT filmName, Sum(film_rental.quantity) AS sum

FROM film_rental GROUP BY filmName) s


--- Selects the filmName to output by selecting the movie(s) which have the most rentals WHERE sum > (SELECT Max(quantity) AS Max

FROM film_rental);



Justification

With the data provided in Table 1, first procedure was to apply the normalisation rules, which allows identifying logical relations that support the data requirements on a business. This makes access easier and ensures that no anomalies rise when maintaining the dataset, such as deletion side effects. This process is frequently referred to as a non-loss decomposition process, because the data structure in continually fragmented into more and more tables without losing the fundamental relationships.


Normalisation is executed as a series of steps starting by collecting the dataset, transforming the dataset from UNF to 1NF, proceeding to 2NF and going to at least 3NF. As normalisation proceeds, requirements become more restricted (Beynon-Davies, 1996, 2004). In the 1NF the table is analysed and it requires that if any attribute is being repeated or multi-value attributes are found, the attribute is removed. The normalisation from 1NF to 2NF involves the removal of partial dependencies and their placement on a new relation. Even though in 2NF the redundancy is less than in 1NF, it is still present and may suffer from update side effects caused by a transitive dependency, and that is why 3NF is necessary. From 2NF to 3NF, the transitive dependencies are removed. Any field dependent not only on the primary key should be moved to another relation.


The technique ER Diagramming is an easier way to visualize a complex system rather than reading a long textual description of a users’ requirements specification. ER Diagrams are used to represent entities and show how they relate to one another more easily. These entities are an element, physical object or a concept, recognised by the company as being able to exist independently and can be uniquely identified. “If you need to store data about many properties of some thing, then that thing is likely to be an entity.”(Beynon-Davies, 1996, p. 163). There are binary relationships (between two entities) and N-ary relationships (between N entities).


In practice, database developers usually do more conceptual data modelling, because it covers more ground, than they do normalisation (Beynon-Davies, 1996, 2004).



Reflection

The way to see a database as a whole and ensure that the inserted data is accurate, without corrupting the dataset by replacing or eliminating important data, granting easy access, is to follow all the necessary steps.


Table 1 has all the information needed to build the database, but because it is not in 3NF it cannot be built from that table. That is where the normalisation process is used and Tables 2, 3, 4 and 5 are created. Those tables are all interlinked and when designed and populated with SQL code produce a functional database. On this scenario, 3NF is enough but on other situations with more data, it may need the use of BNCF, 4NF, 5NF and 6NF requirements.


Even with the tables built and all the data available it can be hard to picture the database. The more data there is, the harder it gets, that is why the ER Diagram is used, as it makes it easier to distinguish entities, connections between them and the attributes. Building this Diagram facilitates the use of SQL code to design the database, making it easier and time saver.


The database design steps are used to facilitate the development and creation of a better dataset.




If you need any hep in Database Design and Implementation, then we are ready to help you.