Study Guides (248,518)
Canada (121,606)
CS 348 (16)
Midterm

Midterm + Solution of Winter 2010 Winter 2010

4 Pages
409 Views
Unlock Document

Department
Computer Science
Course
CS 348
Professor
Ihab Ilyas
Semester
Fall

Description
CS 348 Midterm Exam No aids permitted Winter 2010 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 : strinProvinc: strinMake : strinModel : strinYear: integeVIN : string) where values ofYear must lie between 1900 and 2011; Make is a foreign key into a relation calleManufacturer(having an attribute also nameMake as its primary key); and VIN is required to take a unique value for eCar 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 grouRequirement gives the minimum daily requirement for each nutrienSupplies specifies the amount of a nutrient provided by 1 serving of the food; anCombats 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” Professor Tompa February 25, 2010, 1:00-2:20 pm Page 1 of 4 CS 348 Midterm Exam No aids permitted Winter 2010 b) [7 marks] Write an SQL query to answer: “Which food groups include a food that provides at least two different nutrients that combat cancer? For each such food group, give also the count of the number of foods in the group satisfying this criterion. select food_group, count(*) from Class c where c.food in select food from Supplies s, Combats x where s.nutrient=x.nutrient and x.disease=”cancer” group by food having count(*) >= 2 group by food_group [Alternative solution:] select food_group, count(*) from Class c where c.food in select s1.food from Supplies s1, Combats x1, Supplies s2, Combats x2 where s1.nutrient=x1.nutrient and x1.disease=”cancer” and s2.nutrient=x2.nutrient and x2.disease=”cancer” and s1.food = s2.food and s1.nutrient != s2.nutrient group by food_group c) [4 marks] Using SQL, define a view that shows pairs for each food in the `meat' group providing the highest amount of that nutrient (as compared to others in the meat group). create view V as ( select food, nutrient from Supplies s, Class c where c.food=s.food and c.food_group=”meat” and s.amount >= all (select s2.amount from Supplies s2, Class c2 where c2.food=s2.food and c2.food_group=”meat”) ) d) [3 marks] Use relational algebra to express the query: “Which food
More Less

Related notes for CS 348

Log In


OR

Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


OR

By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.


Submit