【SQL开源代码栏目提醒】:网学会员为广大网友收集整理了,schema.sql,希望对大家有所帮助!
-- Create the table to use for simulating SEQUENCEs
CREATE TABLE hw_seq_tb
( sequence_name VARCHAR(32) NOT NULL,
next_number NUMERIC(15) NOT NULL);
ALTER TABLE hw_seq_tb ADD CONSTRAINT hw_seq_pk PRIMARY KEY (sequence_name);
-- populate table with names of logical sequences.
INSERT INTO hw_seq_tb (sequence_name, next_number)
VALUES ('address', -999999999999999) ;
INSERT INTO hw_seq_tb(sequence_name, next_number)
VALUES ('catalog', -999999999999999) ;
INSERT INTO hw_seq_tb(sequence_name, next_number)
VALUES ('catalog item', -999999999999999) ;
INSERT INTO hw_seq_tb(sequence_name, next_number)
VALUES ('credit card', -999999999999999) ;
INSERT INTO hw_seq_tb(sequence_name, next_number)
VALUES ('entity', -999999999999999) ;
INSERT INTO hw_seq_tb(sequence_name, next_number)
VALUES ('item', -999999999999999) ;
INSERT INTO hw_seq_tb(sequence_name, next_number)
VALUES ('price list', -999999999999999) ;
INSERT INTO hw_seq_tb(sequence_name, next_number)
VALUES ('relationship description', -999999999999999) ;
INSERT INTO hw_seq_tb(sequence_name, next_number)
VALUES ('role', -999999999999990) ;
INSERT INTO hw_seq_tb(sequence_name, next_number)
VALUES ('sale', -999999999999999) ;
INSERT INTO hw_seq_tb(sequence_name, next_number)
VALUES ('sku map', -999999999999999) ;
COMMIT;
CREATE TABLE hw_entity_tb
(
entity_id NUMERIC(19) NOT NULL,
rel_desc VARCHAR(100),
start_date DATE NOT NULL,
end_date DATE NOT NULL,
constraint hw_rel_ship_ety_pk PRIMARY KEY (entity_id)
);
-- COMMENT ON TABLE hw_entity_tb IS
-- 'This table is the super class of all physical and cyber entities.';
-- COMMENT ON COLUMN hw_entity_tb.entity_id IS
-- 'Internally generated object id for the entity represented by this row.';
-- This table contains properties for the relationship_entity class
CREATE TABLE hw_entity_properties_tb
(
property_name VARCHAR(50) NOT NULL,
property_value BIT VARYING(8000) NOT NULL,
entity_id NUMERIC(19) NOT NULL,
CONSTRAINT hw_rel_ship_prop_PK PRIMARY KEY (entity_id, property_name),
CONSTRAINT hw_rel_ship_prop_fk foreign key (entity_id)
REFERENCES hw_entity_tb (entity_id)
);
-- This table has role information of an entity
CREATE TABLE hw_role_tb
(
role_id NUMERIC(19) NOT NULL,
entity_id NUMERIC(19),
persister_interface VARCHAR(80),
CONSTRAINT hw_role_pk PRIMARY KEY (role_id),
CONSTRAINT hw_role_rel_fk FOREIGN KEY (entity_id)
REFERENCES hw_entity_tb (entity_id)
);
-- This table has customer information.
CREATE TABLE hw_customer_tb
(
role_id NUMERIC(19) NOT NULL,
CONSTRAINT hw_customer_pk PRIMARY KEY (role_id),
CONSTRAINT hw_role_role_fk FOREIGN KEY (role_id)
REFERENCES hw_role_tb (role_id)
);
-- This table has organization entities responsible for accounts
CREATE TABLE hw_responsible_organization_tb
(
role_id NUMERIC(19) NOT NULL,
CONSTRAINT hw_org_ety_resp_acct_pk PRIMARY KEY (role_id),
CONSTRAINT hw_org_ety_resp_acct_role_fk FOREIGN KEY (role_id)
REFERENCES hw_role_tb (role_id)
);
-- This table has cyber entity information
CREATE TABLE hw_cyber_entity_tb
(
entity_id NUMERIC(19) NOT NULL,
-- constraint hw_cyber_entity_pk PRIMARY KEY (cyber_entity_id),
CONSTRAINT hw_cyber_entity_pk PRIMARY KEY (entity_id),
CONSTRAINT hw_cyber_entity_rel_fk FOREIGN KEY (entity_id)
REFERENCES hw_entity_tb (entity_id)
);
-- This table has email address of persons and organization
CREATE TABLE hw_email_address_tb
(
entity_id NUMERIC(19) NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
email_type VARCHAR(50) NOT NULL,
email_address VARCHAR(50),
CONSTRAINT hw_mail_addr_type_pk
PRIMARY KEY (entity_id,email_type,start_date,end_date),
CONSTRAINT hw_mail_addr_type_rel_fk
FOREIGN KEY (entity_id)
REFERENCES hw_entity_tb (entity_id)
);
-- This table stores address information.
CREATE TABLE hw_addr_tb
(
address_id NUMERIC(19) NOT NULL,
line_1 VARCHAR(50) not null,
line_2 VARCHAR(50),
line_3 VARCHAR(50),
line_4 VARCHAR(50),
city VARCHAR(50) not null,
state VARCHAR(50) not null,
zipcode VARCHAR(10) not null,
country VARCHAR(50) not null,
constraint hw_addr_pk PRIMARY KEY (address_id)
);
CREATE TABLE hw_addr_type_tb
(
address_type VARCHAR(50) NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
address_id NUMERIC(19) NOT NULL,
entity_id NUMERIC(19) NOT NULL,
CONSTRAINT hw_addr_type_pk
PRIMARY KEY (entity_id, address_type,
start_date, end_date, address