Normalisation of a database schema assessment

Assignment Help Database Management System

Reference no: EM134038021 , Length: Word Count:1500

Database Design and Management

Normalisation of a Database Schema

Your Task
This assessment is to be completed individually. Each student will be allocated a randomised system scenario using the MyKBS Assessment 2 Scenario Generator. Based on your allocated system, you will design an unnormalised database schema and then normalise it step-by-step through 1NF, 2NF, and 3NF. You will document your process, create dependency diagrams, and critically evaluate the role of ChatGPT in suggesting the initial table and functional dependencies.

Assessment Description
This assessment evaluates your understanding and application of database normalisation principles. You will transform an unnormalised database schema into its highest normalised form (3NF). Your schema must be based on the randomised scenario allocated to you. You must design your own attributes (at least 8) based on the business scenario, then use ChatGPT to generate a sample table with at least 8 entries and to suggest possible functional dependencies of the original table. ChatGPT must not be asked about the normalisation process itself. Instead, you will critically analyse its suggestions for the initial table and dependencies as part of your report. This assessment relates directly to Database Management DBMS concepts.

This assessment aims to achieve the following subject learning outcomes:

LO2: Construct database models.
LO3: Use normalisation techniques for the purpose of data integrity.

Assessment Instructions

To normalise a database schema from its initial unnormalised state to the third normal form (3NF) and document each process step, including dependency diagrams.

Steps to Follow:
Assessment 2 Scenario Generator to obtain your allocated system scenario.

Based on your scenario, design an unnormalised table with at least 8 attributes relevant to the business case. When selecting your attributes, ensure that your initial unnormalised table is not in the first normal form (1NF). The table should include repeating groups, multi-valued attributes, and sufficient complexity to demonstrate different types of dependencies (full, partial, and transitive). This will ensure that you have enough material to apply the normalisation process effectively.

Use ChatGPT (free version) to generate a sample table with at least 8 records based on your chosen attributes and suggest functional dependencies for the original unnormalised table only.

Example Prompt for ChatGPT:
"I am designing a database for a [system allocated by scenario generator]. The database includes the following attributes: [list your 8 or more attributes]. Can you generate a sample unnormalised table with at least 8 records using these attributes, and then list the possible functional dependencies for this table? Please do not provide any normalisation steps."

Do not use ChatGPT for any steps related to the normalisation process (1NF, 2NF, 3NF).

Critically analyse ChatGPT's suggested table and functional dependencies in a dedicated section of your report, highlighting strengths, limitations, and your evaluation of accuracy.

Carefully examine the generated table and scenario to understand the structure and the data stored in the database.

List all functional dependencies in the unnormalised schema (the original tables).

Manually carry out the normalisation process (1NF, 2NF, 3NF) and document each step, including dependency diagrams:

Transform to the first normal form (1NF) and ensure the table complies with the first normal form (1NF) rules. Draw the dependency diagram for the table in 1NF.

Transform to the second normal form (2NF) and draw the dependency diagram for the table(s) in 2NF.

Transform to the third normal form (3NF) and draw the table(s) dependency diagram in 3NF.

Provide a step-by-step explanation of how you transformed the schema from its initial form to 1NF, then to 2NF, and finally to 3NF.

Document the final set of tables, including table names, attributes, and primary/foreign keys. These database structures can also be considered within Oracle Database Management contexts.

Write a report (1500 words) that includes your schema design, ChatGPT analysis, normalisation steps, and diagrams.

Include an appendix with your ChatGPT prompts and responses, with annotations showing your evaluation.

Reference no: EM134038021

Questions Cloud

Pharmacological methods of cervical ripening : What are two pharmacological methods of cervical ripening? How do these medications work? What is a usual dose and and how is it administered?

Completing the shortness of breath and chest pain : An adult patient presents to the emergency department completing the shortness of breath, chest pain and impeding sense of doom.

Medical term for underlined phrases : Fill in the correct medical term for the underlined phrases. The answers will be one word unless otherwise indicated.

Dosage started to not work to reduce cadells pain : He needs to increase Cadell's dosage of pain medication because the previous dosage started to not work to reduce Cadell's pain.

Normalisation of a database schema assessment : TECH1400 Database Design and Management, Normalisation of a Database Schema Assessment - design an unnormalised database schema and then normalise

What is jeana most likely suffering from : What is Jeana most likely suffering from? Juvenile rheumatoid arthritis Growing pains Osteosarcoma Slipped femoral capital epiphysis.What is Jeana most likely s

Discuss trauma symptoms : Describe the nature and prevalence of the selected trauma? Discuss trauma symptoms?

Shift report on new admission of laboring woman : The nurse has just received change of shift report on a new admission of a laboring woman.

Chaplaincy across multiple health systems : This case study reflects situations that I encountered in my chaplaincy across multiple health systems.

User Account

All Pages