CS 440 Database Management Systems

CS 440 Database Management Systems 2 : Review of Relational Model and SQL

Announcement • Regularly check the announcements on the course website: • Homework, projects, …

Relational Database Management Physical Storage Conceptual Design Schema Relational Model Entity Relationship(ER) Model Files and Indexes

Relational Database Management Relational Model & SQL Physical Storage Conceptual Design Schema Relational Model Entity Relationship(ER) Model Files and Indexes

Relational Model • Relational model defines data organization and data retrieval/manipulation operations • It is easier to implement than ER model • It captures more details about the data

An Example Relation name Attribute names Book: Title Price Category Year MySQL $102.1 computer 2001 Cell biology $201.69 biology 1954 French cinema $53.99 art 2002 NBA History $63.65 sport 2010 tuples

Relational Model • Attributes • Atomic values • atomic types: string, integer, real, date, … • Each relation must have keys • Attributes without duplicate values • A relation does not contain duplicate tuples. • Reordering tuples does not change the relation. • Reordering attributes does not change the relation.

Database Schema vs. Database Instance • Schema of a Relation • Names of the relation and their attributes. • E.g.: Person (Name, Address, SSN) • Types of the attributes • Constraints on the values of the attributes • Schema of the database • Set of relation schemata • E.g.: Person (Name, Address, SSN) Employment(Company, SSN)

Database Schema vs. Database Instance • Schema: Book(Title, Price, Category, Year) • Instance: Title Price Category Year MySQL $102.1 computer 2001 Cell biology $201.69 biology 1954 French cinema $53.99 art 2002 NBA History $63.65 sport 2010

SQL • A declarative language for querying data stored in relational databases. • Many standards: SQL92, SQL99, … • We focus on the core functionalities. • Much easier to use than procedural languages. • Say what instead of how

The Basic Form SELECT returned attribute(s) FROM table(s) WHERE conditions on the tuples of the table(s) • Apply the WHERE clause’s conditions on all tuples in the tables in the FROM clause. • Return the values of the attributes in the SELECT clause. One or more

Example Schema Beers(name, manf) Bars(name, addr, license) Drinkers( name,addr, phone) Likes(drinker, beer) Sells(bar, beer, price) Frequents(drinker, bar)

Single Relation Query What beers are made by Anheuser-Busch? SELECT name FROM Beers WHERE manf = ‘Anheuser-Busch’; name Bud Bud Lite

Using * What beers are made by Anheuser-Busch? SELECT * FROM Beers WHERE manf = ‘Anheuser-Busch’; name manf Bud Anheuser-Busch Bud Lite Anheuser-Busch

WHERE clause • May have complex conditions • Logical operators: OR, AND, NOT • Comparison operators: <, >, =, … • Types specific operators: LIKE, …

Null Values • Some tuples may not contain any value for some of their attributes • The operator did not enter the data • The operator did not know the value • … • Ex: We do not know Fred’s salary. • Put 0.0 Fred is not on unpaid leave! • Databases use Null value for these cases

A value not like any other value! • A tuple in Sells relation: bar beer price Joe’s Bar Bud NULL SELECT * FROM Sells WHERE price < 0.0 OR price >= 0.0 Does not return Joe’s Bar.

A value not like any other value! • A tuple in Sells relation: bar beer price Joe’s Bar Bud NULL SELECT * FROM Sells WHERE price IS NULL

Multi Relation Query: Join • Find relations between different types of entities: have more business value! • Ex: Using relations Likes(drinker, beer) and Frequents(drinker, bar), find the beers liked by at least one person who frequents JoeBar. SELECT Likes.beer FROM Likes, Frequents WHERE Frequents.bar = ‘Joe Bar’ AND Frequents.drinker= Likes.drinker;

Join Queries • Generally, require processing large number of tuples  time consuming. • Relational Database Management Systems (RDBMS) have ways to process them efficiently • We talk more about this later in the course

Subqueries • SQL queries that appear in WHERE or FROM parts of another query. • Ex: Using Sells(bar, beer, price), find the bars that serve Miller for the same price Joe charges for Bud. • Figure out Joe’s price for Bud : JoePrice • Find bars that offer Miller at price JoePrice

Subqueries SELECT bar FROM Sells WHERE beer=‘Miller’ AND price= (SELECT price FROM Sells WHERE bar= ‘Joe Bar’ AND beer = ‘Bud’); Subquery

Subqueries: ALL, ANY • We like to compare a value to a set of values • Ex: Using Sells(bar, beer, price), find the bars that serve Miller for a cheaper price than the price that every bar charges for Bud. • Figure out the set of all prices for Bud : BudPrice. • Find the bars that offer Miller at a cheaper price than all values in BudPrice.

Subqueries: ALL, ANY SELECT bar FROM Sells WHERE beer=‘Miller’ ANDprice < ALL (SELECT price FROM Sells WHERE beer = ‘Bud’); Subquery

