CS41DS Assignment Help
Database Systems Assignment help
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.
R2 Design database queries to support each stage of the Science Archives
functionality, as described in the client specification. To demonstrate the
completion of this requirement, please include the following in your submission:
(e) a list of all identified business processes that emerge from the interview
transcript grouped by the type of user that interacts with the system;
(f) an SQL script containing the queries with appropriate comments that link
them with the previously identified business processes.
R3 Would there be anything to gain from reorganising the proposed relational
schema to fit Kimball’s Dimensional Model? How will the identified business
process(es) interact with the data marts added as the system’s functionality
expands? To demonstrate the completion of this requirement, please include the
following in your submission:
(g) a well-articulated, to-the-point narrative answering the questions above,
accompanied by a draft dimensional schema depicting identified facts and
dimension tables. Note that you do not need to provide the implementation
(no SQL code!) for your dimensional model, but the draft dimensional
schema will help communicate your thoughts for a new design.
You are expected to justify all of your design and implementation decisions and
support your justifications using visual aids, such as figures, tables, screenshots
and diagrams where appropriate.
Leave A Comment