use [master];
go

if db_id('MusicCollection_db') is not null
begin
	drop database [MusicCollection_db];
end
go

create database [MusicCollection_db];
go

use [MusicCollection_db];
go
create table [Discs]
(
[Id] int not null identity(1, 1) primary key,
[Title] nvarchar(100) not null check ([Title] <> N''),
[SingerId] int not null,
[StyleId] int not null,
[LabelId] int not null,
[Release] date not null check ([Release] <= getdate()) default getdate())
go
insert [Discs] (Title,SingerId,StyleId,LabelId,Release)values
(N'Live at Wembley 86',1,1,1,'1992-05-26'),
(N'Babylon by Bus',2,2,2,'1978-11-10'),
(N'Thriller',3,3,3,'1982-11-30'),
(N'The Fat of the Land',4,4,4,'1997-06-30'),
(N'The Slim Shady LP',5,5,5,'1999-02-23'),
(N'Queen Rock Montreal',1,1,1,'2007-10-29'),
(N'Live at the Rainbow 74',1,1,1,'2014-09-08'),
(N'Invincible',3,3,3,'2001-10-30'),
(N'Recovery',5,5,5,'2010-06-18')
go
create table [Styles]
(
[Id] int not null identity(1, 1) primary key,
[Name] nvarchar(50) not null unique check ([Name] <> N''))
go
insert [Styles](Name)values
(N'Rock'),
(N'Reggae'),
(N'Pop'),
(N'Breakbeat'),
(N'Rap')
go
create table [Singers]
(
[Id] int not null identity(1, 1) primary key,
[Name] nvarchar(max) not null check ([Name] <> N''))
go
insert [Singers](Name)values
(N'Queen'),
(N'Bob Marley'),
(N'Michael Jackson'),
(N'The Prodigy'),
(N'Eminem')
go
create table [Labels]
(
[Id] int not null identity(1, 1) primary key,
[Name] nvarchar(100) not null unique check ([Name] <> N''),
[Country] nvarchar(100) not null check ([Country] <> N''))
go
insert [Labels](Name,Country)values
(N'Hollywood Records',N'USA'),
(N'Island Records',N'Jamaica'),
(N'Westlake Recording Studios',N'USA'),
(N'XL Recordings',N'Great Britain'),
(N'Aftermath Entertainment',N'USA')
go
create table [Songs]
(
[Id] int not null identity(1, 1) primary key,
[Name] nvarchar(max) not null check ([Name] <> N''),
[DiscId] int not null,
[Duration] time not null,
[StyleId] int not null,
[SingerId] int not null)
go
insert [Songs](Name,DiscId,Duration,StyleId,SingerId)values
(N'One Vision',1,'00:05:50',1,1),(N'A Kind of Magic',1,'00:08:41',1,1),(N'Who Wants to Live Forever',1,'00:05:16',1,1),
(N'Bohemian Rhapsody',1,'00:05:50',1,1),(N'Radio Ga Ga',1,'00:05:57',1,1),(N'We Are the Champions',1,'00:04:05',1,1),
(N'We Will Rock You',1,'00:02:46',1,1),
(N'Positive Vibration',2,'00:05:48',2,2),(N'Concrete Jungle',2,'00:05:40',2,2),(N'Kinky Reggae',2,'00:04:49',2,2),
(N'Lively Up Yourself',2,'00:06:21',2,2),(N'Is This Love',2,'00:07:30',2,2),(N'Heathen',2,'00:04:30',2,2),(N'Jamming',2,'00:05:44',2,2),
(N'Thriller',3,'00:05:57',3,3),(N'Billie Jean',3,'00:04:54',3,3),(N'Human Nature',3,'00:04:07',3,3),
(N'P.Y.T. (Pretty Young Thing)',3,'00:03:58',3,3),(N'Love Never Felt So Good',3,'00:03:54',3,3),(N'Beat It',3,'00:04:18',3,3),
(N'Smack My Bitch Up',4,'00:05:43',4,4),(N'Breathe',4,'00:05:35',4,4),(N'Diesel Power',4,'00:04:18',4,4),
(N'Funky Shit',4,'00:05:16',4,4),(N'Mindfields',4,'00:05:40',4,4),(N'Firestarter',4,'00:04:42',4,4),
(N'My Name Is',5,'00:04:28',5,5),(N'Guilty Conscience',5,'00:03:19',5,5),(N'Brain Damage',5,'00:03:46',5,5),
(N'If I Had',5,'00:04:05',5,5),(N'Role Model',5,'00:03:25',5,5),(N'My Fault',5,'00:04:01',5,5),
(N'We Are the Champions',6,'00:04:05',1,1),(N'We Will Rock You',7,'00:02:46',1,1),
(N'Not Afraid',9,'00:04:08',5,5),(N'Speechless',8,'00:03:18',3,3)
go
alter table [Discs]
add foreign key ([SingerId]) references [Singers]([Id]);
go
alter table [Discs]
add foreign key ([StyleId]) references [Styles]([Id]);
go
alter table [Discs]
add foreign key ([LabelId]) references [Labels]([Id]);
go
alter table [Songs]
add foreign key ([DiscId]) references [Discs]([Id]);
go
alter table [Songs]
add foreign key ([StyleId]) references [Styles]([Id]);
go
alter table [Songs]
add foreign key ([SingerId]) references [Singers]([Id]);
go