I would like this done in the next 8-9 hours from now. You must know about databases, SQL, and whatever else is mentioned in the linked file. I will be uploading the problems.doc so that will be your guide.
Question #1: Suppose that you are asked to write a database application that captures the following information: We want to store information about books. Each book has a title, a price, and an ISBN number. The ISBN number uniquely identifies the book. Some books are children’s books. Each children’s book also specifies the minimum age and the maximum age, which together indicate the ideal age range for its readers. Some books are educational books. Each educational book also specifies the grade, that is, the school grade that the book is most suited for. Each educational book may also be associated with one or multiple exercise CDs. Each CD has a name and a length (in minutes). We can only uniquely identify each CD using its name and the information of the book that the CD is associated with. Each book is written by one or many authors. Each author has an author ID, a name, and multiple addresses. Each book is also published by exactly one publisher. Each publisher has a name (which uniquely identifies the publisher) and a single address. When a publisher publishes a book, we want to record the publishing year and the quantity (that is, the number of copies that the publisher produces). a) Draw an ER diagram for this application. Decide the key attributes and identify those. b) Convert the above ER diagram into a relational schema. Specify the primary key of each relation in your schema. __________________________________________________________________ Question #2: SQL Consider a database schema with the following relations: Suppliers(sid: integer, sname: string, address: string) Parts(pid: integer, pname:string, color: string) Catalog(sid:integer, pid:integer, cost:real) Please write the following SQL queries: (a) Find the sids of suppliers who sell a 'red' part that costs more than 100. (b) An expensive part is a part that has its costs > 100. Find the sid of those suppliers who sell such part. __________________________________________________________________ Question #3: Write corresponding relational algebra for the two above SQL questions. Question #4: Normalization 1. Given the relation R(A,B,C,D,E) with functional dependencies A-> BC, C -> D. Is R in 3NF form? In BCNF form? If not, please decompose it into BCNF. Please show the decomposition process. (35 points) 2. (35 points) Suppose you are given a relation R(X,Y,Z,W). For the following sets of FDs, assuming they are the only dependencies that hold for R, do the following: (a) Identify the candidate key(s) for R. (b) State whether or not the proposed decomposition of R into smaller relations is a good decomposition and briefly explain why or why not. X → Y, Y → Z, Z → W; decompose into XY and XZW