how to write embedded SQL within domain specific languages Skills Be able to construct embedded SQL statements to enable database event handling through the use of advanced concepts such as triggers.

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

This assignment contributes 60% to your final module mark.

The following learning outcomes will be assessed:

Knowledge

2. Understanding of how to write embedded SQL within domain specific languages

Skills

4. Be able to construct embedded SQL statements to enable database event handling through the use of advanced concepts such as triggers

Important Information

You are required to submit your work within the bounds of the University Infringement of Assessment Regulations (see your Programme Guide). Plagiarism, paraphrasing and downloading large amounts of information from external sources, will not be tolerated and will be dealt with severely. Although you should make full use of any source material, which would normally be an occasional sentence and/or paragraph (referenced) followed by your own critical analysis/evaluation.  You will receive no marks for work that is not your own. Your work may be subject to checks for originality which can include use of an electronic plagiarism detection service.

Where you are asked to submit an individual piece of work, the work must be entirely your own. The safety of your assessments is your responsibility. You must not permit another student access to your work.

Where referencing is required, unless otherwise stated, the Harvard referencing system must be used (see your Programme Guide).

Please ensure that you retain a duplicate of your assignment. We are required to send samples of student work to the external examiners for moderation purposes. It will also safeguard in the unlikely event of your work going astray.

Submission Date and Time	As advised on Canvas
Submission Location	Electronic submission Via Canvas

Introduction

Harvey’s Clinic is a local Health and Wellbeing clinic which has been in business for over five years. Based in Washington (Tyne and Wear), it has a good client base that continues to grow each year, and is made up of regular clients along with visitors. They currently have a paper-based system for recording all appointments and client details. In order to cope with their growth and allow for more efficiency, the clinic has decided to computerise their client and appointment recording system. You have been tasked with developing a database application to meet their needs.

 

Current Position

 

The clinic has five full and part-time therapists for whom they keep details (name, address, date of birth, telephone number, National Insurance Number, speciality, e.g. physiotherapy, acupuncture, etc.). Clients can make an appointment with any therapist by either calling into the clinic or telephoning for an appointment. The name, address and contact number are held for all clients who make an appointment. The appointment date and time is recorded against the requested therapist for that client. Appointments can be made between 8.30 am and 5.00 pm Monday to Friday and 9 am to 12.30 pm Saturday.

 

There are a number of therapy treatments available at the clinic which fall into the following categories:

 

• Physiotherapy;

• Osteopathy;

• Acupuncture;

• Chiropody;

• Podiatry;

•  

 

Clients can book in for an appointment for only one treatment per appointment.

 

PROPOSED SYSTEM

 

Using the Oracle 12c Relational Database Management System you are required to design and develop a prototype system that not only satisfies the requirements of the current system, but also has features that you consider to be worthwhile enhancements to the current system. To achieve this, you must:

 

a.) Using an Entity-Relationship (E-R) diagram, produce a design of the proposed system, correctly showing labelled relationships with optionality and cardinality constraints clearly indicated. You do not need to show attributes on the diagram, only entity names. Ensure that you state clearly any assumptions that you have made in creating your Entity-Relationship Diagram.

 

b.) Using a data dictionary, specify a set of tables and appropriate attributes for your design from part (a) above. For each table, your data dictionary must specify:

 

1. table name;

2. for each attribute, its name, description and data type (using Oracle data types used in the SQL booklet for this module);

3. primary key and any foreign keys (ensure you specify which table each foreign key relates to);

4. any further constraints on the data (e.g. business constraints on data values and dates).

 

c.) Subsequently, produce a single SQL script file which can be run within Oracle APEX without error and which drops (where necessary) and creates your tables (correctly ensuring that any referential integrity issues can be resolved), and inserts sample data into each table. The SQL script file must also contain the code for the PL/SQL transactions that you implement in (d) below.

 

d.) Using Oracle PL/SQL develop a set of transactions (i.e. using a combination of stored procedures, stored functions, triggers and cursors) based on your design for the proposed prototype system. Each transaction needs to include one stored procedure and any other function, triggers and cursors that you think necessary to fully implement the transaction. The transactions you are required to develop are:

 

1. A transaction which allows for registration of new customers.

2. A transaction which allows an existing customer to book an appointment. This transaction must allow the client to specify a specific treatment and therapist and appointment booking date and time, ensuring that the

 

appointment can only be booked if a treatment matches that therapist’s specialism, etc., and the appointment slot is available.

• A transaction which allows the clinic to produce a report showing all booked appointments between two dates for a specific

 

When developing the system you should take into account the important development issues identified below:

 

General Development Issues:

 

The Management wishes their system to be extremely user-friendly. Therefore, you should consider the following points:

• Appropriate use of messages, both error and informative, as well as user prompts should be employed throughout.

• Automatic generation of record numbers (e.g. customer numbers) where appropriate should be used in your

On a more technical level:

 

• Data types used should match those used in the tutorial

• Dates and other relevant data should be validated

• Exception Handling must be in place to deal with all errors, g. invalid dates, duplicate appointment times, incorrect therapist specified, etc..

• Any fields that require mandatory input, e. NOT NULL must be validated on input.

 

This is an individual assignment. There are two parts to this assignment:

 

PART A

For this part of the assignment you are required to hand in your design documentation and an SQL script file which includes the following:

 

• Copies of appropriate design documents. For example, your E-R diagrams and data dictionary.

• An SQL script file which can be run without error and:

  1. creates your tables (correctly ensuring that any referential integrity issues can be resolved).

  2. inserts sample data into every table;

• creates the PL/SQL transactions described in part (d) above; and

1. includes PL/SQL anonymous blocks which call your PL/SQL stored procedures supplying sample test

 

You must not use any automated code (e.g. table definition) facilities of Oracle APEX or any other tool that you choose to use. Use of any tools such as these will result in 0 marks for each affected element.

 

PART B

You are required to prepare and submit a short investigative report on the use of Embedded SQL (maximum of 500 words) with specific reference to a programming language of your choice (e.g. C#, Java, PHP, etc.). It is expected that you will give an example of how you would embody at least one of your developed transactions from Part A. References should be used appropriately in your discussion.

 

The whole assignment must be submitted within one ZIP file. The ZIP file should only contain two files:

1. a PDF file containing your design documentation from Part A and your 500 word report from part B. These must all be included in one Word processed (e.g. MS Word, Apple Pages) document and converted to PDF for

2. your SQL file (with a .sql extension) containing all SQL and PL/SQL code required from part

The mark scheme and mark breakdown is shown on the following page.

 

Attachments:

Expert's Answer

889 Times Downloaded

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of