Get 15% off on your first assignment order and best assignment writing service for HND AssignmentsOrder Now

Have Any Question?

UK +4474648-84564

Free Support





Assignment 2:  Practical Assignment

Submission Deadline:

Assignment Submission: Sunday 15 January 2023, 23:59

If this submission date or time changes, or if there is a variation
to this specification, you will be informed.

  1. Introduction

This document forms the specification for one of your assignments in this module (there are two assignments in total).  This assignment will address and assess Learning Outcomes 1 and 3 as indicated below and in the Module Handbook:

Learning OutcomeSkillAssessed Through
1. Discuss critically the major developments and research in database securityLearningPractical assignment
2. Discuss critically the major developments and research in database administrationKnowledge and UnderstandingResearch paper and seminar
3. Analyse, design and build an appropriate secure database systemAnalysisPractical assignment
  • Requirements
    • You are required to build a small database to support the needs of a training company called Trainset.  As part of this you will also focus on some elements of security & data protection.  To do this you will need to create tables and SQL code to support the following scenario:
    • Trainset run professional short training courses (including ITIL, Project Management, etc.)
    • These courses have a title, a duration of 1,2,3,4 or 5 days, and a cost that varies between £300 and £2000 inclusive.
    • The courses are run repeatedly at various locations.
    • The start date of each course offering, its location and its instructor are recorded.
    • Students attend the courses and Trainset needs to record the student name, gender, credit card number and company name.
    • Credit card numbers should be entered as text with the format ‘nnnn nnnn nnnn nnnn’ where ‘n’ is a number between 0 and 9.
    • Each time a student attends a course they will give an evaluation of the course (the evaluation is a whole number from 0 to 5).
    • Students will attempt an examination for each course they attend. Trainset needs to record the examination result of each student in each examination.
    • It is not a requirement that every course run has attendees registered, and it is also not required that every student has to have attended a course.
    • See Section 3 for detailed requirements and mark allocations.
    • This coursework is weighted at 60% of the overall module mark.  
  • The coursework should be completed and submitted as an individual piece of work.  It is not group work.

    You should be aware of the rules on academic misconduct.  These rules are very clear. The Faculty takes a strict line in enforcing them. It is important that you understand the regulations as the penalties for infringement can be very serious including: module failure; grade point zero; downgrading your degree classification; award failure; expulsion from the University. To avoid plagiarism please consult the guidelines on the University website.
  • Requirements

Please read this section carefully.  This section provides the questions on which you will be assessed.  There are 7 questions in total.  All questions should be answered fully in your coursework submission.

  • For Question 5(c) you will need access to a secondary Oracle account.
    This will take the form of your student identifier suffixed with A.
  • Login credentials for this account will be provided by your module tutor.
  • If your secondary account credentials have not been received prior to starting your coursework, please contact your module tutor without delay.
  • Failure to contact your tutor to resolve login issues, leaving sufficient time to complete your coursework, will not constitute a sufficient reason to award or defer marks.

Question 1

Trainset have produced a first draft model of the scenario described in Section 2.1 as shown in Fig. 1:


You should assume this model is a reasonable, but incomplete, draft.  Use it as a basis to develop a final, improved entity model.  Then using your improved and complete entity model, construct a set of tables with suitably-defined columns to support the scenario.

(15 marks)

Question 2

Populate the tables you created from Question 1 with appropriate test data, taking into account the following:

  1. There may be some courses that have yet to run.
  2. There may be some students who have yet to a take a course.
  3. All course offerings are uniquely identified by a single key column called offering_id 
  4. Attendance records are uniquely identified by a combination of foreign keys (not by a single-column surrogate key).

In order to keep the amount of test data to a minimum, assume that the company is unlikely to run more than six course titles and that the number of students about whom they have details is less than 11.

Before continuing, review the query specifications in Questions 4 and 6. You will need to have data values that are relevant to these queries.  However, the design of your tables and test data should not be compromised merely to support these queries.

(10 marks)

Question 3

Create appropriate primary and foreign key constraints on the tables.  

Ensure you provide the SQL used to create these constraints, together with a brief justification for your choices (max. 150 words).

(10 marks)

Question 4

Develop a set of SQL queries against your test tables as follows, showing all output that they produce.  Note that you will need to populate your test tables with the appropriate data for each answer.

  1. Find details of all courses running in London.
  • Find the course that runs the greatest number of times.
  • Find the total number of attendees for each course.
  • Show details of the student names and the titles of the courses that they have attended.
  • List the title and cost of each course.
  • Add a column to your answer to 4(e) that compares the cost of the course to the average cost i.e. shows the difference between the course cost and the average cost of all courses.  

(15 marks)

Question 5

  1. Develop a view on a join of only two tables that shows course details together with the details of the offerings of those courses within the last 12 months.

    Do not include courses for which there are no offerings.
  2. Test the view from 5(a) for its ability to support DML activity.  The testing should involve DELETEs, INSERTs and a limited number of UPDATE statements affecting different columns.  You should then examine any error messages and briefly explain why they arise.  The tests should involve:
  3. Separate UPDATEs on each individual column within the view;
  4. INSERTs of new rows into the view;
  5. DELETEs of rows from the view.

