CS952 Database and Web Systems Development
Assessment
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 during the classwork 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 manufacturing company system
A national manufacturing company of precision engine parts maintains a series of
factories throughout the UK at which it manufactures products for its customers.
Each factory has a unique factory number as well as an address and telephone
number. Each factory has a manager, who can be identified by her/his staff number.
Managers are employees of the manufacturing company. The company also
employs secretaries. Although secretaries are not assigned to factories, one
secretary is designated as the supervisor of the other secretaries. All secretaries
have staff numbers. A record is required of an employee's name and home
telephone number. In addition, for secretaries, the administrative grade is also
recorded. At any one time, a manager is never responsible for more than one factory
and some managers are not currently responsible for any factory.
Each factory has several vehicles that it uses. Each vehicle has a registration
number and details of the make and model also need to be recorded. When a
vehicle requires routine maintenance, this is carried out by one of the larger
factories. Such large factories have a status of 'regional centre', as opposed to other
factories that have a status of 'local centre'. A vehicle is always routinely maintained
by the same factory.
Each product is manufactured specifically for a single customer but may be
manufactured by any of the company’s factories. Details of the customer's identifier
and name are required. Each of the products manufactured by the company has a
unique product code. In addition, for each product there is a need to record its
product name and the total number of items (which may be zero) of that product held
by the company. Factories manufacture many different products.
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
structures 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
4.3 Physical Design
Physical database design is not necessary in this exercise and should not be carried
out.
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 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) List the names of secretaries and the names of the other secretaries they
manage. If an owner has not introduced any other owners, their name should
be listed in the result.
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 accessibility into account
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 table 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 drawn using the conventions
introduced in the lectures.
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%)
The solution must be typed (not hand written), although EER diagrams may be hand
drawn and scanned. (IMPORTANT do not submit poor quality photographs of EER
models. Ensure that images are submitted with the correct orientation). Items (i) to
(v) should be combined into a single document in the order shown and submitted
through the link on the class Myplace page. The document must not be zipped and
must not contain screen shots or other elements that cannot be processed by
Turnitin. If a Turnitin error (eg " Turnitin has returned an error with your submission:
Your submission must contain 20 words or more ") is returned when you upload the
document, it means that the PDF contains text as images. You must OCR (optical
character recognition) the text so that it can be processed by Turnitin. Failure to
follow these instructions may result in marks being deducted.
Item vii should be submitted through the same link in a separate document. All work
will be evaluated for originality by Turnitin similarity checking.
All work must be submitted by 5.00pm on Wednesday 15th April 2020.
This exercise is worth 25% or the overall marks for this module.