[Informatics] northern hemisphere Informatics exam

[Andrew Pate]

Hi Laurie,
Surely that table setup would not meet 3NF?
The stars table would have duplicates of actors names, which would cause possible data integrity issues.

Table:Stars
surname, firstname, movie_title

Brando, Marlon, “On the Waterfront”
Brando, Marlon, “The Dogfather”

It seems strange that a sample answer would not go directly to a 3NF solution.

A better solution?
Table: movies
Id, title, director, release_date (*of course, director should come out as well)

Table: stars
Id, surname, firstname

Table: movies_stars (or some other name depending on your convention)
Id, movie_id, star_id

This provides many-many relationships in both directions (more than one actor per movie, more than one movie per actor)

So a description (which is what the question asks for, not a database design):
The data would be stored in (at least) 3 tables. One table would hold the information about movies (id, title, director’s name and release date). One table would hold the information about stars (id, surname and first_name). The 3rd table would join the two tables together using the id’s from each of the two other tables (id, movie_id, star_id).

Thanks
Andrew.

From: informatics <informatics-bounces at edulists.com.au> On Behalf Of Laurie McDonald (Mr)
Sent: Sunday, 11 November 2018 10:41 PM
To: Year 12 VCE Informatics Teachers' Mailing List <informatics at edulists.com.au>
Subject: [Informatics] northern hemisphere Informatics exam

Hi All

Not sure if it has been mentioned before, but for those interested.
There is a Northern Hemisphere 2018 exam in Informatics (and others)
https://www.vcaa.vic.edu.au/Pages/vce/nht/resources.aspx<https://www.vcaa.vic.edu.au/Pages/vce/nht/resources.aspx>

Question 3 Part B (4 marks) Asks:

Data about movies and movie stars is to be stored in a database. The data about movies will include the movie’s title, the director’s name and the release date. The data about movie stars will include the actor’s name and the titles of the movies in which they have starred. Describe how the tables in a relational database management system would be structured to store all the data.

The answer states:
Students were asked to describe how the table in a relational database management system would be structured to store all the data. The data could be stored in two tables, one for movies and one for the stars. The movies table would have columns/fields for Movie ID, title, director, and date. The stars table would have columns/fields for actor and title. The Movie ID would provide the link/relationship between the two tables.

Do you think the Stars table (actor and title) and the link described is enough info?

cheers

Kind Regards,
Mr Laurie McDonald



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://edulists.com.au/pipermail/informatics/attachments/20181111/9136a988/attachment.html>