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_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
    ADD CONSTRAINT albume_case_de_discuri_fk FOREIGN KEY ( case_de_discuri_id_casa )
        REFERENCES case_de_discuri ( id_casa );




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;
/