NYU Tandon School of Engineering November 11, 2019 Computer Science and Engineering
CS6083, Fall 2019
Problem Set #3 (due November 22)
Consider the database from the first problem in HW#2, storing measurements from a series of weather
stations:
Station (sid, scity, sstate, slatitude, slongitude); Measurement (sid, mtimestamp, mtemp, mhumid, mprecip);
In this problem, you have to design a simple web frontend with PHP that allows people to look up weather information by using a web browser. Your web application should support the following:
(i) On the first page, there is a text box where users can type in a city name.
(ii) When the button is pressed, your PHP code should display a list of all weather stations in this city, together with the most recent temperature, humidity, and precipitation information from each station.
(iii) The user should now be able to click on a weather station to obtain a list of all past measurements of that station, with time, date, temperature, humidity, and precipitation.
Test your application using data provided for the previous homework. You will have to meet with a grader to give a quick demo. (Details on scheduling demos will be announced later, and demos will 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àD, DBà AC, HDàABH, AàH, EàH, Hà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 it into 3NF.
Problem 3:
Consider the following schema for a short-term car rental business, where all attributes are combined into just two tables:
CarRental(custid, custname, custcity, custstate, carid, carmodel, caryear, rentdate, rentalfee, citytax, pickupbid, returnbid) /* pickupbid and returnbid are foreign keys referencing bid in Branch */
Branch(bid, bcity, bstate)
In this schema, we assume that car rentals are always for one day. Customers are identified by a custid, and have a name, city, and state of residence. Cars are identified by a carid, and have a model and a year when the car was made. For each rental, we store the rental fee that is paid to the company, as well as a city tax
Problem 1:
that goes to the city where the car is picked up. Cars can be picked up and returned in many branches of the company, where each branch is identified by a bid. In addition, assume that there is a rule that requires that two customers who pick up the same model of car on the same day at the same branch always pay the same rental fee.
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 a canonical cover of the functional dependencies in F.
d) Is the above schema in BCNF? Prove or disprove. If it is not in BCNF, convert it into BCNF. Show the steps in your decomposition and the functional dependencies you are using to decompose.
e) Is the BCNF schema from d) dependency-preserving? Prove or disprove. If not, convert it into 3NF.
f) Suppose we add an additional constraint that says that the citytax that is collected must be the same for all rentals that were picked up in the same city, but that it might be different for different cities. (E.g., maybe Houston charges a tax of $10 per rental, Chicago $5, and Dallas $0.) How would this change your answers for parts b) through e)?
g) Suppose that instead of the constraint in f), we have a constraint that all cars must be returned to a branch in the same state? E.g., if you pick up a car at a branch in Texas, you can only return it at other branches in Texas. Can you handle this sort of constraint using functional dependencies? How would it change your solution? (Discuss briefly – you do not need to go through all the steps again.)
Problem 4:
In this problem, you are asked to explore the metadata querying facilities in your database system. The answers may depend on which system you have installed, so make sure to state which system you are using! Write the following queries using the bakery database dicussed in class. Data will be provided. Please submit screenshots of the result.
a) List all tables whose name starts with “C”.
b) List all foreign keys referencing oid in the Order table
c) List all tables and the number of attributes each table has.
d) For each attribute in the Order table, output how many distinct values the attribute has in the database.
e) List any customers whose name contains the name of an ingredient. (E.g., there could be customer with name “Barry Sugar”, or “Frank Salt”.)
Reviews
There are no reviews yet.