1 / 22

CMSC424: Database Design

CMSC424: Database Design. Instructor: Amol Deshpande amol@cs.umd.edu. Today. Movies Database Subqueries A Complex Query Duplicate Semantics Formal semantics of SQL Other advanced features Views Integrity Constraints Transactions Triggers. SQL Query Examples.

Samuel
Download Presentation

CMSC424: Database Design

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. CMSC424: Database Design Instructor: Amol Deshpande amol@cs.umd.edu

  2. Today • Movies Database • Subqueries • A Complex Query • Duplicate Semantics • Formal semantics of SQL • Other advanced features • Views • Integrity Constraints • Transactions • Triggers

  3. SQL Query Examples • Movie(title, year, length, inColor, studioName, producerC#) • StarsIn(movieTitle, movieYear, starName) • MovieStar(name, address, gender, birthdate) • MovieExec(name, address, cert#, netWorth) • Studio(name, address, presC#) • Queries: • Producer with maximum average length of movies • Find producer of Star Wars. • All producers of movies in which harrison ford stars

  4. SQL Query Examples • Movie(title, year, length, inColor, studioName, producerC#) • StarsIn(movieTitle, movieYear, starName) • MovieStar(name, address, gender, birthdate) • MovieExec(name, address, cert#, netWorth) • Studio(name, address, presC#) • Queries: • Find movie titles that appear more than once • Find number of people 3 hops away from Kevin Bacon

  5. More SQL • Select * into temptable from X1, … • Having • WHERE is to FROM what HAVING is to GROUPBY

  6. Duplicates • By definition, relations are sets • So  No duplicates allowed • Problem: • Not practical to remove duplicates after every operation • Why ? • So… • SQL by default does not remove duplicates • SQL follows bag semantics, not set semantics • Implicitly we keep count of number of copies of each tuple

  7. Formal Semantics of SQL • RA can only express SELECT DISTINCT queries • To express SQL, must extend RA to a bag algebra • Bags (aka: multisets) like sets, but can have duplicates e.g: {5, 3, 3} e.g: homes = • Next: will define RA*: a bag version of RA

  8. Formal Semantics of SQL: RA* • *p (r): preserves copies in r e.g: *city = Brighton (homes) = • *A1, …, An (r): no duplicate elimination e.g:  *cname (homes) =

  9. Formal Semantics of SQL: RA* • r* s : additive union • * = r s • r-* s: bag difference e.g: r -* s = s -* r =

  10. Formal Semantics of SQL: RA* • r* s: cartesian product = *

  11. Formal Semantics of SQL • Query: SELECT a1, ….., an FROM r1, ….., rm WHERE p • Semantics: *A1, …, An (*p (r1* … * rm) ) (1) • Query: SELECT DISTINCT a1, ….., an FROM r1, ….., rm WHERE p What is the only operator to change in (1)? • Semantics: A1, …, An (*p (r1* … * rm) ) (2)

  12. Set/Bag Operations Revisited • Set Operations • UNION ≡ U • INTERSECT ≡ ∩ • EXCEPT ≡ - • Bag Operations • UNION ALL ≡ U* • INTERSECT ALL ≡ ∩* • EXCEPT ALL ≡ -* • Duplicate Counting: • Given m copies of t in r, n copies of t in s, how many copies of t in: r UNION ALL s? A: m + n r INTERSECT ALL s? A: min (m, n) r EXCEPT ALL s? A: max (0, m-n)

  13. SQL: Summary

  14. Next… • Views

  15. Views • Provide a mechanism to hide certain data from the view of certain users. To create a view we use the command: • Can be used in any place a normal table can be used • For users, there is no distinction in terms of using it create view v as<query expression> where: <query expression> is any legal expression The view name is represented by v

  16. Example Queries • A view consisting of branches and their customers create view all-customers as(select branch-name, customer-namefrom depositor, accountwhere depositor.account-number = account.account-number) union(select branch-name, customer-namefrom borrower, loanwhere borrower.loan-number = loan.loan-number) Find all customers of the Perryridge branch select customer-namefrom all-customerswhere branch-name = ‘Perryridge’

  17. Views • Is it different from DBMS’s side ? • Yes; a view may or may not be materialized • Pros/Cons ? • Updates into views have to be treated differently • In most cases, disallowed.

  18. Next • Database updates

  19. Modification of the Database – Deletion • Delete all account records at the Perryridge branch • delete from account where branch-name = ‘Perryridge’ • Delete all accounts at every branch located in Needham city. • delete from accountwhere branch-name in (select branch-name from branch where branch-city = ‘Needham’)delete from depositorwhere account-number in (select account-number from branch, account where branch-city = ‘Needham’ and branch.branch-name = account.branch-name)

  20. Example Query • Delete the record of all accounts with balances below the average at the bank. delete from accountwhere balance < (select avg (balance)from account) Problem: as we delete tuples from deposit, the average balance changes Solution used in SQL: • First, compute avg balance and find all tuples to delete • Next, delete all tuples found above (without recomputing avg or retesting the tuples)

  21. Modification of the Database – Insertion • Add a new tuple to account • insert into account values (‘A-9732’, ‘Perryridge’,1200) • or equivalentlyinsert into account (branch-name, balance, account-number) values (‘Perryridge’, 1200, ‘A-9732’) • Add a new tuple to account with balance set to null • insert into account values (‘A-777’,‘Perryridge’, null)

  22. Modification of the Database – Updates • Increase all accounts with balances over $10,000 by 6%, all other accounts receive 5%. • Write two update statements: • update account set balance = balance  1.06 where balance > 10000 • update account set balance = balance  1.05 where balance  10000 • The order is important • Can be done better using the case statement

More Related