project title $($Drug Interaction Database$)$ Design the database following an ER model. $(5$ marks$)$

$-$ The Conceptual ER should contain all necessary entities and relationship titles $($many

to many is allowed at this stage$)$ and cardinalities.

$-$ The ERD should contain more details compared to Conceptual ER such as entities,

attributes, relationships $($not many to many$),$ cardinalities etc.

$$ Convert your ER diagram to a relational schema. $(2\mathrm{.}5$ marks$)$

$-$ Underline all key attributes in your relations.

$$ Normalize the tables $($each table should be in $3$NF at least$)(2\mathrm{.}5$ marks$)$

$-$ Specify all functional dependencies for each relation. $($Mapping$)$

$$ Use Oracle to create the normalized tables. $(2$ marks$)$

$$ Populate your tables with $5$ rows at least. $(2$ marks$)$

$$ Design and implement at least $4$ queries. $(2$ marks$)$ As specified below

$-$ Data Retrieval Queries with Select $($total of $4$ Queries$)$

$$ Where

$$ Order By

$$ Group By

$$ Aggregate functions

$$ Subquery

$$ Join query

Note:

$-$ You are free to mix between all those clauses in one or more query but you have to

use them all. So the total number of queries for Data Retrieval Queries with Select in

the end will be $4.$

$-$ Simple queries such as select all $(*)$ is NOT accepted.

$$ Design two stored procedures as the following $(4$ marks$)$

$$ Create a PARAMETER based SELECT QUERY stored procedure which return records

based on parameters?

$$ Create an UPDATE query based stored procedure.Each student should submit one report for each group about his$/$her project via the

Blackboard contains the following: