Using MYSQL: Create a VIEW named OFFERING that combines the base tables to prese

Question

Using MYSQL:

Create a VIEW named OFFERING that combines the base tables to present the following schema:

OFFERING (course_id, sec_id, title, semester, year, dept_nm, credits, instr_nm)

An example tuple in this view:

UNIVERSITY schema:

Classroom(Building, Room_Number, Capacity);
Department(Dept_Name, Building, Budget);
Course(Course_Id, Title, Dept_Name, Credits);
Instructor(Id, Name, Dept_Name, Salary);
Section(Course_Id, Sec_Id, Semester, Year, Building, Room_Number, Time_Slot_Id);
Teaches(Id, Course_Id, Sec_Id, Semester, Year);
Student(Id, Name, Dept_Name, Tot_Cred);
Takes(Id, Course_Id, Sec_Id, Semester, Year, Grade);
Advisor(S_Id, I_Id);
Time_Slot(Time_Slot_Id, Day, Start_Time, End_Time);
Prereq(Course_Id, Prereq_Id);

Explanation / Answer

Create view OFFERING as Select Course.Course_id as course_id, Section.sec_id as sec_id , Course.Title as title, Section.semester as semester , Section.year as year , Department.Dept_name as dept_nm, Course.credits as credits , Instructor.Name as instr_nm from Course inner join Section on Course.Course_id = Section.Course_id inner join Department on Course.Dept_Name = Department.Dept_Name inner join Teaches on Teaches.Course_Id = Course.Course_id inner join Instructor on Teaches.Id = Instructor.Id ;

Do ask if any doubt. Please upvote.

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects