complexdatabase.ddl

此程序都是企业级 的数据库开发程序 全面揭示了JAVA对数据库的操作

-----------------------------------------------------------------------
-- Drop old table and sequence
-----------------------------------------------------------------------

drop table RECORDINGS;

drop sequence RECORDINGS_SEQ;


-----------------------------------------------------------------------
-- Create new table CUSTOMER_TYPE.
-----------------------------------------------------------------------

create table CUSTOMER_TYPE (
        CUSTOMER_TYPE_ID INT not null,
        DESCRIPTION VARCHAR2(50) not null, 
        constraint CUSTOMER_TYPE_PK primary key (CUSTOMER_TYPE_ID) ); 


-----------------------------------------------------------------------
-- Create new table CUSTOMER_TRACKING_TYPE.
-----------------------------------------------------------------------

create table CUSTOMER_TRACKING_TYPE (
        TRACKING_TYPE_ID INT not null,
        DESCRIPTION VARCHAR2(50) not null, 
        constraint CUSTOMER_TRACKING_TYPE_PK primary key (TRACKING_TYPE_ID) ); 


-----------------------------------------------------------------------
-- Create new table CUSTOMERS.
-----------------------------------------------------------------------

create table CUSTOMERS (
        CUSTOMER_ID INT not null,
        NICKNAME VARCHAR2(20) not null,
        EMAIL_ADDRESS VARCHAR2(50) not null,
        CUSTOMER_TYPE_ID INT not null,
        NAME VARCHAR2(50) null,
        ADDRESS VARCHAR2(50) null,
        CITY VARCHAR2(20) null,
        STATE CHAR(2) null,
        ZIPCODE CHAR(10) null,
        COMPANY_NAME VARCHAR2(50) null,
        TAX_EXEMPT_ID VARCHAR2(50) null,
        VERSION INT not null, 
        constraint CUSTOMERS_PK primary key (CUSTOMER_ID),
        constraint CUSTOMER_TYPE_CUSTOMERS_FK1 foreign key (CUSTOMER_TYPE_ID)
            references CUSTOMER_TYPE (CUSTOMER_TYPE_ID) ); 


-----------------------------------------------------------------------
-- Create new table RECORDINGS.
-----------------------------------------------------------------------

create table RECORDINGS (
        RECORDING_ID INT not null,
        RECORDING_TITLE VARCHAR2(50) not null,
        RECORDING_ARTIST VARCHAR2(50) not null,
        CATALOG_NUMBER VARCHAR2(20) not null,
        LIST_PRICE DECIMAL(7,2) not null,
        VERSION INT not null, 
        constraint RECORDINGS_PK primary key (RECORDING_ID) ); 


-----------------------------------------------------------------------
-- Create new table CUSTOMER_TRACKED_RECORDINGS.
-----------------------------------------------------------------------

create table CUSTOMER_TRACKED_RECORDINGS (
        RECORDING_ID INT not null,
        TRACKING_TYPE_ID INT not null,
        CUSTOMER_ID INT not null,
        DATE_TRACKED DATE not null,
        VERSION INT not null, 
        constraint CUSTOMER_TRACKED_RECORDINGS_PK 
        primary key (RECORDING_ID, CUSTOMER_ID, TRACKING_TYPE_ID),
    constraint RECORDINGS_ID_FK1 foreign key (RECORDING_ID)
            references RECORDINGS (RECORDING_ID), 
        constraint CUSTOMER_TRACKING_ID_FK1 foreign key (TRACKING_TYPE_ID)
        references CUSTOMER_TRACKING_TYPE (TRACKING_TYPE_ID),
    constraint CUSTOMERS_ID_FK1 foreign key (CUSTOMER_ID)
        references CUSTOMERS (CUSTOMER_ID) ); 


-----------------------------------------------------------------------
-- Create new sequences.
-----------------------------------------------------------------------

create sequence RECORDINGS_SEQ;

create sequence CUSTOMERS_SEQ;


-----------------------------------------------------------------------
-- Insert reference data into static tables.
-----------------------------------------------------------------------

-- Populate CUSTOMER_TYPE table
insert into CUSTOMER_TYPE
     values (1, 'Generic Customer');
         
insert into CUSTOMER_TYPE
     values (2, 'Frequent Buyer');
         
insert into CUSTOMER_TYPE
     values (3, 'Corporate Customer');
         
insert into CUSTOMER_TYPE
     values (4, 'Non-Profit Corporation');
         
-- Populate CUSTOMER_TRACKING_TYPE table
insert into CUSTOMER_TRACKING_TYPE
     values (1, 'Customer Purchase');

insert into CUSTOMER_TRACKING_TYPE
     values (2, 'Customer Interest');

commit;