# PHPCourseWork20211122

**Repository Path**: su-miaomiao/phpcoursework20211122

## Basic Information

- **Project Name**: PHPCourseWork20211122
- **Description**: PHP course work
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2021-11-23
- **Last Updated**: 2021-11-26

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# The key requirements

• 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

• 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

# PHP Course Work Report

# Basic Env

| Env            | Version             | Extra                       |
| -------------- | ------------------- | --------------------------- |
| uniform server | Windows application | provide PHP/MySQL/Nginx Env |
| PHP            | 5.6                 | USE PDO                     |
| MySQL          | 5.7                 | Storage Data                |
| Nginx          | 1.15                | Web Server                  |

# Part A

> In this part, I will check the mock data (information source) and iterate out 1NF、2NF、3NF which will be my guidance to design database table.

## UNF

- The origin data is a big excel table like:

| Quiz Name | Quiz Author  | Quiz Available | Quiz Duration | Student ID | Student Name | Date of Attempt | score |
| --------- | ------------ | -------------- | ------------- | ---------- | ------------ | --------------- | ----- |
| SQL       | Peter Parker | Yes            | 60 minutes    | 44         | Duncan Hull  | 22/11/2020      | 20    |
| SQL       | Peter Parker | Yes            | 60 minutes    | 44         | Bill         | 22/11/2020      | 19    |
| SQL       | Peter Parker | Yes            | 60 minutes    | 44         | Marry        | 22/11/2020      | 30    |

- Quiz Question Record

| Quiz ID | Question ID | Question                | Question Key A | Question Key B | Question Key C | Question Key D |
| ------- | ----------- | ----------------------- | -------------- | -------------- | -------------- | -------------- |
| 34      | 1           | which SQL statement...  | SELECT         | OPEN           | GET            | EXTRA          |
| 34      | 2           | with SQL, how do you... | INSERT NEW     | INSERT INTO    | ADD NEW        | ADD RECORD     |

## 1NF

> Atomicity of each column

| Quiz ID | Quiz Name | Quiz Author  | Quiz Available | Quiz Duration | Student ID | Student Name | Date of Attempt | Questions                                    | score |
| ------- | --------- | ------------ | -------------- | ------------- | ---------- | ------------ | --------------- | -------------------------------------------- | ----- |
| 34      | SQL       | Peter Parker | Yes            | 60 minutes    | 44         | Duncan Hull  | 22/11/2020      | 1which SQL statement is used to extra... ... | 100   |

## 2NF

> A table only describes one thing (Meet 1NF)

### quiz inforamtion

| Quiz ID | Quiz Name | Quiz Author  | Quiz Available | Quiz Duration |
| ------- | --------- | ------------ | -------------- | ------------- |
| 34      | SQL       | Peter Parker | Yes            | 60 minutes    |

### quiz question item

| Quiz ID | Question ID | Question                             | Question Key                              |
| ------- | ----------- | ------------------------------------ | ----------------------------------------- |
| 34      | 1           | which SQL statement is used to extra | SELECT,OPEN,GET,EXTRA                     |
| 34      | 2           | with SQL, how do you select...       | INSERT NEW,INSERT INTO,ADD NEW,ADD RECORD |

### user information

| Student ID | Student Name |
| ---------- | ------------ |
| 44         | Duncan Hull  |

### quiz record

| Quiz ID | Quiz Name | Student ID | Student Name | Date of Attempt | score |
| ------- | --------- | ---------- | ------------ | --------------- | ----- |
| 34      | SQL       | 44         | Duncan Hull  | 22/11/2020      | 100   |

## 3NF

> Each column in the table is only directly related to the primary key and not indirectly related (Meet 2NF)

### quiz record (renew by 3NF)

| Quiz ID | Student ID | Date of Attempt | score |
| ------- | ---------- | --------------- | ----- |
| 34      | 44         | 22/11/2020      | 100   |

