SET08104 Database Systems 2010 Coursework Solution

Question 2: creating a table

create table publicRelations(
        depno integer,
        empno integer,
        primary key (depno, empno),
        bonus decimal(8,2),
        constraint foreign key (empno) references employee (empno),
        constraint foreign key (depno) references department (depno)
) ;

insert into publicRelations (depno, empno, bonus) values (1,2,200.00);
insert into publicRelations (depno, empno, bonus) values (2,7,200.00);
insert into publicRelations (depno, empno, bonus) values (3,16,200.00);
insert into publicRelations (depno, empno, bonus) values (4,22,200.00);
insert into publicRelations (depno, empno, bonus) values (5,27,200.00);

Slightly different solutions are possible, but

• At least 1 primary key is required.
• Redundancy: no unneccessary columns should be added.
• 2 foreign keys are required to establish the relationships with the employee table and the department table.
• The bonus should be decimal, not integer because it is a monetary value which can change.

Question 3: ER diagram

• Each foreign key establishes a relation.
• The name of the relation should correspond to the name of the table.
• The relations for the publicRelations table are one-to-one (not many-to-one or one-to-many).

Question 4: Normalisation

1NF

Retail( rID, rlocation)
RetailStaff( rID, staffID, staffName, staffPhNumber)
RetailProduct( rID, productID, productName, amount)

2NF

Retail( rID, rlocation)
RetailStaff( rID, staffID)
Staff(staffID, staffName, staffPhNumber)
RetailProduct( rID, productID, amount)
Product(productID, productName)

3NF