Ie 423 design of decision support systems
This presentation is the property of its rightful owner.
Sponsored Links
1 / 53

IE 423 – Design of Decision Support Systems PowerPoint PPT Presentation


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

IE 423 – Design of Decision Support Systems. Database development – Relationships and Queries Introduction to SQL. Important Events. Back from Spring Break (hopefully) Welcome Back. Keepin Up. Read Pol and Ahuja – Chapter 8 (should have already done this)

Download Presentation

IE 423 – Design of Decision Support Systems

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


Ie 423 design of decision support systems

IE 423 – Design of Decision Support Systems

Database development –

Relationships and Queries

Introduction to SQL


Important events

Important Events

Back from Spring Break (hopefully)

Welcome Back


Keepin up

Keepin Up

  • Read

    • Pol and Ahuja – Chapter 8 (should have already done this)

    • Pol and Ahuja – Chapter 9 – SQL

    • Pol and Ahuja – Chapter 10 – Visual Studio


Ie 423 design of decision support systems

SQL

SQL – Structured Query Language

Standard language for managing and manipulating Relational Databases and Relational DataBase Management Systems

RDBMS?

Standard – because it is widely used in RDBMSs

…but not in all


Ie 423 design of decision support systems

SQL

SQL – used in

MS Access (behind the “curtain”)

MS SQL Server

Oracle

DB2

MySQL

PostGres


Ie 423 design of decision support systems

SQL

SQL – standard, but…

Some variations in SQL across platforms

Usually syntactic differences

Function differences

Subsets

Arguments


Ie 423 design of decision support systems

SQL

SQL – Important Point!

SQL is a database management language

SQL is not a programming language

What does this mean?

Why does this matter?

What can we do about this?


Ie 423 design of decision support systems

SQL

SQL – four components to the language

DDL – Data Definition Language

Define/delete db objects

DML – Data Manipulation Language

Language for using db

DCL – Data Control Language

Defines/sets control features of db

DSPL – Data Stored Procedure Language

Tools for creating SQL modules


Ie 423 design of decision support systems

SQL

SQL – four components to the language

DCL and DSPL – outside the scope of this class (mostly)

DDL and DML – we will take a closer look at these two languages


Ie 423 design of decision support systems

SQL

SQL – four components to the language

DCL and DSPL – outside the scope of this class (mostly)

DDL and DML – we will take a closer look at these two languages


Ie 423 design of decision support systems

DDL

DDL – Data Definition Language

Create

TABLES

PRIMARY KEYS

FOREIGN KEYS

INDEXES


Ie 423 design of decision support systems

DDL

CREATE tables

CREATE TABLE [tblName]

({<fieldName> <datatype> <constraints>},

{},…)

