更新时间:2023-02-02 21:49:21
我认为学生 - 年级 - 作业模式应该没问题.您应该通过查询计算学生的成绩数,而不是在数据库中维护该信息.在概念层面(不是严格的 mysql),我会做这样的事情.
表学生(id int(主键)变量名...//其他学生属性(例如电话号码))表级(id int(主键)名称 varchar//例如A+"、B-"等浮点数//等级值以点为单位)表分配(id int(主键)name varchar//赋值名称student_id int (foreign key)//完成特定作业的学生Grade_id int (foreign key)//在特定作业中取得的成绩...//其他分配属性(例如截止日期))
I'm trying to create a database that stores the students grade for each homework assignment I want to be able to store the grade and how many times that student got a certin grade for example a student got an A+ 30 times
, for each student, as well as how many grades a student got. And how much a grade is worth in points for example an A is worth 4.3
.
So I was wondering what is the best way to build my MySQL database what do I need to add and what do I need to drop and how can I store numbers like 4.3
for example.
My database so far
CREATE TABLE grades (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
grade INT UNSIGNED NOT NULL,
student_work_id INT UNSIGNED NOT NULL,
student_id INT UNSIGNED NOT NULL,
date_created DATETIME NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE work (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
student_id INT UNSIGNED NOT NULL,
title TEXT NOT NULL,
content LONGTEXT NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS student (
id int(8) NOT NULL auto_increment,
student varchar(20) NOT NULL,
PRIMARY KEY (`id`)
)
example of output.
student | grade | count
1 A 10
1 C 2
1 F 4
2 B 20
2 B- 3
2 C+ 10
student | grade | points
1 A 4.3
2 B+ 3.3
3 B- 2.7
4 D+ 1.3
student | total grades
1 90
2 80
3 100
4 1
I think a student - grade - assignment schema would be ok. You should count the number of grades of a student with a query and not maintain that information in your database. At a conceptual level (not strict mysql), I would do something like this.
table student (
id int (primary key)
varchar name
... // other student attributes (e.g. phone number)
)
table grade (
id int (primary key)
name varchar // e.g. "A+", "B-", etc.
float points // grade value in points
)
table assignment (
id int (primary key)
name varchar // assignment name
student_id int (foreign key) // student that completed the particular assignment
grade_id int (foreign key) // grade achieved in particular assignment
... // other assignment attributes (e.g. due date)
)