Consider the following table that stores data for pet-owners on an online pet-fan website: Pet(pet_id, name,...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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. 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.
Expert Answer:
Answer rating: 100% (QA)
To assist you in understanding the database normalization process lets go through the questions one by one Part 1 Question 1 Minimal Cover of Functional Dependencies A minimal cover is a set of functi... View the full answer
Related Book For
Modern Systems Analysis And Design
ISBN: 9780134204925
8th Edition
Authors: Joseph Valacich, Joey George
Posted Date:
Students also viewed these databases questions
-
Among the reasons given in the textbook for companies to decide to enter foreign markets, which reason do you think predominates? Expl Compare and contrast the benefits of a company diversifying into...
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Draw an ERD for each of the following situations. (If you believe that you need to make additional assumptions, clearly state them for each situation.) Draw the same situation using the tool you have...
-
Using the 526 observations on workers in a certain dataset, we include educ (years of education), exper (years of labor market experience) and tenure (years with the current employer) in regression...
-
1. TRUE or FALSE? The ARPANet, the precursor to today's Internet, was funded primarily by the U.S. Department of Defense. 2. TRUE or FALSE? Because the ARPANet utilized ordinary phone lines to...
-
Refer to the previous question. Suppose that each of the audit activities can be crashed by the amounts indicated in the following table. a. What is the earliest the audit could be completed and what...
-
Fill in the Blank. The deflection of a rotating shaft becomes very large at the ____________ speed.
-
The Wall Street Journal reported that Juniper Networks, Inc.a maker of company network equipmentplans to offer its more than 1,000 employees the opportunity to reprice their stock options. Junipers...
-
solve x-x-12=0
-
For the network of Fig. 7.90, determine: a. VG. b. IDQ and VGSQ c. VD and VS d. V o 20 V 2.2 k 910 k DSS GS. 0 110 k 1.1 k
-
Information from the financial statements of the Heir Jordan Corporation are shown below. Prepare the pro forma financial statements and calculate EFN, assuming a 15 percent increase in sales, no new...
-
Consider the following apportionment problem: If there are to be 475 representatives, use the apportionment plan requested in Problems 56-60. Webster's plan North: Northeast: East: Southeast: South:...
-
In Problems 55-59, consider the following situation. A political party holds a national convention with 1,100 delegates. At the convention, five persons (which we will call \(\mathrm{A}, \mathrm{B},...
-
Consider the following apportionment problem: If there are to be 475 representatives, use the apportionment plan requested in Problems 56-60. Jefferson's plan North: Northeast: East: Southeast:...
-
In Problems 55-59, consider the following situation. A political party holds a national convention with 1,100 delegates. At the convention, five persons (which we will call \(\mathrm{A}, \mathrm{B},...
-
A box contains \(\$ 8.40\) in quarters and dimes. The number of quarters is twice the number of dimes. How many of each type of coin is in the box?
-
Calculate the duration of this 4-year par bond that has an annual coupon rate of 6.5% and a remaining time to maturity of 4 years. Suppose the market yield over the next year is expected to go down...
-
Suppose the concentration of glucose inside a cell is 0.1 mm and the cell is suspended in a glucose solution of 0.01 mm. a. What would be the free energy change involved in transporting 10-o mole of...
-
Suppose you were designing a file of student records for your universitys placement office. One of the fields that would likely be in this file is the students major. Develop a coding scheme for this...
-
What are the common security threats to systems? How can they be addressed?
-
Change the discount rate for Problem and Exercise 5-35 to 10 percent and redo the analysis. Problem and Exercise 5-35. Consider your use of a PC at either home or work and list tangible benefits from...
-
Determine the state of stress at point \(A\) on the cross section of the post at section \(a-a\). Indicate the results on a differential element at the point. 5ft 400 lb a 1.5 ft 300 lb a 2.5 in. 2...
-
The rod has a diameter of \(40 \mathrm{~mm}\). Determine the stress components that act at point \(B\), and show the results on a volume element located at this point. 1500 N 300 mm 600 N 100 Nm 800...
-
Determine the state of stress at point \(B\) on the cross section of the post at section \(a-a\). Indicate the results on a differential element at the point. 5 ft 400 lb 1.5 ft 300 lb a a 2.5 in.- 2...
Study smarter with the SolutionInn App