Home Page > > Details

CS952 Database and Web Systems Developmen

 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.
Contact Us - Email:99515681@qq.com    WeChat:codinghelp
Programming Assignment Help!