CS952留学生代做、代写Web Systems、SQL程序调试、SQL代做

日期：2019-04-08 10:30

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.

1. 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.

2. 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.

3. 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.

4. 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 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.

5. 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