Problems creating table after normalisation

Question

I have been given a task to normalise a sales order, create a relational schema and input the data into SQL developer.

I have normalised to 3NF and got this:

Customer(Customer_ID, Customer_name)
Primary key Customer_ID

Employee(Employee_ID, Employee_name)
Primary key Employee_ID

Sales_Order(Sales_order_ID, Employee_ID, Customer_ID, Sale_date, Order_total)
Primary key Sales_order_ID
Foreign keys Employee_ID, Customer_ID 

Order_Line(Sales_order_ID, Product_ID, Quantity, Line_total)
Composite key Sales_order_ID
Foreign key Product_ID

Product(Product_ID, Product_name, Product_price, Product_colour)
Primary key Product_ID

CREATE TABLE EMPLOYEE(
    Employee_ID CHAR(3), 
    Employee_name CHAR(20),  
    CONSTRAINT pk_Employee PRIMARY KEY (Employee_name)
);

CREATE TABLE CUSTOMER(
    Customer_ID CHAR(5), 
    Customer_Name CHAR(20),  
    CONSTRAINT pk_Customer PRIMARY KEY (Customer_ID)
);

CREATE TABLE PRODUCT(
    Product_ID CHAR(5),
    Product_Name CHAR(30),
    Product_Colour CHAR(10),
    Product_Price CHAR(5),
    CONSTRAINT pk_Product PRIMARY KEY (Product_ID)
);

CREATE TABLE SALES_ORDER(
    Sales_order_ID CHAR(6),
    Employee_ID CHAR(3),
    Customer_ID CHAR(5),
    Sale_Date CHAR(10),
    Order_total CHAR(7),
    CONSTRAINT pk_Order PRIMARY KEY (Sales_order_ID),
    CONSTRAINT fk_Order FOREIGN KEY (Employee_ID) REFERENCES EMPLOYEE (Employee_ID),
    FOREIGN KEY (Customer_ID) REFERENCES CUSTOMER (Customer_ID)
);

CREATE TABLE ORDER_LINE(
    Sales_order_ID CHAR(6),
    Product_ID CHAR(5),
    Quantity CHAR(3),
    Line_total CHAR(5),
    CONSTRAINT pk_Order_Line PRIMARY KEY (Sales_order_ID),
    FOREIGN KEY (Product_ID) REFERENCES PRODUCT (Product_ID)
);

I am able to create tables Employee, Customer, Product, Sales_order but not Order_line.

I am told that the table or view does not exist.

What does this mean?

Have I normalised incorrectly?

Have I designed the relational schema incorrectly?

Answer 1

When I run your code in this db fiddle, creation of table SALE_ORDER fails with the following message:

ORA-02270: no matching unique or primary key for this column-list

This is because of this foreign key:

CONSTRAINT fk_Order FOREIGN KEY (Employee_ID) REFERENCES EMPLOYEE (Employee_ID),

The underlying problem is that you have defined Employee_name as the primary key of table EMPLOYEE.

CREATE TABLE EMPLOYEE (
    Employee_ID CHAR(3), 
    Employee_name CHAR(20),  
    CONSTRAINT pk_Employee PRIMARY KEY (Employee_ID)
);

This does not seem like a sensible option. In the real life, it is possible that two different employees would have the same name. Instead, you probably want to use Employee_ID as the primary key for EMPLOYEE.

Consider this definition for table EMPLOYEE:

CREATE TABLE EMPLOYEE (
    Employee_ID CHAR(3), 
    Employee_name CHAR(20),  
    CONSTRAINT pk_Employee PRIMARY KEY (Employee_ID)
);

With this new set up, all tables are created successfully. You may now insert your data.

Demo on DB Fiddle

---

Side note: I forsee issues with table ORDER_LINE:

• primary key should be Order_line_ID instead of Sales_order_ID

• Sales_order_ID should have a foreign key constraint referencing SALE_ORDER(Sales_order_ID).

Consider this new definition for ORDER_LINE:

CREATE TABLE ORDER_LINE(
    Order_line_ID CHAR(6),
    Sales_order_ID CHAR(6),
    Product_ID CHAR(5),
    Quantity CHAR(3),
    Line_total CHAR(5),
    CONSTRAINT pk_Order_Line PRIMARY KEY (Order_line_ID),
    FOREIGN KEY (Product_ID) REFERENCES PRODUCT (Product_ID),
    FOREIGN KEY (Sales_order_ID) REFERENCES SALES_ORDER(Sales_order_ID)
);