|
|
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 students: student ID, name, gender, age, major, nationality, internship unit (for students who practice in the school, this item is empty) ...
4. Internship unit: unit number, unit name, contact phone number, mailing address, postal code ...
5. Topic Selection: Paper Title, Requirements ...
6. On-campus mentor: teacher ID, name, gender, department, title, position ...
7. Off-campus internship 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 types: keys, constraints, and defaults
Student Number int Primary Key
Name varchar (20)
Last name char (2)
Age tinyint
Professional char
Hometown varchar (50)
Paper ID int Foreign key: Whether the paper ID referring to the 'Paper' table is off-site by default 0
Conversion of corporate entities into 'learning units' tables
Column name data types: keys, constraints, and defaults
Unit ID int Primary key
Name varchar (50)
Phone varchar (30)
Address varchar (50)
Zip int
Teacher entity is converted into 'School Tutor' form
Column name data types: keys, constraints, and defaults
Teacher ID int Primary key
Name varchar (20)
Last name char (2)
Don't char
Post char
Title char
Employee entities are converted to 'off-school tutor' form
Column name data types: keys, constraints, and defaults
Employee ID int Primary key
Name varchar (20)
Last name char (2)
Department varchar (50)
Post char
Title char
Unit ID int Foreign key: Refer to the Unit ID of the Unit of Study table
The paper entity is converted into a 'paper'
Column name data types: keys, constraints, and defaults
Paper ID int Primary Key
Title of thesis varchar (50)
‘Student Thesis Internship’ form.
Column name data types: keys, constraints, and defaults
Student ID int Primary key: Refer to Student ID for Student ID
Employee ID int Foreign key: Refer to the 'Employee ID' of the 'Tutor Outside' table
Teacher ID int Foreign key: Refer to 'Teacher ID' in 'School Tutor' table
Unit ID int Foreign key: Refer to 'Unit ID' of 'Learning Unit' table
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 = 'Teacher' and Student. Student Number = Student Paper Practice. Student Number and Student Paper Practice. Teacher ID = School Tutor. Teacher ID)
go
But the next step: should you use check to limit or use stored procedures to control it?
Or is there a better way to change the design of the database?
Can't figure it out ～～～～～～～～～
Please enlighten me |
|
|
|
China
Post time: 2020-1-17 07:20:01
