CS240A
Winter 2007
DATA BASES and KNOWLEDGE BASES

Instructor: Carlo Zaniolo


Data Mining Project and Course Final

 

Week 7: O-R DBMS and XML: Read the notes

Sample Midterm

Assignments for Week 5(Due on WD Feb 14--more might be added next monday)

  • Study Chapters 5 and 6 of ADS textbook.

  • Write a recursive SQL query to coalesce the periods after Sal is projected out from
    EHist(Eno, Sal, Title, From, To). Possible solution

  • Solve the same problem in Datalog using double negation. Two possible solutions

  • Give a simpler SQL expression for the temporal joins of ADS example 5.9 (HINT: use a simplified logical expression for when the periods overlap, and then CASE statement to return the results).Possible solution -same file as SQL coalesce

  • EHist(Eno, Sal, Title, From, To) is actually a concrete view that stores the transaction time history for the relation EMP(Eno, Sal, Title).The concrete view must be maintained by active DB2 rules. Please write those rules. Possible solution.

  • Read up on the following temporal curiosity items:

     

  • Do exercises 6.1 and 6.3 in the ADS textbook. Solutions
  • Study Chapter 11 of ADS textbook till page 285 (Sections 11.1 and 11.2).
  • Do exercise 11.2 in the ADS textbook

Assignments for Week 4 (Due Feb 5)

  • Study sections 3.1, 3.2, 3.3, 3.4 and 4.1 from the ADS book.

  • Read Section 7.1, 7.2, 7.3 of DB2 book

  • Study Sections 2.1, 2.2 and 2.3 of ADS textbook


  • Write an SQL program (and execute it on DB2) to find the cities which are reachable from Austin, with their distance from Austin.
  • Also Find which of the above cities is the furthest from Austin. (You can express this query using stratified negation or using aggregates)
  • Another way to find all the people who are of the same generation as marc is to find his ancestors of level K by assuming that marc is a zero-level ancestor of himself, his father is first level, grandma is second level, and so on. Then starting from any ancestor at level K we find his/her children and decrease the level by one. Let us repeat this operation until we get back to zero, since the people we find at level zero are of the same generation as marc! Please, write this program in SQL and test it in DB2.

Midterm on Feb 21

Assignments for Week 3 (Due Jan 29)

  • Study Section 9.7 of ADS textbook

  • Read Sections 1--3 of DB2 book

  • Study Section 5.8 of DB2 textbook

  • Study Sections 2.1, 2.2 and 2.3 of ADS textbook

  • Download and install DB2!
    .
  • Express in LDL++ the recursive queries at pages 253 and 262 of the DB2 book (you can also find them in the following text file)

Assignments for Week 2 (Due Jan 29)

  • In the ADS book, study Sections 9.1--9.3 and Section 9.5 till 9.5.3 (excluded).

  • Another way to find all the people who are of the same generation as marc is to find his ancestors of level K by assuming that marc is a zero-level ancestor of himself, his father is first level, grandma is second level, and so on. Then starting from any ancestor at level K we find his/her children and decrease the level by one. Let us repeat this operation until we get back to zero, since the people we find at level zero are of the same generation as marc! Please, write and test this program in LDL++.
    - ----------------You can find solutions for this problem here--------------

  • Assume that you have facts as in Example 8.22. Write an LDL++ program to compute the number of suppliers for each part, and to find the part that has the largest number of suppliers. Finally explain how your rules are implemented by a Datalog compiler (i.e., will the differential fixpoint be used? What are the re-written rules? Will the magic set or the left/right linear recursion method be used, and if so, what are the rewritten rules?

    ----------You can find solutions for this problem here--------------

Assignments for Week 1 (Due on 1/17/2007)

    • Study Sections 8.1--8.5 of ADS textbook.

    • Do Exercises 8.1, 8.2, 8.5, 8.7 from the Advanced Database Systems (ADS) textbook.
  • Study Sections 8.6--8.7 of ADS textbook.


  • Using the tables/facts in example1.fac of the LDL++ tutorial write and run following queries:
  1. Write an LDL++ program to find the cities which are reachable from Austin, with their distance from Austin.
  2. Find which of the above city is the furthest from Austin. (You can express this query using stratified negation.)
  3. Write an LDL++ program to add up the population of cities in Texas. (Here you need recursive rules that exploit the lexicographical order of city names.)

You can find solutions for these LDL++ problems here: