16.5 Create ARTIST Table

16.5.1 Create Table

-- USE VRG;
CREATE TABLE ARTIST (
    ArtistID             int                NOT NULL,
    LastName             varchar(25)            NOT NULL,
    FirstName            varchar(25)            NOT NULL,
    Nationality          varchar(30)            NULL,
    DateOfBirth          decimal(4,0)           NULL,
    DateDeceased         decimal(4,0)           NULL,
    CONSTRAINT  ArtistPK                  PRIMARY KEY(ArtistID),
    CONSTRAINT  ArtistAK1                 UNIQUE(LastName, FirstName),
    CONSTRAINT  NationalityValues CHECK
                    (Nationality IN ('Canadian', 'English', 'French',
                     'German', 'Mexican', 'Russian', 'Spanish',
                     'United States')),
    CONSTRAINT  BirthValuesCheck  CHECK (DateOfBirth < DateDeceased),
    CONSTRAINT  ValidBirthYear    CHECK
                    (DateOfBirth between 1000 and 2999),
    CONSTRAINT  ValidDeathYear    CHECK
                    (DateDeceased between 1000 and 2999)
    );

16.5.2 Run Reverse Engineer

  • Do reverse engineering on vrg.

  • In the resulting diagram, inspect the artist table for each column, its data type and leading notation as well.

16.5.3 (not run) INSERT INTO ARTIST

INSERT INTO ARTIST
(ArtistID, LastName, FirstName, Nationality, DateOfBirth, DateDeceased)
VALUES (1, 'Miro', 'Joan', 'Spanish', 1893, 1983);