hotel-bndd2/ddl-lista-10-11-25.sql at main · gmulato/hotel-bndd2 · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
DROP TABLE AMIGOSECRETO;
DROP TABLE LISTAPRESENTE;
DROP TABLE PRESENTE;
DROP TABLE AMIGO;

CREATE TABLE AMIGO (
    AID        INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ANOME      VARCHAR2(100) NOT NULL,
    SEXO       CHAR(1),
    IDADE      INTEGER
);

CREATE TABLE PRESENTE (
    PID          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    PDESCRICAO   VARCHAR2(200) NOT NULL,
    PVALOR       NUMBER(10,2)
);

CREATE TABLE LISTAPRESENTE (
    AID         INTEGER NOT NULL,
    PID         INTEGER NOT NULL,
    PREFERENCIA INTEGER,

    CONSTRAINT PK_LISTAPRESENTE PRIMARY KEY (AID, PID),
    CONSTRAINT FK_LISTAPRESENTE_AMIGO FOREIGN KEY (AID)
        REFERENCES AMIGO (AID),
    CONSTRAINT FK_LISTAPRESENTE_PRESENTE FOREIGN KEY (PID)
        REFERENCES PRESENTE (PID)
);

CREATE TABLE AMIGOSECRETO (
    AID           INTEGER NOT NULL,
    AID_AMIGO     INTEGER NOT NULL,
    PID_RECEBIDO  INTEGER NOT NULL,

    CONSTRAINT PK_AMIGOSECRETO PRIMARY KEY (AID),

    CONSTRAINT FK_AMIGOSECRETO_ORIGEM FOREIGN KEY (AID)
        REFERENCES AMIGO (AID),

    CONSTRAINT FK_AMIGOSECRETO_DEST FOREIGN KEY (AID_AMIGO)
        REFERENCES AMIGO (AID),

    CONSTRAINT FK_AMIGOSECRETO_PRESENTE FOREIGN KEY (PID_RECEBIDO)
        REFERENCES PRESENTE (PID)
);