Design and Implementation of a Film Streaming Service Database System

Assessment Task

Task 1   

• This assignment will involve working with Masters Students at the University Of Applied Sciences, Landshut, Germany in a collaborative venture as defined in the attached Case Study.
  
  
• Your group will be acting as a UK organisation that wishes to collaborate with a German organisation, and will need to complete a number of tasks jointly with your German colleagues. You will be assigned to one of the three attached cases.
  
  
• The work you do will be assessed by a group presentation detailing a formal interoperability contract, a model of the resulting processes and the IT infrastructure that will be needed in both companies to support the collaboration. Further details on the collaboration will be provided in the lectures. Presentations will be peer assessed by the group and moderated by the tutor

Deliverable for task 1

A presentation that explains

• The formal interoperability contract 5%
• The BPMN model 10%
• The database infrastructure and data exchange you believe the two companies need in order to collaborate both now and in the future 5%

Task 2  

You are required to design and implement a database system for a film streaming service. Each user of the service has a unique membership id. For each user, you keep his/her name, email, user’s registration date and user’s leaving date (if available).

You also keep information about the films, both the ones which are offered by the service and the ones which are not. This information includes film’s id, name, running time, genre (action adventure, science fiction, horror, romance, comedy, drama, thriller, classic, children's film, where a single film may have more than one genre), year, director, up to five of actors starring in the film, and the date of addition to the service (if available).

It is assumed that each film is directed by a single director. For each film it is also required to record whether it is a sequel of another film, and if so which one (if there are more than two films in the series, then each film is considered to be the sequel of the previous one, e.g. Terminator 3 is a sequel of Terminator 2, which is a sequel of Terminator).

You also need to store information about all streams of every user, i.e. the film, date of its latest stream and the playing position on which the film was stopped (if it is). Furthermore, you need to store the searches of the user. These include the search string, the date, top three results, the number of all results and whether the search was successful (whether the user has clicked on one of the results).

In addition, you gather and organise facts on films, directors and actors.  For each director, you record his/her id, name, the year in which he/she was born, and the year of death (if deceased). For each actor, you record his/her id, name, the year in which he/she was born, and the year of death (if deceased). A same person can be both a director and an actor.

Deliverable for task 2

1. Develop an Entity-Relationship model of the information requirements for the above system including a description of the necessary data integrity constraints. 25%
   
   
2. Translate your model into an equivalent set of relations. Specify all relation headings, indicating primary and foreign keys. Transform the relations to Third Normal Form and explain why they are in Third Normal Form. 10%

Task 3 

You have been given a database on European Football. The description of the database, including the tables and fields can be found

Deliverable for task 3

1. Develop data selection use cases for the scenario above. Describe these use cases, explain why they make practical sense and formulate at least five queries which will help to implement them, with an explanation how. Use the different types of selection queries, such as simple selection, joins, grouping, mathematical operations, complex queries etc. Run these queries and provide screenshots of results. 15%
   
2. Develop a data analytics use case for the scenario above. Describe this use case and explain why it makes practical sense. Formulate the queries to select the necessary data, and perform data analytics tasks including data pre-processing and prediction using methods learned in the class. Describe how you used CRISP-DM methodology to improve the predictions and include all relevant results. 30%

Learning Outcomes 

Having successfully completed this unit the student is expected to:

To develop an understanding of modern data management technologies

1. To perform and critically analyse data modelling
2. To design and implement data management solutions using contemporary tools
3. To be able to understand and perform common data analytics tasks