联系方式

  • QQ:99515681
  • 邮箱:99515681@qq.com
  • 工作时间:8:00-23:00
  • 微信:codinghelp

您当前位置:首页 >> Database作业Database作业

日期:2020-11-15 08:33

ISAD251 (IMAT5030) Database Applications Development

Assignment 2020-21

Page 1 of 16

ASSIGNMENT - ORACLE & APEX APPLICATION DEVELOPMENT

Placement Application Tracking System (PATS)

Scenario

The second year computing students are strongly encouraged to undertake a

placement year as part of their studies. In addition to significantly improving

the student’s graduate employment prospects, there is strong evidence that

undertaking a placement year often leads to a higher degree classification.

There is a need to introduce a system, which will track student activity through

the application process from the start of Year 2 until a placement employment

is achieved.

The system must be able to handle students, companies and jobs and track the

interaction between all three.

The system should hold the following data about the student (mandatory data

indicated by *)

– *student record number (SRN)

– *name

– *programme1

– *date of birth – a student must be at least 18 years of age on 1st June

2020 to be able to undertake a placement

– term-time address2 comprising *flat number, floor number, building

name, street name, district, and area: Hong Kong Island, Kowloon,

New Territories, etc.

– home address comprising house (or flat) number or name - if different

from the term-time address

– *mobile telephone number

– residential telephone number

– *email address


1 A programme may have a mandatory placement year. See Appendix A for a set of data.

2 You might expect the system ultimately to be able to determine the remainder of any address using an

Address File – you must not implement this.


ISAD251 (IMAT5030) Database Applications Development

Assignment 2020-21

Page 2 of 16

– a note of any placement preferences (e.g. nature of the placement,

geographical location),

– date when a CV was submitted for approval

– date when the CV was approved3

– *username

A company may have one or more sites, e.g. HSBC at Central, West Kowloon,

etc. and may advertise placement opportunities at a number of locations.

Site data includes its address and area.

A job vacancy at a company site has a job title, a short job description, a

contact for enquiries (either an email address or a telephone number or both),

a salary, a start date for the placement (e.g. 1st October 2020), the default

time for which is 00:00, an application method (CV with covering letter,

application form, online application, etc.) and a closing date for applications4

(e.g. 31st August 2020), the default time for which is 23:59. The start date for

the placement and the closing date for applications must be on weekdays (i.e.

Monday to Friday).

A student may make an application for many jobs and a job may receive many

applications. Note that in order to apply for any job, a student must have

submitted a CV and have had it approved. Those students with an approved CV

are deemed to be active in the placement process and those without inactive.

For an application for a particular job, an application history is required

showing the application status (from the list below) over time and the date

and time of any change in status.

application submitted

application withdrawn

applicant invited for interview

applicant invited to assessment centre

applicant rejected

applicant offered position

applicant accepted offer

applicant declined offer


3 Note that you should not store actual CV document itself

4 You can assume that once posted on the system, jobs are open to applications.


ISAD251 (IMAT5030) Database Applications Development

Assignment 2020-21

Page 3 of 16

Tasks

Analyse the above data5 and implement the resulting tables in Oracle SQL

including as much sensible data integrity as possible. You are strongly

recommended to consider the use of sequences and triggers.

Design and implement TWO applications in Oracle Application Express (APEX):

Administrator’s web application

1. to enter and maintain data relating to students, companies, company

sites and job vacancies.

2. to browse/search current and past job vacancies.

3. to view a report on the status of a given student and his/her

application(s).

4. to provide management reports to show

? the company, site, job title, closing date and number of

applications made to date for vacancies with a closing date

within the next seven days

? the name, programme and email address of students without

an approved CV, i.e. inactive in the placement process

? the name, programme and email address of active students

who are not yet placed (i.e. who have not accepted an offer)

5. to display a management dashboard with

? a pie chart showing the proportion of placed to unplaced

active students

? a calendar of job vacancy closing dates


5 Those entities which are in bold in above scenario should give you a clue about the required

tables. You are encouraged to have your table design verified at an early stage to avoid

problems.


ISAD251 (IMAT5030) Database Applications Development

Assignment 2020-21

Page 4 of 16

Student’s mobile-friendly web application (for use on an iPad Mini for

example)

1. to view/browse/search current job vacancies

2. to allow a student to record applications made and maintain his/her

application(s) history

