College of Engineering & Physical Sciences

College of Engineering & Physical Sciences Assignment help

Assignment Brief/ Coursework Content:
Aim
This assignment is designed to extend and assess your understanding of
database design, implementation and usage in a business intelligence context.
Problem Statement

An organisation hires you to design the database and demonstrate the use of a
recommendation system called Science Archives. With that new system, the
company aims to provide meaningful recommendations of publicly available
scientific articles, conference papers, university theses and books to users in
relation to their scientific interests. To understand what is necessary and meet the
system’s expectations, you had an interview with a representative from the
organisation, i.e., your client, who provided you with information regarding the
system’s functionalities. The whole transcript of the interview is found in the
Appendix of this coursework brief.

Requirements
R1 Design a database to underpin the functionality of Science Archives, as
specified in the client interview. To demonstrate the completion of this
requirement, please include the following in your submission:

(a) a valid, detailed relational schema featuring all necessary normalised
relations, all attributes with appropriate data types and constraints, a clear
indication of primary and foreign keys as well as correct cardinalities and
connectors linking the relations (an example is found in figure 1 of the
Appendix);

(b) concise yet conclusive explanation of the design decisions that lead to the
creation of the relational schema, with direct reference to the client
specification, i.e., the interview transcript found in the Appendix;

(c) the SQL Data Definition Language (DDL) commands necessary to create a
database schema that reflects your relational schema with all appropriate
attributes and attribute domains, integrity constraints (not just primary and
foreign keys), as well as a few SQL statements to populate the database
with dummy data (at least three entries per table);

(d) for every type of integrity constraint in place, you must use an example SQL
statement to demonstrate its effectiveness, that is, under which
circumstances violations may occur.