CIS 220, Business Information Systems and Analytics

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).