3. to allow a student to view a report on the status of his/her

application(s)

Employer’s web application

1. to browse/search student(s) who have been offered placement places

and view the name, programme, mobile phone number and email

address of the student(s).

Your applications should include appropriate validation, interface and

navigation features.

You will need to incorporate individual user logins – hence the inclusion of a

student username - but you are advised not to attempt this until the rest of

your application has a high level of functionality.

Individual Assignment

This is an individual assignment.

Deliverables

You should submit a zip file (.zip) named using your assigned Id (e.g.

ISAD01.zip) via the DLE containing THREE files only:

A single PDF document (in landscape orientation format6

) named using your Id

(e.g. ISAD01.pdf) with (in this order)

1. A list of any additional assumptions you have made which affect your

solution.

2. A sensibly sized and legible SQL Developer Data Modeler diagram

showing all of your tables and their relationships.


6 Given that much of your document will look better in landscape (SQL code and database

diagram in particular), you should use landscape throughout. This will save you the trouble of

adding section breaks.


ISAD251 (IMAT5030) Database Applications Development

Assignment 2020-21

Page 5 of 16

3. Your Oracle CREATE TABLE statements with any related object creation

statements (CREATE SEQUENCE, CREATE TRIGGER)

? You should ensure that your objects are presented in a logical order,

i.e. a table referenced by a foreign key (e.g. a lookup table) should

appear before the table with the foreign key reference.

? You should pay special attention to the completeness and

presentation of these statements as both SQL Developer and APEX

Object Browser can generate incomplete DDL output.

? You are strongly advised to refer to the checklist in Appendix B and

perform a self-check before submission.

? You must adhere to the presentation guidelines contained in

Appendix C. Do not underestimate the time needed to ensure good

presentation.

4. Listings of CREATE statements for any other database objects, e.g.

views, procedures and/or functions, you may have used.

5. A set of relevant and sensibly sized screenshots showing your application

in operation together with a written commentary where appropriate.

6. A critical appraisal of your solution highlighting worthy features,

together with any shortcomings and how they might be resolved.

Export files (???.ISAD01.sql) of each of your applications created using the

following settings:

Note that any part of your submission in an incorrect file format may not be

marked. Coursework may be submitted at any time ahead of the deadline

time. Please note the University regulations concerning the late submission

of coursework.

Submission deadline: 23:59, Thursday, 14th January 2021 via DLE

Note that database objects and applications may need to be accessed in your

absence as part of the development and assessment process. It is therefore

imperative that your solution is developed on Oracle Database 18c Express

Edition. Solutions developed elsewhere will receive a mark of zero.

You are required to perform a 15-minute defence of your solution during the

12th lesson on 14th January 2021. You must ensure that your tables are

populated with an adequate amount of sensible test data in advance of this

session and it is particularly important that date and time-dependent data is

applicable to the date and time of your solution defence.


ISAD251 (IMAT5030) Database Applications Development

Assignment 2020-21

Page 6 of 16

Assessment

The assignment assesses the following Assessed Learning Outcomes for the

module, viz. to

1. write effective SQL statements for defining, manipulating and

controlling data.

2. design and implement a multi-user database application

The mark for this assignment contributes 50% to the overall mark for the

module.

Marks allocation and assessment criteria

Deliverable Maximum

Marks

Database diagram and additional assumptions

To achieve full marks for this section, you must submit a legible ER

diagram showing all of your tables and their relationships.

Failure to do so will result in a mark of zero for this section.

5

Database objects

To achieve a mark of 40% or above in this section, you must show

how you have implemented all of your tables with appropriate

table and column names, data types and entity and referential

integrity.

To achieve a mark of 60% or above in this section, you must also

show how you have implemented a significant amount of data

integrity using a variety of methods.

To achieve a mark of 80% or above in this section, you must also

show how you have implemented complete data integrity using a

variety of methods.

Marks will be reduced for database objects (tables, views,

sequences, triggers, procedures and functions) being omitted, poor

presentation of code, constraints without explicitly assigned names

and deviation from the recommended naming convention.

30


ISAD251 (IMAT5030) Database Applications Development

Assignment 2020-21

Page 7 of 16

Critical appraisal

To achieve a mark of 40% or above in this section, you must provide

a superficial account of the problems areas and worthy features.

To achieve a mark of 60% or above in this section, you must provide

a substantial account of the problems areas and worthy features,

