Deriving table design

1. Entity tables: create a table for every entity.
2. Relationships: create new tables or amend those created in step 1.

Entity tables
Attributes of an entity are converted to column attributes of the table. One or more column attributes are chosen as a primary key, the primary key must be unique for each row in the table.

Affected tables:
Manager (username, password, f name, l name)
Payroll Personnel (username, password, fname, lname)
Query (ref, fname, lname, payroll no, query, date time)
Reply (date time, reply)
Comment (date time, comment)

Relationships
One to Many relationships
One to Many relationships is normally represented just using two tables, one table representing each of the entity sets. One of the unique attributes of table one (many end) usually the primary key becomes an additional column attribute in table two (one end) known as a foreign key. The foreign key could form or form part of table two primary key if it helps make a row unique.

Affected tables:
Query (ref, fname, lname, payroll no, query, date time, Manager.username)

Reply (date time, Query.ref , Payroll Personnel.username, reply)
Comment (date time, Query.ref , Payroll Personnel.username, comment)

Many to Many relationships
Many to Many relationships are represented using three tables, one table representing each of the entity sets and a third table that links rows of the two tables together. The third table primary key is composed of foreign keys which are column attributes of the other two tables usually their primary keys.

Affected tables:
View (date time, Query.ref , Payroll Personnel.username)

Normalisation
Normalisation is used when designing relational database tables to ensure that the tables do not contain any anomolies. Following the guidelines all tables with the exception of Query table are in Boyce-Codd normal form (BCNF). It could be argued that Query table would experience the delete anomaly, as once a query is deleted information stored about the employee on which the query was based will be lost. In order to make the Query table comply with BCNF it would be necessary to create a seperate table Employee to store information about the employee. As payroll_no field is not mandatory and details regarding employees do not need to be stored for any reason other than the query to which they relate, Query table is left in 2NF.