13-22
Martin Shoes, Inc. (Planning a Database Using REA and E-R Methodology)
Martin Shoes, Inc., manufactures and distributes orthopedic footwear. To sell its products, the marketing department requires sales personnel to call on the shoe retailers within their assigned geographic territories. Each salesperson has a laptop computer, which he or she uses to record sales orders during the day and to send these sales order to Martin's network nightly for updating the company's sales order file.
Each day, warehouse personnel review the current sales order in its file, and where possible, pick the good and ready them for shipment. (Martin ships goods via common carrier, and shipping terms are generally FOB from the shipping point.) When the shipping department completes the shipment, it also notifies the billing department, which then prepares an invoice for the customer. Payment terms vary by customer, but most are "net 30." When the billing department receives a payment, the billing clerk credits the customer's account and records the cash received.
Requirements:
1. Identify the resources, events, and agents within Martin's revenue process.
2. Develop an E-R diagram for this process.
3. With a particular DBMS is mind, design the tables for this revenue process. Note that you will need tables for each resource, event, and agent, as well as tables for each many-to-many relationship.
This has me utterly confused. I even read the chapters and still haven't been able to figure it out. Someone help?
Understanding the revenue process of Martin Shoes, Inc. and designing a database using REA and E-R methodology can be challenging. However, I'll guide you through the steps to break it down and find a solution:
1. Identifying the resources, events, and agents:
- Resources: These are economic resources that are of interest to the company. In this case, resources could include products (shoes), customers, cash, and inventory.
- Events: These are business activities that affect resources. In this case, events could include sales order creation, shipment, billing, and cash receipt.
- Agents: These are individuals or entities who participate in events and have the ability to influence resources. In this case, agents could include sales personnel, warehouse personnel, billing clerks, and customers.
2. Developing an E-R diagram:
- An Entity-Relationship (E-R) diagram is a visual representation of entities (resources), relationships between entities, and attributes of entities.
- To create the E-R diagram, start by identifying the main entities involved (e.g., Shoes, Customers, Sales Orders, Inventory). Then, identify the relationships between these entities (e.g., Shoes are part of Sales Orders, Sales Orders belong to Customers, Inventory consists of Shoes, etc.).
- Use entity symbols (rectangles) and relationship symbols (diamonds) to represent entities and relationships, respectively. Connect the entities with relationships using lines/associations.
- Add attributes (e.g., ShoeName, Size, Quantity, CustomerName, Address) to entities as necessary.
3. Designing tables for the revenue process:
- For each resource, event, and agent identified in step 1, you will create tables in a database management system (DBMS).
- Determine the attributes for each table. For example, the Shoes table might have attributes like ShoeID, ShoeName, Size, and Quantity. The Customers table might have attributes like CustomerID, CustomerName, and Address.
- Consider relationships between entities (tables). For example, the Sales Orders table could have foreign keys referencing both the Shoes and Customers tables.
- Design tables for many-to-many relationships. For example, if one sales order can have multiple shoes, and one shoe can be a part of multiple sales orders, you might create an intermediate table called SalesOrderShoes, with attributes like SalesOrderID and ShoeID.
Note: The specific design and table structures will depend on the DBMS you are using, such as MySQL, Oracle, or SQL Server. Refer to the documentation or guides for your chosen DBMS to learn how to create tables and define relationships using SQL or the specific interface.
While this explanation provides a high-level overview, it's important to consult textbooks, online resources, or even seek guidance from an instructor or expert in database design for a more comprehensive understanding and a detailed implementation.