This deliverable is concerned with the implementation of your relational schema from coursework 1, using a relational database management system (DBMS(, It is strongly recommended to use either Oracle or MySQL.
You should base your implementation on the normalised relational schema developed in coursework 1 (original or refined). The coursework should be delivered in a form so that the tables can be easily created, data inserted and queries and views run easily by the person marking the work. This means that screen shots of code are not acceptable, please include the text of the code either in separate files or copy and paste the text of the code into your main report document.
You can create your tables using individual “create table” statements, but we would recommend instead that you put all of these into one script file, so that it is easier to reconstruct your entire schema should you find data or tables have been corrupted or when you wish to make changes. You can use as a template for your create tables script the “labtables”script used to create the EMP and DEPT tables for the labs. Remember to use the correct version of the script depending on whether you are using the Oracle or MySQL DBMS.
For coursework 2, you are asked to hand in:
- The relational schema from coursework 1. If you decide to amend this, that is fine, you are not tied to the design you had for coursework 1, but you should include the relational schema you submitted for coursework 1 and explain any changes you have made.
- A listing of all the 'create table' commands that were used to set up your database. You should make use of declarative constraints to establish primary and foreign keys and perform validation checks on data to be entered.
- The sample test data. This test data should be carefully designed in order to test that your queries will work under any conditions.
- A set of 3-4 view definitions ('create view' commands), with listings of the output you obtain when you list these views.
AND the SQL CREATE VIEW command itself. Marks will be awarded for the appropriateness of the views created in the context of the library system, that is, how useful they are likely to be to individuals or groups of users of the system.
- A set of about 12 SQL queries with listings of the output you obtain when you run the query. Marks will be awarded for use of a wide range of SQL language constructs and the relevance of the query to individuals or groups of users of the library system. A query, in this context, consists of both the English explanation of what the query is intended to do, the SQL code for the query and the output it produces. Remember that screen prints of code are not acceptable, you must submit the proper text of the query, though screen prints of the output produced by the query are acceptable.
- A set of 4 database triggers used to implement business rules or perform validation, auditing or replication processes in the system. A trigger in this context includes an English specification of what the trigger is intended to do, the code of the trigger itself and a demonstration of trigger execution.
Note that it is essential that you do actually build the database AND test out all the queries / views/triggers that you include in your report. Any evidence that this has not been done, or that any problems have not been honestly reported, will be severely penalised. In particular, any evidence that code has been included that won’t actually run on the DBMS being used, unless clearly identified as such, will result in a mark of 0% for the whole coursework submission.
Finally, you should include up to a page discussing the data security issues that would need to be addressed by the administrators and users of the system, including complying with the UK Data Protection Act 2018.
Assessment
Like the first coursework, this second coursework counts for 50% of the final assessment of the module.
Contact us to get any database project assignment help with an affordable prices at realcode4you@gmail.com and get instant help