Coursework #2: Design and implement a relational database and attach a (web based) interface to it

COURSEWORK #2

Design and implement a relational database and attach a (web-based) interface to it

Statement

Design and implement a relational database for a real life application that requires the use of a database. Build an interface to the database suitable for a part of the considered application.

Objective

To perform (most parts of) the whole process of database systems development; in particular to carry out the following (development) phases:

  1. user requirements specification;
  2. conceptual design;
  3. logical design;
  4. physical design and implementation;
  5. implementation of a web-based interface.
Size

Even though you are not developing an industrial database system, you will have to carry out most of the relevant tasks related to database systems development.

Method of work

This coursework may be approached as a group or an individual project. The decision will be taken in the lecture in which the coursework is announced. The decision should be unanimously complied with, unless there are good reasons not to do so.

Deliverables

 Technical Report (based on the task you should carry out); one report is required per student (even if you are working in a group);

 Group Management Report (only required for if a decision is taken to carry out this as a group work); for example, you can assign roles to the members of a group: user (deals mainly with requirements specification and testing); designer (deals with conceptual and logical design issues); programmer (deals with physical design and implementation)
The Group Management Report must contain an account of the contribution of each student in the group to the coursework (however, this can be confidential, if you so wish).

Tasks

 Requirements specification

 data requirements

 transaction requirements

 Conceptual design

 draw EER diagram(s) in UML (it includes hierarchies and aggregations);

 explain entities, if necessary;

 explain attributes (for each entity), if necessary; specify attribute domains;

 explain relationships, if necessary (you may need to specify further semantic assumptions (i.e., apart from the ones stated in the requirements specification) when you explain structural constraints);

 specify enterprise constraints (in English), if any;

 review and validate model (e.g. dry-run transactions with user; check for traps);

 Logical design

 transform ER model into relational model

 specify ‘relational constraints’:

 candidate keys; primary key;

 foreign keys;

 normalise relational model

 for each new relations do not forget to specify the relevant ‘relational constraints’;

 validate resulting model against user transactions and review model with user; if applicable, make sure you made provisions for future growth;

 <optional> : draw EER diagram for final relational model

 Physical design and implementation

 choose a DBMS in which to implement the system

 design base relations for target DBMS

 discuss implementation solutions for domains, where necessary;

 implement integrity constraints, where possible; implement primary key, candidate keys, foreign keys, foreign key rules, attribute constraints and table constraints;

 design inter-table constraints in SQL (assertions), if any; implement them (if any) via Postgres rules/triggers;

 design views, if appropriate;

 design queries;

 design security mechanisms

 Testing, validation and maintenance (may be incorporated in the task below)

 Design and implementation of a web based interface (alternatively, of an interface written in Java)

 describe the functionality of the interface (this may be based on only a partition of the database);

 describe the organisation of the interface/site (e.g., how do HTML forms and PHP scripts link to each other);

 explain how each function is implemented; e.g., possible approaches to PHP implementations:

 employ SQL queries based on constant values.

 employ parameterised SQL statements (queries and update statements) catalogue; the parameters can be provided by users through HTML-forms;

 test the interface;

 state modifications to the database required by the implementation of the interface.

Hints

 The requirements you elicit in the requirements specification document can be more comprehensive than the system you will develop. If this is the case, then you will have to specify exactly what the scope covered by your system is. This approach will be a very useful exercise. In simple words it can be described as:

 what does the user want

 what can it be developed in the time you have

 decision: (a) are you going to develop the whole system; (b) are you going to develop a scaled down version of the whole system or (c) are you going to develop only a part of the overall system

 Reviewing a model with the user implies checking that the relevant data is described in the model and that the required transactions can be performed

 The user may expect certain developments to the database system in the future (because of expected developments to his/her company); you should provide for such future developments (“check for future growth”)

 Postgres should be the back end.

 A worked example of a database system design can be found in your main text (Connolly and Begg).

 Lab 11 and Lab 12 provide you with a sufficient introduction to the development of a PHP web interface to a database. By adapting and expanding the examples provided in the corresponding lab handouts you will be able to successfully complete the task regarding the interface. Alternatively, if you use Java, refer to the corresponding lab handout.

 You may use the interface to drive the development of the database (prototype based development). This method of work is highly recommended.

1

Version 1.3

set by: Marian F. Ursu, 2002