ver 2

DROP TABLE CallTextCharge CASCADE CONSTRAINTS PURGE;

DROP TABLE OrderInStore CASCADE CONSTRAINTS PURGE;

DROP TABLE DataCharge CASCADE CONSTRAINTS PURGE;

DROP TABLE CustomerAccount CASCADE CONSTRAINTS PURGE;

DROP TABLE DeviceReturns CASCADE CONSTRAINTS PURGE;

DROP TABLE PhoneStore CASCADE CONSTRAINTS PURGE;

DROP TABLE OrderOnline CASCADE CONSTRAINTS PURGE;

DROP TABLE Phone CASCADE CONSTRAINTS PURGE;

DROP TABLE Customer CASCADE CONSTRAINTS PURGE;

CREATE TABLE Customer
(
    customer_no          CHAR(18) NOT NULL ,
    name                 VARCHAR2(20) NULL ,
    address              VARCHAR2(20) NULL ,
    email                VARCHAR2(20) NULL ,
CONSTRAINT  XPKCustomer PRIMARY KEY (customer_no)
);

CREATE TABLE Phone
(
    serial_no            VARCHAR2(20) NOT NULL ,
    customer_no          CHAR(18) NOT NULL ,
    manufacturer         CHAR(18) NULL ,
    model                VARCHAR2(20) NULL ,
    description          VARCHAR2(20) NULL ,
    credit_offer         VARCHAR2(20) NULL ,
CONSTRAINT  XPKPhone PRIMARY KEY (serial_no,customer_no),
CONSTRAINT R_37 FOREIGN KEY (customer_no) REFERENCES Customer (customer_no)
);

CREATE TABLE OrderOnline
(
    customer_no          CHAR(18) NOT NULL ,
    serial_no            VARCHAR2(20) NOT NULL ,
    dispatch_date        DATE NULL ,
    purchase_date        VARCHAR2(20) NULL ,
    backroom_staff_member CHAR(18) NULL ,
CONSTRAINT  XPKOrderOnline PRIMARY KEY (customer_no,serial_no),
CONSTRAINT R_20 FOREIGN KEY (serial_no, customer_no) REFERENCES Phone (serial_no, customer_no),
CONSTRAINT R_22 FOREIGN KEY (customer_no) REFERENCES Customer (customer_no)
);

CREATE TABLE PhoneStore
(
    store_no             VARCHAR2(20) NOT NULL ,
    address              VARCHAR2(20) NULL ,
    phone                VARCHAR2(20) NULL ,
CONSTRAINT  XPKStore PRIMARY KEY (store_no)
);

CREATE TABLE DeviceReturns
(
    store_no             VARCHAR2(20) NOT NULL ,
    serial_no            VARCHAR2(20) NOT NULL ,
    customer_no          VARCHAR2(20) NOT NULL ,
    fault_description    VARCHAR2(40) NULL ,
    date_returned        DATE NULL ,
CONSTRAINT  XPKReturns PRIMARY KEY (store_no,serial_no,customer_no),
CONSTRAINT R_13 FOREIGN KEY (store_no) REFERENCES PhoneStore (store_no),
CONSTRAINT R_14 FOREIGN KEY (serial_no, customer_no) REFERENCES Phone (serial_no, customer_no)
);

CREATE TABLE CustomerAccount
(
    customer_no          CHAR(18) NOT NULL ,
    phone_no             VARCHAR2(20) NULL ,
    rental_charge        FLOAT NULL ,
    contract_length      INTEGER NULL ,
    billing_period       INTEGER NULL ,
CONSTRAINT  XPKAccount PRIMARY KEY (customer_no),
CONSTRAINT R_43 FOREIGN KEY (customer_no) REFERENCES Customer (customer_no)
);

CREATE TABLE DataCharge
(
    customer_no          CHAR(18) NOT NULL ,
    cost_per_unit        FLOAT NULL ,
    roaming_charge       FLOAT NULL ,
CONSTRAINT  XPKData PRIMARY KEY (customer_no),
CONSTRAINT R_10 FOREIGN KEY (customer_no) REFERENCES CustomerAccount (customer_no)
);

CREATE TABLE OrderInStore
(
    store_no             VARCHAR2(20) NOT NULL ,
    customer_no          CHAR(18) NOT NULL ,
    serial_no            VARCHAR2(20) NOT NULL ,
    purchase_date        DATE NULL ,
    price                FLOAT NULL ,
    credit               FLOAT NULL ,
    staff_member         VARCHAR2(20) NULL ,
CONSTRAINT  XPKOrderInStore PRIMARY KEY (store_no,customer_no,serial_no),
CONSTRAINT R_39 FOREIGN KEY (store_no) REFERENCES PhoneStore (store_no),
CONSTRAINT R_40 FOREIGN KEY (customer_no) REFERENCES Customer (customer_no),
CONSTRAINT R_42 FOREIGN KEY (serial_no, customer_no) REFERENCES Phone (serial_no, customer_no)
);

CREATE TABLE CallTextCharge
(
    contact_number       VARCHAR2(20) NOT NULL ,
    customer_no          CHAR(18) NOT NULL ,
    provider             VARCHAR2(20) NULL ,
    duration             FLOAT NULL ,
    cost_per_unit        FLOAT NULL ,
    roaming_charge       FLOAT NULL ,
CONSTRAINT  XPKCallsAndTexts PRIMARY KEY (contact_number,customer_no),
CONSTRAINT R_11 FOREIGN KEY (customer_no) REFERENCES CustomerAccount (customer_no)
);