ITECH 5006 Database Management Systems Proof Reading Service

ITECH 5006 Database Management Systems Proof Reading Service

1. Translation of ER diagram into a relational schema

Following tables are created from the ER diagram as a relational model for the database. The tables include the primary key and foreign key along with data types of the each attribute.

Primary key in the tables are shown with the underlining of attributes and foreign keys of the tables are italic.

SUPPLIER (supplierID, supName, supContact, supPhone, supEmail, supAddress, supSuburb, supPostcode)

GENRE (genreID, genreName)

OPSYSTEM (opsystemID, opsystemName, opsystemVersion)

ACTOR (actorID, actName, actSurname, actPlaceOfBirth, actDoB, actDoD)

CAST (actorID, inventID, role)

INVENTORY (inventoryID , title , wholesale, markup, discount, qtyInStock, genre, supplier)

MOVIE (inventID, releaseYear, classification, starRating, director)

GAME (inventID, pubYear, rating, publisher, version)

PUBLISHER (publisherID, pubName, pubContact, pubPhone, pubEmail, pubAddress, pubSuburb, pubPostcode, pubCountry)

DIRECTOR (directorID, directName, directSurname, directPlaceOfBirth, directDoB, directDoD)

GAME_OSYSTEM (opsystemID , inventID)

CUSTOMER (customerID, custName, custSurname, custPhone, custEmail, custAddress, custSuburb, custPostcode, custDiscount)

RECIEPT (receipID, customerID, date)

PURCHASE (receiptID, inventID, quantity, amountPaid)

2. Create database and tables

a) SQL statement to create a database named with BHE_Holdings_1234:

CREATE DATABASE BHE_Holdings_1234;

b) CREATE TABLE can be used to create the tables as:

Supplier table

CREATE TABLE SUPPLIER ( supplierID INT NOT NULL AUTO_INCREMENT, supName VARCHAR(30), supContact INT, supPhone INT, supEmail VARCHAR(30), supAddress VARCHAR(30), supSuburb VARCHAR(30), supPostcode INT, PRIMARY KEY (supplierID) );

Actor table

CREATE TABLE ACTOR (actorID INT NOT NULL AUTO_INCREMENT, actName VARCHAR(30), actSurname VARCHAR(30), actPlaceOfBirth VARCHAR(30), actDoB DATE, actDoD DATE, PRIMARY KEY (actorID) );

Genre table

CREATE TABLE GENRE (genreID INT NOT NULL AUTO_INCREMENT, genreName VARCHAR (30), PRIMARY KEY (genreID) );

Opsystem table

CREATE TABLE OPSYSTEM (opsystemID INT NOT NULL AUTO_INCREMENT, opsystemName VARCHAR(30), opsystemVersion CHAR(10), PRIMARY KEY (opsystemID));

Director table

CREATE TABLE DIRECTOR (directorID INT NOT NULL AUTO_INCREMENT, directName VARCHAR(30), directSurname VARCHAR(30), directPlaceOfBirth VARCHAR(30), directDoB DATE, directDoD DATE, PRIMARY KEY (directorID) );

Publisher table

CREATE TABLE PUBLISHER (publisherID INT NOT NULL AUTO_INCREMENT, pubName VARCHAR(30), pubContact INT, pubPhone INT, pubEmail VARCHAR(30), pubAddress VARCHAR(30), pubSuburb VARCHAR(30), pubPostcode INT, pubCountry VARCHAR(30), PRIMARY KEY (publisherID) );

Customer table

CREATE TABLE CUSTOMER (customerID INT NOT NULL AUTO_INCREMENT, custName VARCHAR(30), custSurname VARCHAR(30), custPhone INT, custEmail VARCHAR(30), custAddress VARCHAR(30), custSuburb VARCHAR(30), custPostcode INT, custDiscount DECIMAL (2,2), PRIMARY KEY (publisherID) );

Inventory table

CREATE TABLE INVENTORY (inventoryID INT NOT NULL AUTO_INCREMENT, title VARCHAR(30), wholesale INT, markup FLOAT, discount FLOAT, qtyInStock INT, genreINT, supplier INT, PRIMARY KEY (publisherID), FOREIGN KEY (genere) references GENRE(genreID), FOREIGN KEY (supplier) references GENRE(suplierID)  );

Cast table

CREATE TABLE CAST (actorIDINT NOT NULL, inventIDINT NOT NULL, role TEXT, PRIMARY KEY (actorID, inventID), FOREIGN KEY (actorID) references ACTOR(actID), FOREIGN KEY (inventID) references INVENTORY(inventoryID)  );

Game_osystem table

