Solutions to Extra Problems for Module 3
1. Write a CREATE TABLE statement for the Customer table. Choose data types appropriate
using standard SQL data types where possible. Note that the CustBal column contains
numeric data. The currency symbols are not stored in the database. The CustFirstName and
CustLastName columns are required (not null).
2. Write a CREATE TABLE statement for the Employee table. Choose data types appropriate
using standard SQL data types where possible. The EmpFirstName, EmpLastName, and
EmpEMail columns are required (not null).
3. Write a CREATE TABLE statement for the OrderTbl table. Choose data types appropriate
using standard SQL data types where possible. The OrdDate column is required (not null).
4. Identify the foreign keys and 1-M relationships among the Customer, Employee, and
OrderTbl tables. For each relationship, identify the parent table and the child table.
5. Extend your CREATE TABLE statement from problem (3) with referential integrity
constraints.
1. From examination of the sample data and your common understanding of order entry
businesses, are null values allowed for the foreign keys in the OrderTbl table? Why or why
not? Extend the CREATE TABLE statement in problem (5) to enforce the null value
constraints if any.
2. Extend your CREATE TABLE statement for the Employee table (problem 2) with a unique
constraint for EmpEMail. Use a named constraint clause for the unique constraint.
The CREATE TABLE statement solution uses the standard SQL:2016 data types. Your DBMS
may provide a different collection of data types.
10/7/2022 Solutions to Module 3 Problems 2
1.
Oracle
CREATE TABLE Customer
( CustNo CHAR(8),
CustFirstName VARCHAR2(20) CONSTRAINT CustFirstNameRequired NOT NULL,
CustLastName VARCHAR2(30) CONSTRAINT CustLastNameRequired NOT NULL,
CustCity VARCHAR2(30),
CustState CHAR(2),
CustZip CHAR(10),
CustBal DECIMAL(12,2),
CONSTRAINT PKCustomer PRIMARY KEY (CustNo) );
MySQL
CREATE TABLE Customer
( CustNo CHAR(8),
CustFirstName VARCHAR(20) NOT NULL,
CustLastName VARCHAR(30) NOT NULL,
CustCity VARCHAR(30),
CustState CHAR(2),
CustZip CHAR(10),
CustBal DECIMAL(12,2),
CONSTRAINT PKCustomer PRIMARY KEY (CustNo) )
PostgreSQL
CREATE TABLE Customer
( CustNo CHAR(8),
CustFirstName VARCHAR(20) NOT NULL,
CustLastName VARCHAR(30) NOT NULL,
CustCity VARCHAR(30),
CustState CHAR(2),
CustZip CHAR(10),
CustBal DECIMAL(12,2),
CONSTRAINT PKCustomer PRIMARY KEY (CustNo) );
2.
Oracle
CREATE TABLE Employee
( EmpNo CHAR(8),
EmpFirstName VARCHAR2(20) CONSTRAINT EmpFirstNameRequired NOT
NULL,
EmpLastName VARCHAR2(30) CONSTRAINT EmpLastNameRequired NOT
NULL,
EmpPhone CHAR(15),
EmpEMail VARCHAR2(50) CONSTRAINT EmpEmailRequired NOT NULL,
CONSTRAINT PKEmployee PRIMARY KEY (EmpNo) );
10/7/2022 Solutions to Module 3 Problems 3
MySQL
CREATE TABLE Employee
( EmpNo CHAR(8),
EmpFirstName VARCHAR(20) NOT NULL,
EmpLastName VARCHAR(30) NOT NULL,
EmpPhone CHAR(15),
EmpEMail VARCHAR(50) NOT NULL,
CONSTRAINT PKEmployee PRIMARY KEY (EmpNo) )
PostgreSQL
CREATE TABLE Employee
( EmpNo CHAR(8),
EmpFirstName VARCHAR(20) NOT NULL,
EmpLastName VARCHAR(30) NOT NULL,
EmpPhone CHAR(15),
EmpEMail VARCHAR(50) NOT NULL,
CONSTRAINT PKEmployee PRIMARY KEY (EmpNo) );
3.
Oracle
CREATE TABLE OrderTbl
( OrdNo CHAR(8),
OrdDate DATE CONSTRAINT OrdDateRequired NOT NULL,
CustNo CHAR(8),
EmpNo CHAR(8),
CONSTRAINT PKOrderTbl PRIMARY KEY (OrdNo) )
MySQL
CREATE TABLE OrderTbl
( OrdNo CHAR(8),
OrdDate DATE NOT NULL,
CustNo CHAR(8),
EmpNo CHAR(8),
CONSTRAINT PKOrderTbl PRIMARY KEY (OrdNo) )
PostgreSQL
CREATE TABLE OrderTbl
( OrdNo CHAR(8),
OrdDate DATE NOT NULL,
CustNo CHAR(8),
EmpNo CHAR(8),
CONSTRAINT PKOrderTbl PRIMARY KEY (OrdNo) )
10/7/2022 Solutions to Module 3 Problems 4
4.
There are two 1-M relationships: (1) Customer (CustNo PK) – OrderTbl (CustNo FK) and
(2) Employee (EmpNo PK) – OrderTbl (EmpNo FK).
5.
The CREATE TABLE statement has been extended with foreign keys for CustNo and
EmpNo.
Oracle
CREATE TABLE OrderTbl
( OrdNo CHAR(8),
OrdDate DATE CONSTRAINT OrdDateRequired NOT NULL,
CustNo CHAR(8),
EmpNo CHAR(8),
CONSTRAINT PKOrderTbl PRIMARY KEY (OrdNo) ,
CONSTRAINT FKCustNo FOREIGN KEY (CustNo) REFERENCES Customer,
CONSTRAINT FKEmpNo FOREIGN KEY (EmpNo) REFERENCES Employee
)’
MySQL
CREATE TABLE OrderTbl
( OrdNo CHAR(8),
OrdDate DATE NOT NULL,
CustNo CHAR(8),
EmpNo CHAR(8),
CONSTRAINT PKOrderTbl PRIMARY KEY (OrdNo) ,
CONSTRAINT FKCustNo FOREIGN KEY (CustNo) REFERENCES Customer (CustNo),
CONSTRAINT FKEmpNo FOREIGN KEY (EmpNo) REFERENCES Employee (EmpNo)
)
MySQL
CREATE TABLE OrderTbl
( OrdNo CHAR(8),
OrdDate DATE NOT NULL,
CustNo CHAR(8),
EmpNo CHAR(8),
CONSTRAINT PKOrderTbl PRIMARY KEY (OrdNo) ,
CONSTRAINT FKCustNo FOREIGN KEY (CustNo) REFERENCES Customer (CustNo),
CONSTRAINT FKEmpNo FOREIGN KEY (EmpNo) REFERENCES Employee (EmpNo)
);
6.
Null values are not allowed for CustNo. The sample data shows that each order has a related
customer. In addition, common practice indicates that an order requires a customer. Fraud
could result if orders are stored without a related customer. Null values are allowed for the
10/7/2022 Solutions to Module 3 Problems 5
EmpNo column. The sample data shows rows without an EmpNo value. The null values
may correspond to internet orders where no employee takes the order.
Oracle
CREATE TABLE OrderTbl
( OrdNo CHAR(8),
OrdDate DATE CONSTRAINT OrdDateRequired NOT NULL,
CustNo CHAR(8) CONSTRAINT CustNoRequired NOT NULL,
EmpNo CHAR(8),
CONSTRAINT PKOrderTbl PRIMARY KEY (OrdNo) ,
CONSTRAINT FKCustNo FOREIGN KEY (CustNo) REFERENCES Customer,
CONSTRAINT FKEmpNo FOREIGN KEY (EmpNo) REFERENCES Employee
);
MySQL
CREATE TABLE OrderTbl
( OrdNo CHAR(8),
OrdDate DATE NOT NULL,
CustNo CHAR(8) NOT NULL,
EmpNo CHAR(8),
CONSTRAINT PKOrderTbl PRIMARY KEY (OrdNo) ,
CONSTRAINT FKCustNo FOREIGN KEY (CustNo) REFERENCES Customer (CustNo),
CONSTRAINT FKEmpNo FOREIGN KEY (EmpNo) REFERENCES Employee (EmpNo)
)
PostgreSQL
CREATE TABLE OrderTbl
( OrdNo CHAR(8),
OrdDate DATE NOT NULL,
CustNo CHAR(8) NOT NULL,
EmpNo CHAR(8),
CONSTRAINT PKOrderTbl PRIMARY KEY (OrdNo) ,
CONSTRAINT FKCustNo FOREIGN KEY (CustNo) REFERENCES Customer (CustNo),
CONSTRAINT FKEmpNo FOREIGN KEY (EmpNo) REFERENCES Employee (EmpNo)
);
7.
Oracle
CREATE TABLE Employee
( EmpNo CHAR(8),
EmpFirstName VARCHAR2(20) CONSTRAINT EmpFirstNameRequired NOT NULL,
EmpLastName VARCHAR2(30) CONSTRAINT EmpLastNameRequired NOT NULL,
EmpPhone CHAR(15),
EmpEMail VARCHAR2(50) CONSTRAINT EmpEmailRequired NOT NULL,
CONSTRAINT PKEmployee PRIMARY KEY (EmpNo),
CONSTRAINT UniqueEMail UNIQUE (EmpEMail) );
MySQL
10/7/2022 Solutions to Module 3 Problems 6
CREATE TABLE Employee
( EmpNo CHAR(8),
EmpFirstName VARCHAR(20) NOT NULL,
EmpLastName VARCHAR(30) NOT NULL,
EmpPhone CHAR(15),
EmpEMail VARCHAR(50) NOT NULL UNIQUE,
CONSTRAINT PKEmployee PRIMARY KEY (EmpNo)
CONSTRAINT UniqueEMail UNIQUE (EmpEMail) )
PostgreSQL
CREATE TABLE Employee
( EmpNo CHAR(8),
EmpFirstName VARCHAR(20) NOT NULL,
EmpLastName VARCHAR(30) NOT NULL,
EmpPhone CHAR(15),
EmpEMail VARCHAR(50) NOT NULL UNIQUE,
CONSTRAINT PKEmployee PRIMARY KEY (EmpNo),
CONSTRAINT UniqueEMail UNIQUE (EmpEMail) );
