ICT322 StudyUnits_Jul10 - ICT322 Database Systems:...

Info iconThis preview shows pages 1–5. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: ICT322 Database Systems: Implementation And Administration 0B0B 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...
View Full Document

This note was uploaded on 09/06/2010 for the course IT ICT322 taught by Professor Tan during the Spring '10 term at Singapore Institute of Management.

Page1 / 33

ICT322 StudyUnits_Jul10 - ICT322 Database Systems:...

This preview shows document pages 1 - 5. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online