DOC PREVIEW
SJSU CMPE 226 - Midterm Exam Keys

This preview shows page 1 out of 3 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 3 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 3 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

CmpE 226 --- Database Systems Spring 2003Your Name:________________________________ Your Phone: _____________________CmpE 226 DB--Midterm Page 1CmpE 226 --- Database Systems Spring 2003Your Name:________________________________ Your Phone: _____________________1. (5 points) T/F – Which of the following statement is False and which is True:___T___a) A "projection"of a table is a choice of some or all of its columns. ___F___b) Product operands have to be the same set and order of attributes. ___F___c) ALL, BY, AS, CURRENT are SQL commands. ___F___d) ERD supports generalization.___F___e) The following ERD is showing an inclusive relationship that indicates “a Paymentis either for Books or Machines.”2. Define (a) (5 points) data abstraction. Name the four levels of data abstraction.Data abstraction allows us to hide some details of the representation of the data. The four levels of data abstraction are:View level, logical level, constraint level, and physical level.(b) (5 points) Define aggregate functions in SQL. Name five aggregation functions.Max, Min, Avg, Count, and Sum, are aggregate operators.3. Consider the map of California shown in the Figure on the last page of the exam.(a) (5 points) Represent using a constraint relation California(region, x, y) the five regions of the state of California. Use as few constraint tuples as possible.CmpE 226 DB--Midterm Page 2(b) (5 points) Assume that there are two water reservoirs located at (25, 20) and at (30, 15). Represent using a constraint relation Closer_to_First(x; y) those areas anywhereinside or outside of California that are closer to the first than to the second water reservoir. Use as simple type of constraints as possible.The line through points (25,20) and (30,15) is y = -x + 45.The midpoint of these is (55/2, 35/2). The line perpendicular toy = -x + 45 and going through the midpoint is y = x - 10. Beingcloser to the first point means being above this line. CLOSER_TO_FIRST ----------------------- X Y ----------------------- x y y > x - 10 -----------------------(c) (5 points) Represent using a constraint relation Distance_to_First(x; y; d) the Euclidean distance d between any point (x, y) and the first reservoir. DISTANCE_TO_FIRST ---------------------------------------- X Y D ---------------------------------------- x y d d^2 = (x-25)^2 + (y-20)^2 ----------------------------------------(d) (5 points) Write a relational algebra query that finds those areas that are either less than 8 units distance from the first reservoir or in region B but not both. Use the relations that you defined above.using | | for project and 6 for select, we define: ___ Within8 <- | | X,Y ( 6 d<=8 DISTANCE_TO_FIRST ) ___ InB <- | | X,Y ( 6 region="B" CALIFORNIA ) Then we have: (Within8 \_/ InB) \ (Within8 /^\ InB)(e) (5 points) Write a SQL query that finds the areas that are less than 3 units distance from the first reservoir and are also closer to the first reservoir than to the second reservoir. Use the relations that you defined above. (Select X, Y From DISTANCE_TO_FIRST where d < 3) Intersect (Select X, Y From CLOSER_TO_FIRST)CmpE 226 DB--Midterm Page 34. (5 points) Suppose a manufacturer produces three types of soft drinks. Each bottle of the first type needs 6 units of sugar, 4 units of coloring, and 3 units of flavoring. Each bottle of the second type needs 5 units of sugar, 3 units of coloring, and 4 units of flavoring. Finally, each bottle of the third type needs 10 units of sugar, 7 units of coloring, and 2 units of flavoring. The manufacturer can use each day at most only 1000 units of sugar, 800 units of coloring, and 500 units of flavoring. Represent as a constraint relation with scheme Produce(x, y, z) all production possibilities where x; y, and z are, respectively, the amount of type one, two, and three soft drinks produced. PRODUCE ---------------------------------------- X Y Z ---------------------------------------- x y z 6x + 5y + 10z <= 1000, 4x + 3y + 7z <= 800, 3x + 4y + 2z <= 500 ---------------------------------------- . 5. (5 points) Prepare an entity-relationship diagram for any of the following problems. Haveat least 7 1:1 and 1:M relationships. M:M relationships are not allowed. Use at least one parallel relationship type. Use fully and partly optional, inclusive, and exclusive relationships and conditions wherever applicable. Name at least 2 to 5 attributes per entity. As you prepare the diagrams, you may add additional entities. [Important: You must satisfy all the requirements in your model.] (a) All the rental properties in your neighborhood. Include the occupied and the vacant properties. Also show how occupancy changes over time. (b) A map of how to get to your home or any other home in your neighborhood from different directions.(c) A campus map. Include the area of the buildings. Also show how the buildings changed over time, i.e., when they were built or expanded in space.(d) A parking lot. Show the parking lot and how cars are entering and leaving the parking lot.(e) A passenger airplane flight scheduler. Include reservations, capacity checkfor the


View Full Document

SJSU CMPE 226 - Midterm Exam Keys

Documents in this Course
SQL-99

SQL-99

71 pages

XML

XML

52 pages

XML

XML

14 pages

Chapter 9

Chapter 9

45 pages

Load more
Download Midterm Exam Keys
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Midterm Exam Keys and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Midterm Exam Keys 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?