Ie 423 design of decision support systems
Download
1 / 53

IE 423 – Design of Decision Support Systems - PowerPoint PPT Presentation


  • 67 Views
  • Uploaded on

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)

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'IE 423 – Design of Decision Support Systems' - meli


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


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


SQL

SQL – used in

MS Access (behind the “curtain”)

MS SQL Server

Oracle

DB2

MySQL

PostGres


SQL

SQL – standard, but…

Some variations in SQL across platforms

Usually syntactic differences

Function differences

Subsets

Arguments


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?


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


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


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


DDL

DDL – Data Definition Language

Create

TABLES

PRIMARY KEYS

FOREIGN KEYS

INDEXES


DDL

CREATE tables

CREATE TABLE [tblName]

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

{},…)

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

{}… ;


DDL

CREATE tables

CREATE TABLE tblDepartment

(DeptID VARCHAR2 (10) NOT NULL,

Name VARCHAR2 (20),

Address VARCHAR2 (200),

Phone NUMBER (30),

CollegeID VARCHAR2(3) )

CONSTRAINT deptPK PRIMARY KEY (DeptID),

deptFK FOREIGN KEY (CollegeID)

REFERENCES tblCollege (CollegeID)

;


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


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


DDL

CREATE Indexes

CREATE INDEX tblDeptAddressIndex

ON tblDepartment (Address)

;


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


DDL

DDL – Data Definition Language

DROP TABLE [tblName];

DROP TABLE tblInstructors;

DROP INDEX [tblNameindex]

DROP INDEX tblStudentIndex;


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


DDL

DDL – Data Definition Language

ALTER – modifies db objects

Delete a field from a table

ALTER tblDepartment

DROP Chair

DROP CONSTRAINT DeptFK2 ;


DML

DML – Data Manipulation Language

Language to use DB

Language to do queries and other DB functions

Why do you need to know this?


DML

Select Queries

SELECT [fieldName, fieldName2,…]

FROM [tblName];

SELECT Name, Phone

FROM tblDept;

SELECT * FROM tblStudent;


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?


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?


DML

Select Queries – JOIN and the WHERE clause

SELECT Name, Email, DeptName

FROM tblFaculty tblDept

WHERE tblFaculty.DeptID=tblDept.DeptID;


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!


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;


DML

Select Queries – JOIN and the WHERE clause

WHERE clause operators –

= < <= > >= !=

BETWEEN, LIKE, IN

AND, OR, NOT


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#);


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#);


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#);


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*” );


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


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*” );


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;


DML

Select Queries – JOIN and the WHERE clause

Sometimes we only want one result per entity


DML

Select Queries – JOIN and the WHERE clause

Remember this?

What would we get as a result?


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*” );


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*” );


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*” );


DML

Select Queries – JOIN and the WHERE clause

Did you notice something else new?

The keyword IN

Allows you to nest queries in queries


DML

Select Queries – Ordering the results

Use the ORDER BY clause

SELECT [fields]

FROM [tables]

WHERE [select_criteria]

ORDER BY [field1 <ASC, DESC>;


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;


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;


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


DML

Action Queries

INSERT Queries

DELETE Queries

UPDATE Queries


DML

Action Queries

INSERT Queries

INSERT INTO [table]

(field1, field2, …)

Values (“value1”, value2,…);


DML

Action Queries

INSERT Queries

INSERT INTO tblStudent

(Name, Age, Email)

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

Order is important

Datatypes must match


DML

Action Queries

DELETE Queries

Deletes records from a table

DELETE FROM [table]

WHERE [delete_criteria];


DML

Action Queries

DELETE Queries

Deletes records from a table

DELETE FROM tblStudent

WHERE Rank=“Senior”;


DML

Action Queries

Update Queries

UPDATE [table]

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

WHERE [update_criteria];


DML

Action Queries

Update Queries

UPDATE tblFaculty

SET Salary = Salary *1.01

WHERE StartDate < 1/1/1920;


ad