-- Generated by Oracle SQL Developer Data Modeler 18.3.0.268.1156
--   at:        2019-12-15 13:23:09 EET
--   site:      Oracle Database 11g
--   type:      Oracle Database 11g



CREATE TABLE albume (
    id_album                  NUMBER(3) NOT NULL,
    nume_album                VARCHAR2(30),
    numar_aprecieri           NUMBER(10),
    case_de_discuri_id_casa   NUMBER(2) NOT NULL,
    genuri_gen_muzical        VARCHAR2(20) NOT NULL
);

ALTER TABLE albume
    ADD CHECK ( length(nume_album) >= 2 );

ALTER TABLE albume ADD CONSTRAINT albume_pk PRIMARY KEY ( id_album );

ALTER TABLE albume ADD CONSTRAINT albume_nume_album_un UNIQUE ( nume_album );

CREATE TABLE albume_artisti (
    artisti_nume_artist   VARCHAR2(30) NOT NULL,
    albume_id_album       NUMBER(3) NOT NULL
);

ALTER TABLE albume_artisti ADD CONSTRAINT albume_artisti_pk PRIMARY KEY ( artisti_nume_artist,
                                                                          albume_id_album );

CREATE TABLE albume_clienti (
    clienti_cnp       VARCHAR2(13) NOT NULL,
    albume_id_album   NUMBER(3) NOT NULL
);

ALTER TABLE albume_clienti ADD CONSTRAINT albume_clienti_pk PRIMARY KEY ( clienti_cnp,
                                                                          albume_id_album );

CREATE TABLE artisti (
    nume_artist   VARCHAR2(30) NOT NULL,
    rank          NUMBER(3)
);

ALTER TABLE artisti
    ADD CHECK ( length(nume_artist) >= 3 );

ALTER TABLE artisti ADD CONSTRAINT artisti_pk PRIMARY KEY ( nume_artist );

CREATE TABLE case_de_discuri (
    id_casa           NUMBER(2) NOT NULL,
    nume_casa         VARCHAR2(30) NOT NULL,
    data_infiintare   VARCHAR2(10)
);

ALTER TABLE case_de_discuri
    ADD CHECK ( length(nume_casa) >= 2 );

ALTER TABLE case_de_discuri ADD CONSTRAINT case_de_discuri_pk PRIMARY KEY ( id_casa );

ALTER TABLE case_de_discuri ADD CONSTRAINT case_de_discuri_nume_casa_un UNIQUE ( nume_casa );

CREATE TABLE clienti (
    cnp           VARCHAR2(13) NOT NULL,
    nume_client   VARCHAR2(30) NOT NULL,
    numar_card    NUMBER(16),
    adresa        VARCHAR2(30)
);

ALTER TABLE clienti ADD CHECK ( length(cnp) = 13 );

ALTER TABLE clienti
    ADD CHECK ( length(nume_client) >= 6 );

ALTER TABLE clienti ADD CONSTRAINT clienti_pk PRIMARY KEY ( cnp );

ALTER TABLE clienti ADD CONSTRAINT clienti_numar_card_un UNIQUE ( numar_card );

CREATE TABLE detalii_albume (
    albume_id_album   NUMBER(3) NOT NULL,
    pret              NUMBER(3) NOT NULL,
    data_aparitie     VARCHAR2(10),
    cantitate         NUMBER(3)
);

ALTER TABLE detalii_albume ADD CONSTRAINT detalii_albume_pk PRIMARY KEY ( albume_id_album );

CREATE TABLE genuri (
    gen_muzical   VARCHAR2(20) NOT NULL
);

ALTER TABLE genuri ADD CONSTRAINT genuri_pk PRIMARY KEY ( gen_muzical );

ALTER TABLE albume_artisti
    ADD CONSTRAINT albume_artisti_albume_fk FOREIGN KEY ( albume_id_album )
        REFERENCES albume ( id_album );

ALTER TABLE albume_artisti
    ADD CONSTRAINT albume_artisti_artisti_fk FOREIGN KEY ( artisti_nume_artist )
        REFERENCES artisti ( nume_artist );

ALTER TABLE albume
    ADD CONSTRAINT albume_case_de_discuri_fk FOREIGN KEY ( case_de_discuri_id_casa )
        REFERENCES case_de_discuri ( id_casa );

