关于前端:COMP23111-数据库设计开发

COMP23111 Databases Systems
Coursework 2
Introduction
The second coursework allows you to firstly normalise an information source to
inform the design of your database. Your design will be implemented using
MySQL. Then you are to create an application using PHP and MySQL to interact
with the database.
There is not enough information in the information source so you will have to make
assumptions after the normalisation stage, for example, in the information source
there is no indication of storing a score for the user that took the quiz but this should
be incorporated when you design and implement.
Part A: You are to take the information source (see next page) and normalise to 3NF
to create associated relations and the attributes within them.
Part B: Create a relational Schema that clearly identifies the relations, the attributes,
and any constraints.
Part C: Implement your design being mindful of the data types for your fields and
any referential actions for foreign keys to update or delete data from related tables.
Part D: Using PHP and MySQL create an application and front-end for your
database. The application should:
• Allow a user to register
• Allow a staff or student user to login in
• Allow a staff user to create, update or delete a quiz or associated questions
for that quiz
• Allow a user to select from the available quizzes and take that quiz (if the
quiz is set to available)
• Allow a user to view any quizzes already taken with their score for those
quizzes
Part E:
• Create a stored procedure that displays the student names and their scores
for the quizzes where they achieved less than 40%
• Create a trigger that will log the staff id, the quiz id and the current date
and time, when a staff user deletes a quiz
The Information Source
Below is a snippet of the information source for the normalisation section of the
report.
…
How to Structure the Report
Below we give a general structure for your report, however, this is just for guidance,
and you should structure your report as appropriate with headings and
subheadings.
Coverpage (your name, title of assessment, module code, date)
Table of Contents
Part A: Normalisation
Introduction to section
UNF
Paragraph or two explaining what you did for UNF
Then your UNF table
1NF
Paragraph or two explaining what you did for 1NF
Then your 1NF relations
2NF
Paragraph or two explaining what you did for 2NF
Then your 2NF relations
3NF
Paragraph or two explaining what you did for 3NF
Then your 3NF relations
Part B: Relational Schema
Introduction to section
Your Schema
Part C: Implementation
Introduction to section
MySQL Statements used to implement your schema
Part D: The Application
Introduction to section
User guide on how to use your application, you may wish to include screenshots.
Note: Although we would like you to attempt to make the user interface pleasing, you are
mostly graded in this section based on functionality and the interaction with the
database.
Part E: Stored Procedures and Triggers
Introduction to section
MySQL used to create the stored procedures and triggers
Submission
A zip file containing:
• The source files for the implementation (the php files)
• A backup of your database
• The report (in PDF)
Submission deadline is available on Blackboard
Grading/Marking
Part A: Normalisation [≈ 29%]
Appropriate detail and representation of UNF
Appropriate detail and representation of 1NF
Appropriate detail and representation of 2NF
Appropriate detail and representation of 3NF
Part B: Relational Schema [≈ 12%]
Well-presented Schema
Correctness of Schema
Identification of changes/assumptions made from original information
source, such as including the storing of score, password for the user, etc. It
is your design so you should include what you think is appropriate with
some justification.
Note: Do not go back and alter Part A based on assumptions here.
Part C: Implementation [≈ 15%]
Well-presented MySQL statements
Correctness of the MySQL statements
Part D: The Application [≈ 32%]
Functionality of Application for key areas required
Part E: Stored Procedures and Triggers [≈ 6%]
Correctness of Stored Procedure
Correctness of Trigger
Presentation [≈ 6%]
Presentation of the Report (well-structured with appropriate headings etc)
Questions & Answers
How are the GTAs going to run the coursework for marking?
• The GTAs will use the Virtual Machine to run the application. You should ensure
that you test the application in the Virtual Machine so that it runs as you expect.
Could I develop the applications with modern technologies like NodeJS, Flask, Java
Spring, GoLang, etc.
• No frameworks are to be used. The assessment scripts are designed to test you on
certain aspects on the course and any restrictions built into them is intentional.
You can, however, use Bootstrap framework for the front-end if you wish.
Are any marks awarded for validation of data? • It is imperative that data is entered correctly into a database. Validation should
be used to reduce the number of errors during the process of data input.
What types of validation should I use? • I would suggest you look at the different validation techniques, any data captured
by the user should be validated, as a guide we would expect: o Unique Values, a question last year was asked“if a user tries to register
with the same username as an existing user, should this be accounted for?”.
The reply: We would expect some validation here that will inform the user
that the username is already taken and give the user the option to try
another username.
o Type validation, is the data entered the correct type? For example, if the
input is a surname, you would not expect numeric digits. o Non-Null Values – if there are constraints of NOT NULL for any fields in
the database, then validation should ensure the user enters a value for any
fields that have this constraint.
o Range Check is often used for numeric fields. An example of where you
might use a range check is when a user sets the quiz duration when
configuring the quiz options. Invalid input might be a duration which is
less than 1 (less than one minute for a student to complete a quiz seems a
little unfair).
o Restricted Choice is used when the user can only enter a choice from a valid
list of choices, an obvious example might be asking the user which quiz
they wish to compete. They user only be able to enter a quiz that exists.
o Format is often the most useful validation check and has many applications.
You will have seen examples such as checking for valid postcodes, email
addresses, course codes etc. A format check is essentially checking a string
matches a specific pattern and often is implemented using regular
expression.
Should I use client-side or server-side validation?
• HTML 5 offers nice features for client-side validation (such as regular expression),
anything that HTML 5 doesn’t provide can normally be provided by JavaScript.
However, you should not assume the user is using HTML5 or JavaScript, so
server-side validation can also be used for these situations.
Coursework 2 asks for a backup of the database. Is this backup the .sql file that we get
when we export the database on phpMyAdmin?
• Yes, you need to include the database file. This can be achieved in phpMyAdmin
using the export feature, or using the mysql command mysqldump.
We need to implement the front-end for the student actually taking the quiz as well? –
This seems to be outside the scope of the course and can take some time (for example, if
you are not familiar with PHP)
• Yes, you also need to implement a front-end for the user taking a quiz. I don’t
agree that it is outside the scope of the course since it involves SELECTing data,
INSERTing data (i.e. their answers/score) and UPDATing data (i.e. if your design
only stores the last attempt of a given quiz).
Wanted to ask if the 3NF form and relational schema should be the same as the one which
we will use to implement the database in the application. By this I mean, if we want to
change/add something into the database for the app, that we did not deduce from the
information source, should we also change the 3NF and Relational Schema.
• When you implement the database, it will be different from the relational scheme
since the information source does not capture everything.
• There is not the requirement to redo the normalisation in part A based on the
actual implemented database.
Can a member of staff edit another member of staff’s quiz? Or is it the case that when a
member of staff logs in, they can only view and edit the quizzes they have specifically
created.
• This is entirely your choice. You could add the functionality to allow the staff
member creating the quiz the option to allow or not allow other staff to change the
quiz (although this is not a requirement).
Are we allowed to make assumptions before starting the normalisation process? For
example, are we allowed to add our own attributes to the information source, other than
the existing ones?
• Better to normalise what is in the given information source.
Could you please clarify if we will be marked on the appearance of the website, or you
will mark the functionality of the website?
• The user interface does not have to look amazing. We are interested in the
interaction with the database; however, why not apply a little CSS? There is a little
credit given for an attempt to make the interface a little better, not much though –
focus your efforts on the functionality, make it look nicer if you have time.
I would consider 3NF to be a subset of 2NF, 2NF of 1NF, etc. If I can just come up with
the 3NF version off the bat, do I have to then backtrack and make it worse for the sake of
the earlier sections or can this count as all of them?
• You need to show your understanding of each normalisation stage from UNF to
3NF.
Can we use html/css to create our application login page etc for our application? While
using php to add/remove/extract data from database?
• Yes, this is what is expected. You could also use some JavaScript if you wanted.