CIS 220, Business Information Systems and Analytics
Consider purchasing two or more books from Amazon. Feel free to visit amazon.com and pick out two or more books and take the order to the point it asks for your credit card if you are unsure of what is involved.
Relat
N
To build an E-R diagram:
Use or copy and paste the items on the right.
Double-click to change the text.
Connect shapes by moving a line close, and then dragging to node on one shape and then to node on another.
N
N
N
1
1
1
1
N
1
Entity 4
Entity 3
Entity 2
Entity 1
Simon Business School
University of Rochester
CIS 220, Business Information Systems and Analytics
Fall 2020
Team Database Assignment #1 for Monday, October 26th, at 11:59pm
You do not need to do anything in Microsoft Access for any of this assignment.
1. (10 points) Write an SQL query, just as we did in the first database lecture, that answers the
following question: “How many shares of Amazon does I24 hold?” Your query should work on
any database with the structure of the Stock Broker’s database shown in class the first database
lecture, i.e., you should not make any assumptions about the data.
2. (20 points) Consider purchasing two or more books from Amazon. Feel free to visit amazon.com
and pick out two or more books and take the order to the point it asks for your credit card if you
are unsure of what is involved. Think about what key pieces of information Amazon must keep
track of in order to process an order. Other than not assuming there is only one product in
every order, you can simplify this as much as you want. For example, you don’t need to worry
about shopping carts, multiple shipments, or multiple payments for a single order. Focus on
the bare essentials, just as we did in lecture with the Lexus example.
Draw an entity-relationship diagram for a database to keep track of information for Amazon.
Focus on the customer/sales side of the business (you do not need to worry about employees or
suppliers or purchase orders from suppliers). Again, you also don’t need to worry about
shopping carts, multiple shipments, or multiple payments for a single order. You do not need
to list all fields for each file, but you should specify cardinalities for relationships. Note that
there is no ‘right’ answer to this question (although there are plenty of wrong and not so good
answers). Your database should have at least three tables, but if you are ending up with many
more than four you are making the assignment harder than intended, i.e., while the database
could have many more than four tables, you should not worry about secondary tables (e.g.,
authors).
You can use the PowerPoint E-R diagram template posted with the 10/20 lecture, any other
tool you like, or simply draw it by hand and take a picture of it.
3. (10 points) Now convert your entity relationship diagram from Question 2 to relational form
(just a list of table names and attributes with the key field(s) underlined, e.g., Table 1: Attribute
1, Attribute 2…). Your database should include any fields necessary to keep track of
relationships.
You do not need to do anything in Access for any part of this assignment, although if you would rather
create a database in Access for 3 instead of displaying the database in relational form, that’s fine (you
still need to do the E-R diagram for 2). If you draw the E-R diagram by hand, which is fine, you can just
take a picture of it and include the picture in your submission, either in your write-up or separately (if
it’s in a separate file, please put “See attached” as your answer for 2 on your submission).