辅导 FIT9132 Databases - Assignment 2A

日期：2023-10-17 07:28

FACULTY OF
INFORMATION
TECHNOLOGY
FIT9132 Introduction to Databases - Assignment 2A
Creating, Populating and Manipulating Database - Monash New Smile
Purpose Students will be asked to implement, via SQL, a small database in the Oracle
RDBMS from a provided logical model case study, followed by the insert of
appropriate data to the created tables. Once populated the database will be used
to carry out specified DML commands and make specified changes to the
database structure via SQL. This task covers learning outcomes:
1. Apply the theories of the relational database model.
3. Implement a relational database based on a sound database design.
4. Manage data that meets user requirements, including queries and
transactions.
Your task This is an open book, individual task. The final output for this task will be a set
of tables and data implemented in the Oracle RDBMS
Value 25% of your total marks for the unit
Due Date Thursday, 12th October 2023, 4:30 PM
Submission ● Via Moodle Assignment Submission
● FIT GitLab check ins will be used to assess history of development
Assessment
Criteria
● Application of relational database principles.
● Handling of transactions and the setting of appropriate transaction
boundaries.
● Application of SQL statements and constructs to create and alter tables
including the required constraints and column comments, populate
tables, modify existing data in tables, and modify the "live" database
structure to meet the expressed requirements (including appropriate use
of constraints).
Late Penalties ● 10% deduction per calendar day or part thereof for up to one week
● Submissions more than 7 calendar days after the due date will receive a
mark of zero (0) and no assessment feedback will be provided.
Support
Resources
See Moodle Assessment page
Feedback Feedback will be provided on student work via:
● general cohort performance
● specific student feedback ten working days post submission
● a sample solution
Page 1 of 17
FACULTY OF
INFORMATION
TECHNOLOGY
INSTRUCTIONS
Your task for this assignment is to create, populate and manipulate a database which can be used
to support the activities of a dental clinic called Monash New Smile (MNS).
Monash New Smile (MNS) provides dental services such as fillings, scalings, extractions etc to its
patients. For each service, MNS records a service code, service description and the MNS standard
fee for this service. The actual fee charged to a patient for a particular service may be varied from
this standard fee. These services are provided by a range of professionals (providers) that MNS
employs. Each provider is assigned a provider code. MNS records the provider's title, name, the
specialisation, and the room number where they normally treat patients. Each provider may have a
particular specialisation (one only), but some providers may not have any specialisation. Some
services require a particular provider while other services are able to be provided by a number of
providers.
MNS patients are assigned a patient number. The company records the patient's name, date of
birth, the residential address, contact phone number and contact email address (all patients are
required to provide a contact number (mobile phone) and an email address for the purpose of
confirming appointments). Patients must also provide another person’s name and phone number
for emergency purposes, which is also recorded.
Patients contact Monash New Smile and make appointments to see a provider. Each appointment
is booked with only a single provider, the provider is assigned when the appointment is first
recorded. The system also ensures that patients, providers, and rooms are not double booked (e.g.
a patient cannot have two scheduled appointments at the same date time). There is a possibility
that a patient needs more than one appointment per day (e.g. having an X-Ray procedure in the
morning and coming back in the afternoon for re-evaluation). An appointment can be a follow up of
a prior appointment. MNS records the prior appointment number for each follow up appointment.
For example, appointment number 4 is a follow up of appointment number 1, and appointment
number 31 is a follow up of appointment 4.
An appointment will require one or more services depending on the work required. For an
appointment, MNS records the appointment date and time and the room number in which the
consultation (appointment) will take place - in some circumstances this room may not be the
provider's normal room, for example, due to the need to use specialist equipment. The reception
staff, based on the patient's requirements, set an appointment length as either short (30 minutes),
standard (60 minutes) or long (2 hours). The staff who schedule appointments will also ensure that
the allocated provider is available and can provide the services which will be required during this
appointment, your design is not required to enforce this, although these staff must be able to look
up which providers provide which services if required.
Providers require the assistance of one dental nurse during an appointment. Each nurse is
assigned a nurse number - MNS also records the nurse's name and their contact number. One
nurse is assigned to each appointment. Nurses employed by MNS are hired based on three
employment status: Casual (C), Contract (T), and Fulltime (F).
Page 2 of 17
FACULTY OF
INFORMATION
TECHNOLOGY
Each service provided at a particular appointment may require several items, for example fillings
require Porcelain Etch and Silane. MNS records the quantity of items needed in each service
provided at a particular appointment. Each item is assigned a unique identifier. MNS also records
the item’s description, standard cost and the number of items that are currently on hand. This
figure is needed to make decisions about placing item purchase orders.
Based on these requirements a data model has been created for MNS:
The schema/insert file for creating this model (mns_schema_insert.sql) is available in the archive
ass2a_student.zip - this file partially creates the Monash New Smile tables and populates several
of the tables (those shown in purple on the supplied model) - you should read this schema carefully
and be sure you understand the various data requirements. You must not alter the schema file
in any manner, it must be used as supplied. Please note the yellow tables will not be used in
any manner in this assignment and should be ignored (they will be used in Assignment 2B).
Steps for working on Assignment 2A
1. Download the Assignment 2A Required Files (ass2a_student.zip) archive from Moodle
2. Extract the zip archive and place the contained files in your local repository in the folder
/Assignments/Ass2A. Do not add the zip archive to your local repo. Then add, commit and
push them to the FITGitLab server.
3. Run mns_schema_insert.sql
4. Write your answer for each task in its respective file (e.g. write your answer for Task 1 in
T1-mns-schema.sql and so on).
5. Save, add, commit and push the file/s regularly while you are working on the
assignment
Page 3 of 17
FACULTY OF
INFORMATION
TECHNOLOGY
6. Finally, when you have completed all tasks, upload all required files from your local
repository to Moodle. Check that the files you have uploaded are the correct files (download
them from Moodle into a temporary folder and check they are correct). After you are sure
they are correct, submit your assignment. Note that the filenames must not be changed -
you must submit files with exactly the same names as those supplied in the provided
archive (ass2a_student.zip).
The final SQL scripts you submit MUST NOT contain SPOOL or ECHO commands (you may
include them as you work but must comment them out before submission). Please carefully read
the Marking Guide on pages 16 and 17.
************************************************************************************************************
In arriving at your solutions for assignment 2A you are ONLY permitted to use the SQL structures
and syntax which have been covered within this unit:
● Week 6 Workshop and Week 7 Applied - Creating & Populating the Database
● Week 7 Workshop and Week 8 Applied - Insert, Update, Delete (DML) and Transaction
Management
● Week 8 Workshop and Week 9 Applied - SQL Part I - Basic
● Week 9 Workshop and Week 10 Applied - SQL Part II- Intermediate
● Week 10 Workshop and Week 11 Applied - SQL Part III - Advanced
SQL syntax and commands outside of the covered work, as detailed above, will NOT be
accepted/marked.
Views must not be used in completing these tasks.
You must also keep up to date with the Ed Assignment 2A forum where further clarifications may
be posted. Please be careful to ensure you do not publicly post anything which includes your
reasoning, logic, or any part of your work to this forum, doing so violates Monash
plagiarism/collusion rules and has significant academic penalties. Attend a consultation session,
use a private Ed post, or email your allocated tutor to raise such questions.
************************************************************************************************************
Page 4 of 17
FACULTY OF
INFORMATION
TECHNOLOGY
TASKS
ENSURE your id and name are shown at the top of any file you submit.
GIT STORAGE
Your work for these tasks MUST be saved in your individual local working directory (repo) in the
Assignment 2A folder and regularly pushed to the FIT GitLab server to build a clear history of
development of your approach. A minimum of ten pushes to the FIT GitLab server is required (2
pushes per file). Please note ten pushes is a minimum, in practice we would expect significantly
more. All commits must include a meaningful commit message which clearly describes what the
particular commit is about and must be correctly assigned to your valid GitLab author.
You must regularly check that your pushes have been successful by logging in to the web interface
of the FIT GitLab server; you must not simply assume they are working. Before submission, via
Moodle, you must log in to the web interface of the GitLab server and ensure your submission files
are present on the GitLab server and that their names are unchanged.
TASK 1: DDL (16 marks):
For this task you are required to add to T1-mns-schema.sql, the CREATE TABLE and
CONSTRAINT definitions for the EMERGENCY_CONTACT, PATIENT and APPOINTMENT tables
in the positions indicated by the comments in the script.
The table below provides details of the meaning of the attributes in the missing three tables. You
MUST use identical table and attribute names as shown in the data model above to name the
tables and attributes which you add. The attributes must be in the same order as shown in the
model. You must use delete RESTRICT/NO ACTION for all FK constraints. These new DDL
commands must be hand-coded, not generated in any manner (generated code will not be
marked).
Table name Attribute name Meaning
EMERGENCY_
CONTACT
ec_id Emergency contact identifier
ec_fname Emergency contact first name
ec_lname Emergency contact last name
ec_phone Emergency contact phone number
PATIENT
patient_no Patient number (unique for each patient)
patient_fname Patient first name
patient_lname Patient last name
patient_street Patient residential street address
patient_city Patient residential city
patient_state Patient residential state - NT, QLD, NSW, ACT, VIC,
Page 5 of 17
FACULTY OF
INFORMATION
TECHNOLOGY
TAS, SA, or WA
patient_postcode Patient residential postcode
patient_dob Patient date of birth
patient_contactmobile Patient contact mobile number
patent_contactemail Patient contact email address
ec_id Patient emergency contact identifier
APPOINTMENT
appt_no Appointment number (identifier)
appt_datetime Date and time of the appointment
appt_roomno Room in which appointment is scheduled to take
place
appt_length Length of appointment - Short, Standard or Long (S,
T or L)
patient_no Patient who books the appointment
provider_code Provider who is assigned to the appointment
nurse_no Nurse who is assigned to the appointment
appt_prior_apptno Prior appointment number which leads to this
appointment (this is required to be unique)
To test your code you will need to first run the provided script mns_schema_insert.sql to create the
other required tables. mns_schema_insert.sql, the head of this schema file, contains the drop
commands for all tables in this model. If you have problems with Task 1 and/or Task 2 simply rerun
mns_schema_insert.sql which will cause all tables to be dropped (including any you have created
as part of task 2) and correct the issues in your script. Do not add DROP TABLE statements to
either of your Task 1 or Task 2 scripts.
TASK 2: Populate Sample Data (24 marks):
Before proceeding with Task 2, you must ensure you have successfully run the file
mns_schema_insert.sql (which must not be edited in any way) followed by the extra definitions
that you added in Task 1 above (T1-mns-schema.sql). Note that the mns_schema_insert SQL
commands nor any of your task 1 SQL code may be added to/reproduced in your Task 2 script.
Load the EMERGENCY_CONTACT, PATIENT and APPOINTMENT tables with your own
test data using the supplied T2-mns-insert.sql script file, and SQL commands which will
insert as a minimum (at least), the following sample data:
○ 5 EMERGENCY_CONTACT entries
○ Involve at least 2 people being the emergency contact for more than two
patients
○ 10 PATIENT entries
○ Involve at least 5 adult patients and 5 under age (under 18 years old) patients
Page 6 of 17
FACULTY OF
INFORMATION
TECHNOLOGY
○ 15 APPOINTMENT entries
○ All appointments which you add must be scheduled on three specific days in
2023. You may pick any three dates between 1 May 2023 and 31 August 2023
○ Involve some parallel appointments (ie. two or more appointments scheduled
at the same date and time)
○ Involve at least 5 different providers
○ Involve at least 5 different nurses
○ Involve at least 3 follow up appointments
In adding this data, you must ensure that the data you insert will make full use of the various
features you have coded in Task1. For example, if you have coded a check clause with permitted
values of 1, 2 and 3 then your inserted data must use all three of these permitted values.
Your inserted data must conform to the following rules:
1. You may treat all the data that you add as a single transaction since you are setting up the
initial test state for the database.
2. The primary key values for this data should be hardcoded values (i.e. NOT make use of
sequences) and must consist of values below 100.
3. The data added must be sensible (e.g. the patient data must mimic the real data; the
patient, provider, nurse and room must not be double booked; the following appointment for
a provider must not be scheduled before the previous appointment for that provider is
completed; and other real scenario constraints).
For this task ONLY, Task 2, you may look up and include values for the loaded tables/data
directly where required. However, if you wish, you can still use SQL to get any non-key
values.
In carrying out this task you must not modify any data or add any further data to the tables
which were populated by the mns_schema_insert.sql script.
For all subsequent questions (Task 3 onwards) you are NOT permitted to:
● manually lookup an attribute/s in the database to obtain any value,
● manually calculate values (including dates/times) external to the database, e.g. on a calculator
and then use such values in your answers. ALL necessary calculations must be carried out
as part of your SQL code, or
● assume any contents in the database - rows in a table are potentially in a constant state of
change
Your answers must recognise the fact that you have been given, with the supplied insert file, only a
small sample snapshot of a multiuser database, as such you must operate on the basis that there
will be more data in all the tables of the database than you have been given. Your answers
must work regardless of the extra quantity of this extra "real" data and the fact that multiple
users will be operating in the tables at the same time. You must take this aspect into
consideration when writing SQL statements.
You must ONLY use the data as provided in the text of the questions. Failure to adhere to this
requirement will result in a mark of 0 for the relevant question.
Page 7 of 17
FACULTY OF
INFORMATION
TECHNOLOGY
Your SQL must correctly manage transactions and use sequences to generate new primary
keys for numeric primary key values (under no circumstances may a new primary key value be
hardcoded as a number or value).
TASK 3: DML (20 marks):
Your answers for this task (Task 3) must be placed in the supplied SQL script T3-mns-dm.sql
For this task you are required to complete the following sub-tasks in the same order they are listed.
Where you have been supplied with a string contained in italics, such as Orthodontics you may
search in the database using the provided string exactly as supplied. When a name is supplied you
may break the name into title, first name and last name, for example, Dr. Gerry ELLIOTT can be
split into Dr, Gerry and ELLIOTT, again note that the case must be maintained as it was supplied.
(a) Oracle sequences are going to be implemented in the database for the subsequent
insertion of records into the database for the EMERGENCY CONTACT, PATIENT, and
APPOINTMENT tables. Provide the CREATE SEQUENCE statements to create three
sequences which could be used to provide primary key values for these three tables. These
sequences must start at 100 and increment by 5.
Immediately prior to each create sequence command, place an appropriate DROP
SEQUENCE command so that it will cause the sequence to be dropped before being
created if it exists. Please note that there can only be three sequences introduced and used
in Task 3.
[2 marks]
Question 3b, 3c, 3d and 3e are related questions. You can use the information
provided below as needed in any part of task 3.
(b) On 1st September 2023, Jonathan Robey (phone number: 0412523122) made an
appointment for his two kids (Laura and Lachlan) for a general dental check-up. Jonathan
registered himself as the emergency contact of his two kids. Previously this family has not
been involved with MNS.
The reception then created two short (S) appointments for:
- Laura on 4th September 2023 at 3:30 PM, and
- Lachlan on 4th September 2023 at 4:00 PM.
Both kids were seen by Dr. Bruce STRIPLIN. You may assume that there is only one
provider named Dr. Bruce STRIPLIN in the system. Nurse Chelsea Ford (nurse number: 6)
was assigned to assist the provider in this appointment.
Take the necessary steps in the database to record the required entries for these
appointments. You may assume that Jonathan is only registered as the emergency contact
of Laura and Lachlan in the system (i.e Jonathan is not a patient at MNS and does not
Page 8 of 17
FACULTY OF
INFORMATION
TECHNOLOGY
currently wish to be registered as a patient). You may make up your own values for any
required attributes where a value has not been supplied, for example a date of birth.
[8 marks]
(c) Based on the diagnosis made by Dr Bruce STRIPLIN during the appointment on 4th
September, Lachlan needed a follow up appointment for a tooth extraction procedure. The
follow up appointment was scheduled 10 days after this first appointment on the 4th of
September. The follow up appointment time was 4PM. The assigned nurse for this follow up
appointment was nurse Katie (nurse number: 14) and the procedure would be carried by
Dr. Bruce STRIPLIN. Take the necessary steps in the database to record the required entry
for this follow up appointment.
[3 marks]
(d) On 10th September, Jonathan called the receptionist to reschedule Lachlan’s follow up
appointment due to important school commitments. The follow up appointment was shifted
to four days later (i.e. 14 days after the first appointment) at 4PM. The assigned provider
and nurse remained the same. Make these required changes to the data in the database.
[4 marks]
(e) On 15th September Dr. Bruce STRIPLIN had to leave the country due to an emergency
family matter and was not able to work for one week. All Dr. Bruce STRIPLIN appointments
between 15th September and 22nd September (inclusive) had to be cancelled. Take the
necessary steps in the database to remove these appointments from the system.
[3 marks]
TASK 4: DATABASE MODIFICATIONS (20 marks):
Your answers for these tasks (Task 4) must be placed in the supplied SQL script T4-mns-alter.sql
The required changes must be made to the "live" database (the database after you have
completed tasks 1, 2 and 3) not by editing and executing your schema file again. Before carrying
out the work below, please ensure that you have completed tasks 1, 2 and 3 above. Also
remember as stated on page 7 …"there will be more data in all the tables of the database than
you have been given. Your answers must work regardless of the extra quantity of this extra
"real" data and the fact that multiple users will be operating in the tables at the same
time."...
If in answering these questions you need to create a table, please place a drop table statement
prior to your create table statement.
Page 9 of 17
FACULTY OF
INFORMATION
TECHNOLOGY
(a) MNS would like to be able to easily determine the total number of appointments for each
patient in the system.
Add a new attribute which will record this requirement. Based on the data which is currently
stored in the system, this attribute must be initialised to the correct current number of
appointments for each patient.
As part of your solution provide appropriate select and desc statements to show the
changes you have made. Select to show any data changes which have occurred and desc
tablename e.g. desc customer to show any table structural changes.
[4 marks]
(b) From this point forward, MNS would like to allow a patient to register more than one
emergency contact person if they wish. Change the database to meet this requirement.
Note that you must not lose any of the existing emergency contact data.
As part of your solution, provide appropriate select and desc statements to show the
changes you have made. Select to show any data changes which have occurred and desc
tablename eg. desc customer to show any table structural changes.
[8 marks]
(c) To ensure that nurse skills are kept up to date, nurses may be required to undertake
initial/refresher training. This training is provided by other nurses. A given nurse may be
trained by many other nurses. A given nurse may act as a trainer for many other nurses.
The data below shows some samples of nurse training logs:
Page 10 of 17
FACULTY OF
INFORMATION
TECHNOLOGY
Change the database to meet this requirement. Note that you do not need to populate the
training data, you only need to provide the structure in which data will be stored.
As part of your solution, provide appropriate desc statements to show the changes you
have made e.g. desc customer to show any table structural changes.
[8 marks]
TASK 5: Transaction Theory (10 marks):
Your answers for this task (Task 5) must be written in an Ms Word document or Google document.
Once you have completed Task 5a and 5b, download or print the document as a pdf file and name
the file as T5-mns-transaction.pdf.
(a) A database has eleven transactions running as listed below (the time is shown horizontally
from left to right):
At time tc a checkpoint is taken, at time tf the database fails due to a power outage. If the
database is a write through database, three stages are involved in the recovery process
when the database is restarted.
Use the diagram above to discuss what happens at each of the three stages and what
transactions are involved.
[ 5 marks]
Page 11 of 17
FACULTY OF
INFORMATION
TECHNOLOGY
(b) Given the following transaction sequence, copy and paste this sequence into your answer
document and clearly indicate what locks are present at each of the indicated times (Time
0 to Time 34).
Cell entries must have the form:
● S(Tn) - for a shared lock by Tn,
● X(Tn) - for an exclusive lock by Tn or
● Tn wait Tm - for a wait of Tn due to Tm (where n and m are transaction numbers).
TIME TRANS ACTION A B C D E F G H
0 T1 Read A
1 T2 Read B
2 T1 Read C
3 T4 Read D
4 T5 Read A
5 T2 Read E
6 T2 Update E
7 T3 Read F
8 T2 Read F
9 T5 Update A
10 T1 Commit
11 T6 Read A
12 T5 Rollback
13 T6 Read C
14 T6 Update C
15 T7 Read G
16 T8 Read H
17 T9 Read G
18 T9 Update G
19 T8 Read E
20 T7 Commit
21 T9 Read H
Page 12 of 17
FACULTY OF
INFORMATION
TECHNOLOGY
22 T3 Read G
23 T10 Read A
24 T9 Update H
25 T6 Commit
26 T11 Read C
27 T12 Read D
28 T12 Read C
29 T2 Update F
30 T11 Update C
31 T12 Read A
32 T10 Update A
33 T12 Update D
34 T4 Read G
Complete the following:
(i) For each of the listed items A .. H, what wait states are present at time 34 (the last time
listed). Shown the waits in the form:
Item A: T1 waiting on T5 (note this is an example ONLY)
Item B: … etc
(ii) Prepare a wait for graph indicating the state of waiting locks at time 34. Your wait for
graph can be prepared using any drawing package such as LucidChart or free hand
drawn. Paste/insert an image of your wait for graph into your answer document below
your answer to (i) above
(iii) Report if deadlock exists or not, and if it does exist, list the transactions involved.
[ 5 marks]
Page 13 of 17
FACULTY OF
INFORMATION
TECHNOLOGY
Submission Requirements
Due Date: Thursday, 12th October 2023 at 4:30 PM
Please note, if you need to resubmit, you cannot depend on your tutors' availability, for this reason,
please be VERY CAREFUL with your submission. It is strongly recommended that you submit
several hours before this time to avoid such issues.
For this assignment there are five files you are required to submit to Moodle and must exist in your
individual FIT Gitlab repository under Ass2A folder:
● T1-mns-schema.sql
● T2-mns-insert.sql
● T3-mns-dm.sql
● T4-mns-alter.sql
● T5-mns-transaction.pdf
If you need to make any comments to your marker/tutor please place them at the head of each of
your solution scripts in the "Comments for your marker:" section.
Do not zip these files into one zip archive, submit four independent SQL scripts and one pdf file. The
individual files must also have been pushed to the FIT GitLab server with an appropriate history as
you developed your solutions (a minimum of ten pushes - 2 per file, however we would strongly
recommend more than this). Please ensure your commit comments are meaningful.
Late submission will incur penalties at the rate of -10 marks for every 24 hours the
submission is late.
Please note we cannot mark any work on the GitLab Server, you need to ensure that you
submit correctly via Moodle since it is only in this process that you complete the required student
declaration without which work cannot be assessed.
It is your responsibility to ENSURE that the files you submit are the correct files - we
strongly recommend after uploading a submission, and prior to submitting, that you
download the submission and double-check its contents.
Your assignment MUST show a status of "Submitted for grading" before it will be marked.
Page 14 of 17
FACULTY OF
INFORMATION
TECHNOLOGY
If your submission shows a status of "Draft (not submitted)" it will not be assessed and will incur
late penalties after the due date/time.
Please carefully read the documentation under the "Assignment Submission" on the Moodle
Assessments page which covers things such as extensions and resubmission.
Resubmission
If you wish to resubmit your assignment you must email your tutor, provide your full details as listed
below and request that they reopen your submission for a second submission. Note if this
resubmission is after the due date/time the submission will be regarded as late.
When you contact your tutor (or workshop leader) via email, please ensure you clearly include your
full name, unit code and applied class number as part of every email you send so they can identify
who the message has come from. This will ensure we can respond as quickly and accurately as
possible.
You must NOT assume that your tutor will be available if you require a resubmission close to the
due date/time - they may have classes or not be available for other reasons, so do not leave
submission to the very last minute.
Page 15 of 17
FACULTY OF
INFORMATION
TECHNOLOGY
Marking Guide
Submitted code will be assessed against an optimal solution for this task - this optimal solution will
be available as a sample solution after Assignment 2A has been graded. Given that parts of this
task involve SQL, there are often several alternative approaches possible, such alternatives will be
graded based on the code successfully meeting the briefs requirements. If it does, the answer will
be accepted and graded appropriately.
Marking Criteria Items Assessed
TASK 1 DDL 16 marks
DDL Creation of tables Maximum 10 marks - Create table:
● Marks awarded for correct table DDL
● Marks awarded for correct attributes/data types
● Marks awarded for correct PK definition
● Marks awarded for correct use of column comments
● Mark penalty applied if different table/attribute names used
than expressed in the supplied data model
● Mark penalty applied if different order of attributes used
than expressed in the supplied data model
● No marks awarded if generated schema used
DDL implementation of
non-PK database
constraints
Maximum 6 marks - Non-PK Constraints:
● Marks awarded for correct implementation of non-PK
constraints
TASK 2 Populate Sample Data 24 marks
Insert of required items
test data
Maximum 12 marks- Insert of data:
● Marks awarded for correct insert of required data
● Marks awarded for correct management of transactions
Insert of valid test data Maximum 12 marks - Valid data inserted:
● Marks awarded for validity of data inserted
○ meets the requirements expressed in the
assignment brief
● Marks awarded for correct management of dates when
inserting
Task 3 DML 20 marks
Maximum 20 marks - Satisfy brief requirements:
● Marks awarded (a) - (e) for SQL code which meets the
expressed requirement
● Mark penalty applied if commit not used appropriately
● Mark penalty applied if date handling and string database
lookups not managed correctly
Page 16 of 17
FACULTY OF
INFORMATION
TECHNOLOGY
Task 4 Database Modifications 20 marks
Maximum 20 marks - Satisfy brief requirements:
● Marks awarded (a) - (c) for SQL code which meets the
expressed requirement (including appropriate use of
constraints). In making these modifications there must be
no loss of existing data or data integrity within the
database.
● Mark penalty applied if commit not used appropriately
● Mark penalty applied if column comments not used where
required
Task 5 Transaction Theory 10 marks
Maximum 10 marks - Satisfy brief requirements:
● Marks awarded (a) - (b) for correct answers and
explanations
● Mark penalty for incorrect lock table notation
Correct u