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

Question 3: ER diagram

This depends on what was created in Question 2:

Question 4: Normalisation

1NF

Two repeating groups need to be removed:

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

2NF

There are partial key dependencies for staffID and productID.

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

3NF

There are no transitive dependencies, therefore 3NF is the same as 2NF.