CMP-4010B Database Systems February 2017 Coursework Assignment Page 1/6 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, 4th May 2017 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 2017 Coursework Assignment Page 2/6 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 4th May 2017 (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 the system for the new domestic Cheap & Chearful Airways (CCA) database system. Your role is to prototype and test some of the functionality required for the booking aspect of 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 Psycopg2 to 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 company 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: LeadCustomer (CustomerID, FirstName, Surname, BillingAddress, email) Passenger(PassengerID, FirstName, Surname, PassportNo, Nationality, DoB) Flight (FlightID, FlightDate, Origin, Destination, MaxCapacity, PricePerSeat) FlightBooking (BookingID, CustomerID, FlightID, NumSeats, Status, BookingTime, TotalCost) SeatBooking(BookingID, PassengerID, SeatNumber) where the Status can be one of the following: - Reserved – the seats have been allocated and the booking has been completed - Cancelled – the booking has been cancelled and seats are no longer allocated A number of assumptions have been made to reduce the volume of programming required, these are: • A flight is a simple one-way flight which also describes the maximum capacity of the aircraft to which it has already been allocated. You do not need to worry about booking passengers a return trip. • A lead customer can book seats for multiple passengers; the seats must be all on the same flight. • The origin and destination are simple text fields and not using any form of lookup table. • Each flight has a price per seat and all seats in a flight are charged at that price. • The total cost for the booking is the sum of all individual seat costs. • The lead customer may or may not be one of the passengers in the booked flight.CMP-4010B Database Systems February 2017 Coursework Assignment Page 3/6 The actions of interest to us in this work are: 1. Insert a new record. This could be a. Given a lead customer ID number name, and contact details, create a new customer record. b. Given a passenger with an ID, name date of birth, etc., create a new passenger record. c. Given a flight ID number, origin, destination, flight date, capacity of the aircraft, and price per seat create a new flight record. 2. Given a customer ID number, remove the record for that customer. It should not be possible to remove customers that have active (i.e. reserved) flight bookings. A customer that has only cancelled bookings could be removed; the associated bookings should also be removed along with all the seat bookings. 3. Check the availability of seats on all flights by showing the flight ID number, flight date along with the number of booked seats, number of available seats and maximum capacity. 4. Given a flight ID number, check the status of all seats currently allocated to that flight, i.e. return the total number of reserved/ cancelled/ available seats. 5. Produce a ranked list of all lead customers, showing their ID, their full name, the total number of bookings made and the total spend made for all bookings. The list should be sorted by decreasing total value. 6. Given a booking ID, customer ID number, flight ID number, number of seats required and passenger details, make a booking for a given flight. This procedure should first show seats available in a given flight and then proceed to insert booking, if there are sufficient seats available. The customer could be an existing customer or a new customer, in which case it should be entered first into the database. Seats numbers can be allocated at the time of booking or later on. The making of a booking with all the steps outlined should work as an atomic operation. 7. Given a booking ID number, cancel the booking. Note that cancelling a booking only changes the status and should not delete the historical details of the original booking. However, cancelled seats should be viewed as available. 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). In particular you should not modify the type of the ID attributes to anything other than integer. 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.) Also include a copy of your own test data used to test your queries. This could be in the form of insert statements or it could be a copy of the tables showing the inserted data. 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.CMP-4010B Database Systems February 2017 Coursework Assignment Page 4/6 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. • Document also with 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 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 using ready for electronic submission by the coursework deadline. Archive formats other than zip are not acceptable. Instruction for electronic submission of code will be accessible via Blackboard. Online submissions to Blackboard are timed by the system. Any submissions that are late will receive the standard penalties. Note that incomplete submissions for any of the parts will result in a reduced mark to account for the lack of supporting evidence for your work. 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. However, demo marks will be penalised and may even go down to zero if the supportive evidence is not submitted online as requested, is incomplete or if it is submitted late. Demonstrating your work (along with submitting the requested evidence) is 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! No changes to the code are allowed after the submission deadline. Important Notes • No additional report is required. • The electronic documents should be well presented to aid checking. • 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 and test data should be produced during the demonstration and submitted electronically.CMP-4010B Database Systems February 2017 Coursework Assignment Page 5/6 • Part 2: Your SQL data manipulation statements for each of the requirements should be available during the demonstration and submitted electronically together with evidence of testing. • Part 3: Your python source code files (.py). • Attend and participate in a demonstration in week 12/13.CMP-4010B Database Systems February 2017 Coursework Assignment Page 6/6 Appendix Initial database design CREATE TABLE LeadCustomer ( CustomerID INTEGER NOT NULL, FirstName VARCHAR(20) NOT NULL, Surname VARCHAR(40) NOT NULL, BillingAddress VARCHAR(200) NOT NULL, email VARCHAR(30) NOT NULL ) CREATE TABLE Passenger ( PassengerID INTEGER NOT NULL, FirstName VARCHAR(20) NOT NULL, Surname VARCHAR(40) NOT NULL, PassportNo VARCHAR(30) NOT NULL, Nationality VARCHAR(30) NOT NULL, Dob DATE NOT NULL ) CREATE TABLE Flight ( FlightID INTEGER NOT NULL, FlightDate TIMESTAMP NOT NULL, Origin VARCHAR(30) NOT NULL, Destination VARCHAR(30) NOT NULL, MaxCapacity INTEGER NOT NULL, PricePerSeat DECIMAL NOT NULL ) CREATE TABLE FlightBooking ( BookingID INTEGER NOT NULL, CustomerID INTEGER NOT NULL, FlightID INTEGER NOT NULL, NumSeats INTEGER NOT NULL, Status CHAR(1) NOT NULL, BookingTime TIMESTAMP NOT NULL, TotalCost DECIMAL ) CREATE TABLE SeatBooking ( BookingID INTEGER NOT NULL, PassengerID INTEGER NOT NULL, SeatNumber CHAR(4) )