Question

Learning Objectives:

Learn to define constraints on tables

Lean to define primary keys and foreign keys

Exercise                                                                               

In Lab 09, we will continue the practice of creating tables. You will need to write SQL statements for the tasks specified below. After you complete them, submit your SQL script (in one *.sql file) to Blackboard by 11:59pm Nov. 16 (Friday). Include your name as a comment in the SQL script submitted. The creation statement for each table is worth one point, thus 3 points in total for this practice. The instructor will grade them manually.

We will continue the example of we practiced in Lab 08 last week. If you have the database VRG in your MySQL server, drop it completely first before starting with this week’s exercise.

Re-create a database named VRG and set it as the default database. VRG is used for a small art gallery, the View Ridge Gallery, which sells contemporary European and North American fine arts. The database consists of three tables.

Create an ARTIST table that tracks the information about artists whose works are on sell by VRG;

Create a WORK table that tracks the information about each piece of art work ever owned by VRG;

The detail of those table requirements are as follows. You can reuse your script for Lab08 to save some typing.

ARTIST Table

ColumnName	Data Type	NULL Status	Column Constraints
ArtistID	Int	NOT NULL	This is a surrogate key: automatically increase by 1 for every new row, starting from 1 for the first row
LastName	Char(25)	NOT NULL
FirstName	Char(25)	NOT NULL
Nationality	Char(50)	NULL	The default value of nationality is “USA”
DateofBirth	Date	NULL
DateDeceased	Date	NULL
Table-Level Constraints The combination of FirstName and LastName needs to be unique across rows in this table DateofBirth needs to be earlier than DateDeceased (Hint: this needs a CHECK constraint. MySQL does not really support it, but let’s practice writing it once.) Set ArtistID as the primary key of this table

WORK Table

ColumnName	Data Type	NULL Status	Column Constraints
WorkID	Int	NOT NULL	This is a surrogate key: automatically increase by 1 for every new row, starting from 1 for the first row
Title	Char(35)	NOT NULL
Copy	Char(12)	NOT NULL
Medium	Char(35)	NULL	The default value of nationality is “print”
Description	VarChar(1000)	NULL
ArtistID	Int	NOT NULL
Table-Level Constraints The combination of Title and Copy needs to be unique across rows in this table Set WorkID as the primary key of this table Set ArtistID as the foreign key of this table, referring to the ArtistID column of the Artist table. Any changes (both updates and deletes) on the existing values of the ArtistID column of the Artist table will cascade to this table.

After creating the ARTIST and WORK tables, insert the following rows for each table. Pay attention that the new rows you add need to follow all the constraints we specified (or learn from the error messages MySQL reported if you don’t)

For the ARTIST table, insert the following rows by using INSERT statements:

LastName	FirstName	Nationality	DateofBirth	DateDeceased
Smith	James		1970-01-01
Henderson	Sue		1940-01-01	2017-01-01
Smith	James		1906-01-01	2000-10-4

Note: (1) Don’t insert any value if the value is not provided in the table above. After insertion, check how the surrogate key and the default value work.

          (2) When inserting a Date value, you also need to enclose the value with a pair of single quotation marks.

          (3) There will be a syntax error when inserting the last row. Think why this error happens, and then fix it by changing the value of Firstname in the to-be-inserted row to something else.

For the WORK table, insert the following rows by using INSERT statements:

Title	Copy	Medium	Description	ArtistID
Lily Pond	the 74th			2
Sunflower	the 1st	Canvas		2
Random Error	the 1st			5

Note: (1) Don’t insert any value if the value is not provided in the table above. After insertion, check how the surrogate key and the default value work.

          (2) There will be a syntax error when inserting the last row. Think why this error happens, and abandon this insertion.

Update the ArtistID in one row of the Artist table, and check how the data of the WORK table change

Write an UPDATE statement to change the ArtistID from 2 to 138 for the ARTIST table. After update, check what changes have happened to the data in both ARTIST and WORK table.

Delete a row in the Artist table, and check how the data of the WORK table change

Write a DELETE statement to delete rows with an ArtistID as 138 from the ARTIST table. After deletion, check what changes have happened to the data in both ARTIST and WORK table.

Answer 1

/*-------- Drop Databse ----------------------------*/

drop database VRG

/*--------------------------------------------------------------*/

/*-------- Create Databse ----------------------------*/

create database VRG

/*--------------------------------------------------------------*/

/*-------- Creating Table Artist ----------------------------*/

CREATE TABLE Artist (

ArtistID INT IDENTITY(0,1) NOT NULL,

LastName char(25) NOT NULL,

FirstName char(25)NOT NULL,

Nationality char(50) default 'USA',

DateofBirth date,

DateDeceased date,

PRIMARY KEY (ArtistID),

CONSTRAINT LF_Person UNIQUE (FirstName,LastName),

CHECK (DateofBirth<DateDeceased)

);

/*--------------------------------------------------------------*/

/*-------- Creating Table Work ----------------------------*/

CREATE TABLE WORK (

WorkID INT IDENTITY(0,1) NOT NULL,

Title char(35) NOT NULL,

Copy char(12)NOT NULL,

Medium char(35) default 'print',

Description varchar(1000),

ArtistID INT,

CONSTRAINT TC UNIQUE (Title,Copy),

PRIMARY KEY (WorkID),

FOREIGN KEY (ArtistID) REFERENCES Artist(ArtistID)

);

/*--------------------------------------------------------------*/

/*-------- Insert In Artist ----------------------------*/

insert into Artist(LastName,FirstName,Nationality,DateofBirth,DateDeceased) values('Smith','James',

1970-01-01),('Henderson','Sue',1940-01-01,2017-01-01),('Smith','James',1906-01-01,2000-10-4);

/*--------------------------------------------------------------*/

/*-------- Insert In WORK ----------------------------*/

insert into WORK (Title,Copy,Medium,Description,ArtistID) values('Lily Pond','

the 74th',2),('Sunflower','the 1st','Canvas',2),('Random Error',5);

/*--------------------------------------------------------------*/