Page

$1$

of $3$

DROP DATABASE IF EXISTS $`$bookstore$`$;

CREATE DATABASE IF NOT EXISTS $`$bookstore$`$;

USE $`$bookstore$`$;

# Table structure for table $`$author$`$

DROP TABLE IF EXISTS $`$author$`$;

CREATE TABLE $`$author$`($

$`$authorID$`$ int$(11)$ NOT NULL,

$`$firstName$`$ varchar$(60)$ DEFAULT NULL,

$`$lastName$`$ varchar$(60)$ DEFAULT NULL,

PRIMARY KEY $(`$authorID$`)$

$)$;

INSERT INTO $`$author$`$ VALUES $(1,$'Sansa','Stark'$),(2,$'Stephen','King'$),$

$(3,$'Prakash','Bhandari'$),(4,$'Laurianne','Sitbon'$),(5,$'Sophie','Monk'$),$

$(6,$'George','Costanza'$),(7,$'Nikki','Peever'$),(8,$'Amanda','Jarvinen'$),$

$(9,$'Jake','Bradford'$),(10,$'Wayne','Wood'$),(11,$'Sam','Roy'$),(12,$'Ned','Stark'$)$;

# Table structure for table $`$publisher$`$

DROP TABLE IF EXISTS $`$publisher$`$;

CREATE TABLE $`$publisher$`($

$`$publisherCode$`$ int$(11)$ NOT NULL,

$`$publisherName$`$ varchar$(60)$ NOT NULL,

$`$publisherCity$`$ varchar$(60)$ DEFAULT NULL,

$`$publisherState$`$ enum$(\text{'}$QLD$\text{'},$'VIC',$\text{'}$NSW$\text{'},\text{'}$WA$\text{'},$'TAS',$\text{'}$NT$\text{'},\text{'}$SA$\text{'})$ NOT NULL,

PRIMARY KEY $(`$publisherCode$`)$

$)$;

