15826 hw1

(2) LINEAR HASHING

(a)

m = 11,22,44.

(b)

2

(c)

h1(x) = x mod 88
h2(x) = x mod 176

(3) SQL

(a) top winners:

SQL:

SELECT p.personid,p.firstname,p.lastname,COUNT(n.won) AS cnt
FROM people AS p ,nominations AS n
WHERE p.personID = n.personID AND n.won = 't'
GROUP BY p.personid HAVING cnt >= 3
ORDER BY cnt DESC, p.lastname,p.firstname;

RESULT:

personID firstname lastname win
534|Katharine|Hepburn|4
540|Ingrid|Bergman|3
420|Walter|Brennan|3
37|Jack|Nicholson|3

(b) Duplicate detection:

SQL:

CREATE VIEW remake AS SELECT title,year FROM ( SELECT DISTINCT title,year FROM nominations) GROUP BY title HAVING COUNT(*) >= 2;

SELECT n.title, MIN(n.year) AS first,MAX(n.year) AS second FROM nominations AS n INNER JOIN remake ON n.title = remake.title GROUP BY n.title ORDER BY first ASC, second ASC;

RESULT:

The Letter|1928|1940
A Star Is Born|1937|1954
The Hurricane|1937|1999
Goodbye, Mr. Chips|1939|1969
Henry V|1946|1989
Cyrano de Bergerac|1950|1990
Moulin Rouge|1952|2001
True Grit|1969|2010

(c) Competitors

SQL:

SELECT p1.lastname,p1.firstname,p2.lastname,p2.firstname,
COUNT(*) AS times
FROM nominations AS n1,nominations AS n2,
people AS p1,people AS p2
WHERE n1.year = n2.year AND n1.catId = n2.catId
AND p1.personId = n1.personId AND p2.personId = n2.personId
AND p1.lastname < p2.lastname

GROUP BY n1.personId,n2.personId
HAVING COUNT(*) >= 3
ORDER BY times DESC, p1.lastname,p1.firstname;

RESULT:

Davis|Bette|Hepburn|Katharine|4
Davis|Bette|Garson|Greer|4
Nicholson|Jack|Pacino|Al|4
Bergman|Ingrid|Garson|Greer|3
Boyer|Charles|Tracy|Spencer|3
Colbert|Claudette|Davis|Bette|3
Kerr|Deborah|Taylor|Elizabeth|3
Lemmon|Jack|O'Toole|Peter|3
Newman|Paul|Tracy|Spencer|3
Olivier|Laurence|Stewart|James|3