CMP-4010B Database Systems February 2016 Coursework Assignment Page 1/5

COURSEWORK ASSIGNMENT UNIVERSITY OF EAST ANGLIA School of Computing Sciences

Module: CMP-4010B ASSIGNMENT TITLE: SQL and Python Programming Exercise DATE SET : Week 7

DATE & TIME OF SUBMISSION : Thursday, 28th April week 12, 3 pm Demonstration Week 12/13 RETURN DATE : Assessment Period Week 4 ASSIGNMENT VALUE : 40 %

SET BY : Dr Beatriz de la Iglesia SIGNED: CHECKED BY : Dr Graeme Richards SIGNED:

Aim: To implement a database application in Python by first completing the database table definitions and writing interactive SQL statements. Learning outcomes:

Experience in the following:

- problem solving techniques using Python, SQL and postgreSQL; - interpreting user requirement and defining solutions; - creating table definitions using SQL;

- manipulating table data using SQL; - SQL programming in postgreSQL; - managing a database from a Python application using Psycopg2

- demonstration and presentation of technical systems; - managing time based on workload, deadlines and distribution of effort.

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;

Good use of Python to implement a prototype database client application; Ability to correctly and accurately interpret project specification;

Correct functionality and output as required by each requirement;

Neatly presented work with correct program output during demonstration. Description of assignment: See Attached. Required:

See Attached Handing in procedure:

You will be expected to demonstrate your work in week 12/13. The SQL and program demonstrated must match the

version handed in! All deliverables will be submitted electronically (instructions on how to submit electronically will be issued in due course). Plagiarism: Plagiarism is the copying or close paraphrasing of published or unpublished work, including the work of another student without the use of quotation marks and due acknowledgement. Plagiarism is regarded a serious offence by the

University and all cases will be reported to the Board of Examiners. Work that contains even small fragments of plagiarised material will be penalised. CMP-4010B Database Systems February 2016 Coursework Assignment Page 2/5 UNIVERSITY OF EAST ANGLIA

School of Computing Sciences

CMP-4010B Database Systems Coursework Assignment – SQL and Python/Flask Programming

Set: Week 7 Hand in: Thursday 28th April (Week 12) by 3 PM (Demonstrations will be scheduled for Week 12/13) Returned by: Assessment period week 4 Value: 40% of unit marks

Introduction Your Company has been given the opportunity to implement a database system for a book wholesaler. The

wholesaler sells direct to shops and periodically sends out sales representatives to take orders. The sales rep collects orders using a portable electronic device. Your role is to prototype and test some of the functionality required for the system. The first stage in this process is 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 before writing the programmatic interface. Once the correct functionality has been achieved, you are required to develop a Python application to execute SQL statements. The Python application should use

Psycopg2to execute the commands developed earlier using the interactive SQL interface. Your prototype Python application will be used to demonstrate the processes and database functionality to the wholesaler

before a full user interface is developed; therefore 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 with a real system. A detailed specification of the task to be undertaken and the deliverables to be produced for assessment are given below. 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* ) OrderLine(ShopOrderID*, BookID*, Quantity, UnitSellingPrice) where CategoryType can be either 'fiction' or 'Non-fiction'.

A number of assumptions have been made for you, these are: • Only the tasks below need to be provided to the user. All other tables and data can be managed

using interactive SQL, e.g. managing shops and sales reps. • 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 provided by the sales rep. • The user interface can be console based or web based but most of the marks will be awarded based

on the ability to complete the tasks and present the results clearly, not on how advanced the user interface is. • You will need to provide validation and integrity checks for the tasks required by the prototype.

CMP-4010B Database Systems February 2016 Coursework Assignment Page 3/5 • The Price in Book is the retail value. The UnitSellingPrice may be a lower price quoted in an order as to make a sale.

The actions of interest to us in this work are: 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, apply a discount to the standard price of all books in that category.

Assignment Part 1. Create a test database (25% of marks) A copy of the create table statements for the database definition is available in a text file CW2Tables.txt in Blackboard. Prepare additional SQL clauses and/or statements to complete the definition of the database

by specifying primary keys, domain constraints, entity and referential integrity constraints, etc. Note that you should NOT modify the name and type of the attributes (i.e. the information you have been given). Save all

your Data Definition Language (DDL) statements in a text file. Load a reasonable volume of test data into the tables for use in your testing. The test data should be