### quiz question item(renew by 3NF)

| Quiz ID | Question ID | Question                | Question Key A | Question Key B | Question Key C | Question Key D |
| ------- | ----------- | ----------------------- | -------------- | -------------- | -------------- | -------------- |
| 34      | 1           | which SQL statement...  | SELECT         | OPEN           | GET            | EXTRA          |
| 34      | 2           | with SQL, how do you... | INSERT NEW     | INSERT INTO    | ADD NEW        | ADD RECORD     |

# Part B

> In this part, I wrote the Relational Schema for the user、token and so on.

- User Relational Schema: (email, password, identity)

  - This table will storage student and staff's account, password will be stored in cipher text.

- Token Relational Schema: (userID, token, inuse)

  - This table keep user online, student or staff use token as a credentials to request secret information.

- Quiz Remove Record Relational Schema: (quiz_id, user_id)

  - This table will be auto filled by trigger.Table record quizID and operatorID.

- Quiz Relational Schema: (quiz name, userID, quiz available, quiz duration)

  - Storage Quiz basic information, include available status and duration.

- Quiz Question Item: (Question, answerA, answerB, answerC, answerD, correct answer, quizID)

  - Stroage the quiz questions and answer A、B、C、D and correct answer. One of the features of this system is automatic scoring, this system will automatically compare the answers submitted by the students with the correct answers and calculate the score.

- Quiz Taken Record: (quizID, userID, answer, score)

  - The student finish a quiz will storage the answer and score.

- Quiz Visit Record: (ip, url and param, visit time)

  - Record request log, auto record by this system.

# Part C

> In this part, I will following the part A and part B to specific table design work.

> table prefix: qz

## qz_user(user account)

| field name  | type(len)    | explanation                | extra                         |
| ----------- | ------------ | -------------------------- | ----------------------------- |
| id          | int(11)      | User ID(Staff ID / Stu ID) | key, unique, not null, auto++ |
| username    | varchar(255) | Account                    | Emial                         |
| password    | varchar(255) | Pwd                        |                               |
| identity    | int(11)      | Student or Staff           | Student:0,Staff:1             |
| create_time | datetime     |                            |                               |

```SQL
CREATE TABLE `qz_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'User ID',
  `username` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Email',
  `password` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Hash password',
  `identity` int(11) DEFAULT NULL COMMENT 'Student:0,Staff:1',
  `create_time` datetime DEFAULT NULL COMMENT 'create_time',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
```

## qz_quiz(quiz inforamtion)

| field name     | type(len)    | explanation              | extra                       |
| -------------- | ------------ | ------------------------ | --------------------------- |
| id             | int(11)      | Quiz ID                  | key, not null, auto++       |
| quiz_name      | varchar(255) | Quiz Name                |                             |
| user_id        | int(11)      | Quiz Author UserID       |                             |
| quiz_available | int(11)      | Available or Unavailable | Available: 0, Unavailable:1 |
| quiz_duration  | varchar(255) | Quiz Duration (min)      |                             |
| create_time    | datetime     |                          |                             |

```SQL
CREATE TABLE `qz_quiz` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Quiz ID',
  `quiz_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Quiz Name	',
  `user_id` int(11) DEFAULT NULL COMMENT 'Quiz Author UserID',
  `quiz_available` int(11) DEFAULT NULL COMMENT 'Available or Unavailable',
  `quiz_duration` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Quiz Duration',
  `create_time` datetime DEFAULT NULL COMMENT 'create_time',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE DEFINER=`root`@`localhost` TRIGGER dellog BEFORE DELETE ON qz_quiz FOR EACH Row
BEGIN
	INSERT INTO qz_del_record SET `quiz_id` = old.id, `user_id` = old.user_id, `create_time` = now();
END;
```

## qz_quiz_question_item(for quiz)

