Create student-dissertation database

Chapter 1 Program Design Content and Requirements

1.1 Programming issues

(1) Create student_dissertation database, create 4 data tables in the database: t_student (student table), t_department (professional table), t_dissertation (topic table), t_s_dissertation (selection table), their table structure design is as follows:

(2) Enter the following data for each table:

Insert picture description here


(2) Enter the following data for each table:

Insert picture description here


Insert picture description here


Insert picture description here

1.2 Program design content and requirements

(1) Set the primary key for each table
(2) Query to obtain the student information of
all boys with a major number of 1 (3) Query to obtain the student information of all boys in the electronics department
(4) Create a view to show that the student's selected topic has not yet passed Approval information
(5) Create a stored procedure, and return the basic information of all students in the major by specifying a major.
(6) Create a trigger. When a topic is deleted, the relevant records in the topic table will be deleted at the same time
(7) Establish Referential integrity constraints between related tables in the database are all set to cascade

1.3 Purpose of program design

The course design of "Database Principles and Applications" is one of the very important practical links. It is a comprehensive exercise after completing the "Database Principles and Applications" course. The design of this course is mainly to consolidate students' understanding of the basic principles and basic theories of database, master the basic methods of database application system design and development, and further improve students' ability to comprehensively use the knowledge they have learned.
(1) Through this design, it is to understand students' mastery of database ˌ table creation, and insertion of data ˌ stored procedures.
(2) Master the basic operation and use skills of MySQL.
(3) Review and consolidate the content learned this semester.
(4) Improve students' comprehensive ability through a practical training, and let students discover their own problems.

Chapter 2 Program Implementation Ideas

In the database field, ER diagrams are usually designed to describe things in the real world. Each entity in the ER diagram corresponds to a data table.
In entity graphs, rectangles are used to represent entities, ellipses are used to represent attributes, and undirected edges are used to connect them to the entity. In this way, the attributes of the entity can be clearly seen. Draw the entity diagram of the four tables as follows:

(1) Student table ER diagram

Insert picture description here


(2) Major table ER diagram

Insert picture description here


(3) Subject table ER diagram

Insert picture description here


(4) Course selection table ER diagram

Insert picture description here


At the same time, the tables in a database are not independent of each other. There is a certain connection between them, such as The attribute of professional number exists in both the student table and the professional table. In ER diagrams, relationships can be used to represent the connections between entities. Prisms are usually used to represent the connection between entities.

Insert picture description here


Through the connection between the tables, we can achieve multi-table join query, through the relationship between the tables, the information in one table can be queried to get the information in another table. After clarifying the relationship between the tables, review the knowledge database creation statement create database to create the database, and then create each table through create table, and then improve the data in the table, and insert the data into the table with insert into values. Such a more complete database is created.
Then, in the created data table to realize the data query, such as providing a student's attribute information, you can query all the information of the student. At the same time, according to the requirements of the topic, you need to create corresponding views, stored procedures, triggers, etc. The view function is similar to filtering; the trigger is equivalent to installing a trigger device before or after the event occurs; creating a stored procedure can reduce the data transmission between the data side and the server side.
According to what you have learned, create a view through the create view statement, complete the creation of a stored procedure through the create procedure statement, and create a trigger through the create trigger.

Chapter 3 Program List or Text

1. Create a database
create database student_dissertation;
Insert picture description here
2. Create t_student table
mysql> create table t_student(
    -> studentid char(20) not null,#学生学号
    -> studentname char(8) null,#姓名
    -> studentsex char(2) null,#性别
    -> departmentid int(8) null,#专业号
    -> studentphone char(30) null,#电话
    -> password char(20) null,#密码
    -> primary key(studentid)
-> );
Insert picture description here
3. Create the t_department table
mysql> create table t_department(
    -> departmentid int(8) not null primary key,#专业号
    -> departmentname char(50) null,#专业名称
    -> manager char(8) null #主任名
    -> );
Insert picture description here
4. Create the t_dissertation table
mysql> create table t_dissertation(
    -> dissertationid char(20) not null primary key,#课题号
    -> dissertationname char(30) null,#课题名称
    -> attachment char(30) null,#附件说明
    -> teachername char(8) null #指导教师名
    -> );
Insert picture description here
5. Create t_s_dissertation table
mysql> create table t_s_dissertation(
    -> studentid char(20) not null,
    -> dissertationid char(20) not null,
    -> introduction text null,
    -> sendtime datetime null,
    -> state char(10) null,
    -> primary key(studentid,dissertationid)
    -> );
Insert picture description here
6. Enter data for the table t_department
mysql> insert into t_department values
(1,'电子系','杨明'),
(2,'计算机系','李长山'),
(3,'自动化系','顾林');
Insert picture description here
7. Input data to the table t_student
mysql> insert into t_student values
    -> ('1000','朱群','男',1,'13759778212','123456'),
    -> ('1001','王小川','男',1,'13576543233','778899'),
    -> ('1002','秦悦','女',2,'13767885731','456123'),
    -> ('1003','陈小飞','男',3,'13755897651','369874'),
    -> ('1004','胡小丽','女',3,'13567334622','147896'),
    -> ('1005','张维','女',1,'13854673392','458972'),
    -> ('1006','李勇','男',1,'13787416711','147896'),
    -> ('1007','罗青','女',1,'13887914751','146697');
