NYU Tandon School of Engineering March 31, 2020 Computer Science and Engineering
CS6083, Spring 2020
Problem Set #3 (due 4/14)
Consider the following database schema which you have already seen in class and a previous homework:
Copyright By PowCoder代写 加微信 powcoder
Customer (cid, came, caddress, cphone, ccn) Movie (mid, title, genre, year)
Branch (bid, bname, baddress)
Copy (copyid, mid, bid)
Rental (cid, copyid, outdate, returndate, cost)
In this problem, you need to design a simple web front-end with PHP, which allows people to access certain information with a web browser. Your web application should support the following operations:
(i) On a start page, there should be a textbox where users can type in their customer id and then submit it by pressing a button.
(ii) After the button has been pressed, your PHP code should display all the rentals a customer has ever made, showing information such as the movie title, movie genre, movie year, branch id, branch name, branch address, copy id, rent date, return date, and cost.
(iii) After the rentals have been displayed, the user should be able to click on the branch id, which will display a list of all the movies that this branch has available, including the movie title, genre, movie year, and number of available copies.
Please test your application with the data provided in the previous homework. You will need to schedule an online demo (via skype or zoom) with one of the TAs to show your solution. (Details on scheduling demos will be announced later, and demos may be a few days after the deadline for the other problems. )
Consider a relational schema R = {A, B, C, D, E, H, I}, satisfying the functional dependencies F = {A ¡ú C, B ¡ú ACE, DH ¡ú AEI, E ¡ú H, H ¡ú E, D ¡ú E}.
a) Derive all candidate keys for this schema.
b) Derive a canonical cover of the functional dependencies in F.
c) Is the above schema in BCNF? Prove or disprove. If it is not in BCNF, convert it into BCNF.
d) Is the BCNF schema from (c) dependency-preserving? Prove or disprove. If not, convert into 3NF.
Consider the following single-table database modeling customers, books, and book purchases in a chain of bookstores.
bookstore(cid, cname, cemail, bid, btitle, byear, bprice, sid, scity, sstate, pprice, ptimedate)
Customers are identified by a cid, and have a name and email address. Books are identified by a bid (say, an ISBN number), and have a title, year of publication, and current price. Stores (branches) are identified by an sid and also have a city and state. For each purchase, we store the time and date of the purchase (ptimedate) and the price that was paid. We assume that the current price of a book (bprice) is the same in all stores. Every time a customer buys a book, a new record is inserted into the table.
a) Explain why the above is not a good relational design. Name several reasons.
b) Identify the set F of non-trivial functional dependencies for this schema. (It is enough to identify a subset E such that the closures of E and F are the same.)
c) Derive all candidate keys for this table.
d) Derive a canonical cover of the functional dependencies in F.
e) Is the above schema in BCNF? Prove or disprove. If it is not in BCNF, convert it into BCNF.
f) Is the BCNF schema from e) dependency-preserving? Prove or disprove. If not, convert it into 3NF.
g) Suppose we make the assumption that the current price of a book could be different in different stores. However, the price must be the same in all stores that are located in the same state. How would this change your answers for parts b) through f)?
In this problem, you have to explore the metadata querying facilities of your database system, in order to write metadata queries using the same schema and data as in Problem 1:
a): List the names of all tables in descending order of their number of attributes.
b) List all foreign keys in this schema
c) For each attribute in the Movie table, output how many distinct values the attribute has in the current database.
d) List the attribute name contains substring ¡°id¡±
e) List the number of attributes of type varchar in the schema
f) Can you write a trigger that, if a new table is added to the database, adds a new attribute ¡°xname¡± of type varchar[10] to that table. Is this possible? If yes, do so. Else, explain why not.
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com