s2wmEPK9

1USE master
2GO
3IF NOT EXISTS (
4   SELECT name
5   FROM sys.databases
6   WHERE name = N'Radiostation'
7)
8CREATE DATABASE [Radiostation]
9GO
10
11USE [Radiostation]
12CREATE TABLE dbo.Station
13(
14   ID_station INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
15   Station_rating INT NOT NULL,
16   Station_name NVARCHAR(40) NOT NULL
17);
18GO
19
20CREATE TABLE dbo.Presenter
21(
22   ID_presenter INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
23   Presenter_rating INT NOT NULL,
24   Presenter_name NVARCHAR(40) NOT NULL
25);
26GO
27
28CREATE TABLE dbo.Program
29(
30   ID_prog INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
31   Prog_rating INT NOT NULL,
32   Prog_name NVARCHAR(40) NOT NULL,
33   ID_station INT NOT NULL,
34   FOREIGN KEY(ID_station) REFERENCES dbo.Station (ID_station) 
35);
36GO
37
38CREATE TABLE dbo.Edition_Program
39(
40   ID_ed INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
41   Ed_rating INT NOT NULL,
42   Start_time DATETIME NOT NULL,
43   End_time DATETIME NOT NULL,
44   ID_presenter INT NOT NULL,
45   FOREIGN KEY(ID_presenter) REFERENCES dbo.Presenter (ID_presenter),
46   ID_prog INT NOT NULL,
47   FOREIGN KEY(ID_prog) REFERENCES dbo.Program (ID_prog) 
48);
49GO
50
51CREATE TABLE dbo.Applicant
52(
53   ID_applicant INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
54   Age INT NOT NULL,
55   Name_applicant NVARCHAR(40) NOT NULL,
56   Gender NVARCHAR(40) NOT NULL
57);
58GO
59
60CREATE TABLE dbo.Application
61(
62   ID_application INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
63   ID_track INT NOT NULL,
64   App_time DATETIME NOT NULL,
65   ID_applicant INT NOT NULL,
66   FOREIGN KEY(ID_applicant) REFERENCES dbo.Applicant (ID_applicant),
67   ID_list INT NOT NULL
68);
69GO
70
71CREATE TABLE dbo.Application_list
72(
73   ID_list INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
74   ID_ed INT REFERENCES dbo.Edition_Program (ID_ed) NOT NULL,
75   ID_app INT REFERENCES dbo.Application (ID_application) NOT NULL
76);
77GO
78
79CREATE TABLE dbo.Genre
80(
81   ID_genre INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
82   Genre_rating INT NOT NULL,
83   Genre_name NVARCHAR(40) NOT NULL
84);
85GO
86
87CREATE TABLE dbo.Band
88(
89   ID_band INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
90   Band_rating INT NOT NULL,
91   Band_name NVARCHAR(40) NOT NULL
92);
93GO
94
95CREATE TABLE dbo.Album
96(
97   ID_album INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
98   Album_rating INT NOT NULL,
99   Album_name NVARCHAR(40) NOT NULL,
100   ID_band INT NOT NULL,
101   FOREIGN KEY(ID_band) REFERENCES dbo.Band (ID_band) 
102);
103GO
104
105CREATE TABLE dbo.Track
106(
107   ID_track INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Track PRIMARY KEY,
108   Track_rating INT NOT NULL,
109   Track_name NVARCHAR(40) NOT NULL,
110   ID_band INT NOT NULL,
111   ID_album INT NOT NULL,
112   ID_genre INT NOT NULL
113   FOREIGN KEY(ID_band) REFERENCES dbo.Band (ID_band),
114   FOREIGN KEY(ID_album) REFERENCES dbo.Album (ID_album),
115   FOREIGN KEY(ID_genre) REFERENCES dbo.Genre (ID_genre)
116);
117GO
118
119INSERT INTO dbo.Band(Band_rating, Band_name)
120	VALUES (100, N'Wovenhand');
121GO
122
123INSERT INTO dbo.Band(Band_rating, Band_name)
124	VALUES (90, N'Megadeth');
125GO
126
127DELETE dbo.Band
128WHERE Band_name = N'Wovenhand'
129
130DELETE dbo.Band