CONSTRAINT {<keyname> PRIMARY KEY (fieldname1, fieldname2,…)

{}… ;


Ie 423 design of decision support systems

DDL

CREATE tables

CREATE TABLE tblDepartment

(DeptIDVARCHAR2 (10) NOT NULL,

NameVARCHAR2 (20),

Address VARCHAR2 (200),

PhoneNUMBER (30),

CollegeIDVARCHAR2(3) )

CONSTRAINT deptPK PRIMARY KEY (DeptID),

deptFK FOREIGN KEY (CollegeID)

REFERENCES tblCollege (CollegeID)

;


Ie 423 design of decision support systems

DDL

CREATE tables

Standard SQL datatypes

VARCHAR(maxbytes)

-- TEXT same in MySQL

Max maxbytes – Oracle = 4000; SQL Server=8000; MySQL=65,532 (see docs for specific DB platform

DECIMAL(precision, scale) (optional)

Numeric base10 to a specific precision and scale (number of fractional decimal positions)

DECIMAL(9,2) could store 9,999,999.99

DATE (some variation across DB platforms)

DATE

TIME

TIMESTAMP


Ie 423 design of decision support systems

DDL

CREATE tables

Standard SQL datatypes

SMALLINT

2 bytes

-32,768 to 32,767

INTEGER

4 bytes

-2,147,483,648 to a 2,147,483,647

BIGINT

8 bytes

-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807


Ie 423 design of decision support systems

DDL

CREATE Indexes

CREATE INDEX tblDeptAddressIndex

ON tblDepartment (Address)

;


Ie 423 design of decision support systems

DDL

DDL – Data Definition Language

DROP – deletes db objects

DROP TABLE – deletes a table (and its data) from a db

…not used to drop a field from a table or a record from a table


Ie 423 design of decision support systems

DDL

DDL – Data Definition Language

DROP TABLE [tblName];

DROP TABLE tblInstructors;

DROP INDEX [tblNameindex]

DROP INDEX tblStudentIndex;


Ie 423 design of decision support systems

DDL

DDL – Data Definition Language

ALTER – modifies db objects

Add a field to a table

ALTER tblDepartment

ADD COLUMN Chair Number (10)

ADD CONSTRAINT deptFK2 FOREIGN KEY (Chair)

REFERENCES tblFaculty(FacultyID) ;


Ie 423 design of decision support systems

DDL

DDL – Data Definition Language

ALTER – modifies db objects

Delete a field from a table

ALTER tblDepartment

DROP Chair

DROP CONSTRAINT DeptFK2 ;


Ie 423 design of decision support systems

DML

DML – Data Manipulation Language

Language to use DB

Language to do queries and other DB functions

Why do you need to know this?


Ie 423 design of decision support systems

DML

Select Queries

SELECT [fieldName, fieldName2,…]

FROM [tblName];

SELECT Name, Phone

FROM tblDept;

SELECT * FROM tblStudent;


Ie 423 design of decision support systems

DML

Select Queries - JOIN

SELECT [fieldName, fieldName2,…]

FROM [tblName1, tblName2, tblName3,…];

SELECT Name, Email, DeptName

FROM tblFaculty tblDept;

Simple Query with Join

But what does this do?


Ie 423 design of decision support systems

DML

Select Queries – JOIN

SELECT Name, Email, DeptName

FROM tblFaculty tblDept;

Simple Query with Join

But what does this do?

Cartesian Product

What’s that?


Ie 423 design of decision support systems

DML

Select Queries – JOIN and the WHERE clause

SELECT Name, Email, DeptName

FROM tblFaculty tblDept

WHERE tblFaculty.DeptID=tblDept.DeptID;


Ie 423 design of decision support systems

DML

Select Queries – JOIN and the WHERE clause

How about this – Name in tblFaculty, Name in tblDept

SELECT Name, Email, Phone, Name

FROM tblFaculty tblDept

WHERE tblFaculty.DeptID=tblDept.DeptID;

Two fields in query with same identifier?

Oh my!


Ie 423 design of decision support systems

DML

Select Queries – JOIN and the WHERE clause

Use a qualifier – fully specify field name using its table name

SELECT tblFaculty.Name, Email, Phone, tblDept.Name

FROM tblFaculty tblDept

WHERE tblFaculty.DeptID=tblDept.DeptID;


Ie 423 design of decision support systems

DML

Select Queries – JOIN and the WHERE clause

WHERE clause operators –

= < <= > >= !=

BETWEEN, LIKE, IN

AND, OR, NOT


Ie 423 design of decision support systems

DML

Select Queries – JOIN and the WHERE clause

SELECT Name, Email, Salary, StartDate, tblDept.Name

FROM tblFaculty, tblDept

WHERE tblFaculty.DeptID=tblDept.DeptID AND

(Salary > 80000) AND

(StartDate > #1/1/1995#);


Ie 423 design of decision support systems

DML

Select Queries – JOIN and the WHERE clause

BETWEEN – matching a range

SELECT Name, Email, Salary, StartDate, tblDept.Name

FROM tblFaculty, tblDept

WHERE tblFaculty.DeptID=tblDept.DeptID AND

(Salary BETWEEN 70000 AND 80000) AND

(StartDate > #1/1/1995#);


Ie 423 design of decision support systems

DML

Select Queries – JOIN and the WHERE clause

BETWEEN – matching a range

SELECT Name, Email, Salary, StartDate, tblDept.Name

FROM tblFaculty, tblDept

WHERE tblFaculty.DeptID=tblDept.DeptID AND

(Salary BETWEEN 70000 AND 80000) AND

(StartDate > #1/1/1995#);


Ie 423 design of decision support systems

DML

Select Queries – JOIN and the WHERE clause

LIKE – matching a string or part of one

SELECT Name, Email, Rank

FROM tblStudent, tblRegister, tblCourses

WHERE (tblStudent.SID=tblRegister.StudentID)

AND (tblRegister.CourseID=tblCourses.CourseID)

AND (tblCourses.Cname LIKE “Cooking*”

OR tblCourses.Cname LIKE “Food*” );


Ie 423 design of decision support systems

DML

Select Queries – JOIN and the WHERE clause

LIKE – matching a string or part of one

LIKE match operators

* - zero or more characters

? – zero or one character

% - any number of characters ( %science% )

_ - any one character


Ie 423 design of decision support systems

DML

Select Queries – JOIN and the WHERE clause

Using aliases AS -- lets your rename objects

Within the query

Alias does not persist

SELECT Name, Email, Rank

FROM tblStudent AS tS, tblRegister AS tR, tblCourses AS tC

WHERE (tS.SID=tR.StudentID)

AND (tR.CourseID=tC.CourseID)

AND (tC.Cname LIKE “Cooking*”

OR tC.Cname LIKE “Food*” );


Ie 423 design of decision support systems

DML

Select Queries – JOIN and the WHERE clause

Using aliases AS -- lets your rename objects

Within the query

Alias does not persist

SELECT CONCAT(FirstName,” “,LastName) AS FullName, Email, Rank

FROM tblStudent;


Ie 423 design of decision support systems

DML

Select Queries – JOIN and the WHERE clause

Sometimes we only want one result per entity


Ie 423 design of decision support systems

DML

Select Queries – JOIN and the WHERE clause

Remember this?

What would we get as a result?


Ie 423 design of decision support systems

DML

Select Queries – JOIN and the WHERE clause

Remember this?

What would we get as a result?

SELECT Name, Email, Rank

FROM tblStudent AS tS, tblRegister AS tR, tblCourses AS tC

WHERE (tS.SID IN

SELECT DISTINCT StudentID

FROM tR WHERE tR.CourseID IN

SELECT CourseID FROM tC

WHERE (tC.Cname LIKE “Cooking*”

OR tC.Cname LIKE “Food*” );


Ie 423 design of decision support systems

DML

Select Queries – JOIN and the WHERE clause

The keywork DISTINCT causes only one record per criteria match to be returned

SELECT Name, Email, Rank

FROM tblStudent AS tS, tblRegister AS tR, tblCourses AS tC

WHERE (tS.SID IN

SELECT DISTINCT StudentID

FROM tR WHERE tR.CourseID IN

SELECT CourseID FROM tC

WHERE (tC.Cname LIKE “Cooking*”

OR tC.Cname LIKE “Food*” );


Ie 423 design of decision support systems

DML

Select Queries – JOIN and the WHERE clause

Did you notice something else new?

The keyword IN

SELECT Name, Email, Rank

FROM tblStudent AS tS, tblRegister AS tR, tblCourses AS tC

WHERE (tS.SID IN

SELECT DISTINCT StudentID

FROM tR WHERE tR.CourseID IN

SELECT CourseID FROM tC

WHERE (tC.Cname LIKE “Cooking*”

OR tC.Cname LIKE “Food*” );


Ie 423 design of decision support systems

DML

Select Queries – JOIN and the WHERE clause

Did you notice something else new?

The keyword IN

Allows you to nest queries in queries


Ie 423 design of decision support systems

DML

Select Queries – Ordering the results

Use the ORDER BY clause

SELECT [fields]

FROM [tables]

WHERE [select_criteria]

ORDER BY [field1 <ASC, DESC>;


Ie 423 design of decision support systems

DML

Select Queries – Ordering the results

Use the ORDER BY clause

SELECT Name, Email, Phone, StartDate

FROM tblFaculty

ORDER BY StartDate ASC;

SELECT Name, Email, Phone, StartDate

FROM tblFaculty

ORDER BY StartDate DESC, Name ASC;


Ie 423 design of decision support systems

DML

Select Queries – Summarizing data – getting some stats

The GROUP BY clause

SELECT tblDept.Name, COUNT(tblStudent.SID) AS StudentCount),

AVG(tblStudent.GPA) AS MeanGPA

FROM tblStudent, tblDept

WHERE tblStudent.DeptID = tblDept.DeptID

AND (tblDept.College=“CEMR”)

GROUP BY tblDept.Name;


Ie 423 design of decision support systems

DML

Select Queries – Summarizing data – getting some stats

The HAVING clause, like WHERE but for GROUP BY

SELECT tblDept.Name, COUNT(tblStudent.SID) AS StudentCount),

AVG(tblStudent.GPA) AS MeanGPA

FROM tblStudent, tblDept

WHERE tblStudent.DeptID = tblDept.DeptID

AND (tblDept.College=“CEMR”)

GROUP BY tblDept.Name

HAVING COUNT(tblStudent.SID >25);


Ie 423 design of decision support systems

DML

Action Queries

INSERT Queries

DELETE Queries

UPDATE Queries


Ie 423 design of decision support systems

DML

Action Queries

INSERT Queries

INSERT INTO [table]

(field1, field2, …)

Values(“value1”, value2,…);


Ie 423 design of decision support systems

DML

Action Queries

INSERT Queries

INSERT INTO tblStudent

(Name, Age, Email)

Values(“Bob Smith”, 21, [email protected]);

Order is important

Datatypes must match


Ie 423 design of decision support systems

DML

Action Queries

DELETE Queries

Deletes records from a table

DELETE FROM [table]

WHERE [delete_criteria];


Ie 423 design of decision support systems

DML

Action Queries

DELETE Queries

Deletes records from a table

DELETE FROM tblStudent

WHERE Rank=“Senior”;


Ie 423 design of decision support systems

DML

Action Queries

Update Queries

UPDATE [table]

SET [field=value, field=value, …]

WHERE [update_criteria];


Ie 423 design of decision support systems

DML

Action Queries

Update Queries

UPDATE tblFaculty

SET Salary = Salary *1.01

WHERE StartDate < 1/1/1920;


  • Login