Insert picture description here


Insert picture description here
8. Input data to table t_dissertation
mysql> insert into t_dissertation values
    -> ('Z001','基于CRC技术的无线通信模块','资助','沈方'),
    -> ('Z002','基于ARM的室内环境无线监测系统','资助','罗青'),
    -> ('D001','基于51单片机的GPS定位仪','资助','郭海'),
    -> ('J001','基于B/S构架的数字图书馆','资助','赵萍'),
    -> ('J002','听力训练播放器','非资助','王海');
Insert picture description here
9. Input data to table t_s_dissertation
mysql> insert into t_s_dissertation values
    -> ('1000','Z001','电子类','2009.10.20','待审批'),
    -> ('1001','Z002','电子类','2008.8.5','通过'),
    -> ('1002','J001','计算机类','2009.9.28','待审批'),
    -> ('1003','D001','电子,自动化类','2009.9.26','待审批'),
    -> ('1001','D001','电子,自动化类','2009.10.6','待审批'),
    -> ('1002','J002','计算机类','2008.7.24','通过');
Insert picture description here


Insert picture description here
10. Inquiry requires to obtain student information of all boys whose major number is 1
mysql> select *from t_student where departmentid=1;
Insert picture description here
11. Query and get student information of all boys in the electronics department
select * from t_student where departmentid=1 and studentsex='男';
Insert picture description here
12. Create a view to display the information that students have not passed the approval
mysql> create view v_nopass(studentid,dissertation,introduction,sendtime,state) as select * from t_s_dissertation where state='待审批
Insert picture description here
13. Create a stored procedure to return the basic information of all students in that major by specifying a major
mysql> delimiter //
mysql> create procedure p_department (in name char(50))
    -> begin
    -> select studentid,studentname,studentsex,studentphone,password from t_student where
    -> departmentid in(select departmentid from t_department where departmentname=name);
    ->  end 
 ->//
Insert picture description here
mysql> delimiter //
mysql> create trigger tr_drop after delete
    -> on t_dissertation for each row
    -> begin
    -> delete from t_s_dissertation where t_s_dissertation.dissertationid=old.dissertationid;
    -> end
-> //
Insert picture description here
AFTER TABLE t_student ADD FOREIGN KEY(departmentid) REFERENCES t_department(departmentid) on delete cascade on update cascade;
Insert picture description here
alter table t_s_dissertation add foreign key(studentid) references t_student(studentid) on delete cascade on update cascade;
Insert picture description here
alter table t_s_dissertation add foreign key(dissertationid) references t_dissertation(dissertationid) on delete cascade on update cascade;
Insert picture description here

Chapter 3 Curriculum Design Experience

Looking back on the design of this course, I still feel a lot of emotion. From theory to practice, during this period, it can be said that there are more bitterness than sweetness, but many, many things can be learned, and at the same time, it can not only consolidate what I have learned before. I have learned a lot of knowledge that I haven’t learned in books. Through this course design, I understand that it is very important to combine theory with practice. The main gains are as follows:
(1) I reviewed the knowledge learned this semester and found my own shortcomings and existing problems.
(2) This is the first time to complete the creation of the entire database. I understand that we should not only master theoretical knowledge, but also exercise our operational capabilities in order to better apply what we have learned.
(3) In this rare course design process, I exercised my thinking ability and practical ability. Through the process of topic selection and circuit design, the integrity of my thinking about the problem and the feasibility of connecting with real life have been strengthened.
(4) At the same time, cultivate the habit of finding solutions after encountering problems in practice, such as using the Internet or communicating with classmates.
(5) Through this training, I can better understand the power and wonder of the database, as well as the diversity of functions.
In this training, I not only gained a lot, but also discovered its own shortcomings and areas that need improvement:
(1) The mastery of knowledge is not strong enough, and the ability to operate on the computer needs to be improved.
(2) The knowledge of the database is limited to books, and not understood in real life.
(3) Insufficient familiarity with some query statements, which leads to a long time spent
(4) The understanding of the stored procedures of the database is not deep enough
(5) In the future, you should inquire more information, find the reasons and correct the existing problems, and learn to learn. use.
(6) Usually, the problems encountered are not solved in time, which causes the problems to exist all the time.

Chapter 5 References

[1] Chen Linlin, Jiang Lili, Editor-in-Chief Xie Erhu. SQL Server 2008 Database Design Tutorial. Jiangsu University Press, August 2016
[2] Ren Jinjun, Lin Haixia Editor-in-Chief MySQL Database Management and Development People's Posts and Telecommunications Publishing House June 2017 Month
[3] Baidu knows: https://www.unjs.com/fanwenwang/ziliao/590237.html
[4] Baidu Encyclopedia:
http://www.360doc.com/content/16/1011/21/15549660_597691151. shtml
[5] Baidu knows: https://bbs.csdn.net/topics/390822588

Thanks