Question

Design a relational database containing information about parts, suppliers, and customers. Each part might be supplied by several suppliers and ordered by many customers. Each supplier might supply many parts and have many customers. Each customer might order many parts from many suppliers; in fact, the same part might be ordered from more than one supplier. (Avoid redundancies similar to those in Figure 9.4.)

Short answer

Design tables for Parts, Suppliers, and Customers with intersection tables for relationships, ensuring normalization.

Step-by-step solution

Determine the Entities

Identify the main entities for the database, which are Parts, Suppliers, and Customers. Each of these entities will correspond to a separate table in the database.

Define the Relationships

Parts can be supplied by multiple Suppliers, Suppliers can supply multiple Parts, Customers can order multiple Parts, and Customers may deal with multiple Suppliers. This implies that there are many-to-many relationships between Parts and Suppliers, Parts and Customers, as well as Suppliers and Customers.

Create the Parts Table

Create a table named 'Parts' with attributes such as PartID (primary key), PartName, Description, etc. This table holds all the information related to the parts.

Create the Suppliers Table

Create a table named 'Suppliers' with attributes such as SupplierID (primary key), SupplierName, Location, etc. This table stores all details about the suppliers.

Create the Customers Table

Create a table named 'Customers' with attributes like CustomerID (primary key), CustomerName, ContactInfo, etc. This table contains all the customer details.

Design Intersection Tables for Many-to-Many Relationships

To handle many-to-many relationships, create intersection tables such as 'PartSupplier' with attributes like PartID and SupplierID, and 'PartOrder' with attributes like PartID, SupplierID, and CustomerID. These tables reference the primary keys from the related tables to represent each relationship uniquely.

Further Normalize the Data

Review the design to ensure there's no redundancy and that tables are in at least Third Normal Form (3NF). Each piece of data should appear only once unless it's a foreign key reference.

Key concepts

Entities and Relationships

When designing a relational database, it is crucial to start by identifying the entities involved. An entity, in database terms, represents a distinct piece of information. In the scenario of parts, suppliers, and customers, the entities are the Parts, Suppliers, and Customers. These entities will translate into database tables. Understanding the relationships between these entities is equally important. Relationships depict how entities interact with one another. For example, a Part might be supplied by multiple Suppliers and ordered by various Customers. Visualizing these interactions as relationships helps us determine how data should flow and connect. By capturing these relationships early on, we lay a strong foundation for an efficient database design.

Normalization

Normalization is the process of organizing a database to reduce redundancy and improve data integrity. The goal is to design a database structure that prevents modification anomalies and ensures the correct representation of data. During the normalization process, a database is typically organized into tables and columns. Each table should contain related data only. Although there are several normal forms, reaching at least the Third Normal Form (3NF) is generally recommended. This requires:

• Eliminating repeating groups and dependencies.
• Ensuring that every non-key attribute is fully functionally dependent on the primary key.
• Making sure there are no transitive dependencies.

This systematic approach helps maintain data accuracy and consistency across the database.

Many-to-Many Relationships

In database design, many-to-many relationships occur when multiple records in one table are associated with multiple records in another table. In our example, Parts can be supplied by multiple Suppliers, and Suppliers can supply numerous Parts. Similarly, many Customers can order a variety of Parts. To manage many-to-many relationships in a relational database, you use intermediate tables, often called join or junction tables. These tables serve to connect primary keys of related tables. For instance:

• 'PartSupplier' might include 'PartID' and 'SupplierID' to represent parts supplied by each supplier.
• 'PartOrder' could incorporate 'PartID', 'SupplierID', and 'CustomerID' to establish which parts have been ordered by which customers from specific suppliers.

These intersection tables effectively resolve many-to-many relationships by structuring data in a manner that reflects all possible interactions between entities.

Database Tables

Database tables are the fundamental units where data is stored within a relational database. Each table is organized into rows and columns, akin to a spreadsheet. In our scenario, each of the identified entities—Parts, Suppliers, and Customers—will have its own respective table. Here’s a breakdown of what each table might include:

• Parts Table: This could contain PartID, PartName, Description, etc., with PartID serving as the primary key.
• Suppliers Table: Attributes may include SupplierID, SupplierName, Location, etc., and SupplierID as its primary key.
• Customers Table: Might feature CustomerID, CustomerName, ContactInfo, etc., with CustomerID as the primary key.

Deleting or modifying data within these tables would lead to corresponding changes in related intersection tables, maintaining data consistency across the database.

Primary Keys and Foreign Keys

Primary keys and foreign keys are essential elements that establish the relationships and integrity of a relational database. A primary key is a unique identifier for each row within a table, ensuring that no two rows can have the same primary key value. They are crucial for the integrity and uni-dimensionality of the data. Meanwhile, a foreign key is a reference in a table to a primary key in another table, allowing relationships between tables. For instance, in the 'PartOrder' table, 'PartID' and 'SupplierID' serve as foreign keys that reference the primary keys in the Parts and Suppliers tables. This mechanism of keys enables the database to maintain a web of interconnected data with defined linkages. A well-designed structure of primary and foreign keys minimizes data redundancy while clarifying relationships between distinct pieces of information across the dataset.