Docsity
Log inSign up
Docsity
Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity

Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips
Guidelines and tips
Sell on Docsity
Sell on Docsity
Log inSign up

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search Store documents

The best documents sold by students who completed their studies

Search through all study resources
Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents
download point icon20 Points

For each uploaded document

Answer questions
download point icon5 Points

For each given answer (max 1 per day)

All the ways to get free points
Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

University Rankings

Discover the best universities in your country according to Docsity users

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

From our blog

Exams and Study
Go to the blog

Final Exam for Advanced Database Systems | CSE 880, Exams of Deductive Database Systems

Michigan State University (MSU)Deductive Database Systems
Prof. Sakti Pramanik

Material Type: Exam; Professor: Pramanik; Class: Advanced Database Systems; Subject: Computer Science & Engineering; University: Michigan State University; Term: Spring 2008;

Typology: Exams

Pre 2010
On special offer
30 Points
Discount

Limited-time offer

Uploaded on 07/22/2009

koofers-user-dws
koofers-user-dws 🇺🇸

10 documents

1 / 5

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
Michigan State University
Department of Computer Science & Engineering
CSE 880 FINAL EXAMINATION
April 27, 2008
Due: Wednesday, April 30 by 9:00 a.m.
Submit your answers in hard copy and slide it under my office door.
IMPORTANT: You MUST NOT discuss this exam with
anybody else. If you think that a question is not clearly stated, you
must not talk to another fellow student or any one else for clarifi-
cation. Instead, you make your own assumptions and answer the
question based on those assumptions. Write those assumptions in
your answer.
1. Use the relations
Student table: s(sno, name)
Student Course table: sc(sno, cno, grade)
to write oracle triggers for the following:
(a) (10 points) When student grades are changed and the change is more that 25%,
insert the changes into a table named Notify.
(b) (20 points) Those triggers that maintain a materialized view for average grades
of students in a university.
2. (a) (10 points) Assume a polygon <(2,2),(5,1),(9,4),(14,3),(20,10),(2,2) >. Using
the following formula for area computation of a triangle (based on cross product of
two vectors), compute the area of the polygon. Give the details of your approach.
Area(P1, P2, P3)= (x1y2-x2y1+x3y1-x1y3+x2y3-x3y2)/2
where P1=(x1,y1), P2=(x2,y2), P3=(x3,y3) and < P 1, P 2, P 3>are in counter
clockwise order.
(b) (10 points) Based on the above formula of area, show if the point C(3,8) is located
on the left side or the right side of the vector < A(2,2), B(7,10) >. Assume the
vector direction is from A to B.
(c) (10 points) Using the properties above show if the following two line segments
intersect or not. Show details of your approach.
Line segment 1: <(1,1),(10,10) >
Line segment 2: <2,5),(5,2) >
1
pf3
pf4
pf5
Discount

On special offer

Partial preview of the text

Download Final Exam for Advanced Database Systems | CSE 880 and more Exams Deductive Database Systems in PDF only on Docsity!

Michigan State University

Department of Computer Science & Engineering

CSE 880 FINAL EXAMINATION

April 27, 2008

Due: Wednesday, April 30 by 9:00 a.m.

Submit your answers in hard copy and slide it under my office door.

IMPORTANT: You MUST NOT discuss this exam with

anybody else. If you think that a question is not clearly stated, you

must not talk to another fellow student or any one else for clarifi-

cation. Instead, you make your own assumptions and answer the

question based on those assumptions. Write those assumptions in

your answer.

  1. Use the relations Student table: s(sno, name) Student Course table: sc(sno, cno, grade) to write oracle triggers for the following:

(a) (10 points) When student grades are changed and the change is more that 25%, insert the changes into a table named Notify. (b) (20 points) Those triggers that maintain a materialized view for average grades of students in a university.

  1. (a) (10 points) Assume a polygon < (2, 2), (5, 1), (9, 4), (14, 3), (20, 10), (2, 2) >. Using the following formula for area computation of a triangle (based on cross product of two vectors), compute the area of the polygon. Give the details of your approach. Area(P1, P2, P3)= (x1y2-x2y1+x3y1-x1y3+x2y3-x3y2)/ where P1=(x1,y1), P2=(x2,y2), P3=(x3,y3) and < P 1 , P 2 , P 3 > are in counter clockwise order. (b) (10 points) Based on the above formula of area, show if the point C(3,8) is located on the left side or the right side of the vector < A(2, 2), B(7, 10) >. Assume the vector direction is from A to B. (c) (10 points) Using the properties above show if the following two line segments intersect or not. Show details of your approach. Line segment 1: < (1, 1), (10, 10) > Line segment 2: < 2 , 5), (5, 2) >

