Relational Database Design Tasks

Understanding Primary Keys in Relational Databases

Choosing the right primary key is essential for guaranteeing the uniqueness of each record and for supporting efficient joins. In the REA (Resources, Events, Agents) mapping scenario, the best practice is to use an auto‑incremented integer as the primary key for the Resource table.

• Why an auto‑incremented integer? It provides a simple, immutable identifier that does not depend on business data such as names, which can change or be duplicated.
• Avoid composite keys when possible – they increase index size and complicate foreign‑key relationships.
• UUIDs are useful for distributed systems, but they are larger and slower for typical relational workloads.

By defining Resource_ID INT AUTO_INCREMENT PRIMARY KEY, you ensure each resource is uniquely identifiable, making future queries and joins straightforward.

Establishing Correct Foreign Key Relationships

Linking Agents to Events

In the REA Event table, the foreign key that connects a sales officer (Agent) to a sale event must reference the Agent's primary key. The correct definition is:

• Event.Agent_ID REFERENCES Agent.Agent_ID

This relationship enforces referential integrity, ensuring that every event record points to a valid agent.

Warehouse Schema: Connecting Stock to City

For a warehouse management system, the Stock table should reference the city where the stock is stored. The proper foreign key is:

• Stock.City_ID REFERENCES Warehouses.City_ID

Using the city identifier rather than a batch number or warehouse name keeps the design normalized and avoids redundancy.

Normalization: Moving Toward Second Normal Form (2NF)

Normalization eliminates data anomalies by organizing data into logical tables. When converting a car dealership spreadsheet to 2NF, any attribute that depends only on the primary key should be placed in its own table.

Identifying the Attribute to Relocate

Among the listed columns, Car_VIN (Vehicle Identification Number) uniquely identifies each vehicle and should be moved to a separate Inventory table. This separation ensures that vehicle‑specific details are not duplicated across sales records.

• Original spreadsheet columns: Salesman_Name, Car_VIN, Customer_Address, Commission_Rate.
• After normalization: Inventory (Car_VIN, Model, Year, etc.) and Sales (Invoice_No, Customer_ID, Car_VIN, etc.).

Defining Primary Keys After Normalization

Once tables are normalized, the primary key must uniquely identify each transaction. For the Sales table, the optimal composite key combines Invoice_No with Customer_ID. This combination guarantees that each invoice is linked to a specific customer, preventing duplicate invoice numbers across different customers.

• Incorrect options:
  • Car_VIN together with Salesman_ID – does not ensure invoice uniqueness.
  • Date together with Car_Model – many sales can share the same date and model.
  • Single auto‑incremented Sales_ID – while technically unique, it loses the business meaning of the invoice number.
• Correct composite key: PRIMARY KEY (Invoice_No, Customer_ID).

Chart of Accounts: Recognizing Expense Accounts

In a standard chart of accounts, each account number follows a coding convention that groups similar accounts together. Expense accounts typically fall within the 5000‑5999 range.

The correct example is 5500 – Utilities Expense. This number clearly belongs to the Expenses category, whereas numbers like 1500 (Equipment), 2500 (Accounts Payable), and 3500 (Retained Earnings) belong to Assets, Liabilities, and Equity respectively.

Designing the Stock Table: What Belongs and What Doesn’t

Attributes to Include

The Stock table should store information directly related to inventory items, such as:

• Quantity
• Cement_Type
• Batch_Number
• City_ID (foreign key to the city table)

Attributes to Exclude

Attributes that describe the city itself, like City_Capacity, belong in the City or Warehouse table, not in Stock. Storing city capacity in Stock would duplicate data and violate normalization principles.

Detecting Gaps in Sequential IDs: The Gate Pass Log Example

Sequential identifiers are often used for tracking logs. An intentional missing number can indicate a removed or skipped entry. In the Gate Pass Log, the sequence 001, 002, 004, 005 shows a gap at 003, which is the expected missing number.

• Why gaps matter: They can signal data entry errors, deletions, or system‑generated skips.
• Best practice: Use auto‑incremented keys for internal integrity, but maintain a separate business‑visible reference number if gaps need to be tracked.

Putting It All Together: A Mini‑Project Walkthrough

To reinforce the concepts, let’s outline a small project that incorporates each of the topics covered.

1. Create the Resource table with Resource_ID INT AUTO_INCREMENT PRIMARY KEY and additional columns like Resource_Name.
2. Define the Agent table with Agent_ID INT AUTO_INCREMENT PRIMARY KEY and Name.
3. Build the Event table including Event_ID, Agent_ID (foreign key), and Resource_ID (foreign key).
4. Normalize dealership data by creating an Inventory table (Car_VIN primary key) and a Sales table with the composite primary key (Invoice_No, Customer_ID).
5. Set up the Chart of Accounts with expense accounts in the 5000 range, e.g., 5500 Utilities Expense.
6. Design the Warehouse schema with Warehouses (City_ID primary key) and Stock (City_ID foreign key, Quantity, Cement_Type, Batch_Number).
7. Implement a Gate Pass Log using an auto‑incremented Log_ID and a separate Reference_Number that may contain intentional gaps.

Following these steps ensures a well‑structured, normalized database that respects primary‑key uniqueness, foreign‑key integrity, and industry‑standard coding conventions.

Key Takeaways for Database Designers

• Use simple, immutable primary keys (auto‑increment integers or UUIDs) to guarantee uniqueness.
• Define foreign keys that reference the exact primary key of the related table.
• Normalize data to at least 2NF by moving attributes that depend only on part of a composite key into separate tables.
• When creating composite primary keys, combine columns that together provide business meaning (e.g., Invoice_No + Customer_ID).
• Follow chart‑of‑accounts conventions: expense accounts typically start with 5.
• Store only attributes that belong to a table’s entity; avoid mixing city‑level data with stock‑level data.
• Be aware of intentional gaps in sequential identifiers and handle them appropriately.

By mastering these principles, you’ll design relational databases that are robust, scalable, and easy to maintain.