INSERT INTO $`$publisher$`$ VALUES $(1,$'Publishing House','Perth',$\text{'}$WA$\text{'}),(2,$'Storm Pty

Ltd$\text{'},$'Melbourne','VIC'$),(3,$'Rooster House','Sydney',$\text{'}$NSW$\text{'}),(4,$'Rainforest

Publishing','Hobart','TAS'$),(5,$'Cavill Press','Gold Coast',$\text{'}$QLD$\text{'}),(6,$'South

Publishing','Adelaide',$\text{'}$SA$\text{'}),(7,$'James Bond','Gold Coast',$\text{'}$QLD$\text{'})$;

# Table structure for table $`$branch$`$

DROP TABLE IF EXISTS $`$branch$`$;

CREATE TABLE $`$branch$`($

$`$branchNumber$`$ int$(11)$ NOT NULL AUTO$\_$INCREMENT,

$`$branchName$`$ varchar$(60)$ DEFAULT NULL,

$`$streetNo$`$ varchar$(15)$ DEFAULT NULL,

$`$streetName$`$ varchar$(30)$ DEFAULT NULL,

$`$branchCity$`$ varchar$(60)$ DEFAULT NULL,

$`$branchState$`$ enum$(\text{'}$QLD$\text{'},$'VIC',$\text{'}$NSW$\text{'},\text{'}$WA$\text{'},$'TAS',$\text{'}$NT$\text{'},\text{'}$SA$\text{'})$ NOT NULL,

$`$numberEmployees$`$ int$(11)$ DEFAULT NULL,

PRIMARY KEY $(`$branchNumber$`)$

$)$;

INSERT INTO $`$branch$`$ VALUES $(1,$'QUT Bookstore',$\text{'}123\text{'},$'Fake

Street','Brisbane',$\text{'}$QLD$\text{'},5),(2,$'Pick$-$a$-$book','Level $40,200\text{'},$'Martin

Place','Sydney',$\text{'}$NSW$\text{'},10),(3,$'Paging all Readers',$\text{'}4300\text{'},$'Brunswick

Street','Melbourne','VIC',$10),(4,$'Books by the Ocean',$\text{'}20\text{'},$'Jane

Street','Hobart','TAS',$3),(5,$'Look Inna Book',$\text{'}600\text{'},$'Lake View','Darwin',$\text{'}$NT$\text{'},3),$

$(6,$'Rad$-$elaide Books',$\text{'}100\text{'},$'North Street','Adelaide',$\text{'}$SA$\text{'},4),(7,$'South Perth

Books','Level $4,500\text{'},$'Wilston Avenue','Perth',$\text{'}$WA$\text{'},7),(8,$'Once Upon A

Bookstore',$\text{'}3$A$/700\text{'},$'The Strand Road','Townsville',$\text{'}$QLD$\text{'},8),(9,$'Bookmark

Joe$\backslash \text{'}$s$\text{'},\text{'}1\text{'},$'Red Road','Ayers Rock',$\text{'}$NT$\text{'},4),(10,$'North Perth books',$\text{'}762\text{'},$'Western

Road','Perth',$\text{'}$WA$\text{'},3)$;

# Table structure for table $`$book$`$

DROP TABLE IF EXISTS $`$book$`$;

CREATE TABLE $`$book$`($

$`$ISBN$`$ char$(13)$ NOT NULL,

$`$title$`$ varchar$(60)$ NOT NULL,

$`$publisherCode$`$ int$(11)$ NOT NULL,

$`$genre$`$ enum$(\text{'}$Non$-$Fiction','Science Fiction','Fantasy','Crime','Mystery','Young

Adult','Romance','General Fiction'$)$ DEFAULT NULL,

$`$retailPrice$`$ decimal$(6,2)$ DEFAULT NULL,

$`$paperback$`$ enum$(\text{'}$True$\text{'},$'False'$)$ NOT NULL,

PRIMARY KEY $(`$ISBN$`),$

KEY $`$publisherCode$`(`$publisherCode$`),$

CONSTRAINT $`$publisherCode$`$ FOREIGN KEY $(`$publisherCode$`)$ REFERENCES $`$publisher$`$

$(`$publisherCode$`)$

$)$;

INSERT INTO $`$book$`$ VALUES $(\text{'}0000000000001\text{'},$'True Crime',$1,$'Crime',$54\mathrm{.}95,$'True'$),$

$(\text{'}0099886674850\text{'},$'The Neverending Story',$1,$'Fantasy',$215\mathrm{.}80,$'False'$),$

$(\text{'}1234567890123\text{'},\text{'}$MySQL $101\text{'},1,$'Non$-$Fiction',$10\mathrm{.}70,$'True'$),$

$(\text{'}1236739872348\text{'},$'Teenage Mutant Ninja Turtles',$5,$'General Fiction',$12\mathrm{.}00,$'False'$),$

$(\text{'}1265763422222\text{'},$'The Bachelorette',$6,$'Romance',$1\mathrm{.}50,$'True'$),(\text{'}2847563928012\text{'},$'Game

of Thrones',$2,$'General Fiction',$70\mathrm{.}95,$'True'$),(\text{'}3467802389345\text{'},$'A Clockwork

Orange',$2,$'General Fiction',$80\mathrm{.}75,$'True'$),$

$(\text{'}4440987653789\text{'},$'Ghostrider',$2,$'Mystery',$30\mathrm{.}95,$'True'$),(\text{'}4455667788990\text{'},$'Steven

Avery $-$ guilty or innocent?$\text{'},3,$'Crime',$50\mathrm{.}00,$'True'$),(\text{'}4568777220982\text{'},$'The

Habibs',$4,$'General Fiction',$13\mathrm{.}95,$'True'$),(\text{'}6373242890234\text{'},$'American Horror

Story',$2,$'Non$-$Fiction',$90\mathrm{.}80,$'False'$),(\text{'}6543780976790\text{'},$'Arrow',$3,$'Young

Adult',$10\mathrm{.}95,$'True'$),(\text{'}9999999999999\text{'},$'Stranger Things $2\text{'},7,$'Science

Fiction',$100\mathrm{.}50,$'False'$)$;

# Table structure for table $`$inventory$`$

DROP TABLE IF EXISTS $`$inventory$`$;

CREATE TABLE $`$inventory$`($

$`$ISBN$`$ char$(13)$ NOT NULL,

$`$branchNumber$`$ int$(11)$ NOT NULL,

$`$quantityInStock$`$ int$(11)$ DEFAULT $\text{'}0\text{'},$

PRIMARY KEY $(`$ISBN$`,`$branchNumber$`),$

CONSTRAINT $`$branchNumber$`$ FOREIGN KEY $(`$branchNumber$`)$ REFERENCES $`$branch$`$

$(`$branchNumber$`),$

CONSTRAINT $`$inventoryISBN$`$ FOREIGN KEY $(`$ISBN$`)$ REFERENCES $`$book$`(`$ISBN$`)$

$)$;

INSERT INTO $`$inventory$`$ VALUES $(\text{'}0099886674850\text{'},1,1),(\text{'}0099886674850\text{'},2,1),$

$(\text{'}0099886674850\text{'},3,4),(\text{'}0099886674850\text{'},4,3),(\text{'}0099886674850\text{'},5,2),$

$(\text{'}009$