Assignment 1
Project Part A
The Task for Part A
You are to design a solution for a database management system that addresses the needs of an online
augmented reality game.
In this part you will design a database by:
1. Creating an ER model for the database based on a use case;
2. Creating a relational schema for the database; and,
3. Validating the relational schema for the database using normalisation.
Background
You have been hired by the Brisbane based company Aggity Games. Aggity are passionate about bringingaugmented reality games to the masses. After some success with their previous game, Bush Rangers, theyare keen to get you on board to design the database for their new game, which is called Treasure Hunters!
In Treasure Hunters players will travel to different locations in the real world in the hunt for treasure. Theirsmartphones will act as a gateway into the treasure hunter world, with real objects taking on different roleswithin the game, such as beacons that unlock quests or the elusive treasure.
It is nowyour task to design the database.
The Scenario – Treasure Hunters, an augmented reality online game.
In order to play the game, each player creates their account. They must choose a unique username,
provide their name (first and last names), gender (female, male, other or prefer not to disclose), date of
birth, email and postal address (street number, street name, suburb, city and postcode) and up to three
phone numbers. The date and time of account creation and the player’s total points will also be associated
with their account.
The game revolves around players embarking on quests and hunting for treasure. Multiple players can go
on the same quest and find the same treasure. There are hundreds of quests, new quests are added every
week and some of the more advanced quests can contain up to five mini quests. Each quest needs to be
identifiable using a unique id, and they also have a name. Players initiate quests by finding objects in the
real world, which act as beacons, and each quest is assigned a beacon. Once the player finds a beacon
they will be presented with a story, which provides some background on the quest, and a list of the treasurethey must hunt down in order to finish the quest. The database needs to keep track of each player’sprogress (active, complete, inactive) on each quest.
There are different types of treasure (common, uncommon, rare, ultra-rare and elite). The players are
directed to a webpage where they can obtain various clues to find the treasure. Each treasure has one webpage. Whena player finds the treasure, they are awarded points. Each piece of treasure rewards a different amount ofpoints. When a player unlocks the list of treasures for a quest, they can see a description for each piece.
Players can use their points to purchase badges from virtual stores. All stores have a name, and they are
only open at certain times of the day, so their opening and closing times need to be recorded.
In order to purchase badges from the stores, players can use their points. There are a lot of different
badges that players can purchase. To help players decide what to purchase they will be able to read the
badge names and descriptions. The price of the badges varies depending on demand, and is re-calculated
when a player reaches a store. This means that the price itself is not stored in the database. However,
when a player purchases a badge from a store, the store name, date and time of purchase and the cost of
the purchase need to be recorded, so that they can see a detailed list of their purchases at any time.
Part A Tasks
Part A requires you to complete a number of sequential tasks to fulfill the requirements of the scenario. In
this part you will design a database by:
1. Creating an ER model for the database;
2. Deriving the relational schema for the new database;
3. Demonstrating the constraints of the relational schema through examples; and
4. Verifying a previous (different) relational schema for the database using normalization;
Task 1
Considering the use case provided, create an Entity Relationship Diagram that correctly models the data
requirements of Treasure Hunters. You must ensure that everything that happens in the game (or in
relation to the game) is recorded. Your ER diagram (using UML notation) needs to show:
the entity types,
the binary relationship types,
the complex relationship type,
the multiplicity of relationship types,
the attributes (and if applicable their domains) of entity types,
the attributes (and if applicable their domains) of relationship types, and
the primary key of each entity type.
State any assumptions you make.
Task 2
Derive a Relational Model from the conceptual schema you provided in Task 1 (it should match exactly).
For the purpose of this assignment, only derive the part of the database that stores the information about
players, quests, beacons and treasures (considering a sub-ERD), and leave aside the information relating
to stores and badges.
- List all the relations derived from the entity types of the ERD, and their initial attributes. For each relationship type in the sub-ERD justify whether it leads to a new relation, a new attribute in a relation derived from an entity type, or to a merger of two relations derived from two entity types. 3. List all the relations in the final relational model, and all their attributes 4. List all the constraints included in the conceptual schema in addition to the relevant primary keys and foreign keys.
Task 3
Create four rows of sample data for each of the relations you identified in Task 2.
Based on your relational model and sample data, specify an example of each of the following, andexplain why you selected it:
A delete operation that would run successfully
An update operation that would run successfully
An update operation that would not run successfully
An insert operation that would not run successfully
Task 4
Aggity Games had an intern who prototyped the database for the players and quests, but they could never
implement it, and they have already been told this is really poor design. This is what it looks like, with
some sample data:
| Player Name | Quests | Points | Address_id |
| Amy | Q33 Q12 Q25 | 674 | 2 |
| Michael | Q54 Q25 | 78 | 2 |
| Emily | Q33 | NULL | 5 |
| Tom | Q45 | 54 | 6 |
Treasure
| Name | Description | Quest_id | Quest Name | Beacon |
| Diamond | The tree’s diamonds | Q25 | Urban Elves | Toohey Forest Sign |
| Heart | The Big Gum’s heart | Q25 | Urban Elves | Toohey Forest Sign |
| Diamond | The river’s diamonds | Q33 | Pirates of Brisbane River | City Cat (Broncos) |
| Gold | The Captain’s chest | Q33 | Pirates of Brisbane River | City Cat (Broncos) |
Address
| Id | Street number | Street name | city | postcode |
| 2 | 15 | Queen Street | Brisbane | 4000 |
| 3 | 2 | Edward Street | Noosa | 4230 |
| 5 | 6 | Alice Street | Gympie | 5533 |
| 6 | 28 | George Street | Bundaberg | 4332 |
For each of the 3 relations proposed (Player, Treasure, Address), explain which normal form (0NF, 1NF or2NF) it is in, and why.
Project Part B
For this assessment, you will:
1. Build a script that will create a database for a given relational schema;
2. Provide the SQL commands needed to retrieve the required data using assessment 1a extended
schema;
3. Provide the commands to modify (INSERT, UPDATE & DELETE) the data using assessment 1a
extended schema;
4. Provide the commands needed to create appropriate indexes and views;
5. Provide advice on the basic security measures that should be implemented.
Task 1
Oktomook Book Store Database
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.
- Creating the database
- Successfully creating new tables
- Including all attributes
- Including constraints
- Correctly creating Primary Keys
- Correctly creating Foreign Keys
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.
Task 2
using the Treasure Hunter’s database
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
Write a query to list the name (first and last), date of birth, gender and email of players who live in Sunnybankor Sunnybank Hills. Note that you can assume these are the only suburbs starting with ‘Sunnybank’.
Query 2
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
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
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
List the number of quests embarked upon by each player with progress status ‘complete’ for the treasures
that is common.
Task 3
Insert
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
Write a DELETE command to remove all the rows from the player progress table for which progress is
inactive.
Update
Write an UPDATE comment to change the address of all players with the last name ‘Smith’ who live at ‘180Zelda Street, Linkburb’ to ’72 Evergreen Terrace, Springfield’.
Task 4
Create Index
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
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
Treasure Hunters Company has 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.
TreasureHunter(1).sql
#create and use the TreasureHunter database
CREATE DATABASE IF NOT EXISTS `treasurehunters`;
USE `treasurehunters`;
#create and populate the badge table, which has descriptions of all the types of badges
DROP TABLE IF EXISTS `badge`;
CREATE TABLE `badge` (
`badgeID` INT NOT NULL AUTO_INCREMENT,
`badgeName` VARCHAR(20) DEFAULT NULL,
`badgeDescription` VARCHAR(60) DEFAULT NULL,
PRIMARY KEY (`badgeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `badge` WRITE;
INSERT INTO `badge` (badgeName, badgeDescription) VALUES
('Welcome','This is your first badge!'),
('Beacon','Congrats on finding your first beacon!'),
('Treasure','Congrats on your first treasure!'),
('100Treasures','You\'ve found 100 treasures!'),
('500Treasures','You\'ve found 500 treasures!'),
('5000Treasures','You\'ve found 5000 treasures!'),
('BoyScout','Keep on truck\'in!'),
('RealmKnight','Knight of the Realm and Saviour of the Meek!'),
('TimeTraveller','Man from Gallifrey! Who?'),
('PowerRanger','Mighty Morphin Treasure Hunter!'),
('Knightmare','Master of the night!'),
('BlackBeard','Argh! I be Black Beard! Where be my booty?'),
('CaptainSponge','Life\'s true treasure is having fun!'),
('PeepingTom','Sneakiest sneak of sneakington'),
('AdvanceTreasureFair','Congrats on finishing your first Advanced Quest!'),
('HowCute','You\'ve completed your first mini quest!'),
('Meow','The cats out of the bag!');
UNLOCK TABLES;
#create and populate the quest table which stores the record of all quests
DROP TABLE IF EXISTS `quest`;
CREATE TABLE `quest` (
`questID` INT NOT NULL AUTO_INCREMENT,
`questName` VARCHAR(45) DEFAULT NULL,
`beacon` VARCHAR(45) DEFAULT NULL,
`story` TEXT DEFAULT NULL,
`advanceQuestID` INT DEFAULT NULL,
PRIMARY KEY (`questID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `quest` WRITE;
INSERT INTO `quest` (questName, beacon, story, advanceQuestID) VALUES
('Good Will','QUT Gardens Point Campus, Brisbane','Venture to the brown waters of mucky muck over a bridge of good will. Good luck!',NULL),
('Mini Good Will','Good Will Bridge, Brisbane','Gaze across the waters of mucky muck in search of a floating cat',1),
('South Bank','South Bank','Stumble upon the site of EXPO 88 and hunt its many treasures',NULL),
('Mini South Bank 1','Information Centre, South Bank','You\'re lost! Go in search of a map', 3),
('Mini South Bank 2','Big Wheel, South Bank','The Wheel marks the Spot. Round and round and round...', 3),
('Mini South Bank 3','Exhibition Centre, South Bank','There be a place of booths and booty a plenty. Find a magical machine selling golden vests of glory', 3),
('Mini South Bank 4','QPAC, South Bank','Welcome, welcome, one and all. Find a man in a costume.', 3),
('Surfing','Surfers Paradise Beach, Gold Coast','Argh me lad (or lady) we be headin to the sea! Buried below a sign of silver surf boards be the treasure we seek.', NULL),
('Dreamers','Dreamworld Entry, Gold Coast','Ick they be but plastic but the people of the dream world will pay precious gold for this booty',NULL),
('Seaworld Seal','Seaworld, Gold Coast','Splish splash, hunt the seal who be a show off.', NULL),
('Dump','Recycle Centre, Logan','Pooh-ey! What a dump!?! Mix with the Logans to find discarded treasure that shimmers', NULL),
('Valley','Fortitude Valley','Drunks and derelicts. Clubbers and bouncers. Seek the gates out of there.', NULL);
UNLOCK TABLES;
#create and populate the store table which stores the record of all of the game stores
DROP TABLE IF EXISTS `store`;
CREATE TABLE `store` (
`storeID` INT NOT NULL AUTO_INCREMENT,
`storeName` VARCHAR(45) DEFAULT NULL,
`openTime` TIME DEFAULT NULL,
`closeTime` TIME DEFAULT NULL,
PRIMARY KEY (`storeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `store` WRITE;
INSERT INTO `store` (storeName, openTime, closeTime) VALUES
('Farmer Joes','08:00:00','16:00:00'),
('All traders','01:00:00','23:00:00'),
('24/7','00:01:00','23:59:00'),
('Farmer Joes','08:00:00','16:00:00'),
('Banana Pants','01:30:00','23:30:00'),
('FaceyFacey','15:00:00','23:59:00'),
('Black Death','21:00:00','23:00:00'),
('Phone Booth','00:10:00','23:10:00'),
('Pineapple under the sea','00:01:00','23:59:00'),
('Yo Mumma\'s House','08:00:00','16:00:00'),
('Weapon\'s Mart','01:00:00','23:00:00'),
('Bag it up Shoppy','00:01:00','23:59:00'),
('Kangaroo-la-lu', '06:00:00', '11:00:00');
UNLOCK TABLES;
#create and populate the treasure table which stores the record of all of the game treasures
DROP TABLE IF EXISTS `treasure`;
CREATE TABLE `treasure` (
`treasureID` INT NOT NULL AUTO_INCREMENT,
`description` TEXT DEFAULT NULL,
`points` INT NOT NULL DEFAULT '0',
`webpage` VARCHAR(100) NOT NULL,
`questID` INT,
`type` ENUM('common','rare','ultra-rare','elite','uncommon') DEFAULT NULL,
PRIMARY KEY (`treasureID`),
CONSTRAINT `questID` FOREIGN KEY (`questID`) REFERENCES `quest` (`questID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `treasure` WRITE;
INSERT INTO `treasure` (description, points, webpage, questID, `type`) VALUES
('Pebble',10,'www.rocky.com',1,'common'),
('City Cat',15,'www.meow.com.au',1,'ultra-rare'),
('Rainbow',30,'www.redandblueandgreenandpurple.com.au/qut',1,'elite'),
('Pidgeon',5,'https://qut.edu.au/treasurehunter',1,'common'),
('Gold medalleon',100,'https://treasurehunters.com/9998%$uios&#js',1,'uncommon'),
('Ibis',20,'https://www.test.com.au',1,'elite'),
('Dirty dish rag',5,'www.howdirty.com',11,'common'),
('Rusty bike',5,'www.howrusty.com',11,'common'),
('Southbank Map',10,'www.directionsatlast.com',2,'common'),
('Brisbane Map',15,'www.directionstocity',2,'ultra-rare'),
('Gold Coast Map',30,'www.waveymap.com',2,'elite'),
('Star Fish',25,'www.pointyfish.com',10,'common'),
('Only 1 exists!',100,'https://treasurehunters.com/9998%$uios&#js',1,'uncommon'),
('Sea cucumber',150,'https://www.test.com.au',10,'elite'),
('Cigarette Butt',5,'www.ewwwwhowdirty.com',12,'common'),
('Bouncer',65,'www.youhaveguts.com',12,'rare'),
('Red lego brick',10,'www.lego.com/red',9,'common'),
('Blue lego brick',10,'www.lego.com/blue',9,'common'),
('Green lego brick', 10,'www.lego.com/green',9,'common'),
('Lego House',20,'www.lego.com/house',9,'rare'),
('Lego Batman',100,'www.lego.com/batman',9,'ultra-rare'),
('Seal',20,'www.splishsplash.me',10,'uncommon'),
('Stage',22,'www.encore.com',7,'uncommon'),
('Life Line Book Fest Sign',200,'www.howrusty.com',6,'ultra-rare'),
('Brisbane Sign',30,'www.hellobrisbane.com',3,'uncommon'),
('Big Wheel Ticket',300,'www.nooneridesthewheel.com',5,'ultra-rare'),
('Surfboard',40,'www.waverider.com',8,'uncommon');
UNLOCK TABLES;
#create and populate the player table which has all of the user information
DROP TABLE IF EXISTS `player`;
CREATE TABLE `player` (
`username` VARCHAR(30) NOT NULL,
`firstName` VARCHAR(50) DEFAULT NULL,
`lastName` VARCHAR(50) DEFAULT NULL,
`gender` ENUM('Female','Male','Other','Prefer not to disclose') DEFAULT NULL,
`dob` DATE DEFAULT NULL,
email VARCHAR(320) NOT NULL,
`streetNumber` VARCHAR(15) DEFAULT NULL,
`streetName` VARCHAR(30) DEFAULT NULL,
`suburb` VARCHAR(45) DEFAULT NULL,
`state` ENUM ('QLD', 'SA', 'VIC', 'NSW', 'TAS', 'WA', 'NT', 'ACT') DEFAULT NULL,
`postcode` char(4) DEFAULT NULL,
`creationDateTime` DATETIME NOT NULL,
`totalPoints` INT NOT NULL DEFAULT 0,
PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `player` WRITE;
INSERT INTO `player` VALUES
('frankie','Frank','Smith','Male','1999-03-12', '[email protected]','180','Zelda Street','Linkburb','QLD','4000','2017-09-06 07:00:05',10),
('jerry','Jerry','Seinfield','Other','1967-12-04', '[email protected]','3/5999','Elanor Road','Stafford','QLD','4053','2017-09-07 18:00:44',50),
('jojo','Joanne','Kay','Prefer not to disclose','2000-08-02', '[email protected]','7','Latrobe Tce','Docklands','VIC','2000','2017-09-06 07:00:05',20),
('nikki','Nikki','Storm','Female','1989-08-11', '[email protected]','1007','National Circuit','Barton','ACT','2600','2017-09-09 14:36:05',80),
('drwho','Doctor','Who','Male','1800-01-01', '[email protected]','1','PhoneBooth','Boothby','QLD','4000','2017-05-09 11:00:05',520),
('chickenNeck','Graham','Marshall','Male','1995-10-25','[email protected]', '53','Thugsta Drive','Helensvale','QLD','4212','2017-09-07 17:00:44',50),
('banana95','Alanna','Davidson','Female','1995-05-14', '[email protected]','587','Gumby Road','Sunnybank Hills','QLD','4109','2017-03-14 21:45:38',615),
('ml','Tsai','MengLung','Male','1990-03-02', '[email protected]', '341','Main Street','Sunnybank','QLD','4109','2017-09-06 07:00:05',420),
('twowong','Jason','Wong','Male','1989-11-15', '[email protected]', '124','Seetan Street','Sunnybank Hills','QLD','4109','2017-09-09 14:36:05',380),
('namasaya','Sietta','Stonebert', 'Female','1989-03-05', '[email protected]', '21-29','Farris Street','Sunnybank','QLD','4109','2017-09-06 07:00:05',220),
('jeffas','Jeffrey','Davidson','Male','1987-12-04', '[email protected]','31/50','Ronker Road','Woolloongabba','QLD','4053','2017-10-17 12:00:44',311),
('jojo123','Jessica','Kanikian','Female','1998-11-21','[email protected]', '17','Main Street','Sunnybank','QLD','4109','2017-08-04 13:00:00',20),
('haxman','Allisandra','Zanita','Female','1972-08-11','[email protected]', '78','O\'Grady Street','Upper Mount Gravatt','QLD','4012','2017-05-15 14:36:05',15);
UNLOCK TABLES;
#create and populate the phonenumber table which has all of the users phone numbers
DROP TABLE IF EXISTS `phonenumber`;
CREATE TABLE `phonenumber` (
`username` VARCHAR(30) NOT NULL,
`phoneNumber` CHAR(10) NOT NULL,
PRIMARY KEY (`username`,`phoneNumber`),
FOREIGN KEY (`username`) REFERENCES `player` (`username`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `phonenumber` WRITE;
INSERT INTO `phonenumber` VALUES
('frankie','0400123977'),
('banana95', '3698521478'),
('frankie','0733405211'),
('jerry','0456789012'),
('jojo','0400123456'),
('jojo','0466111222'),
('nikki','0215154878'),
('drwho','0458987896'),
('chickenNeck','0145232147'),
('chickenNeck','0425647895'),
('chickenNeck','0412354789'),
('ml','5554785471'),
('twowong','3802545411'),
('namasaya','2314528974'),
('jeffas','0485778991'),
('jojo123','3725896523'),
('haxman','2587487456');
UNLOCK TABLES;
#create and populate the playerprogress table which stores the record of the players progress for each quest
DROP TABLE IF EXISTS `playerprogress`;
CREATE TABLE `playerprogress` (
`questID` int NOT NULL,
`username` VARCHAR(30) NOT NULL,
`progress` ENUM('active','complete','inactive') DEFAULT NULL,
PRIMARY KEY (`questID`,`username`),
CONSTRAINT `playerprogressquestID` FOREIGN KEY (`questID`) REFERENCES `quest` (`questID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `username` FOREIGN KEY (`username`) REFERENCES `player` (`username`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `playerprogress` WRITE;
INSERT INTO `playerprogress` VALUES
(1,'frankie','inactive'),
(1, 'banana95', 'complete'),
(1,'jerry','active'),
(1,'jojo','complete'),
(1,'nikki','complete'),
(2,'frankie','active'),
(2, 'banana95', 'complete'),
(2,'jerry','inactive'),
(2,'nikki','complete'),
(12,'jeffas','inactive'),
(2, 'jeffas', 'complete'),
(1,'drwho','active'),
(1,'chickenNeck','complete'),
(1,'twowong','complete'),
(2,'drwho','active'),
(2,'ml','inactive'),
(1,'jojo123','complete'),
(1,'haxman','active'),
(1,'namasaya','complete'),
(3, 'banana95','complete'),
(3,'drwho','complete'),
(3,'frankie','active'),
(4,'jerry','inactive'),
(4, 'banana95', 'active'),
(11,'frankie','inactive'),
(11, 'banana95','complete'),
(5,'jerry','active'),
(6,'jojo','complete'),
(7,'nikki','complete'),
(7,'banana95','complete'),
(8,'frankie','active'),
(9,'jerry','inactive'),
(9, 'banana95','inactive'),
(9,'nikki','complete');
UNLOCK TABLES;
##create and populate the playertreasure table which stores the record of the player locating the treasures
DROP TABLE IF EXISTS `playertreasure`;
CREATE TABLE `playertreasure` (
`username` VARCHAR(30) NOT NULL,
`treasureID` INT NOT NULL,
PRIMARY KEY (`username`,`treasureID`),
KEY `treasureID_idx` (`treasureID`),
CONSTRAINT `treasureID` FOREIGN KEY (`treasureID`) REFERENCES `treasure` (`treasureID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `usernametreasure` FOREIGN KEY (`username`) REFERENCES `player` (`username`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `playertreasure` WRITE;
INSERT INTO `playertreasure` VALUES
('frankie',1),
('jerry',1),
('frankie',3),
('frankie',2),
('jerry',2),
('jojo',4),
('jojo', 14),
('nikki',4),
('drwho', 6),
('chickenNeck', 5),
('chickenNeck', 3),
('chickenNeck', 6),
('ml', 27),
('twowong',15),
('namasaya', 23),
('jeffas', 15),
('jojo123', 15),
('haxman', 8),
('banana95', 1),
('banana95', 2),
('banana95', 3),
('jeffas', 4);
UNLOCK TABLES;
#create and populate the purchases table which stores the records of the player buying badges at the stores
DROP TABLE IF EXISTS `purchase`;
CREATE TABLE `purchase` (
`purchaseID` INT NOT NULL AUTO_INCREMENT,
`storeID` INT NOT NULL,
`badgeID` INT NOT NULL,
`username` VARCHAR(30) NOT NULL,
`cost` INT NOT NULL,
`purchaseDateTime` DATETIME NOT NULL,
PRIMARY KEY (`purchaseID`),
KEY `store_idx` (`storeID`),
KEY `badge_idx` (`badgeID`),
KEY `usernamepurchase_idx` (`username`),
FOREIGN KEY (`badgeID`) REFERENCES `badge` (`badgeID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY (`storeID`) REFERENCES `store` (`storeID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY (`username`) REFERENCES `player` (`username`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `purchase` WRITE;
INSERT INTO `purchase` (storeID, badgeID, cost, purchaseDateTime, username) VALUES
(1,1,0,'2017-09-10 03:05:00','frankie'),
(1,2,20,'2017-09-12 12:35:00','nikki'),
(5,1,0,'2017-09-15 07:25:00','jerry'),
(3,4,2,'2017-09-15 07:25:00','jerry'),
(1, 1, 0, '2017-05-12 07:24:00', 'banana95'),
(13, 4, 0, '2017-09-15 07:24:00', 'banana95'),
(2, 15, 34, '2017-09-15 08:24:00', 'banana95'),
(2, 17, 15, '2017-09-15 09:34:00', 'banana95'),
(2, 3, 0, '2017-06-15 13:42:00', 'banana95'),
(6, 12, 21,'2017-09-15 07:28:00', 'jeffas');
UNLOCK TABLES;
Solution
task1.sql
#create the Oktomook database
CREATE DATABASE IF NOT EXISTS oktomook;
USE oktomook;
#create the branch table
DROP TABLE IF EXISTS branch;
CREATE TABLE branch (
branchNumber INT NOT NULL AUTO_INCREMENT,
branchName VARCHAR(64),
streetNo INT,
streetName VARCHAR(64),
branchCity VARCHAR(64),
branchState VARCHAR(64),
numberEmployees INT,
PRIMARY KEY (branchNumber)
);
#create the publisher table
DROP TABLE IF EXISTS publisher;
CREATE TABLE publisher (
publisherCode INT NOT NULL AUTO_INCREMENT,
publisherName VARCHAR(64) NOT NULL ,
publisherCity VARCHAR(64),
publisherState ENUM('QLD','VIC','NSW','WA','TAS','NT','SA'),
PRIMARY KEY (publisherCode)
);
#create the author table
DROP TABLE IF EXISTS author;
CREATE TABLE author (
authorId INT NOT NULL AUTO_INCREMENT,
firstName VARCHAR(64),
lastName VARCHAR(64),
PRIMARY KEY (authorId)
);
#create the book table
DROP TABLE IF EXISTS book;
CREATE TABLE book (
ISBN BIGINT ZEROFILL NOT NULL ,
title VARCHAR(255) NOT NULL,
publisherCode INT,
genre ENUM('Non-Fiction','Science Fiction','Fantasy','Crime','Mystery','Young Adult','Romance','General Fiction'),
retailPrice NUMERIC,
paperback VARCHAR(64),
PRIMARY KEY (ISBN),
FOREIGN KEY (publisherCode) REFERENCES publisher(publisherCode)
);
#create the wrote table
DROP TABLE IF EXISTS wrote;
CREATE TABLE wrote (
ISBN BIGINT ZEROFILL,
authorId INT,
sequenceNumber INT,
FOREIGN KEY (ISBN) REFERENCES book(ISBN),
FOREIGN KEY (authorId) REFERENCES author(authorId)
);
#create table inventory
DROP TABLE IF EXISTS inventory;
CREATE TABLE inventory (
ISBN BIGINT ZEROFILL,
branchNumber INT,
quantityInStock INT DEFAULT 0,
FOREIGN KEY (ISBN) REFERENCES book(ISBN),
FOREIGN KEY (branchNumber) REFERENCES branch(branchNumber)
);
tasks2345.sql
-- -- -- -- -- --
-- Task 2
-- -- -- -- -- --
# query1
SELECT
concat(firstName, ' ', lastName) AS name,
dob,
gender,
email
FROM player
WHERE suburb LIKE '%Sunnybank';
# query2
SELECT
player.username,
sum(cost)
FROM player
JOIN purchase
ON player.username = purchase.username
GROUP BY player.username
ORDER BY username DESC;
# query 3
SELECT
player.username,
phoneNumber
FROM player
LEFT JOIN phonenumber
ON player.username = phonenumber.username
JOIN (SELECT min(dob) AS dob
FROM player) pl1 ON pl1.dob = player.dob;
# query4
SELECT
badge.*,
firstName,
lastName,
email
FROM badge
LEFT JOIN purchase ON badge.badgeID = purchase.badgeID
LEFT JOIN player ON purchase.username = player.username
ORDER BY badgeName, firstName, lastName ASC;
# query5
SELECT COUNT(quest.questID)
FROM player
JOIN playerprogress ON player.username = playerprogress.username
JOIN quest ON playerprogress.questID = quest.questID
JOIN treasure ON quest.questID = treasure.questID
WHERE playerprogress.progress = 'complete'
AND treasure.type = 'common';
-- -- -- -- -- --
-- Task 3
-- -- -- -- -- --
INSERT INTO badge (badgeName, badgeDescription) VALUES ('Fools Gold', 'Trickiest trickster in all the seas');
DELETE FROM playerprogress
WHERE progress = 'inactive';
UPDATE player
SET streetNumber = 72,
streetName = 'Evergreen Terrace',
suburb = 'Springfield'
WHERE lastName = 'Smith'
AND streetNumber = 180
AND streetName = 'Zelda Street'
AND suburb = 'Linkburb';
-- -- -- -- -- --
-- Task 4
-- -- -- -- -- --
CREATE INDEX webpage ON treasure (webpage);
CREATE VIEW view AS
SELECT
firstName,
lastName,
creationDateTime
FROM player
JOIN playerprogress ON player.username = playerprogress.username
WHERE progress <> 'complete';
-- -- -- -- -- --
-- Task 5
-- -- -- -- -- --
GRANT INSERT ON treasurehunters.player TO 'nikki'@'localhost';
GRANT DELETE ON treasurehunters.player TO 'nikki'@'localhost';
REVOKE INSERT ON treasurehunters.player FROM 'phil'@'localhost';
REVOKE DELETE ON treasurehunters.player FROM 'phil'@'localhost';