关于桥接实体与多属性实体的Oracle SQL创建技术问询

Hey there, let's tackle your database design questions one by one, and also fix a small critical mistake I noticed in your existing code first:

1. Creating the buys Bridge Entity #

Since customers and products have a many-to-many relationship (one customer can buy multiple products, one product can be bought by multiple customers), the buys table acts as the junction to link these two entities. Here's how to create it properly:

CREATE TABLE buys (
    cus_id NUMBER(5,0),
    prod_id NUMBER(5,0),
    -- Optional: Add extra fields if you need to track purchase details, e.g.:
    -- purchase_date DATE DEFAULT SYSDATE,
    -- quantity NUMBER(3,0) DEFAULT 1,
    CONSTRAINT buys_pk PRIMARY KEY (cus_id, prod_id),
    CONSTRAINT buys_customer_fk FOREIGN KEY (cus_id) REFERENCES customers(cus_id),
    CONSTRAINT buys_product_fk FOREIGN KEY (prod_id) REFERENCES products(prod_id)
);

Notes: #

• The composite primary key (cus_id, prod_id) ensures a customer can't be recorded as buying the same product multiple times (if repeat purchases are allowed, add a unique buy_id column as the primary key instead, and keep cus_id/prod_id as foreign keys).
• Foreign key constraints enforce referential integrity—you can't add a purchase for a customer or product that doesn't exist in their respective tables.

2. Handling Multi-Attributes (e.g., Customer Names) #

Your current approach to splitting the customer name into cus_fname, cus_mname, and cus_lname is exactly how you implement multi-attributes for a single logical property. This is a standard practice for several reasons:

• It allows easier filtering/sorting (e.g., finding all customers with the last name "Smith").
• It accommodates naming conventions across different regions (e.g., Western names with middle initials, or Eastern names with family names first).

If your requirement was for a customer to have multiple distinct names (like aliases or maiden names), you'd need a separate table (e.g., customer_aliases) linked to cus_id, but based on your ER diagram, splitting the full name into sub-fields is the correct approach.

3. NOT NULL Constraints for Primary Keys & Name Fields #

Primary Keys: #

In Oracle, when you define a column as a primary key, the database automatically applies both NOT NULL and UNIQUE constraints to it. You don't need to explicitly add NOT NULL to your primary key columns (like cus_id or prod_id)—it's redundant.

Name Fields: #

If your business rules require customers to provide a first and last name, you should explicitly add NOT NULL to those fields. Here's the corrected customers table creation statement:

CREATE TABLE customers (
    cus_id NUMBER(5,0) CONSTRAINT cus_id_pk PRIMARY KEY,
    cus_fname VARCHAR2(32) NOT NULL,
    cus_mname VARCHAR2(32), -- Optional field, no NOT NULL
    cus_lname VARCHAR2(32) NOT NULL,
    cus_gender NUMBER(1,0),
    cus_dob DATE
);

Critical Fix for Your products Table: #

You had a copy-paste error in your products code—you referenced cus_id instead of prod_id for the primary key. Here's the corrected version:

CREATE TABLE products (
    prod_id NUMBER(5,0) CONSTRAINT prod_id_pk PRIMARY KEY,
    prod_name VARCHAR2(32),
    prod_cost NUMBER(4,2),
    prod_retail NUMBER(4,2)
);

内容的提问来源于stack exchange，提问作者James Coles