7CI022 Database Systems & Security Coursework Assignment Brief | University Of Wolverhampton

Learning Outcomes

• LO1: Apply a critical understanding of, and demonstrate ability in application of the tools and techniques used in the development of database applications
• LO2: Utilise appropriate software to design and populate a database so that the data can be accessed by an analysis tool.
• LO3: Understand and apply the fundamental legal and ethical aspects of data governance and security.

Your Task

You are required to design and implement a database system for an application area of your choice. For example, it could be a booking type application or an order processing type system (but other suggestions are welcome!). Examples include a system to book tickets for a theatre, a flight or a system to allow customers to hire or buy books, tools, cars or other. We want you to propose a scenario drawn from your own experience. The scenario and the produced database system will be
judged by the tutors using the marking schema in the later section of this document. The underlying database should be a relational one (Oracle). We expect you to provide a few example SQL statements, for instance a meaningful query to your database. You are also required to discuss data governance aspects, i.e., security, integrity, ethics and legal issue.

The assignment has two parts: a modelling part and an implementation part. Both have to be contained in the same assignment report.

Subtasks

Part 1 (LO1, LO2) is about the design of your database. You need to do the following 4 subtasks:

1. Choose an appropriate application area or business. Please refrain from reusing one of the scenarios in the description above. Introduce your scenario.
2. Analyse your application area or business. Informally describe potential business rules, scenarios, information needs and the data involved.
3. Use the Oracle Data Modeller to draw an ER model that covers your scenario. At least 4 or 5 entities are expected, including attributes, cardinalities and relationship names.
4.  Normalise your database and explain why it is normalised. List table names, primary keys,foreign keys and attributes.

The second part (LO1, LO3) is about implementing your database and legal and ethical aspects of data governance and security related to your scenario. You need to do the following subtasks:

1. Database implementation: Implement the model created in Part 1. The following content is expected:
a. Use your database (e.g., Oracle Data Modeler and Live SQL) to provide the SQL statements to create your tables. Also provide the SQL statements to populate eachtable with at least one entry. Execute the statements and present their output.
b. Use your database (e.g., Oracle and Live SQL) to devise at least 3 SQL queries to retrieve some essential data, covering identified information needs, from your database. At least one of them needs to contain a JOIN (AND) operation across 2 or more tables. Execute the queries and present their output.

2. Security, integrity and ethics aspects: Please discuss security, integrity and ethics aspects for the operation of your database and data governance. The following parts are expected:
a. A general discussion and reflection of security, integrity, and ethical aspect of data governance.
b. A discussion of security, integrity, and ethical aspect of data governance applied to your scenario.