Outline of the lecture
This presentation is the property of its rightful owner.
Sponsored Links
1 / 21

OUTLINE OF THE LECTURE PowerPoint PPT Presentation


  • 62 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

OUTLINE OF THE LECTURE

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Outline of the lecture

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

Step 1: Columns of the Tables and Data types

  • Create Table Employee(

    FnameVarchar(15),

    Minit Char,

    LnameVarchar(15),

    Ssn Char(9),

    Bdate Date,

    Address Varchar(30),

    Sex Char,

    Salary Decimal(10,2),

    Super_ssn char(9),

    Dno INT);

  • Create Table Department(

    DnameVarchar(15),

    Dnumber INT,

    Mgr_ssn char(9),

    Mgr_start_date Date);


Step 1 columns of the tables and data types1

Step 1: Columns of the Tables and Data types

  • Create Table Dependent( Essn Char(9),

    Dependent_nameVarchar(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.


Outline of the lecture

Step2: Single column constraints:

  • Create Table Department(

    DnameVarchar(15) NOT NULL,

    Dnumber INT NOT NULL,

    Mgr_ssn char(9) NOT NULL DEFAULT 123459999,

    Mgr_start_date Date,

    Unique (Dname)

    );

  • Create Table Employee(

    FnameVarchar(15) NOT NULL,

    Minit Char,

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

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

Step 3: Multi-column constraint: Primary Key

  • Primary Key Vs Unique Constraint

  • Create Table Employee(

    FnameVarchar(15) NOT NULL,

    LnameVarchar(15) NOT NULL,

    Ssn Char(9) NOT NULL,

    Super_ssn char(9),

    Dno INT NOT NULL DEFAULT 9,

    Primary Key (Ssn)

    );

  • Primary Key can also defined as Unique + Not Null Constraint

  • Many DBMS build an index on Primary Key (main advantage)

  • Create Table Department(

    DnameVarchar(15) NOT NULL,

    Dnumber INT NOT NULL,

    Mgr_ssn char(9) NOT NULL DEFAULT 123459999,

    Mgr_start_date Date,

    Primary Key (Dnumber),

    Unique (Dname)

    );


Outline of the lecture

Step 3: Multi-column constraint: Foreign Key

  • Ref: Elmasari, Navathe, “Fundamentals of Database Systems” 6th edition


Step 3 multi column constraint foreign key

Step 3: Multi-column constraint: Foreign Key

  • Need to be careful while putting the constraints.

  • Otherwise data entry becomes hard.

  • Does this schema create any trouble?

  • Create Table Employee(

    FnameVarchar(15) NOT NULL,

    LnameVarchar(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)

    );

  • Circular reference Employee and Department refer to each other

  • Consider adding some constraints later using ALTER Table command

  • Create Table Department(

    DnameVarchar(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)

    );


Outline of the lecture

Step 3: Multi-column constraint: Foreign Key

  • Create Table Employee(

    FnameVarchar(15) NOT NULL,

    LnameVarchar(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 On Update

    );

  • SET NULL

  • SET DEFAULT

  • CASCADE

  • RESTRICT/ NO ACTION

  • SET NULL

  • SET DEFAULT

  • CASCADE

  • RESTRICT/ NO ACTION


Outline of the lecture

Step 3: Multi-column constraint: Foreign Key (Set null/default)

  • Create Table Employee(

    FnameVarchar(15) NOT NULL,

    LnameVarchar(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


Outline of the lecture

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


Outline of the lecture

Step 3: Multi-column constraint: Foreign Key (restrict vs cascade)

  • Create Table Employee(

    FnameVarchar(15) NOT NULL,

    LnameVarchar(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.


Outline of the lecture

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’ ?


Outline of the lecture

Step 3: Multi-column constraint: Foreign Key: Exercise

  • Ref: Elmasari, Navathe, “Fundamentals of Database Systems” 6th edition


Outline of the lecture

Step 3: Multi-column constraint: Foreign Key Exercise

  • Create Table Department(

    DnameVarchar(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(

    PnameVarchar(15) NOT NULL,

    Pnumber INT NOT NULL,

    PlocationVarchar(15),

    Dnum INT,

    Primary Key (Pnumber),

    Foreign Key (Dnum) References Department (Dnumber)

    On Delete Cascade On Update Cascade

    );


Outline of the lecture

  • Identify the affected rows when record with Dnumber =4 is deleted


Step 4 business process constraints check constraints

Step 4: Business process constraints (Check constraints)

  • Create Table Department(

    DnameVarchar(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)

    );

  • Create Table Employee(

    FnameVarchar(15) NOT NULL,

    Minit Char,

    LnameVarchar(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)

    );

Check constraints for maintaining the sanity of data


Outline of the lecture1

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

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

    • 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

Part II: Introduction to SQL Language: Unrestricted Results with Projection

  • Projected only few columns in SQL Language:

    SELECTSsn, Bdate, Address

    FROM Employee;

    • Outputs the Ssn, Bdate and Address columns of Employee

  • Ordered results:

    SELECTLname, Ssn, Dno

    FROM Employee

    ORDER BY LnameASC;

    • 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

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

    WHEREDno = 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

      WHEREDno = 5 AND Salary >100000;


Part ii introduction to sql language restricted results substring pattern matching

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.

    SELECTFname, 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 (‘).


  • Login