IMAT5030讲解、辅导Database编程语言、辅导SQL程序
- 首页 >> Java编程 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
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