Database System Design Assessment: Tasks and Requirements

This assessment requires you to finish 3 tasks related to the different stages of a database system design. It is worth 25% of the overall mark for the module. The assessment is linked to the following learning outcomes of the module:

• Understand the principles of relational databases, relational algebra and SQL and be able to demonstrate a proficiency in writing Structured Query Language.

• Understand the database design stages within the systems development life cycle and be able to differentiate among conceptual, logical, and physical database design.

• Demonstrate knowledge of different approaches to data and object modeling by producing models for some simple problems.

• Acquire the necessary skills to develop an end to end application.

• Demonstrate key transferable skills in the areas of: communication, working with others, self-management, problem formulation and decision making.

This is an individual assessment.

If you have questions about this assessment, please post them to the discussion board on Blackboard or contact the module leader.

You are required to design a database for a conference submission system which allows researchers to submit their research papers for consideration. The requirements for the system are summarised as follows:

• Each author is affiliated with an institution. An institution is identified by a unique id and has a name. Each institution belongs to a single country where each country is identified by a unique id and a unique name. The institution name is unique within the country.

• Each paper is assigned a unique id by the system and has a title, abstract, and a submission date. A paper may have multiple authors, only one of whom is designated as the main author, i.e. the submitter. Each paper must have a main author. Each paper belongs to a single category, e.g. Hardware Security. Each category is identified by a unique id and has a unique name.

• Reviewers are uniquely identified by a unique id. First and last names, an email id, and a country of residence are recorded for each reviewer. Each reviewer is affiliated with an institution. A reviewer is associated with 1 or more categories that they can review. The policy of the conference is that a reviewer can not be an author.

• Each paper is assigned a number of reviewers for evaluation. A reviewer rates each paper assigned to them on a scale of 1 to 5, where 5 means the paper is excellent whereas 1 means the paper is poor. Each paper review has 2 types of written comments: one to be seen by the review committee only and the other as feedback to the author(s). The date each review has been completed is also recorded. We also wish to store the final status of the paper where the value 0 denotes reject, 1 denotes accept with revisions and 2 denotes accept without revisions. We also wish to record the average score of each paper (the average of the scores of the reviews for that paper).

• The system also records conflict of interests (COIs) between papers and reviewers, i.e. reviewers which cannot review a specific paper.

You are required to finish all 3 following tasks:

1) Design a conceptual schema (using UML notation) from the above description

2) Give the logical relational schema for the database including all constraints for all involved relations. Apply the necessary normalization (if required) to eliminate any redundancy from the relations

3) Give the Oracle SQL DDL statements to create the tables corresponding to the relations in your logical schema including all the necessary constraints