Consider the following table that stores data for pet-owners on an online pet-fan website: Pet(pet_id, name, pet_age, owner_id, since, owner_age, pet_friend_id). In this relational schema, each pet has a unique identifier (pet_id), a name (name), and an age (pet_age). Each pet can have multiple owners, e.g., a family that has a single dog. We identify each owner by its identifier (owner_id). Furthermore, each owner has an age (owner_age). Furthermore, we keep track of the date at which the owner started ownership of the pet (since). Finally, we keep track of all the other pets that are friends of a pet. Each such pet is identified by its identifier (pet_friend_id). Next, an example of an instance of this relational schema: pet_id name pet_age owner_id 1 Alicia 12 3 1 Alicia 12 3 1 Alicia 12 5 Alicia 12 Bo Bo Celeste 1 2 2 3 2995 5 5 7 7 1 since Jan. 12 Jan. 12 Feb. 17 Feb. 17 Mar. 5 Mar. 5 Dec. 22 owner_age pet_friend_id 24 2 24 29 29 21 21 99 3 2 3 1 3 1 W N N N N 2 2 Question 1. Provide a minimal cover of all realistic non-trivial functional dependencies that hold on the above relational schema. Argue, for each functional dependency, why this functional dependency hold. 3 HINT: We only require a minimal cover. Hence, there is no need for trivial functional dependencies and functional dependencies that can be derived from other functional dependencies. Part 2: Refinement of a table A book-ordering website wants to keep track of all their sales. To do so, they developed the following relational schema to hold all relevant information: 2. Are there any other non-trivial dependencies that hold on this table? If so, provide an example of such a dependency and argue why this dependency holds. HINT: E.g., multi-valued dependencies, inclusion dependencies, or join dependencies. In this relational schema, the following order information will be stored: i. The identifier id of the order, the identifier user_id of the user that placed the order, the location user_location of that user, and the date date at which the order was placed. Id Ui 1 1 1 1 Hamilton. 24 Dec. 1 Hamilton 24 Dec. 1 1 1 Hamilton 24 Dec. 24 Dec. 1 1 Hamilton 1 1 Hamilton 24 Dec. 1 1 -NNN22 Order(id, user_id, user_location, date, book_id, format, amount, isbn, title author, publisher, age_rating, child_friendly, category. 1 ii. The information on the books ordered. Each order can order several books and several different formats (e.g., hardcover, paperback, e-book) of the same book. Hence, we store the identifier book_id of the ordered book (as used internally by the website), the format format of the ordered book, and the amount of copies ordered. Next, an example of an instance of this relational schema (we use shorthand notations for each attribute: Id is id, Ui is user_id, Ul is user_location, D is date, B is book_id, F is format, Am is amount, Is is isbn, T is title, Au is author, P is publisher, Ar is age-rating, Cf is child_friendly, and Ca is category): iii. Publishing information of the book, including the ISBN, the title, one-or-more authors, the publisher, the age-rating of the book and whether the book is suitable for children, and, finally, one-or-more categories of the book. UI B D 1 Hamilton 24 Dec. 2 1 Hamilton 24 Dec. 2 Toronto. Toronto Toronto F Au P paperback paperback 3 2 paperback 3 paperback 3 2 Am Is T Ar Cf 3 1234 Book! Alicia ThePrinter 18+ no 1234 Book! Alicia ThePrinter 18+ no 1234 Book! Dafni ThePrinter 18+ no 1234 Book! Dafni ThePrinter 18+ no hardcover 6 1237 Book! Alicia hardcover 6 1237 Book! Alicia hardcover 6 1237 Book! Dafni hardcover 6 1237 Book! Dafni e-book 1241 Book! Alicia 1 1241 Book! Alicia Book! Dafni Book! Dafni Comic! Bo 1 ThePrinter 18+ no ThePrinter 18+ no ThePrinter 18+ no ThePrinter 18+ no ThePrinter 18+ no ThePrinter 18+ no ThePrinter 18+ no ThePrinter 18+ no TheCopier 5+ e-book e-book e-book 1 1241 1 1241 yes Hamilton 24 Dec. Toronto 25 Dec. 2 26 Dec. 27 Dec. 28 Dec. NNNN U N N N N N Hamilton 30 Dec. 5 paperback 1 1298 Ca CS Theory CS Theory CS Theory CS Theory CS Theory CS Theory Comedy The primary key of this table is "id, book_id, format, author, category". Furthermore, the following functional dependencies hold on this table: id user_id, user_location, date; user_location; user_id- book_id- id, book_id, format amount; title, publisher, age rating, child_friendly; book_id, format isbn; isbn book_id, format; child_friendly. age rating Question 3. Is the relational schema Order in 3NF? If so, then explain why Order is in 3NF. Otherwise, decompose the schema using the 3NF Synthesis algorithm (DECOMPOSE-3NF) and document each step you make while applying the algorithm. Provide the functional dependencies that hold in each relational schema in your resulting decomposition (a minimal cover suffices). Explain whether this decomposition is lossless-join and whether it is dependency-preserving (with respect to the original functional dependencies). Finally, decompose the example dataset according to the relational schema obtained from the decomposition algorithm. 4. Is the relational schema Order in BCNF? If so, then explain why Order is in BCNF. Otherwise, decompose the schema using the BCNF Decomposition algorithm (DECOMPOSE-BCNF) and document each step you make while applying the algorithm. Provide the functional dependencies that hold in each relational schema in your resulting decomposition (a minimal cover suffices). Explain whether this decomposition is lossless-join and whether it is dependency-preserving (with respect to the original functional dependencies). Finally, decompose the example dataset according to the relational schema obtained from the decomposition algorithm. 5. According to the consultant, the following multi-valued dependencies also hold: 3- author; and 3-> category, in which 3 are all attributes except the two attributes author and category. Is the relational schema Order in 4NF? If so, then explain why Order is in 4NF. Otherwise, decompose the schema using the 4NF Decomposition algorithm (DECOMPOSE-4NF) and document each step you make while applying the algorithm. Provide the functional dependencies that hold in each relational schema in your resulting decomposition (a minimal cover suffices). Explain whether this decomposition is lossless-join and whether it is dependency-preserving (with respect to the original functional dependencies). Finally, decompose the example dataset according to the relational schema obtained from the decomposition algorithm. 6. Does any of the above three decompositions of Order resolve all design issues of Order? If so, explain which decomposition(s) resolve all design issues. Else, provide an example of a design issue that was not resolved by decomposition.