Please come up with database report based on Aged care facility Management.Students are expected to develop and provide their own case study and then interpret that to

create an ER Model of the system.

They are then expected to provide a physical implementation of the ER model in the form of

the DDL to create the required tables, attributes, and relationships.

Students are then required to provide the DML to insert sufficient information into the database

to answer a set of queries.

They should also provide proof of the running DDL and DML queries by providing images of

the output obtained.

It is a requirement of this assignment that students use Xampp for the database components.

The submission must be presented in the format of a professional report. Further information is

given in the Detailed Requirements and Marking Criteria sections of this document.your chosen scenario must include at least Ten to Tweleve entities

that are related to each other.A final consideration when thinking of your scenario is the list of SQL

queries that you will need to execute within your database to prove the creation of tables and

insertion of data. For instance, you will need to include certain search and statistical functions,

so your scenario will need to be sophisticated enough to make these queries possible. Your ER Diagram must include Ten to Twelve entities.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.

$$ One $($or more$)$ of your entities must contain a Date 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.

Detailed Requirements

$$ This assignment is an individual assignment. It is a requirement of this assignment that

students use XAMPP or Microsoft SQL for the database components.

$$ Students should submit a report that follows the format of a business$/$professional report

and contain, at a minimum, a Title Page, Table of Contents, Executive Summary and

References $($if cited$)$ and the following content:An ER model of the case study system. This should conform to the third normal form.

Students should be aware there are disjoint subtype entities. Students can use any

drawing package to present the ER diagram, but the diagram should use the Crows foot

notation and conform to the standards identified in $($Coronel and Morris $2018).$ These

include that entities are shown in a rectangle with the name of the entity in grey at the

top separated from two columns below with PK$,$ and FK identifiers, where appropriate

in the first column and attributes in the second column. Primary key attributes are to be

separated from other attributes by a line across the rectangle. All entities are to be in

upper case and attribute names are to be in capitalise. All relationships should be

labelled and identified as mandatory or optional. All connectivity, participation and

cardinalities $($if there are specific limits$)$ should be shown.

$2.$ A screenshot of the XAMPP showing the creation of a database with the name

ICT$702\_$yourStudentID$\_$Individual$\_$Assignment.

$3.$ 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.

$.$

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

display the inserted data