You are creating a data model for local coffee shops. For each of the following attributes,...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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. Data type Justification Attribute 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 4 5 7 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 "Course": [{ "number": "INFO201", "name": "Foundational Skills for Data Science", "credits": 5, "Offering": [ ] "number": "INFO330", name": "credits": 5, "Offering" : [ 1 {"quarter": "AUT2022", "instructor": "Hendry"}, {"quarter": "WIN2023", "instructor": "Walsh"} First schema Given this schema: "number": "INFO360", "name": "Design Methods", "credits": 4, "offering" : [ {"quarter": "WIN2023", "instructor": "Kotut"). bases & Data Modeling", ("quarter": "SPR2022", "instructor": "Boiko"}, {"quarter": "AUT2022", "instructor": "Hay"), {"quarter": "WIN2023", "instructor": "Wang"} Users(uID, username, joinedTime, city) Posts(pID, uID, postTime, postText) Likes(uID, PID, likeTime) Posts[UID] Users [UID] Likes [UID] Users[UID] Likes [pID] C Posts [pID] Solve all queries below using only select (o), project (II), Cartesian product (x), natural join (), and the assignment operator (:=). 4. (5 pt) Write a query that finds the usernames for all users in Seattle. 5. (5 pt) Write a query that finds all text posted by users in Seattle. 2 6. (10 pt) Write a query that finds all IDs of posts that have been posted by users in Seattle and liked by users in Portland. This question combines lots of opera- tions; 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 a schema based on the attributes in the books, ratings, and users tables. In pgAdmin, you can find the attributes under Schemas →→ Tables → <table name> → Columns. Some information about these attributes: • The 'id' attribute in all three tables is different! You can think of them in your mind as the "book_id', 'user_id', and 'rating_id'. . 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 only select (o), project (II), Cartesian product (x), natural join (), sort (7), and the assignment oper- ator (:=). 8. (5 pt) Write a query to list all book titles rated by user "LibreLibrary". BONUS: (5 pt) Write a query to list all book titles that have a 4 or higher GoodReads rating that have been rated by user "LibreLibrary" sorted by "LibreLibrary”'s rating. 3 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. Data type Justification Attribute 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 4 5 7 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 "Course": [{ "number": "INFO201", "name": "Foundational Skills for Data Science", "credits": 5, "Offering": [ ] "number": "INFO330", name": "credits": 5, "Offering" : [ 1 {"quarter": "AUT2022", "instructor": "Hendry"}, {"quarter": "WIN2023", "instructor": "Walsh"} First schema Given this schema: "number": "INFO360", "name": "Design Methods", "credits": 4, "offering" : [ {"quarter": "WIN2023", "instructor": "Kotut"). bases & Data Modeling", ("quarter": "SPR2022", "instructor": "Boiko"}, {"quarter": "AUT2022", "instructor": "Hay"), {"quarter": "WIN2023", "instructor": "Wang"} Users(uID, username, joinedTime, city) Posts(pID, uID, postTime, postText) Likes(uID, PID, likeTime) Posts[UID] Users [UID] Likes [UID] Users[UID] Likes [pID] C Posts [pID] Solve all queries below using only select (o), project (II), Cartesian product (x), natural join (), and the assignment operator (:=). 4. (5 pt) Write a query that finds the usernames for all users in Seattle. 5. (5 pt) Write a query that finds all text posted by users in Seattle. 2 6. (10 pt) Write a query that finds all IDs of posts that have been posted by users in Seattle and liked by users in Portland. This question combines lots of opera- tions; 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 a schema based on the attributes in the books, ratings, and users tables. In pgAdmin, you can find the attributes under Schemas →→ Tables → <table name> → Columns. Some information about these attributes: • The 'id' attribute in all three tables is different! You can think of them in your mind as the "book_id', 'user_id', and 'rating_id'. . 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 only select (o), project (II), Cartesian product (x), natural join (), sort (7), and the assignment oper- ator (:=). 8. (5 pt) Write a query to list all book titles rated by user "LibreLibrary". BONUS: (5 pt) Write a query to list all book titles that have a 4 or higher GoodReads rating that have been rated by user "LibreLibrary" sorted by "LibreLibrary”'s rating. 3
Expert Answer:
Answer rating: 100% (QA)
It looks like you have a series of questions related to database design and SQL queries Lets address each question one by one First for the coffee shop data model Attribute Data type Justification sho... View the full answer
Related Book For
Modern Database Management
ISBN: 978-0133544619
12th edition
Authors: Jeff Hoffer, Ramesh Venkataraman, Heikki Topi
Posted Date:
Students also viewed these databases questions
-
What system would therefore be suitable for Aviva to bring all their regions into a single platform while providing real-time information to the managers as well giving employees access to their own...
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Job descriptions: In determining who will be responsible for what duties, you are to compile a Job description for the Assistant Front Office Manager, The Front Desk Agent, The Reservations Agent and...
-
A company has 10,000 employees. Each employee is entitled to twenty days of paid holiday per calendar year. Up to five days of this entitlement may be carried forward and taken in the following year...
-
A 25-L tank that is initially evacuated is connected by a valve to an air supply line flowing air at 20C, 800 kPa. The valve is opened, and air flows into the tank until the pressure reaches...
-
3) (2 points) Satellite phones (a.k.a. satphones) do not require cell phone towers and can receive and transmit signals directly to geosynchronous satellites 36000 km above the equator. Determine the...
-
Comment on the phrase information system is an open system.
-
Mason Company and Rodd Company both apply overhead to the Work in Process Inventory account using direct labor hours. The following information is available for both companies for the year. Required...
-
1. Choose a key material from Table 02 above and document your choice of material and temper in your work. I used Tin Brass (Sy= 55000posi, Su=6000 ksi, E=16000, elongation: 6%) Hardness 76) 2....
-
Shopping cart Billing -------------- THE LANGUAGE ISC# C# An e-commerce company is currently celebrating ten years inbusiness They are having a sale to honor their privileged members.those who have...
-
Complete the following table. Table D. Calculated vs Measured pHs for Na,co3 Titration mL of 0.20 M HCI added Calculated pH Measured pH (from prelab) (from titration curve) 49 11.10 0.00 11.66 4.0...
-
Distinguish between "net passive income" and "passive investment income" in the computation of the tax on excess net passive income of an S corporation.
-
If you know the present value of an ordinary annuity, how can you find the PV of the corresponding annuity due?
-
What conditions must be met for an S corporation to be subject to a tax on passive investment income?
-
Patagonia sells rugged clothing and gear to mountain climbers, skiers, and other extreme-sport enthusiasts. The company is also well known for its environmental stands and its commitment to product...
-
If you calculated the value of an ordinary annuity, how could you find the value of the corresponding annuity due?
-
In the solubility rules, Ag2CrO4 was listed as an "insoluble"salt. It is actually slightly soluble in an equilibrium reaction:Ag2CrO4 (s) ? 2Ag+ (aq) + CrO42- (aq) The concentration of Ag+...
-
5. How much would you need to deposit in an account now in order to have $5,000 in the account in 5 years? Assume the account earns 2% interest compounded monthly. 10. You deposit $300 each month...
-
Give four reasons why many system designers believe that data modeling is important and arguably the most important part of the systems development process.
-
When a student has not chosen a major at a university, the university often enters a value of "Undecided" for the major field. Is "Undecided" a way to represent the null value? Should it be used as a...
-
Briefly describe four components of a disaster recovery plan.
-
In the research on leader behaviors, which style of leadership describes the preferred high-high combination? (a) transformational (b) transactional (c) laissez-faire (d) democratic
-
When managers use offers of rewards and threats of punishments to try to get others to do what they want them to do, they are using which type of power? (a) formal authority (b) position (c) referent...
-
In terms of leadership behaviors, someone who focuses on doing a very good job of planning work tasks, setting performance standards, and monitoring results would be described as _________. (a) task...
Study smarter with the SolutionInn App