Identify an appropriate data type for Prod_Price.

Construct the steps in a query to output the names of all products supplied by Veggy Co. with prices in the range from 4.00 to 10.00 inclusive.

Outline the redundant data in this relation.

Explain the problems that may arise for the following function performed on the given relation. In your answer, you should give an appropriate example: Inserting a new tuple with an item that is supplied by existing supplier.

Explain the problems that may arise for the following function performed on the given relation. In your answer, you should give an appropriate example: Deleting a tuple from the relation.

Explain the problems that may arise for the following function performed on the given relation. In your answer, you should give an appropriate example: Modifying a specific attribute value in the tuple.

Construct the database in the 3rd normal form (3NF) using the notation

Greengrocer (Prod_ID, Prod_Name, Prod_Price, Supp_Name, Supp_Contact, Supp_Phone)You must show all your workings.

You must show all your workings.

Award up to [1 max].
Real number/float;
Accept currency.

Award up to [3 max].
Award [1] for selecting correct field from the relation.
Award [1] for correct supplier name comparison.
Award [1] for correct checking the price range.
Award [1] for correct logical operations.

Example answer 1:

SELECT Prod_Name FROM Greengrocer
WHERE Supp_Name == 'Veggy Co.' AND Prod_Price >= 4.00 AND
Prod_Price <= 10.00

Example answer 2:

SELECT Prod_Name FROM Greengrocer
WHERE NOT (Prod_Price <4.00 OR Prod_Price >10) AND
Supp_Name == 'Veggy Co.'

Example answer 3:

SELECT Prod_Name FROM Greengrocer
WHERE NOT (Prod_Price <4 OR Prod_Price >10 OR Supp_Name !=
'Veggy Co.' )

Accept logically equivalent answers.

Award up to [2 max].
Redundant data unnecessarily repeats in a relation;
Supplier data (name, phone, contact) are repeated;
For every item that is supplied by the same supplier;

For example,
Items P118, P122 and P220 have the same supplier (Veggy Co)
Items P219, P111, P121 and P211 have the same supplier (Fruit and Veggie)

Note: Accept any correct example from the table given, such is the following:

Award up to [2 max].
Award [1] for explaining a problem that may arise (such as inconsistency, inaccuracy / unnecessary additional/repeated work needed when updating).
Award [1] for including example with reference to the relation given.

Example answer:
When inserting a tuple supplied by the existing supplier, for example, inserting:

All data about supplier should be accurately entered;
And consistent with the data about this supplier in all other tuples;

Award up to [2 max].
Award [1] for explaining a problem that may arise (such as inconsistency, inaccuracy).
Award [1] for including example with reference to the relation given.

Example answer:
Deleting a tuple could remove data which is not intended to be lost;
For example; deleting the following tuple.

Which is the only tuple in the relation which holds data about New Fruits suppliers (all data about this supplier will be lost);

Award up to [2 max].
Award [1] for explaining a problem that may arise (such as inconsistency, inaccuracy/unnecessary additional/repeated work needed when updating.
Award [1] for including example with reference to the relation given.

Example answer:
Modifying/changing the phone number for Fruit and Veggie. would require that each and every tuple containing item supplied by Fruit and Veggie should be modified (unnecessary work);

Otherwise the data in the relation would be inaccurate (for example, would contain different/incorrect phone numbers for the same supplier);

Award up to [8 max]. 
Award [1] for explaining/clearly showing each of the steps.
Award [1] for identifying two entities (Product and Supplier) in Greengrocer.
Award [1] for choosing/identifying a primary key in relation Supplier.
Award [1] for clearly showing that the relation must be in 1NF and then could be put to 2NF.
Award [1] for showing that each tuple in 2NF has a primary key.
Award [1] for showing that in 2NF all attributes are on the whole dependent of the primary key.
Award [1] for introducing new relations or using a foreign key.
Award [1] for showing that the relation must be in 2NF and then could be put to 3NF.
Award [1] stating that there are no transitive dependencies in 3NF.

Note: Because there is significant inconsistency in textbooks’ accounts of precisely what constitutes 1NF and 2NF, award marks depending on the explanation. Please see the two different example answers.

Example answer 1:
Given relation
Greengrocer(Prod_ID, Prod_Name, Prod_Price, Supp_Name, Supp_Phone,Supp_Contact)

There are two entities in this relation (Product and Supplier).

Product(Prod_ID, Prod_Name, Prod_Price)
Supplier(Supp_ID, Supp_Name, Supp_Phone, Supp_Contact)

Primary key (Supp_ID) in relation Supplier is added to make sure that it is unique because it might be that two suppliers have the same name.

Product(Prod_ID, Prod_Name, Prod_Price, Supp_ID)
Supplier(Supp_ID, Supp_Name, Supp_Phone, Supp_Contact)

To construct to 2NF relation must be in 1NF;
Each tuple in 2NF must have a primary key;
And all attributes in the 2NF must be dependent on the whole of the primary key.

New relation is introduced.

Product(Prod_ID, Prod_Name, Prod_Price)
Supplier(Supp_ID,Supp_Name,Supp_Phone,Supp_Contact)
ProdSupp(Prod_ID, Supp_ID)

There are two different contacts with the same name (Mia Abiss) at the two different companies.
Assuming that each contact has a unique phone number, a new relation could be introduced in which Supp_Phone is a key.

Supplier(Supp_ID, Supp_Name)
SupplierContact(Supp_Phone, Supp_Contact)
Product(Prod_ID, Prod_Name, Prod_Price)
ProdSupp (Prod_ID, Supp_ID)

The relation is in the 2NF and there are no attributes which are not dependent on the key.
There are no transitive dependences so it is in the 3NF.

Example answer 2:
The table is already in 1NF because all values are atomic. There are no “fake” attributes/columns, like Supp_Phone1, Supp_Phone2;
So there are no repeating groups, either obvious or created by repeating columns for what is really the same attribute;
To be in 2NF, in addition to being in 1NF;
Every non-prime attribute must be dependent on the whole of every key;
To be in 3NF, in addition to being in 2NF;
All the attributes in a relation must be determined only by the key(s);
And there should not be any transitive dependences;

There are two entities (Supplier and Product);
Supp_ID should be introduced;

Supplier(Supp_ID, Supp_Name, Supp_Phone, Supp_Contact)
Product(Prod_ID, Prod_Name, Prod_Price, Supp_ID*)

Where underlining marks primary key(s) and *foreign key(s);

There are two different contacts with the same name(Mia Abiss)at the two different companies.

A new relation introduced in which Supp_Phone is a key;

SupplierContact(Supp_Phone, Supp_Contact, Supp_ID*)
Supplier(Supp_ID, Supp_Name)
Product(Prod_ID, Prod_Name, Prod_Price, Supp_ID*)