Reference no: EM132311790
Oracle SQL Modeler Assignment: Database Design - Monash Dental
Monash Dental Case Study - Read the case attached case study.
TASKS -
Moodle Part A Submission: Please ENSURE your name and ID are shown on every page of any document you submit. If a document is a multipage document, such as for the normalisation, please also make sure you include page numbers on every page.
1. Using LucidChart, prepare an INITIAL conceptual model (Entity Relationship Diagram) for Monash Dental (MD).
- For this initial conceptual model, include what you see as identifiers (keys) for each entity only (other attributes ? must not be included?) and all relationships.
- Surrogate keys must not be added to this model. Connectivity and Participation for all relationships must be shown on the diagram?. Participation must show both minimum and maximum values (using a single line for 1:1 is not acceptable for this unit).
Moodle Part B Submission:
2. Perform normalisation to 3NF for the data depicted in the sample documents (note that these are two sample documents are instances of the same form). The approach you are required to use is the same approach as shown in the normalisation lab solution. The normalisation must begin by you representing the supplied documents as a single UNF form.
During normalisation, you must:
- Not add surrogate keys to the normalisation.
- You must include all attributes (you must not remove any attribute as derivable)
- Map the document structure into UNF as per the FIT9132 guidelines
- Clearly show UNF, 1NF, 2NF and 3NF.
- Clearly identify the Primary Key in all relations.
- Clearly identify the partial and transitive dependencies (if they exist) in all 1NF relations. You may use a dependency diagram or alternative notation (see the normalisation tutorial sample solution for a possible alternative representation).
Your attribute names as used in your normalisation and those on your conceptual/logical models must be consistent i.e. the same name used on each for the same property.
3. Using LucidChart, prepare a FULL conceptual model (Entity Relationship Diagram) for Monash Dental (MD).
- For this FULL conceptual model, include what you see as identifiers (keys) for each entity, ?all required attributes?and all relationships.? This full model will be based on your feedback from your Part A submission, the normalisation above and further reading of the case study. It may be necessary to revise/update this model while developing your logical model in part 4 below.
- Surrogate keys must not be added to this model. Participation and connectivity for all relationships must be shown on the diagram?.
4. Based on your final full version of your conceptual model, prepare a logical level design for the Monash Dental database.
- The logical model must be drawn using the Oracle Data Modeler. The information engineering or Crow's foot notation must be used in drawing the model.
- All entities depicted must be in 3NF.
- All attributes must be commented in the database?.
- Be sure to include the legend as part of your model.
5. Generate the schema for the database in Oracle Data Modeler and use the schema to create the database in your Oracle account. The only? edit you are permitted to carry out to the generated schema file is to add header comment/s containing your details (student name/id) and the commands to spool/echo your run of the script.
- Capture the output of the schema statements using the spool command.
- Ensure your script includes drop table statements at the start of the script.
- Name the schema file as md_schema.sql.
Note - Need part A and B at the same time.
Attachment:- Assignment File - Case Study.rar