Coursework 2: Database Design and Implementation with PHP and MySQL

Part A: Normalization

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.

1. Allow a user to register

2. Allow a staff or student user to login in

3. Allow a staff user to create, update or delete a quiz or associated questions for that quiz

4. Allow a user to select from the available quizzes and take that quiz (if thequiz is set to available)

5. Allow a user to view any quizzes already taken with their score for those

 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

Below is a snippet of the information source for the normalisation section of the report.

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

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

3. Are any marks awarded for validation of data?

Part B: Relational Schema

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.

4. 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:

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

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

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

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

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

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

11. Should I use client-side or server-side validation?

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

13. 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?

Part C: Implementation

14. Yes, you need to include the database file. This can be achieved in phpMyAdmin using the export feature or using the MySQL command MySQL dump.

15. We need to implement the front-end for the student 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)

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

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

18. When you implement the database, it will be different from the relational scheme since the information source does not capture everything.

 

19. There is not the requirement to redo the normalisation in part A based on the actual implemented database.

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

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

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

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

24. I would consider 3NF to be a subset of 2NF, 2NF of 1NF, etc. If I can just come up with the 3NF version of 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.

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