PostgreSQL代写:INFO2120 Database Application Programming

Introduction

这个PostgreSQL的Web应用程序算是Database System这门课里面比较坑的一个作业了。Start code提供的Web框架是基于Python编写的,Database要求用PostgreSQL。由于Python没有比较好用的PostgreSQL的lib,很多底层的DB操作都得自己实现。相比PHP+MySQL的传统架构复杂了不少。
由于直接就是第三次作业,还得把前两次的要求和代码重新看一遍,随手修掉的bug都有一大堆。

This assignment is about the programming of database interaction code within an application, building on the car-sharing database scenario introduced in Assignments 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 assignment for teams of about 3 members, and it is assumed that you will continue in your Assignment 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 assignment 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:

  1. Check availability (basic availability plus no clashes with other bookings).
  2. Create a new booking entry.
  3. Keep the member’s ‘number of booking’ statistics up-to-date.
  4. Estimate the cost of the booking according to the member’s plan.

If successful, details are shown in the Booking Details screen.

Booking Details

For a given booking, specified by its booking ID number, this screen should display:

  • Car details (name, registration)
  • The car bay where the car is located
  • Booked period
  • Time and date of when the booking was made

Booking History

This screen should list all the member’s bookings. Each item in the list should include:

  • the car’s name and registration
  • the reserved date
  • the duration of the booking

The bookings should be in reverse chronological order (most recent first) according to the reserved date. A user can choose to see further details of a booking in the Booking Details screen.

CarBay Browser

This screen allows the user to search for car bays. By default , this screen should show the home bay as selected by the member (if a home bay was selected by the member). Search attributes include:

  • car bay name
  • part of the address such as city or suburb name

All matching bays should be shown with the following attributes:

  • carbay name
  • address
  • number of cars parked there
  • optionally: link to a map using the carbay’s URL
  • optionally: whether any car there is available at the current time

CarBay Details

The screen should show details of a carbay and all its cars, with the following attributes:

  • all details about a carbay including name, address, description, gps location, walkscore
  • car names and regos
  • optional: whether each car it is available at the current time (i.e. it is not currently booked)

Note that there could be more than one car at any given car bay.

Car Details

This page should give all the details of a particular car, including:

  • car name
  • car model and year
  • car category, capacity and transmission type

Also, the details should include a list of which hours the car is available for the current day, taking
account of any existing bookings.

Extensions

Proficiency in core skills and application of more advanced skills can be demonstrated through implementation of extensions to the core functionality. Students wishing to attain a D/HD mark for this assignment should implement at least one extension that demonstrates research and application of skills or techniques beyond those covered in the core brief, such as indexes, triggers, views, or recursive/analytical SQL. You should consult your tutor for guidance on what would be an appropriate extension for your group, and what the criteria will be for marking them. Below are a few suggestions for possible extensions. Depending upon the scope you may wish to do one large extension or a couple of smaller ones.

Option 1: Physical Optimisations and Reservation Materialised View

Suggest and create indexes which make your most frequent queries and transactions faster. A common task handled by the database is checking whether a car is available for a given period. To reduce the burden of this operation, an reservation table can be written to record which hours are reserved for any booked cars. Availability checks can then perform a query on this table. Support this by:

  1. Adding this reservation table
  2. Write a query to populate this table for the existing bookings
  3. Write triggers for the Booking table to make corresponding updates to the reservation table.

Option 2: Invoicing

Each month a new invoice is generated by the company for each member to calculate the costs owed and due to the member. This needs to include the plan fees and booking costs for any booked cars. Write a stored procedure to populate the invoice data for a specific member for a given month, and support this with functionality for a member to view a list of all their invoices and the specific details for a particular invoice.

Option 3: Data-User-Interaction Analysis and Design

Analyse the user interface of the given skeleton code with regard to

  • its usability on a mobile device such as a smartphone,
  • finding an available cars close to the current position,
  • an efficient way to extend a currently active booking.
    Which parts of the user interface would you want to change to support these usability criterions? Design an alternative interface that supports those functions better, and explain how the database access part is affected by your changes (such as which kinds of queries would either needed to be changed or added). You submission for this extension should include:
  • a textual discussion of your usability analysis with regard to above’s criteria,
  • a wireframe of your planned revised site layout,
  • a mockup of your new interface design, and
  • a discussion of which parts of the database-related code would be affected by your design.

Submission Details

Please submit your solution in the ‘Assignment’ 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.