xrA3rcXq

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);
16
17CREATE TABLE artists (
18    id              SERIAL      NOT NULL,
19    artistName      VARCHAR(64) NOT NULL DEFAULT '', -- artist common name
20    birthDate       DATE        NOT NULL DEFAULT CURRENT_DATE, -- artist birth date
21
22    PRIMARY KEY (id)
23);
24
25CREATE TABLE songs (
26    id              SERIAL      NOT NULL,
27    aid             INT         NOT NULL DEFAULT 0, -- artist id
28    song_title      VARCHAR(64) NOT NULL DEFAULT '', -- song title
29    duration        INT         NOT NULL DEFAULT 0, -- song duration
30
31    PRIMARY KEY (id),
32    FOREIGN KEY (aid) REFERENCES artists(id)
33);
34
35CREATE TABLE albums (
36    id              SERIAL      NOT NULL,
37    albumName       VARCHAR(64) NOT NULL DEFAULT '', -- album name
38
39    PRIMARY KEY (id)
40);
41
42CREATE TABLE album_songs (
43    id              SERIAL      NOT NULL,
44    song_id         INT         NOT NULL DEFAULT 0,
45    album_id        INT         NOT NULL DEFAULT 0,
46    atrack          INT         NOT NULL DEFAULT 1,
47
48    PRIMARY KEY (id)
49);
50
51CREATE TABLE album_stocks (
52    id              SERIAL      NOT NULL,
53    album_id        INT         NOT NULL DEFAULT 0,
54    client_id       INT         NOT NULL DEFAULT 0,
55    borrowed        SMALLINT    NOT NULL DEFAULT 0, -- 0 not borrowed, 1 borrowed
56
57    PRIMARY KEY (id)
58);
59
60CREATE TABLE rentings (
61    id        SERIAL      NOT NULL,
62    sid       INT         NOT NULL DEFAULT 0,
63    rent_date DATE        NOT NULL DEFAULT CURRENT_DATE,
64    ret_date  DATE        NOT NULL DEFAULT CURRENT_DATE,
65
66    PRIMARY KEY (id),
67    FOREIGN KEY (sid) REFERENCES album_stocks(id)
68);