CASE BACKGROUND

The system we will design is a $$Student Registration System$.$Read the following description of the system carefully before moving on to the following section:

$$

As the head of information systems of a local college, you have been assigned the task of developing a new student registration system. The university wants to use a web$-$based $3-$tier system to replace its legacy system that was developed based on mainframe technology. $$The new system will allow students to register for courses and view report cards online from personal computers connected to the Internet. $$

$$

Students can select multiple course offerings for the coming semester. $$Course offerings will have a maximum of ten students and a minimum of three students. $$A course offering with fewer than three students will be canceled. $$For each semester, there is a period of time during which students can change their schedule. $$Students must be able to access the system during this time to add$/$drop courses. $$Once the registration process is completed for a student, the registration system sends a note to the billing system. $$If a course is full during the registration process, the student must be notified of the change before submitting the schedule for processing.

$$

At the end of the semester, the student will be able to view an electronic report card. $$Since student grades are sensitive information, the system must employ extra security measures to prevent unauthorized access.

$$

Professors will be able to access the online registration system to indicate which courses they will be teaching. They can also see which students signed up for their course offerings. $$In addition, the professors will be able to record and post student grades online.

PART $2$ DATA MANIPULATION

In Part $2$ of the Assignment, your task is to write query statements using SQL$.$The SQLite lab session will help you with a set of sample queries. $$Your task is to make queries to answer the following exercise questions.

$$

SQL Exercise Questions:

$1.$ Directory of Students. Include the ID$,$ name and major of all students. Sort the results by student ID$.$

$$

$2.$ Multi$-$credit Courses. List the ID$,$ name and department of all courses that are worth more than $1$ credit.

$$

$3.$ Management Courses in Fall $2018.$ List all courses offered by the management department in Fall semester of $2018.$

Include the ID$,$ name, department, the sections, meeting times and the locations for each course.

$$

$4.$ Roger$$s Instructors. The student Roger Collins would like to know his professors. List all instructors of courses in which he is registered in Spring $2019.$ Include the professor$$s name, the course ID$,$ course name and section.

$$

$$

$$

$$

$$

$$

$$

$$

$$

$$

$$

$$

$$

$$

$$

$$

$$

$$

$$

$$

$$

$$

$$

$$

$$

$5.$ Major Size. List the majors and the number of students who belong to each major. Name the column for number of students CountOfStudents.

$$

$6.$ Course Enrollment. Show all courses offered in Spring $2019$ and the number of students registered in each course. $$Include the department name, course ID$,$ section and total number of students. Sort the results by department name, course ID within department, then section within each course.

$$

$7.$Half$-$time Students. List all students who are taking less than $12$ credits of courses in Spring $2019.$

$$

$8.$ Course Registration. Jerry Nelson $($StudentID: S$137)$ has registered for section $04$ of 'Strategic Management' $($CourseID: MGMT$4501)$ for Spring $2019.$ Write an INSERT query to perform this operation.

$$

$9.$Professor Reappointment. Professor Janet Fisher $($ProfessorID: P$4332)$ is moving from the Management department to the

Economics department. Write an UPDATE query to perform this operation.