Comment on the outcomes in terms of how easily the view can be updated (circa. 150-200 words).

(You may then wish to ROLLBACK any changes you have made to the data in order to obtain a consistent set of test data for Question 6).

  • Demonstrate and test the following using your primary Oracle account and your ‘_2’ secondary Oracle account.  Show your SQL statements and the output of your tests in each case.
  • Enable read only access to the view for the secondary account and show evidence of your grant by querying the Oracle data dictionary for privileges granted by you.
  1. Change the privilege for the secondary account to permit updates to the view.  Test the privilege using your ‘_2’ account and while logged in as ‘_2’ use the data dictionary to show the privileges that the ‘_2’ account now has.  
  1. Prohibit the secondary account from accessing the view.   

(20 marks)

Question 6

It has been found necessary to redact data from the birth date and credit card number columns held in the student table.

Show how this can be done by using the supplied Oracle package dbms_redact to redact:

  1. The day and month from the birth date column;
    1. The first 12 characters in credit/debit card numbers so that they are replaced by suitable dummy characters.

(15 marks)

Question 7

With the increasing risks of cyber-attacks, database hacks, and data leaks, knowing how to fully secure and protect a production database is crucial.  

Choose one of the areas below and discuss it in terms of historical and recent developments, and historical and current research in database security:

  • Authentication and authorization;  
  • Auditing access;  
  • Data encryption;  
  • Users’ permissions and database roles.  

Illustrate your findings with practical examples from specific database environments such as Oracle 11g/12c.

Maximum word count: 1,000 words, with 10% variation (plus or minus).

(15 marks)

  • Assessment Scheme

    • The following sections aim to provide you with guidance when producing your practical assignment work.  Only grades D (Distinction), M (Merit) and P (Pass) are pass grades.  
    • Distinction: 

      The work submitted for the practical assignment will demonstrate:
  • An entity model that correctly reflects the scenario.
  • A set of tables that adheres to the entity model and scenario.
  • Well-designed test data and constraints.
  • A correct set of SQL queries.
  • A thoroughly tested view with a discussion on its ability to be updated.
  • Completely-correct granting & revoking of privileges.
  • Completely-correct data redaction.
  • A solution to Question 7 that demonstrates a deep level of understanding of concept and practice, a high degree of critical analysis, and a strong technical underpinning. Get best of commercial cookery assignments in Australia !!
  • Merit:

    The work submitted for the practical assignment will demonstrate:
  • The answer must almost correctly reflect the scenario.
  • A set of tables that generally adhere to the entity model and scenario.
  • Well-designed test data and constraints.
  • A set of SQL queries that is mostly correct.
  • A well-constructed view with partial testing and discussion.
  • Mostly correct granting and revoking of privileges.
  • Mostly correct data redaction.
  • A solution to Question 7 that demonstrates a very good level of understanding of concept and practice – although the full extent of this may not be evident.  There will be a good critical analysis, and a good technical underpinning that demonstrates understanding of the core practices.
  • Pass:

The work submitted for the practical assignment will demonstrate:

  • The answer must generally reflect the scenario.
  • A set of tables that generally adhere to the entity model and scenario.
  • Well-designed test data and constraints.
  • A set of SQL queries that is generally correct.
  • A properly constructed view with some testing and discussion.
  • Partially correct granting & revoking of privileges and partially correct data redaction.
  • A solution to question 7 that demonstrates a reasonable level of understanding of concept and practice – and the core/fundamental elements of concept and practice will have been presented.  The solution will be mainly descriptive in content with a low level of critical analysis.  Some technical underpinning that demonstrates understanding of the core practices will be present, although this may be inaccurate or missing in parts.
  • Submission Guidelines

    • You should answer all 7 questions as specified in Section 3.
    • The primary method of submission is via Blackboard.
    • You should submit a PDF copy of your coursework by the submission deadline.  You may be asked to submit in another file format.  In this case your Tutor will inform you accordingly.  
    • If necessary, you may be asked to submit via another secondary method – such as an email attachment.  In this case your Tutor will inform you accordingly. Get HND Assignment help now !!
  • PDF documents should not be password protected or have restricted permissions – specifically, they should allow modifications (for annotations and comments).
    • You are advised to keep copies of all your work for your own records.
    • Your submitted coursework should include:
  • The entity model;
  • All table creation statements;
  • The SQL used to create your constraints;
  • Listings of all test data and an explanation of the chosen test data:
    Use the format and output from
        SELECT * FROM <your table name>; 
    to show the test data;
    It is not necessary to include the INSERT statements;
  • The view definition, and an explanation of how and why its data can be changed;
  • The output of all queries, including the output from querying the data dictionary, together with the code used to generate the output;
  • Any DDL statements that you have issued.
  • Use the following formatting guidelines:
  • Output should be properly formatted and presented to be clear and legible; particularly, avoid any wrap-around caused by the Oracle client.  
  • Use the Courier New font for presenting all code, output and test data.