search

ALLHUBS-Jan-2024-Liftoff / max-group-CJSY

MIT License
0 stars 3 forks source link

Back-end Developer / Database Specialist: Set up MySQL database #11

Open UGcode101 opened 3 months ago

UGcode101 commented 3 months ago

Install and configure MySQL database locally or on a cloud service.

shivaniladia commented 3 months ago

CREATE TABLE users ( User_ID int NOT NULL AUTO_INCREMENT, Name varchar(50) NOT NULL, Email varchar(50) NOT NULL, Password varchar(100) NOT NULL, Date_of_Birth date DEFAULT NULL, Profile_Image blob, PRIMARY KEY (User_ID), UNIQUE KEY Email (Email) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE playlists ( Playlist_ID int NOT NULL AUTO_INCREMENT, User_ID int DEFAULT NULL, Name varchar(50) NOT NULL, Image blob, PRIMARY KEY (Playlist_ID), KEY User_ID (User_ID), CONSTRAINT playlists_ibfk_1 FOREIGN KEY (User_ID) REFERENCES users (User_ID) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE playlist_tracks ( Playlist_ID int NOT NULL, Track_ID int NOT NULL, Order int DEFAULT NULL, PRIMARY KEY (Playlist_ID,Track_ID), KEY Track_ID (Track_ID), CONSTRAINT playlist_tracks_ibfk_1 FOREIGN KEY (Playlist_ID) REFERENCES playlists (Playlist_ID), CONSTRAINT playlist_tracks_ibfk_2 FOREIGN KEY (Track_ID) REFERENCES tracks (Track_ID) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE tracks ( Track_ID int NOT NULL AUTO_INCREMENT, Album_ID int DEFAULT NULL, Name varchar(50) NOT NULL, Duration int NOT NULL, Path varchar(255) DEFAULT NULL, PRIMARY KEY (Track_ID), KEY Album_ID (Album_ID), CONSTRAINT tracks_ibfk_1 FOREIGN KEY (Album_ID) REFERENCES albums (Album_ID) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE artists ( Artist_ID int NOT NULL AUTO_INCREMENT, Name varchar(50) NOT NULL, Genre varchar(50) DEFAULT NULL, Image blob, PRIMARY KEY (Artist_ID) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE albums ( Album_ID int NOT NULL AUTO_INCREMENT, Artist_ID int DEFAULT NULL, Name varchar(50) NOT NULL, Release_Date date DEFAULT NULL, Image varchar(255) DEFAULT NULL, PRIMARY KEY (Album_ID), KEY Artist_ID (Artist_ID), CONSTRAINT albums_ibfk_1 FOREIGN KEY (Artist_ID) REFERENCES artists (Artist_ID) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci