Download Entity Relationship -Introduction to Database Systems - Exams and more Exams Introduction to Database Management Systems in PDF only on Docsity!
CS186^ Department of EECS, Computer Science Division^ UNIVERSITY OF CALIFORNIAClass Account: ___________________________ Bohannon/Cooper Spring 2008 First Exam: Introduction to Database Systems February 26, 2008 Instructions: 1. 2. 3. 4. Write your name on each page.Turn in your notes page with your test.There are 100 points total.Please read over the test and plan your time. Best to skip and go
Entity Relationship Graphs (14 pts)^ back if you are stuck on a question.
You have just been hired as a consultant for a big airplane manufacturer. Impressed by your background in databases, they want you to completely redesign their database system. Talking with the people in the company, you get the following information.
- • • The database contains information about employees, factories and parts.Each employee has a social security number (SSN), name and salary. An employee is uniquely identified by his or her SSN.Each factory has an id, name and a budget. The id uniquely
- • • • identifies a project.Each part has an id and a name. The id uniquely identifies a part.Each employee reports to at most one other employee.Each employee works in at least one factoryEach part is manufactured in exactly one factory.. Each part is a A partial Entity-Relationship diagram for the above application is shown on the next page. • add attributes of^ component of zero or more other parts. parts
(^2) • • • add missing relationshipsadd keys of entity setscapture key and participation constraints on relationshipsName:__________________________________
File Organization and Structure (12 pts) Name:__________________________________
a) (6 pts) Consider a large table of students. the table that best suits a given workload. alternatives for implementing an index, and the type of data structures that we have available 1. If 95% of the workload is a query requesting several pieces of You need to build an index forRecall the 3 data entry information about students whose last name begins with a particular letter, and 5% of the workload is adding a new student, but the additions only occur on Saturday. what storage option is appropriate for ‘Students’.
- If the percentage of the workload distribution is reversed and additions could occur any time, would the storage option change? Why?
b) (6 pts) Operating systems provide default file system management. Name two features that DBMS systems require that are not provided by
(^5) the default OS mechanisms, and specify whether they are provided at the file-layer or record-layer.Name:__________________________________
(space for drawing B+T^7 rees) Name:__________________________________
Sorting (14 pts) Name:__________________________________
Answer the following questions about external merge sorts. a. (3 pts) Consider an external sort of a large file that does not fit in memory memory available to the sort process?. What is the main impact of increasing the amount of i. ii. iii. iv. The sort will likely proceed faster because the sort will read less data on each pass.The sort will likely proceed faster because the sort will potentially do fewer passes.The speed of the sort will not be affected.The sort will likely proceed slower because of the need to read ANSWER: _______ b. (3 pts) The primary benefit of double buffering is:^ more data on each pass. ANSWER: _______^ i.^ ii.^ iii.^ iv.^ The ability to sort two files at once.The ability to merge two runs at once.The ability to reduce the number of passes in the sort.The ability to overlap computation and I/O. c. i. ii. (4 pts) After each pass (except pass 0) of a 2-way external merge sort, we have:Half as many runs of data as before the pass, but each run is twice as largeTwice as many runs of data as before the pass, and each run is iii. iv. the same sizeThe same number of runs of data as before the pass, and each run is the same sizeHalf as many runs of data as before the pass, and each run is half as large ANSWER: _______
Aug. 11 Nov. 2 June 29 642216343343121 131515 Name: __________________________________ 1000502 $0.15$0.13$ July 4 For the following queries, we have supplied some of the values for the results of the query. Fill in the missing values. Assume set semantics. 43121 15 1 $ a pts) NAME Roofing nail πNAME,QUANTITY (^) (σCATEGORY=’Nails’ ( Products (^) QUANTITY 100 Sales ) ) ( Drywall nail b. πNAME,MANAGER,MSRP,PRICE (σMSRP>PRICE( Products^1000 Sales Stores ) ) ( 5pts) NAME Table saw Hammer (^) MANAGER Naomi Smith (^) MSRP $15 PRICE $0.11$210$ c. π MANAGER MANAGER ( ( πSTORE_ID (Stores) - πSTORE_ID (Sales) ) Stores ) (4 pts) d. S1 = ρ (Sales1(DATE1, PRODUCT_ID1, STORE_ID1, QUANTITY1, PRICE1),Sales) S2 = ρ (Sales2(DATE2, PRODUCT_ID2, STORE_ID2, QUANTITY2, PRICE2),Sales) (5 pts) π PRODUCT_ID1 43121 PRODUCTID1,PRICE1,PRICE2 ( σDATE1 <> DATE2 PRICE1 $15 ( σ PRODUCT_ID1=PRODUCT_ID2 PRICE2 $12 S1 × S2 ) ) 64221 $0.11 $0.
Name:__________________________________
Relational Calculus In this section, Assume there is an single attribute, NAME, and that there is foreign key constraint from Store.MANAGER to Manager.NAME. additional table , Manager, with a
a. Write a (tuple) Relational Calculus expression to return Sales tuples indicating that the Shady Lane, Austin store sold more than 50 Hammers. PRODUCT_ID and STORE_ID and avoid joins. (4 pts) Your answer can use the values shown above to get b. Complete the following expression to return managers that only manage stores in the “WEST” region (4 pts) M | M c. (8 pts)∈ Manager ∧ ∀_____________________________________ Write a Relational Calculus expression to find managers whose stores all either 1) sold more than 1000 of some (single) “Nail” product on some day or 2) sold a power tool costing over $100.
Name:__________________________________ c. (6 pts) with an endangered species in a region. Complete the following XQuery that lists species that competes for food { for $r in doc/eco/region, $s in $r/species where $s/eats/animal = $r/species_______________________________________ $r/species_______________________________________ } return { $s/@name }^ or $s/eats/vegetable =
