[purpose and requirements of the experiment]
1. Understand and master the methods and main steps of database design;
2. Master the establishment method of conceptual structure and common expression tools (E-R diagram);
3. Master the conversion principle from conceptual structure to logical structure;
3. Master the syntax of SQL process control statements, stored procedures and triggers;
4. Understand the concept and principle of stored procedure and trigger;
5. Master the relevant operations of database programming in SQL Server 2008.
[experiment content]
Suppose a database should be established according to the information of departments, students, classes and societies of a university. A department has several majors. Each major recruits only one class every year, and each class consists of several students. Students of a department live in the same dormitory area. Each student can participate in more than one society. Each society has several students. Students participate in a society with an individual year of membership.
Try to complete the database design according to the above business rules and implement it in SQL Server.
1. The database is designed by using the standard design method, which is required to reach 3NF. Please give the detailed design process.
(1) Conceptual structural design:
Analyze the relationship between the four entities:
A department has several majors, 1: n;
Each major only recruits one class every year, 1:1;
Each class consists of several students, 1:n;
Students of a department live in the same dormitory area, 1; 1；
Each student can participate in multiple societies. Each society has several students, m:n;
There is a year when students participate in a society, 1:1
(2) Abstract structural design:
Basic elements involved: entity, attribute, code, domain, contact, etc.
(1) Entity: Department, student, major, society, major, class
(2) Attributes: properties of entities
Attributes of each entity:
Department: department number, department name
Student: student number, student name, student gender
Society: Student Union name and society number
Class: class number and class name
Major: major number and name
(3) Relationship: the relationship between the internal relationship of an entity and the relationship between entities: 1 to 1, 1 to many, and many to many. Reflected in the E-R diagram are the corresponding relationship types, and 1:1, 1:N, M: N.
(4) E-R drawing:

2. Indicate the main code and external code of each relationship.
Department (department number, department name)
Student (student number, student name, student gender, society number, society number)
Institute (Institute name, institute number)
Enrollment (student number, enrollment time)
Specialty (specialty number, specialty name)
Solid lines are the main codes, and the rest are external codes.
3. Please design a stored procedure and take the society number as the input parameter to realize the function of counting the number of students in the specified society.

create table Student information sheet(Student number varchar(10) not null,
full name varchar(20) not null,
Gender varchar(5) not null,
Age int not null,
Discipline number varchar(5) not null);

insert into Student information sheet(Student number,full name,Gender,Age,Discipline number)
values('s25301','Zhang Qiuli','female',18,'B21')
insert into Student information sheet(Student number,full name,Gender,Age,Discipline number)
values('s25302','Wen Cai Li','male',21,'B22')
insert into Student information sheet(Student number,full name,Gender,Age,Discipline number)
values('s25303','Li Siwen','female',22,'A20')
insert into Student information sheet(Student number,full name,Gender,Age,Discipline number)
values('s25304','Ouyang Junxiong','male',21,'A22');

create table Professional information sheet(
Discipline number varchar(10) not null,
Professional name varchar(20) not null);

insert into Professional information sheet(Discipline number,Professional name)
values('B21','Computer science and technology')
insert into Professional information sheet(Discipline number,Professional name)
values('B22','Data science and big data technology')
insert into Professional information sheet(Discipline number,Professional name)
values('A20','software engineering')
insert into Professional information sheet(Discipline number,Professional name)
values('A22','artificial intelligence');

create table Society information sheet(
Society number varchar(10) not null,
Institute name varchar(20) not null);

insert into Society information sheet(Society number,Institute name)
values('20021','Psychological Society')
insert into Society information sheet(Society number,Institute name)
values('20022','Basketball Society')
insert into Society information sheet(Society number,Institute name)
values('20023','Dance Society')
insert into Society information sheet(Society number,Institute name)
values('20024','Music Society');

create table Department information table(
Department number varchar(10) not null,
Department name varchar(20) not null);

insert into Department information table(Department number,Department name)
values('201','faculty of Mathematics')
insert into Department information table(Department number,Department name)
values('202','Department of computer science and technology')
insert into Department information table(Department number,Department name)
values('203','department of education')
insert into Department information table(Department number,Department name)
values('204','Department of foreign languages');

create table Attendance information form(
Student number varchar(10) not null,
Society number varchar(10) not null,
Membership time varchar(20) not null);

insert into Attendance information form(Student number,Society number,Membership time)
values('s25301','20021','4/21')
insert into Attendance information form(Student number,Society number,Membership time)
values('s25302','20022','4/13')
insert into Attendance information form(Student number,Society number,Membership time)
values('s25303','20023','4/1')
insert into Attendance information form(Student number,Society number,Membership time)
values('s25304','20024','4/12');

Student information sheet

Professional information sheet

Society information sheet

Department information table

Attendance information form