Study Guides (400,000)
US (230,000)
UC-Irvine (3,000)
COMPSCI (100)
Quiz

COMPSCI 122A Quiz: Assignment1Exam


Department
Computer Science
Course Code
COMPSCI 122A
Professor
Kenneth Shea
Study Guide
Quiz

This preview shows page 1. to view the full 5 pages of the document.
CS 122A: Introduction to Data Management – Spring 2020
Homework 1: E/R Modeling(100 points)
Due Date:
Friday, Apr 10 (11:00 PM)
Partner Policy
The partner policy for this HW (and subsequent) HW assignments will be that your CS122A partner is your
“brainstorming buddy”. You can discuss the HW with them freely w/o violating any rules. But - in the end, this
is an individual assignment, and each of you should turn in your own work.
For this particular assignment, you
should attempt the assignment alone first, and then talk to each other, and then write down some notes
about the key differences between yours and your partner’s ER diagram. If you do not have a partner, you will
need to find someone to talk to after completing ER design on your own - i.e., students who solo need to find
some other team to compare diagrams with in order to get credit for this last part of the assignment (see
below).
HW Submissions
This HW assignment should be turned in with a filename that contains both your student IDs and your names (e.g.,
12345678_John_Doe+98765432_Jane_Doe.pdf) and it must be submitted online, as a PDF file, through the associated
assignment in Gradescope (HW1 in this case). See the table below for HW 1 submission opportunities. Note that after
the second deadline, Saturday April 11th, no further HW 1 submissions will be accepted at all. We will not accept any
assignments after that time since we will publish a correct solution at that time. Plan to turn your work in on time! If
possible, save your one dropped assignment for the end of the term when you are most likely to want/need it, or for a
time when something unanticipated crops up.
Date / Time
Grade Implications
Friday, Apr 10 (11:00 PM)
Full credit will be available
Saturday, Apr 11 (11:00 PM)
10 points will be deducted
E-R Schema Design [90 pts]
Fed up with being cooped up at home 24x7, you and a group of your fellow CS122a students have decided to devote
some of your untapped energy to starting a new online service called ShopALot.com. Many stores have started to
cobble together approaches to safe and socially distant shopping; your goal is to help them out, especially the smaller
local stores, by providing a database-backed service that supports “parking lot shopping.” To reduce the traffic inside
busy stores such as grocery stores, users who know exactly what they want and verify that it's available will be able to
submit their orders for goods in advance and then wait in the parking lot. From there, designated "shoppers" --
ShopALot.com employees -- will be assigned their orders and will shop for them in batches and then perform the
handoff to ShopALot customers from the comfort of the customers’ cars in store parking lots. Your CEO’s initial vision for
this service was inspired in part by Amazon stores, so the first working name for the startup was another major river:
Nile.com was the plan until one of your fellow co-founders pointed out that now might not be the best time to name a
You're Reading a Preview

Unlock to view full version

Only page 1 are available for preview. Some parts have been intentionally blurred.

new startup after another virus...
In an interview with the ShopALot founders, they shared the following thoughts on the data model needed for their
service:
1. Each user will be assigned a unique user id by ShopALot when they first join the service. Users will have an email
address, a name consisting of a first name and last name, and a set of phone number(s); each phone number will
consist of its type (e.g., home, mobile, or other) along with the number itself.
2. A ShopALot user can be a customer or a shopper. (Shoppers can also be customers since they may want to use
the service too sometimes!) To help shoppers to control the amount of shopping they may be asked to do at
once, shoppers can optionally specify their order capacity (e.g., at most 6 orders at a time).
3. Associated with each customer is one or more vehicles that they might drive. In order for a shopper to identify a
vehicle in the parking lot (and consequently, the customer), a vehicle is uniquely identified by the combination
of its license plate number and state. Shoppers can also spot a customer’s cars based on the remaining
attributes of their vehicle: its year, make, model, and color. (Make and color are mandatory; year and model are
optional.)
4. Each product that some store might sell and/or that a customer might want is identified by a unique product id.
Each product also has a name, a description, one category (e.g. baby-care, beverages, deli, frozen-foods,
meal-kits, etc...), and a list price.
5. The central functionality of ShopALot lies in its management of orders. Each order is identified by a unique order
id. Each order, of course, will eventually contain one or more order-items. An order-item consists of a unique
(within the scope of its order) item number, a quantity, and order-item price itself. Each order-item cannot exist
without an associated order. The total price of an order can be computed based on its contents (i.e., its
associated order-items, quantities, and prices). A given order-item represents the inclusion of a particular
product (such as a much-coveted 6-pack of Charmin two-ply toilet paper) in the order.
6. Stores are another central component of ShopALot. Each store will have a unique store id, a name, a single
phone number, one or more product categories, and an address. An address includes the store’s street, city, and
zip code.
7. Since this is about parking-lot shopping, a customer must own at least one vehicle; however, a vehicle may be
owned by multiple customers. (E.g., a married couple might both be ShopALot customers and might drive any
one of their cars on a trip to pick up an order.)
8. Customers may register themselves as close friends with other customers.
9. A shopper must work for one or more stores, and a given store can have any number of shoppers.
10. Customers may place orders, and each customer order will be time-stamped with its initial order time and
optionally with a target pickup time. Shoppers will be assigned to fulfill orders, and the actual time of fulfillment
(i.e., the time when the shopper delivered the order to the customer in the lot) will also be recorded so that
ShopALot knows when the final delivery occurred.
11. The ShopALot service’s central entity, the order, can only be placed for a single store. (If a customer wanted to
order items from several stores that share a parking lot, those would be handled as separate orders.) A single
vehicle must be associated with a given order as well to specify the vehicle to which the shopper should deliver a
given order after fulfilling it.
You're Reading a Preview

Unlock to view full version