STUDY UNITS
ICT322 DATABASE SYSTEMS
SU-2
SIM UNIVERSITY
1.1
SQL DDL, DML, and Joins
•
DDL stands for Data Definition Language
•
The 3 DDL statements covered in this chapter are :
1.
CREATE TABLE
2.
ALTER TABLE
3.
DROP TABLE
•
CREATE TABLE statement is used to construct tables, define columns and column
constraints, and create relationships
•
Tables can also be created using graphical tools provided by most DBMSs
•
Fig 7.13 of the course textbook is reproduced below for easy reference.
It illustrates the
various parts of the CREATE TABLE construct, as well as table relationships:
CREATE TABLE ARTIST (
ArtistID
int
NOT NULL IDENTITY (1,1),
Name
char (25)
NOT NULL,
Nationality
char (30)
NULL,
Birthdate
numeric (4,0) NULL,
DeceasedDate
numeric (4,0) NULL,
CONSTRAINT
ArtistPK PRIMARY KEY (ArtistID),
CONSTRAINT ArtistAK1
UNIQUE (Name),
CONSTRAINT NationalityValues CHECK
(Nationality IN (‘Canadian’, ‘English’, ‘French’, ‘German’, ….)),
CONSTRAINT BirthVluesCheck CHECK (Dirthdate < DeceasedDate),
CONSTRAINT ValidBirthYear CHECK (Birthdate LIKE ‘[1 – 2][0 – 9][0 – 9][0 – 9]’)
);
CREATE TABLE WORK (
WorkID
int
NOT NULL IDENTITY (500, 1),
Title
char (25)
NOT NULL,
Copy
char (8)
NOT NULL,
Description
varchar (1000) NULL DEFAULT ‘Unknown provenance’,
ArtistID
Int
NOT NULL,
CONSTRAINT WorkPK
PRIMARY KEY (WorkID),
CONSTRAINT WorkAK1
UNIQUE (Title, Copy),
CONSTRAINT
ArtistFK
FOREIGN KEY (ArtistID) REFERENCES
ARTIST (ArtistID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
