Database System Questions
Relational Calculus Customer(custNr, name, baseLoc,birthDt, gender) Property (propId, locDesc, state, propType, rating) Rental(custNr, propId, startDt, totalCost) To make it easier, use the following tuple variables: Customer – c, Property – p, and Rental – r. There may be cases where you need multiple tuple variables for the same relation; please use something meaningful (e.g., r444). You can see the sample data in this link (https://cs.utsa.edu/~cs1083/CS3743Hwk5RelCalcWOData.pdf), that you may use for showing the results of some queries. You may need to copy paste the link in another tab of your web browser to see it. Please show appropriate relational calculus RANGE statements and a single GET statement to answer each of the following. There will also be some questions where you will have to show the results of some of the queries. Remember that, for the Existential quantifier you can use E (in red color) and for the Universal quantifier you can use an A (in red color). The tuple variables used for the quantifiers will not need to be shown as subscript, but, they will have to be also in red color. See the following query as an example: RANGE SUPPLIER s RANGE SHIPMENT h RANGE PART p Get W17 (p.P#, p.PNAME): Ah (h.P# = p.P# -> Es (s.S# = h.S# ^ s.CITY = 'London')) instead of RANGE SUPPLIER s RANGE SHIPMENT h RANGE PART p Get W17 (p.P#, p.PNAME): ∀h (h.P# = p.P# → ∃s (s.S# = h.S# ^ s.CITY = 'London')) Note that the quantifiers are the only ones in red. This option is provided to you, so that you don't have to search for the symbol of Existential or Universal. nor the sufixes. You need to follow this format. Question 1: Find the propId, locDesc, and state for properties with a propType equal to "T" (i.e., traditional). Question 2: Find the propId and startDt for properties which the customer named Ava Kashun has a rental. Question 3: Find the property ID and locDesc of properties in TX (state) which have a start date after "2019-11-03" for the customer named "Perry Scope". Question 4: Find the customer numbers and names of customers who have a rental start after "2019-11-04" for a property which customer #444 also had a rental. Do not include #444 in your result. Hint: you will have to use one of the relations twice. Question 5: Find the customer number and names of customers who have rented all of the properties having a propType of "C". Question 6: Find the custNr, name, gender, and birth date of customers who only rent properties with a state equal to "CO". State whether your answer includes customers that don't have any associated rentals. Question 7: Find the propId and locDesc for properties on which only customer #777 has a rental. State whether your answer includes properties that don't have any associated rentals. Question 8: Find the customer numbers and names of customers who have rented all the properties which Penny Lane has rented. State whether you included Penny Lane in your answer. Hint: obtain the custNr for Penny Lane before attempting the universal quantifier. Question 9: Find the customer number, names, and birth date for customers who never rented a property with a state equal to "CO". State whether your answer includes customers that don't have any associated rentals. Question 10: Using the PartDB discussed in class, show the result for the following Query. RANGE SUPPLIER s RANGE SHIPMENT h RANGE PART p GET w (s.CITY): Ap (p.NAME = 'CAM' -> Eh (h.P# = p.P# ^ s.S# = h.S#)) Question 11: Write what would have been the problem statement for the following query. RANGE Course c RANGE Student s RANGE Enrollment e GET (c.Title) : Ee Es (c.C# = e.C# ^ e.ST# = s.ST# ^ s.Classif = 'SO')