FIT3171代写、代写SQL语言编程

日期：2022-05-31 08:16

FIT3171 Databases

Creating, Populating and Manipulating Database - Run Monash (RM)

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, 26 May 2022, 4:30 PM (AEST) / 2:30 (MYT)

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 14

FACULTY OF

INFORMATION

TECHNOLOGY

INSTRUCTIONS

Run Monash (RM) is a running carnival which is held on separate dates at various Monash

campuses during different seasons (Summer, Autumn, Winter and Spring) of the year. The

carnival naming convention that Run Monash uses is RM Series name> . So, for example, a carnival to be held during the Autumn season at the

Clayton campus in 2022 will be named RM Autumn Series Clayton 2022.

Anyone can attend an RM Carnival, the carnivals are open to the public as well as Monash

staff and students. A carnival is run on a particular date, in a particular location and only

lasts for one day. RM only runs one carnival on any particular date. During a carnival a range

of events are offered from the following list (only some may be offered):

● Marathon 42.2 Km

● Half Marathon 21.1 Km

● 10 Km Run

● 5 Km Run

● 3 Km Community Run/Walk

Run Monash expects to offer around 10 - 20 such events across all carnivals in a given year.

When a competitor initially registers for Run Monash, they are assigned a unique competitor

number. A competitor is required to provide details of an emergency contact at the time of

registration. The relationship to the competitor can be Parent (P), Guardian (G), Partner (T)

or Friend (F).

When a carnival is being offered, Run Monash contacts all registered competitors and

provides details of the carnival date and what events are on offer. A competitor can only

attend one event at a particular carnival. Every entry is assigned an entry number. The entry

number is reused in each event. Run Monash also, on the carnival day, using official timing

devices, Run Monash records the entrants starting and finishing times.

Teams are identified by a unique team name which the team manager must select when

they first create the team. This team manager can then add/invite other competitors from the

carnival to join their team. Team names are unique only within a given carnival. A given team

name may be reused by different competitors in a different carnival as teams are recreated

for each carnival depending on which competitors have entered an event for the carnival.

Run Monash wishes to record, as part of the stored data, how many members are on each

team. Teams may also nominate a charity for which they will raise funds, although not all

teams will do so. All charities for which funds can be raised must first be approved by Run

Monash. Note that an individual competitor may be supporting a charity as an individual and

also the same or a different charity as a team member.

Page 2 of 14

FACULTY OF

INFORMATION

TECHNOLOGY

A model to represent this system has been developed:

The schema/insert file for creating this model (rm-schema-insert.sql) is available in the

archive ass2-student.zip - this file partially creates the Run Monash 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.

Steps for working on Assignment 2

1. Download the Assignment 2 Required Files (ass2-student.zip) archive from Moodle

2. Extract the zip archive and place the contained files in your local (MoVE or local

HDD) repository in the folder /Assignments/Ass2. Do not add the zip archive to your

local repo. Then add, commit and push them to the FITGitLab server.

3. Run rm-schema-insert.sql

4. Write your answer for each task in its respective file (e.g. write your answer for Task 1

in T1-rm-schema.sql and so on).

5. Save, add, commit and push the file/s regularly while you are working on the

assignment

6. Finally, when you have completed all tasks, upload all required files from your local

repository to Moodle (if you are using MoVE you will need to download them to your

local HDD first - do not attempt to upload from MoVE). 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.

Page 3 of 14

FACULTY OF

INFORMATION

TECHNOLOGY

Note that the final SQL scripts you submit MUST NOT contain SPOOL or ECHO commands

(you may include them as you work but must remove them before submission). Please

carefully read the Marking Guide document.

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 2 folder and regularly pushed to the FIT GitLab server to build a clear history

of development of your approach. Any submission with less than five pushes to the

FITGitLab server will incur a grade penalty of 10 marks. Please note five pushes is a

minimum, in practice we would expect significantly more.

Before submission via Moodle you must log into the web interface of the GitLab server and

ensure your files are present in your individual repo.

TASK 1: DDL (20 marks)

