PostgreSQL代写：INFO2120DatabaseApplicationProgramming

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.