Module 7 Critical Thinking Normalization Exercise (70 Points) Examine the follow
ID: 3722242 • Letter: M
Question
Module 7 Critical Thinking
Normalization Exercise (70 Points)
Examine the following relation and answer the following questions. Assume these are the values that exist for all time (e.g., the toys shown always cost the amount shown) and that boys with the same name are the same person.
BOY
GROUP
AGE
TOY
CATEGORY
PRICE
Charlie
6 year olds
6
Soccer ball
Sports
18.44
Jimmy
5 year olds
5
Hammer
Tools
7.99
Robert
6 year olds
6
Baseball
Sports
8.99
Jimmy
5 year olds
5
Soccer ball
Sports
18.44
Jimmy
5 year olds
5
Wrench
Tools
6.99
Charlie
6 year olds
6
Baseball
Sports
8.99
Stewart
5 year olds
5
Oracle
Computer Software
399.99
1. Is this relation in at least 1NF? Why or why not?
2. Assuming the values shown are the only possible tuples for all time, what is the primary key of the initial relation? Remember that a primary key must be unique and not null.
3. What normal form is this relation currently in?
4. Describe the specific modification anomalies that exist if we DELETE the tuple containing Stewart.
5. If necessary, decompose the initial relation into a set of non-loss 3NF relations by showing the relations, attributes, and tuples. Show complete relations with attribute headings and all data values in the tuples of your relations. Determine the number of 3NF relations you end up with after normalization, write this number, and then circle the number.
At the top of your Word file include your name, and the date.
BOY
GROUP
AGE
TOY
CATEGORY
PRICE
Charlie
6 year olds
6
Soccer ball
Sports
18.44
Jimmy
5 year olds
5
Hammer
Tools
7.99
Robert
6 year olds
6
Baseball
Sports
8.99
Jimmy
5 year olds
5
Soccer ball
Sports
18.44
Jimmy
5 year olds
5
Wrench
Tools
6.99
Charlie
6 year olds
6
Baseball
Sports
8.99
Stewart
5 year olds
5
Oracle
Computer Software
399.99
Explanation / Answer
1. Is this relation in at least 1NF? Why or why not?
Yes the relation is in 1NF. A relation is said to be in 1NF, if the domain of each attribute
is atomic, and each attribute contains only single value from that domain.
Here, every attribute has a single value, and the domain of each attribute is atomic.
So, the relation is in 1NF.
2. Assuming the values shown are the only possible tuples for all time, what is the primary key of the initial relation? Remember that a primary key must be unique and not null.
Given the given tuples are the only possible for all time, we can consider the BOY + PRICE
as a primary for the relation. Ofcourse there are multiple key possiblilities like BOY + TOY
we can pick any of them as primary key. And therefore, I designate BOY + PRICE as primary key.
3. What normal form is this relation currently in?
This is just in 2NF. This violates the 3NF. This is because the attribute group can be identified
by the non-key attribute age and vice-versa.
4. Describe the specific modification anomalies that exist if we DELETE the tuple containing Stewart.
If the Stewart tuple is deleted, you'll have the deletion anomaly. This is because, if you
delete this tuple, you'll loose the toy information.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.