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

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

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 .

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

View Full Document
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.
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
Ask a homework question - tutors are online