cZkEEf8n

1DROP TABLE IF EXISTS customers CASCADE;
2DROP TABLE IF EXISTS artists CASCADE;
3DROP TABLE IF EXISTS songs CASCADE;
4DROP TABLE IF EXISTS albums CASCADE;
5DROP TABLE IF EXISTS album_songs CASCADE;
6DROP TABLE IF EXISTS album_stocks CASCADE;
7DROP TABLE IF EXISTS rentings CASCADE;
8
9CREATE TABLE customers (
10    id              SERIAL      NOT NULL,
11    cemail          VARCHAR(64) NOT NULL DEFAULT '', -- customer email
12    cname           VARCHAR(64) NOT NULL DEFAULT '', -- customer name
13
14    PRIMARY KEY (id),
15    UNIQUE (cemail)
16);
17
18CREATE TABLE artists (
19    id              SERIAL      NOT NULL,
20    artistName      VARCHAR(64) NOT NULL DEFAULT '', -- artist common name
21    birthDate       DATE        NOT NULL DEFAULT CURRENT_DATE, -- artist birth date
22
23    PRIMARY KEY (id),
24    UNIQUE (name)
25);
26
27CREATE TABLE songs (
28    id              SERIAL      NOT NULL,
29    aid             INT         NOT NULL DEFAULT 0, -- artist id
30    song_title      VARCHAR(64) NOT NULL DEFAULT '', -- song title
31    duration        INT         NOT NULL DEFAULT 0, -- song duration
32
33    PRIMARY KEY (id),
34    FOREIGN KEY (aid) REFERENCES artists(id),
35    CHECK (duration >= 0)
36);
37
38CREATE TABLE albums (
39    id              SERIAL      NOT NULL,
40    albumName       VARCHAR(64) NOT NULL DEFAULT '', -- album name
41
42    PRIMARY KEY (id),
43    UNIQUE (albumName)
44);
45
46CREATE TABLE album_songs (
47    id              SERIAL      NOT NULL,
48    song_id         INT         NOT NULL DEFAULT 0,
49    album_id        INT         NOT NULL DEFAULT 0,
50    atrack          INT         NOT NULL DEFAULT 1,
51
52    PRIMARY KEY (id),
53    CHECK (atrack > 0)
54);
55
56CREATE TABLE album_stocks (
57    id              SERIAL      NOT NULL,
58    album_id        INT         NOT NULL DEFAULT 0,
59    client_id       INT         NOT NULL DEFAULT 0,
60    borrowed        SMALLINT    NOT NULL DEFAULT 0, -- 0 not borrowed, 1 borrowed
61
62    PRIMARY KEY (id)
63);
64
65CREATE TABLE rentings (
66    id        SERIAL      NOT NULL,
67    sid       INT         NOT NULL DEFAULT 0,
68    rent_date DATE        NOT NULL DEFAULT CURRENT_DATE,
69    ret_date  DATE        NOT NULL DEFAULT CURRENT_DATE,
70
71    PRIMARY KEY (id),
72    FOREIGN KEY (sid) REFERENCES album_stocks(id)
73);