Database Implementation

School of Psychology and Computer Science

UCLan Coursework Assessment Brief

2021-2022

Module Title: Database Implementation

Module Code: CO4703

Level 7

Database Implementation

This assessment is worth 100% of the overall module mark

THE BRIEF/INSTRUCTIONS

Task 1 (50 marks)

Use Oracle Forms to create a database application. This application is based on the following table types, with primary keys underlined and foreign keys in italics:

Student (sId, surname, forenames, address, mobilePhone, cCode, cYear, dob)

Sessions (sCode, sType, mCode, tutor, SDT, EDT, Room)

Attendance (aNo, sId, sCode, CSDT)

Student

sId 17 char maximum (e.g. Johnson_B) (student ID)

surname 15 char maximum

forenames 15 char maximum

address 50 char maximum

mobilePhone 11 digit number (e.g. 07654321234)

cCode 7 char (e.g. COMP101) (course code)

cYear 1 digit number (range 1,2,3,4,5) (course year)

dob date (date of birth)

Sessions

sCode number (3 digits) (session code)

sType Lecture, Lab, Tutorial, Seminar (session type)

mCode 6 char maximum (e.g. CO4703) (module code)

tutor 20 char maximum (tutor name)

SDT date (start date and time)

EDT date (end date and time)

Room 10 char maximum (e.g. CM210)

Attendance

aNo number (5 digits)

sId 17 char maximum

sCode number (3 digits)

CSDT date (date and time of card scanning)

You are expected to create these tables using SQL and then populate them with appropriate and sufficient test data of your own (hint: use varchar2 for sId and also for all variable length character strings).

To gain a pass mark for Task 1 you will achieve basic functionality for forms A, B and C as outlined below. To gain higher marks you will create a more user-friendly interface (e.g. good screen layout, appropriate use of colours, use of buttons, clear messages), and will achieve the functionality as italicised for each form.

Form A – Create/Amend a student (20 marks)

Enter details; all alphabets should be in upper case automatically

Save a new record

Year should be 1 to 5 only (inclusive)

Date of birth (they should be on or past their 18th birthday)

Buttons that perform functions (SAVE, CLEAR, DELETE etc.)

A list of values for the course code

Any other functionality you deem to be appropriate

Form B – Display a student’s attendance details (20 marks)

A two block form showing full details of a student and all the sessions she/he has attended.

Enter a sId and the basic attendance details are displayed

This form is read only and updating should be disallowed

sId is selected from a list of values

For each session attendance the session type, module code and tutor should be displayed

Display the total number of sessions attended by the student

Addition of functional buttons

Any other functionality that you deem to be appropriate

Form C – Menu (10 Marks)

Two buttons which call up Forms A and B

When Forms A and B are exited, control should pass back to the menu

A password is required for form A (but not B). Form A should be accessed only if the correct password is entered.

Any other functionality that you deem to be appropriate.

For Task 1, you should submit relevant and detailed documentation in a word-processed file demonstrating the design and implementation of the above three forms. The documentation should have the following; you may include additional items that you deem to be appropriate.

SQL table creation and insertion statements for all tables.

For each of the functions listed above under Form A, B and C, explain how the function is implemented. Show and explain trigger codes and other techniques (list of values, alert etc.) wherever appropriate.

Include screenshots to illustrate your explanation wherever appropriate. The screenshots should show the date and time when they are taken, which are normally displayed in the Windows taskbar.

Because Task 1 is assessed through this documentation only, anything that is not included in it will not be given marks.

Task 2 (50 marks)

You should submit one single word-processed file including the previous Task 1 and the current Task 2.

Design a relational database for the table fragment shown below.

You should assume that the data shown in the table is a typical snapshot of data for these records and demonstrates all the enterprise rules which apply, even though some rules may be simpler than those in reality.

Normalise this table into Third Normal Form showing the intermediary stages of First and Second Normal Form. Primary keys and foreign keys in all the three forms should be indicated clearly. You are expected to start the normalisation process by first choosing the booking number alone as a primary key. Your normalised tables should only include the attributes shown in the table fragment; do not add any new attribute.

Justify your solution to this problem.

(30 marks)

Critically evaluate normalisation as a technique for database design. For each advantage or disadvantage identified, you should use the A2Z data to illustrate and explain it. You may extend these data in order to identify suitable examples to illustrate your point, however you should only do so if the current data do not provide for such examples.

The evaluation should not exceed 500 words (excluding the list of references).

References must be listed in the end and cited within your evaluation using a consistent format such as Harvard Refencing.

(20 marks)

Learning Outcomes being assessed:

1. Implement a functional relational database, giving consideration to integrity and validation constraints, using ….appropriate database environment.

2. Apply and justify database design techniques for the construction of a conceptual data model.

3. Critically review work with respect to underpinning theories.

PREPARATION FOR THE ASSESSMENT

Review the learning materials on Blackboard particularly those in the Lab and Tutorial Material folder.

Completion of the lab materials will provide formative feedback in preparation for this assessment.

You are expected to continue independent study alongside the timetabled sessions

RELEASE DATE AND HAND IN DEADLINE

Assessment Release date: 18 March 2022

Assessment Deadline Date and time: Tuesday 3 May 2022 20:00

Please note that this is the final time you can submit – not the time to submit!

SUBMISSION DETAILS

Assignment work submissions must be word-processed with a footer comprising: your registration number; module code; date; page number. Work should be submitted before the deadline specified via the assignment box for CO4703 in Blackboard.

You are required to submit one single word-processed file including both tasks above. You must also complete and add the cover sheet (separate file on Blackboard) to your assignment submission.

You must retain SQL script and Form files in case you are required to submit in future or in case you are required to demonstrate your work.

Staff reserve the right to interview individual students in order to determine final marks.

HELP AND SUPPORT

You will find information links to all our Library resources in the Library area of the Student Hub. For support with using these resources, please contact your subject librarian at [email protected].

You can get support with your academic skills (academic writing, critical thinking and referencing) through WISER. For details of the WISER support services go to the Study Skills section of the Student Hub.

If you have not yet made the university aware of any disability, specific learning difficulty, long-term health or mental health condition, please complete a Disclosure Form. The Inclusive Support team will then contact to discuss reasonable adjustments and support relating to any disability.  For more information, visit the Inclusive Support page of the Student Hub.

To access mental health and wellbeing support, please complete our online referral form. Alternatively, you can email [email protected], call 01772 893020, attend a drop-in, or visit our UCLan Wellbeing Service pages for more information.

If you have any other query or require further support you can contact Student Support via [email protected].  Speak with us for advice on accessing all the University services as well as the Library services. Whatever your query, our expert staff will be able to help and support you. For more information please visit the Student Hub.

If you have any valid mitigating circumstances that mean you cannot meet an assessment submission deadline and you wish to request an extension, you will need to apply online prior to the deadline.

Disclaimer: The information provided in this assessment brief is correct at time of publication. In the unlikely event that any changes are deemed necessary, they will be communicated clearly via e-mail and a new version of this assessment brief will be circulated.

Version: 1