Transcript ppt - Spatial Database Group

OUTLINE OF THE LECTURE
PART I GOAL: Understand the Data Definition Statements in Fig 4.1

Step1: Columns of the Tables and Data types.

Step2: Single column constraints:
 Not Null; Default and; Unique

Step3: Multi-column constraints:
 Primary key
 Foreign key

Step 4: Business process constraints (Check constraints)
PART II GOAL: Introduction to SQL Language

Unrestricted results
 Unordered or Ordered.
 Projection on certain specific columns of table.

Restricted results using ‘where clause’
STEP 1: COLUMNS OF THE TABLES AND DATA TYPES
 Create Table Employee(
Fname Varchar(15),
Minit Char,
Lname Varchar(15),
Ssn Char(9),
Bdate Date,
Address Varchar(30),
Sex Char,
Salary Decimal(10,2),
Super_ssn char(9),
Dno INT);
 Create Table Department(
Dname Varchar(15),
Dnumber INT,
Mgr_ssn char(9),
Mgr_start_date Date);
STEP 1: COLUMNS OF THE TABLES AND DATA TYPES
 Create Table Dependent(
Essn Char(9),
Dependent_name Varchar(15),
Sex Char,
Bdate Date,
Relationship Varchar(8));
• Downside: Poor quality data might get entered into the
database. E.g.
• Too many NULLs are possible.
• Duplicate entries of employees and departments.
STEP2: SINGLE COLUMN CONSTRAINTS:
Create Table Employee(
Fname Varchar(15) NOT NULL,
Minit Char,
Lname Varchar(15)
NOT NULL,
Ssn Char(9)
NOT NULL,
Bdate Date,
Address Varchar(30),
Sex Char,
Salary Decimal(10,2),
Super_ssn char(9),
Dno INT NOT NULL DEFAULT 9);
 Create Table Department(
Dname Varchar(15)
Dnumber INT
Mgr_ssn char(9)
Mgr_start_date Date,
Unique (Dname)
NOT NULL,
NOT NULL,
NOT NULL DEFAULT 123459999,
);
Sometime we may want use
default values for some fields to
maintain consistency
Not NULL constraint help us to
maintain data quality.
Ensure each Dname is Unique,
No duplicates are allowed
STEP 3: MULTI-COLUMN CONSTRAINT: PRIMARY KEY
Create Table Employee(
Fname Varchar(15) NOT NULL,
Lname Varchar(15)
NOT NULL,
Ssn Char(9)
NOT NULL,
Super_ssn char(9),
Dno INT NOT NULL DEFAULT 9,
Primary Key (Ssn)
);
• Primary Key Vs Unique Constraint
• Primary Key can also defined as
Unique + Not Null Constraint
• Many DBMS build an index on
Primary Key (main advantage)
 Create Table Department(
Dname Varchar(15) NOT NULL,
Dnumber INT
NOT NULL,
Mgr_ssn char(9)
NOT NULL DEFAULT 123459999,
Mgr_start_date Date,
Primary Key (Dnumber),
Unique (Dname)
);
STEP 3: MULTI-COLUMN CONSTRAINT: FOREIGN KEY
• Ref: Elmasari, Navathe, “Fundamentals of Database Systems” 6th edition
STEP 3: MULTI-COLUMN CONSTRAINT: FOREIGN KEY
•
Create Table Employee(
Fname Varchar(15) NOT NULL,
•
Lname Varchar(15)
NOT NULL,
•
Ssn Char(9)
NOT NULL,
Super_ssn char(9),
Dno INT NOT NULL DEFAULT 9,
Primary Key (Ssn),
Foreign Key (Super_ssn) References Employee (Ssn),
Foreign Key (Dno) References Department (Dnumber)
);
Need to be careful while putting the
constraints.
Otherwise data entry becomes hard.
Does this schema create any trouble?
• Circular reference Employee and
Department refer to each other
• Consider adding some constraints
later using ALTER Table command
 Create Table Department(
Dname Varchar(15) NOT NULL,
Dnumber INT
NOT NULL,
Mgr_ssn char(9)
NOT NULL DEFAULT 123459999,
Mgr_start_date Date,
Primary Key (Dnumber),
Unique (Dname),
Foreign Key (Mgr_ssn) References Employee (Ssn)
);
STEP 3: MULTI-COLUMN CONSTRAINT: FOREIGN KEY
Create Table Employee(
Fname Varchar(15) NOT NULL,
Lname Varchar(15)
NOT NULL,
Ssn Char(9)
NOT NULL,
Super_ssn char(9),
Dno INT NOT NULL DEFAULT 9,
Primary Key (Ssn),
Foreign Key (Super_ssn) References Employee (Ssn),
);
Foreign Key (Dno) References Department (Dnumber)
On Delete • SET NULL
On Update
•
•
•
SET DEFAULT
CASCADE
RESTRICT/ NO
ACTION
•
•
•
•
SET NULL
SET DEFAULT
CASCADE
RESTRICT/ NO
ACTION
STEP 3: MULTI-COLUMN CONSTRAINT: FOREIGN KEY (SET NULL/DEFAULT)
Create Table Employee(
Fname Varchar(15) NOT NULL,
Lname Varchar(15)
NOT NULL,
Ssn Char(9)
NOT NULL,
Super_ssn char(9),
Dno INT NOT NULL DEFAULT 1,
Primary Key (Ssn),
Foreign Key (Super_ssn) References Employee (Ssn),
Foreign Key (Dno) References Department (Dnumber)
On Delete SET DEFAULT On Update SET NULL
);
• SET NULL: sets the value of the referenced column to NULL
• SET DEFAULT: Sets the value of the referenced column to the DEFAULT Value
STEP 3: MULTI-COLUMN CONSTRAINT: FOREIGN KEY (SET NULL/DEFAULT)
• What rows in Employee table are modified when Dnumber = 5 is deleted
• What rows in Employee table modified when Dnumber = 1 is changed to Dnumber = 3
STEP 3: MULTI-COLUMN CONSTRAINT: FOREIGN KEY (RESTRICT VS CASCADE)
 Create
