create database BAND;
use BAND;



create table TUser(
    user_id int auto_increment primary key,
    user_ac varchar(21) unique not null,
    user_pw varchar(21) not null,
    user_sf varchar(6) check(user_sf in('admin','fans','band')) default 'fans'
);


create table TSinger(
    singer_id int auto_increment primary key,
    singer_name varchar(45),
    singer_sex varchar(10),
    singer_age int
);


create table TBand(
    band_id int auto_increment primary key,
    band_name varchar(45),
    user_id int,
    foreign key(user_id)references TUser(user_id) on delete cascade on update cascade,
    band_boss int,
    foreign key(band_boss)references TSinger(singer_id)on delete set null on update cascade,
    band_build date,
    band_text varchar(250)
);


create table TCont(
    cont_id int auto_increment primary key,
    cont_begin date,
    cont_end date,
    cont_work varchar(45),
    band_id int ,
    foreign key(band_id)references TBand(band_id)on delete cascade on update cascade,
    singer_id int,
    foreign key(singer_id)references TSinger(singer_id)on delete cascade on update cascade
);


create table TParty(
    party_id int auto_increment primary key,
    band_id int ,
    foreign key(band_id)references TBand(band_id)on delete cascade on update cascade,
    party_name varchar(45),
    party_where varchar(250),
    party_time date
);


create table TFans(
    fans_id int auto_increment primary key,
    user_id int,
    foreign key(user_id)references TUser(user_id) on delete cascade on update cascade,
    fans_sex varchar(10),
    fans_age int,
    fans_name varchar(45),
    fans_sch varchar(45),
    fans_work varchar(45)
);


create table TTick(
    tick_id int auto_increment primary key,
    party_id int ,
    foreign key(party_id)references TParty(party_id)on delete cascade on update cascade,
    fans_id int ,
    foreign key(fans_id)references TFans(fans_id)on delete cascade on update cascade
);


create table TAlbum(
    album_id int auto_increment primary key,
    band_id int ,
    foreign key (band_id)references TBand(band_id)on delete cascade on update cascade,
    album_name varchar(45),
    album_text varchar(250),
    album_time date
);


create table TFollow(
    follow_id int auto_increment primary key,
    fans_id int ,
    foreign key(fans_id)references TFans(fans_id) on delete cascade on update cascade,
    band_id int ,
    foreign key(band_id) references TBand(band_id) on delete cascade on update cascade
);


create table TScore(
    score_id int auto_increment primary key,
    score_score int ,
    fans_id int,
    foreign key(fans_id) references TFans(fans_id)on delete cascade on update cascade,
    album_id int ,
    foreign key(album_id)references TAlbum(album_id)on delete cascade on update cascade
);


create table TText(
    text_id int auto_increment primary key,
    text_text varchar(250),
    fans_id int ,
    album_id int,
    foreign key(fans_id)references TFans(fans_id)on delete cascade on update cascade,
    foreign key(album_id)references TAlbum(album_id)on delete cascade on update cascade
);


create table TLike(
    like_id int auto_increment primary key,
    fans_id int ,
    album_id int ,
    foreign key(fans_id)references TFans(fans_id)on delete cascade on update cascade,
    foreign key(album_id)references TAlbum(album_id)on delete cascade on update cascade
);


create table TSong(
    song_id int auto_increment primary key,
    song_name varchar(45),
    song_songer varchar(45)
);


create table TSing(
    sing_id int auto_increment primary key ,
    band_id int ,
    song_id int ,
    sing_name varchar(45),
    foreign key(band_id)references TBand(band_id)on delete cascade on update cascade,
    foreign key(song_id)references TSong(song_id)on delete cascade on update cascade
);

create table TLove(
    love_id int auto_increment primary key ,
    fans_id int,
    sing_id int ,
    foreign key(sing_id)references TSing(sing_id)on delete cascade on update cascade,
    foreign key(fans_id)references TFans(fans_id)on delete cascade on update cascade
);


create table THas(
    has_id int auto_increment primary key,
    sing_id int ,
    album_id int ,
    foreign key(sing_id)references TSing(sing_id)  on delete cascade on update cascade,
    foreign key(album_id)references TAlbum(album_id)  on delete cascade on update cascade
);





insert into TUser(user_ac,user_pw,user_sf) value('admin','ad4a05c4bd23b101','admin');