CS2102 Exercise I. (24 marks) Give your answer in the space provided in the scri
ID: 3756275 • Letter: C
Question
CS2102 Exercise I. (24 marks) Give your answer in the space provided in the script. Points may be deducted for unnecessary comments, unnecessary long or complicated answers and wrong answers. Consider the following self-describing database schema for the management of breakfast at Nang-Si-Da Resont. customer(name, passport, nationality, age) menu(type, item, price) breakfast(passport date, item) Primary keys are underlined passport in the table 'breakfast' references the primary key of the table customer item' in the table 'breakfast references the primary key of the table 'item The following constraints are enforced. Customers are identified by their passport number. Note that two different customers may have the same name. Menu items are identified by their name (attribute 'item). The combination of attributes 'date', item' and 'passport, is unique in the breakfast table and none of their values is null. The natural referential integrity applies. For example, Ms. Sinsamut, a 35 years old Thai national, with passport L123X, ordered fried rice and coffee on 31/12/2013. The next day, she ordered coffee. Fried rice is a food item, which costs 120 Baht. Coffee is a drink item, which costs 50 Baht. There is another customer called Sinsamut. This other customer is a 60 years old American national, with passport U0001 The following is the corresponding excerpt of the database instance: customer(Sinsamut, L123X', 'Thal, 35) customer( Sinsamut, "U0001', 'American', 60) menu(food', Fried Rice', 120) menu('drink 'Coffee', 50) breakfast('L123X, '31/12/2013', Fried Rice') breakfast('L123X, 31/12/2013', 'Coffee) breakfast( L123X, '01/01/2014', 'Coffee') Consider primary and foreign key constraints to simplify your answers.Explanation / Answer
15.A. select name
from customer
where passport in
( select distinct(passport)
from breakfast
where item = 'coffee' or item = 'toasts');
in 15th question they asked the customer names who orderd coffee or toasts in breakfast. first we have to select passport from breakfast (since it is foreign key in between breakfast and customer). so select distinct(passport)
from breakfast
where item = 'coffee' or item = 'toasts' become inner query and we are using distinct function to avoid multiple times encouter of same customer passport.
after selecting distinct passports we select correspoinding name of customer from customer table. due to distict passport we can elimate multiple encouter of same customer name.. there for
select name
from customer
where passport in
become outer query..
16.A select item
from menu
where price in
(select min(price)
from menu
where type = 'food');
the question is finding cheapest food item. we have types in food like drink or food and cheapest means low price food. so first we select minimum price(by using min() aggrigation function we can easily get min value of column) from menu of type food .
therefore select min(price)
from menu
where type = 'food' becomes inner query.
after finding minimum price we select corresponding price food items using comparison.
therefore
select item
from menu
where price in
becomes outer query for selecting item name from menu..