Table Employee(
Fname Varchar(15) NOT NULL,
Lname Varchar(15)
NOT NULL,
Ssn Char(9)
NOT NULL,
Super_ssn char(9),
Dno INT NOT NULL DEFAULT 1,
Primary Key (Ssn),
Foreign Key (Super_ssn) References Employee (Ssn)
On Delete RESTRICT On Update CASCADE,
Foreign Key (Dno) References Department (Dnumber)
);
• CASCADE: cascades the effect to tuple containing the foreign key:
• For e.g. When used with on delete, if the tuple containing the referenced key is delete,
then all the tuples with that foreign key are also deleted.
• RESTRICT: Produces an error indicating that the deletion or updating can violate a foreign
key constraint.
STEP 3: MULTI-COLUMN CONSTRAINT: FOREIGN KEY (RESTRICT VS CASCADE)
• What happens when record of Employee ‘Franklin Wong’ is deleted ?
• What happens when SSN of Employee ‘James Borg’ is updated to ‘888665599’ ?
STEP 3: MULTI-COLUMN CONSTRAINT: FOREIGN KEY: EXERCISE
• Ref: Elmasari, Navathe, “Fundamentals of Database Systems” 6th edition
STEP 3: MULTI-COLUMN CONSTRAINT: FOREIGN KEY EXERCISE
 Create Table Department(
Dname Varchar(15) NOT NULL,
Dnumber INT
NOT NULL,
Mgr_ssn char(9)
NOT NULL,
Mgr_start_date Date,
Primary Key (Dnumber),
Unique (Dname),
Foreign Key (Mgr_ssn) References Employee (Ssn)
);
 Create Table Works_on(
Essn char(9) NOT NULL,
Pno INT
NOT NULL,
Hours Decimal (3,1) NOT NULL,
Primary Key (Essn,Pno),
Foreign Key (Essn) References Employee(ssn),
Foreign Key (Pno) Reference Project(Pnumber)
On Delete Cascade On Update Cascade
 Create Table Project(
Pname Varchar(15) NOT NULL,
Pnumber INT
NOT NULL,
);
Plocation Varchar(15),
Dnum INT,
Primary Key (Pnumber),
Foreign Key (Dnum) References Department (Dnumber)
On Delete Cascade On Update Cascade
);
• Identify the affected rows when record with Dnumber =4 is
deleted
Step 4: Business process constraints (Check constraints)
Create Table Employee(
Fname Varchar(15) NOT NULL,
Minit Char,
Lname Varchar(15)
NOT NULL,
Ssn Char(9)
NOT NULL,
Bdate Date,
Address Varchar(30),
Sex Char,
Salary Decimal(10,2),
Super_ssn char(9),
Dno INT NOT NULL DEFAULT 9,
Constraint EMPSAL
Check (Salary > 0.0)
);
 Create Table Department(
Dname Varchar(15) NOT NULL,
Dnumber INT
NOT NULL,
Dept_create_date
Date NOT NULL,
Mgr_ssn char(9)
NOT NULL DEFAULT 123459999,
Mgr_start_date Date,
Constraint MGRDATE
Check (Dept_create_date <= Mgr_start_date)
);
Check constraints for
maintaining the sanity of data
OUTLINE OF THE LECTURE
PART I GOAL: Understand the Data Definition Statements in Fig 4.1

