The table shown in Figure 1 is susceptible to modification anomalies. Provide examples of insertion, deletion, and update anomalies. Describe and illustrate the process of normalizing the table to 3NF. State any assumptions you make about the data shown in this table. I want to see you take the table through appropriate structural changes to 1NF, then 2NF, then 3NF. Start by listing the functional dependencies and then resolve by modifying the table structure. Show the table structures in the following format Table_Name( primary key, attribute1, attribute2, attributeN, foreign_key) Legend Bold and underline -> primary key Italicized -> foreign key

The table shown in Figure 1 is susceptible to modification anomalies. Provide examples of insertion, deletion, and update anomalies. Describe and illustrate the process of normalizing the table to 3NF. State any assumptions you make about the data shown in this table. I want to see you take the table through appropriate structural changes to 1NF, then 2NF, then 3NF. Start by listing the functional dependencies and then resolve by modifying the table structure. Show the table structures in the following format Table_Name( primary key, attribute1, attribute2, attributeN, foreign_key) Legend Bold and underline -> primary key Italicized -> foreign key

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Q: 3. The QR table records the workshop attendance of students. The BB table is a master table that…

A: Answer

Q: Your task is to break this up into a list of "normalized" tables that you describe using the…

A: We have to demonstrate your understanding of normalization and splitting tables Part One: Use…

Q: The ability to protect the operating system is essential for ensuring that it runs properly. In this…

A: The answer:

Q: What is a surrogate key, and when should you use one? What are the defects created within…

A: A surrogate key is a unique identifier assigned to a row in a table that does not have any business…

Q: Give four reasons to create an instance of your relational schema with sample data?

A: Given: Give four reasons to create an instance of your relational schema with sample data?

Q: Using a normalized version of the Index metadata relation may lead to subpar performance, and the…

A: Version of the Index metadata relation that is normalized Since the index attribute field comprises…

Q: Implement a new independent entity phone in the Sakila database. Attributes and relationships are…

A: There is only one error in the above code and that is the ALTER TABLE command on the address table.…

Q: Why are some table structures deemed terrible while others are deemed good? How can you know which…

A: Given that; The topic is about Why are some table structures deemed terrible while others are deemed…

Q: What would be introduced as criteria of a table being in first normal form? a.) There should be no…

A: First Normal form:- A relation in a relational database has a property called first normal form…

Q: Provide the PK Provide the partial dependencies, if there are any Provide the transitice…

A: Primary Key: Primary Key is a column which is a unique data in the table . All records in…

Q: The reasons why using a normalised version of the Index metadata relation could result in poor…

A: Index metadata in the context of databases refers to the data about the indexes in a folder. This…

Q: Explain normalization technique with respect to a redundant and non-redundant database table.

A: The Normalization technique with respect to a redundant and non-redundant database table:…

Q: Indicate the changes (using the shorthand representation) that you would need to make to the…

A: Question 1.) Indicate the changes (using the shorthand representation) that you would need to make…

Q: Split the list from part D into tables such that each has only one theme. Create appropriate ID…

A: Modification problem:- Routines to modify the problem change an object after it is created. Changes…

Q: 5.6 LAB - Implement independent entity (Sakila) Implement a new independent entity phone in the…

A: First check whether phone_id column exists in your customer, staff, and store table or not. If…

Q: Could you find the reasons why this set of data items is not in 3NF and tell what normal form if any…

A: A relation is within the third type if there's no transitive dependency for non-prime attributes…

Q: can you provide me with details on the progression from 1NF to 3NF form with the relation between…

A: Normalization: The above database is not normalized as follows: To normalize the database, each…

Question

TASK THREE: Reason about and normalize a poorly designed table

