Databases

TDA357 (Chalmers), DIT621 (University of Gothenburg)

Home Exam on 20 March 2020 at 8:30-12:30

1 Entity-Relationship Modelling (10p)

1.1 E-R diagram (5p)

ENTITY Book : _isbn title year

ENTITY Author : _name _year

RELATIONSHIP IsWrittenBy Book -- Author

WEAK ENTITY Chapter Book In : _number title

RELATIONSHIP IsChapterAuthor Chapter -> Author

1.2 From E-R model to database schema (5p)

Title(_title,year)

TvSeries(_title)
  title -> Title(title)

TvEpisode(_title,_seriesTitle,season,episode)
title -> Title(title)
seriesTitle -> TvSeries(title)

2 Functional Dependencies (8p)

2.1 Redundancies and inconsistencies (2p)

An update the keeps the information consistent:

UPDATE Bookings SET seats=40 WHERE lab='ED3507'

An update that introduces an inconsistency:

UPDATE Bookings SET seats=40 WHERE lab='ED3507' AND course='TDA555';

2.2 Dependencies and keys (3p)

Functional dependencies:

course -> name
lab -> seats
lab timeslot -> course

There is only one possible key: lab timeslot

2.3 Normalization (3p)

BCNF violations

course->name
lab->seats

Relations after BCNF decomposition

Bookings(course,_timeslot,_lab)
  course -> Courses(course)
  lab -> Labs(lab)

Courses(_course,name)

Labs(_lab,seats)

Tables

Courses

course	name
TDA357	Databases
TDA555	Intro to FP

Labs

lab	seats
ED3507	30
ED3354	16
ED3358	16

Bookings

course	timeslot	lab
TDA357	Monday 10-12	ED3507
TDA357	Friday 13-15	ED3507
TDA555	Monday 10-12	ED3354
TDA555	Monday 10-12	ED3358
TDA555	Friday 15-17	ED3507

3 SQL queries (12p)

3.1 (2p)

SELECT title
FROM Ratings
WHERE averageRating>=8.5 AND numberOfVotes>=10000;

3.2 (3p)

    SELECT title,releaseYear
    FROM AppearsIn NATURAL JOIN Movies
    WHERE name='Max von Sydow'
INTERSECT
    SELECT title,releaseYear
    FROM AppearsIn NATURAL JOIN Movies
    WHERE name='Angela Lansbury'

Another solution:

WITH MaxMovies AS (
  SELECT title
  FROM AppearsIn
  WHERE name = 'Max von Sydow'
),
AngelaMovies AS (
  SELECT title
  FROM AppearsIn
  WHERE name = 'Angela Lansbury'
)
SELECT Movies.title, releaseYear
FROM Movies, MaxMovies, AngelaMovies
WHERE Movies.title = MaxMovies.title
AND   Movies.title = AngelaMovies.title;

Yet another solution:

SELECT M.title,releaseYear
FROM Movies AS M
     INNER JOIN AppearsIn AS A1
             ON A1.title=M.title AND A1.name='Angela Lansbury'
     INNER JOIN AppearsIn AS A2
             ON A2.title=M.title AND A2.name='Max von Sydow';

And yet another solution:

SELECT title,releaseYear
FROM Movies NATURAL JOIN AppearsIn
WHERE name='Max von Sydow'
  AND title IN (SELECT title FROM AppearsIn WHERE name='Angela Lansbury');

Another variant:

SELECT * FROM Movies
WHERE 'Max von Sydow' IN (SELECT name FROM AppearsIn
                          WHERE AppearsIn.title = Movies.title)
AND 'Angela Lansbury' IN (SELECT name FROM AppearsIn
                          WHERE AppearsIn.title = Movies.title);

Even more ways:

SELECT title, releaseYear
FROM Movies
WHERE (SELECT COUNT(name) 
       FROM AppearsIn
       WHERE title=Movies.title AND
             name IN ('Max von Sydow', 'Angela Lansbury')) = 2;

3.3 (3p)

SELECT DISTINCT name
FROM Movies NATURAL JOIN AppearsIn NATURAL JOIN MovieStars
WHERE releaseYear-birthYear<16

Alternative solution:

