4. Consider the following relations: BOOKS (BookNo, Title, Author, ISBN, Stock, Price, Tax) STORE(StoreNo. City, State, Zip, Inventory) STOCK(StoreNo. BookNo.Qty) Assume the attribute city in STORE table takes three different values like "Muscat", "Nizwa" and "Sohar". i. Show the predicates by which STORE may be Horizontally partitioned by City. How would be a derived Horizontal partitioning of STOCK be defined based on the partitioning of the STORE? ii.

4. Consider the following relations: BOOKS (BookNo, Title, Author, ISBN, Stock, Price, Tax) STORE(StoreNo. City, State, Zip, Inventory) STOCK(StoreNo. BookNo.Qty) Assume the attribute city in STORE table takes three different values like "Muscat", "Nizwa" and "Sohar". i. Show the predicates by which STORE may be Horizontally partitioned by City. How would be a derived Horizontal partitioning of STOCK be defined based on the partitioning of the STORE? ii.

Database Systems: Design, Implementation, & Management

12th Edition

ISBN:9781305627482

Author:Carlos Coronel, Steven Morris

Publisher:Carlos Coronel, Steven Morris

Chapter8: Advanced Sql

Section: Chapter Questions

Problem 18RQ

See similar textbooks

Similar questions

Consider the following relations:BOOKS(Book#, Primary_author, Topic, Total_stock, $price)BOOKSTORE(Store#, City, State, Zip, Inventory_value)STOCK(Store#, Book#, Qty)Total_stock is the total number of books in stock and Inventory_value is thetotal inventory value for the store in dollars.a. Give an example of two simple predicates that would be meaningful forthe BOOKSTORE relation for horizontal partitioning.b. How would a derived horizontal partitioning of STOCK be defined basedon the partitioning of BOOKSTORE?c. Show predicates by which BOOKS may be horizontally partitioned bytopic.d. Show how the STOCK may be further partitioned from the partitions in(b) by adding the predicates in (c).
Consider the relation r(A, B, C), where the index is the attribute A. Give an example of a query that can only be answered by looking at the index rather than the relationship's tuples. (Index-only plans are query plans that rely just on the index rather than the actual relation.)
Consider the relation r(A, B, C) with the attribute A as the index. Give an example of a query that can be answered only by looking at the index, rather than the tuples in the relationship. (Index-only plans are query plans that just utilise the index and do not access the actual relation.)
You are given a relation R with the following attributes R(A,B,C,D,E). Assume that you were given the following functional dependencies: • FD1= AB -> E • FD2: = D -> C (a) What is a valid key for this relation? justify your response. (b) Is this relation in 3NF (Third Normal Form)? Justify your response. If it is not in 3NF, normalize the relation and demonstrate all steps of normalization.
Multiple choices. Circle (or indicate) the correct (or the best) answer. You should choose only one answer. Multiple circles (or answers) will be considered as wrong answer. No explanation needed. No partial credit will be given. (1) Consider a weak entity set e’ in an ERD. If the identifying strong entity set e has primary key p, the weak entity set e’ has three attributes b, c and d in its schema, and if b and c together form the partial key of e’, then the schema of the relational table reduced from this weak entity set e’ is p, b, c, e [underlined attributes indicate schema of the relation] p, b, c, e [underlined attributes indicate schema of the relation] p, b, c, e [underlined attributes indicate schema of the relation] p, b, c [underlined attributes indicate schema of the relation] None of the above. (2) The following is the most fundamental concept in traditional database management. (a) 3NF and BCNF (b) Lossless join (c) ER diagram (d) Schema (e) Lossless join (3)…
1. Consider the following relations:Student (snum: integer, sname: string, major: string, level: string, age: integer)Class (name: string, meets at: string, room: string, fid: integer)Enrolled (snum: integer, cname: string)Faculty (fid: integer, fname: string, deptid: integer)The meaning of these relations is straightforward; for example, Enrolled has onerecord per student-class pair such that the student is enrolled in the class. Level is atwo character code with 4 different values (example: Junior: A Level etc)Write the following queries in SQL. No duplicates should be printed in any of theanswers.i. Find the names of all Juniors (level = A Level) who are enrolled in a classtaught by Prof. Kwizeraii. ii. Find the names of all classes that either meet in room R128 or have five ormore Students enrolled.iii. Find the names of all students who are enrolled in two classes that meet atthe same time.
Convert the ER diagram to relations in the normal manner to capture as many constraints as possible, and then identify which of the answers is a relation schema. A. Dependents(ID, name, bDate) B. Employees(ID, name, street, city, state, phones, sDate, lengthEmp) C. Coverage(ID, name) D. Employees(ID, name, street, city, state, sDate, lengthEmp)
Question 6. Consider the following relation: Invoice(Order_ID, Order_Date, Customer_ID, Customer_Name,Customer_Address,Product_ID, Product_Desc, Price, Order_Quantity) FD1 - Order_ID -> Order_Date, Customer_ID, Customer_Name, Customer_Address FD2 - Customer_ID -> Customer_Name, Customer_Address FD3 - Product_ID -> Product_Description, Price FD4 - Order_ID, Product_ID -> Order_Quantity What are the candidate keys of this relation? Give reasons to explain if this is in 1NF or not. If not, decompose it to 1NF. Give reasons to explain if this is in 2NF or not. If not, decompose it to 2NF. Give reasons to explain if this is in 3NF or not. If not, decompose it to 3NF.
Consider the following relational schema, representing five relations describing shopping transactions andinformation about credit cards generating them [the used notation is explained above].SHOPPINGTRANSACTION (TransId, Date, Amount, Currency, ExchangeRate, CardNbrCREDITCARD, StoreIdSTORE)CREDITCARD (CardNbr, CardTypeCARDTYPE, CardOwnerOWNER, ExpDate, Limit)OWNER (OwnerId, Name, Surname, BirthDate, Job, IncomeLevel, Level)CARDTYPE (CardType, Circuit, Issuer, Address, Country, CardName, Color)STORE (StoreId, Name, Address, Country, Zone, Web, Email, Type)1) Answer the following questions considering the domain described by the previous relational schema:1.1. How many transactions can be generated by a given credit card? (provide the minimum and themaximum number)1.2. How many shopping transactions occurred in the same store can be associated with a givenidentifier? (provide the minimum and the maximum number)2) Write the SQL query representing the following request:2.1. Determine the…
Consider the following schema for a student relation. Student{Student ID, Last Name, First Name, Course ID, Course Section, Course Name, Faculty_ID, Faculty Last Name, Faculty First Name, Faculty Building&Office #, Semester, Grade}Sample Data:A43469873, Clark, Mary, MSIS5663, 01, Data Warehousing, A98706724, Sarathy, Rathindra, BUS407, SP2022, AIn a given semester (Note that there will be multiple semesters): A student may be enrolled in multiple courses, from multiple faculty, and receives a grade for each A faculty may teach multiple sections of multiple coursesIf you make any other assumptions, clearly state them here.a. Assuming that the table is in 1NF, identify all candidate key(s) and your choice of primary key.b. Identify the functional dependencies in the 1NF table above based on the chosen primary key, including any partial and transitive dependencies. (If necessary, clearly state your assumptions). In answering this, you may use the format in question 1d.c. After…
Consider the relation schema Appointment={Patient, GP, Date, Time, Clinic, Room} and the followingset Σ of FDs:• Patient, Clinic, Date → Time• Patient → GP• GP → Clinic• Clinic, Date, Time, Room → Patient• Patient, Date, Time → Clinic, RoomIs the above relation schema Appointment in BCNF? If not, identify a BCNF decomposition for Appointment. You need to include the main steps used for identifying the BCNF decomposition. Check ifthis BCNF decomposition is dependency preserving
Question 1: Normalization Please TYPE IF POSSIBLE OR WRITE CLEARLY Consider a relation X with six attributes WYSMFE where WS is a composite primary key for X. Assume X has the following functional dependencies (X is one table with the attributes W, Y, S, M, F, E): WS è YMFE S è YME E è M In what normal form is relation X? Explain why? Is it possible to decompose X into a number of relations in order to achieve a higher normal form? Explain how (this means using English sentences and a diagram showing the final relation(s)).

SEE MORE QUESTIONS

Recommended textbooks for you

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

SEE MORE TEXTBOOKS