Transcript Implementation of a Physical Design

SQL Structured Query Language
Implementation Lecture – 8
Database Technology Level I
www.hope.ac.uk
Deanery of Business & Computer Sciences
Aims



2
To introduce the implementation of a
Physical design using SQL.
To introduce SQL Data Definition Language
(DDL).
To introduce SQL Data Manipulation
Language (DML).
www.hope.ac.uk
Deanery of Business & Computer Sciences
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

Field Name
Data Type
Size
courseCode
Text
8
title
Text
25
cost
Currency
3
www.hope.ac.uk
Format
Key Type
Index
Required
Primary Key
Yes(no duplicates)
Yes
Yes
Currency
No
Deanery of Business & Computer Sciences
SQL (Data Definition Language DDL
Database Definition Commands
 CREATE DATABASE
 DROP DATABASE
 SHOW DATABASES
 CREATE TABLE
 ALTER TABLE
4
www.hope.ac.uk
Deanery of Business & Computer Sciences
SQL Create Table
CREATE TABLE <table name>
(
<Column Name> <Data Type> <Conditional Clauses> ,
<Column Name> <Data Type> <Conditional Clauses> ,
…..
);
5
www.hope.ac.uk
Deanery of Business & Computer Sciences
SQL Create Table
Each column definition has 3 components.
6

<COLUMN NAME> this the name of the attribute identified in
the design.

<DATA TYPE> 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.

<CONDITIONAL CLAUSES> such as specification of Primary
Keys, Indexes and Nulls.
www.hope.ac.uk
Deanery of Business & Computer Sciences
SQL Create Table
CREATE TABLE tblCourse
(courseCode VARCHAR(8) PRIMARY KEY,
title VARCHAR(25) NOT NULL,
cost CURRENCY
);
7
www.hope.ac.uk
Deanery of Business & Computer Sciences
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)
Field Name
Data Type
Size
studentID
Text
courseCode
Text
8
Key Type
Index
Required
8
Compound Key
Yes(duplicates OK)
Yes
8
Compound Key
Yes(duplicates OK)
Yes
www.hope.ac.uk
Format
Deanery of Business & Computer Sciences
SQL Create Table
CREATE TABLE tblStudentCourse
(studentID VARCHAR(8),
courseCode VARCHAR(8),
PRIMARY KEY (studentID, courseCode)
);
ALTERNATIVE
9
CREATE TABLE tblStudentCourse
(studentID TEXT(8),
courseCode TEXT(8),
PRIMARY KEY (studentID, courseCode)
);
www.hope.ac.uk
Deanery of Business & Computer Sciences
Enforcing Foreign Keys
ALTER TABLE tblStudentCourse
ADD CONSTRAINT courseStudentCourseFK
FOREIGN KEY (courseCode)
REFERENCES tblcourse (courseCode);
10
www.hope.ac.uk
Deanery of Business & Computer Sciences
Adding data using SQL
INSERT INTO targetTable (Field1, Field2, …)
 VALUES (Value1, Value2, …)
11
www.hope.ac.uk
Deanery of Business & Computer Sciences
Adding data using SQL
Method 1
INSERT INTO tblProduct (prodID, prodDesc,
cost)
VALUES (‘ssaay1’, ‘Nut’, 34.50)
12
www.hope.ac.uk
Deanery of Business & Computer Sciences
Adding data using SQL
Method 2
INSERT INTO tblProduct
VALUES (‘ssaay2’, ‘screw’, 4.50);
13
www.hope.ac.uk
Deanery of Business & Computer Sciences
Adding data using SQL
INSERT INTO tblSupplier
(suppName, suppAdd1, suppAdd2,
suppPostCode)
VALUES
(‘Johnsons’, ‘34 Meols Parade’, ‘Meols’, ‘CH47
6AY’)
14
www.hope.ac.uk
Deanery of Business & Computer Sciences
Summary





15
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
www.hope.ac.uk
Deanery of Business & Computer Sciences
REMEMBER
16

Implementation Test (3), W/B
18th February 2008 (20%)

Group Work Implementation Due 7th March 2008
www.hope.ac.uk
Deanery of Business & Computer Sciences