ixi9AtTr

1DROP DATABASE IF EXISTS ArtBase;
2CREATE DATABASE ArtBase;
3USE ArtBase;
4 
5CREATE TABLE ARTIST(
6    AName        VARCHAR(30)      NOT NULL,
7    Birthplace   VARCHAR(30)      NOT NULL,
8    Age          VARCHAR(3)       NOT NULL,
9    Style        VARCHAR(15)      NOT NULL,
10    PRIMARY KEY(AName)
11);
12 
13CREATE TABLE ARTWORK(
14    Title        VARCHAR(30)      NOT NULL,
15    Year         CHAR(4)          NOT NULL,
16    Type         VARCHAR(30)      NOT NULL,
17    Price        DECIMAL(11,2)      NOT NULL,
18    AName        VARCHAR(30)      NOT NULL,
19    PRIMARY KEY(Title),
20    FOREIGN KEY(AName) REFERENCES ARTIST(AName)
21);
22 
23CREATE TABLE CUSTOMER(
24    CustID       VARCHAR(15)      NOT NULL,
25    CName        VARCHAR(30)      NOT NULL,
26    Address      VARCHAR(30)      NOT NULL,
27    Amount       DECIMAL(11,2)    NOT NULL,
28    PRIMARY KEY(CustID)
29);
30 
31CREATE TABLE GROUPS(
32    GName        VARCHAR(30)      NOT NULL,
33    PRIMARY KEY(GName)
34);
35 
36CREATE TABLE CLASSIFY(
37    Title        VARCHAR(30)      NOT NULL,
38    GName        VARCHAR(30)      NOT NULL,
39    PRIMARY KEY(Title, GName),
40    FOREIGN KEY(GName) REFERENCES GROUPS(GName),
41    FOREIGN KEY(Title) REFERENCES ARTWORK(Title)
42);
43 
44CREATE TABLE LIKE_GROUP(
45    CustID       VARCHAR(15)      NOT NULL,
46    GName        VARCHAR(30)      NOT NULL,
47    PRIMARY KEY(CustID, GName),
48    FOREIGN KEY(CustID) REFERENCES CUSTOMER(CustID),
49    FOREIGN KEY(GName) REFERENCES GROUPS(GName)
50);
51 
52CREATE TABLE LIKE_ARTIST(
53    CustID       VARCHAR(15)      NOT NULL,
54    AName        VARCHAR(30)      NOT NULL,
55    PRIMARY KEY(CustID, AName),
56    FOREIGN KEY(CustID) REFERENCES CUSTOMER(CustID),
57    FOREIGN KEY(AName) REFERENCES ARTIST(AName)
58);