CREATE TABLE GAME_OSYSTEM (opsystemIDINT NOT NULL, inventIDINT NOT NULL,PRIMARY KEY (opsystemID, inventID), FOREIGN KEY (inventID) references INVENTORY(inventoryID), FOREIGN KEY (opsystemID) references OPSYSTEM(opsystemID)  );

Movie table

CREATE TABLE MOVIE (inventIDINT NOT NULL, releaseYear DATE, classification TEXT, starRating TINYINT, directorINT, name TEXT, PRIMARY KEY (inventID), FOREIGN KEY (director) references DIRECTOR(directorID) );

Receipt table

CREATE TABLE RECEIPT (receiptID INT AUTO_INCREMENT, customerIDINT, r_date DATE, PRIMARY KEY (receipID), FOREIGN KEY (customerID) references CUSTOMER(customerID) );

Purchase table

CREATE TABLE PURCHASE (receiptID INT NOT NULL, inventIDINT NOT NULL, quantity INT, amountPaid FLOAT, PRIMARY KEY (receiptID, inventID), FOREIGN KEY (receiptID) references RECEIPT(receiptID), FOREIGN KEY (inventID) references INVENTORY(inventID) );

Game table

CREATE TABLE GAME (inventIDINT NOT NULL, pubYear DATE, rating TINYINT, publisherINT, version CHAR(10), PRIMARY KEY (inventID), FOREIGN KEY (publisher) references PUBLISHER(publisherID));

3. Insert data into tables

Supplier table

INSERT INTO SUPPLIER (supName, supContact, supPhone, supEmail, supAddress, supSuburb, supPostcode) VALUES (‘Sony movies’, ‘John Carter’, 56545656, ‘sonymovies.com’, ‘123 Big Streat’, ‘New York’, 54533);

Actor table

INSERT INTO ACTOR (actName, actSurname, actPlaceOfBirth, actDoB, actDoD) VALUES (‘Toms’, ‘Crush’, ‘Britain (UK)’, ’12-02-1980’, ‘’);

Genre table

INSERT INTO GENRE (genreName) VALUES (‘top music’);

Opsystem table

INSERT INTO OPSYSTEM (opsystemName, opsystemVersion) VALUES (‘Android’, ‘Kitkat’);

Director table

INSERT INTO DIRECTOR (directName, directSurname, directPlaceOfBirth, directDoB, directDoD) VALUES (‘Mark’, ‘Smith’, ‘New York (UK)’, ’02-02-1930’, ’02-04-1999’);

Publisher table

INSERT INTO PUBLISHER (pubName, pubContact, pubPhone, pubEmail, pubAddress, pubSuburb, pubPostcode, pubCountry) VALUES (‘Wide Road’, ‘Miller Paul’, 786768688, ‘miller@gmail.com’, ‘234 Wall Streat’, ‘New York’, 57768, ‘UK’);

Customer table

INSERT INTO CUSTOMER (custName, custSurname, custPhone, custEmail, custAddress, custSuburb, custPostcode, custDiscount) VALUES (‘Your’, ‘Name’, 75848758, ‘yn@mail.com’, ’54 straight bridge’, ‘New York’, 57682, ‘5.50’);

Inventory table

INSERT INTO INVENTORY (title, wholesale, markup, discount, qtyInStock, supplier, genre) VALUES (‘shut gun’, ‘20.50’, ‘130’, 10, 10, 2, 2);

Cast table

INSERT INTO CAST VALUES (4, 5,‘Super game for child’);

Game_osystem table

INSERT INTO GAME_OSYSTEM VALUES (2,3);

Movie table

INSERT INTO MOVIE VALUES (3, ‘1995’, ‘M’, 4, 7, ‘XXX’);

Receipt table

INSERT INTO RECEIPT (customerID,r_date) VALUES (5, ’12-12-2011’);

Purchase table

INSERT INTO PURCHASE (4, 2, 123, 345.78);

Game table

INSERT INTO GAME (10, ‘2014’, 8, 2, 3.5);

4. SQL Queries

a. List all details of all the records in the customers table, sorted by postcode in descending order

SELECT * FROM CUSTOMER ORDER BY custPostcode DESC;

b. List all the titles of all the items in inventory with their supplier’s name, email, phone number and postcode.

SELECT title FROM INVENTORY WHERE INVENTORY.supplier=SUPPLIER.supplierID;

c. List all the actors who are deceased. Print their name as ONE column with a suitable heading, their date of birth and their date of death.

SELECT TOP 5 CONCAT (actName, actSurname) AS Actor, actDOB, actDOD FROM ACTOR;

d. List all of the inventory by genre. Display the title and the genre of each.

SELECT title, genreName FROM INVENTORY, GENRE WHERE INVENTORY.genre=GENRE.genreID;

e. List all the movies in the shop in alphabetical order. Include movie name, genre, classification, star rating and director name.

