[ExamVT13] Block 3: SQL Data Definition Language

[Niklas Broberg]

==========================================

The domain for this block, and for several following blocks as well, is that of a database for the catalogue of an online music streaming site. You are given the following schema of their intended database:

Tracks(trackId, title, length)

 length > 0

Artists(artistId, name)

Albums(albumId, title, yearReleased)

TracksOnAlbum(album, trackNr, track)

 album -> Albums.albumId

 track -> Tracks.trackId

 (album, track) unique

 trackNr > 0

Participates(track, artist)

 track -> Tracks.trackId

 artist -> Artists.artistId

Users(username, email, name)

 email unique

Playlists(user, playlistName)

 user -> Users.username

InList(user, playlist, number, track)

 (user, playlist) -> Playlists.(user, playlistName)

 track -> Tracks.trackId

PlayLog(user, time, track)

 user -> Users.username

 track -> Tracks.trackId

 (user, time) unique

An artist can be either a solo artist or a group, the design makes no difference between the two kinds. Tracks are recorded by one or more artists, and each track can appear on one or more albums (but no more than once on each album) to account for e.g. “Greatest hits” or collection albums. Users of the site can register, in order to create playlists, which are simply ordered collections of tracks. Finally, the system stores a log over all songs played by registered users, to calculate statistics and to give suggestions and feedback.

(Note: The actual music files to be streamed is considered to be stored separately, outside the scope of this schema.)

==========================================

3A (4p)

Write SQL DDL code that correctly implements these relations as tables in a relational DBMS. Make sure that you implement all given constraints correctly. Do not spend too much time on deciding what types to use for the various columns. We will accept any types that are not obviously wrong. Don’t forget to implement all specified constraints, including checks.

==========================================

3B (6p)

Note that the relation PlayLog, storing the log of songs played by registered users, curiously has no primary key specified. It does, however, have a uniqueness constraint. Explain why this choice was made, and what the benefits and drawbacks are for this particular situation.

==========================================

3C (8p)

When a user right-clicks a song in the online interface, they get the option “Add song to playlist”. If they choose this option, they may pick one of their existing playlists, or choose the option “Create new playlist”. If they opt for the latter, they supply a name for the new playlist, which is created with the song in question in it. Sketch an overview of how to ensure, through the use of views, and/or triggers, and privileges, that the database stores the correct information in the correct tables.

For any views you want to use, give the schema and explain its intended contents.

For any trigger you might include, list the trigger head ([BEFORE/AFTER/INSTEAD OF] [INSERT/DELETE/UPDATE] ON which element), and describe its intended operation in broad terms (a simple overview in plain English would be fine, you don’t have to write any code).

Also specify what privileges the front-end should be granted in order to handle this use case.

==========================================

[Gorm Andersson]

I've been trying to come up with an answer for 1B for a while, and I want to know if I'm on the right track.
The two differences between unique and primary key that I can think of is that primary keys are indexed, and cannot be null. Allowing nulls doesn't seem to be the issue so it must be index related. A log of all tracks played is already sorted by time for every user, therefor easy to pull stats from, and requires a huge amount of inserts, one per track played. Thus indexes are not wanted and a uniqueness constraint is preferred.

[Grégoire Détrez]

Hi Gorm,

On Thursday, March 13, 2014 9:59:35 AM UTC+1, Gorm Andersson wrote:

The two differences between unique and primary key that I can think of is that primary keys are indexed, and cannot be null. Allowing nulls doesn't seem to be the issue so it must be index related. A log of all tracks played is already sorted by time for every user, therefor easy to pull stats from, and requires a huge amount of inserts, one per track played. Thus indexes are not wanted and a uniqueness constraint is preferred.

That was indeed the expected answer. But we just discovered that it is not correct: databases systems create an index for UNIQUE constraints as well (at least oracle and PostgreSQL do) so you don't really get anything from not having a primary key here.

In addition, since the database have to enforce the constraint, not having an index would mean it needs to look at all existing rows on INSERT, which would probably not be any faster.

Grégoire

[Grégoire Détrez]

Here is one possible solution for the other questions, with additional comments:

3A

Creating SQL tables is relatively straightforward. Check an SQL reference for the

right keywords and be careful of composite keys.

    CREATE TABLE Tracks(

      trackId INTEGER PRIMARY KEY,

      title TEXT,

      length INTEGER CHECK (length > 0)

    );

    

    CREATE TABLE Artists(

      artistId INTEGER PRIMARY KEY,

      name TEXT

    );

    

    CREATE TABLE Albums(

      albumId INTEGER PRIMARY KEY,

      title TEXT,

      yearReleased INTEGER

    );

    

    CREATE TABLE TracksOnAlbum(

      album INTEGER REFERENCES Albums(albumId),

      trackNr INTEGER CHECK (trackNr > 0),

      track INTEGER REFERENCES Tracks(trackId),

      PRIMARY KEY (album,trackNr),

      UNIQUE (album, track)

    );

    

    CREATE TABLE Participates(

      track INTEGER REFERENCES Tracks(trackId),

      artist INTEGER REFERENCES Artists(artistId)

    );

    

    CREATE TABLE Users(

      username TEXT PRIMARY KEY,

      email TEXT UNIQUE,

      name TEXT

    );

    

    CREATE TABLE Playlists(

      user_ TEXT REFERENCES Users(username),

      playlistName TEXT,

      PRIMARY KEY (user_, playlistName)

    );

    

    CREATE TABLE InList(

      user_ TEXT,

      playlist TEXT,

      number INTEGER,

      track INTEGER REFERENCES Tracks(trackId),

      PRIMARY KEY (user_, playlist, number),

      FOREIGN KEY (user_, playlist) REFERENCES Playlists (user_,playlistName)

    );

    

    CREATE TABLE PlayLog(

      user_ TEXT REFERENCES Users(username),

      time TIMESTAMP,

      track INTEGER REFERENCES Tracks(trackId),

      UNIQUE (user_,time)

    )

3C

Let's first step through the queries the interface needs to execute on the

database in order to perform this "microinteraction". First, the web interface

needs to load the user's playlists in case she wants to add the song to an

existing playlist::

    SELECT playlistName FROM Playlists WHERE user = ...;

Next, if the user choose to create a new playlist, the interface will need to

add it to the database::

    INSERT INTO Playlists(user_, playlistName)

      VALUES (<username>, <new playlist's name>);

Finally, in order to add the song to the playlist, we need insert a row in the

InList table::

    INSERT INTO InList(user_, playlist, track)

      VALUES (<username>, <playlist name>, <track id>)

Most of the constraint we would like the data to follow will already be enforced

by our schema (e.g. unique playlist name for a user, existing playlist, existing

track id...) But there is one thing that we need to take care of: the interface

doesn't ask the user to assign a number to the track added to the playlist, so

we need to make this happen automatically. One possible solution is to use a

BEFORE INSERT trigger on the InList track. The trigger will assign to

NEW.number the next available number::

    NEW.number := SELECT MAX(NUMBER) + 1 FROM InList

      WHERE user = ... AND playlist = ...

So the needed permissions, for this interaction only, (the interface might need

other permissions to do other things, like play a track or browse collection)

  SELECT, INSERT ON Playlists

  INSERT ON InList