Database Fundamentals Assignment 1
Due Sunday August 16, 2020, 11:59pm
The Big Bang Theory, which began broadcasting on September 24, 2007, is a popular sitcom that has a total of 12 seasons to date. Your task is to go to The Big Bang Episodes wiki page https://en.wikipedia.org/wiki/List_of_The_Big_Bang_Theory_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 TV show detail.
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. 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 Episodesbyairdate
o Episodes by director
o Writers/Directorsingeneral
• 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 Conceptual UML design 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 draw.io
b. Multiplicities must be included and can be represented using numerical or crow’s foot
notation.
c. Save the UML as an image to be included in your final word document. You may need to
rotate the page layout to landscape. Remember, as an image, Zooming will enable viewing easily.
2. An Executable UML design of the database showing the attributes of each class and highlighting ALL associations between classes including their multiplicities
a. The UML diagram must be drawn up in DBDesignerFork
b. Multiplicities must be included and can be represented using number or crow’s feet
notation
c. Save the UML as an image to be included in your final word document. You may need to
rotate the page layout to landscape. Remember, as an image, Zooming will enable viewing easily.
Don’t forget to save frequently in case DBDesigner locks up
3. Written relational / logical schemas for your design showing:
a. Each of the required table schemas
b. Any primary and candidate keys using notation PK(xx) and CK(yy)
c. Any foreign keys using notation FK(attr1) -> table2(attr1)
4. At least two table Creation statements for your design 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
5. Justification for some of your decisions (those that may be debatable). For example, why you may have chosen a particular class and/or relationship over another e.g. a 1:many relationship instead of inheritance, or vertical vs horizontal inheritance in the logical design.
There is a maximum of 2500 words so dot point justifications are acceptable.
Your designs should be combined into a final word document that includes a Title page with your name, ID and Title of the assignment, the above numbered points in their own section with suitable section labels.
Due Date
The assignment and all your outstanding work is due Sunday, 16th August 2020 11:59 PM.