cmsc424 database design n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
CMSC424: Database Design PowerPoint Presentation
Download Presentation
CMSC424: Database Design

Loading in 2 Seconds...

play fullscreen
1 / 18

CMSC424: Database Design - PowerPoint PPT Presentation


  • 120 Views
  • Uploaded on

CMSC424: Database Design. Instructor: Amol Deshpande amol@cs.umd.edu. Today. Mapping relational algebra to SQL Complex SQL Queries See “movies database sql queries” on class webpage for the actual queries SQL Formal Semantics Advanced SQL Features. SQL Query Examples.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'CMSC424: Database Design' - loki


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
cmsc424 database design

CMSC424: Database Design

Instructor: Amol Deshpande

amol@cs.umd.edu

today
Today
  • Mapping relational algebra to SQL
  • Complex SQL Queries
    • See “movies database sql queries” on class webpage for the actual queries
  • SQL Formal Semantics
  • Advanced SQL Features
sql query examples
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
sql query examples1
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
more sql
More SQL
  • Select *

into temptable

from X1, …

  • Having
    • WHERE is to FROM what HAVING is to GROUPBY
duplicates
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
formal semantics of sql
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
formal semantics of sql ra
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) =

formal semantics of sql ra1
Formal Semantics of SQL: RA*
  • r* s : additive union
  • *

=

r

s

  • r-* s: bag difference

e.g: r -* s = s -* r =

formal semantics of sql ra2
Formal Semantics of SQL: RA*
  • r* s: cartesian product

=

*

formal semantics of sql1
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)

set bag operations revisited
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)

slide14
Next
  • Database updates
modification of the database deletion
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)
example query
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)
modification of the database insertion
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)
modification of the database updates
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