Lab 1: Data modeling & relational algebra
Winter 2024 INFO330
Objectives: To be able to justify data modeling decisions and write simple queries in relational algebra
Assignment tools: Third section requires pgAdmin and a connection to class database
What to turn in: A single file containing your answers. Turn in your solution in Canvas by uploading your file. This can be any number of fotmats, such as *.pdf, *.doc, *.docx, or *.txt.
Due date: Check Canvas, turn in via Canvas
Data models
1. (10 pt) You are creating a data model for local coffee shops. For each of the following attributes, provide an appropriate datatype and justification for your choice. Include a field size where applicable.
|
Attribute Data type Justification |
|
shop_id shop_name owner_name street city state zipcode latlong date_opened has_decaf |
2. (5 pt) Using the coffee shop data model from above, let’s say we also want to model people who buy coffee. Is a hierarchical relationship between people and coffee shops appropriate for this? Why or why not?
3. (5 pt) Represent the content of the following JSON file in one or more tables.
1 {
2 "Course" : [{
3 "number" : "INFO201" ,
4 "name" : "Foundational Skills for Data Science" ,
5 "credits" : 5 ,
6 "Offering":[
7 { "quarter" : "AUT2022" , "instructor" : "Hendry"} ,
8 { "quarter" : "WIN2023" , "instructor" : "Walsh" }
9 ]
10 } ,
11 {
12 "number" : "INFO330" ,
13 "name" : "Databases & Data Modeling" ,
14 "credits" : 5 ,
15 "Offering":[
16 { "quarter" : "SPR2022" , "instructor" : "Boiko" } ,
17 { "quarter" : "AUT2022" , "instructor" : "Hay"} ,
18 { "quarter" : "WIN2023" , "instructor" : "Wang"}
19 ]
20 } ,
21 {
22 "number" : "INFO360" ,
23 "name" : "Design Methods" ,
24 "credits" : 4 ,
25 "Offering":[
26 { "quarter" : "WIN2023" , "instructor" : "Kotut" }
27 ]
28 }]
29 }
First schema
Given this schema:
Users(uID, username, joinedTime, city)
Posts(pID, uID, postTime, postText)
Likes(uID, pID, likeTime)
Posts[uID] ⊆ Users[uID]
Likes[uID] ⊆ Users[uID]
Likes[pID] ⊆ Posts[pID]
Solve all queries below using any relational algebra operator discussed in class. Most queries can be solved using set operations, select (
), project (Π), Cartesian product (×), joins (⋈θ ), and assignment operators (:=).
4. (5 pt) Write a relational algebra query that returns the usernames for all users in Seattle
5. (5 pt) Write a relational algebra query that returns all text posted by users in Seattle.
6. (10 pt) Write a relational algebra query that returns all IDs of posts that have been posted by users in Seattle AND liked by users in Portland. This question combines lots of operations; try to break down what you need to do step by step, write the component pieces, and then combine them together.
Toy database schema
Connect to our class server and use the toy database.
7. (5 pt) Write out the table schema based on the attributes in the books, ratings, and users tables. In pgAdmin, you can find the attributes under Schemas → Tables →→ Columns.
Some information about these attributes:
• The ‘id’ attribute in all three tables is different! You can think of them as ‘book_id’, ‘user_id’, and ‘rating_id’. This is important for joins!
• The ‘title’ attribute in the Books table is the same as the ‘book_name’ attribute in the Ratings table.
• The ‘id’ attribute in the Users table is the same as the ‘user_id’ attribute in the Ratings table.
With this information in mind, please solve all queries below using the relational algebra operators discussed in class.
8. (5 pt) Write a relational algebra query to return all book titles rated by user “LibreLibrary”.
BONUS: (5 pt) Write a relational algebra query to return all book titles that have a 4 or higher GoodReads rating that have been rated by user “LibreLibrary” sorted by “LibreLibrary”’srating.