Reference no: EM133034218

CO4703 Database Implementation - University of Central Lancashire

Learning Outcome 1: Implement a functional relational database, giving consideration to integrity and validation constraints, using appropriate database environment

Learning Outcome 2: Apply and justify database design techniques for the construction of a conceptual data model

Learning Outcome 3: Critically review work with respect to underpinning theories

Task 1
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)
Module (mCode, mTitle, mDescription, mLeader, credit, fee)
Registration (regNo, sId, mCode, dateOfRegistration, approvedBy)

Student

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

surname                       15 char maximum

forenames                    15 char maximum

address                                    45 char maximum

mobilePhone               11 digit number (e.g. 07123456789)

cCode                          5 char (e.g. C1001)                             (course code)

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

dob                              date                                                     (date of birth)

Module

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

mTitle                          50 char maximum                                (module title)

mDescription               1000 char maximum                            (module description)

mLeader                      20 char maximum                                (module leader)

credit                           2 digit number (20 or 40 or 60 only)

fee                               number (e.g. 495.95); always below 1000

Registration

regNo                          number (5 digits)

sId                               20 char maximum

mCode                                    6 char

dateOfRegistration     date

approvedBy                20 char maximum

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 mCode, 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

1. Enter details; all alphabets should be in upper case automatically
2. Save a new record
3. Year should be 1 to 4 only (inclusive)
4. Date of birth (they should be on or past their 18th birthday and on or before 40th birthday)
5. Buttons that perform functions (SAVE, CLEAR, DELETE etc.)
6. A list of values for the course code (based on existing codes in the script file)
7. Any other functionality you deem to be appropriate

Form B - Display a student's module registration details

A twoblock form showing full details of a student and all the modules she/he has registered.
1. Enter a sId and the basic module details are displayed
2. This form is read only and updating should be disallowed
3. sId is selected from a list of values
4. For each module registration the module title and fee should be displayed
5. Display the total amount of module fees for the student
6. Addition of functional buttons
7. Any other functionality that you deem to be appropriate

Form C - Menu

1. Two buttons which call up Forms A and B
2. When Forms A and B are exited, control should pass back to the menu
3. A password is required for form A (but not B). Form A should be accessed only if the correct password is entered.
4. 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.

1. SQL table creation and insertion statements for all tables.
2. 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.
3. Include screenshots to illustrate your explanation wherever appropriate.

Task 2

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

(a) Design a relational database for the table fragment shown on the next page.

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 customer code 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.

(b) Critically evaluate normalisation as a technique for database design. For each advantage or disadvantage identified, you should use the Product-4-U Ltd 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 formatsuch as Harvard Refencing.

Attachment:- Database Implementation.rar