Subqueries: IN • We like to check if the result of a subquerycontains a particular value. • Ex: Using Beers(name, manf) and Likes(drinker, beer) find the manf of each beer John likes. SELECT manf FROM Beers WHERE name IN (SELECT beer FROM Likes WHERE drinker=‘John’); A set of beers

Subqueries: Exists • We like to check if a subquery has any result. • Ex: Using Beers(name, manf), find the beers that are the only beer made by their manufacturers. SELECT name FROM Beers b1 WHERE NOT EXISTS (SELECT * FROM Beers WHERE manf=b1.manf AND name <> b1.name);

Bag versus Set • Duplicates are allowed in bags • {a, a, b, b, b} vs. {a, b} • Generally, the results of SQL queries are bags SELECT name FROM Beers; name Bud Bud Lite Bud

Removing Duplicates • Use DISTINCT SELECT DISTINCT name FROM Beers; name Bud Bud Lite

Set Operations • R UNION S • Returns the union between tuples of relation R and tuples of relation S. • R INTERSECT S • Returns the tuples common between relation R and relation S. • R EXCEPT S • Returns the tuples found in relation R but not in relation S.

Set Operations: Example • Using relations Likes(drinker, beer), Sells(bar, beer, price), and Frequents(drinker, bar), find the drinkers and beers such that • The drinker likes the beer, and • The drinker frequents at least one bar that sells the beer • “and” shows that we should compute intersection.

Set operations: Example The drinker likes the beer (SELECT * FROM Likes) INTERSECT (SELECT drinker, beer FROM Sells, Frequents WHERE Frequents.bar=Sells.bar); The drinker frequents at the bar that sells the beer

Set Operations • The results of set operations in SQL do not have any duplicate tuples. • We can force them not to remove duplicates by ALL. • .. INTERSECT … .. INTERSECT ALL … • .. UNION …  .. UNION ALL … • .. DIFFERENCE …  .. DIFFERENCE ALL …

People betting on OSU football games Out(Game, Outcome) Bets(Who, Outcome, Game, Amt) Some games have not been played yet: Arizona

Problem 1 List the completed games that nobody bet on.

Problem 1 List the completed games that nobody bet on. (SELECT Game FROM Out) Except (SELECT Game FROM Bets)

Problem 2 Who bet the most money on a single game?

Problem 2 Who bet the most money on a single game? SELECT Who, Amt FROM Bets WHERE Amt >= ALL (SELECT Amt FROM Bets)

Problem 3 List the games that all bettors agree on.

Problem 3 List the games that all bettors agree on. (SELECT Game FROM Bets) EXCEPT (SELECT Bets1.Game FROM Bets Bets1, Bets Bets2 WHERE Bets1.Game = Bets2.Game AND Bets1.Outcome <> Bets2.Outcome)

Aggregation Functions • Compute some value based on the values of an attribute: • COUNT, SUM, AVG, MIN, MAX • Each RDBMS may define additional functions. • Using Bars (name, addr, license), find the number of bars in Oregon. SELECT COUNT(*) FROM Bars WHERE addrLIKE‘%,OR,%’;

Aggregation Functions • Aggregation functions generally do not consider NULL values. SELECT COUNT(*) FROM Bars WHERE addrLIKE‘%,OR,%’; SELECT COUNT(license) FROM Bars WHERE addrLIKE‘%,OR,%’; The number of bars in Oregon The number of licensed bars in Oregon

Grouping Tuples • We like to categorize tuples based on some attributes. • Group the tuples in Sells(bar, beer, price) according to the barand beer. SELECT bar,beer FROM Sells GROUP BYbar,beer;

Grouping Tuples • We can retrieve only some of the groups. SELECT bar,beer FROM Sells WHERE bar=‘Joe Bar’ or bar=‘Red Lion’ GROUP BY bar,beer;

Aggregation Functions for Groups • Using Sells(bar, beer, price) find the minimum price of each beer. • Group tuples in Sells based on beer. • Compute MIN over price for each group.

Aggregation Functions for Groups • Using Sells(bar, beer, price) find the minimum price of each beer. SELECT beer,MIN(price)AS minprice FROM Sells GROUP BY beer;

Grouping Tuples • The attributes in the select clause are: • Aggregated values • Attributes in the Group By clause. SELECT beer, MIN(price),bar FROM Sells GROUP BY beer; Error

Filtering Groups • Selecting groups based on the value of an aggregation function. • Using Sells(bar, beer, price), find the minimum price of each beer whose maximum price is less than 4.

Filtering Groups SELECT beer, MIN(price) FROM Sells WHERE MAX(price) < 4 GROUP BY beer • We cannot use aggregation functions in WHERE clause. Error

CS 440 Database Management Systems