代写COMP23111、代写SQL/Java编程

日期：2021-12-04 04:55

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.