sufficient to test all of the queries with their expected output and should provide a suitable environment in which to test normal operation as well as abnormal conditions. • Document this stage with a copy of your complete DDL statements in SQL (including any table

definitions, views, triggers, comments, etc.) You should bring a printed copy to the demonstration.

Part 2. Test the functionality (50% marks aprox.) For each of the tasks described above, prepare in text files interactive SQL statements. Test these statements using the PGAdmin III interface. You should ensure you test your queries thoroughly, e.g. test entity integrity, referential integrity and other constraints as necessary. The purpose of this stage is to prototype and test SQL statements for each task for use in your final version

of the Python application. However, your .sql files need to ready to be loaded during the demo so if you fail to demonstrate your Python program working you can at least demonstrate your prototype SQL statements through the interactive interface.

• Document this stage with a copy of the SQL statements. Your SQL statements need to be

accessible (through copy/paste) as text files through the demonstration. • Evidence of testing of each SQL statement (e.g. copy of the output of running the query). Part 3. Develop a prototype version of the client (25% marks aprox.) Write a Python/Flask/Psycopg2 application. The application should comprise a home web page with a

number of forms each of which handles one of the tasks above. Each form's action should lead to a Python function to execute the relevant SQL query. If the query requires output this should be presented on another CMP-4010B Database Systems

February 2016 Coursework Assignment Page 4/5 web page which should include a link back to the home page. If an error occurs a suitable error message

should be displayed on the home page. When a task is complete, a simple one line message should be displayed. Python must not be used for any data processing other than submitting SQL statements and receiving results.The objective is to demonstrate the good use of Python and SQL for database access. Whilst your program should be well laid out and easily readable, no extra credit will be given for complex coding and exotic user interfaces are not required! • The deliverable for this part of the assignment will be a copy of an annotated program source listing (your .py file) and the contents of your templates folder. You do not need to produce a printed copy of your Python code for the demonstration. All deliverables from part 1, 2 and 3 should be collated together in a folder which should then be zipped ready for electronic submission by the coursework deadline. Instruction for electronic submission of code will be place on Blackboard. Online submissions to the Blackboard Digital Dropbox are timed by the system. Any submissions that are late will receive the standard penalties.

Week 12/13 Demonstration of Part 3 You must keep your tables, test data, interactive SQL statements and program so that you can give a demonstration to show your Python/Flask application working. Demonstrations will be scheduled for the end of week 12/ beginning of week 13 and all marks will be awarded at the demonstration. Demonstrating

your work is therefore a mandatory part of the assessment procedure and those that do not come for their scheduled demonstration time will receive a mark of zero for the assignment, unless they have secured an extension. The demonstration will involve loading a set of provided test data and carrying out a standard set of tests. The SQL and program demonstrated must match the version submitted electronically! Important Notes • No additional report is required.

• The electronic documents should be neatly formatted to ease reading. • This is an individual piece of coursework NOT a group project. Collusion checks may be carried out on the electronic submissions. • As you will be given a SQL script in week 12 to load test data into your tables, it is vital that you do

not change the table names, field names, field types etc. from what is described below. • The demonstration must take place on a CMP lab machine using a Python desktop application as the client communicating with the CMP PostgreSQL database server. We will not accept other systems, languages, technologies or machines.

Summary of Deliverables • Part 1: A copy of your SQL data definition statements should be produced during the demonstration and submitted electronically. • Part 2: Your SQL data manipulation statements for each of the requirements should be available during the demonstration and submitted electronically. • Part 3: Your python source code files (.py). • Attend and participate in a demonstration in week 12/13. CMP-4010B Database Systems

February 2016 Coursework Assignment Page 5/5 Appendix

Initial database design CREATE TABLE Category (

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

SalesRepID INTEGER, Name VARCHAR(50)

) CREATE TABLE Shop ( ShopID INTEGER, Name VARCHAR(50)

) CREATE TABLE Publisher ( PublisherID INTEGER, Name VARCHAR(50) )

CREATE TABLE Book ( BookID INTEGER, Title VARCHAR(50), Price DECIMAL(10,2), CategoryID INTEGER,

PublisherID INTEGER ) CREATE TABLE ShopOrder

( ShopOrderID INTEGER, OrderDate DATE, ShopID INTEGER,

SalesRepID INTEGER ) CREATE TABLE Orderline

( ShopOrderID INTEGER,

BookID INTEGER, Quantity INTEGER, UnitSellingPrice DECIMAL (10,2) )