Subject: Oracle DBA Problem B DDL Assume that you are designing a database with
ID: 3851109 • Letter: S
Question
Subject: Oracle DBA
Problem B DDL
Assume that you are designing a database with the following tables:
CUSTOMER (CustomerID, LastName, FirstName, Phone, Email)
PURCHASE (InvoiceNumber, Date, PreTaxAmount, CustomerID)
PURCHASE_ITEM (InvoiceNumber, ItemNumber, RetailPrice)
ITEM (ItemNumber, Description, Cost, ArtistName)
where
CustomerID in PURCHASE must exist in CustomerID in CUSTOMER
InvoiceNumber in PURCHASE_ITEM must exist in InvoiceNumber in
PURCHASE
ItemNumber in PURCHASE_ITEM must exist in ItemNumber in ITEM.
Write CREATE TABLE statements for each of these tables. Include any foreign
key constraints appropriate for each of these tables. Make your own assumptions
regarding cascading deletions.
Explanation / Answer
CREATE TABLE CUSTOMER
(
CustomerID int,
LastName varchar(50),
FirstName varchar(50),
Phone varchar(10),
Email varchar(50),
CONSTRAINT CUSTOMER_pkey
PRIMARY KEY (CustomerID)
)
CREATE TABLE PURCHASE
(
InvoiceNumber int,
Date datetime,
PreTaxAmount decimal(10,2),
CustomerID int,
CONSTRAINT PURCHASE_pkey
PRIMARY KEY (InvoiceNumber)
CONSTRAINT PURCHASE_fkey
FOREIGN KEY (CustomerID)
)
CREATE TABLE PURCHASE_ITEM
(
InvoiceNumber int,
ItemNumber int,
RetailPrice double(10,2),
CONSTRAINT PURCHASE_ITEM_pkey
PRIMARY KEY (InvoiceNumber) ,
CONSTRAINT PURCHASE_ITEM_fkey1
FOREIGN KEY (ItemNumber) ,
)
CREATE TABLE ITEM
(
ItemNumber int,
Description varchar(50),
Cost double(10,2),
ArtistName varchar(50),
CONSTRAINT ITEM_pkey
PRIMARY KEY (ItemNumber)
)