Examine the following relation and answer the following ques

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.

AGE

PRICE

6

5

6

5

5

6

5

Is this relation in at least 1NF? Why or why not?

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.

What normal form is this relation currently in?

Describe the specific modification anomalies that exist if we DELETE the tuple containing Stewart.

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.

Solution

Is this relation in at least 1NF? Why or why not?

YES, this is in 1NF. 1NF is a normAL form that contains only atomic values i.e. a value that cannot be divided. the relational table satisfies this,

-----------------

what is the primary key of the initial relation?

as the question explains that a primary key is one which is unique throughout the table and is not null. here we cannot just pick a single attribute as our key , since they are not unique. so we combine them to find a combination that is! Boy and Toy is a combination of attribute that is unique and not null and hence can be our primary key.

------------------------------

What normal form is this relation currently in?

this table is in 1NF.

to be in 2NF it must have all its non-key attributes to be fully functionally dependent on the primary key

PK - Boy, TOy

Boy, Toy --> Group, Age, Price

but.

Group --> Age

hence, it is not in 2NF

----------------------

Describe the specific modification anomalies that exist if we DELETE the tuple containing Stewart.

deleting stewart will have no effect on the table, since it is not present in any other tuple.

---------------------

to convert this to 3NF.

need to eliminate the functional dependencies to first make it 2NF we would to normalize it. What we observed is that Group --> Age , so we must break our original table in two one consisting of the other attributes and one consisting of Age such that Group in the first table cannot disrupt the functionally dependency with it there. let us give Boy to Age as its key. Now for 3NF we must eliminate the transitional dependencies .

Toy/Boy -- > Category

Category --> Price

but Cateogry does not give Boy.

so this is transitively dependent.

So let us move Price to a new table , and have Boy with it to act as the key.

now our relations become -

1 - Boy, Toy , Group , category;

2 - Boy, Price

3 - Boy , Age

Determine the number of 3NF relations you end up with after normalization, write this number, and then circle the number.

3

-----------------------------------------