Part $2($Normalization$)$

Consider the following relation:

In the above table, ACID is a unique identifier for each aircraft the airline owns. Each

aircraft is of a particular vendor and model. The seating capacity and flight range are

the same for all aircraft of a given vendor$/$model$.$ Each aircraft identified by ACID was

purchased on the purchase data given and has flown the number of hours in flight

hours.

a$.$ What is a primary key for this relation?

b$.$ Identify the non$-$trivial, full functional dependencies in this relation.

Consider the following relation:

The relation has the following non$-$trivial, full functional dependencies:

ISBN longrightarrow Title, Publisher Name, Publisher Phone, Editor Name, Editor Phone, Editor Gender, Book

Genre

Publisher Name longrightarrow Publisher, Phone

Editor Name longrightarrow Editor Phone, Editor Gender

Author Name longrightarrow Author Address, Author Age, Author Gender

ISBN, Book Format longrightarrow Book Pages,

ISBN, Book Format, Author Name longrightarrow Book Pages, Title, Publisher Name, Publisher Phone, Editor

Name, Editor Phone, Editor Gender, Book Format, Book Pages, Book Genre, Author Address, Author

Age, Author Gender

ISBN, Author Name longrightarrow Title, Publisher Name, Publisher Phone, Editor Name, Editor Phone, Editor

Gender, Book Genre, Author Address, Author Age, Author Gender

The relation also has the following multi$-$dependencies:

ISBN $$ Author Name, Author Address, Author Age, Author Gender

ISBN $$ Book Format, Book Pages

Books may have more than one author and may be published in more than one format

$($the format determining the number of pages$),$ but have a single publisher and editor.

To ensure completeness, books have a tuple for every author$/$format combination

$($hence the multidependencies$).$ That is$,$ if our textbook were published in both

paperback and hardback, it would have four rows in the table: two entries for each of

the two authors, differing in the book format attribute $($one having book format

paperback and the other having book format hardback$).$ In general, if a book has $n$

authors and $m$ formats, it will have $nm$ tuples in the table.

a$.$ What is a primary key for the relation?

b$.$ Produce a set of relations which are in third normal form.

c$.$ Are these relations also in Boyce$-$Codd Normal form?

d$.$ Are these relations also in Fourth Normal Form?