GROUP - PROJECT (Coursework)
This training is assessed by a single group project, based around designing and implementing web based management system, which is based on ORACLE Relational Database (ORACLE-RDB). Some group will submit their design and DDL of the ORACLE-RDB schema and develop a working web site based on their design. Details of these components of assessment are given below. The weighting of this project is as follows:
Database Management System (ORACLE Plus PHP/Developer 6i)
• System design and the implementation of the ORACLE-RDB Schema (source code for the database design + DDL table creation + DML insert statements + Queries and their results) 50%
• Implementing a functioning web site based on a ORACLE-RDB schema 30%
• 15 Minutes demonstration of the web site 20%
project aims: Assess the team’s ability to implement a ORACLE relational database from a problem specification, demonstrate awareness of the referential integrity requirements of the training and allow database access via the web.
Resources required: ORACLE; Developer 6i; PHP; HTML
Deadline for submission: May 22nd, 2011
Problem Statement:
Consider a hospital where patients are treated in a single ward by the doctors assigned to them. Usually each patient will be assigned a single doctor, but in rare cases they will have two. Healthcare assistants
also attend to the patients; a number of these are associated with each ward. Initially the system will be concerned solely with drug treatment. Each patient is required to take a variety of drugs a certain number of times per day and for varying lengths of time.
The system must record details concerning patient treatment and staff payment. Some staff is paid part time and doctors and care assistants work varying amounts of overtime at varying rates (subject to grade).
The system will also need to track what treatments are required for which patients and when and it should be capable of calculating the cost of treatment per week for each patient (though it is currently unclear to what use this information will be put).
Requirements:
1. Design an Entity Relation Model (ER) for the problem described above that shows entities, attributes, keys, relationships, mapping cardinalities, participation constraints, etc. state clearly the assumptions that you made and which justify your modelling choices.
2. Convert your ER into an ORACLE data model. This should include tables with their appropriate keys (primary, candidate, etc), and show the appropriate DDL statement for each table creation (Use the desc <table_name> SQL command).
3. Design a data that are suitable for the queries listed below, state why you have chosen such data and include any assumption you have made. Also insert the data into the tables (more than one record for each table) and show the content of each table using the SQL command: Select * from <table_name>.
4. Perform the following queries on your ORACLE relational database schema :
• List all doctors who treat patient ‘John Smith’
• List health assistants for each ward.
• Display the number of patients for doctors who are pediatricians
• List all treatments for patient ‘Ahmad Jayousi’
• Display patients who have taken ‘Panadol’
• List the number of staff who are part time as well as the number of doctor staff who are full time and treated a patient multiple times.
5. Using a combination of HTML on the client side and HTML, PHP and Oracle on the server side, create separate web applications with this database as follows:
• Insert a new patient and doctors
• Update and delete any information related to patients or doctors
• Query about a patient using any field in its table as well as the doctor number
• Insert, query, update and delete a treatment
• Exit and save buttons
Note: Please ensure that your web site application is running on your web space.
Presentation:
The group will present their functioning web site to the trainer and other group members. The tutor will assess the merits of the web site and the ORACLE RDB and he may ask you a few questions. The presentation weighting is 20% of the project.
Assignment submission
What to submit (soft and hard copies):
ER diagram and the logical data design (mapping ER to tables, final relational database).
Spooled ORACLE listings to show the command files used to create the tables,
together with spooled listings of the table structures (use the DESCRIBE
command).
Spooled ORACLE listings to show the command files used to insert your data into the tables, (use the INSERT command).
Spooled ORACLE listings to show the command file used in the implementation of
the constraints, plus listings to show the query for each problem indicated in question four and the result of that queryارجو المساعدة انا حلية اربع اسئلة اما سئال الخامس ماعرفت احلو لانه طالب مني ارفع قاعد البيانات على web بستخدام HTML او PHP ارجو المساعدة شكرا لكم