(d) (10 points) Consider the SegmentIntersectionTest algorithm of page 180 of Rigaux,

et. al.. What is the purpose of the Above and Below funtions in the algorith.

If there are n line segments (total for all polylines) what is the order of computa- tional time complexity for detecting a segment intersection (using sweep-line). (e) Consider the algorithm SegmentIntersection of page 182 for finding all segment intersections.

i. (10 points) What is the purpose of the AddInter function?

ii. (10 points) If there are n line segments with k intersections, what is the total

number of entries inserted into the evnt list ε. What is the time complexity

of the algorithm. Give a short justification.

  1. (a) Given the schema:

RIVER(NAME:char, FLOOD-PLAIN:polygon, GEOMETRY:linstring) ROAD(ID:char, NAME:char, TYPE:char, GEOMETRY:linstring) FOREST(NAME:char, GEOMETRY:polygon) LAND-PARCELS(ID:integer, GEOMETRY:polygon, COUNTY:char)

Transform the following two queries into SQL using the OGIS specified data types and operations: i. (10 points) Name all rivers that cross Itasca State Forest. ii. (10 points) No urban development is allowed within two miles of the Red River and five miles of the Big Tree State Park. Identify the land parcels and the county they are in that cannot be developed.

  1. (a) (20 points) Write Oracle SQL to retrieve the first 10 nearest neighbors (photo id and photo) of a query image from the table stockphotos created as follows: CREATE TABLE stockphotos (photo_id NUMBER, photographer VARCHAR2(64), annotation VARCHAR2(255), photo ORDSYS.ORDImage, photo_sig ORDSYS.ORDImageSignature);

Following is an example of computing the weighted sum of distances between the query image and a database image.

ORDSYS.ORDImageSignature.evaluateScore(DBimage_sig,QRYimage_sig, ’color=1.0,texture=0,shape=0,location=0’);

(b) (5 points) Describe briefly the type of performance that can be mesured for the above retrival system using recall and precision.

  1. Following table lists the transactions for sales of items A, B, C, D. Each row of the table indicates the particular items sold in a transaction. 1 in the entry means the item

(c) (20 points) Relation scheme for Sales table is given below: Sales(Sale-id, prod-id, product-name, product-category, product-warehouse, warehouse- loc, store-id, loc-id, prod-price, qty-sold, store-city, store-state, store name, rep- sOnQuota, RepsOnNoQuota, Hr, day, month, yr) i. Decompose the above table into 3NF. Assume appropriate functional depen- dencies. ii. Decompose the above table into forms (i.e., tables corresponding to STAR Schema) for data warehousing applications. (d) (15 points) Answer the following in SQL for the above relations. If you are not able to do it by using regular SQL, define additional features for SQL as appropriate. i. For each state, average sales for reps who are on quota vs. average sales for re ps who are not on quota. ii. Last year’s vs. this year’s total sales for each product. iii. For each state, show the top 10 customers based on total sale for 2007.

  1. Let us consider a relation r(A,B,D,E) where the domain of each attribute is the positive integers. Assume r always satisfies the condition (constraints) C0 where C0 is A ≤ B ∧ B ≤ D ∧ D ≤ E Let C1 be A < B ∧ B < D C2 be A = D C3 be A < D C4 be A 6 = B ∨ B 6 = D C5 be B < E

(a) (15 points) Which of the following fragmentation schemes are valid for r? i. {C 1 , C 2 } ii. {C 2 , C 3 } iii. {C 1 , C 4 , C 5 } iv. {C 2 , C 4 , C 5 } (b) (10 points) Let s1,s2,s3,s4 be the fragments for r corresponding to the fragmenta- tion conditions C1, C2, C3,C4, respectively. Give minimal sets of fragments that can be used to evaluate σC (r) for the following choices of C: i. A 6 = B ∧ B = D ii. B < D ∧ D < E

  1. Consider the following two tables stored at two different sites:

Supplier (at site 1) Supp − part (at site 2)

Sno Sna Sadr Sno Pno

s1 sn1 sa1 s1 p s2 sn2 sa2 s1 p s3 sn3 sa3 s2 p s4 sn4 sa4 s2 p s5 sn5 sa5 s2 p s5 p

Assume the following sizes for the attributes: Sno: 4 bytes, Sna: 100 bytes, Sadr: 100 bytes, Pno: 4 bytes. Give the number of bytes that are needed to be transferred between the two sites to process the following query given in relational algebra. Assume that the query has originated at site 2 and the answer should be presented at site 2. (Supplier ./ Supp − part)

(a) (10 points) Using regular join (b) (10 points) Using semijoin