SQL code should run successfully on one click even after just copy and paste
Bachelor and Associate Degree of Information Technology Database Systems (BIT231) Semester 1 2018 1 of 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 1 2018 2 of 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 branch is a book store. Its Primary Key is branch_no 5. Wrote: Given that one book can be written by many authors and an author can write many books, 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 branch holds on its shelves. Its Primary Key is a composite key – bookcode 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 (18 + 4 + 2 + 2 + 2 = 28 marks) 1. CREATE a table for every entity represented on the ER Diagram a. Choose data types carefully b. 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 current price of 3.95 to 5.50. Fiction 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 1 2018 3 of 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 branches 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 by 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 current 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" branch. 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 branch 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','Barbara'), ('Williams','Peter'), ('Kafka','Franz'), ('Novalis',''), ('Lovecraft','H.P'), ('Paz','Octavio'), ('Camus','Albert'), ('Castleman','Riva'), ('Zinbardo Philip','Philip'), ('Gimferrer','Pere'), ('Southworth','Rod'), ('Wray','Robert'), ('Lepa','Jerzy'), ('Rowling','JK') book table records: ('0189','Kane and Abel','PB','FIC',5.55,'Y','2001-06-16 00:00:00'), ('0200','Stranger','BB','HOR',8.75,'N','2002-09-18 00:00:00'), ('0378','Dunwich Horror and Others','PB','HOR',19.75,'N','2001-06-05 00:00:00'), ('079X','Smokescreen','PB','MYS',4.55,'Y','2002-08-24 00:00:00'), ('0808','Knockdown','PB','MYS',4.75,'Y','2002-07-07 00:00:00'), ('1351','Cujo','SI','HOR',6.65,'Y','2002-04-30 00:00:00'), ('1382','Marcel Duchamp','PB','ART',11.25,'Y','2004-05-23 00:00:00'), ('138X','Death on the Nile','BB','MYS',3.95,'Y','2004-09-06 00:00:00'), ('2226','Ghost From the Grand banks','BB','SFI',19.95,'N','2003-04-08 00:00:00'), ('2281','Prints of the 20th Century','PB','ART',13.25,'Y','2004-11-06 00:00:00'), ('2766','Prodigal Daughter','PB','FIC',5.45,'Y','2006-05-15 00:00:00'), ('2908','Hymns to the Night','BB','POE',6.75,'Y','2006-06-04 00:00:00'), ('3350','Higher Creativity','PB','PSY',9.75,'Y','2006-08-08 00:00:00'), ('3743','First Among Equals','PB','FIC',3.95,'Y','2007-09-09 00:00:00'), ('3906','Vortex','BB','SUS',5.45,'Y','2007-02-10 00:00:00'), ('5163','Organ','SI','MUS',16.95,'Y','2001-05-12 00:00:00'), ('5790','Database Systems','BF','CS',54.05,'N','2004-09-06 00:00:00'), ('6128','Evil Under the Sun','PB','MYS',4.45,'Y','0200-12-12 00:00:00'), ('6328','Vixen 07','BB','SUS',5.55,'Y','2008-04-04 00:00:00'), ('669X','A Guide to SQL','BF','CS',23.95,'Y','2009-07-09 00:00:00'), ('6908','DOS Essentials','BF','CS',20.50,'Y','2006-05-15 00:00:00'), ('7405','Night Probe','BB','SUS',5.65,'Y','2005-05-15 00:00:00'), ('7443','Carrie','SI','HOR',6.75,'Y','2010-06-14 00:00:00'), ('7559','Risk','PB','MYS',3.95,'y','2012-06-19 00:00:00'), ('7947','Database Programming','BF','CS',39.90,'Y','2012-01-01 00:00:00'), ('8092','Magritte','SI','ART',21.95,'N','2013-02-02 00:00:00'), ('8720','Castle','BB','FIC',12.15,'Y','2013-08-08 00:00:00'), ('9611','Amerika','BB','FIC',10.95,'Y','0000-00-00 00:00:00'), ('0180','Shyness','BB','PSY',7.65,'Y','2001-05-12 00:00:00') branch 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),