A SQL script is a set of SQL commands saved as a SQL file. If you are already ru
ID: 3586122 • Letter: A
Question
A SQL script is a set of SQL commands saved as a SQL file. If you are already running MySQL, you can execute a SQL script file using the source command or you can import it in Workbench. Write an SQL script that builds a database to match the relational model provided to you. These SQL statements in the script must be provided in the correct order.
Marks will be awarded for the following:
1. Creating the database (1 mark)
2. Successfully creating new tables (1 mark)
3. Including all attributes (1 mark)
4. Including constraints (1 mark)
5. Correctly creating Primary Keys (1 mark)
6. Correctly creating Foreign Keys (1 mark)
You are required to create a database for the fictitious book store Oktomook for Task 1. The database is based on the model below:
OKTOMOOK RELATIONAL MODEL
Branch (branchNumber, branchName, streetNo, streetName, branchCity, branchState, numberEmployees)
Publisher (publisherCode, publisherName, publisherCity, publisherState)
Author (authorID, firstName, lastName)
Book (ISBN, title, publisherCode, genre, retailPrice, paperback)
Wrote (ISBN, authorID, sequenceNumber)
Inventory (ISBN, branchNumber, quantityInStock)
FOREIGN KEYS •
Book(publisherCode) is dependent on Publisher (publisherCode)
• Wrote (ISBN) is dependent on Book (ISBN)
• Wrote (authorID) is dependent on Author (authorID)
• Inventory (ISBN) is dependent on Book (ISBN)
• Inventory (branchNumber) is dependent on Branch (branchNumber)
OTHER CONSTRAINTS
• The domain of Publisher(state) is [QLD, VIC, NSW, WA, TAS, NT, SA].
• The domain of Book(genre) is [Non-Fiction, Science Fiction, Fantasy, Crime, Mystery, Young Adult, Romance, General Fiction]
• ISBN must be a 13-digit number and may begin with a zero.
• The publisher name and book title are both mandatory.
• Paperback must be either True or False.
• The default quantity in stock is 0.
For task 2,
we have provided you with the creation script for the Treasure Hunter’s database. You should run this script in MySQL Workbench and use this database to extract the necessary information. The script is based on the following schematic:
TREASURE HUNTER’S RELATIONAL MODEL
Player (username, firstName, lastName, gender, DOB, email, streetNo, streetName, suburb, state, postcode, creationDateTime, totalPoints)
PhoneNumber (phoneNumber, username)
Treasure (treasureID, description, points, webpage, type, questID)
Quest (questID, questName, story, beacon, advancedQuestID)
Store (storeID, storeName, openTime, closeTime)
Badge (badgeID, badgeName, badgeDescription)
PlayerProgress (questID, username, progress)
PlayerTreasure (username, treasureID)
Purchase (purchaseID, storeID, username, badgeID, purchaseDateTime, cost)
FOREIGN KEYS
• PhoneNumber (username) is dependent on Player(username)
• Quest (advancedQuestID) is dependent on Quest(questID)
• Treasure (questID) is dependent on Quest (questID)
• PlayerProgress (questID) is dependent on Quest (questID)
• PlayerProgress (username) is dependent on Player (username)
• PlayerTreasure (username) is dependent on Player (username)
• PlayerTreasure (treasureID) is dependent on Treasure (treasureID)
• Purchase (storeID) is dependent on Store (storeID)
• Purchase (username) is dependent on Player (username)
• Purchase (badgeID) is dependent on Badge (badgeID) OTHER CONSTRAINTS
• Player (gender) must be female, male, other or prefer not to disclose.
• Player (state) domain is [QLD, SA, TAS, NSW, WA, NT or ACT].
• Treasure (type) domain is [common, uncommon, rare, ultra-rare or elite].
• Players may enter up to three phone numbers.
• Players must enter at least one phone number.
• PlayerProgress (progress) domain is [active, inactive or complete].
• Player (email) is mandatory
. Query 1 (1 mark)
Write a query to list the name (first and last), date of birth, gender and email of players who live in Sunnybank or Sunnybank Hills. Note that you can assume these are the only suburbs starting with ‘Sunnybank’.
Query 2 (2 mark)
Write a query to find out how much each player has spent at the stores. Your output should be sorted by username in descending order.
Query 3 (2 marks)
Write a query that lists the username and phone number of the oldest player. Note that you must use subqueries for this query
. Query 4 (3 marks)
Write a query that lists all of the badges. If the badge has been purchased include the first name, last name and email address of the player(s). Sort the list based on the badge name followed by first name then last name in ascending order.
Query 5 (3 marks)
List the number of quests embarked upon by each player with progress status ‘complete’ for the treasures that are common
Task 3 [3 marks]
Insert (1 mark)
Write an INSERT command to insert a row into badge table. The badge is called ‘Fools Gold’ and the description should be ‘Trickiest trickster in all the seas’.
Delete (1 marks)
Write a DELETE command to remove all the rows from the player progress table for which progress is inactive.
Update (1 mark)
Write an UPDATE comment to change the address of all players with the last name ‘Smith’ who live at ‘180 Zelda Street, Linkburb’ to ’72 Evergreen Terrace, Springfield'
Task 4 [3 marks]
Create Index (1 mark)
Currently the database only contains a small number of records, however the data contained within it is expected to grow significantly in the future. Creating indexes on commonly searched columns is a way performance issues can be minimized.
Write a command to create an index on webpage of the treasure table.
Create view – 2 marks
Write a command to create a view to list the firstname, lastname and account creation date of any players that haven’t completed any quests.
Task 5 [2 marks]
Treasure Hunters Company have two employees, Nikki and Phil, to work with the MySQL database. Working as MySQL database administrator, provide the commands required to grant or revoke access so the following security requirements are met:
A. User Nikki must be able to add records to the PLAYER table (0.5 Marks)
B. User Nikki must be able to remove records from the PLAYER table (0.5 Marks)
C. User Phil is no longer allowed to add data to the PLAYER table (0.5 Marks)
D. User Phil is no longer allowed to delete records from the PLAYER table (0.5 Marks)
Assume usernames of employees Nikki and Phil are nikki and phil respectively.
Explanation / Answer
/*creating database*/
CREATE database Oktomook;
/*Using database*/
USE Oktomook;
/*creating tables*/
CREATE TABLE Branch(branchNumber int, branchName varchar(30), streetNo int, streetName varchar(30), branchCity varchar(30), branchState varchar(30),
numberEmployees int(20), PRIMARY KEY (branchNumber));
CREATE TABLE Publisher( publisherCode varchar(30), publisherName varchar(30) not null, publisherCity varchar(30),
publisherState ENUM('QLD', 'VIC', 'NSW', 'WA', 'TAS', 'NT', 'SA'),PRIMARY KEY(publisherCode));
CREATE TABLE Author(authorID varchar(30), firstName varchar(20), lastName varchar(20), PRIMARY KEY(authorID));
CREATE TABLE Book (ISBN char(13),title varchar(30)NOT NULL, publisherCode varchar(20),
genre ENUM('Non-Fiction', 'Science Fiction', 'Fantasy, Crime', 'Mystery', 'Young Adult', 'Romance', 'General Fiction') , retailPrice varchar(20), paperback BOOLEAN,
FOREIGN KEY (publisherCode) REFERENCES Publisher(publisherCode), PRIMARY KEY(ISBN));
CREATE TABLE Wrote (ISBN char(13), authorID varchar(20), sequenceNumber int, FOREIGN KEY (ISBN) REFERENCES Book(ISBN),
FOREIGN KEY (authorID) REFERENCES Author(authorID));
CREATE TABLE Inventory (ISBN char(13), branchNumber int, quantityInStock int DEFAULT '0',FOREIGN KEY (ISBN) REFERENCES Book(ISBN),
FOREIGN KEY (branchNumber) REFERENCES Branch(branchNumber));
-----------------------------------------------------------------------------------------------------------------------------------------------------
/* creating datebase*/
CREATE database TREASURE_HUNTER;
/* using datebase*/
use TREASURE_HUNTER;
/* creating tables with required constraints*/
create table Player(username varchar(20) primary key, firstName varchar(20), lastName varchar(20), gender ENUM('female', 'male', 'other', 'prefer not to disclose'),
DOB date, email varchar(20) not null,streetNo varchar(20) ,streetName varchar(20), suburb varchar(20),
state ENUM('QLD', 'SA', 'TAS', 'NSW','WA','NT','ACT'), postcode varchar(20),creationDateTime datetime, totalPoints int);
create table PhoneNumber (phoneNumber BigInt(10) not null, username varchar(20), FOREIGN KEY (username) REFERENCES Player(username));
create table Quest (questID varchar(20) primary key, questName varchar(20), story varchar(20), beacon varchar(20), advancedQuestID varchar(20),
FOREIGN KEY (advancedQuestID) REFERENCES Quest(QuestID));
create table Treasure (treasureID varchar(20) primary key, description varchar(20), points int , webpage varchar(10),
type ENUM('common', 'uncommon', 'rare', 'ultra-rare' ,'elite'), questID varchar(10),FOREIGN KEY (questID) REFERENCES Quest(questID));
create table Store (storeID varchar(20) primary key, storeName varchar(20), openTime time, closeTime time);
create table Badge (badgeID varchar(20) primary key, badgeName varchar(20), badgeDescription varchar(20));
create table PlayerProgress (questID varchar(20), username varchar(20), progress ENUM('active', 'inactive','complete'), FOREIGN KEY (questID) REFERENCES Quest(questID),
FOREIGN KEY (username) REFERENCES Player(username));
create table PlayerTreasure (username varchar(20), treasureID varchar(20), FOREIGN KEY (username) REFERENCES Player(username),
FOREIGN KEY (treasureID) REFERENCES Treasure(treasureID));
create table Purchase (purchaseID varchar(20) primary key, storeID varchar(20), username varchar(20), badgeID varchar(20), purchaseDateTime datetime, cost int,
FOREIGN KEY (storeID) REFERENCES Store(storeID),FOREIGN KEY (username) REFERENCES Player(username),FOREIGN KEY (badgeID) REFERENCES Badge(badgeID) );