CS348 Study Guide - Midterm Guide: Unique Key, Relational Algebra, Foreign Key

169 views4 pages
Published on 16 Oct 2011
School
University of Waterloo
Department
Computer Science
Course
CS348
Professor
CS 348 Midterm Exam No aids permitted Winter 2010
Professor Tompa February 25, 2010, 1:00-2:20 pm Page 1 of 4
Write all your answers neatly in the answer booklet provided.
Be sure your name and id number are on the cover of the booklet.
Note that in marking your answers to Questions 1 and 2, we will be tolerant of minor syntax
errors for which your intention is clear.
1. [6 marks] Write an appropriate DDL statement to declare the following relation in SQL:
Car (LicenceNum: string, Province: string, Make: string, Model: string, Year: integer, VIN: string)
where values of Year must lie between 1900 and 2011; Make is a foreign key into a
relation called Manufacturer (having an attribute also named Make as its primary key); and
VIN is required to take a unique value for each Car tuple.
create table Car (
LicenceNum varchar(10) not null,
Province char(2) not null,
Make varchar(20) references Manufacturer,
Model varchar(20),
Year integer check(Year >= 1900 and Year <= 2011),
VIN char(20) unique,
primary key (LicenceNum, Province)
)
2. [24 marks in total]
The Nutritionists Union has created the following relational database schema to maintain
information about foods, including their nutritional value and health implications:
Class ( food , food_group )
Requirement ( nutrient , minimum_recommendation )
Supplies ( food , nutrient , amount )
Combats ( nutrient , disease )
(The Class relation partitions foods into food groups; Requirement gives the minimum
daily requirement for each nutrient; Supplies specifies the amount of a nutrient provided
by 1 serving of the food; and Combats relates nutrients to the diseases they help prevent.)
a) [5 marks] Write an SQL query to answer:
“Which foods provide (or exceed) the minimum recommendation of iron?”
select food
from Supplies s, Requirement r
where s.nutrient = r.nutrient and amount >= minimum_recommendation
and s.nutrient = “iron”
Unlock document

This preview shows page 1 of the document.
Unlock all 4 pages and 3 million more documents.

Already have an account? Log in

Document Summary

Write all your answers neatly in the answer booklet provided. Be sure your name and id number are on the cover of the booklet. Vin is required to take a unique value for each car tuple. create table car ( Year integer check(year >= 1900 and year <= 2011), Vin char(20) unique, primary key (licencenum, province: [24 marks in total] The nutritionists union has created the following relational database schema to maintain information about foods, including their nutritional value and health implications: Supplies ( food , nutrient , amount ) Which foods provide (or exceed) the minimum recommendation of iron? select food from supplies s, requirement r where s. nutrient = r. nutrient and amount >= minimum_recommendation and s. nutrient = iron . Winter 2010: [7 marks] write an sql query to answer: Which foods provide (or exceed) the minimum recommendation of iron? food( nutrient= iron and amount >= minimum_recommendation(supplies. Requirements) : [5 marks] use relational algebra to express the query: