Bachelor and Associate Degree of Information Technology Database Systems (BIT231) Semester...

Question

Bachelor and Associate Degree of
Information Technology
Database Systems (BIT231) Semester XXXXXXXXXXof 3
BIT231 Databases Systems
Assignment #2 –Semester 1, 2018
Assessment Information
This assessment contributes 25% to your mark for this subject and it is initially graded out of 100.
See this assessment's Moodle dropbox for the submission date.
All late submissions receive a 10% per-day late penalty.
It's important to back-up your work regularly. Statements about lost files, even when caused by
technology failure or theft, will not be accepted as a valid reason for late submission of work.
Submission Instructions:
Upload the following files to Moodle:
• A text file containing all your SQL code. It should be successfully run all your code in one hit
(via a single copy and paste) within phpMyAdmin's SQL window
• A Melbourne Polytechnic Assignment cover sheet
Bookstore Database Scenario
The BooksGalore database gathers and organises information about its stores and the books it
stocks. Here is the ER Diagram of the BooksGalore database:
Bachelor and Associate Degree of
Information Technology
Database Systems (BIT231) Semester XXXXXXXXXXof 3
More information on each table:
1. Publisher: A list of companies that publish books. The Primary Key is publisher_code
2. Book: Each record in this table stores a book release, NOT a list of individual copies (e.g. it
would list First Among Equals once, not one record for each of its 12 copies of First Among
Equals). Its Primary Key is bookcode and its Foreign Key is publisher_code
3. Author: A list of authors. Its Primary Key is author_no and it is an AUTO_INCREMENT field
4. Branch: A
anch is a book store. Its Primary Key is
anch_no
5. Wrote: Given that one book can be written by many authors and an author can write many
ooks, this table servers as the association between the book and author tables. The
Sequence attribute indicates the order in which the authors of a particular book are listed on
the cover. Its Primary Key is a composite key – on bookcode and author_no – and these 2
fields also serve as Foreign Keys
6. Invent (Inventory): A customer can find a book title at many store locations and a store will
have many books on its shelves, and so this table makes it possible to show how many
copies of a book each
anch holds on its shelves. Its Primary Key is a composite key –
ookcode and author_no – and these 2 fields also serve as Foreign Keys
Your Tasks
Instructions:
• Write MySQL code to solve the following tasks, using the above scenario to inform you.
• Write the task number and your name in a MySQL comment before each statement you
write (where "statement" means SQL code that is completed by a semi-colon).
• Keep all code in one file so that all your code can be copied, pasted, and run successfully in
one hit in phpMyAdmin's "SQL" window. You may wish to write your code in Notepad++.
• If you believe a task or scenario is ambiguous, include any assumptions you make within
your SQL comments
Part A XXXXXXXXXX2 = 28 marks)
1. CREATE a table for every entity represented on the ER Diagram
a. Choose data types carefully
. Use NOT NULL appropriately
c. Use the DEFAULT clause on at least one appropriate column
d. Set the referential integrity constraints ON UPDATE and ON DELETE
2. Create INSERT statements to populate the publisher, book, author, & wrote tables with:
e. One publisher - Allison and Busby. Find their details on the Web.
f. Two Authors. One of which should be you (that is, use your own name and details
for one of the Authors) and the other should be an Author named Mark Twain.
g. One book called "Winning Ways", written by you and published by Allison and Busby
3. Locate the "BooksGalore records.sql" file on Moodle and insert all the records in that file
into the appropriate tables
4. Use an update query to change the price of any fiction book that has a cu
ent price of 3.95
to XXXXXXXXXXFiction books are marked with the category FIC.
5. Delete all publishers that have the publisher code BB.
Bachelor and Associate Degree of
Information Technology
Database Systems (BIT231) Semester XXXXXXXXXXof 3
Part B (12 Marks)
6. Retrieve all the data in the Author table
7. Retrieve the price of the Book "Stranger"
8. List in alphabetical order the names of all books that have a price greater than $5.50
9. List the names of publishers not located in New York city (That is: New York, NY).
10. List all the different book types. Avoid duplicates
11. List the book code and title of every book that has the type FIC, MYS or ART.
Part C (36 Marks)
12. List the names and addresses of
anches that have between 10 and 15 employees
13. Find all publishers with a "." (that is, a dot) in their name
14. Find all books that have the word "the" in their title
15. List the authors who published "Higher Creativity"
16. List the book titles that were published in the state of "NY"
17. List the books written by each author. Show only the author last name and the book title
18. List the books written by Agatha Christie
19. Determine which book has the most copies on hand and which book has the least. Show the
name of each book and how many copies there are.
20. List the book code and title of every book that has the type "FIC" or that has been published
y a publisher with a code of BB".
21. Calculate the average price for each type of book and display the type and average.
22. For every book published by "Addison Wesley", list the book title and book price.
23. Members get a 10% discount off regular book prices. Determine the discounted price of
every book and display the book title, original price, and discounted price. Tip: You can
calculate 90% of the cu
ent price or subtract 10% from the original price.
Part D (24 Marks)
24. How many books has each author written? Show the authors full name in a single column,
with their last name in capital letters, then a comma, then their first name, and name the
column appropriately.
25. List the book titles of all books that are held at the "BooksGalore Downtown"
anch. Use
inner queries only i.e. no table joins
26. What is the title of the book with the second lowest price? List the book title and price
27. Which Book has the second highest availability (units on hands) in the table?
28. Which Book is not available at "BooksGalore Brentwood"?
29. List the publishers who have published 10 or more books.
30. Identify the authors who are wrote more than 3 books and show where their books are
stored. Show
anch name and author name.
31. For all book types contains more than 5 published books, display the earliest publish dates
for each book type

