Database Course Design Questions

I have been designing my database course since Saturday and February November 27. So far, it is a little dizzy ~~~~~ Start introspection Summary

This series of learning logs will be published at intervals of three to four days (or one article per day, I have some experience in completing this database course design and timely reflection and summary.

As the first log:

Let's first introduce the selected question: School Classroom Management Information System.

Requirement analysis result Description: 1. organization structure description: The school has several schools, such as the Computer School and the management school. A college has several majors, such as the Computer School, which has two Majors: computer science and technology and information security; A major has several classes. A class has several students, and the school has several teaching buildings, such as South 1 and South 2. a teaching building has several floors, for example, South 1 has one floor, two floors, three floors, and four floors. One floor has several classrooms, such as 1101,1102, 1103,1104. A teacher can open several courses, and a course can be taught by multiple teachers. A classroom can have different courses at different time periods. 2. user Requirements on system information (1) Information Requirements: because the system is mainly used by teachers and students, the system information requirements can be divided into the following aspects: a. basic information about teachers, including the instructor ID, instructor name, gender, department, title, and ID card number. B. Basic information about students, it mainly includes the student's student ID, name, Department number, ID card number, etc. C. basic information about the Classroom Information, including the classroom number, teaching building number, and floor number; d. Basic information of the Information teaching building of the teaching building, including the name and number of the teaching building. (2) The system shall complete the following information processing requirements: A. query by instructors; students can query the personal information and course class information of related teachers of relevant departments to better understand teachers and their course classes. B. Classroom query. Students can query information about classrooms in the relevant teaching buildings and the usage of the classrooms at any time every day, or have classes, lectures, or activities. This feature allows you to better understand the classroom and usage. C. Course query; students can query the details of a course. For example, a course can contain multiple instructor classes to better understand the details of any course. D. Borrow a classroom. Students can borrow a classroom to obtain the right to use the classroom for a certain period of time, give lectures, and conduct community activities. E. I want to study by myself. Students can query the free classrooms in a certain time period or multiple time periods on the current day and go to study by themselves. Design requirements: according to the above description, design the concept of the system structure is the E-R diagram, and then converted to the corresponding logical structure. Complete the following requirements 1. Create a view to query the course names of all the courses taught by Teacher Zhang of the Computer Science Institute. 2. Compile a stored procedure with parameters, query the usage of the specified teaching building, specified classroom, specified time period, or have classes, lectures, activities, or free time. 3. Compile a stored procedure to query all instructors of a specified course and the corresponding course class time. 4. Write the stored procedure to complete the function of borrowing classrooms from students. If the requested classroom conflicts, a prompt is displayed.