Database Design

This design has not been finalized as of yet, as we have yet to determine suitable sizes of VARCHAR fields, (de)normalize appropriately and perform other design verifications.

However, it is clear that all given classes are physical classes and not catalogue classes.

Keys
The diagram does not accurately show the details of the implementation, but a rough layout.

Primary Keys

 * Users are identified by their username
 * Team Members are identified by their username
 * Diaries are identified by their author and the date


 * Comments are identified by the tank name, tank author and user
 * Tanks are identified by their name and author

Foreign Keys

 * In TeamMembers, field 'team_username' references Users 'username'
 * In Diary, field 'author' references TeamMembers 'team_username'
 * In Comments, field 'author' references Users 'username'
 * In Comments, field 'tank' references Tanks 'tank_name'
 * In Comments, field 'tankauthor' references Tanks 'author'
 * In Tank, field 'author' references Users 'username'

Required Attributes (not part of keys)

 * In Comments, the field 'comments_date' is required
 * In Tank, the field 'uploadDate' is requiredn Tank, the field

Relationships between Tables
Users may have zero-to-many Comments

Users may have zero-to-many Tanks

Tanks may have zero-to-many Comments

TeamMembers may have zero-to-many Diaries

Users may or may not be a part of TeamMembers

Normalization
The level of normalization per table is described below, up to 3NF, as a working list (i.e: tables are listed in the lower forms as well, up to the form they violate):

1NF
This is a relational database, therefore all tables are in 1NF.

2NF

 * Diary - The author and diary date are both required in order to identify diary entry and diary heading. Diary is in 2NF.
 * Users - Non-composite primary key, Users is in 2NF.
 * TeamMembers - Non-composite primary key, TeamMembers is in 2NF.
 * Comments - Comment date, comment and rating all require the full combination of tank, comment author and tank author to be known, making Comments 2NF.
 * Tank - ImageURL is always unique, but is determinable by the primary key, making it fully functional dependent. Upload dates, tank data and tank description are alse determinable by the primary key, making Tank 2NF

3NF

 * Diary - The diary entry and heading can't determine other attributes, Diary is in 3NF.
 * Users - The user's password, secret question and answer and their status as a developer can not determine other values, Users is in 3NF.
 * TeamMembers - The team role can not determine the username, TeamMembers is in 3NF.
 * Comments - The comment date, comment and rating are not guaranteed to determine other values, Comments is in 3NF.
 * Tank - The tank name and author are jointly responsible for determining any other value. The upload date, tank data, tank description and imageURL are therefore not partially dependent on the primary key, meanng Tank is in 3NF.

BCNF

 * Diary - No alternate candidate keys exist, Diary is in BCNF.
 * Users - No alternate candidate keys exist, Users is in BCNF.
 * TeamMembers - In our context, the team role can not be used as a candidate key, therefore TeamMembers is in BCNF.
 * Comments - Neither the comment data, text, comment date or rating can be used in any combination to form a candidate key, so no other candidate key can exist. Comments is in BCNF.
 * Tank - Boyce-Codd normal form is violated in Tank, since the imageURL is another candidate key to the primary key, but has not been used. Therefore, Tank is  not in BCNF.