ALTER TABLE albume_clienti
    ADD CONSTRAINT albume_clienti_albume_fk FOREIGN KEY ( albume_id_album )
        REFERENCES albume ( id_album );

ALTER TABLE albume_clienti
    ADD CONSTRAINT albume_clienti_clienti_fk FOREIGN KEY ( clienti_cnp )
        REFERENCES clienti ( cnp );

ALTER TABLE albume
    ADD CONSTRAINT albume_genuri_fk FOREIGN KEY ( genuri_gen_muzical )
        REFERENCES genuri ( gen_muzical );

ALTER TABLE detalii_albume
    ADD CONSTRAINT detalii_albume_albume_fk FOREIGN KEY ( albume_id_album )
        REFERENCES albume ( id_album );

ALTER TABLE albume_artisti
    ADD CONSTRAINT albume_artisti_albume_fk FOREIGN KEY ( albume_id_album )
        REFERENCES albume ( id_album );

ALTER TABLE albume_artisti
    ADD CONSTRAINT albume_artisti_artisti_fk FOREIGN KEY ( artisti_nume_artist )
        REFERENCES artisti ( nume_artist );

ALTER TABLE albume
    ADD CONSTRAINT albume_case_de_discuri_fk FOREIGN KEY ( case_de_discuri_id_casa )
        REFERENCES case_de_discuri ( id_casa );

ALTER TABLE albume_clienti
    ADD CONSTRAINT albume_clienti_albume_fk FOREIGN KEY ( albume_id_album )
        REFERENCES albume ( id_album );

ALTER TABLE albume_clienti
    ADD CONSTRAINT albume_clienti_clienti_fk FOREIGN KEY ( clienti_cnp )
        REFERENCES clienti ( cnp );

ALTER TABLE albume
    ADD CONSTRAINT albume_genuri_fk FOREIGN KEY ( genuri_gen_muzical )
        REFERENCES genuri ( gen_muzical );

ALTER TABLE detalii_albume
    ADD CONSTRAINT detalii_albume_albume_fk FOREIGN KEY ( albume_id_album )
        REFERENCES albume ( id_album );

CREATE SEQUENCE albume_id_album_seq START WITH 1 NOCACHE ORDER;

CREATE OR REPLACE TRIGGER albume_id_album_trg BEFORE
    INSERT ON albume
    FOR EACH ROW
    WHEN ( new.id_album IS NULL )
BEGIN
    :new.id_album := albume_id_album_seq.nextval;
END;
/

CREATE SEQUENCE case_de_discuri_id_casa_seq START WITH 1 NOCACHE ORDER;

CREATE OR REPLACE TRIGGER case_de_discuri_id_casa_trg BEFORE
    INSERT ON case_de_discuri
    FOR EACH ROW
    WHEN ( new.id_casa IS NULL )
BEGIN
    :new.id_casa := case_de_discuri_id_casa_seq.nextval;
END;
/



-- Oracle SQL Developer Data Modeler Summary Report:
--
-- CREATE TABLE                             8
-- CREATE INDEX                             0
-- ALTER TABLE                             30
-- CREATE VIEW                              0
-- ALTER VIEW                               0
-- CREATE PACKAGE                           0
-- CREATE PACKAGE BODY                      0
-- CREATE PROCEDURE                         0
-- CREATE FUNCTION                          0
-- CREATE TRIGGER                           2
-- ALTER TRIGGER                            0
-- CREATE COLLECTION TYPE                   0
-- CREATE STRUCTURED TYPE                   0
-- CREATE STRUCTURED TYPE BODY              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                          2
-- CREATE MATERIALIZED VIEW                 0
-- CREATE MATERIALIZED VIEW LOG             0
-- CREATE SYNONYM                           0
-- CREATE TABLESPACE                        0
-- CREATE USER                              0
--
-- DROP TABLESPACE                          0
-- DROP DATABASE                            0
--
-- REDACTION POLICY                         0
--
-- ORDS DROP SCHEMA                         0
-- ORDS ENABLE SCHEMA                       0
-- ORDS ENABLE OBJECT                       0
--
-- ERRORS                                   0
-- WARNINGS                                 0