script_sql_bd.sql

CREATE TABLE users (
    user_id  int AUTO_INCREMENT,
    username     text,
    user_password text,
    firstname    text,
    lastname     text,
    editor   boolean,
    login    boolean,
    last_login datetime,
    PRIMARY KEY(user_id)
);

CREATE TABLE music (
    nmusic       int AUTO_INCREMENT,
    name         text,
    day_of_creation date,
    lyrics       text,
    artist_nartist   int NOT NULL,
    users_user_id    int NOT NULL,
    PRIMARY KEY(nmusic)
);

CREATE TABLE artist (
    nartist  int AUTO_INCREMENT,
    name         text,
    date_of_birth date,
    description  text,
    PRIMARY KEY(nartist)
);

CREATE TABLE album (
    nalbum       int AUTO_INCREMENT,
    nmusic       int,
    nartist  boolean,
    day_of_creation date,
    description  text,
    editor       text,
    artist_nartist   int NOT NULL,
    PRIMARY KEY(nalbum)
);

CREATE TABLE critique (
    text         text,
    rating   smallint,
    album_nalbum     int,
    users_user_id int,
    PRIMARY KEY(album_nalbum,users_user_id)
);

CREATE TABLE band (
    artist_nartist int,
    PRIMARY KEY(artist_nartist)
);

CREATE TABLE singer (
    artist_nartist int,
    PRIMARY KEY(artist_nartist)
);

CREATE TABLE composer (
    music_nmusic     int NOT NULL,
    artist_nartist int,
    PRIMARY KEY(artist_nartist)
);

CREATE TABLE shared (
    id_shareduser int AUTO_INCREMENT,
    users_user_id int,
    PRIMARY KEY(id_shareduser,users_user_id)
);

CREATE TABLE playlist (
    nplaylist    int AUTO_INCREMENT,
    nmusic   int,
    private  boolean,
    users_user_id int,
    PRIMARY KEY(nplaylist,nmusic,users_user_id),
    UNIQUE(nmusic)
);

CREATE TABLE url (
    url      text,
    music_nmusic int,
    PRIMARY KEY(music_nmusic)
);

CREATE TABLE playlist_music (
    playlist_nplaylist   int,
    playlist_nmusic  int,
    playlist_users_user_id int,
    music_nmusic         int,
    PRIMARY KEY(playlist_nplaylist,playlist_nmusic,playlist_users_user_id,music_nmusic)
);

CREATE TABLE music_album (
    music_nmusic int,
    album_nalbum int,
    PRIMARY KEY(music_nmusic,album_nalbum)
);

ALTER TABLE music ADD CONSTRAINT music_fk1 FOREIGN KEY (artist_nartist) REFERENCES artist(nartist);
ALTER TABLE music ADD CONSTRAINT music_fk2 FOREIGN KEY (users_user_id) REFERENCES users(user_id);
ALTER TABLE album ADD CONSTRAINT album_fk1 FOREIGN KEY (artist_nartist) REFERENCES artist(nartist);
ALTER TABLE critique ADD CONSTRAINT critique_fk1 FOREIGN KEY (album_nalbum) REFERENCES album(nalbum);
ALTER TABLE critique ADD CONSTRAINT critique_fk2 FOREIGN KEY (users_user_id) REFERENCES users(user_id);
ALTER TABLE band ADD CONSTRAINT band_fk1 FOREIGN KEY (artist_nartist) REFERENCES artist(nartist);
ALTER TABLE singer ADD CONSTRAINT singer_fk1 FOREIGN KEY (artist_nartist) REFERENCES artist(nartist);
ALTER TABLE composer ADD CONSTRAINT composer_fk1 FOREIGN KEY (music_nmusic) REFERENCES music(nmusic);
ALTER TABLE composer ADD CONSTRAINT composer_fk2 FOREIGN KEY (artist_nartist) REFERENCES artist(nartist);
ALTER TABLE shared ADD CONSTRAINT shared_fk1 FOREIGN KEY (users_user_id) REFERENCES users(user_id);
ALTER TABLE playlist ADD CONSTRAINT playlist_fk1 FOREIGN KEY (users_user_id) REFERENCES users(user_id);
ALTER TABLE url ADD CONSTRAINT url_fk1 FOREIGN KEY (music_nmusic) REFERENCES music(nmusic);
ALTER TABLE playlist_music ADD CONSTRAINT playlist_music_fk1 FOREIGN KEY (playlist_nplaylist) REFERENCES playlist(nplaylist);
ALTER TABLE playlist_music ADD CONSTRAINT playlist_music_fk2 FOREIGN KEY (playlist_nmusic) REFERENCES playlist(nmusic);
ALTER TABLE playlist_music ADD CONSTRAINT playlist_music_fk3 FOREIGN KEY (playlist_users_user_id) REFERENCES playlist(users_user_id);
ALTER TABLE playlist_music ADD CONSTRAINT playlist_music_fk4 FOREIGN KEY (music_nmusic) REFERENCES music(nmusic);
ALTER TABLE music_album ADD CONSTRAINT music_album_fk1 FOREIGN KEY (music_nmusic) REFERENCES music(nmusic);
ALTER TABLE music_album ADD CONSTRAINT music_album_fk2 FOREIGN KEY (album_nalbum) REFERENCES album(nalbum);