MySQL Student management system: Table establishment, foreign key one-to-many, many-to-many relationship, the establishment of the Intermediate association table

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