I dont think Im on the right track at all for answering this

I don;t think I\'m on the right track at all for answering this question (

CREATE TABLE STORE
(
StoreID nchar(10) Primary key,
StoreName varchar2(25),
City varchar2(25),
Country varchar2(25),
Phone number(10),
Fax number(10),
Email varchar2 (50),
Contact varchar2 (25));
/

CREATE TABLE PURCHASE_ITEM
(
PurchaseItemID number(10) Primary Key,
StoreID varchar2(25),
PurchaseDate DATE,
Description varchar2(100),
ItemCategory varchar2(25),
PriceUSD number(10),

);
/

CREATE TABLE SHIPMENT
(
ShipmentID number, Primary Key,
ShipperID number,
ShipperInvoiceNumber number,
Origin varchar2(25),
Destination varchar2(25),
DepartureDate number,
ArrivalDate number,

);
/

CREATE TABLE SHIPPER
(
ShipperID number,
ShipperName VARCHAR2(25),
Phone number,
Fax number,
Email VARCHAR2 (50),
Contact VARCHAR2 (40),
);
/

CREATE TABLE SHIPMENT_ITEM
(
ShipmentID number, AUTOINCREMENT(100,1),
ShipmentItemID number,
PurchaseItemID number,
InsuredValue number, DEFAULT(100),
);
/

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>QUESTION>>>>>>>>>>>>>>>>>>>>>>>>:

Suppose that you have designed a database for Morgan Importing that has the following tables:

STORE (StoreID, StoreName, City, Country, Phone, Fax, Email, Contact) PURCHASE_ITEM (PurchaseItemID, StoreID, Date, Description, Category, PriceUSD) SHIPMENT (ShipmentID, ShipperID, ShipperInvoiceNumber, Origin, Destination, DepartureDate, ArrivalDate) SHIPMENT_ITEM (ShipmentID, ShipmentItemID, PurchaseItemID, InsuredValue) SHIPPER (ShipperID, ShipperName, Phone, Fax, Email, Contact)

STORE has a surrogate key StoreID and we’ll let the DBMS assign the value when possible. For Oracle, we’ll use create sequence when we create the tables.

Check the NULL/NOT NULL constraints for each table column and Specify alternate keys (UNIQUE), if any   A. Write CREATE TABLE statements for each of the tables based on the information. Set the first value of PurchaseID to 500 and increment it by 5. Set the first value of ShipmentID to 100 and increment it by 1. Use FOREIGN KEY constraints to create appropriate referential integrity constraints. Set UPDATE and DELETE behavior in accordance with your referential integrity action design. Set the default value of InsuredValue to 100.
Write a constraint that STORE.Country be limited to seven countries (you can pick the seven countries you want to purchase from), but be sure to include the countries for the PURCHASE_ITEM.City locations in the following figure.

B. Use the following data to inset rows into the tables



C. Write an UPDATE statement to change values of STORE.City from New York City to NYC.

D. Write a DELETE statement(s) to delete a SHIPMENT and all of the items on that SHIPMENT.

E. Create a view called PurchaseSummaryView that shows only PURCHASE_ITEM.PurchaseID, PURCHASE_ITEM.Date, PURCHASE_ITEM.Description, and PURCHASE_ITEM.PriceUSD.

Solution

Question is not well expressed some information are missing

A>

In Oracle Sql Auto key generation is a two step process.

Step1:

CREATE SEQUENCE sequence_name

MINVALUE value

MAXVALUE value

START WITH value

INCREMENT BY value

CACHE value;

Step 2:

Bind it to the attribute you want to auto-increment.

Create table Test_table( Id NUMBER DEFAULT sequence_name.NEXTVAL)

CREATE TABLE STORE

(

StoreID nchar(10) Primary key,

StoreName varchar2(25),

City varchar2(25),

Country varchar2(25),

Phone number(10),

Fax number(10),

Email varchar2(50),

Contact varchar2(25),

CONSTRAINT check_country_value

CHECK (Country IN (\'Add\',\'7\',\'Country\',\'name\'))

);

/

CREATE SEQUENCE PurchaseItemID_seq

MINVALUE 500

START WITH 500

INCREMENT BY 5

/

CREATE TABLE PURCHASE_ITEM

(

PurchaseItemID number(10) DEFAULT PurchaseItemID_seq.NEXTVAL Primary Key,

StoreID varchar2(25),

PurchaseDate DATE,

Description varchar2(100),

Category varchar2(25),

PriceUSD number(10),

CONSTRAINT fk_storeId

    FOREIGN KEY (StoreID)

    REFERENCES STORE(StoreID)

ON DELETE RESTRICT ON UPDATE CASCADE

);

/

CREATE SEQUENCE ShipmentID_seq

MINVALUE 100

START WITH 100

INCREMENT BY 1

/

CREATE TABLE SHIPMENT

(

ShipmentID NUMBER DEFAULT ShipmentID_seq.NEXTVAL Primary Key,

ShipperID number,

ShipperInvoiceNumber number,

Origin varchar2(25),

Destination varchar2(25),

DepartureDate number,

ArrivalDate number,

);

/

CREATE TABLE SHIPMENT_ITEM

(

ShipmentID number, AUTOINCREMENT(100,1),

ShipmentItemID number,

PurchaseItemID number,

InsuredValue number DEFAULT(100),

);

/

CREATE TABLE SHIPPER

(

ShipperID number,

ShipperName VARCHAR2(25),

Phone number,

Fax number,

Email VARCHAR2 (50),

Contact VARCHAR2 (40),

);

/