Разработка базы данных из предметной области магазин, страница 4

     REFERENCES objects (object_id)  ON DELETE CASCADE

);

CREATE TABLE attributes (

           attr_id NUMBER(20,0) NOT NULL ,

           object_type_id NUMBER(20,0) NOT NULL ,

           name VARCHAR2(100),

           CONSTRAINT attributes_pk PRIMARY KEY (attr_id),

           CONSTRAINT attributes_object_types_fk FOREIGN KEY (object_type_id)

           REFERENCES object_types (object_type_id)

);

CREATE TABLE params (

           object_id NUMBER(20,0) NOT NULL ,

           attr_id NUMBER(20,0) NOT NULL ,

           text_value VARCHAR2(1000),

           number_value NUMBER(20,5),

           date_value DATE,

           CONSTRAINT params_objects_fk FOREIGN KEY (object_id)

           REFERENCES objects (object_id),

           CONSTRAINT params_attributes_fk FOREIGN KEY (attr_id)

           REFERENCES attributes (attr_id)

);

CREATE INDEX xif1_params ON params (object_id);

commit;

7.2 Заполнение таблиц метамодели

delete from params;

delete from objects;

delete from attributes;

delete from object_types;

/* Object_types (object_type_id, name, description) */

INSERT INTO object_types VALUES (1, 'worker',  'Worker');

INSERT INTO object_types VALUES (2, 'client',  'Client');

INSERT INTO object_types VALUES (3, 'product', 'Product');

INSERT INTO object_types VALUES (4, 'sell',    'Sell');

/* Attributes (attr_id, object_type_id, name) */

INSERT INTO attributes VALUES (1, 2, 'clientAddress');

INSERT INTO attributes VALUES (2, 2, 'clientPhone');

INSERT INTO attributes VALUES (3, 3, 'price');

INSERT INTO attributes VALUES (4, 3, 'quantity');

INSERT INTO attributes VALUES (5, 4, 'workerID');

INSERT INTO attributes VALUES (6, 4, 'clientID');

INSERT INTO attributes VALUES (7, 4, 'sellQuantity');

INSERT INTO attributes VALUES (8, 4, 'sellDate');

/* Objects (object_id, parent_id, object_type_id, name) */

/* Worker*/

INSERT INTO objects VALUES (1,NULL ,1, 'w1');

INSERT INTO objects VALUES (2,NULL ,1, 'w2');

INSERT INTO objects VALUES (3,NULL ,1, 'w3');

INSERT INTO objects VALUES (4,NULL ,1, 'w4');

INSERT INTO objects VALUES (5,NULL ,1, 'w5');

INSERT INTO objects VALUES (6,NULL ,1, 'w6');

INSERT INTO objects VALUES (7,NULL ,1, 'w7');

/* Client*/

INSERT INTO objects VALUES (101,NULL ,2, 'Bob1');

INSERT INTO objects VALUES (102,NULL ,2, 'Bob2');

INSERT INTO objects VALUES (103,NULL ,2, 'Bob3');

INSERT INTO objects VALUES (104,NULL ,2, 'Bob4');

INSERT INTO objects VALUES (105,NULL ,2, 'Bob5');

INSERT INTO objects VALUES (106,NULL ,2, 'Bob6');

INSERT INTO objects VALUES (107,NULL ,2, 'Bob7');

INSERT INTO objects VALUES (108,NULL ,2, 'Bob8');

INSERT INTO objects VALUES (109,NULL ,2, 'Bob9');

/* Product */

INSERT INTO objects VALUES (10001,NULL ,3, 'product1');

INSERT INTO objects VALUES (10002,NULL ,3, 'product2');

INSERT INTO objects VALUES (10003,NULL ,3, 'product3');

INSERT INTO objects VALUES (10004,NULL ,3, 'product4');

INSERT INTO objects VALUES (10005,NULL ,3, 'product5');

INSERT INTO objects VALUES (10006,NULL ,3, 'product6');

INSERT INTO objects VALUES (10007,NULL ,3, 'product7');

INSERT INTO objects VALUES (10008,NULL ,3, 'product8');

INSERT INTO objects VALUES (10009,NULL ,3, 'product9');

/* Sell*/

INSERT INTO objects VALUES (1000001,10001 ,4, '1');

INSERT INTO objects VALUES (1000002,10002 ,4, '2');

INSERT INTO objects VALUES (1000003,10004 ,4, '3');

INSERT INTO objects VALUES (1000004,10006 ,4, '4');

INSERT INTO objects VALUES (1000005,10005 ,4, '5');

INSERT INTO objects VALUES (1000006,10005 ,4, '6');

INSERT INTO objects VALUES (1000007,10007 ,4, '7');

INSERT INTO objects VALUES (1000008,10003 ,4, '8');

INSERT INTO objects VALUES (1000009,10009 ,4, '9');

INSERT INTO objects VALUES (1000010,10008 ,4, '10');

INSERT INTO objects VALUES (1000011,10006 ,4, '11');

INSERT INTO objects VALUES (1000012,10008 ,4, '12');

INSERT INTO objects VALUES (1000013,10001 ,4, '13');

INSERT INTO objects VALUES (1000014,10001 ,4, '14');

INSERT INTO objects VALUES (1000015,10003 ,4, '15');