Simon Business School University Of Rochester CIS 220 Busine

Simon Business School University Of Rochestercis 220 Business Inform

Simon Business School University Of Rochestercis 220 Business Inform

Simon Business School University Of Rochestercis 220 Business Inform

Simon Business School University Of Rochestercis 220 Business Inform

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

Paper For Above instruction

This assignment encompasses three interconnected components focused on understanding and designing a business data system, specifically tailored to a stock brokerage context and an e-commerce platform like Amazon. The objectives include constructing an SQL query based on a provided database structure, creating an entity-relationship diagram (ERD) for Amazon's order processing, and translating that ERD into a relational schema. These components collectively develop practical skills in database querying, conceptual modeling, and database normalization, which are essential in business information systems.

Answer to Part 1: SQL Query to Find Amazon Shares Held by I24

The first task involves writing a SQL query to determine how many shares of Amazon (symbol: AMZN) the client with identifier I24 holds. Based on typical stockbroker database structures, this database likely contains tables such as Clients, Holdings, and Stocks. The 'Holdings' table is assumed to link clients and stocks, recording the number of shares held for each client-stock pair.

Given this, the SQL query must select the number of shares from the Holdings table where the client ID is 'I24' and the stock symbol is 'AMZN'. The following query demonstrates this:



SELECT h.shares


FROM Holdings h


JOIN Stocks s ON h.stock_id = s.stock_id


JOIN Clients c ON h.client_id = c.client_id


WHERE c.client_id = 'I24' AND s.symbol = 'AMZN';

This query performs a join between the Holdings and Stocks tables on stock_id, and between Holdings and Clients on client_id. It filters for client 'I24' and stock symbol 'AMZN'. The resulting shares field provides the number of Amazon shares held by I24. Since the structure is presumed uniform, this query will correctly work on any database with the same schema.

Answer to Part 2: ER Diagram for Amazon Customer/Sales Data

The second task requires designing an entity-relationship diagram modeling Amazon’s customer and order data. To simplify, the focus is solely on the sales and customer aspects, excluding employee management, suppliers, or shipment details. The ER diagram must include at least three entities with appropriate relationships and cardinalities.

The primary entities typically include Customer, Order, and Product. Here is an outline of the core components:

  • Customer: Records customer information; relationship with Order.
  • Order: Represents individual purchase transactions; linked to Customer and Product.
  • Product: Details about items sold; linked to Order through an associative entity, e.g., OrderItem.

The relationships include:

  • One Customer can place many Orders (1:N).
  • Each Order includes one or more Products (M:N), modeled through OrderItem, which captures the quantity ordered of each product.

The ER diagram can be drawn using software or by hand, with entities represented as rectangles, relationships as lines, and crow’s feet to specify cardinality. For example:

  • Customer (CustomerID, Name, Email, Address)
  • Order (OrderID, Date, CustomerID)
  • Product (ProductID, Title, Price)
  • OrderItem (OrderID, ProductID, Quantity)

Cardinalities include:

  • Customer to Order: 1:N (One customer can have many orders)
  • Order to Product: M:N, managed via OrderItem, where each order can have multiple products, and each product can appear in multiple orders

Answer to Part 3: Relational Schema

Transforming the ER diagram into a relational schema involves listing each entity as a table with key attributes underlined and including foreign keys to establish relationships:



Customer (CustomerID, Name, Email, Address), PK: CustomerID


Order (OrderID, Date, CustomerID), PK: OrderID, FK: CustomerID


Product (ProductID, Title, Price), PK: ProductID


OrderItem (OrderID, ProductID, Quantity), PK: (OrderID, ProductID), FK: OrderID references Order, FK: ProductID references Product

This relational schema maintains referential integrity among the tables and accurately models the relationships described in the ER diagram, enabling effective data management for Amazon’s order processing system.

Conclusion

This comprehensive assignment bridges SQL querying, conceptual data modeling, and schema translation, providing a robust foundation for understanding how business data systems are structured and operated. The questions prompt students to think critically about database design, enabling practical application of theoretical concepts to real-world scenarios, such as stock holdings and e-commerce order processing.

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems. Pearson.

Related Assignments