CREATE DATABASE IF NOT EXISTS music_rating_system;
USE music_rating_system;
CREATE TABLE song (
SID VARCHAR(11) NOT NULL,
title VARCHAR(60) NOT NULL,
type VARCHAR(16) NOT NULL,
year INT NOT NULL,
singer VARCHAR(20) NOT NULL,
composer VARCHAR(20) NOT NULL,
lyreist VARCHAR(20) NOT NULL,
PRIMARY KEY (SID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE User (
UID VARCHAR(11) NOT NULL,
name VARCHAR(11) NOT NULL,
sex CHAR(1),
phone CHAR(11),
birthday DATE,
PRIMARY KEY (UID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE Comment (
SID VARCHAR(11) NOT NULL,
UID VARCHAR(11) NOT NULL,
score INT NOT NULL CHECK (score BETWEEN 1 AND 10),
PRIMARY KEY (SID, UID),
FOREIGN KEY (SID) REFERENCES song(SID) ON DELETE CASCADE,
FOREIGN KEY (UID) REFERENCES User(UID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE INDEX idx_song_title ON song(title);
CREATE INDEX idx_song_singer ON song(singer);
CREATE INDEX idx_song_type ON song(type);
CREATE INDEX idx_comment_score ON Comment(score);