Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

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:

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

○ Type validation, is the data entered the correct type? For example, if the input is a surname, you would not expect numeric digits.

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

○ 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).

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

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