Tutorial3 - 4. Find the names of all Professors who teach a...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
Tutorial 3 Problem 1: BCNF form Consider a relation with schema R(A,B,C,D,E) and FD’s AB C, DE C, and B D. Is this relation in BCNF? Why or why not? If it's not, decompose the relation into collections of relations that are in BCNF. Make sure to indicate which dependency you apply to each decomposition .
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Problem 2: SQL Consider the following database schema, and write the requested queries in SQL. Courses(course_id, course_name, department) Professors(prof_id, prof_name, office_building) Coursebook(prof_id, course_id, scheduled_time) 1. Find the names of all courses, which are scheduled at 2:00pm and are not in the Linguistics Department. 2. Find the names of all Professors who do not teach any courses in the Computer Science department. 3. Find the names of all Professors who teach courses only in the Math Department.
Background image of page 2
Background image of page 3
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 4. Find the names of all Professors who teach a course which has a 2 somewhere in its course_id. Problem 3: SQL Subqueries Consider the following database schema: • Camera(manufacturer, model, type, price) • P&S(model, shutter_speed, zoom, internal_memory) • SLR(model, shutter_speed, internal_memory) Note that both P&S (point and shoot) and SLR (digital single lens reflex) are Cameras, identified by the type field. Write the following queries in SQL. Do not use aggregation functions. 1. Find the model of P&S's whose internal memory is lower than that of any SLR. 2. Find the manufacturer of the P&S with the fastest shutter speed. 3. Find the list of model-manufacturer pairs where the model in each pair is the most expensive model for the manufacturer....
View Full Document

This note was uploaded on 02/17/2012 for the course CS 411 taught by Professor Winslett during the Spring '07 term at University of Illinois at Urbana–Champaign.

Page1 / 3

Tutorial3 - 4. Find the names of all Professors who teach a...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online