| field name     | type(len)    | explanation          | extra                   |
| -------------- | ------------ | -------------------- | ----------------------- |
| id             | int(11)      | Quiz Item ID         | key, not null, auto++   |
| item_key       | varchar(255) | Quiz Question        |                         |
| answer_a       | varchar(255) | Question Alternative |                         |
| answer_b       | varchar(255) | Question Alternative |                         |
| answer_c       | varchar(255) | Question Alternative |                         |
| answer_d       | varchar(255) | Question Alternative |                         |
| answer_d       | varchar(255) | Question Alternative |                         |
| correct_answer | varchar(255) | Correct Answer       | For Automatic scoring   |
| quiz_id        | int(11)      | Quiz ID              | foreign key(qz_quiz:id) |
| create_time    | datetime     |                      |                         |

```SQL
CREATE TABLE `qz_quiz_question_item` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Quiz Item ID',
  `item_key` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Quiz Question',
  `answer_a` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Question Alternative',
  `answer_b` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Question Alternative',
  `answer_c` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Question Alternative',
  `answer_d` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Question Alternative',
  `correct_answer` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'A/B/C/D',
  `quiz_id` int(11) DEFAULT NULL COMMENT 'Quiz ID',
  `create_time` datetime DEFAULT NULL COMMENT 'create_time',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
```

> Though by 3NF have been designed some table, but there still need some assistance tables

## qz_taken_record(score and answer of the quiz)

| field name  | type(len)    | explanation   | extra                      |
| ----------- | ------------ | ------------- | -------------------------- |
| id          | int(11)      | Quiz Taken ID | key, not null, auto++      |
| quiz_id     | int(11)      | Quiz ID       | foreign key(qz_quiz:id)    |
| user_id     | int(11)      | Stu ID        | foreign key(qz_user:id)    |
| answer      | varchar(255) | Answer List   | Such as [A,C,B,C,A, ...]   |
| score       | int(11)      | Quiz Score    | How many question is right |
| create_time | datetime     |               |                            |

