CS2102 Exercise I. (24 marks) Give your answer in the space provided in the scri
ID: 3756274 • 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
13)
Every value present in foreign key must present in primary key of the referenced relation.
the given query executed dependently. b.passport depends on c.passport.
If there is a key constraint then merge the table of relationship set with an entity set to have minimum number of relations in the relation model.
14)
SELECT AVG(age)
FROM
(
SELECT age
FROM customer c, breakfast b
WHERE c.passport=b.passport AND b.item='Fried Rice');