1. Consider the relation schemas: faculty = (SSN, last-name, first-name, street,
ID: 3723975 • Letter: 1
Question
1. Consider the relation schemas: faculty = (SSN, last-name, first-name, street, city, state) student (SSN, last-name, first-name, street, city, state) and the following instance: SSN last-name citystate street Perkins Iowa State first-name 388-67-9009 Bai 980-67-8765 Jackson May 980-67-8765 Robert 435-67-2342 Charles Courtney Baton Rouge LA Baton Rouge LA Baton Rouge LA Washington Baton Rouge LA Shuju Tina a. Is this instance a legal instance of relation faculty? Why? b. Is this instance a legal instance of relation student? Why? 2. I have a relational database schema: Professor = (Professorld, name, Deptld, password, address) Transcript = (Student!d. CrsCode. Semester, Grade) Student-Student ld, name, Deptld, year, address) CourseOfferings = (CrsCode. Semester) Course CrsCode, CrsTitle, CrsDescription Teaches = (Professor!d. CrsCode. Semester) Write out the relational algebra for the following queries. 1) Find the semesters when course ‘operating systems, was offered. 2) Find the names and Deptld of all professors who taught in spring 2010.Explanation / Answer
Hi.. Please check below.
Question 1:
a. Is this instance a legal instance of relation faculty? why?
Ans:
Yes. It is a legal instance because it satisfies the primary key of lastname in faculty table.
b. Is this instance a legal instance of relation student? why?
Ans:
No. It is not legal instance because it doesnt satisfies primary key constraint.
SSn: 980-67-8765 is avialable for two records and it violates primary key.
Question 2:
1. Find the semester when course 'opertaing systems' was offered?
Ans: select a.Semester from Transcript a,Course b where a.CrsCode=b.CrsCode and b.CrsTitle='Operating Systems';
2. find the names and departments id of all professors who taught in spring 2010.
Ans: select p.name,p.DeptId from Professor p, Teaches T where p.ProfessorId=T.ProfessorId and T.CrsCode in (select CrsCode from Course where CrsTitle='Spring 2010' )
Please check the above answers and let me know any issues. Thank you. All the best.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.