```SQL
CREATE TABLE `qz_taken_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Quiz Taken ID',
  `quiz_id` int(11) DEFAULT NULL COMMENT 'Quiz ID',
  `user_id` int(11) DEFAULT NULL COMMENT 'Stu ID',
  `answer` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Answer Lists',
  `score` int(11) DEFAULT NULL COMMENT 'Quiz Score	',
  `create_time` datetime DEFAULT NULL COMMENT 'create_time',
  PRIMARY KEY (`id`),
  KEY `quiz_id` (`quiz_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
```

## qz_del_record(trigger auto fill)

| field name  | type(len) | explanation | extra                   |
| ----------- | --------- | ----------- | ----------------------- |
| id          | int(11)   | Quiz Del ID | key, not null, auto++   |
| quiz_id     | int(11)   | Quiz ID     | foreign key(qz_quiz:id) |
| user_id     | int(11)   | Opeartor ID | foreign key(qz_user:id) |
| create_time | datetime  |             |                         |

```SQL
CREATE TABLE `qz_del_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Quiz Del ID',
  `quiz_id` int(11) DEFAULT NULL COMMENT 'Quiz ID',
  `user_id` int(11) DEFAULT NULL COMMENT 'Opeartor ID',
  `create_time` datetime DEFAULT NULL COMMENT 'create_time',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
```

## qz_token

| field name  | type(len)    | explanation       | extra                   |
| ----------- | ------------ | ----------------- | ----------------------- |
| id          | int(11)      | Token ID          | key, not null, auto++   |
| user_id     | int(11)      | Stu ID / Staff ID | foreign key(qz_user:id) |
| token       | varchar(255) | Token             |                         |
| inuse       | int(11)      | Valid             | Valid:1, InValid: 0     |
| create_time | datetime     |                   |                         |

```SQL
CREATE TABLE `qz_token` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL COMMENT 'stu or staff id',
  `token` varchar(255) DEFAULT NULL,
  `inuse` int(11) unsigned DEFAULT NULL COMMENT 'valid:1 invalid:0',
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=188 DEFAULT CHARSET=utf8;
```

## qz_visit

| field name | type(len)    | explanation  | extra                 |
| ---------- | ------------ | ------------ | --------------------- |
| id         | int(11)      | Token ID     | key, not null, auto++ |
| ip         | varchar(255) | visitor ip   |                       |
| url        | varchar(255) | request url  |                       |
| time       | datetime     | request time |                       |

```SQL
CREATE TABLE `qz_visit` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `ip` varchar(255) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=320 DEFAULT CHARSET=utf8;
```

- qz_quiz:qz_quiz_question_item = 1:n
- qz_quiz:qz_taken_record = 1:n
- qz_taken_record:qz_taken_record_item = 1:n

# Part D

> In this part, implementation of the quiz system

## function list

> This function list is based on requirement analysis

- [x] 1.  Student or staff login
  - Student and staff use same login page, the system will automatically determine the identity of the logged-in user.
- [x] 2.  Student register
  - Should use email as username, and auto valid it.
- [x] 3.  Automatically scoring
  - Quiz system can automatically compare the answers submitted by the students with the correct answers and calculate the score.
- [x] 4.  For Staff: Quiz Management.
  - Create, update, delete quiz, set duration and so on.
- [x] 5.  For Staff: Quiz question Management.
  - Create, update, delete quiz question, set alternative and correct answer.
- [x] 6.  For Staff: View Quiz taken user and result
  - View a student quiz taken result.
- [x] 7.  For Student: View Available quizzes
  - Check quizzes list.
- [x] 8.  For Student: Take a available quiz
  - The student select a available quiz and take it.
- [x] 9.  For Student: View taken quizzes with score
  - Check the quiz result.

## API List

> Test Server prefix:
> http://192.168.1.103/vsCode/20211122PHPCourseWork/index.php

- Return Param

| field | explanation | type   | extra |
| ----- | ----------- | ------ | ----- |
| code  | status code | int    |       |
| msg   | message     | string |       |
| data  | token       | string |       |

### 01.register(注册)

- URL: /user/register/{username}/{password}
- Example: /user/register/admin@qq.com/mockpasswd

### 02.login(登录)

- URL: /user/login/{username}/{password}
- Example: /user/login/admin@qq.com/mockpasswd

### 03.get Quiz List(获取 Quiz 列表)

- URL: /user/getQuizList
- Example: /user/getQuizList

### 04.add new quiz(新增 Quiz)

- URL: /user/addQuiz/{quiz_name}/{quiz_available}/{quiz_duration}/{token}
- Example: /user/addQuiz/SQL/1/60

### 05.remove quiz(删除 Quiz, trigger 触发)

- URL: /user/removeQuiz/{quiz_id}/{token}
- Example: /user/removeQuiz/10/token

### 06.update quiz(更新 Quiz 信息)

- URL: /user/updateQuiz/{quiz_name}/{quiz_available}/{quiz_duration}/{quiz_id}/{token}
- Example: /user/updateQuiz/SQL/1/60/10/token

### 07.add new question(为某个 Quiz 新增一个问题)

- URL: /user/addQuestion/{question}/{optional1}/{optional2}/{optional3}/{optional4}/{correctAnswer}/{quiz_id}
- Example: /user/addQuestion/这是题目内容/选项 1/选项 2/选项 3/选项 4/正确答案题号/10

### 08.remove question(为某个 Quiz 删除一个问题)

- URL: /user/removeQuestion/{quiz_item_id}
- Example: /user/removeQuestion/1

### 09.update new question(为某个 Quiz 更新一个问题)

- URL: /user/updateQuestion/{question}/{optional1}/{optional2}/{optional3}/{optional4}/{correctAnswer}/{quiz_item_id}/{token}
- Example: /user/updateQuestion/这是题目内容/选项 1/选项 2/选项 3/选项 4/正确答案题号/1/token

### 10.view somebody's answer(查看某 Quiz 考生答案)

- URL: /user/getAnswer/{quiz_id}/{token}
- Example: /user/getAnswer/10/usertoken

### 11.give score for a quiz record(给某次 Quiz 打分)

- URL: /user/score/{taken_id}/{score}/{token}
- Example: /user/score/11/100/usertoken

### 12.view quiz taken user(查看某 Quiz 考生列表)

- URL: /user/getQuizTakenList/{quiz_id}/{token}
- Example: /user/getQuizTakenList/11/usertoken

### 13.get qusetion list by quiz_id(通过 quiz ID 查询其包含的题目)

- URL: /user/getQuentionList/{quiz_id}/{token}
- Example: /user/getQuentionList/11/usertoken

### 14.submit Quiz answer and auto scoring(提交 Quiz 自动打分)

- URL: /user/submitQuizAnswer/{quiz_id}/{answer}/{token}
- Example: /user/submitQuizAnswer/11/2-B,6-D,7-C,9-A,10-,11-B,12-C,16-A/usertoken

### 15.after login, get user information(查询用户信息)

- URL: /user/getUserInfo/{token}
- Example: /user/getUserInfo/token

### 16. get my quiz taken record by quiz id(用户查询自己做题记录及分数)

- URL: /user/getMyQuizTakenList/{quiz_id}/{token}
- Example: /user/getMyQuizTakenList/1/token

<!-- ### Template

- URL: /index.php/class/method
- Example: /index.php/class/method/param1/param2
- Param:

| 字段 | 说明 | 类型 | 备注 | 必填 | 方式 |
| ---- | ---- | ---- | ---- | ---- | ---- |
|      |      |      |      |      |      |
|      |      |      |      |      |      |
|      |      |      |      |      |      |

- Return Param

| 字段 | 说明 | 类型 | 备注 |
| ---- | ---- | ---- | ---- |
|      |      |      |      |
|      |      |      |      |
|      |      |      |      |

| 字段 | 说明 | 类型 | 备注 |
| ---- | ---- | ---- | ---- |
|      |      |      |      |
|      |      |      |      |
|      |      |      |      | -->

# Part E

> This part, create trigger and procedure

## trigger

```SQL
CREATE TRIGGER dellog BEFORE DELETE ON qz_quiz FOR EACH Row
BEGIN
	INSERT INTO qz_del_record SET `quiz_id` = old.id, `user_id` = old.user_id, `create_time` = now();
END;
```

## procedure

```SQL
create procedure show_less40_percent()
BEGIN
	SELECT * FROM qz_taken_record WHERE score <= 40;
END;
```

# Part F

> In this part, I will test the system and take running screenshot

> The system can automatic judgment the identity of staff or student, because the table has a column named 'identity'

## For Student

### Login

![](https://img.webpro.ltd/picbed/img/202111261417801.png)

### Register

![](https://img.webpro.ltd/picbed/img/202111261418740.png)

### Select a available quiz take it

![](https://img.webpro.ltd/picbed/img/202111261424638.png)

![](https://img.webpro.ltd/picbed/img/202111261421703.png)

### View score of already taken quiz

![](https://img.webpro.ltd/picbed/img/202111261422917.png)

## For Staff

### Login

![](https://img.webpro.ltd/picbed/img/202111261423430.png)

### Create/Update/Delete quiz

![](https://img.webpro.ltd/picbed/img/202111261427579.png)

![](https://img.webpro.ltd/picbed/img/202111261423540.png)

### Create/Update/Delete associated questions for a quiz

![](https://img.webpro.ltd/picbed/img/202111261425775.png)

![](https://img.webpro.ltd/picbed/img/202111261426240.png)

### Check the result of taken quiz

![](https://img.webpro.ltd/picbed/img/202111261427649.png)