Use The Data Model Designed In Week 2

Use the data model designed in Week 2 This data model will be reviewed and taken from the conceptual model to logical and physical model status Make sure you define the following

Use the data model designed in Week 2. This data model will be reviewed and taken from the conceptual model to logical and physical model status. Make sure you define the following

Analyze and develop a comprehensive database design for Joe’s Sub Shop online ordering and delivery service based on the conceptual data model created in Week 2. Your task is to transition from the conceptual model to the logical model, and then to the physical model, ensuring that the database will effectively support the shop's ordering and delivery operations. The specific requirements are as follows:

1. Select an appropriate database management system, such as Oracle, SQL Server, or MySQL. For each attribute within your entities, specify the data type and size, ensuring data integrity and efficiency.

2. Review all relationships within your data model to confirm they are accurately defined, properly implemented, and correctly represented in the logical schema. Address any many-to-many relationships with junction tables or appropriate foreign key constraints.

3. Normalize your data model to at least the Third Normal Form (3NF). This involves eliminating partial dependencies and transitive dependencies, ensuring each table has a primary key, and that non-key attributes are dependent solely on the primary key.

Ensure that your final design is clear, optimized, and adheres to normalization principles to facilitate efficient data storage, retrieval, and maintenance. Your submission should include detailed descriptions of each entity, attribute data types and sizes, relationships, and normalization steps taken. Properly document your assumptions and choices throughout the modeling process.

Paper For Above instruction

In designing an effective database for Joe’s Sub Shop’s online ordering and delivery system, it is crucial to adopt a methodical approach that transitions from a conceptual to a logical and then physical data model. This systematic process ensures the resulting database is well-structured, scalable, and maintains data integrity while supporting the business requirements efficiently.

Selection of Database Management System (DBMS). For this project, SQL Server is chosen due to its robustness, extensive support for normalization, and widespread enterprise use. SQL Server allows for flexible data types, scalable storage, and strong referential integrity enforcement, which are essential for managing customer, order, and delivery data effectively.

Logical Data Model Development. Beginning with the conceptual model, the entities identified include Customers, Address, Orders, Products, and Payments. The relationships are as follows: each customer has one address (one-to-one), a customer can place multiple orders (one-to-many), each order can contain many products (many-to-many), and each order has one associated payment (one-to-one). To resolve the many-to-many relationship between Orders and Products, a junction table, such as Order_Details, is created, capturing product quantities and specific toppings.

Handling Relationships and Foreign Keys. In the logical model, foreign key constraints are implemented to enforce relationships: Customer_ID in Orders references Customers; Order_ID and Product_ID in Order_Details reference Orders and Products, respectively; Payment_ID in Orders references Payments; and Address_ID in Customers references Addresses. These constraints ensure referential integrity and facilitate joins across tables.

Normalization to 3NF. The normalization process involves examining each table to remove partial dependencies and transitive dependencies. Initially, the Tables are analyzed:

  • Customer table contains CustomerID, CustomerName, PhoneNumber, and AddressID. No partial dependencies since all are dependent on the primary key.
  • Address table with AddressID, Place, ZipCode — directly dependent on AddressID.
  • Orders table with OrderID, CustomerID, Quantity, TotalPrice, PaymentID. No transitive dependencies; all attributes depend on OrderID.
  • Products table with ItemNumber, SandwichName, Toppings, Price, all directly dependent on ItemNumber.
  • Payments table with PaymentID, CardType, CardDetails, dependent on PaymentID.

The tables are structured so that each non-primary attribute depends solely on the primary key, fulfilling 3NF criteria. Any redundancies or dependencies observed are resolved by creating separate tables rather than including multiple data points in a single table.

Designing the Physical Model. Data types and sizes are assigned to each attribute considering their nature:

  • CustomerID: INT, primary key
  • CustomerName: NVARCHAR(50)
  • PhoneNumber: NVARCHAR(15)
  • AddressID: INT, primary key
  • Place: NVARCHAR(100)
  • ZipCode: NVARCHAR(10)
  • OrderID: INT, primary key
  • CustomerID: INT, foreign key
  • Quantity: INT
  • TotalPrice: DECIMAL(10,2)
  • PaymentID: INT, primary key
  • CardType: NVARCHAR(20)
  • CardDetails: NVARCHAR(50)
  • ItemNumber: INT, primary key
  • SandwichName: NVARCHAR(50)
  • Toppings: NVARCHAR(100)
  • Price: DECIMAL(10,2)

The physical implementation also involves creating indexes on foreign key columns, setting appropriate constraints, and optimizing table structures for performance. Proper documentation of these types and sizes ensures data validation and efficient storage.

Conclusion. Mapping the conceptual model through normalization to a physical schema in SQL Server results in a well-structured database that supports Joe’s Sub Shop online ordering system. By carefully selecting data types, enforcing relationships with keys, and ensuring normal form compliance, the database will facilitate efficient data management, maintainability, and scalability aligned with the business needs.

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
  • Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, & Management (11th ed.). Cengage Learning.
  • Korth, H. F., Silberschatz, A., & Sudarshan, S. (2010). Database System Concepts (6th ed.). McGraw-Hill.
  • Rob, P., & Coronel, C. (2009). Database Systems: Design, Implementation, and Management (9th ed.). Cengage Learning.
  • Hoffer, J. A., Venkataraman, R., & Topi, H. (2016). Modern Database Management (12th ed.). Pearson.
  • Harrington, J. L. (2016). Relational Database Design and Implementation. Morgan Kaufmann.
  • Ullman, J. D., & Widom, J. (2008). A First Course in Database Systems (3rd ed.). Pearson.
  • Connolly, T., & Begg, C. (2014). Database Systems (6th ed.). Pearson.
  • Harper, W. (2010). SQL Server 2008 R2 Unleashed. Sams Publishing.

Related Assignments