[ExamVT13] Block 1: Entity-Relationship diagrams

[Niklas Broberg]

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

1A (8p)

(i) (4p)

An online service for streaming video needs a database to keep track of the items in its catalogue. 

The service supplies two kinds of shows: full movies, and TV series. For movies, the database needs to store the name of the movie, the year when it was released, its length, and its parental guideline rating. For simplicity we assume that the name and year together are enough to uniquely determine a movie.

TV series are sent as episodes. Each series has one or more seasons, conveniently labeled S1, S2 etc. Within each season there are one or (typically) more episodes, labeled E01, E02 etc. Apart from the labels, the database needs to store the name and the parental guideline rating of the whole series, the year each season was originally aired, and the name and length of each episode.

Finally, to help users find shows they like, each show can be assigned to one or more predefined categories, e.g. “Drama”, “Horror”, “Family”, etc.

Your task is to draw an ER diagram that correctly models this domain and its constraints.

(ii) (4p)

The E/R diagram below depicts a rudimentary database used for a carpool. Translate the ER diagram above into a set of relations. Mark keys and references clearly in your answer.

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

1B (12p)

Below is part of a database schema used for the catalogue of a music-streaming service:

Tracks(trackId, title, length)

Acts(actId)

Artists(artistId, name, yearOfBirth)

artistId -> Acts.actId

Groups(groupId, name, yearStarted)

groupId -> Acts.actId

PlaysIn(artist, group, instrument)

artist -> Artists.artistId

group -> Groups.groupId

Albums(albumId, title, yearReleased)

SingleActAlbums(album, act)

album -> Albums.albumId

TracksOnAlbum(album, track, trackNr)

album -> Albums.albumId

track -> Tracks.trackId

Participates(track, act)

track -> Tracks.trackId

act -> Acts.actId

All of this should be self-explanatory (or part of the problem), but if there is something that you don’t understand, don’t hesitate to ask. (Note that this schema is different from the one used in block 3 and onwards, and that the domain modeled here is not identical to the one modeled there.)

(i) (8p)

Reconstruct the ER diagram that led to these relations and constraints.

(ii) (4p)

During the translation that led to the above schema, the ER approach will have been used used consistently. For each case where applicable in the diagram you have reconstructed, state what constraints you would lose by instead using the NULL approach.

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

[Gorm Andersson]

In 1B SingleActAlbums(album, act) has a reference for album, but not for act. What gives?

[Niklas Broberg]

An omission - good spot. There was an errata given for this when the exam was live.

That is, there should be a reference from SingleActAlbums.act to Acts.actId.

Thanks, Niklas

On 7 Mar 2014 22:53, "Gorm Andersson" <civ...@gmail.com> wrote:

In 1B SingleActAlbums(album, act) has a reference for album, but not for act. What gives?

--
You received this message because you are subscribed to the Google Groups "tda357-vt2014" group.
To unsubscribe from this group and stop receiving emails from it, send an email to tda357-vt201...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[TM Jansson]

Here goes nothing...

1A i) See enclosed diagram. Can you have "nested weak entities"? (To get the composite key of Episode, you need episodeNmbr, but also SeasonNmbr and Name).

1A ii)
Models(_brand_, _model_, type, #doors)

Cars(_RegNr_, miles, brand, model)
  (brand, model) -> Models.(brand, model)

Members(_SSNr_, Name, PhoneNr)

Bookings(_date_, car_, member)
  car -> Cars.regNr
  member -> Members.SSNr
  member unique

1B i) Again, see enclosed file. The coloring is quite arbitrary.

1B ii) If the NULLs approach had been used, we would not have the constraints on Artists and Groups, but rather a single constraint Acts
(How much do I need to say here? I have to admit I'm not sure I understand the question...)

[Jonas Almström Duregård]

Nice!

A few comments about 1B:

i) Group and artist do not need double edges (they are not weak, they are subentities)

The way to test correctness here is to translate back to a schema and make sure you get the original thing. For SingleActAlbum this is not the case, a many-to-exactly one is not translated as a separate relation, just an extra attribute in Album.

So your diagram would give
Album(albumId, title, yearReleased, act)

  act -> Act.actId

For ii), one constraint is that even though artists can play in groups, groups can not play in artists and artists can not play in artists etc. Why is this enforced with the ER approach? Why would it break with the Null approach? Suggestions are most welcome.

Any other constraints?

--

[Niklas Broberg]

Correct answers:

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

1A (8p)

(i) (4p)

An online service for streaming video needs a database to keep track of the items in its catalogue. 

[...]

Your task is to draw an ER diagram that correctly models this domain and its constraints.

I refer to Magdalena's answer above - it would have received full points.

There is an alternative solution that would also have given full points. It was somewhat unclear from the description what was needed in order to uniquely identify a TV series, and some made the (somewhat realistic) assumption that both year and name were necessary here as well. The solution then is to have a parent entity "Show", with year and name together as key (plus PG rating still). "Movie" and "Series" would then be two separate sub-entities (ISA), and "Season" and "Episode" would be chained weak entities supported from "Series".
 

(ii) (4p)

The E/R diagram below depicts a rudimentary database used for a carpool. Translate the ER diagram above into a set of relations. Mark keys and references clearly in your answer.

Again, Magdalena's answer above is spot on.

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

1B (12p)

Below is part of a database schema used for the catalogue of a music-streaming service:

[...]

(i) (8p)

Reconstruct the ER diagram that led to these relations and constraints.

Here I would give Magdalena's solution 7 points out of 8. The one error is the relationship SingleActAlbum, which cannot be many-to-exactly-one since then it would not have been translated to a separate relation. Two alternative solutions exist, and they are in fact equivalent: Make SingleActAlbum a many-to-at-most-one relationship; or make SingleActAlbum a sub-entity (ISA), with a many-to-exactly-one relationship (with arbitrary name) to Acts.
 

(ii) (4p)

During the translation that led to the above schema, the ER approach will have been used used consistently. For each case where applicable in the diagram you have reconstructed, state what constraints you would lose by instead using the NULL approach.

There are two places where this is relevant. The first is the two sub-entities Groups and Artists. In plain English, the constraint we lose is that artists can play in groups - groups cannot play in groups, nor anything play "in" an artist. That answer would suffice for full points. Formally, what happens is that the references from PlaysIn are weakened, from referring to the specialized Groups and Artists relations, to a single generic Acts relation.

The other place where the ER approach is used is the aforementioned SingleActAlbum. No constraints would be lost if using the NULL approach instead, so that would be a reasonable translation here.