cmsc424 review n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
CMSC424: Review PowerPoint Presentation
Download Presentation
CMSC424: Review

Loading in 2 Seconds...

play fullscreen
1 / 18

CMSC424: Review - PowerPoint PPT Presentation


  • 154 Views
  • Uploaded on

CMSC424: Review. Database Management Systems. Manage data Store data Update data Answer questions about the data. What kind of data ?. Enterprise data Banking Supermarkets, Sales Airlines Universities Manufacturing Human resources More recent: Semi-structured Data (XML)

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: Review' - arvid


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
database management systems
Database Management Systems

Manage data

  • Store data
  • Update data
  • Answerquestions about the data
what kind of data
What kind of data ?
  • Enterprise data
    • Banking
    • Supermarkets, Sales
    • Airlines
    • Universities
    • Manufacturing
    • Human resources
  • More recent:
    • Semi-structured Data (XML)
    • Scientific data
    • Biological data
    • Sensor network data etc etc…
na ve solutions
Naïve solutions
  • Don’t offer:
    • Consistency
      • Atomicity, durability etc
    • Concurrency
    • Declarative data retrieval
    • Control of redundancy
    • Dynamic data evolution
slide5
DBMS
  • Database Management Systems provide
    • Data abstraction
      • Key in evolving systems
      • Probably the most important purpose of a DBMS
      • Goal: Hiding low-level details from the users of the system
    • Guarantees about data integrity
      • In presence of concurrent access, failures…
    • Speed !!
data abstraction

View Level

View 1

View 2

View n

Data Abstraction

What data users and application programs see ?

Logical

Level

What data is stored ?

describe data properties such as

data semantics, data relationships

Physical

Level

How data is actually stored ?

e.g. are we using disks ? Which

file system ?

dbms at a glance
DBMS at a Glance
  • Data Modeling
  • Data Retrieval
  • Data Storage
  • Data Integrity
data modeling
Data Modeling
  • A data model is a collection of concepts for describing data properties and domain knowledge:
    • Data relationships
    • Data semantics
    • Data constraints
  • We discussed two models:
    • Entity-relationship Model
      • Diagrammatic representation
      • Easier to work with
      • Syntax not important, but remember the “meaning”
      • Remember what you can model
    • Relational Model
      • Only one abstract concept
      • Closer to the physical representation on disk
      • Normalization
data retrieval
Data Retrieval
  • Query = Declarative data retrieval program
    • describes what data to acquire, not how to acquire it
    • Non-declarative:
      • scan the accounts file
      • look for number 55 in the 2nd field
      • subtract $50 from the 3rd field
    • Declarative (posed against the tables abstraction):

update accounts

set balance = balance - 50

where acct_no = 55

  • Why ?
    • Easier to write
    • More efficient to execute
      • Database system can decide how to execute it
data storage
Data Storage
  • Where and how to store data ?
    • Main memory ?
      • What if the database larger than memory size ?
    • Disks
      • We discussed properties of disks
      • RAID
      • How to move data between memory and disk ?
        • Buffer Management
          • LRU, MRU, Clock
    • Indexes
      • Closely tied to data retrieval
      • B+-trees, Hashing
data integrity
Data Integrity

Manage concurrency and crashes

    • Transaction: A sequence of database actions enclosed within special tags
    • Properties:
      • Atomicity: Entire transaction or nothing
      • Consistency: Transaction, executed completely, take database from one consistent state to another
      • Isolation: Concurrent transactions appear to run in isolation
      • Durability: Effects of committed transactions are not lost
    • Consistency: Transaction programmer needs to guarantee that
      • DBMS can do a few things, e.g., enforce constraints on the data
    • Rest: DBMS guarantees
  • Haven’t covered in class yet
data integrity1
Data Integrity
  • Semantic constraints
    • Typically specified at the logical level
    • E.g. balance > 0
  • Assert statements
  • Functional dependencies
    • kinda
dbms at a glance1
DBMS at a glance
  • Data Models
    • Conceptual representation of the data
  • Data Retrieval
    • How to ask questions of the database
    • How to answer those questions
  • Data Storage
    • How/where to store data, how to access it
  • Data Integrity
    • Manage crashes, concurrency
    • Manage semantic inconsistencies
  • Not fully disjoint categorization !!
sql assignment
SQL Assignment

Report the home run champs in the last

three years (2002 to 2004).

select h.year, firstname, lastname, h.hrs

from playerinfo p, hitting h

where p.playerid = h.playerid and

h.hrs = (

select max(hrs)

from hitting h2

where h.year = h2.year);

sql assignment1
SQL Assignment

Report the last name of the batter who

would be reported first in alphabetical order.

select firstname, lastname

from playerinfo

where firstname <= all (select firstname from playerinfo)

and

lastname <= all (

select lastname

from playerinfo p2

where p2.firstname = playerinfo.firstname);

sql assignment2
SQL Assignment

20 Create the dream NL batting team (that will

have the most total RBI) from 2004 statistics.

Remember, a NL team consists of 1 LF, 1CF,

1RF, 1 SS, 1 2B, 1 3B, 1 1B, 1 Catcher,

and 1 Pitcher. Only consider the position at

which the hitter played the maximum number of

games; so a player will only qualify at one

fielding position. Break Ties arbitrarily.

slide17

create table rbistable as

select p.firstname, p.lastname, p.playerid, h.rbis, f.pos

from playerinfo p, hitting h, fielding f

where p.playerid = h.playerid and f.playerid = h.playerid and

f.year = 2004 and h.year = 2004

and f.numgames = (select max(f2.numgames)

from fielding f2

where f2.playerid = f.playerid and f2.year = f.year);

create table rbistable2 as

select firstname, lastname, pos, rbis, playerid

from rbistable r1

where r1.rbis = (select max(r2.rbis) from rbistable r2

where r2.pos = r1.pos);

select firstname, lastname, pos, rbis from rbistable2 r

where playerid <= all (select playerid from rbistable2 r2

where r2.pos = r.pos);

slide18

22 Rank the 2004 teams by their number of wins.

The output should contains a table with two

columns: Team Name, and Rank (between 1 and 30),

and it should be sorted by Rank. Two teams

with same number of wins will be ranked the

same, and the next rank will be skipped in

that case.

select t1.teamname, t1.wins, 31 - count(t2.teamname) as rank

from teams t1, teams t2

where t1.year = 2004 and t1.year = t2.year and

t1.losses <= t2.losses

group by t1.teamname, t1.wins

order by rank;