2023年11月25日

PHP代写 | CS6083, Spring 2020 Problem 1

本次PHP代写是设计一个有关电影的数据库系统并实现网页
CS6083, Spring 2020
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. )
Problem 2
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.
Problem 3
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)?
Problem 4
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.