-- Generado por Oracle SQL Developer Data Modeler 3.1.1.703
-- en: 2018-08-02 08:13:37 VET
-- sitio: Oracle Database 11g
-- tipo: Oracle Database 11g
CREATE TABLE ACCESS_TYPE
(
ID INTEGER NOT NULL ,
ACCESS_NAME VARCHAR2 ,
STATUS VARCHAR2 (1)
)
;
ALTER TABLE ACCESS_TYPE
ADD CONSTRAINT ACCESS_TYPE_PK PRIMARY KEY ( ID ) ;
CREATE TABLE ACCOUNT_BANK
(
ID INTEGER NOT NULL ,
ACCOUNT_NUMBER VARCHAR2 (25) ,
BANK_ID INTEGER NOT NULL ,
STATUS VARCHAR2 (1)
)
;
ALTER TABLE ACCOUNT_BANK
ADD CONSTRAINT ACCOUNT_BANK_PK PRIMARY KEY ( ID ) ;
CREATE TABLE BANK
(
ID INTEGER NOT NULL ,
BANK_NAME VARCHAR2
)
;
ALTER TABLE BANK
ADD CONSTRAINT BANK_PK PRIMARY KEY ( ID ) ;
CREATE TABLE CLIENT
(
PERSON_ID INTEGER NOT NULL ,
JOIN_DATE DATE ,
STATUS VARCHAR2 (1)
)
;
ALTER TABLE CLIENT
ADD CONSTRAINT CLIENT_PK PRIMARY KEY ( PERSON_ID ) ;
CREATE TABLE DETAIL
(
ID INTEGER NOT NULL ,
QTY INTEGER ,
PLAN_NAME_ID INTEGER NOT NULL ,
SPEED_ID INTEGER NOT NULL ,
SHAPED_SPEED_ID INTEGER NOT NULL ,
SERVICE_CONTRACT_ID INTEGER NOT NULL ,
STATUS VARCHAR2 (1)
)
;
ALTER TABLE DETAIL
ADD CONSTRAINT DETAIL_PK PRIMARY KEY ( ID ) ;
CREATE TABLE DETAIL_PAY
(
ID INTEGER NOT NULL ,
NUMBER_REFERENCE VARCHAR2 NOT NULL ,
AMOUNT_PAY NUMBER (10,2) ,
INVOICE_ID INTEGER NOT NULL ,
ACCOUNT_BANK_ID INTEGER NOT NULL ,
STATUS VARCHAR2 (1)
)
;
ALTER TABLE DETAIL_PAY
ADD CONSTRAINT DETAIL_PAY_PK PRIMARY KEY ( ID ) ;
CREATE TABLE DETAIL_TICKET
(
DATE_STATUS DATE NOT NULL ,
TICKET_ID INTEGER NOT NULL ,
STATUS_TICKET_ID INTEGER NOT NULL ,
STAFF_PERSON_ID INTEGER NOT NULL ,
SUMMARY VARCHAR2 ,
DETAIL_TICKET_TICKET_ID INTEGER ,
STATUSTICKET_ID INTEGER ,
ANCESTOR_ID DATE
)
;
ALTER TABLE DETAIL_TICKET
ADD CONSTRAINT DETAIL_TICKET_PK PRIMARY KEY ( TICKET_ID, STATUS_TICKET_ID, DATE_STATUS ) ;
CREATE TABLE INVOICE
(
ID INTEGER NOT NULL ,
DATE_INVOICE DATE NOT NULL ,
DATE_PAYLIMIT DATE ,
TOTAL_AMOUNT NUMBER (10,2) ,
CLIENT_PERSON_ID INTEGER NOT NULL ,
PAYMENT_WAY_ID INTEGER NOT NULL ,
SERVICE_CONTRACT_ID INTEGER NOT NULL ,
DEBT NUMBER (10,2)
)
;
ALTER TABLE INVOICE
ADD CONSTRAINT INVOICE_PK PRIMARY KEY ( ID ) ;
CREATE TABLE "LEVEL"
(
ID INTEGER NOT NULL ,
LEVEL_NAME VARCHAR2 NOT NULL ,
AMOUNT_LEVEL NUMBER (10,2) ,
ANCESTOR_ID INTEGER
)
;
ALTER TABLE "LEVEL"
ADD CONSTRAINT LEVEL_PK PRIMARY KEY ( ID ) ;
CREATE TABLE LOCATION
(
ID INTEGER NOT NULL ,
NAME_LOCATION VARCHAR2 ,
ID_ANCESTOR INTEGER
)
;
ALTER TABLE LOCATION
ADD CONSTRAINT LOCATION_PK PRIMARY KEY ( ID ) ;
CREATE TABLE PAYMENT_WAY
(
ID INTEGER NOT NULL ,
NAME VARCHAR2
)
;
ALTER TABLE PAYMENT_WAY
ADD CONSTRAINT PAYMENT_WAY_PK PRIMARY KEY ( ID ) ;
CREATE TABLE PERSON
(
ID INTEGER NOT NULL ,
NAME VARCHAR2 ,
LASTNAME VARCHAR2 NOT NULL ,
BIRTH_DATE DATE ,
TELEPHONE VARCHAR2 (50) ,
EMAIL VARCHAR2 (50) ,
ADDRESS VARCHAR2 ,
LOCATION_ID INTEGER NOT NULL ,
ID_ANCESTOR INTEGER ,
ZIP INTEGER ,
STATUS VARCHAR2 (1)
)
;
ALTER TABLE PERSON
ADD CONSTRAINT PERSON_PK PRIMARY KEY ( ID ) ;
CREATE TABLE PLAN_NAME
(
ID INTEGER NOT NULL ,
PLAN_NAME VARCHAR2 ,
QUOTA VARCHAR2 ,
ACCESS_TYPE_ID INTEGER NOT NULL ,
PRICE NUMBER (10,2) ,
STATUS VARCHAR2 (1)
)
;
ALTER TABLE PLAN_NAME
ADD CONSTRAINT PLAN_NAME_PK PRIMARY KEY ( ID ) ;
CREATE TABLE ROL
(
ID INTEGER NOT NULL ,
ROL_NAME VARCHAR2 ,
ANCESTOR_ID INTEGER
)
;
ALTER TABLE ROL
ADD CONSTRAINT ROL_PK PRIMARY KEY ( ID ) ;
CREATE TABLE SERVICE_CONTRACT
(
ID INTEGER NOT NULL ,
DATE_HIRESERV DATE ,
DATE_INSTALATION DATE ,
TIME_INSTALATION VARCHAR2 (8) ,
TOTAL_AMOUNT NUMBER (10,2) ,
CLIENT_ID INTEGER NOT NULL ,
STAFF_PERSON_ID INTEGER NOT NULL ,
LOCATION_ID INTEGER NOT NULL ,
ADDRESS_INSTALATION VARCHAR2 ,
MAX_MONTHDEBT INTEGER ,
STATUS VARCHAR2 (1)
)
;
ALTER TABLE SERVICE_CONTRACT
ADD CONSTRAINT SERVICE_CONTRACT_PK PRIMARY KEY ( ID ) ;
CREATE TABLE SHAPED_SPEED
(
ID INTEGER NOT NULL ,
RATE VARCHAR2 ,
STATUS VARCHAR2 (1)
)
;
ALTER TABLE SHAPED_SPEED
ADD CONSTRAINT SHAPED_SPEED_PK PRIMARY KEY ( ID ) ;
CREATE TABLE SPEED
(
ID INTEGER NOT NULL ,
SPEEPDESC VARCHAR2
)
;
ALTER TABLE SPEED
ADD CONSTRAINT SPEED_PK PRIMARY KEY ( ID ) ;
CREATE TABLE STAFF
(
PERSON_ID INTEGER NOT NULL ,
HIRE_DATE DATE ,
ROL_ID INTEGER NOT NULL ,
LEVEL_ID INTEGER NOT NULL ,
MENTOR_ID INTEGER ,
STATUS VARCHAR2 (1)
)
;
ALTER TABLE STAFF
ADD CONSTRAINT STAFF_PK PRIMARY KEY ( PERSON_ID ) ;
CREATE TABLE STATUS_TICKET
(
ID INTEGER NOT NULL ,
STATUS_NAME VARCHAR2
)
;
ALTER TABLE STATUS_TICKET
ADD CONSTRAINT STATUS_TICKET_PK PRIMARY KEY ( ID ) ;
CREATE TABLE TICKET
(
ID INTEGER NOT NULL ,
DATE_OPEN DATE NOT NULL ,
WAITTIME INTEGER ,
CLIENT_PERSON_ID INTEGER NOT NULL ,
DURATION_TIME INTEGER ,
SUMMARY VARCHAR2 (200) ,
STATUS VARCHAR2 (1) ,
SERVICE_CONTRACT_ID INTEGER NOT NULL
)
;
ALTER TABLE TICKET
ADD CONSTRAINT TICKET_PK PRIMARY KEY ( ID ) ;
ALTER TABLE PLAN_NAME
ADD CONSTRAINT ACCESSTYPE_FK1 FOREIGN KEY
(
ACCESS_TYPE_ID
)
REFERENCES ACCESS_TYPE
(
ID
)
;
ALTER TABLE DETAIL_PAY
ADD CONSTRAINT ACCOUNTBANK_FK1 FOREIGN KEY
(
ACCOUNT_BANK_ID
)
REFERENCES ACCOUNT_BANK
(
ID
)
;
ALTER TABLE ACCOUNT_BANK
ADD CONSTRAINT BANK_FK FOREIGN KEY
(
BANK_ID
)
REFERENCES BANK
(
ID
)
;
ALTER TABLE SERVICE_CONTRACT
ADD CONSTRAINT CLIENT_FK1 FOREIGN KEY
(
CLIENT_ID
)
REFERENCES CLIENT
(
PERSON_ID
)
;
ALTER TABLE INVOICE
ADD CONSTRAINT CLIENT_FK2 FOREIGN KEY
(
CLIENT_PERSON_ID
)
REFERENCES CLIENT
(
PERSON_ID
)
;
ALTER TABLE INVOICE
ADD CONSTRAINT CONTRACT_FK FOREIGN KEY
(
SERVICE_CONTRACT_ID
)
REFERENCES SERVICE_CONTRACT
(
ID
)
;
ALTER TABLE DETAIL
ADD CONSTRAINT CONTRACT_FK1 FOREIGN KEY
(
SERVICE_CONTRACT_ID
)
REFERENCES SERVICE_CONTRACT
(
ID
)
;
ALTER TABLE DETAIL_TICKET
ADD CONSTRAINT DETAILTICKET_FK FOREIGN KEY
(
DETAIL_TICKET_TICKET_ID,
STATUSTICKET_ID,
ANCESTOR_ID
)
REFERENCES DETAIL_TICKET
(
TICKET_ID,
STATUS_TICKET_ID,
DATE_STATUS
)
;
ALTER TABLE DETAIL_PAY
ADD CONSTRAINT INVOICE_FK1 FOREIGN KEY
(
INVOICE_ID
)
REFERENCES INVOICE
(
ID
)
;
ALTER TABLE "LEVEL"
ADD CONSTRAINT LEVEL_FK1 FOREIGN KEY
(
ANCESTOR_ID
)
REFERENCES "LEVEL"
(
ID
)
;
ALTER TABLE STAFF
ADD CONSTRAINT LEVEL_FK2 FOREIGN KEY
(
LEVEL_ID
)
REFERENCES "LEVEL"
(
ID
)
;
ALTER TABLE SERVICE_CONTRACT
ADD CONSTRAINT LOCATION_FK FOREIGN KEY
(
LOCATION_ID
)
REFERENCES LOCATION
(
ID
)
;
ALTER TABLE LOCATION
ADD CONSTRAINT LOCATION_FK1 FOREIGN KEY
(
ID_ANCESTOR
)
REFERENCES LOCATION
(
ID
)
;
ALTER TABLE PERSON
ADD CONSTRAINT LOCATION_FK2 FOREIGN KEY
(
LOCATION_ID
)
REFERENCES LOCATION
(
ID
)
;
ALTER TABLE INVOICE
ADD CONSTRAINT PAYMENTWAY_FK1 FOREIGN KEY
(
PAYMENT_WAY_ID
)
REFERENCES PAYMENT_WAY
(
ID
)
;
ALTER TABLE CLIENT
ADD CONSTRAINT PERSON_FK1 FOREIGN KEY
(
PERSON_ID
)
REFERENCES PERSON
(
ID
)
;
ALTER TABLE PERSON
ADD CONSTRAINT PERSON_FK2 FOREIGN KEY
(
ID_ANCESTOR
)
REFERENCES PERSON
(
ID
)
;
ALTER TABLE STAFF
ADD CONSTRAINT PERSON_FK3 FOREIGN KEY
(
PERSON_ID
)
REFERENCES PERSON
(
ID
)
;
ALTER TABLE DETAIL
ADD CONSTRAINT PLANNAME_FK1 FOREIGN KEY
(
PLAN_NAME_ID
)
REFERENCES PLAN_NAME
(
ID
)
;
ALTER TABLE ROL
ADD CONSTRAINT ROL_FK FOREIGN KEY
(
ANCESTOR_ID
)
REFERENCES ROL
(
ID
)
;
ALTER TABLE STAFF
ADD CONSTRAINT ROL_FK2 FOREIGN KEY
(
ROL_ID
)
REFERENCES ROL
(
ID
)
;
ALTER TABLE TICKET
ADD CONSTRAINT SERVICE_CONTRACT_FK FOREIGN KEY
(
SERVICE_CONTRACT_ID
)
REFERENCES SERVICE_CONTRACT
(
ID
)
;
ALTER TABLE DETAIL
ADD CONSTRAINT SHAPEDSPEED_FK1 FOREIGN KEY
(
SHAPED_SPEED_ID
)
REFERENCES SHAPED_SPEED
(
ID
)
;
ALTER TABLE DETAIL
ADD CONSTRAINT SPEED_FK1 FOREIGN KEY
(
SPEED_ID
)
REFERENCES SPEED
(
ID
)
;
ALTER TABLE STAFF
ADD CONSTRAINT STAFF_FK1 FOREIGN KEY
(
MENTOR_ID
)
REFERENCES STAFF
(
PERSON_ID
)
;
ALTER TABLE DETAIL_TICKET
ADD CONSTRAINT STAFF_FK3 FOREIGN KEY
(
STAFF_PERSON_ID
)
REFERENCES STAFF
(
PERSON_ID
)
;
ALTER TABLE SERVICE_CONTRACT
ADD CONSTRAINT STAFF_FK4 FOREIGN KEY
(
STAFF_PERSON_ID
)
REFERENCES STAFF
(
PERSON_ID
)
;
ALTER TABLE DETAIL_TICKET
ADD CONSTRAINT STATUSTICKET_FK1 FOREIGN KEY
(
STATUS_TICKET_ID
)
REFERENCES STATUS_TICKET
(
ID
)
;
ALTER TABLE TICKET
ADD CONSTRAINT TICKETCLIENT_FK3 FOREIGN KEY
(
CLIENT_PERSON_ID
)
REFERENCES CLIENT
(
PERSON_ID
)
;
ALTER TABLE DETAIL_TICKET
ADD CONSTRAINT TICKET_FK1 FOREIGN KEY
(
TICKET_ID
)
REFERENCES TICKET
(
ID
)
;
-- Informe de Resumen de Oracle SQL Developer Data Modeler:
--
-- CREATE TABLE 20
-- CREATE INDEX 0
-- ALTER TABLE 50
-- CREATE VIEW 0
-- CREATE PACKAGE 0
-- CREATE PACKAGE BODY 0
-- CREATE PROCEDURE 0
-- CREATE FUNCTION 0
-- CREATE TRIGGER 0
-- ALTER TRIGGER 0
-- CREATE STRUCTURED TYPE 0
-- CREATE COLLECTION TYPE 0
-- CREATE CLUSTER 0
-- CREATE CONTEXT 0
-- CREATE DATABASE 0
-- CREATE DIMENSION 0
-- CREATE DIRECTORY 0
-- CREATE DISK GROUP 0
-- CREATE ROLE 0
-- CREATE ROLLBACK SEGMENT 0
-- CREATE SEQUENCE 0
-- CREATE MATERIALIZED VIEW 0
-- CREATE SYNONYM 0
-- CREATE TABLESPACE 0
-- CREATE USER 0
--
-- DROP TABLESPACE 0
-- DROP DATABASE 0
--
-- ERRORS 0
-- WARNINGS 0
Crear cuenta
Comentarios sobre la versión: 1 (0)
No hay comentarios