| staffNo | dentistName | patientNo | patientName | appointment date | time | surgeryNo |
|---------|---------------|-----------|-------------|------------------|-------|-----------|
| S1011 | Tony Smith | P100 | Gillian White | 12-Aug-03 | 10.00 | S10 |
| S1011 | Tony Smith | P105 | Jill Bell | 13-Aug-03 | 12.00 | S15 |
| S1024 | Helen Pearson | P108 | Ian MacKay | 12-Sept-03 | 10.00 | S10 |
| S1024 | Helen Pearson | P108 | Ian MacKay | 14-Sept-03 | 10.00 | S10 |
| S1032 | Robin Plevin | P105 | Jill Bell | 14-Oct-03 | 16.30 | S15 |
| S1032 | Robin Plevin | P110 | John Walker | 15-Oct-03 | 18.00 | S13 |

*Figure 1: Details of patient dental appointments.*

The table shown in Figure 1 is susceptible to modification anomalies. Provide examples of insertion, deletion, and update anomalies.

Describe and illustrate the process of normalizing the table to 3NF. State any assumptions you make about the data shown in this table. I want to see you take the table through appropriate structural changes to 1NF, then 2NF, then 3NF.

Start by listing the functional dependencies and then resolve by modifying the table structure.

**Show the table structures in the following format**

Table_Name(*primary_key*, attribute1, attribute2, attributeN, *foreign_key*)

**Legend**
- **Bold and underline** -> primary key
- *Italicized* -> foreign key

Expert Solution

This question has been solved!

Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.

SEE SOLUTION Check out a sample Q&A here

Step 1: An analysis of the question at hand.

VIEW

Step 2: Normalize a Poorly Designed Table to 3NF

VIEW

Solution

VIEW

Step by step

Solved in 3 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions

Explain why utilizing the normalized Index metadata relation will result in inferior performance. Provide a normalized version of the relation.
Using a normalised version of the Index metadata relation could have a negative impact on performance.
Why may using a normalized version of the Index metadata relation lead to lower performance, and what causes this?
The following is the relation notation of a database table in 1NF ORDER(CustNum, CustFN, CustLN, OrderNum, OrderDate, ProdNum, ProdDesc, Qty, UnitPrice) The functional dependencies are given below: CustNum → CustFN, CustLN OrderNum - OrderDate, CustNum, CustFN, CustLN ProdNum → ProdDesc OrderNum, ProdNum -> UnitPrice, Qty Use the functional dependencies above to answer the following questions. Don't not make additional assumptions and functional dependencies. A. List all partial dependencies in the relation only (if any) using the dependency numbers. Don't write the dependencies in X->Y format, just indicate their numbers (which are listed on the left side, i.e., 1, 2, 3, 4). Indicate only the dependencies that you think are partial. B. Identify and show the 2NF. Show primary keys and foreign keys references. Use relational notation (show references). Write down all relations and their references below. C. List all transitive dependency in your answer to question b (if any)…
Provide an example of a database table that is not in 1NF and normalize it to the first normal form.
Your task is to break this up into a list of "normalized" tables that you describe using the text-based "relational notation". You will need to break this example into as many separate tables as you feel is appropriate to remove all modification anomalies. Please submit your relational notations in an ordinary word document along with the sample data in a "grid". I'm expecting to see both PK and FK notations in your submission. Note: I'm not asking for a database... I'm asking for a list of tables in relational notation plus the sample data in rows/columns. Part One: Use text-based relational notation to describe each table/field Note: I am not yet requiring you to solve the "multi-column" problem Show PK and FK (use Microsoft Word's underline and italics tools) Part Two: Create a "grid" for each table with the all of the rows/columns of data for that table after normalization (to include PKs/FKs) Make sure you don't have any duplicate rows in your "grid" Note: You can just do…
Normalise the following table named 'ORDER' to remove modification anomalies showing all steps for 1NF, 2NF and 3NF. State any necessary assumptions to support your answer. Important : Provide appropriate justification for each normalisation step. ORDER (orderNo, orderDate, partNo, partDescription, numOrdered, quotedPrice) Primary Key: orderNo, partNo Given that the following dependencies exist: orderNo -> orderDate, partNo, partDescription partNo -> partDescription, quotedPrice orderNo, partNo -> numOrdered Subject: MYSQL