CMP-4010B Database Systems

SQL Programming

Learning Outcomes

Experience in the following:

- problem solving techniques using SQL, PostgreSQL; (SQL, Transferable skills)

- interpreting user requirement and defining solutions; (SQL, DBMS understanding)

- creating table definitions using SQL; (SQL, Relational Model)

- creating ER diagrams (SQL, DBMS understanding)

- manipulating table data using SQL; (SQL)

- SQL programming in PostgreSQL; (SQL)

- SQL transactions in PostgreSQL; (SQL, Transactions)

- managing time based on workload, deadlines, and distribution of effort. (Transferable skills)

Specification Overview

Aim:

To implement part of a database application by first completing the database table definitions, then writing interactive SQL statements.

Coursework Description

See Attached at Page 4.

Relationship to Formative Assessment

All SQL Labs build towards this exercise, with each lab helping you to perform. Transactions of Interest that contribute to building this solution. Labs include expected query results so you can ensure you get correct answers and perform a form. of self-assessment.

Summary of Deliverables

Part 1: A copy of your SQL data definition statements and test data should be produced and submitted electronically.

Part 2: Your SQL statements for each of the Transactions of Interest should be produced and submitted electronically together with evidence of testing for each statement on your own data and assessment data. There will be two documents – one with your own data and one with the assessment data using the assessment template.

The Assessment Template file issued nearer the submission time (48 hours prior to submission deadline).

Resources

The necessary materials are in the lecture notes/lab materials. Links to relevant

PosgreSQL help pages are in the lecture slides. Recommended books in reading list are also useful.

Marking scheme

Marking Details	Marks
Part 1 - DDL Data integrity and constraints, Views, functions, triggers, comments, an ER diagram, your own data for testing.	Approx. 30%
Part 2 - DML Data Insertion, Update, and Deletion: Correctness and data integrity. Data Retrieval: Correctness.	Approx. 60%
Overall Presentation - Clarity and professionalism of the template presentation.	Approx. 10%

Assessment criteria

□   Good use of SQL data definition language to complete the table definitions;

□   Good use of SQL data manipulation language to write interactive queries;

□   Ability to interpret project specification correctly and accurately. This may   include a little bit of independent thinking on what may make a table/report look good;

□   Correct functionality and output as required by each requirement;

□   Neatly presented work with correct program output in the assessment

template; Sufficiency and completeness of Submitted code and tests data.

Plagiarism, Collusion, and Contract Cheating

The University takes academic integrity very seriously. You must not commit

plagiarism, collusion, or contract cheating in your submitted work. Our Policy on Plagiarism, Collusion, and Contract Cheating explains:

□   what is meant by the terms ‘plagiarism’, ‘collusion’, and ‘contract cheating’

□   how to avoid plagiarism, collusion, and contract cheating

□   using a proof reader

□   what will happen if we suspect that you have breached the policy.

It is essential that you read this policy and you undertake (or refresh your memory of) our school’s training on this. You can find the policy and related guidance here:

https://my.uea.ac.uk/departments/learning-and-teaching/students/academic- cycle/regulations-and-discipline/plagiarism-awareness

The policy allows us to make some rules specific to this assessment. Note that:

In this assessment, working with others is not permitted. All aspects of your submission, including but not limited to: research, design, development and writing, must be your own work according to your own understanding of topics. Please pay  careful attention to the definitions of contract cheating, plagiarism and collusion in    the policy and ask your module organiser if you are unsure about anything.

CMP-4010B Database Systems SQL Programming Coursework

Introduction

Your start-up company has been given the opportunity to implement a database   system for a RoyalReads book wholesaler. The wholesaler sells directly to shops and periodically sends out sales representatives to take orders. The sales representatives collect orders using a portable electronic device. Your role is to prototype and test some of the functionality required for the system.

Firstly, you need to analyse the requirements and write SQL statements to perform these tasks. These statements can be tested using an interactive SQL interface to ensure correct functionality. You only need to implement the features required for the user interaction described in the tests below.

A description of the tables and required functionality has been provided. Naturally, it  is grossly simplified compared to a real system. A detailed specification of the task to be undertaken and the deliverables to be produced for assessment is given below.

You can use your own facilities for program development, but the final version must be based on PostgreSQL and capable of running in the CMP labs.

System Functionality

The database comprises the following tables:

Category (CategoryID, Name, CategoryType ) SalesRep (SalesRepID, Name )

Shop (ShopID, Name)

Publisher (PublisherID, Name)

Book (BookID, Title, Price, CategoryID, PublisherID )

ShopOrder (ShopOrderID, OrderDate, ShopID, SalesRepID, Status) OrderLine(ShopOrderID, BookID, Quantity, UnitSellingPrice)

Where Name in Category can be anything reasonable value, including ‘Fantasy’,

‘Mystery’, ‘History’, ‘ Travel’, ‘ Technology’; CategoryType in Category can be either    ‘fiction’ or ‘Non-fiction’ and Status in ShopOrder can either be ‘Open or ‘Dispatched’ .

A number of assumptions have been made, these are:

□   Only the tasks below need to be programmed at this stage. All other tables

and data can be managed using interactive SQL, e.g. managing shops and sales reps. However, insert statements will need to be provided for each table in order to fill them with your own test data for the first part of the exercise.

□   It is not necessary to auto-generate ID numbers for the tables. IDs will be supplied when data is provided for testing and in the text files.

□   You will need to conduct sufficient validation and integrity checks for each task to make sure they will work with assessment test data.

□   The Price in Book is the retail value. The UnitSellingPrice may be a lower price, quoted in an order as to make a sale.

