FIT9132代写、代做SQL语言编程

日期：2023-10-16 09:34

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 use of Git 10 marks

Maximum 10 marks - Git used appropriately:

● Marks awarded for ten pushes showing a clear

development history for the files (minimum two pushes per

file)

● Marks awarded for correct Git author details used in

pushes (see week 2 Applied notes Appendix)

● Marks awarded for the use of meaningful commit

messages (ie. not blank or of the form "Push1")

Penalties

Use of

● VIEWs

● SET ECHO or

SPOOL commands,

and/or

● PL/SQL

Use of VIEWS, inclusion of SET ECHO/SPOOL, and/or PL/SQL

commands in Task 1 - Task 4, will result in a grade deduction

of 10 marks being applied.

Incorrect application of

relational database

principles

Marks will be deducted, based on any question, where basic

relational model principles have been violated. For example,

creation of a table which is not in 3NF

Late submission penalty - 10 marks for each 24 hours late or part thereof

Final Assignment Mark Calculation

Total:100 marks, recorded as a grade out of 25

Page 17 of 17