COMP9311 Database Systems Lab5
和之前lab的文件复制方式以及数据库构建方法一样，本文不再赘述。该练习的ER图如下：
ER Design
schema文件如下：
-- Beer rating database
create table Location (
id serial primary key,
state varchar(50), -- not every country has states
country varchar(50) not null
);
create table Taster (
id serial primary key,
family varchar(30) not null,
given varchar(30), -- some people have only one name
livesIn integer not null references Location(id)
);
create table Brewer (
id serial primary key,
name varchar(50) not null,
locatedIn integer not null references Location(id)
);
create table BeerStyle (
id serial primary key,
name varchar(30) not null
);
create table Beer (
id serial primary key,
name varchar(50) not null,
style integer not null references BeerStyle(id),
brewer integer not null references Brewer(id)
);
create table Ratings (
taster integer not null references Taster(id),
beer integer not null references Beer(id),
score integer not null
constraint validRating
check (score >= 1 and score <= 5)
);
Q1
Find out who made what ratings.
If you look at the Ratings table, you'll see that it contains just a bunch of numbers. This is good for being compact, but hard to work out what it all means. Write an SQL query that will put together the data from the Ratings table with the Taster and Beer tables so that you get a better idea of who rated which beer. The result should display the taster's name (given name only), along with the name of the beer, its brewer and the rating. Order the table by the tasters' names; for a given taster, sort in descreasing order of rating (i.e., highest-rated beer first).
When you invoke the view, you should see output that looks like this:
taster | beer | brewer | rating
--------+------------------------+--------------------------+--------
Adam | Old | Toohey's | 4
Adam | Victoria Bitter | Carlton and United | 1
Adam | New | Toohey's | 1
Geoff | Redback | Matilda Bay Brewing | 4
Geoff | James Squire Pilsener | Maltshovel Brewery | 4
Geoff | Empire | Carlton and United | 3
Hector | Sierra Nevada Pale Ale | Sierra Nevada | 4
Hector | Fosters | Carlton and United | 3
Jeff | Sierra Nevada Pale Ale | Sierra Nevada | 4
Jeff | Burragorang Bock | Scharer's Little Brewery | 3
Jeff | Rasputin | North Coast Brewing | 1
John | Sierra Nevada Pale Ale | Sierra Nevada | 5
John | 80/- | Calendonian Brewing | 4
John | Rasputin | North Coast Brewing | 4
John | Empire | Carlton and United | 3
John | Chimay Red | Chimay | 3
John | Crown Lager | Carlton and United | 2
John | Victoria Bitter | Carlton and United | 1
Peter | XXXX | Castlemaine/Perkins | 5
Raghu | Old Tire | New Glarus Brewing | 5
Raghu | Sierra Nevada Pale Ale | Sierra Nevada | 3
Raghu | Rasputin | North Coast Brewing | 3
Ramez | Sierra Nevada Pale Ale | Sierra Nevada | 4
Ramez | Bigfoot Barley Wine | Sierra Nevada | 3
Rose | Redback | Matilda Bay Brewing | 5
Sarah | Burragorang Bock | Scharer's Little Brewery | 4
Sarah | James Squire Amber Ale | Maltshovel Brewery | 3
Sarah | James Squire Pilsener | Maltshovel Brewery | 3
Sarah | Old | Toohey's | 3
Sarah | Scharer's Lager | Scharer's Little Brewery | 3
Sarah | New | Toohey's | 2
Sarah | Victoria Bitter | Carlton and United | 1
(32 rows)
SELECT t.given, b.name, br.name, r.score
FROM Taster t, Beer b, Brewer br, Ratings r
WHERE t.id = r.taster AND r.beer = b.id AND b.brewer = br.id
ORDER BY t.given, r.score desc;
--问题要求涉及到schema的4个table，用where限制条件把4个table联系起来即可。
--order by默认升序ascd，可以不写，降序要写desc。
--为了方便后续查询，可以写成view
create or replace view AllRatings(taster,beer,brewer,rating)
as
SELECT t.given, b.name, br.name, r.score
FROM Taster t, Beer b, Brewer br, Ratings r
WHERE t.id = r.taster AND r.beer = b.id AND b.brewer = br.id
ORDER BY t.given, r.score desc;
Q2
Find out what is John's favourite beer.
Clearly, you can work out the answer to this once you've solved the query above. However, try to write a query that returns a single tuple with the name of the beer(s) and brewer(s) for the beer(s) that John Shepherd rates highest.
When you invoke the view, you should see output that looks like this:
beers=# select * from JohnsFavouriteBeer ;
brewer | beer
---------------+----------
Sierra Nevada | Pale Ale
(1 row)
SELECT br.name as brewer, b.name as beer
FROM Beer b, Brewer br, Ratings r, Taster t
WHERE r.score = (SELECT max(r.score) FROM Ratings r, Taster t WHERE t.given = 'John' AND t.id = r.taster)
AND r.beer = b.id AND r.taster = t.id AND b.brewer = br.id AND t.given = 'John';
--先写子查询找到John评分最高的啤酒分数，再限定分数为最高分数对应的brewer和beer
--如果延续Q1的view可以写成
CREATE OR REPLACE VIEW JohnsFavoriteBeer(brewer, beer) AS
SELECT brewer, beer
FROM AllRatings
WERHER taster = 'John' AND
rating = (SELECT max(rating) FROM AllRatings WHERE taster = 'John');
Q3
Find out anyone's favourite beer.
The above view seems to provide a useful operation, but seems a little restrictive. Surely I don't always want to know what is John's favourite beer. Maybe I want to know what is Adam's favourite beer, or Sarah's favourite beer. The queries to do this would be almost the same as the one used in the view above, but with the name changed. Which naturally raises the question Can view definitions be parameterised?. The answer is No in standard SQL, but PostgreSQL provides functions, which can be used to implement something like this.
PostgreSQL functions can be defined in a number of languages, including SQL. An SQL function typically contains a single SQL query, into which parameters to the function can be interpolated, thus providing a parameterisable query. (Note that SQL functions can contain an arbitrary sequence of SQL statements, including updates, separated by semi-colons. The result of such a function is the the result of the last SQL statement.)
For the favourite beer example, define a function which takes as input the complete name of a taster (as a text string) and returns one or more tuples containing the name of the brewer and the beer, as in the above example.
Why "one or more tuples"? Maybe a taster has several equally favourite beers. Always consider this possibility when faced with an information request like "Find the largest ..." or "Find the most expensive ..."; there may be a number of equally large/expensive things in the database. Also, do not assume that a given taster's maximum rating will be equal to 5; there may be people who rate beers harshly and never give a better rating than 4.
Make sure that you test your function on all tasters, to ensure that it's working properly for the people who have several equal favourite beers, or those who haven't given a rating of 5.
Before we can define a function, we need to define a type for the return tuples. Note that this happens automatically for views, but not for functions. The tuple type can be defined as:
create type BeerInfo as (brewer text, beer text);
The function could be used as follows::
beers=# select * from FavouriteBeer('John');
brewer | beer
---------------+----------
Sierra Nevada | Pale Ale
(1 row)
beers=# select * from FavouriteBeer('Adam');
brewer | beer
----------+------
Toohey's | Old
(1 row)
CREATE OR REPLACE FUNCTION FavouriteBeer(text)
returns setof BeerInfo
AS $$
SELECT brewer, beer
FROM AllRatings
WHERE taster = $1 AND
rating = (SELECT max(rating) FROM AllRatings WHERE taster = $1)
$$ LANGUAGE sql;
-- sql的函数变量不能直接在程序化语言中用变量名，而是使用$1代表第一个标量，同理$2代表第二个变量。
Q4
What style is that beer?
Sometimes beers are named after their style (e.g., Sierra Nevada Pale Ale). Other times, imaginative names are used (e.g. Rooftop Red, Old Peculier). For these latter ones, we may know the name and want to discover what the style is. Write an SQL function that takes two text string arguments (the name of a brewer and the name of a beer) and returns the text string giving the style of the beer).
The function should give the correct result, regardless of the text case of the input parameters, so the following query should also work:
beers=# select BeerStyle('Sierra Nevada','Pale Ale');
beerstyle
-----------
Pale Ale
(1 row)
beers=# select BeerStyle('sierra nevada','pale ale');
beerstyle
-----------
Pale Ale
(1 row)
beers=# select BeerStyle('sieera nevada','pale ale');
beerstyle
-----------
(1 row)
CREATE OR REPLACE FUNCTION BeerStyle(brewer text, beer text)
returns text
AS $$
SELECT s.name
FROM Beer b, Brewer br, BeerStyle s
WHERE b.brewer = br.id AND s.id = b.style AND lower(br.name) = lower($1) AND lower (b.name) = lower ($2)
$$ LANGUAGE sql;
--为了同时兼容大小写，where的限制条件使用了lower()函数，统一转化为小写
Q5
Consider the following PostgreSQL SQL function to produce a representation of a taster's address:
create or replace function TasterAddress(text) returns text
as $$
select loc.state||', '||loc.country
from Taster t, Location loc
where t.given = $1 and t.livesIn = loc.id
$$ language sql;
This function would be used as follows. The function works ok for people who have both a state and a country, but fails for people who have just a country specified; it gives a null address, when clearly some address information is known. To observe the bug, try to find Sarah's address. Modify the function so that it produces a sensible result when either the country or the state is null. Once you've fixed this bug, you ought to be able to get a result like::
beers=# select tasterAddress('John');
tasteraddress
----------------------------
New South Wales, Australia
(1 row)
beers=# select tasterAddress('Jeff');
tasteraddress
--------------------
California, U.S.A.
(1 row)
beers=# select tasterAddress('Sarah');
tasteraddress
---------------
England
(1 row)
CREATE OR REPLACE FUNCTION TasterAddress(text)
returns text
AS $$
SELECT CASE
WHEN loc.state is null then loc.country
WHEN loc.country is null then loc.state
ELSE loc.state||', '||loc.country
END
FROM Taster t, Location loc
WHERE t.given = $1 AND t.livesIn =loc.id
$$ LANGUAGE sql;
--为了实现country或者state为null时依然输出，使用了case when的条件判断语句
Q6
Define a function that produces a summary of beer tasting.
The function BeerSummary returns a summary for each beer consisting of the name of the beer, followed by its average rating (to 1 decimal place), followed by a comma-separated list of the given names of the people who tasted and rated the beer.
The function should produce the following results on the example database:
beers=# select BeerSummary();
beersummary
-------------------------------------------
Beer: 80/-
Rating: 4.0
Tasters: John
Beer: Bigfoot Barley Wine
Rating: 3.0
Tasters: Ramez
Beer: Burragorang Bock
Rating: 3.0
Tasters: Jeff, Sarah
Beer: Chimay Red
Rating: 3.0
Tasters: John
... a bunch of text omitted here to save space ...
Beer: Sierra Nevada Pale Ale
Rating: 4.0
Tasters: Hector, Jeff, John, Raghu, Ramez
Beer: Victoria Bitter
Rating: 1.0
Tasters: Adam, John, Sarah
Beer: XXXX
Rating: 5.0
Tasters: Peter
(1 row)
CREATE OR REPLACE FUNCTION BeerDisplay(_beer text, _rating float, _tasters text)
returns text
AS $$
BEGIN
return E'\n' ||
'Beer: ' || _beer || E'\n' ||
'Rating: ' || to_char(_rating, '9.9') || E'\n' ||
'Tasters: ' || substr(_tasters, 3, length(_tasters)) || E'\n';
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION BeerSummary()
returns text
AS $$
DECLARE
r record;
out text := '';
curbeer text := '';
tasters text;
sum integer;
count integer;
BEGIN
for r in SELECT * FROM AllRatings ORDER BY beer, taster
loop
if (r.beer <> curbeer) then
if (curbeer <> '') then
out := || BeerDisplay(curbeer, sum/count, tasters);
end if;
curbeer := r.beer;
sum := 0; count := 0; tasters := '';
end if;
sum := sum + r.rating;
count := count + 1;
tasters := tasters || ', ' || r.tasters;
end loop;
out := out || beerDisplay(curbeer, sum/count, tasters);
return out;
END;
$$ LANGUAGE
--record type类似tuple，但是没有预先设置好的structure，可以存放一个tuple或者是row
Q7
Define a new aggregation operator to concatenate a column of strings.
You could use this operator to get a list of taster names as follows:
beers=# select concat(given) from Taster ;
concat
----------------------------------------------------------
John,Adam,Jeff,Sarah,Raghu,Ramez,Hector,Geoff,Peter,Rose
(1 row)
CREATE OR REPLACE FUNCTION appendNext(_state text, _next text)
returns text
AS $$
BEGIN
return _state|| ', ' || _next;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION finalText(_final text)
returns text
AS $$
return substr(_final, 2, length(_final));
$$ LANGUAGE plpgsql;
CREATE AGGREGATE concat (text)
(
stype = text,
initcond = '',
sfunc = appendNext,
finalfunc = finalText
);
Q8
Define a view that produces a summary of beer tasting.
The view should return a set of tuples where each tuple contains the name of the beer, the average rating (to 1 decimal place), and a comma-separated list of the given names of people who tasted and rated the beer.
The view should produce the following results on the example database:
beers=# select * from BeerSummary;
beer | rating | tasters
------------------------+--------+------------------------------
Redback | 4.5 | Geoff,Rose
Fosters | 3.0 | Hector
New | 1.5 | Adam,Sarah
Empire | 3.0 | Geoff,John
Old Tire | 5.0 | Raghu
Old | 3.5 | Adam,Sarah
80/- | 4.0 | John
Chimay Red | 3.0 | John
Crown Lager | 2.0 | John
James Squire Amber Ale | 3.0 | Sarah
Sierra Nevada Pale Ale | 4.0 | Hector,Jeff,John,Raghu,Ramez
Rasputin | 2.7 | Jeff,John,Raghu
Burragorang Bock | 3.5 | Jeff,Sarah
XXXX | 5.0 | Peter
Scharer's Lager | 3.0 | Sarah
Bigfoot Barley Wine | 3.0 | Ramez
Victoria Bitter | 1.0 | Adam,John,Sarah
James Squire Pilsener | 3.5 | Geoff,Sarah
(18 rows)
CREATE OR REPACE VIEW BeerSummary(beer, rating, tasters)
AS
SELECT beer, to_char(avg(rating), '9.9'), concat(taster)
FROM AllRatings
GROUP BY beer;