□   Note that orders are ‘Open’ by default until they are set to ‘Dispatched’ and while they are open new OrderLines can be inserted, i.e. the orders can be modified by inserting new items.  However, once the order is set to

‘Dispatched’ no additional insertions are allowed.

Transactions of Interest:

1.  Given a category ID, name and type, create a new category.

2.  Given a category ID, remove the record for that category.

3.  Produce a summary report of books available in each category.  The report

should include the number of book titles and the average price in each category as well as an appropriate report header and a summary line with totals (hint: summary line may be produced by a separate query). Format your field values appropriately.

4.  Given a publisher name, produce a report of books ordered by year and

month.  For each year and month the report should show bookid, title, total number of orders for the title, total quantity and total selling value (both order value and retail value).

5.  Given a book ID, produce the order history (i.e. all order lines) for that book. The query should include order date, order title, price, unitselling price, total quantity, order value and shop name.  Include a summary line showing the total number of copies ordered and the total selling value (hint: summary line may be produced by a separate query).

6.  Given start and end dates, produce a report showing the performance of each sales representative over that period.  The report should begin with the rep who generated most orders by value and include total units sold and total order value. It should include all sales reps.

7.  Given a category ID and discount percentage, show the result of applying the discount to the standard price of all books in that category.  This should not    alter the price in the base tables.  The query should show Book’s attributes including price and discounted price.

8.  Given an order ID, add a new order line to an ‘Open’ order.

9.  Given an order ID, set the status of an order to ‘Dispatched’ .

10. Given an order ID, attempt to enter a new line in a ‘dispatched’ order.  This should be prevented by the system.

Tasks:

Part 1: Database definition and data loading (approx. 30% of marks)

For consistency, use the following minimal database definition: A copy of this text can be found in the file Cw_Schema.txt in Blackboard under the Coursework    folder.

Minimal database definition

CREATE TABLE Category (
CategoryID	INTEGER,
Name	VARCHAR(50),
CategoryType	VARCHAR(20)
)

CREATE TABLE SalesRep

(

SalesRepID

Name )

INTEGER,

VARCHAR(50)

CREATE TABLE Shop (

ShopID            INTEGER,

Name              VARCHAR(50)

)

CREATE TABLE Publisher

(

PublisherID

Name )

INTEGER,

VARCHAR(50)

CREATE TABLE Book

(

BookID Title

Price

CategoryID

PublisherID )

INTEGER,

VARCHAR(50),  DECIMAL(10,2), INTEGER,

INTEGER

CREATE TABLE ShopOrder

(
ShopOrderID	INTEGER,
OrderDate	DATE,
ShopID	INTEGER,
SalesRepID	INTEGER,
Status	VARCHAR(20)
)

CREATE TABLE Orderline (
ShopOrderID	INTEGER,
BookID	INTEGER,
Quantity	INTEGER,
UnitSellingPrice	DECIMAL (10,2)
)

Add additional SQL clauses and/or statements to complete the definition of the database by specifying DDL statements including data integrity and constraints (e.g., primary keys, domain constraints, entity and referential integrity constraints), views,   functions, triggers, comments. Note that you should NOT modify the name and type of the attributes in the minimal database definition. Save all your Data Definition Language (DDL) statements in a text file. Analyse the structure of the RoyalReads database and present an Entity-Relationship (ER) diagram of the   database.

At this stage the tables are empty. Load (e.g., using insert statements) a reasonable volume of data into the tables for testing the Transactions of Interest. The data should be reasonable to test the Transactions of Interest with their expected output and should provide a suitable environment in which to test normal operation as well as abnormal conditions (e.g., if you constraint an attribute by defining a primary key on the attribute, adding multiple rows with same value for that attribute would consider as an abnormal condition).

Part 2. Prototyping using Interactive SQL version of the transactions (approx. 60% of marks)

Prepare and test interactive SQL statements for the Transactions of Interest. Test these statements using the SQL Query Tool editor in pgAdmin.  The purpose is to test your SQL statements before real world deployment in the production (i.e.,

Prototyping Phase). You may need more than one execution of some of the

Transactions of Interest to verify the correctness of your work (e.g., test primary

keys, referential integrity, correct and incorrect execution). Please be prepared to   add the SQL statements for an assessment template which will be released to you 48 hours prior to the submission deadline with the assessment test data. The

template will look like the exemplar template in the Appendix (see section Template for Submission with a Mock Question and Answer). The assessment test data will  consist of insert statements for the seven database tables.

Quality of Submission and Showcase Your Technical Skills (approx. 10% of marks)

Clarity and professionalism of the template presentation.

Deliverables

□   Electronic submission to Blackboard (All files and subfolders to be collected in a folder named with your student number, zipped and submitted following

instructions in the Blackboard):

o Part 1: A text file containing SQL data definition statements, together with your own data, and the ER diagram.

o Part 2: Your SQL statements for each of the Transactions of Interest should be produced and submitted electronically together with

evidence of testing for each statement on your own data and

assessment data. There will be two documents – one with your own data and one with the assessment data using the Assessment

Template. The Assessment Template along with the assessment data will be issued 48 hours prior to submission deadline.

A sample submission folder is available on the Blackboard.

Note: If the submission folder does not include the mandatory, completed Assessment Template document, a score of zero will be assigned for the   coursework. Your marks will be determined by the accuracy of the SQL

statements in accordance with the assessment data and reports provided within the Assessment Templates.

Important notes

□   The document you submit should be complete and neatly formatted to ease reading.

□   This is an individual piece of coursework NOT a group project. Collusion/plagiarism checks may be carried out.

□   As you will be given the assessment data based on the tables in the minimal database definition, it is vital that you do not change the table names, field    names, field types.