CS952 Database and Web Systems Development Assessment Classwork
THIS IS AN INDIVIDUAL TASK AND MUST BE ACCOMPLISHED
WITHOUT COLLABORATION, COLLUSION OR THE SHARING OF
SOLUTIONS. FAILURE TO FOLLOW THIS INSTRUCTION WILL
RESULT IN DISCIPLINARY ACTION BEING TAKEN.
- Aim Of The Assignment:
Your task for this assignment is to design and construct a database and a set of web
pages that can be used to recover and display some of the data. - Task:
Read the following description of a data model. From the specification, produce the
following:
An enhanced entity relationship model.
A list of table structures produced by logical design based on the above
enhanced entity relationship model.
The SQL code to construct and populate only sufficient tables to carry out
the query part of the exercise. The database can be built using either Oracle
or MySQL.
A set of web pages that execute the queries and display the results. - Database specification:
The Hospital inpatient system
An in-patient in a hospital is identified by a patient record number and details
of his/her name, address, date of birth and GP are recorded. Patients are assigned to
a single ward in the hospital. Each ward is identified by the ward number and also
has a name. Some wards may have no patients assigned to them. Each ward is
staffed by at least one nurse. Within a ward a designated nurse is in charge of the
ward and of the other nurses in the ward.
Every patient in the hospital is allocated to one of the consultants working in the
hospital. Each consultant has a recorded specialism and heads a team of personnel
consisting of junior doctors who may be either registrars or housemen. All doctors
in the hospital (i.e. both junior doctors and consultants) have staff numbers and their
names are also recorded. The teams in the hospital are identified by a team code,
and the number of doctors in each team is recorded.
Doctors in the hospital treat many patients. Each patient may be treated by many
doctors who are always members of the same team as the consultant responsible for
that particular patient. During treatment one or more drugs are prescribed for
a patient. Drugs are identified by name and also by code number. The date, dosage
and prescribing doctor are recorded for each drug. Doctors are the only personnel
who are allowed to prescribe drugs. - Process:
4.1 Enhanced entity relationship model
The first step in this process is to construct an enhanced entity relationship diagram.
Read the above specification and write down a list of the entities and attributes. For
each entity indicate the identifier and write a sentence to describe the significance of
the entity. Indicate any supertype/subtype hierarchies. Make a note of necessary
assumptions. Draw an enhanced entity relationship diagram.
4.2 Logical design
Table structures should be written down in the following format:
TABLE_NAME(Primary-key-attribute, Non-key-attribute1, Non-key-attribute2, …).
Using the enhanced entity relationship model from Section 4.1, write down a table
structure for each entity taking care that:
Each attribute becomes a column.
The unique identifier becomes the primary key and is indicated by
underlining.
Subtype/supertype entities are represented in one of three methods described
in the lectures.
Use Table CW-1 as a guide to the way of representing the relationships between
entities. Write down table structures or modify existing structures to represent
relationships in the system. - : 1 1 : N N : M
Obligatory on
neither
New table to
represent
relationship Post
identifiers as
candidate keys
New table to
represent
relationship Post
identifiers as
candidate keys
New table to
represent
relationship Post
identifiers as
candidate keys
Obligatory on
one
Post identifier of
non-obligatory to
obligatory table
New table to
represent
relationship Post
identifiers as
candidate keys
–
Obligatory on
many
–
Post identifier of
“one” table to
“many” table
New table to
represent
relationship Post
identifiers as
candidate keys
Obligatory on
both
Post all attribute
into one table
Post identifier of
“one” table to
“many” table
New table to
represent
relationship Post
identifiers as
candidate keys
Table CW-1: Representing relationships in tables.
4.3 Physical Design
Physical database design is generally specific to the database management system
that is to be used and the performance requirements of the system. In both Oracle
and MySQL the chief tool is the creation of indexes, etc. For the purposes of this
exercise it is sufficient to create the necessary indices. It is not necessary to optimise
the database structure by merging entities etc. Make a note of the attributes from
Section 4.2 that will require indexes. You would normally use an index for
attributes that are involved in joins or which are the subject of an SQL‘where’
clause. Both Oracle and MySQL automatically create indexes for primary keys but
you must identify these using appropriate constraints. You will also need to note
foreign key attributes for index creation as indices.
4.4 Creating and loading the database
Implement your design in Oracle or MySQL on the Departmental devweb server.
Use appropriate integrity constraints. Populate each table with a limited set of data,
i.e. only enough to show that the queries work.
4.5 Querying the database
You now need to write some queries on your database. The queries must be useful
queries and not artificially constructed simply to fulfil the criteria listed. All queries
require a WHERE clause of the form‘…WHERE ATTRIBUTE = Value…’to limit
the rows returned (Value can be a text, numeric, or date value). Write four separate
SQL statements that will:
(i) carry out a join between two tables, use a function such as SUM, COUNT etc.
and a group by clause.
(ii) execute a sub-query. The query must be contained in the WHERE clause and
involve a function as above.
(iii) execute a correlated-query. The correlated query must follow the pattern shown
in Lecture 7, Slide 9.
(iv) carry out a self join that uses primary key/foreign key attributes. The self-join
may involve an intermediate table.
For MySQL, output can be saved to files using PHPMyAdmin. The output of Oracle
SQL queries can be captured in a file by typing:
spool outfile
at the SQL prompt. All screen output is then copied to a file with the name
outfile.lst. The spooling can be stopped by typing:
spool off
at the SQL prompt
Do not submit screen images of SQL commands or results.
4.6 Web Page Front End
Using HTML, CSS, PHP and JavaScript as appropriate, design a website that has
five main pages: one main page with links to four other pages to support your four
queries from above. Each of these query pages should ask the user for required data
and then when submit is hit, present the results in a nicely formatted table. There
should be a common look and feel (i.e. consistent appearance and placement of
content) across all pages and every page should include a suitable company logo.
You should enable users to input data securely, and take into account accessibility
when designing your web site. - Submission
Your submission should consist of the following.
i) Your final EER model solution showing:
a) A list of the entities and the meaning of each entity.
b) A list of attributes for each entity showing the identifier for the entity.
c) A list of the relationships giving for each: a relationship name, the names of
the entities related, the degree of the relationship and the optionality of the
relationship.
d) An enhanced entity relationship diagram.
e) A list of any assumptions you have made. (10%)
ii) A list of the table structures produced by logical design showing the attributes
and primary keys. (10 %)
iii) The SQL create statements (including the specification of integrity constraints)
for creating enough tables to carry out the Web systems part of the exercise. (5%)
iv) The SQL insert statements for populating the tables with a small sample of data.
(5%)
v) The SQL queries listed in Section 4.5 together with a narrative explanation of
each query (do not paraphrase the SQL commands) and its output. (10%)
vi) A 500 word critique of your database and web page structure, highlighting the
strengths and weaknesses of both aspects of your solution and giving reasons for
decisions that you have taken in the design and implementation. (15%)
vii) A zip file containing all the source files and a link to the main web page front
end on devweb as described in Section 4.6. (45%)
Items (i) to (vi) should be combined into a single pdf document and submitted
through the link on the class Myplace page. The document must not be zipped and
will be submitted to Turnitin plagiarism detector.
Item vii should be submitted through the same link in a separate document. All
work will be evaluated for originality.
Submission must be submitted by 12.00 noon on Monday 1st April 2019. Note that
this project can be submitted as late as 12:00 noon, Friday April 12th, 2019 (at the
end of the Spring vacation) without a late penalty being applied.
This exercise is worth 25% or the overall marks for this module
VX: codinghelp