Please come up with the database report based on Aged care facility management scenario with atleast $12$ enties, Design

your ER Diagram with all entity names, attribute names, primary and foreign keys,

relationships, cardinality, and participation indicated. You will need to normalise all your

entities, to resolve any many$-$to$-$many relationships.

Observe the following restrictions when creating your scenario:

$$ Include a specialization hierarchy, with super types and overlapping or disjoint subtypes.

$$ Include an example of both composite and surrogate primary keys.

$$ One $($or more$)$ of your entities must have a numeric field.

$$ One $($or more$)$ of your entities must contain an alphanumeric $($varchar$)$ field$$ Your attribute names and primary and foreign keys should be indicated as per the

conventions given in the lecture slides $($i$.$e$.$ attributes as proper nouns, primary key

underlined, and foreign keys in italics.

$$ All many$-$to$-$many relationships should be resolved, and you may wish to include a

discussion of normalisation $($Functional dependency Diagrams$),$ also including the

normal form that each entity is in and why that is optimal.

$$ For each entity, you must create some example data for that entity. Include at least $5$

rows of data for each entity.

$$ Data Dictionary needs to be provided before creating the tables and inserting the data.

$$ Write the SQL statements to create the table structures from your developed ERD. The

structures should contain the attributes specified in your ERD. Use data types that are

appropriate for the data that will need to be stored in each attribute. Enforce primary key

and foreign key constraints as indicated by your ERD. Write the SQL code to insert

your data into the SQL table structures. Report should include Case study

$-$Executive Summary and Introduction

$-$Assumption

$-$ERD Diagram

$-$Relation Schema

$-$Business Rules

$-$Cardinalities and Connectivity

$-$Normalisation steps $(3$NF$)$ that include dependency diagrams for each entity.

$-$Data Dictionary$-$DDL includes screenshots from Xampp that show the database name $($student ID$)$

$-$DML includes screenshots from Xampp that show the database name $($student ID$)$

$-$A Txt file that includes all the SQL code $($DDL and DML Commands$).$The DDL statements are required to implement the conceptual data model above.

Appropriate constraints must be created. Students must follow the same naming

conventions i$.$e$.$ lower for keywords, uppercase names for tables and attributes with an

underscore between words and a new line for each clause. Students should use the

default schema i$.$e$.$ there is no need to create one.

$-$ DML statements to insert sufficient $(5$ rows$)$ data into the database to correctly

display the inserted data in tables.