Introduction
This request is about the programming of database interaction code within an application, building on the car-sharing database scenario introduced in requests 1 and 2. The objectives are to gain practical experience interacting with a relational database using an Application Programming Interface (API) and transaction programming, and to understand the importance and application of basic security measures. There are also opportunities to use more advanced database
pplication programming techniques, such as stored procedures, triggers, indexes and access control privileges. We also included an optional extension regarding a suitable interface design.
This is a group request for teams of about 3 members, and it is assumed that you will continue in your request 2 group. You should inform your tutor as soon as possible if you wish to change groups.
Please also keep an eye on the discussion forum and further announcements in Piazza.
Design Brief: Programming a Car-Sharing Client Application
In this request your task is to implement the functions required to support the database interactions of an online car-sharing system, hosted on the School’s PostgreSQL server. You will be provided with a reference schema for PostgreSQL, as well as some example data. We will also provide a complete user interface written in Python, for which you need to write the appropriate database interaction functions using the Python DB-API introduced in Week 8. In writing these functions you should consider the following issues, which will be taken into account during marking:
SQL
Your code should make best use of the database to correctly retrieve and update data. In particular, you should avoid writing client-side code for operations, such as joins, that could be better done within the database.
Transaction Handling
You should assume that multiple clients will be running concurrently on the same database, so your functions should make suitable use of transactions. You should consider where to commit or roll back these transactions, and what to do if a transaction fails. D/HD students should also select appropriate isolation levels for their transactions.
Security
Multi-tier architectures increase the scope for nefarious users to gain unintended access to query or modify your database. You should take steps to limit this by preventing SQL Injection attacks, and limiting the privileges available to the client to specific operations on tables, views and stored procedures.
Stored Procedures
Network traffic can be reduced (and cross-client portability increased) by wrapping complex database operations into stored procedures that are run within the database rather than in the client. You should make use of these where appropriate.
Core Functionality
Login
At the login screen, members can log in with their email address (or optionally their nickname) and password. Your interface should verify those values against the data stored in your database. When a valid user/password combination is entered, members shall be directed to the member home screen.
Home Screen
On the home screen, the user should be greeted with their full name, and see the following details:
The member’s membership plan and since when he is a member.
If selected, the name of his home bay.
Number of bookings made by user (from the statistical information stored for each member).
New Booking
A user should be able to use this page to make a booking of a car for a specific period. In making
the booking the application must:
Check availability (basic availability plus no clashes with other bookings).
Create a new booking entry.
Keep the member’s ‘number of booking’ statistics up-to-date.
Estimate the cost of the booking according to the member’s plan.
If successful, details are shown in the Booking Details screen.
Submission Details
Please submit your solution in the ‘request’ section of the unit e-learning site by the deadline, including the following items:
Client Source Code: For most groups this will be a modified version of the database.py, but if more substantial changes have been made you should provide a zip file containing each of the files you have changed, along with a short Changelog.txt file clearly summarising your group’s contributions to each file;
Database Schema DDL: If you have done any extensions that modify the database you should include all such additions (ALTER TABLE statements, views, server-side stored procedures, functions, triggers, indexes or grant statements for PostgreSQL which your created as plain text file with .sql file suffix). You should ensure that this file runs on a clean version of the original schema on the PostgreSQL 9.5 database without errors.