Question

In: Computer Science

Consider the following relational schema: student(studID, studname, major, advisor) department(deptname, major) club(studID,clubname) professor(profID, profname, building, deptname)...

Consider the following relational schema:

student(studID, studname, major, advisor)

department(deptname, major)

club(studID,clubname)

professor(profID, profname, building, deptname)

NOTE: KEY ATTRIBUTES ARE IN BOLD

where advisor takes values in the domain of professor names (profname) and

the underline attributes form the primary key of the corresponding relations.

Questions:

  1. State any assumptions you might make.
  2. Write the relational algebra for the following queries:

2.a. Find all students and their advisors.

2.b. Find all the students who are in any one of the clubs that Jamie Smith is in.

2.c. Find all of the advisors, their buildings and departments that advise students that

are in the same clubs that Jamie Smith participates into.

2.d. Find all professors names and their departments that have offices in the

buildings identified in query 2c.

2.e. Find all student names and their major(s) that participate in Computer Science

Association Club.

Solutions

Expert Solution

Assumptions: Assuming that the student.advisor and professor.profname are the same.

2.a. Find all students and their advisors.

select studname,advisor from student;

2.b. Find all the students who are in any one of the clubs that Jamie Smith is in.

select s.studname
from student s, club c
where s.studID = c.studID
and c.clubname in (select c.clubname
from student s, club c
where s.studID = c.studID
and studname = 'Jamie Smith');

2.c. Find all of the advisors, their buildings and departments that advise
students that are in the same clubs that Jamie Smith participates into.

select s.advisor, p.building, d.deptname
from student s, club c, department d , professor p
where s.studID = c.studID
and s.advisor = p.profname
and p.deptname = d.deptname
and s.major = d.major
and c.clubname in (select c.clubname
from student s, club c
where s.studID = c.studID
and studname = 'Jamie Smith');


2.d. Find all professors names and their departments that have offices in the
buildings identified in query 2c.


select p.profname, p.deptname
from professor p where profID in
(select p.profID
from student s, club c, department d , professor p
where s.studID = c.studID
and s.advisor = p.profname
and p.deptname = d.deptname
and s.major = d.major
and c.clubname in (select c.clubname
from student s, club c
where s.studID = c.studID
and studname = 'Jamie Smith'));


2.e. Find all student names and their major(s) that participate in Computer Science

Association Club.

select s.studname, s.major
from student s, club c
where s.studID = c.studID
and c.clubname = 'Computer Science Association Club';


Related Solutions

Consider the following relational schema: student(studID, studname, major, advisor) department(deptname, major) club(studID,clubname) professor(profID, profname, building, deptname)...
Consider the following relational schema: student(studID, studname, major, advisor) department(deptname, major) club(studID,clubname) professor(profID, profname, building, deptname) NOTE: KEY ATTRIBUTES ARE IN BOLD where advisor takes values in the domain of professor names (profname) and the underline attributes form the primary key of the corresponding relations. Questions: State any assumptions you might make. Write the relational algebra for the following queries: 2.a. Find all students and their advisors. 2.b. Find all the students who are in any one of the clubs...
Consider the following relational schema about a University (the primary keys are underlined and foreign keys...
Consider the following relational schema about a University (the primary keys are underlined and foreign keys are italic) STUDENT(StudentID, name, major, year, age) CLASS(ClassName, meetsAt, room, LecturerID) ENROLLED(StudentID, ClassName, mark) LECTURER(LecturerID, name, DepartmentID) DEPARTMENT(DepartmentID, name) Write the SQL statements for the following query: B1. Find the age of the oldest student. B2. Find the ID’s of lecturers whose name begins with “K” \ B3. Find the age of the youngest student who is enrolled in Mechatronics. B4. Find the age...
Question 2: consider the following library relational database schema Write a program segment to retrieves the...
Question 2: consider the following library relational database schema Write a program segment to retrieves the list of books that became overdue yesterday and that prints the book title and borrower name for each. 1- Use JDBC with Java as the host language
Consider the following relational database schema:             employee(employee-name, employee-id, street, e-city)             works(employee-
Consider the following relational database schema:             employee(employee-name, employee-id, street, e-city)             works(employee-id, company-id, salary)             company(company-name, company-id, c-city)             manages(employee-id, manager-id) Specify the following queries on this database schema using the relational operators we discussed in class. Write your answers on a separate sheet of paper in the order that they are specified below. Retrieve the name and address of employees who work for First Bank Corporation. Retrieve the name, street address, and city of residence of all employees...
Write the following queries using the schema below Class (Number, Department, Term, Title) Student (Username, FirstName,...
Write the following queries using the schema below Class (Number, Department, Term, Title) Student (Username, FirstName, LastName, Year) Takes (Username, Department, Number, Term, Grade) [clarification] In Student, Year is an integer. A student can be a 2nd year student. In that case, Year value would be 2. For the Takes relation: ·         Grade is NA for current semester students ·         Username is foreign key to Student ·         (Department, Number, Term) is foreign key to Class a)       Write an SQL query that returns the Term...
schema: Student( studID, name, major )   // dimension table, studID is key Instructor( instID, dept );  ...
schema: Student( studID, name, major )   // dimension table, studID is key Instructor( instID, dept );   // dimension table, instID is key Class( classID, univ, region, country );   // dimension table, classID is key Took( studID, instID, classID, score );   // fact table, foreign key references to dimension tables Write a SQL query to find all students who took a class from an instructor not in the student's major department and got a score over 80. Return the student name,...
Consider the following project regarding the building of a large bridge between two major cities. The...
Consider the following project regarding the building of a large bridge between two major cities. The bridge will involve a ‘toll’ or monetary charge, where users of the bridge will pay an amount of money each time that they cross over the bridge. The current date is 1 January 2019. The bridge is expected to take 2 years to build, and the first paid crossing of the bridge will occur in exactly 2 years from now. The costs of the...
Consider the schemas of the following relational database for a company. The company has different departments...
Consider the schemas of the following relational database for a company. The company has different departments at different cities in different states: employee(employee-id, dept-id, name, street-num, street-name, city, state, zip, salary) department(dept-id, dept-name, city, state) manager(manager-id, employee-id) NOTES: manager-id in the manager relation is a foreign key to the employee relation. employee-id in the manager relation is a foreign key to the employee relation. dept-id in the employee relation is a foreign key to the department relation. An employee belongs...
Consider the following universal relation THE following database schema is in 4NF. What can you infer...
Consider the following universal relation THE following database schema is in 4NF. What can you infer about multi-valued dependencies? A C D A B C E E F A-It does not have multi-valued dependencies. B-The multi-valued dependency A ->-> C does not hold. If the multi-valued dependency A ->-> C held, the database would not be in 4NF. C-The multi-valued dependency A ->-> B does not hold. If the multi-valued dependency A ->-> B held, the database would not be...
Consider the following database schema:                Product(maker, model, type)                PC(model, speed
Consider the following database schema:                Product(maker, model, type)                PC(model, speed, ram, hd, rd, price)                Laptop(model, speed, ram, hd, screen, price)                Printer(model, color, type, price) Give SQL statement for each of the following: (Grouping and Aggregation) Write the following queries in SQL: Find the average speed of laptops costing over $2000. Find the average price of PC’s and laptops made by manufacturer “D”. Find, for each manufacturer, the average screen size of its laptops. Find the manufacturers...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT