Term Project Deliverable

Image1pngimage2pngimage3pngis 3063 Term Project Deliverable 1 Templ

Image1pngimage2pngimage3pngis 3063 Term Project Deliverable 1 Templ

image1.png image2.png image3.png IS 3063 Term Project Deliverable 1 Template Group Number: Your Name: Your email: Note that in the first deliverable, each student should independently come up with a business problem/ERD with section 1, 3, 4 outlined below completed and exchange this individual proposal with other group members. The group will jointly choose one to develop as a group (you may also combine different portions of multiple ERDs to form a new ERD for the group). Your individual ERD cannot be the same as any example covered in the book, i.e., your ERD may contain some entities covered in the book examples but more than 50% of your entities or relationships should be different from any examples covered in the book/assignments.

Paper For Above instruction

Designing an effective and comprehensive database schema is crucial for managing complex business processes. Based on the provided instructions and the example business context of Perfect Furniture, this paper explores the development of an Entity-Relationship Diagram (ERD) tailored for a furniture manufacturing and sales company. The focus will be on addressing the business problem of data fragmentation across disparate systems, establishing clear entities and relationships, enforcing business rules, and outlining functional features of the proposed database system.

Business Context and Data Management Problem

Perfect Furniture operates as a nationwide furniture manufacturer and retailer, offering a diverse range of products to a broad customer base. Currently, the company employs multiple isolated database systems across its production, sales, inventory management, and distribution units. This disjointed data infrastructure results in inconsistencies, data redundancy, and challenges in real-time monitoring of inventory levels. Consequently, management faces difficulty in making strategic decisions related to production schedules, inventory replenishment, and sales analysis.

The core data management problem centers on integrating these disparate systems into a unified database platform. Such integration aims to streamline data flow, reduce discrepancies, and provide comprehensive visibility into operations for strategic planning and operational efficiency. The ultimate goal is to develop a robust database system that not only consolidates data but also supports dynamic reporting, inventory tracking, and sales analysis—facilitating informed decision-making and improved customer service.

Entities, Attributes, and Data Specifications

The foundational step in designing the ERD involves identifying key entities and their attributes, along with defining constraints and data types. Below is an illustrative set of entities for the furniture business:

Entity: Product

  • ProductID: 8-digit unique identifier for each product. (PK, Char(8), Not Null)
  • Name: Name of the furniture item. (Varchar(100), Not Null)
  • Category: Defines the type of furniture (e.g., sofa, table). (Varchar(50))
  • Price: Retail price of the product. (Decimal(8,2), Not Null, >0)
  • InStockQuantity: Current inventory count. (Integer, Not Null, >=0)

Entity: Customer

  • CustomerID: Unique identifier. (PK, Char(10), Not Null)
  • Name: Customer full name. (Varchar(100), Not Null)
  • Address: Customer address. (Varchar(200))
  • PhoneNumber: Contact number. (Varchar(15))
  • State: Geographical location. (Varchar(50))

Entity: Order

  • OrderID: Alphanumeric order identifier. (PK, Char(12), Not Null)
  • OrderDate: Date when order was placed. (Date, Not Null)
  • CustomerID: FK referencing Customer. (Char(10), Not Null)
  • Status: Order status (Pending, Shipped, Delivered). (Varchar(20))

Entity: Employee

  • EmployeeID: Unique employee identifier. (PK, Char(8), Not Null)
  • Name: Employee full name. (Varchar(100))
  • Position: Job role. (Varchar(50))
  • Department: Department name. (Varchar(50))

Entity: Shipment

  • ShipmentID: Unique shipment identifier. (PK, Char(12), Not Null)
  • OrderID: FK referencing Order. (Char(12), Not Null)
  • ShippedDate: Date shipped. (Date)
  • Carrier: Shipping company. (Varchar(50))
  • Status: Shipping status. (Varchar(20))

Entity: Supplier

  • SupplierID: Unique identifier. (PK, Char(8), Not Null)
  • Name: Supplier company name. (Varchar(100), Not Null)
  • ContactInfo: Contact details. (Varchar(200))

Business Rules and Relationships

Defining the relationships between entities establishes the operational constraints and guiding principles for the ERD. Below are key business rules with cardinalities and constraints:

  • Customer—Order: 1:M, a customer can place zero or many orders, but each order is associated with exactly one customer. (Minimum: 0, Maximum: Many for Customer; Minimum: 1, Maximum: 1 for Order)
  • Order—Order Line: 1:M, each order can contain multiple order lines, each order line belongs to exactly one order. (Minimum: 1, Maximum: Many for Order; Minimum: 1, Maximum: 1 for OrderLine)
  • Product—Order Line: 1:M, a product can appear in many order lines, but each order line contains only one product. Additionally, a product cannot be ordered multiple times in the same order (enforced via a unique composite key on OrderID and ProductID in OrderLine). (Minimum: 0, Maximum: Many for Product; Minimum: 1, Maximum: 1 for OrderLine)
  • Order—Shipment: 1:1, each order is shipped once, and each shipment corresponds to a single order. (Minimum: 0 or 1, depending on whether shipping occurs immediately; maximum: 1)
  • Supply—Product: M:N, a product can have multiple suppliers, and a supplier can supply multiple products. This is broken into two 1:M relationships via an associative entity (Supply).

ERD Design

The ERD will encompass all entities with primary and foreign keys, accurately representing the relationships, including 1:1, 1:M, and M:N relationships, and accommodating constraints such as uniqueness and participation. The ERD will also incorporate supertype/subtype hierarchies if applicable, for example, differentiating between Wholesale and Retail customers, if required.

Application Functionality and Future Extensions

The developed database system aims to provide comprehensive data management capabilities for the business. Users will be able to log and monitor inventory levels, process orders efficiently, track shipments, and generate analytical reports on sales performance, customer preferences, and regional trends. Query functionalities will enable users to identify top-selling products, analyze customer demographics, and monitor inventory turnover rates.

Future extensions include integrating this database with an online storefront system to enable real-time order processing and status updates. Additionally, the system can be connected to a logistics platform for shipment tracking, enabling customers and managers to receive timely updates. Advanced analytics modules can be added for sales forecasting, demand planning, and personalized marketing strategies.

Overall, this ERD and the associated application design will modernize the company's data infrastructure, supporting scalable growth, cross-departmental data visibility, and strategic decision-making.

References

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

Related Assignments