1 / 14

Deductive Databases

Deductive Databases. General idea: some relations are stored (extensional), others are defined by datalog queries (intensional). Many research projects (MCC, Stanford, Wisconsin) [Great Ph.D theses!] SQL3 realized that recursion is useful, and added linear recursion.

victoriathompson
Télécharger la présentation

Deductive Databases

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Deductive Databases • General idea: some relations are stored (extensional), others are defined by datalog queries (intensional). • Many research projects (MCC, Stanford, Wisconsin) [Great Ph.D theses!] • SQL3 realized that recursion is useful, and added linear recursion. • Hard problem: optimizing datalog performance. • Ideas from ddb’s made it into practice (recursion, magic sets, pred. movearound).

  2. SQL: Constraints and Triggers • Chapter 6 Ullman and Widom • Certain properties we’d like our database to hold • Modification of the database may break these properties • Build handlers into the database definition\ • Key constraints • Referential integrity constraints.

  3. Keys: Fundamental Constraint • In the CREATE TABLE statement, use: • PRIMARY KEY, UNIQUE CREATE TABLE MovieStar ( name CHAR(30) PRIMARY KEY, address VARCHAR(255), gender CHAR(1)); • Or, list at end of CREATE TABLE PRIMARY KEY (name)

  4. Keys... • Can use the UNIQUE keyword in same way • …but for any number of attributes • foreign key only reference PRIMARY KEY • Indexing Keys CREATE UNIQUE INDEX YearIndex ON Movie(year) • Makes insertions easier to check for key constraints • Subtle differences between PRIMARY KEY and UNIQUE

  5. Foreign Key Constraints ActedIn(ActorName, MovieName) Movies(MovieName, year) If MovieName in ActedIn is a foreign key for Movies, then: 1) Foreign Key must be a reference to a valid value in the referenced table. 2) … must be a PRIMARY KEY in the referenced table. Yes, this is a referential integrity constraint.

  6. Declaring Foreign Key Constraints • REFERENCES keyword... CREATE TABLE ActedIn ( Name CHAR(30) PRIMARY KEY, MovieName CHAR(30) REFERENCES Movies(MovieName)); • Or, summarize at end of CREATE TABLE FOREIGN KEY MovieName REFERENCES Movies(MovieName) • MovieName must be a PRIMARY KEY

  7. How do we Maintain them? • Given a change to DB, there are several possible violations: • Insert new tuple with bogus foreign key value • Update a tuple to a bogus foreign key value • Delete a tuple in the referenced table with the referenced foreign key value • Update a tuple in the referenced table that changes the referenced foreign key value

  8. How to Maintain? • Recall, ActedIn has FK MovieName... Movies(MovieName, year) (Fatal Attraction, 1987) ActedIn(ActorName, MovieName) (Michael Douglas, Fatal Attraction) insert: (Rick Moranis, Strange Brew)

  9. How to Maintain? • Policies for handling the change… • Reject the update (default) • Cascade (example: cascading deletes) • Set NULL • Can set update and delete actions independently in CREATE TABLE MovieName CHAR(30) REFERENCES Movies(MovieName)) ON DELETE SET NULL ON UPDATE CASCADE

  10. Midterm: BCNF • Decompose R(A,B,C,D,E) with functional dependencies AB, BC, CD into BCNF.

  11. Midterm: SQL 1 • For each book that has been both borrowed and requested, give the phone number of the borrower and the requester Select b.phone_number, r.phone_number From Library_Patron as b, Library_Patron as r, Book_Request as br, Borrowed as bo Where b.card_number = borrowers_card_number and r.card_number = requesters_card_number and bo.book_ISBN = br.book_ISBN

  12. Midterm SQL: 2 • How many times have books titled • A First Course in Database Systems been borrowed? • Select count(*) • From Borrowed, Book • Where Borrowed.book_ISBN = Book.ISBN • And Book.title = ‘A First Course in Database Systems’

  13. Midterm SQL: 3 • The set of pairs (a, n), where n is the number of times • that Alon Levy has borrowed a book authored by a. • Select count(book_ISBN), author • From Borrowed, Book, Library_Patron • Where Borrowed.book_ISBN = Book.ISBN • And Library_Patron.first_name = ‘Alon’ • And Library_Patron.last_name = ‘Levy’ • And Borrowed.borrowers_card_number = • Library_Patron.card_number • Group by author

  14. Midterm SQL: 4 • Give a SQL query that produces the set of pairs • (borrower, number) such that "number" is the number • of books borrowed by the "borrower" and that : • Each book has never been borrowed by someone else • The last name of the book's author is alphabetically after borrowers • Select card_number , count(book_ISBN) • From Library_patron, Borrowed, Book • Where card_number = borrowers_card_number and • author > last_name and book_ISBN = ISBN and • book_ISBN not in ( • Select book_ISBN • From Borrowed as borrower1, Borrowed as borrower2, book • Where book_ISBN = ISBN and • Borrower1.borowers_card_number <>borower2.borrowers_card_number) • Group by card_number

More Related