BIT231: Database Systems
Assessment 2 - Software Application
Assessment Weightage: 30%
Total Assessment Tasks: 2 Tasks
Total Marks: 80
Due Date: Week 10 and Week 12
Assignment Instructions:
1. Use last 2 digits of your student id as part of table and attributes name. e.g. TableNamexx where xx are the digits of your user id.
2. List all the assumptions made
3. The word document should contain
a. Question No
b. SQL Code
c. Screen shot of execution of SQL code on SAS Studio
4. Save all the SAS PROC SQL code in one txt/sql file and submit it with the submission.
5. TXT/SQL file - Your SAS PROC SQL code must run without errors apart from those errors resulting from table and drop sequence commands for the tables and sequences that have not as yet been created. 5 marks will be deducted for each error.
Submission Instructions:
1. Submit on Moodle:
? Submit 2 files on Moodle
? A Word document, A SQL/TXT file contains SAS PROC SQL code that runs on SAS Studio and a cover sheet with your student Id and Name
Bookstore Database
The MPBookstore database gathers and organizes information about branches, publishers, authors, and books. There are six tables in the database.
Publisher: This table stores the publisher of the book, and the publisher's city and state of origin. (publisher_code is the Primary Key)
Book: This table contains Information about books, including the title for the book, the publisher of the book, the publishing date, the price. (book_code is the Primary Key and publisher_code is the Foreign Key)
Author: This table accommodates the information about authors. The table only contains the author's first name and last name, but other information, such as the author's age and nationality can be added. (author_no is the Primary Key)
Branch: This table stores the information about the each branch of the bookstore and their details about what is the branch name and where it is located. (branch_no is the Primary Key)
Wrote: This table contains the association between books and authors as one book can be written by more than one author. It is used to relate books and authors. The Sequence field indicates the order in which the authors of a particular book are listed on the cover
Author_no in Author and Author_number in Wrote , both store author_no values
(book_code,Author_number is the Composite Primary Key. book_code and Author_number are the Foriegn Keys)
Invent(inventory) : This table contains the association between books and branches. It is used to indicate the number of copies of a particular book that are currently on hand at a particular branch. Which book is available at which branch and how many copies of the book are at each branch of the bookstore.
Here is the ER Diagram of Bookstore database
Assessment Tasks
Question 1 (20 Marks):
Consider the ER Diagram given above; build the application using SAS PROC SQL
PART A (12 Marks – Due date week 10)
Import all data from import document (Download it from Moodle) into SAS Studio to run the queries in Question 2. Due in Week 10
PART B (4 + 4 = 8 Marks)
Write SQL statements to for following
a) Use an update query to change the price of any book in the Fiction (FIC) with a current price of 3.95 to 5.50.
b) Use a delete query to delete all books in the SFI type table that have the publisher code BB.
Question 2 (60 Marks):
Manipulate the data with the database you have created in Question 1 and perform the following queries:
PART A (6 Marks)
1. Retrieve all the data in the Author table
2. Retrieve the book price for the Book “Stranger”
3. List all the books in alphabetical order (ASC) and price greater than $5.50
4. List the name of every publisher not located in New York.
5. List all the different book types. Avoid duplicates
6. List the book code and title of every book that has the type FIC, MYS or ART.
PART B (24 marks)
1. List all the branches with No of employees in range of 10 and 15
2. Find all publishers with “’t” in their name
3. Find all books with “Databases” in their title
4. List the authors who published “Higher Creativity”
5. List the books of the publishers who located at “NY”
6. List all books with their authors (full name)
7. What is the max and min “units on hand” out of all books
8. List the publications of author “Christie Agatha”
9. List the book code and title of every book that has the type “FIC” or that has the publisher code
“BB”.
10. Calculate the average price for each type of book.
11. For every book published by “Addison Wesley”, list the book title and book price.
12. Members get a 10% discount off regular book prices. To determine the discounted prices, calculate the dis-counted price of every book. (It can be calculate 90% of the current price, or calculate the difference of the current price and 10% of current price.)
PART C (18+12 marks)
1. How many books do each author wrote?
2. List the book titles of all books that are held at the “Henrys Downtown” branch. Use nested queries only ie. no table joins
3. What is the title of the book with the second lowest price? List the book title and price
4. Which Book has the second highest availability (units on hands) in the table?
5. Which Book is not available at “Henrys Brentwood”
6. List the publishers (and the number of books they published) who have published 10 or more books.
7. Identify the authors who are wrote more than 3 books and which locations their books are available? (6 marks)
8. For all book types contains more than 5 published books, display the earliest publish dates for each book type. (6 marks)
The set textbooks for reference are:
1. Rob, P, Coronel, C & Morris, S 2016, Database systems: design, implementation and management, 12th edn, Course Technology, ISBN-13: 9781305627482.
2. Lafler, Kirk Paul 2013, PROC SQL: Beyond the Basics Using SAS®, 2nd edition, Cary, NC: SAS Institute Inc.

