Posted: June 25th, 2022

COIT20247 Database Design And Development

Question:

Assessment Task

The sample solution shows how to map the ERD into a set relations of at least Third Normal Form (3NF).

Your relations must meet 3NF.

You don’t have to be proud of your relationships.

Choose any two (2) relations you have from the previous step (1a) and do the following:

List all functional dependencies that exist in the

Demonstrate that your relationship meets the Third Normal Form (3NF).

2 Implementation of a Relational Database

Notice: You can use the relationships you have created in step 1-Normalization above to create a database.

Microsoft Access version 2010 or higher is the best tool to do this work.

Microsoft Access should be recommended because your instructor/unit lecturer will be able provide you with adequate support in using this tool.

You can choose to use a different DBMS but it is not guaranteed that your lecturer/tutor can provide sufficient support.

This document assumes you are using MS-Access.

These are the tasks you will need to complete in order to implement the ER model.

All relations should be created in Microsoft Access.

Take into account each attribute of every table and make the appropriate choices about data types & sizes as well as indexes, required/not necessary, and validation rules.

You should make the right choices for each attribute, and ensure data integrity.

(Please refer to the Data Integrity section for more information about data integrity requirements.

As appropriate, create relationships.

Cascade update/delete options are available whenever necessary.

Access has created a default index for each table.

Make sure that your indexes adhere to the guidelines provided in the unit

You can add your own sample data to the database.

It is essential that you include enough sample data in order to test your queries and create a report.

The expected results of your query questions could depend on the actual data.

Data Integrity

To enforce the following requirements, integrity constraints must be implemented within your database

Fees and Charges should only be expressed in positive currency.

It is not a good idea to predate date values.

Names of patients/medical professionals should not be null

Only Australian States/Territories should have state values.

It is important to not lose sight of the value of skills and experience.

The discount amount must be positive and not exceed $50

Information requests

To answer the following information queries, create queries.

You should not use Access query builder (QBE), but you can create queries manually with SQL view/editor.

Your SQL statements may be evaluated in relation to the effectiveness of the information request queries.

Your SQL statements must be consistent with the correct business logic and SQL syntax.

You should have enough sample data to ensure that each query produces the correct output.

Display the information of any medical specialist who may have performed/completed any appointment.

Display details about the appointment, the name of the patient, and the medical specialist involved in the most costly appointment.

Display the name of each medical specialist and the number completed so far.

What patients have seen more than one specialist?

Only display the information of such patients.

Display the details for the patients whose names contain the word “more”.

Display the results in ascending order by name.

Report

A simple report object should be created that shows the skills and experience of each medical specialist.

You must present the details in ascending order by the first name of each medical specialist.

Report on Implementation

A brief implementation report must be provided detailing your experience with the assignment.

Any of these topics can be discussed:

Two interesting facts or procedures that you learned through this assignment.

You completed the assignment with a high level of success despite having to work through a number of difficult or complex requirements.

To show that you’ve thought through the challenges that can arise from implementing a relational data base, use the implementation report.

Answer:

Introduction This report details the implementation of the database.

CQMC is given a MS Access database.

Below are the key features and points of the relational table.

It is the primary key to the Patient table.

It is the primary key to the Specialist table.

The table should not contain transitive dependency.

Both Specialist and Patient have the same condition.

1988 SQL Queries SQLQuery 1 This query returns only records for specialists who have completed appointments.

Query 2 This query returns the most expensive appointment.

Query 3 This query returns the number of completed appointments and names of specialists.

Query 4 This query returns the details of all specialists as well as their completed appointments.

Query 5 This query returns details about patients as well as the number of consultations they have had with specialists.

Query 6 This query returns details about patients whose names contain the word’more’.

Summary Report This report shows the details of specialists.

Limitations in The Assignment – Database forms and other key features are not part of this assignment.

The assignment to build does not include outer join queries.

Learning Factors in the Assignment: Making a database report, complex queries use of validation checks and conclusion. The entire assignment is based on the normalization and implementation of the database.

This is the basis of the report, which provides detailed information about every aspect of each database feature and fact.

Refer to holowczak.com 2016. Converting E.R Models into Relational Models. Viewed May 20, 2017.

Ross R.G. Entity Modelling: Techniques & Applications, 1988