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
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
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
Episodes by title
Episodes by air date
Episodes by director
Writers/Directors in general
Your design should be able to
reconstruct the original information depicted so poorly on the Wikipedia
To complete the assignment you must submit
each of the following:
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
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.
Multiplicities must be included
and can be represented using numerical or crow’s foot notation.
Any Primary and Candidate Keys
should be depicted using (pk), (ck) etc.
Save the UML as an image to be
included in your final word document (make sure it is still readable!)
If you include foreign key
attributes their names will be checked between diagrams so watch out!
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
An Executable UML Diagram drawn using
DBDesigner Fork using crows-foot notation showing:
Each of the required Table
Schemas via the various UML Classes
Any Foreign Keys with
appropriate and consistent naming
Correct relationship types
(identifying vs non-identifying)
Appropriate data types
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 :(
At least two table Creation statements
for your design that work and which
together must cover:
An example of a named Primary Key
An example of a named Foreign Key
An example of a named Composite Primary Key OR Composite Foreign Key OR Inherited Key
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!
The assignment and all your outstanding
work is due Sunday, 12th
April 2018 @ 11:55 PM.