QUESTION $1(30$ MARKS$)$

$1\mathrm{.}1$ What are the six phases of the Database Analysis Lifecycle and what are the activities that occur in

each phase? $(10)$

$1\mathrm{.}2$ Consider the table below.

Convert the report into a set of BCNF tables. Describe each step in detail and show the relational

schema associated with each step. $(20)$

EMP$\_$NUM EMP$\_$FNAME EMP$\_$LNAME JOB$\_$DESC BENEFIT$\_1$ BENEFIT$\_2$ BENEFIT$\_3$ PROJ$\_$ID PROJECT

$200$ Luke Shaw Manager Company

Car

Medical

Aid

$13$th

Cheque

$445$ Sub Zero

$200$ Luke Shaw Manager Company

Car

Medical

Aid

$13$th

Cheque

$555$ Scorpion

$300$ Matthew Matthews Marketing Company

Car

$445$ Sub Zero

$500$ John Staples Marketing Company

Car

$445$ Sub Zero

$600$ Samuel Jackson Accountant $13$th

Cheque

$445$ Sub Zero

$600$ Samuel Jackson Accountant $13$th

Cheque

$555$ Scorpion

QUESTION $2(30$ MARKS$)$

$2\mathrm{.}1$ XYZ Corporation, an IT and Digital media company is restructuring their organisation, part of which

includes redesigning their database into a relational database. You have been tasked with redesigning

the new database. You have been given the following business rules, a manager can be a project

manager or project lead. A project manager oversees a department, a project lead oversees a project.

An employee can be a programmer or designer. A programmer must be front$-$end or back$-$end or both.

A designer must always have a project they are working on$.$

Draw the EERD for XYZ corporation and include all the necessary attributes. $(30)$

QUESTION $3(30$ MARKS$)$

Suppose you have the following table named Student. The table is stored in a Database called

School.

Student

Student$\_$ID Student$\_$Name Student$\_$Surname Student$\_$Phone Student$\_$Address

$1234$ Erling Sule $07112345673321$st Avenue

$5678$ Cristiano Ronaldo $0724567890182$nd Street

$9101$ Abedi Pele $07378945608$ Fauna Drive

Write SQL code to achieve the following.

$3\mathrm{.}1\mathrm{.}1$ Create a database named school and a table named student $($the table must be created within

the School Database$)(5)$

$3\mathrm{.}1\mathrm{.}2$ Insert all the records shown on the table above. $(5)$

$3\mathrm{.}1\mathrm{.}3$ Insert a new record with the Student$\_$ID $9999($use your own values for the rest of the columns$)$

$(5)$

$3\mathrm{.}1\mathrm{.}4$ List all the records where the Student$\_$Name has a letter $$e$.(5)$

$3\mathrm{.}2$ Assume you have the following entity.

Write a SQL query to list each employee name and surname, their weekly average and the monthly

average. Show a field called $$OverallAverage$$ which is the weekly average $+$ monthly average. Sort

the employees alphabetically. $(10)$