For this task you are required to add to T1-rm-schema.sql, the CREATE TABLE and

CONSTRAINT definitions which are missing from the supplied partial schema script in the

positions indicated by the comments in the script.

The table below provides details of the meaning of the attributes in the missing four tables.

You MUST use exactly the same relation 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. These new DDL commands must be hand-coded, not

generated in any manner (generated code will not be marked).

Table name Attribute name Meaning

EMERCONTACT

ec_phone Emergency contact’s phone number (unique identifier)

ec_fname Emergency contact’s first name

ec_lname Emergency contact’s last name

COMPETITOR

comp_no Unique identifier for a competitor

comp_fname Competitor’s first name

comp_lname Competitor’s last name

comp_gender Competitor’s gender (‘M’ for male, ‘F’ for female, or ‘U’ for

‘Undisclosed’)

comp_dob Competitor’s date of birth

comp_email Competitor’s email

Page 4 of 14

FACULTY OF

INFORMATION

TECHNOLOGY

comp_unistatus Competitor’s university student/staff status ('Y' for Yes or

'N' for No)

comp_phone Competitor’s phone number

comp_ec_relationship Emergency contact relationship to competitor (‘P’ for

Parent, ‘G’ for Guardian, ‘T’ for Partner, or ‘F’ for Friend)

ENTRY

entry_no Entry number (unique for each event)

entry_starttime The entrant start time

entry_finishtime The entrant finish time

TEAM

team_id Team identifier (unique)

team_name Team name

team_no_members Number of team members

To test your code you will need to first run the provided script rm-schema-insert.sql to

create the other required tables. rm-schema-insert.sql, at the head of the file, contains the

drop commands for all tables in this model. If you have problems with Task 1 and/or Task 2

simply rerun rm-schema-insert.sql which will cause all tables to be dropped and correct the

issues in your script.

TASK 2: Populate Sample Data (20 marks)

Before proceeding with Task 2, you must ensure you have run the file rm-schema-insert.sql

(which must not be edited in any way) followed by the extra definitions that you added in

Task 1 above (T1-rm-schema.sql).

Load the EMERCONTACT, COMPETITOR, ENTRY, and TEAM tables with your own

test data using the supplied T2-rm-insert.sql script file, and SQL commands which

will insert as a minimum, the following sample data:

(i) 5 EMERCONTACT entries

● of the 5 contacts added 3 must be contacts for more than one

competitor

(ii) 15 COMPETITOR entries

● Have at least 10 competitors who are Monash student/staff

● Have at least 2 competitors who are not Monash student/staff

● Included at least 2 competitors who are under 18 years of age

(iii) 30 ENTRY entries

● Included at least 10 competitors

● Included at least 6 events from 3 different carnivals

● Have at least 5 competitors who join more than 2 events

● Have at least 2 uncompleted entries (registration only)

(iv) 5 TEAM entries

● Have at least 2 teams with more than 2 members

● At least one team with the same name in different carnivals

Page 5 of 14

FACULTY OF

INFORMATION

TECHNOLOGY

In adding this data you must ensure that the test data thoroughly tests the model as

supplied, so as to ensure your schema is correct.

Your inserted data must conform to the following rules:

(i) You may treat all of the data that you add as a single transaction since you are

setting up the initial test state for the database.

(ii) The numeric primary key values for this data should be hardcoded values (i.e. NOT

make use of sequences) and must consist of values below 100.

(iii) The data added must be sensible eg. entry finish times should be after entry start

times with a sensible running duration.

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 rm-schema-insert.sql script.

____________________________________________________________

For all subsequent questions (Task 3 onwards) you are NOT permitted to:

● manually lookup a value in the database to obtain its primary key or the highest/lowest

value in a column,

● manually calculate values (including dates/times) external to the database, e.g. on a

calculator and then use such values in your answers. Any necessary calculations must

be carried out as part of your SQL code, or

● assume any particular 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 very small sample snapshot of a multiuser database, as such you must operate on the

basis that there will be more data in all of 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.

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).

PL/SQL may ONLY be used for Task 5.