[1]. Create the operational database based on the above E/R Diagram, and then populate with
sufficient numbers of records in each table. It is suggested that each table would have 5-20
records depending on the use of that table in the operational database. You need to create a script
file containing the CREATE TABLE and INSERT INTO commands.
You can follow the list of services from this link:
https://www.monash.edu/health/medical/services.
[2]. Then develop a GOVERNMENT HOSPITAL star schema. Identify the fact table, dimensions
and attributes required to support the schema. If the star schema consists of a Bridge Table, you
have to also include the Weight Factor and List Aggregate.
The result of this task is a star schema diagram. You can use any drawing tool, such as
Lucidchart, to draw the star schema.
[3]. Validate your star schema using the Two-Column Table Methodology. You are required to
illustrate some two-column tables for this task based on your star schema design.
[4]. Write the SQL commands to create the fact and dimension tables. You need to create a script
file containing the appropriate SQL commands to create the fact and dimension tables.
[5]. Write the SQL commands to answer the following queries: (you need to make sure that there are
records in your fact and dimensions tables. For each of the following queries, write the SQL and
show the results):
a) Show the total number of patients making appointments during Winter.
b) Show the total service charged for each service cost type.
c) Show the total number of patients by each age group (infant senior 65+) in April 2020.
d) Show the total service charged for general medical consultations in each suburb.