together with suggestions for improvement.

To achieve a mark of 80% or above in this section, you must provide

a complete account of the problems areas and worthy features,

together with suggestions for improvement and an indication as to

how these may be realised.

15

Quality of applications including solution defence

To achieve a mark of 40% or above in this section, you must be able

to demonstrate and document that your applications are able to

maintain and report on the history of applications.

To achieve a mark of 60% or above in this section, you must also be

able to demonstrate and document student, company, site and

vacancy maintenance using appropriate interface features and

validation and the provision of meaningful management reports and

of a management dashboard as specified above.

To achieve a mark of 80% or above in this section, you must also be

able to demonstrate and document the use of user roles in your

application.

Marks will be reduced for the use of insufficient and inappropriate

test data.

Marks will be increased where innovative features have been

incorporated.

50

TOTAL 100


ISAD251 (IMAT5030) Database Applications Development

Assignment 2020-21

Page 8 of 16

Health Warning

This is an assignment that cannot possibly be done at the last minute. It is

estimated that it may take up to 200 hours to complete and you will need to

work consistently from the release of the assignment until the deadline.

You must start early even if you do not yet have all the necessary knowledge

and skills to complete the assignment.

In the first instance, you should concentrate on implementing working

applications – you can add additional features later. You have the opportunity

to discuss how your solution can be improved in the critical appraisal.


ISAD251 (IMAT5030) Database Applications Development

Assignment 2020-21

Page 9 of 16

Appendix A

3384 BSc (Hons) Computer & Information Security

3429 BSc (Hons) Computer Science

0746 BSc (Hons) Computer Systems and Networks

2594 BSc (Hons) Computing

4230 BSc (Hons) Computing & Games Development

6007 BSc (Hons) Applied Computing with Professional Experience*

* mandatory placement year - others optional but recommended


ISAD251 (IMAT5030) Database Applications Development

Assignment 2020-21

Page 10 of 16

Appendix B - CREATE TABLE checklist

? Meaningful table and column names used

? Appropriate data types used

? Primary key defined

? Foreign key(s) defined if applicable

? Appropriate check constraints defined if applicable

? Appropriate not null constraints defined if applicable

? Appropriate unique constraints defined if applicable

? Column constraints defined where possible and table constraints reserved

for multiple column cases

? All constraints named (including NOT NULL constraints)

? Indentation, reserved word identification and non-proportional font used to

aid readability

? Consistent use of a naming convention


ISAD251 (IMAT5030) Database Applications Development

Assignment 2020-21

Page 11 of 16

Appendix C - Presenting SQL CREATE object statements

You must present your statements sensibly and in a logical order. Using the

client-titles schema as an example, it is important to present the titles table

first because it is referenced by the clients table (Title in clients is a foreign

key referencing the primary key in titles).

Use SQL Developer to capture your SQL code for a table by selecting the DDL

tab for the table, and copying and pasting the code into Word. You must not

include screenshots of your code.


ISAD251 (IMAT5030) Database Applications Development

Assignment 2020-21

Page 12 of 16

Format the paragraph in Word as follows:

Change to a non-proportional font (e.g. Lucida Console) and delete superfluous

content (greyed out below)

CREATE TABLE "ISAD251"."TITLES"

(

"TITLE" VARCHAR2(6 BYTE),

CONSTRAINT "TITLES_TITLE_CHK"

CHECK (title = INITCAP(title)) ENABLE,

CONSTRAINT "TITLE_PK" PRIMARY KEY ("TITLE") USING INDEX PCTFREE 10 INITRANS 2

MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1

MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL

DEFAULT) TABLESPACE "USERS" ENABLE

)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE

(

INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0

FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT

)

TABLESPACE "USERS" ;

To produce

CREATE TABLE "ISAD251"."TITLES"

(

"TITLE" VARCHAR2(6 BYTE),

CONSTRAINT "TITLES_TITLE_CHK"

CHECK (title = INITCAP(title)) ENABLE,

CONSTRAINT "TITLE_PK" PRIMARY KEY ("TITLE")

)

Tidy up formatting and indentation


ISAD251 (IMAT5030) Database Applications Development

Assignment 2020-21

Page 13 of 16

CREATE TABLE "ISAD251"."CLIENTS"

