Question

Consider a relational database for pet food information management with the following scheme. In such

schemes, relation names are usually italicized, and primary keys are usually underlined. In the schemes below,
attributes have their usual meanings. Table Pets stores pet’s details and PetID is a unique pet ID. In Owners
and Owns, OID means owner ID, which is unique in Owners. The PetID in Owns, PetID in Likes and PetID
in Pets have identical domains, and PetID refers to the PetID in Pets. FoodID in Foods and FoodID in
Purchases have identical domains, and FoodID in Purchases refers to FoodID in Foods. FoodID is unique in
Foods. TypeofFood in all schemes mean the intended consumer of the food – e.g., dog, or bird, while the name
of the food says what the item is – e.g., bone, and the brand captures the maker of the food item – e.g., Meow
Mix. Note that the TypeofPet in Pets and TypeofFood in Foods have identical domains. The ClassofFood
reflects the category of food such as dry food or wet food. ItemWeight in Foods shows the net package weight.
In Purchases, Quantity refers to the number of packages of the food item bought. In Owns, Year refers to the
year when the pet was adopted and PetAgeatOwnership is the age of the pet when it was adopted. The
PricePaid in Owns is set to a non-zero value if the pet was bought from a store, zero if it was adopted from
another owner at no cost.
Pets(PetID, Name, Age, Street#, City, ZipCode, State, TypeofPet)
Owners(OID, LastName, Street#, City, ZipCode, State, Age, AnnualIncome)
Owns(PetID, Year, OID, PetAgeatOwnership, PricePaid)
Likes(PetID, TypeofFood)
Foods(FoodID, Name, Brand, TypeofFood, Price, ItemWeight, ClassofFood)
Purchases(OID, FoodID, PetID, Month, Year, Quantity)
Write the following queries in SQL and Relational Algebra. [20 points]
1. List food (FoodID, Name, Brand, Price) that is the cheapest dog food of a brand.
2. Compute and list (PetID, Name, Year, TotalCost) annual food costs for each pet living in Moscow,ID.
3. List all states which have at least one pet owner who owns the largest number of cats in his/her zip code. For this query, you can assume that all the zip codes are present in the database in which there
is at least one pet owner.
4. List the poorest pet owner (OID, Name, AnnualIncome, TotalSpending) who spent the most on pet food in 2021.
5. List all pets (PetID, Name, State) which ate all the most expensive foods (in each class - ClassofFood) made by Purina.

Answer 1

1. SQL: `SELECT FoodID, Name, Brand, Price FROM Foods WHERE TypeofFood='dog' AND Brand='Purina' ORDER BY Price LIMIT 1;` Relational Algebra: π_{FoodID, Name, Brand, Price}(σ_{TypeofFood='dog'∧Brand='Purina'}(Foods))

2. SQL: `SELECT O.PetID, P.Name, O.Year, SUM(F.Price * P.Quantity) AS TotalCost FROM Owns O JOIN Purchases P ON O.PetID = P.PetID WHERE O.City='Moscow' AND O.State='ID' GROUP BY O.PetID, P.Year;` Relational Algebra: π_{PetID, Name, Year, TotalCost}(σ_{City='Moscow'∧State='ID'}(Owns ⨝_PetID=PetIDPurchases))

Step-by-step explanation:

1. For the first query, the SQL statement retrieves the cheapest dog food of a specified brand (Purina). The query uses the `ORDER BY` clause to arrange the results in ascending order based on the price and then limits the output to the first row using `LIMIT 1`. The relational algebra expression mirrors this, selecting the desired attributes and filtering based on the specified conditions.

2. The second query calculates and lists the annual food costs for each pet living in Moscow, ID. It involves a join between the "Owns" and "Purchases" tables, grouping the results by pet ID and year, and calculating the total cost using the `SUM` function. The relational algebra expression combines these operations using the appropriate symbols.

3. The third query identifies all states that have at least one pet owner who owns the largest number of cats in their respective zip code. The SQL subquery counts the number of cats in each zip code and compares it to the owner's age, ensuring that the owner has the maximum count. The relational algebra expression corresponds to this logic, extracting the distinct states meeting the specified criteria.