WIXLIB

Midterm ExaminationCMPT354 Database Systems I-- Spring 2012Hassan KhosraviStudent ---------------------Last Name: ------------------First Name: -------------------Instructions:1. Answer all questions2. Credits/points for each question is indicated in the brackets [ ] before the question.3. No books (or other material) are allowed.4. Attach extra sheets (available upon request) if required (write full name on each extra sheet).Good LuckProblemScoreMax score1 ----- 122 ----- 123 ----- 124 ----- 125 ----- 12Total -----------------------------------------------60

1. Database management systems[4] (a) Briefly describe two of the limitation of file systems compared to database managementsystems.They do not directly support querying and modifying dataTheir support for database creation is limited to creation of filesYou can lose data that has not been backed upUncontrolled concurrent accesses (Isolation) can lead to inconsistencies [6] (b) Briefly describe the functionality of the following components in a database managementsystem.Query CompilerThe query compiler consists of three major unitsQuery parser: builds tree of the structure from textQuery preprocessor: performs semantic checks on the queryQuery optimizer: transforms the initial query plan into the best available sequence of operations.Buffer ManagerThe buffer manager communicates with the storage manager to get requested data from thesecondary storage (hard drive) into the main memory (RAM) where it could be used by othercomponents of the system.Concurrency ControlConcurrency control manager is responsible for ensuring that transactions are executedatomically and in isolation from one another.[2] (c) What is meant by “Isolation” and “Atomicity”?Isolation: refers to the requirement that no transaction should be able to interfere with anothertransaction. The effect should be as if all of the transactions were running serially.Atomicity: Requires that each transaction either happens completely or does not happen at all.If one part of the transaction fails, the entire transaction must fails, and the database state is leftunchanged.

Consider a shop for flowers on the World Wide Web. It offers a selection of different bouquets(bunches of flowers) described in the table “Flowers":The table “Customer" contains information about customers:Next, there is a table with information about orders and recipients (to whom the flowers shouldbe delivered)“Prod" identifies the product, i.e. which bouquet should be delivered, “Cust" identifies thecustomer (who ordered the flowers). “Date" is the date when the flowers should be delivered.The rest of the information is for the person receiving the flowers. Some of the flowers are sentvia FedEx and other orders are given to local flower shops. Only flowers that have not yet beendelivered are kept in these two tables.

2. Entity Relationship model[12] (a) Draw the entity relationship model for the described database.

3. Relational AlgebraFormulate the following queries in relational algebra[2] (a) Give all information about flower bouquets which cost over 50σ Price 50(Flowers)[2] (b) Print the recipient name and address of all orders to be delivered in Pittsburgh after10/07/99. You can compare date values by the standard operators , , , Π RName, RAddress(σ RCity 'Pittsburgh' Date '10 07 99'(Order)[4] (c) Print the name of “Loyal customers” and the total amount of money they have spent.(Loyal customers are those that have spent over 300).Π cname, spent))( σ spent 300 (cust, sum(price) spent(flowersorderCustomer)[4] (d) Print the name of customers that prefer mailing their flowers directly rather than sendthem to local flower shops (based on only the current deliveries).Π cname(σ m order s order (cust, count(ord) m order (ordermailed)。))cust, count(ord) s order (orderSubcontracted)

4.SQLConvert your answers from question three to SQL commands[2] (a) Give all information about flower bouquets which cost over 50Select *From flowersWhere price 50[2] (b) Print the recipient name and address of all orders to be delivered in Pittsburgh after10/07/99. You can compare date values by the standard operators , , , Select Rname, RaddressFrom orderWhere order.date ‘1999-07-10’ AND Rcity ‘Pittsburgh’[4] (c) Print the name of “Loyal customers” and the total amount of money they have spent.(Loyal customers are those that have spent over 300).Select Cname, sum(price) as spentFrom flowers Natual join order Natural join customerWhere spent 300Group by cust[4] (d) Print the name of customers that prefer mailing their flowers directly rather than sendthem to local flower shops (based on only the current deliveries).Select CnameFrom (select customer, count(ord) as m orderfrom order Natural join mailed order Natural joingroup by cust) outer join (select customer, count(ord) as s orderfrom order Natural join subcontracted Natural joingroup by cust)Where m order s order

5. Triggers and constraints[3] (a) Use constraint integrity on the field CZip in the Customer table to ensure that the inputvalue has five digits (between 10000 and 99999).Alter table customer add constraint cust zip check (czip 10000 AND czip 99999)Create assertion cust zip check (czip 10000 AND czip 99999)[4] (b) Write a trigger to delete all the orders, mails, and subcontracts of a customer if a personis deleted from a costumer table.Create trigger removeAfter delete on customerFor each rowBeginDelete from mailedWhere ord in (select ord from where cust old.cust);Delete from subcontractsWhere ord in (select ord from order where cust old.cust);Delete from orderWhere ord.cust old.cust;End[5] (c) Use a trigger (or triggers) to ignore mailed or subcontracted orders from customers thathave over 50 undelivered flowers.Create trigger ignoreBefore insert on mailedFor each rowWhen 50 (select count(*)From(Select ord from mailed left join order on mailed.ord order.ordwhere cust (select cust from order where ord new.ord)union allSelect ord from subcontracted left join order on subcontracted.ord order.ordwhere cust (select cust from order where ord new.ord))beginraise (ignore);endYou need to have a similar trigger forBefore insert on subcontractsBefore update on mailedBefore update on subcontracts

1. Database management systems [4] (a) Briefly describe two of the limitation of file systems compared to database management systems. They do not directly support querying and modifying data Their support for database creation is limited to creati