(

"CLIENT_ID" NUMBER,

"SURNAME" VARCHAR2(25 BYTE)

CONSTRAINT "CLIENTS_SURNAME_NN" NOT NULL ENABLE,

"FORENAME" VARCHAR2(25 BYTE)

CONSTRAINT "CLIENTS_FORENAME_NN" NOT NULL ENABLE,

"TITLE" VARCHAR2(6 BYTE),

"DOB" DATE

CONSTRAINT "CLIENTS_DOB_NN" NOT NULL ENABLE,

"PHONE_NO" VARCHAR2(20 BYTE),

CONSTRAINT "CLIENTS_SURNAME_CHK"

CHECK (surname = INITCAP(surname)) ENABLE,

CONSTRAINT "CLIENTS_FORENAME_CHK"

CHECK (forename = INITCAP(forename)) ENABLE,

CONSTRAINT "CLIENTS_PHONE_NO_CHK"

CHECK (REGEXP_LIKE(phone_no, '\(([[:digit:]]{5})\) ([[:digit:]]{6})')) ENABLE,

CONSTRAINT "CLIENTS_CLIENT_ID_PK"

PRIMARY KEY ("CLIENT_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT

1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"

ENABLE,

CONSTRAINT "CLIENTS_TITLE_FK"

FOREIGN KEY ("TITLE")

REFERENCES "ISAD251"."TITLES" ("TITLE") ENABLE

)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE

(

INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL

DEFAULT

)

TABLESPACE "USERS" ;


ISAD251 (IMAT5030) Database Applications Development

Assignment 2020-21

Page 14 of 16

In this case, as the trigger for the clients table uses a sequence, include the CREATE SEQUENCE statement before the

trigger statement.

CREATE SEQUENCE seq_client_id

NOCACHE;

CREATE OR REPLACE TRIGGER "ISAD251"."TRG_CLIENTS" BEFORE

INSERT OR

UPDATE OF dob, phone_no ON clients FOR EACH ROW

BEGIN

IF INSERTING THEN

IF :NEW.client_id IS NULL THEN

SELECT seq_client_id.nextval

INTO :NEW.client_id

FROM sys.dual;

END IF;

END IF;


:NEW.forename := INITCAP(:NEW.forename);

:NEW.surname := INITCAP(:NEW.surname);


/* replace any characters other than digits with an empty string */

:NEW.phone_no := REGEXP_REPLACE(:NEW.phone_no, '[^[:digit:]]', '');


/* adjust to (99999) 999999 format */

:new.phone_no := REGEXP_REPLACE(:NEW.phone_no, '([[:digit:]]{5})([[:digit:]]{6})', '(\1) \2');


IF NOT (MONTHS_BETWEEN(SYSDATE,:NEW.dob) >= 18*12) THEN

/* Issue error code (ORA-20000) and message */

RAISE_APPLICATION_ERROR(-20000, 'Client must be at least 18 years of age');

END IF;

END;

/

ALTER TRIGGER "ISAD251"."TRG_CLIENTS" ENABLE;


ISAD251 (IMAT5030) Database Applications Development

Assignment 2020-21

Page 15 of 16

Group a CREATE TABLE statement with its related object creation statement(s)

- SEQUENCE, TRIGGER - before moving on to the next CREATE TABLE

statement, e.g.

CREATE TABLE statement

CREATE SEQUENCE statement

CREATE TRIGGER statement

CREATE TABLE statement

CREATE SEQUENCE statement

CREATE TRIGGER statement

CREATE TABLE statement

CREATE SEQUENCE statement

CREATE TRIGGER statement

Rather than

CREATE TABLE statement

CREATE TABLE statement

CREATE TABLE statement

CREATE SEQUENCE statement

CREATE SEQUENCE statement

CREATE SEQUENCE statement

CREATE TRIGGER statement

CREATE TRIGGER statement

CREATE TRIGGER statement


ISAD251 (IMAT5030) Database Applications Development

Assignment 2020-21

Page 16 of 16

References

ORACLE APEX

https://apex.oracle.com/en/learn/

ORACLE DBMS

ORACLE SQL Keywords

CREATE Tables in SQL Developer

SQL Developer Data Types


相关文章

【上一篇】:到头了
【下一篇】:没有了

版权所有:编程辅导网 2018 All Rights Reserved 联系方式:QQ:99515681 电子信箱:99515681@qq.com
免责声明:本站部分内容从网络整理而来,只供参考!如有版权问题可联系本站删除。