CSG1207 Systems and Database Design

Assignment Task 2 – (Implementation and Testing)

Trimester 3, 2018

Assignment:                       Task 1 (Database Analysis and Design), Task 2 (Implementation and Testing)
 
Assignment Marks:         Marked out of 100, (30% of unit)
Task 1 is marked out of 40, (10% of unit)
Task 2 is marked out of 80, (20% of unit)
Due Dates:                          Submit Task 2 Work Allocation Plan Thu 06/12/2018 (Week 7)
Task 2 due 12 noon, Thu 17/01/2019 (Week 11)
Task 2 Demonstration: during class Thu 24/01/2019 (Week 12)
 
Learning Outcomes (Why are you doing this assignment?)                                       
This assessment addresses the following learning outcomes from the Unit Outline:

1.       use Data Analysis to design a Database (Task 1);

3.       implement a Database design using a Database Management system (DBMS), and to construct complex queries upon it (Task 2);

 
Task 2 – Implementation and Testing
Once your database has been designed, it is time to implement it in a DBMS, populate the database, and then manipulate the data via queries.   You must incorporate any feedback on Task 1 you were given by your lecturer by modifying your design document. If the database you implement includes anything you had not included in your database design (Task 1), include an updated database design document, which notes the changes/additions to the design you submitted for Task 1. This will include your physical ERD and TICs.
 
Also, make sure you show table creation order by annotating your physical ERD. Alternatively, you may create a list showing table creation order.
 
Format your scripts for readability, and make sure you use comments if you wish to provide further detail or information about your scripts. All SQL scripts must be written in Courier font and must contain a header in the following example format:
 
/*
Name of script: Query 1 – Customer Orders
Author: your name
Date written: dd/mm/yyyy
Purpose: A query to display the customer number, customer name and total dollar value of all orders for every customer. For those customers without any order, display a zero for total dollar value. Sort the results by descending value of the total dollar value.
*/







Make sure you show all output for every script that you create. If there is no output, write “No output” after the script.
 
Database Creation Script 
Produce a script to create the database you designed in Task 1 (incorporating any changes you have made since then).  Be sure to follow exactly the specifications contained in your Table Instance Charts, and be sure to use a consistent naming scheme.
 
Make sure this script can be run multiple times without resulting in any errors.  Examine the creation scripts of the sample databases available in Moodle for an example of how to do this.
 
Once you have created your database, you should use SSMS to create an ER diagram to verify that your implementation matches your design.  This can be done by right clicking on the “Database Diagrams” folder of the database in the Object Explorer in SSMS.
 
(creating an ER diagram in SQL Server Management Studio)
 
Database Population Script 
Write a script consisting of INSERT statements that populates the tables of your database with sufficient data for testing.  Make sure all referential integrity is observed.  Ensure that you create enough data and that it is varied enough to make all of the following queries return meaningful results.
 
You will possibly find the need to create additional data in order for the following queries to return appropriate results – be sure to update this script to include the data you added if that is the case.
 
Think carefully about the test data you need to create. For example, to display meaningful results for Query 3, you will need to have some books sold where the selling price is between the cost price and the recommended price otherwise you will not have any output.
 
Consider using http://www.generatedata.com/.  You are welcome to use this (or other tools) to generate data for your tables.  If you choose to use a data-generating tool, it is still very important
 
 
 
that you understand and can write INSERT statements yourself – you will not have access to data generating tools in your exam.
 
Views
Book View
Create a view which shows all details of all books in stock.  The view should include the ISBN of the book, the title, the category, the recommended price, the publisher name, and the ISBNs of the prequel and the sequel (if any). Display ‘None’ if a book does not have a prequel and/or a sequel. Show the output of the view by SELECTing all columns and rows from the view.
 
Order View
Create a view which shows all details of all orders.  The view should contain the following:

§  The full name of the customer that placed the order

§  The title of the books ordered

§  The cost price and selling price for each book sold

§  A column showing the difference between the price sold and the cost price.

§  The category of each book

 
The output should be sorted by book category. Show the output of the view by SELECTing all columns and rows from the view.
 
Queries
Query 1 – Customer Orders
Write a query to display the customer number, customer name and total dollar value of all orders for every customer. For those customers without any order, display a zero for total dollar value. Sort the results by descending value of the total dollar value. Use the following format:
 
CustID   Last Name  First Name   Total Order Value ($)
——   ———- ———-   ———————
1000     Foo        Red           30,000
1001     Smith      John          25,000
1002     Orders     No            0

Query 2 – Prequel/Sequel Listing
Write a query to display those books that had a sequel published within 24 months of the original book. Use the following as a template for the output.
 
Book Title          Date Published      Sequel Title        Date Published
—————-    ————–      —————–   ————–
Godfather Part I    20-Mar-1990         Godfather Part II   1-Jan-1992
.
.
 
 

 
 
Query 3 – Reduced Selling Price Report
Write a query that shows all books sold where the selling price is between the cost price and the recommended price. Sort the output, showing the greatest difference between selling price and recommended price first. Use the following as a template for the output.