|
|
The database mainly manages the graduation design of graduates. The specific content includes the management of colleges, students, internship units, internship projects and internship instructors. The graduation design of students is divided into on-campus internship and off-campus internship. The information to be recorded is:
3. Basic situation of the students: student ID, name, gender, age, major, nationality, internship unit (for students internships on campus, this field is empty) ...
4. Internship unit: unit number, unit name, contact phone number, mailing address, post code ...
5. Topic Selection: Paper Title, Requirements ...
6. On-campus mentor: teacher ID, name, gender, department, title, position ...
7. Off-campus mentor: employee number, name, gender, department, title, position ...
The restrictions are as follows: a teacher can't supervise more than 10 students; teachers with less than the title of associate professors and professors can only guide students in this specialty;
Following is the table I wrote
Student entity converted to 'Student' table
Column name data type key, constraint, and default
Student number int primary key
Name varchar (20)
Last name char (2)
Age tinyint
Professional char
Hometown Varchar (50)
Paper ID int Foreign key: refer to the 'Paper' table if the paper ID is off-site by default 0
Conversion of corporate entities into 'learning units' tables
Column name data type key, constraint, and default
Unit ID int Primary key
Name varchar (50)
Phone varchar (30)
Address varchar (50)
Zip int
Ranch
Teacher entity is converted into 'School Tutor' form
Column name data type key, constraint, and default
Teacher ID int primary key
Name varchar (20)
Last name char (2)
Department char
Post char
Job title char
Ranch
Employee entities are converted to 'off-school tutor' form
Column name data type key, constraint, and default
Employee ID int primary key
Name varchar (20)
Last name char (2)
Department varchar (50)
Post char
Job title char
Unit ID int Foreign key: Refer to 'Unit ID' of 'Learning Unit' table
Ranch
The paper entity is converted into a 'paper'
Column name data type key, constraint, and default
Paper ID int primary key
Thesis title varchar (50)
Ranch
‘Student Thesis Internship’ form.
Column name data type key, constraint, and default
Student ID int Primary key: Refer to Student ID for Student ID
Employee ID int Foreign key: Refer to the 'Employer ID' table of the 'External Tutor' table
Teacher ID int Foreign key: Refer to 'Teacher ID' in 'School Tutor' table
Unit ID int Foreign key: Refer to the "Unit ID" of the "Learning Unit" table
Ranch
I want to work on the "Student Thesis" form. There is a restriction in China and Canada. If the professional title = 'Teaching Assistant', only 10 students in this department can be instructed. A check on constrain is a condition that cannot be added to a subquery to limit the check. Want to ask what better way,
My approach is to select the conditions on the view,
alter view assistant_student (student number)
as
select student thesis internship.
from student thesis internships, students, mentors
where (Student. Major = School Tutor. Department and School Tutor. Department = 'Assistant' and Student. Student ID = Student Paper Practice. Student Number and Student Paper Practice. Teacher ID = School Tutor. Teacher ID)
go
But what follows:
alter table student thesis internship
add constrain check (count (student thesis internship. Student ID = Assistant_Student (Student ID)) <10)
Always wrong
Please enlighten me |
|
|
|
China
Post time: 2020-1-11 10:00:01
Author