KIT102 Data Modelling Assignment

Due: Friday 25 May, 23:55, Week 12.

All submissions are through MyLO.

The marking rubric is available on MyLO.  You should read the rubric before submitting your assignment.

This assignment will take a lot of time.  You need to start early.

Task

You have been presented with the case study below.  You need to turn this into a MySQL database.

These are the requirements:

1. Read the case study and develop an Entity-Relationship Diagram using the conventions for this unit.  This diagram needs to describe all of the business rules in the case study.
2. Using this unit’s Entity-Relationship to Relational Schema Algorithm, convert your Entity-Relationship Diagram into a relational schema.
3. Using MySQL create the database based upon your relational schema.
4. Enter two records into each of the relations that you have created in your MySQL database.
5. Correctly and appropriately comment your code so that it can be maintained by your client.
6. If you are stuck at any stage – speak to your tutor or email your Unit Coordinator: matthew.springer@utas.edu.au Ask for help.

Submission Requirements

1. In a Word Document or PDF, submit your Entity-Relationship Diagram and relational schema INCLUDING all of the steps involved in the conversion algorithm to MyLO.  Using Visio is recommended for the E-R Diagram, but any diagramming package, or a hand-drawn diagram included as an image in your Word Document or PDF is acceptable.
2. In a separate text document, submit your file called Assignment.sql to MyLO in which you have recorded all of the MySQL code to create and populate your database.

Case Study

Jerry Kyle and Associates has been providing online counselling, psychological services and paternity testing since 2008 from its inner-city Hobart office in Collins Crt.  The service has proven to be quite popular, and lucrative in the 10 years that it has been operating, but with Hobart’s notorious traffic peak-20-minutes, and the threat of everything becoming worse by moving the University of Tasmania into the city centre, the service had decided to abandon its Collins Crt headquarters.  Now leaning heavily on the recently available NBN services, Jerry Kyle and his team have been able to work from home wherever fibre to the premises is available.  The existing MEDINFO database system has been deemed inappropriate for the new types of analysis that are required and so the MEDINFO Update project has been commissioned.

Jerry Kyle and Associates provides personal counselling, psychological treatment and paternity testing.

Potential Clients are assigned an ID when they enter their name into the web-based system.  Each client registers their address, consisting of a street number, street name, suburb and postcode.  Each client has one and only one address, but each address may have more than one client.

Each client meets zero or more professionals before they are finished, and each professional will meet one or more clients.  Professionals are identified by their Professional ID.  Professional details are also collected. Because this is a small organisation, each of the professionals is managed by zero or one professionals, except Jerry Kyle who does not have a manager.  The professionals may manage zero or more professionals.

Each mental illness will affect zero or more clients, and each client by be affected by zero or more mental illnesses.  Mental illnesses are identified with a mental illness code and a description of the mental illness.

Each mental illness has zero or more treatments, and each treatment is used for one and only one mental illness.

Each professional holds one and only one accreditation, and each accreditation may have zero or more professionals accredited by it.  Accreditations are uniquely identified by their name, but the MEDINFO database also holds some details about the accreditation.

Given that each of the professionals specialise in a different area, each treatment is recommended by one and only one professional, but a professional may recommend many treatments or none at all.  Each treatment has a treatment code and details about the treatment.

Each treatment must be certified by the one and only one government certifier.  The government certifier may certify zero or more treatments.  The government certifier is identified with the certifier ID, and some details are collected about the government certifier.

A paternity test may be requested by one or more clients, but each client needs only request one and only one paternity test.  Paternity test are identified by a test ID and some test details are also recorded.

A testing laboratory will be identified by its Lab Code.  Lab details are also recorded.  Each paternity test is sent to one and only one testing laboratory, and that testing laboratory may receive zero or more paternity tests.

The testing laboratory generates zero or more test results and test results are generated by one and only one testing laboratory.

Jerry Kyle and Associates hopes that these business rules will help you design and implement a database that includes some sample data and is appropriately commented so that Jerry Kyle’s in-house staff can further implement and develop the database.