ICT322 StudyUnits_Jul10 - ICT322 Database Systems...

ICT322
Database Systems:
Implementation And Administration
STUDY UNITS

ICT322 DATABASE SYSTEMS STUDY UNITS
SIM UNIVERSITY SU-1
CHAPTER 1 SQL FOR DATABASE CONSTRUCTION
AND APPLICATION PROCESSING
Unit Learning Objectives
1. To be able to create and manage table structures using SQL.
2. To understand and be able to implement referential integrity actions in SQL code.
3. To understand Views and be able to create and use SQL Views to query database tables.
4. To gain an understanding of how SQL is used in an application program.
5. To understand triggers and how to use them to enforce integrity constraints.
6. To understand stored procedures and be able to create them in SQL.
Prerequisite Knowledge
Basic SQL and programming concepts
Reading
Prescribed Text : Chapter 7
Learning Summary
In ICT 321 Chapter 2, you were introduced to SQL. Here in ICT 322 Chapter 1, you will learn
more about SQL data manipulation, SQL views and how to embed SQL statements into
programs, plus triggers and stored procedures. Essentially, you will be able to build databases
and applications to process the data. Following is a summary of the main points covered in
Chapter 1. It is estimated that the student will spend about 3 to 4 hrs covering this chapter,
including hands-on practice. Self-assessment questions are included at suitable junctures in the
notes below to test understanding of the contents covered. Do spend about 30 – 45 mins to go
through these notes and attempt the questions, then check the text and / or other sources for the
accuracy and completeness of your answers. It is also advisable to get some real programming
practice on the version of SQL used in the course.

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
);