Plagiarism
All used sources must be properly acknowledged with references and citations, if you did not create it. Quotations and paraphrasing are allowed but the sources must be acknowledged. Failure to do so is regarded as plagiarism and the minimum penalty for plagiarism is failure for the assignment. The act of given your assignment to another student is classified as a plagiarism offence. Copying large chucks and supplying a reference will result in zero marks as you have not contributed to the report. Copying from Youtube or other videos is also plagiarism (including transcripts). Citation in a video can be included as credits at the end.
Late Submission
Late submission of assignments will be penalised as follows:
• For assignments 1 to 10 days late, a penalty of 5% (of total available marks) per day.
• For assignments more than 10 days late, a penalty of 100% will apply. Which means, the assignment will not be marked and attract a fail grade.
Your submission must be compatible with the software (PDF/Word/Video/Zip) in Melbourne Polytechnic, Computer Laboratories/Classrooms.
Extensions:
Under normal circumstances, extensions will not be granted. In case of extenuating circumstances—such as illness—a Special Consideration form, accompanied by supporting documentation, must be received before 3 working days from the due date. If granted, an extension will be only granted only by the time period stated on the documentation; that is, if the illness medical certificate was for one day, an extension will be granted for one day only. Accordingly, the student must submit within that time limit.
Penalties may apply for late submission without an approved extension.
Penalties:
Academic misconduct such as cheating and plagiarism incur penalties ranging from a zero result to program exclusion.
Marking criteria:
Marks are allocated as indicated on each question. Please read the marking scheme carefully for guidance as to what is required of you
Assessment Tasks (80 marks) Marks
Questin1 (20 marks)
PART A
Import Data to SAS Studio 12
PART B
Update query 4
Delete query 4
Question2 (60 marks)
PART A – Select statement (6 marks)
Retrieve all the data in the Author table 1
Retrieve the book price for the Book “Stranger” 1
List all the books in alphabetical order (ASC) and price greater than $5.50 1
List the name of every publisher not located in New York. 1
List all the different book types. Avoid duplicates 1
List the book code and title of every book that has the type FIC, MYS or ART. 1
PART B – Select statement (24 marks)
List all the branches with No of employees in range of 10 and 15 2
Find all publishers with “’t” in their name 2
Find all books with “Databases” in their title 2
List the authors who published “Higher Creativity” 2
List the books of the publishers who located at “NY” 2
List all books with their authors (full name) 2
What is the max and min “units on hand” out of all books 2
List the publications of author “Christie Agatha” 2
List the book code and title of every book that has the type “FIC” or that has the publisher code “BB”. 2
Calculate the average price for each type of book. 2
For every book published by “Addison Wesley”, list the book title and book price. 2
Members get a 10% discount off regular book prices. To determine the discounted prices, calculate the dis-counted price of every book. 2
PART C – Select statement (30 marks)
9. How many books do each author wrote? 3
10. List the book titles of all books that are held at the “Henrys Downtown” branch. Use nested queries only
ie. no table joins 3
11. What is the title of the book with the second lowest price? List the book title and price 3
12. Which Book has the second highest availability (units on hands) in the table? 3
13. Which Book is not available at “Henrys Brentwood” 3
14. List the publishers (and the number of books they published) who have published 10 or more books. 3
15. Identify the authors who are wrote more than 3 books and which locations their books are available? 6
16. For all book types contains more than 5 published books, display the earliest publish dates for each book type. 6