Reference no: EM131960186

A. Given the relational tables as follows:

SHIP(Shipname, Captain) CREW(Crewname, Hometown, Shipname) GROWS(Hometown, Flower) ALLERGIES(Crewname, Flower)

Please use SQL statement to construct the following queries:

(You are not supposed to use any aggregation functions such as count(), sum(), etc.; you are assumed that you do not know the data values of Shipname, Captain, Crewname, Hometown, Flower, etc. in the database table when you query the database table).

a. For each crew, if the crew is allergic to no flowers that grow on his/her hometown, then list the crew's name.

b. For each crew, if the crew is allergic to all flowers that grow on his/her hometown, then list the crew's name.

c. For each crew, if the crew is allergic only to the flowers that grow on their hometown, then list the crew's name.

d. For each crew, if the crew is allergic to exactly the same flowers that his/her captain is allergic to, then list the crew's name.

B. Explain how functional dependencies can be used to indicate that:

a.) A one-to-one relationship set exists between entity sets advisor and student. b.) A one-to-many relationship set exists between entity sets advisor and student.

C. Given the relational schema R, where X and Y are attribute sets such as: X ⊆ R and Y ⊆ R, please elaborate the meaning of the functional dependencies such as: ø → Y and X → ø.

D. Let F consists of the following functional dependencies for relation schema R(ABCDEG) as follows:

AB → C

A → B

B → A

C → A

BC → D

ACD → B

D → EG

BE → C

CG → BD

CE → AG

a. Find the minimal cover of F (minimize the functional dependency set F).

b. What is the key for schema R(ABCDEG)?