fhmashcp

1DROP TABLE IF EXISTS albums CASCADE;
2DROP TABLE IF EXISTS artists CASCADE;
3DROP TABLE IF EXISTS customers CASCADE;
4DROP TABLE IF EXISTS orders CASCADE;
5DROP TABLE IF EXISTS playlists CASCADE;
6DROP TABLE IF EXISTS tracks CASCADE;
7DROP TABLE IF EXISTS TracksInPlaylists CASCADE;
8DROP TABLE IF EXISTS TracksInOrders CASCADE;
9-- Commands to create necessary tables.
10CREATE TABLE Albums (
11  Id        INT PRIMARY KEY,
12  Title     VARCHAR(120) NOT NULL,
13  Artist_Id INT          NOT NULL
14);
15
16CREATE TABLE Artists (
17  Id   INT PRIMARY KEY,
18  Name VARCHAR(120) NOT NULL
19);
20
21CREATE TABLE Customers (
22  Id    INT PRIMARY KEY,
23  Name  VARCHAR(60) NOT NULL,
24  Email VARCHAR(60) NOT NULL
25);
26CREATE TABLE Orders (
27  Id             INT PRIMARY KEY,
28  Date           DATE        NOT NULL,
29  DeliveryAdress VARCHAR(70) NOT NULL,
30  TrackQuantity  INT(11)     NOT NULL,
31  CustomerId     INT         NOT NULL
32);
33
34CREATE TABLE Playlists (
35  Id   INT PRIMARY KEY,
36  Name VARCHAR(120) NOT NULL
37);
38CREATE TABLE Tracks (
39  Id      INT PRIMARY KEY,
40  Name    VARCHAR(200)   NOT NULL,
41  Length  BIGINT(20)     NOT NULL,
42  Genre   VARCHAR(120)   NOT NULL,
43  Price   DECIMAL(10, 2) NOT NULL,
44  AlbumId INT            NOT NULL
45);
46CREATE TABLE TracksInPlaylists (
47  PlaylistId INT,
48  TrackId    INT
49);
50CREATE TABLE TracksInOrders (
51  OrderId INT,
52  TrackId INT
53);
54-- Commands to insert values in newly-created tables.
55INSERT INTO Albums
56  SELECT DISTINCT
57    AlbumId,
58    AlbumTitle,
59    ArtistId
60  FROM audiotrackstore;
61
62INSERT INTO Artists
63  SELECT DISTINCT
64    ArtistId,
65    ArtistName
66  FROM audiotrackstore;
67
68INSERT INTO Customers
69  SELECT DISTINCT
70    CustomerId,
71    CustomerName,
72    CustomerEmail
73  FROM audiotrackstore;
74
75INSERT INTO Orders
76  SELECT DISTINCT
77    OrderId,
78    OrderDate,
79    DeliveryAddress,
80    OrderTrackQuantity,
81    CustomerId
82  FROM audiotrackstore;
83
84INSERT INTO Playlists
85  SELECT DISTINCT
86    PlaylistId,
87    PlaylistName
88  FROM audiotrackstore;
89
90INSERT INTO Tracks
91  SELECT DISTINCT
92    TrackId,
93    TrackName,
94    TrackLength,
95    TrackGenre,
96    TrackPrice,
97    AlbumId
98  FROM audiotrackstore;
99
100INSERT INTO TracksInOrders
101  SELECT DISTINCT
102    OrderId,
103    TrackId
104  FROM audiotrackstore;
105
106INSERT INTO TracksInPlaylists
107  SELECT DISTINCT
108    PlaylistId,
109    TrackId
110  FROM audiotrackstore