Question

Mary a database administrator frequently writes queries that combines the data in the Students Table with that in the CourseRegistration using Cartesian Products then filtering the data to obtain the course Registrations of a student. Mary also frequently queries for students who have registered for no course using the syntax CourseCount<1. Identity any three improvements that can be made to the performance of the student management system database from this description and write the appropriate best performing versions of each query scenario explaining why your query will perform better.

Answer 1

let cartesian of student table and courseregistration table be

SELECT Student.NAME, Student.AGE, Student.interest_arts,Student.interest_sports,Student.interest_others,CourseRegistration.facultydetails,CourseRegistration.lab,CourseRegistration.lab_schedule,CourseRegistration.Coursecount FROM Student CROSS JOIN CourseRegistration;

Let the new table formed as a result of cartesian be Studentcourse.

Three improvements that can be made to the performance of the student management system database from this description

1.Student Module :The student module gives access to data regarding student who are interested in arts,sports and other etracarricullar activities from students who have registered for no course.

SELECT Student_name ,interest_arts,interest_sports,interest_others FROM Studentcourse WHERE  CourseCount<1.;

2.Lab schedhuling includes the time table / schedule of all the labs present for that particular Courseregistration.

SELECT labs,lab_schedhule FROM Studentcourse;

3'The faculty details includes the contact details of each and indusial professor like e-mail address and office number and professional details like designation , specialization, qualification department wise

The faculty modules provides information of each professor in the particular department.

SELECT course_name,department,faculty,contactdetails, FROM Studentcourse;

These query will perform best because we can get the informations of students who have no registered courses but interested in arts ,sports and other actvities that can direct students to their interested areas.Students who are registered can know more about their timetable and labs so that they can be prepared for that.Most importantly faculty details are really helpfull for doubt clearence.