Student Management System
Administrator Registration/Login/Logoff
Register class (class details)
Register Student Information
View class Information/view teacher Information
Teacher Registration/deregistration View Teacher Profile View student profile by name/class/view student details-support Blur
Registered Account
Administrator (Admini)
AD_ID (Administrator ID) ad_number (Administrator account)
Ad_pass (Administrator password) ad_yn (admin disabled)
CREATE TABLE Admini (
ad_id int PRIMARY key auto_increment,--primary key self-increment, non-null unique
Ad_number int unique NOT NULL,--non-null unique
Ad_pass int NOT null,--non-null
Ad_yn varchar Check (ad_yn= ' y ' or ad_yn= ' n ')--limit only to ' Y ' or ' n '
)
Class (classes)
CL_ID (class ID) cl_adress (class address)
CREATE TABLE Classes (
cl_id int PRIMARY key,--primary key
Cl_adrss varchar (+) not null--non-empty
)
Teacher (teacher)
TE_ID (Teacher id) te_name (teacher name)
Te_subject (Teacher's subject) Te_yn (whether or not to leave)
CREATE TABLE Teacher (
te_id int PRIMARY key auto_increment,--primary key self-increment, non-null unique
Te_name varchar () NOT NULL,--non-null
Te_subject varchar () NOT NULL,--non-null
Te_yn varchar Check (te_yn= ' y ' or te_yn= ' n ')--limit only to ' Y ' or ' n '
)
Teacher Class Association table (Cla_teacher)
CT_ID (association table) cl_id (class _id) te_id (teacher _id)
CREATE TABLE Cla_teacher (
ct_id int PRIMARY key auto_increment,--primary key self-increment, non-null unique
cl_id int,
te_id int
)
--Add Class foreign key
ALTER TABLE cla_teacher ADD CONSTRAINT cl_fk1 FOREIGN KEY (cl_id) REFERENCES classes (cl_id)
--ALTER Table Main Table name ADD CONSTRAINT foreign key name FOREIGN key (primary table as column name for foreign key) REFERENCES from table name (from table column name)
--Add teacher foreign key
ALTER TABLE cla_teacher ADD CONSTRAINT te_fk1 FOREIGN KEY (te_id) REFERENCES teacher (te_id)
--ALTER Table Main Table name ADD CONSTRAINT foreign key name FOREIGN key (primary table as column name for foreign key) REFERENCES from table name (from table column name)
Student (student)
STU_ID (Student ID) stu_name (student name)
Stu_age (student Age) Stu_gender (Student Sex) stu_classes (Student Class)
CREATE TABLE Student (
stu_id int PRIMARY key auto_increment,--primary key self-increment, non-null unique
Stu_name varchar () NOT NULL,--non-null
Stu_age int,
Stu_gender varchar Check (stu_gender= ' male ' or stu_gender= ' female '),--limit only for ' male ' or ' female ' stu_classes int
)
--Add Class foreign key
ALTER TABLE student ADD CONSTRAINT stucl_fk1 FOREIGN KEY (stu_classes) REFERENCES classes (cl_id)
--ALTER Table Main Table name ADD CONSTRAINT foreign key name FOREIGN key (primary table as column name for foreign key) REFERENCES from table name (from table column name)
Chart of accounts (subject)
SU_ID (section Purpose ID) su_name (account name) Su_teacher (subject teacher)
CREATE TABLE Subject (
su_id int PRIMARY key auto_increment,--primary key self-increment, non-null unique
Su_name varchar (a) unique NOT null,--non-null unique
Su_teacher int not null--non-null
)
--Add teacher foreign key
ALTER TABLE subject ADD CONSTRAINT sute_fk1 FOREIGN KEY (su_teacher) REFERENCES teacher (te_id)
--ALTER Table Main Table name ADD CONSTRAINT foreign key name FOREIGN key (primary table as column name for foreign key) REFERENCES from table name (from table column name)
Student Account Association table (STU_SU)
SS_ID (Account association table ID) stu_id (student ID) su_id (account ID)
CREATE TABLE Stu_su (
ss_id int PRIMARY key auto_increment,--primary key self-increment, non-null unique
stu_id int,
su_id int
)
--Add student foreign key
ALTER TABLE stu_su ADD CONSTRAINT stu_fk FOREIGN KEY (stu_id) REFERENCES student (stu_id)
--ALTER Table Main Table name ADD CONSTRAINT foreign key name FOREIGN key (primary table as column name for foreign key) REFERENCES from table name (from table column name)
--Add Account foreign key
ALTER TABLE stu_su ADD CONSTRAINT su_fk FOREIGN KEY (su_id) REFERENCES subject (SU_ID)
--ALTER Table Main Table name ADD CONSTRAINT foreign key name FOREIGN key (primary table as column name for foreign key) REFERENCES from table name (from table column name)
MySQL Student management system: Table establishment, foreign key one-to-many, many-to-many relationship, the establishment of the Intermediate association table