Step1: Columns of the Tables and Data types.

Step2: Single column constraints:
 Not Null; Default and; Unique

Step3: Multi-column constraints:
 Primary key
 Foreign key

Step 4: Business process constraints (Check constraints)
PART II GOAL: Introduction to SQL Language

Unrestricted results
 Unordered or Ordered.
 Projection on certain specific columns of table.

Restricted results using ‘where clause’
Part II: Introduction to SQL Language: Unrestricted Results

“Hello world” example in SQL Language:
SELECT *
<specify the columns to be extracted>
FROM Employee;
<specify the Tables to read> ;
<Optional where clause>
<Optional order by clause>


Outputs the entire contents of the Table Employee
Ordered results:
SELECT *
FROM Employee
ORDER BY Lname ASC;



Outputs the entire contents of Employee ordered (in ascending order) on last name.
DESC -> descending order
Can give multiple column names e.g. ORDER BY Lname ASC, Fname ASC
Part II: Introduction to SQL Language: Unrestricted Results with
Projection

Projected only few columns in SQL Language:
SELECT Ssn, Bdate, Address
FROM Employee;


Outputs the Ssn, Bdate and Address columns of Employee
Ordered results:
SELECT Lname, Ssn, Dno
FROM Employee
ORDER BY Lname ASC;

Outputs the Lname, Ssn and Dno of Employee ordered (in ascending order) on last name.
Part II: Introduction to SQL Language: Restricted Results using where
Clause

Can use where clause to filter some unnecessary results: For instance if we only need the
details of employees from Dept no 5
SELECT *
FROM Employee
WHERE Dno = 5;



<selection condition>
Outputs all the details of employees belonging to Dept no 5
Other logical comparators in SQL: < , <= , >, >= , <>
Use ‘AND’ for conjunction of multiple conditions inside a where clause.
SELECT *
FROM Employee
WHERE Dno = 5 AND Salary >100000;
Part II: Introduction to SQL Language: Restricted Results: Substring
Pattern Matching

Use the ‘LIKE’ comparison operator to specify comparison operations on only
parts of the string.
SELECT Fname, Lname
FROM Employee
WHERE Address LIKE ‘%Minneapolis,MN %’;
Retrieves the employees who live in Minneapolis, MN
 % Arbitrary number of zero or more characters.
 _ Replaces with a single charector.
 \ Escape character for using _ and % as a literal character.
 “ Escape character for single quotation (‘).