author table records:
('Archer','Jeffrey'),
('Christie','Agatha'),
('Clark','Arthur C'),
('Francis','Dick'),
('Cussler','Clive'),
('King','Stephen'),
('Pratt','Philip'),
('Adamski','Joseph'),
('Harmon','Willis'),
('Rheingold','Howard'),
('Owen','Ba
ara'),
('Williams','Peter'),
('Kafka','Franz'),
('Novalis',''),
('Lovecraft','H.P'),
('Paz','Octavio'),
('Camus','Albert'),
('Castleman','Riva'),
('Zinbardo Philip','Philip'),
('Gimfe
er','Pere'),
('Southworth','Rod'),
('Wray','Robert'),
('Lepa','Jerzy'),
('Rowling','JK')
ook table records:
('0189','Kane and Abel','PB','FIC',5.55,'Y',' XXXXXXXXXX:00:00'),
('0200','Stranger','BB','HOR',8.75,'N',' XXXXXXXXXX:00:00'),
('0378','Dunwich Ho
or and Others','PB','HOR',19.75,'N',' XXXXXXXXXX:00:00'),
('079X','Smokescreen','PB','MYS',4.55,'Y',' XXXXXXXXXX:00:00'),
('0808','Knockdown','PB','MYS',4.75,'Y',' XXXXXXXXXX:00:00'),
('1351','Cujo','SI','HOR',6.65,'Y',' XXXXXXXXXX:00:00'),
('1382','Marcel Duchamp','PB','ART',11.25,'Y',' XXXXXXXXXX:00:00'),
('138X','Death on the Nile','BB','MYS',3.95,'Y',' XXXXXXXXXX:00:00'),
('2226','Ghost From the Grand banks','BB','SFI',19.95,'N',' XXXXXXXXXX:00:00'),
('2281','Prints of the 20th Century','PB','ART',13.25,'Y',' XXXXXXXXXX:00:00'),
('2766','Prodigal Daughter','PB','FIC',5.45,'Y',' XXXXXXXXXX:00:00'),
('2908','Hymns to the Night','BB','POE',6.75,'Y',' XXXXXXXXXX:00:00'),
('3350','Higher Creativity','PB','PSY',9.75,'Y',' XXXXXXXXXX:00:00'),
('3743','First Among Equals','PB','FIC',3.95,'Y',' XXXXXXXXXX:00:00'),
('3906','Vortex','BB','SUS',5.45,'Y',' XXXXXXXXXX:00:00'),
('5163','Organ','SI','MUS',16.95,'Y',' XXXXXXXXXX:00:00'),
('5790','Database Systems','BF','CS',54.05,'N',' XXXXXXXXXX:00:00'),
('6128','Evil Under the Sun','PB','MYS',4.45,'Y',' XXXXXXXXXX:00:00'),
('6328','Vixen 07','BB','SUS',5.55,'Y',' XXXXXXXXXX:00:00'),
('669X','A Guide to SQL','BF','CS',23.95,'Y',' XXXXXXXXXX:00:00'),
('6908','DOS Essentials','BF','CS',20.50,'Y',' XXXXXXXXXX:00:00'),
('7405','Night Probe','BB','SUS',5.65,'Y',' XXXXXXXXXX:00:00'),
('7443','Ca
ie','SI','HOR',6.75,'Y',' XXXXXXXXXX:00:00'),
('7559','Risk','PB','MYS',3.95,'y',' XXXXXXXXXX:00:00'),
('7947','Database Programming','BF','CS',39.90,'Y',' XXXXXXXXXX:00:00'),
('8092','Magritte','SI','ART',21.95,'N',' XXXXXXXXXX:00:00'),
('8720','Castle','BB','FIC',12.15,'Y',' XXXXXXXXXX:00:00'),
('9611','Amerika','BB','FIC',10.95,'Y',' XXXXXXXXXX:00:00'),
('0180','Shyness','BB','PSY',7.65,'Y',' XXXXXXXXXX:00:00')
anch table records:
(1,'BooksGalore Downtown','16 Riverview',10),
(2,'BooksGalore on the Hill','Brentwood Mall',6),
(3,'BooksGalore Brentwood','Brentwood Mall',15),
(4,'BooksGalore Eastshore','Eastshore Mall',9),
invent table records:
('0189',2,2),
('0200',1,1),
('0200',2,3),
('079X',2,1),
('079X',3,2),
('079X',4,3),
('1351',2,4),
('1351',3,2),
('138X',2,3),
('2226',1,3),
('2226',3,2),
('2226',4,1),
('2281',4,3),
('2766',3,2),
('2908',1,3),
('2908',4,1),
('3350',1,2),
('3906',2,1),
('3906',3,2),
('5163',1,1),
('5790',4,2),
('6128',2,4),
('6128',3,3),
('6328',2,2),
('669X',1,1),

tfthc636619174117612310307921-1y0cvdkt.pdf tfthc636621633248779141307921-xw0v4bib.txt

Ujjawal · Accepted Answer

PART A
------
--1. CREATE a table for every entity represented on the ER Diagram
CREATE SCHEMA `BooksGalore` ;
CREATE TABLE `BooksGalore`.`publisher` (
  `publisher_code` CHAR(2) NOT NULL,
  `publisher_name` VARCHAR(30) NULL,
  `publisher_city` VARCHAR(30) NULL,
  `publisher_state` VARCHAR(3) NULL,
  PRIMARY KEY (`publisher_code`));
  
CREATE TABLE `BooksGalore`.`book` (
  `bookcode` CHAR(4) NOT NULL,
  `book_title` VARCHAR(100) NULL,
  `publisher_code` CHAR(2) NULL,
  `book_type` CHAR(3) NULL,
  `book_price` DECIMAL(5,2) NULL,
  `paperback` CHAR(1) NULL,
  `publish_date` DATETIME NULL,
  PRIMARY KEY (`bookcode`),
  INDEX `publisher_fk_idx` (`publisher_code` ASC),
  CONSTRAINT `publisher_fk`
    FOREIGN KEY (`publisher_code`)
    REFERENCES `BooksGalore`.`publisher` (`publisher_code`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
CREATE TABLE `BooksGalore`.`author` (
  `author_no` INT NOT NULL AUTO_INCREMENT ,
  `last` VARCHAR(60) NULL,
  `first` VARCHAR(60) NULL,
  PRIMARY KEY (`author_no`));
CREATE TABLE `BooksGalore`.`wrote` (
  `Bookcode` CHAR(4) NOT NULL,
  `Author_number` INT NOT NULL,
  `Sequence_number` INT(11) NULL,
  PRIMARY KEY (`Bookcode`, `Author_number`),
  INDEX `author_fk_idx` (`Author_number` ASC),
  CONSTRAINT `book_fk`
    FOREIGN KEY (`Bookcode`)
    REFERENCES `BooksGalore`.`book` (`bookcode`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `author_fk`
    FOREIGN KEY (`Author_number`)
    REFERENCES `BooksGalore`.`author` (`author_no`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
CREATE TABLE `BooksGalore`.`branch` (
  `branch_no` INT(11) NOT NULL,
  `branch_name` VARCHAR(50) NULL,
  `branch_location` VARCHAR(50) NULL,
  `no_employees` INT(11) NULL,
  PRIMARY KEY (`branch_no`));
CREATE TABLE `BooksGalore`.`invent` (
  `bookcode` CHAR(4) NOT NULL,
  `branch_no` INT(11) NOT NULL,
  `units_on_hand` INT(11) NULL,
  PRIMARY KEY (`bookcode`, `branch_no`),
  INDEX `branch_fk_idx` (`branch_no` ASC),
  CONSTRAINT `branch_fk`
    FOREIGN KEY (`branch_no`)
    REFERENCES `BooksGalore`.`branch` (`branch_no`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `bookcode_fk`
    FOREIGN KEY (`bookcode`)
    REFERENCES `BooksGalore`.`book` (`bookcode`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
-- 2. Create INSERT statements to populate the publisher, book, author, & wrote tables with
	
-- e. One publisher - Allison and Busby
INSERT INTO `BooksGalore`.`publisher` VALUES('AB', 'Allison and Busby', 'London', 'Lon');
--f. Two Authors. One of which should be you (that is, 
--use your own name and details for one of the Authors) and the other should be an Author named Mark Twain. 
INSERT INTO `BooksGalore`.`author` (`last`, `first`) VALUES ('Twain', 'Mark');
INSERT INTO `BooksGalore`.`author` (`last`, `first`) VALUES ('Usman', 'Muhammad');
-- g.One book called "Winning Ways", written by you and published by Allison and Busb
INSERT INTO `BooksGalore`.`book` VALUES('0188','Winning Ways','AB','PSY',9.55,'Y','2001-06-16 00:00:00');
-- 3. Locate the "BooksGalore records.sql" file on Moodle and insert all the records in that file into the appropriate tables
INSERT INTO `BooksGalore`.`author` (`last`, `first`) VALUES('Archer','Jeffrey'),
INSERT INTO `BooksGalore`.`author` (`last`, `first`) VALUES('Christie','Agatha');
INSERT INTO `BooksGalore`.`author` (`last`, `first`) VALUES('Clark','Arthur C');
INSERT INTO `BooksGalore`.`author` (`last`, `first`) VALUES('Francis','Dick');
INSERT INTO `BooksGalore`.`author` (`last`, `first`) VALUES('Cussler','Clive');
INSERT INTO `BooksGalore`.`author` (`last`, `first`) VALUES('King','Stephen');
INSERT INTO `BooksGalore`.`author` (`last`, `first`) VALUES('Pratt','Philip');
INSERT INTO `BooksGalore`.`author` (`last`, `first`) VALUES('Adamski','Joseph');
INSERT INTO `BooksGalore`.`author` (`last`, `first`) VALUES('Harmon','Willis');
INSERT INTO `BooksGalore`.`author` (`last`, `first`) VALUES('Rheingold','Howard');
INSERT INTO `BooksGalore`.`author` (`last`, `first`) VALUES('Owen','Barbara');
INSERT INTO `BooksGalore`.`author` (`last`, `first`) VALUES('Williams','Peter');
INSERT INTO `BooksGalore`.`author` (`last`, `first`) VALUES('Kafka','Franz');
INSERT INTO `BooksGalore`.`author` (`last`, `first`) VALUES('Novalis','');
INSERT INTO `BooksGalore`.`author` (`last`, `first`) VALUES('Lovecraft','H.P');
INSERT INTO `BooksGalore`.`author` (`last`, `first`) VALUES('Paz','Octavio');
INSERT INTO `BooksGalore`.`author` (`last`, `first`) VALUES('Camus','Albert');
INSERT INTO `BooksGalore`.`author` (`last`, `first`) VALUES('Castleman','Riva');
INSERT INTO `BooksGalore`.`author` (`last`, `first`) VALUES('Zinbardo Philip','Philip');
INSERT INTO `BooksGalore`.`author` (`last`, `first`) VALUES('Gimferrer','Pere');
INSERT INTO `BooksGalore`.`author` (`last`, `first`) VALUES('Southworth','Rod');
INSERT INTO `BooksGalore`.`author` (`last`, `first`) VALUES('Wray','Robert');
INSERT INTO `BooksGalore`.`author` (`last`, `first`) VALUES('Lepa','Jerzy');
INSERT INTO `BooksGalore`.`author` (`last`, `first`) VALUES('Rowling','JK');
INSERT INTO `BooksGalore`.`publisher` VALUES('AC','Academic Press','London','UK');
INSERT INTO `BooksGalore`.`publisher` VALUES('AP','Arcade Publishing','New York','NY');
INSERT INTO `BooksGalore`.`publisher` VALUES('AW','Addison Wesley','Reading','MA');
INSERT INTO `BooksGalore`.`publisher` VALUES('BB','Bantam Books','New York','NY');
INSERT INTO `BooksGalore`.`publisher` VALUES('BF','Best and Furrow','Boston','MA');
INSERT INTO `BooksGalore`.`publisher` VALUES('JT','Jeremy P.Tarcher','Los Angeles','CA');
INSERT INTO `BooksGalore`.`publisher` VALUES('MP','McPherson and CO.','Kingston','NY');
INSERT INTO `BooksGalore`.`publisher` VALUES('PB','Pocket Books','New York','NY');
INSERT INTO `BooksGalore`.`publisher` VALUES('RH','Random House','New York','NY');
INSERT INTO `BooksGalore`.`publisher` VALUES('RZ','Rizzoli',

Bachelor and Associate Degree of Information Technology Database Systems (BIT231) Semester XXXXXXXXXXof 3 BIT231 Databases Systems Assignment #2 –Semester 1, 2018 Assessment Information This...

Solution

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment