Description

Quit with the super hero pose, stop flashing those guns, you have some work ahead of you!

The Friends TV show, which began broadcasting on September 22 1994, is a popular sitcom that has a total of 10 seasons. Your task is to teleport online to the Friends Episodes wiki page (Link: https://en.wikipedia.org/wiki/List_of_Friends_episodes) and design a database to capture the TV information and viewer statistics in all the tables.  You don’t need to worry about the reference links to other pages, but we want to record all of the detail in our favourite TV show.

Restrictions on the Design

In order to achieve the desired level of flexibility in data collection, the design will be expected to accommodate the following features:

·         The design should consist of a minimum 4-5 classes but this will easily blow out depending on the integrity of your approach.

·         It is up to you as the designer if you want to use inheritance in the proposed design. 

·         Your design should include primary keys, unique keys and foreign keys as appropriate.

·         Your design will be optimised to reduce the storage of redundant information, this includes names of writers, directors and so on.

·         Your design should facilitate the quick searching of

o   Episodes by title

o   Episodes by air date

o   Episodes by director

o   Writers/Directors in general

·         Your design should be able to reconstruct the original information depicted so poorly on the Wikipedia page!

Submission Requirements

To complete the assignment you must submit each of the following:

1.       A Traditional UML diagram using UML standards (no Foreign Keys in classes, use of association classes, weak entities, inheritance etc where appropriate).  Each class should contain a list of the applicable attributes that meet the design requirements.  The diagram should highlight ALL associations between classes including their multiplicities and include appropriate roles/names to describe the purpose of the association.

a.       The UML diagram should be drawn up in UMLet or some other UML design tool but not using the Management Studio Diagram tool or DBDesignerFork.

b.      Multiplicities must be included and can be represented using numerical or crow’s foot notation.

c.       Any Primary and Candidate Keys should be depicted using (pk), (ck) etc.

d.      Save the UML as an image to be included in your final word document (make sure it is still readable!)

e.      If you include foreign key attributes their names will be checked between diagrams so watch out!

f.        You need to provide any written assumptions that may justify why you chose certain attributes/association types over another (written assumptions/justifications should be succinct, keep to a few sentences for each assumption/justification).

2.       An Executable UML Diagram drawn using DBDesigner Fork using crows-foot notation showing:

a.       Each of the required Table Schemas via the various UML Classes

b.      Any Foreign Keys with appropriate and consistent naming

c.       Correct relationship types (identifying vs non-identifying)

d.      Appropriate data types

e.      Save the UML as an image to be included in your final word document and make sure it is still readable.

Oh! And don’t forget to save frequently because we all know how fun it can be if DBDesigner locks up :(

3.       At least two table Creation statements for your design that work and which together must cover:

a.       An example of a named Primary Key

b.      An example of a named Foreign Key

c.       An example of a named Composite Primary Key OR Composite Foreign Key OR Inherited Key

Hint:

Make sure your two UML diagrams are in agreement as marks are awarded for consistency, this includes association names and multiplicities. Stick to crows-foot notation in DBDesignerFork otherwise the numbers will be misleading.  Make sure you check the SQL that is output by the program as you know it can contain errors and does require some attention!

Due Date

The assignment and all your outstanding work is due Sunday, 12th April 2018 @ 11:55 PM.