SELECT MOVIE.inventID, INVENTORY.title, GENRE.genreNAme MOVIE.classification, MOVIE.starRating, DIRECTOR.directName FROM MOVIE, INVENTORY, GENRE WHERE MOVIE.inventID=INVENTORY.inventoryID AND GENRE.genreName=INVENTORY.genre AND MOVIE.director=DIRECTOR.directName;

f. List all genres that have no titles.

SELECT * FROM GENRE WHERE  genreName IS NULL;

g. Delete the all customers from Beaufort.

DELETE FROM CUSTOMER WHERE custSuburb= ‘Beaufort’;

h. Change customer Mary Spring's name to Mary O’Sullivan.

UPDATE CUSTMER SET custName= ‘Marry’, custSurname= ‘Spring’ WHERE custName= ‘Mary’ AND custSurname= ‘O \’Sullivan’;

i. List all the movie titles with their cast and their director. Print the name of the actors and the directors in one column for each with an appropriate title.

Select MOVIE.Name, CONCAT(directName, directSurname) AS directorName, CONCAT(actName,actSurname) actorName from MOVIE, DIRECTOR, CAST, ACTOR

WHERE MOVIE.directorID= DIRECTOR.directorID

AND CAST.inventID=MOVIE.inventID
and ACTOR.actorID=CAST.actorID

j. Insert a game with the title "Faraway Stars" (genre scifi) published by Moonstruck Games in 2016. The company is located at 1020 Oak Dr, Longwood, 3546, email sales@moonstruck.com.au, phone 4561327895, and contact is Jessie Fandango. The wholesale price is $38 and the mark up is 40%. The shop has 13 in stock and there is no discount applicable to this item.

INSERT INTO PUBLISHER (pubName, pubContact, pubPhone, pubEmail, pubAddress, pubSuburb, pubPostcode, pubCountry) VALUES (‘Moonstruck Games’, ‘Jessie Fandango’, 4561327895, ‘sales@moonstruck.com.au’, ‘1020 Oak Dr’, ‘Longwood’, 3546, ‘AU’);

INSERT INTO INVENTORY (title, wholesale, markup, discount, qtyInStock, supplier, genre) VALUES (‘Faraway Stars’, ‘38’, ‘40’, 0, 13, 2, 2);

k. List all the inventory with its title, its quantity on hand and its sales price (wholesale plus markup).

SELECT title, qtyInStock, SUM (wholesale+markup) AS sales_price FROM INVENTORY;

1. List every item in inventory with its title, genre and whether it is a game or a movie

SELECT title, genre FROM INVENTORY WHERE title LIKE ‘game’;   

5 a) What is a Cartesian product? Give Example.

Cartesian product in the SQL is also referred as the cross-join which is used to return all the rows for all the tables listed in the SQL query. Every row of the first table is mapped with all the rows of another table. In this manner, Cartesian product is fully multiplication of the data of the first table to another table to generate the more linked results (Schwartz et.al, 2012). The syntax for the cross join is given below:

SELECT * FROM table1 CROSS JOIN table2;

Consider the table CUSTOMER and RECIEPT

If CUSTOMER has ten rows and RECIEPT has ten rows then the total number of the rows will be 100. Query would be :

SELECT * FROM CUSTOMER CROSS JOIN RECIEPT;

Cross join is a problem in the SQL because it generates the large set of rows. The data is unnecessary replicated and it becomes ambiguous to find the proper records for the resultant table.

b) How do you prevent a Cartesian product

Cartesian product in the SQL queries can be prevented in the following manner:

Left join:two tables can be joined with the help of left join to remove the Cartesian product as it will only include the data which is not available in the first table and can be captured from the second table. In this manner, the duplication of the data in first table can be prevented and unmatched records from the second table are discarded.

Right join: it is used to complete the table at right side by achieving the rows from the first table for specific criteria (Grust & Rittinger, 2013). In this manner, the data of the first table is consumed by the second table.

Explicit join: rather than to join the two tables, perfectly, their columns can be identified to take participation in joining. In this manner, external queries are useful to fetch the important data from both the tables effectively.

In the SQL presented in  5a, customerID is the primary key in CUSTOMER table and is the foreign key in RECIEPT table and would be used in the join condition.

SELECT * FROM CUSTOMER , RECIEPT where CUSTOMER.customerID= RECIEPT.customerID.

The query would fetch only fetch those Customer records where the customerID value matches in RECIEPT table.

References

Grust, T., & Rittinger, J. (2013). Observing SQL queries in their natural habitat. ACM Transactions on Database Systems(TODS), 38(1), 3.

Schwartz, B., Zaitsev, P., & Tkachenko, V. (2012). High performance MySQL: optimization, backups, and replication. " O'Reilly Media, Inc.".