WITH FirstAppearance AS (
    SELECT name,MIN(releaseYear) AS firstYear
    FROM Movies NATURAL JOIN AppearsIn
    GROUP BY name)
SELECT name
FROM FirstAppearance NATURAL JOIN MovieStars
WHERE firstYear-birthYear<16

Yet another solution:

WITH AppearancesWithAge AS (
    SELECT name,title,releaseYear-birthYear AS age
    FROM Movies NATURAL JOIN AppearsIn NATURAL JOIN MovieStars
)
SELECT DISTINCT name
FROM AppearancesWithAge
WHERE age<16

3.4 (4p)

WITH
  RatedMovies AS (
    SELECT releaseYear AS year,title,averageRating
    FROM Movies NATURAL JOIN Ratings
    WHERE releaseYear>=1970 and numberOfVotes>=10000),
  TopRatings AS (
    SELECT year,MAX(averageRating) AS topRating
    FROM RatedMovies
    GROUP BY year)
SELECT year,topRating AS rating,title
FROM RatedMovies NATURAL JOIN TopRatings
WHERE averageRating=topRating
ORDER BY year;

This should also work (in principle, it took too long in my test):

SELECT releaseYear,averageRating,title
FROM Movies AS M NATURAL JOIN Ratings
WHERE releaseYear>=1970 AND numberOfVotes>=10000
  AND averageRating = (SELECT MAX(averageRating)
                       FROM Movies NATURAL JOIN Ratings
                       WHERE numberOfVotes>=10000
                         AND releaseYear = M.releaseYear);

4 Algebra and theory (8p)

4.1 (4p)

π_{averageRating,title} (σ_{name=‘Tom Cruise’} (AppearsIn ⋈ Rating))

Or:

π_{averageRating,title} ((σ_{name=‘Tom Cruise’} AppearsIn) ⋈ Rating)

4.2 (4p)

SELECT name
FROM Countries
WHERE name IN (SELECT capital FROM countries);

This would be translated to π_name (σ_condition Countries) in relational algebra, where condition comes from WHERE clause in the SQL query, which in this example contains another query. The problem is that the conditions in the sigma operator are not allowed to contain relational algebra operators.

5 Views, Constraints and Triggers (12p)

5.1 (4p)

CREATE TABLE Votes(
    title    TEXT REFERENCES Movies(title),
    username TEXT REFERENCES Users(username),
    vote     INT  NOT NULL CHECK (0<=vote AND vote<=10),
                              -- (vote BETWEEN 0 AND 10)
    PRIMARY KEY (title,username)
);

5.2 (4p)

CREATE VIEW Ratings AS (
  SELECT title,AVG(vote) AS averageRating,COUNT(vote) AS numberOfVotes
  FROM Votes
  GROUP BY title
);

5.3 (4p)

CREATE OR REPLACE FUNCTION propagate_title() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.title<>OLD.title THEN
    UPDATE AppearsIn SET title=NEW.title WHERE title=OLD.title;
    UPDATE Votes     SET title=NEW.title WHERE title=OLD.title;
  END IF;
  RETURN NULL;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER propagate_title AFTER UPDATE ON Movies
  FOR EACH ROW EXECUTE FUNCTION propagate_title();

6 Semi-structured data (10p)

6.1 (5p)

WITH Filmographies AS (
    SELECT jsonb_build_object(
               'name',name,
               'birthYear',MAX(birthYear),
               'movies',jsonb_agg(jsonb_build_object(
                                      'title',title,
                                      'year',releaseYear,
                                      'rating',averageRating))) AS filmography
    FROM Movies NATURAL JOIN MovieStarts
                NATURAL JOIN AppearsIn
                NATURAL JOIN Ratings
    GROUP BY name)
SELECT jsonb_agg(filmography) AS filmographies
FROM Filmographies;

6.2 (5p)

{"type":"array",
 "items":{
     "type":"object",
     "required":["name","birthYear","movies"],
     "properties":{
         "name":{"type":"string"},
         "birthYear":{"type":"number"},
         "movies":{"type":"array",
                   "items":{
                       "type":"object",
                       "required":["title","year","rating"],
                       "properties":{
                           "title":{"type":"string"},
                           "year":{"type":"number"},
                           "rating":{"type":"number"}
                       }
                   }
                  }
     }
 }
}