Course: COMP1556 Database Applications Technologies
Contribution: 100% of course
36: Database Applications Technologies – Term 2 – MAC
PDF file required – ZIP file also required
Due date: 22nd April 2016
This coursework will be marked anonymously
YOU MUST NOT PUT ANY INDICATION OF YOUR IDENTITY IN YOUR SUBMISSION
This coursework should take an average student who is up-to-date with tutorial work approximately 50 hours
Plagiarism is presenting somebody else’s work as your own. It includes: copying information directly from the Web or books without referencing the material; submitting joint coursework as an individual effort; copying another student’s coursework; stealing or buying coursework from someone else and submitting it as your own work. Suspected plagiarism will be investigated and if found to have occurred will be dealt with according to the procedures set down by the University.
All material copied or amended from any source (e.g. internet, books) must be referenced correctly according to the reference style you are using.
Your work will be submitted for electronic plagiarism checking. Any attempt to bypass our plagiarism detection systems will be treated as a severe Assessment Offence.
Coursework Submission Requirements
An electronic copy of your work for this coursework should be fully uploaded by midnight (local time) on the Deadline Date.
The last version you upload will be the one that is marked.
For this coursework you must submit a single Acrobat PDF document. In general, any text in the document must not be an image (ie must not be scanned) and would normally be generated from other documents (eg MS Office using “Save As .. PDF”).
There are limits on the file size. The current limits are displayed on the coursework submission page on the Intranet
Make sure that any files you upload are virus-free and not protected by a password or corrupted otherwise they will be treated as null submissions.
Comments on your work will be available from the Coursework page on the Intranet. The grade will be made available in the portal.
You must NOT submit a paper copy of this coursework.
This coursework should be completed as a pair (i.e. group of 2). You can however opt to work alone if you prefer. You must be ready to demonstrate your work as a pair to your tutor on the date specified on the header of this coursework or by your course coordinator.
The aim of this assignment is to undertake a range of tasks involved in the analysis, design and build of a database structure and applications for a database system for a Medical Recruitment Agency (MRA).
Where information is not available you should make reasonable assumptions. Make sure that you include all business constraints that have been captured during the analysis part.
A small recruitment agency specialises in contracting medical doctors for short temporary assignments. MRA’s task is to assign doctors to different hospitals who ask for locums (locums: doctors who provide temporary cover for absence of permanent medical doctors). A doctor must provide personal details when first registering including their specialities and availability. Doctors may specialise in more than one area and be available for a number of periods which can be represented by a starting and finishing date up to one year from the current date (e.g. 5th Jan 14 – 4th Jan 15; 8th Feb 14 – 7th June 14, etc).
Information about each hospital such as name, address, bed-capacity, specialities and payment-scale is also kept. A hospital can specialise in more than one area (e.g. Maternity, ENT, etc.).
Doctors are paid by the agency according to their grades. The grade is determined by the number of years of experience and the doctor qualifications (e.g. MD with six years on staff grade post is of Payment-Grade 4). The grade determination is outside the scope of this system. Each doctor’s grade is determined and annually updated by the Medical Council.
Hospitals pay the agency directly for each locum’s assignment. Payment for a doctor of one grade may vary from one hospital to another. The agency pays locums a fixed rate according to their assigned grade by the Medical Council regardless of which hospital they work for. All locums of similar specialities and the same grade at the same hospital are paid the same rate. The pay rate for a specific grade may differ from one hospital to another, but this difference will not change the amount that the agency pays a specific doctor when he works in various hospitals. So the agency can make more money from various assignments of the same doctor to different hospitals.
Hospitals send orders to the agency asking for locums in a specialised area for a specific period. After finding a suitable doctor who can cover, the agency confirms the availability by phone with the doctor and sends the hospital a confirmation letter for that assignment. For each assignment the agency keeps records as to which doctors are being assigned to which hospitals, as well as the period of assignment and speciality for this assignment. The profit from each assignment can be calculated by subtracting the total received hospital payment (hospital daily pay rate for the allocated locum times number of days) from the total agency payment due to that locum (the agency daily payment rate times the number of days). Payment history records for all doctors are to be kept in the system.
A database system is required to manage the various daily activities in MRA such as registering a new doctor, placement of doctors in hospitals, managing the financial sides of the business, etc.
The following describes the nature and main functionalities which are required by the MRA business.
The following database queries are required to be displayed on screen (i.e. produce the SQL code only, no form or report). All queries must be completed with each student from the pair completing 3 out of 6 queries. If you opt to work alone then you must complete all 6 queries alone.
For a given particular specialised field, list of names of all ‘available’ doctors.
Produce, on demand, a list of names of all doctors who have been assigned to a given hospital. (E.g. list the names of all doctors who have been working in Queen Mary Hospital). This application is to run on-line.
List all hospitals that are specialised in more than one area.
Produce, on demand, a list of names of all available doctors in a given specialised field within a given date (e.g. a list of all available ENT (Ear, Nose and Throat) specialists within the period 5th 12 – 3rd Nov 12). This application will be run batch on a weekly basis.
List the highest payments that a given doctor has received since he/she was registered with the agency. (E.g. what is the highest payment that Dr. Smith has received?) This application is to run on-line.
Produce, on demand, a list of names of all experienced doctors (i.e. those with not less than 6 years experience) who have been assigned to a given hospital. (E.g. list experienced doctors who have been working in Queen Elizabeth Hospital). This application is to run on-line.
Your electronic submission should include:
An uploaded report called DB_Report which includes the deliverables D1, D2, D3 and D5
ONE Zip file containing D5 and all SQL scripts for the sample queries (A1-A6). Name the file DB_Applications.
All deliverables are listed below:
D1. One A4 page containing the conceptual data model diagram (i.e. an Entity Relationship Diagram using consistently either Chen or UML notation showing:
Relevant entity types (parent and subclasses should be considered)
The primary key for each entity
Relationship types with role names (plus relationship attributes if any)
Structural constraints on each relationship (both cardinality and participation)
Note: if you show attributes other than the Primary Keys (e.g. Foreign Keys) on the conceptual model then you will be penalised.
D2. For the above model, produce the logical data model (the relational schema) (one A4 page). The model represents your mapping from the conceptual model into a relational schema and should show:
All entity and relationship types that are prospective relational tables.
For each prospective table identify the primary key and any necessary foreign keys.
D3. One A4 page containing a normalised set of relations with all attributes listed. On the same page, state clearly any assumptions that you make about the data, in particular, noting any information that you believe should be included, but is not mentioned in the outline specification.
D4. Create a database for the above schema in Oracle DBMS using SQL and populate each table with typical records to clearly demonstrate the application results. (Each student in pair must implement a copy of the database in their own schema and be ready to demonstrate the work to the tutor on request)
D5. The SQL code for the creation scripts and the population scripts used in D4. The SQL queries used to fulfil all applications A1-A6 and show the output results (i.e. screen dumps of your executed queries). Note: you should populate the database with sufficient data to clearly and unambiguously demonstrate all applications mentioned above.
D6. Include an evaluation of your work as an individual (Max 300 words). If you worked in a pair then you must also present work carried out by each member in the pair in the form of a table. This schedule of work of should be agreed by each member before including it in your report.
These extra requirements are not essential and students who do not attempt them will not lose any marks. However, you may gain a few marks if you want to try them and demonstrate any during the demonstration (i.e. viva).
D7. Using any appropriate DBMS tools that you are familiar with, create a simple form to enable an end-user to maintain doctors’ details within your database (i.e. Insert new Doctor, Delete an old one, or Change speciality, Grade, address, etc.).
D8. Produce a summary of profit report for a period between certain dates. The profit is calculated from what the agency is invoicing hospitals subtracting what doctors get paid (assume no other incurred expenses for simplicity). The dates should be captured as parameters at run time.
End of Extra Requirements:
If you do the extended requirements you do not need to upload the source files (i.e. the form and report) but just demonstrate them to your tutor.
Assessment will be graded using the following criteria:
A: (>=70 %) Extremely well developed and presented.
Each candidate is expected to demonstrate a detailed understanding of the process of DB analysis & design (Conceptual & Logical). A completely, optimised and effective implementation of the required SQL applications (i.e. the production of a normalised relational schema with assurance is in 3rdNF). Excellent use & utilisation of facilities available by the DBMS to provide system robustness (e.g. minimising input errors, validation routines, using SQL correctly, etc.) with a sound user interface.
B/C: (55-69%) Well developed and presented.
Work lacks detailed analysis of mapping process, normalisation, etc. Application requirements have been met but not in the most optimal ways (e.g. wrong mapping or placement of foreign keys). Some use of a [limited] range of DBMS facilities. User interface may not be totally robust.
C/D: (40-54%) Submission contains minimum information requested and/or bad presentation.
Work and submitted report lack detailed analysis and/or displays poor understanding. No evidence of complete or fully working application(s), etc. poor demonstration of the use of DBMS facilities. Please note that the following will apply for obtaining a minimum pass mark:
If the student completes only one fully operational application correctly and, has attempted most of the others then the maximum awarded mark is 40%.
If the student completes two or more fully operational SQL Applications correctly then cswk is marked normally with no capping.
E/F: (0-39%) Developed work does not meet the minimum standard required. This includes the following:
No evidence of understanding the purpose of the coursework.
No evidence of implementation having been performed on the DBMS regardless of the quality of the design.
Do not demonstrate the work to their tutor, or having very poor implementation (e.g. if the student is unable to produce a single fully operational application correctly).
Having very poor implementation. I.e. if the student built a DB but is unable to produce a single fully operational query application, then the maximum awarded mark is 25%.
Basic identification of main Entities / sub-classes
Identifying a proper unique identifier
Choice of attribute List:
Links between tables
Mapping Further Requirements
DB Creation & Population
SQL Application A1/D5
SQL Application A2/D5
SQL Application A3/D5
SQL Application A4/D5
SQL Application A5/D5
SQL Application A6/D5
Self or Pair Evaluation
Add Extra Features
If mark exceeds 100% due to bonus marks awarded, then mark will be capped back to 100%.
Our Service Charter
Excellent Quality / 100% Plagiarism-FreeWe employ a number of measures to ensure top quality essays. The papers go through a system of quality control prior to delivery. We run plagiarism checks on each paper to ensure that they will be 100% plagiarism-free. So, only clean copies hit customers’ emails. We also never resell the papers completed by our writers. So, once it is checked using a plagiarism checker, the paper will be unique. Speaking of the academic writing standards, we will stick to the assignment brief given by the customer and assign the perfect writer. By saying “the perfect writer” we mean the one having an academic degree in the customer’s study field and positive feedback from other customers.
Free RevisionsWe keep the quality bar of all papers high. But in case you need some extra brilliance to the paper, here’s what to do. First of all, you can choose a top writer. It means that we will assign an expert with a degree in your subject. And secondly, you can rely on our editing services. Our editors will revise your papers, checking whether or not they comply with high standards of academic writing. In addition, editing entails adjusting content if it’s off the topic, adding more sources, refining the language style, and making sure the referencing style is followed.
Confidentiality / 100% No DisclosureWe make sure that clients’ personal data remains confidential and is not exploited for any purposes beyond those related to our services. We only ask you to provide us with the information that is required to produce the paper according to your writing needs. Please note that the payment info is protected as well. Feel free to refer to the support team for more information about our payment methods. The fact that you used our service is kept secret due to the advanced security standards. So, you can be sure that no one will find out that you got a paper from our writing service.
Money Back GuaranteeIf the writer doesn’t address all the questions on your assignment brief or the delivered paper appears to be off the topic, you can ask for a refund. Or, if it is applicable, you can opt in for free revision within 14-30 days, depending on your paper’s length. The revision or refund request should be sent within 14 days after delivery. The customer gets 100% money-back in case they haven't downloaded the paper. All approved refunds will be returned to the customer’s credit card or Bonus Balance in a form of store credit. Take a note that we will send an extra compensation if the customers goes with a store credit.
24/7 Customer SupportWe have a support team working 24/7 ready to give your issue concerning the order their immediate attention. If you have any questions about the ordering process, communication with the writer, payment options, feel free to join live chat. Be sure to get a fast response. They can also give you the exact price quote, taking into account the timing, desired academic level of the paper, and the number of pages.