SQL Structured Query Language

1
SQL Structured Query Language
2
Aims

• To introduce the implementation of a Physical
  design using SQL.
• To introduce SQL Data Definition Language (DDL).
• To introduce SQL Data Manipulation Language (DML).

3
Implementation of a Physical Design

• Database Design Language
• Course (courseCode, title, cost)
• Entity Course is converted to the following
  physical design.
• courseCode and title are set to text and indexed
  appropriately, cost is set to Currency
• tblCourse

4
SQL (Data Definition Language - DDL

• Database Definition Commands
• CREATE DATABASE
• DROP DATABASE
• SHOW DATABASES
• CREATE TABLE
• ALTER TABLE

5
SQL Create Table

• CREATE TABLE lttable namegt
• (
• ltColumn Namegt ltData Typegt ltConditional Clausesgt
  ,
• ltColumn Namegt ltData Typegt ltConditional Clausesgt
  ,
• ..
• )

6
SQL Create Table

• Each column definition has 3 components.
• ltCOLUMN NAMEgt this the name of the attribute
  identified in the design.
• ltDATA TYPEgt specified for the attribute in the
  Physical design. The data types available for
  use will depend on those appropriate for the
  software with which the database is to be
  implemented.
• ltCONDITIONAL CLAUSESgt such as specification of
  Primary Keys, Indexes and Nulls.

7
SQL Create Table

• CREATE TABLE tblCourse
• (courseCode VARCHAR(8) PRIMARY KEY,
• title VARCHAR(25) NOT NULL,
• cost CURRENCY
• )

8
Implementation of a Physical Design

• StudentCourse(studentID, courseCode)
• FK studentID?tblStudent Update Cascade, Delete
  Restrict
• FK courseCode?tblcourse Update Cascade, Delete
  Restrict
• Entity StudentCourse is converted to the
  following physical design.
• This entity has a Compound Key, all elements of
  the compound key are identified.
• Both attributes are foreign keys. This means
  that their data type and size must exactly match
  the data type and the field to which they will be
  linking to enforce the relationship between the
  tables.
• Compound keys are always indexed yes (duplicates
  OK)

9
SQL Create Table

• CREATE TABLE tblStudentCourse
• (studentID VARCHAR(8),
• courseCode VARCHAR(8),
• PRIMARY KEY (studentID, courseCode)
• )

10
Enforcing Foreign Keys

• ALTER TABLE tblStudentCourse
• ADD CONSTRAINT courseStudentCourseFK FOREIGN KEY
  (courseCode)
• REFERENCES tblcourse (courseCode)

11
Adding data using SQL

• INSERT INTO targetTable (Field1, Field2, )
• VALUES (Value1, Value2, )

12
Adding data using SQL

• INSERT INTO targetTable (Field1, Field2, )
• VALUES (Value1, Value2, )

13
Adding data using SQL

• Method 1
• INSERT INTO tblProduct (prodID, prodDesc, cost)
• VALUES (ssaay1, Nut, 34.50)

14
Adding data using SQL

• Method 2
• INSERT INTO tblProduct
• VALUES (ssaay2, screw, 4.50)

15
Adding data using SQL

• INSERT INTO tblSupplier
• (suppName, suppAdd1, suppAdd2, suppPostCode)
• VALUES
• (Johnsons, 34 Meols Parade, Meols, CH47
  6AY)

16
Summary

• Implementation of a Physical Design
• Use of SQL Create Table
• Use of SQL Alter Table to enforce foreign keys.
• Student individual work
• Rest of chapter 6

17
NEXT WEEK

• Chapter 7
• More Advanced SQL
• Next Week is an in class-Test-
• Logical Design Test