Database Systems & Security
Assignment Brief 2020/21

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 (although 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 (e.g., Oracle or MySQL). We expect you to provide 2-3 example SQL statements, for instance a meaningful query to your database.

The design and implementation of your database should be documented in a
report, following and clearly demonstrating the design, development and implementation of the artefact on the basis of your chosen scenario. The number of words in the report should be in the range of 1200 words.

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 and the data involved.

3. Use the Oracle Data Modeller or any equivalent tool 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.

5. Use your database (e.g., Oracle and SQLPlus) to provide the SQL statements to create your tables. Also provide the SQL statements to populate each table with at least one entry. Execute the statements and present their output.

6. Use your database (e.g., Oracle and SQLPlus) to devise at least 3 SQL queries to retrieve some essential data 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.

7. Please provide a paragraph discussing security and integrity aspects when installing your database.

NOTE: I have chosen to design Doctor's Appointment system. The purpose of my design would be to make a system which can help in scheduling appointment online with different doctors and the system also have patients diseases history, patients can select dates and times when the doctor is next available for their check-ups

Marking Grid

50% of marks are required to pass. All required information and description has to go into one section of your report.