420 likes | 579 Views
MIS 424 Guest Lecture. Welcome. MIS 424 Dr. Steve Ross April 30, 2007. Overview of SQL Server.
E N D
MIS424Dr. Steve RossApril 30, 2007 Overview of SQL Server Material for this lecture is drawn from SQL Server 2005 Database Essentials, SQL Queries for Mere Mortals, and the professor’s experience.Star Wars sounds from http://www.galaxyfaraway.com/Multimedia/sounds.shtml
Presentation Outline • Overview of Relational Database Management • Introduction to SQL Server • Application Architecture
Overview of Relational Database Management How do databases “think”?
What is a Relational Database Management System? • Software • Manages storage and retrieval of data • Stored in two-dimensional tables • Three major parts • Data definition language (DDL) • Creation of the database structure • Data manipulation language (DML) • CRUD • Data control language (DCL) • Controls access to the data
CRUD? • Create new records • Read the data in existing records • Update the data in existing records • Delete records • A “complete” business application must accomplish the full range of CRUD on all fields of all tables.
CRUD – SQL Commands • Create = INSERT • Read = SELECT • Update = UPDATE • Delete = DELETE
Sets • Tables are treated as sets • A command deals with the entire set or a portion thereof determined by a filter • No inherent order unless indexed • Record-by-record processing requires special methods • The result of a SELECT command is another set
Relating Data – Joins • Field(s) in one table compared to field(s) in another • Inner join … records with matching values • Left/Right outer join … all records of one paired with records of the other with matching values • Full outer join … all records of both • Joins are normally based on equal values, but may be based on any relation between the values of the fields (e.g., <, >, between …)
Sample Query SELECT S.TERM,S.SUBJECT,S.COURSE_NUMBER,FIRST_NAME + ' ' + LAST_NAME AS S_NAME FROM dbo.S_SCHEDULE S INNER JOIN dbo.S_HIST_DETAIL HD ON S.TERM=HD.TERM AND S.CRN=HD.CRN INNER JOIN dbo.G_PERSON P ON HD.PIDM=P.PIDM INNER JOIN dbo.S_REG_DETAIL RD ON RD.PIDM=P.PIDM WHERE S.PRIMARY_INSTRUCTOR_PIDM=5120 AND RD.TERM='200720' AND RD.CRN='21070' UNION SELECT S.TERM,S.SUBJECT,S.COURSE_NUMBER,FIRST_NAME + ' ' + LAST_NAME AS S_NAME FROM dbo.S_SCHEDULE S INNER JOIN dbo.S_REG_DETAIL RD0 ON S.TERM=RD0.TERM AND S.CRN=RD0.CRN INNER JOIN dbo.G_PERSON P ON RD0.PIDM=P.PIDM INNER JOIN dbo.S_REG_DETAIL RD ON RD.PIDM=P.PIDM WHERE S.PRIMARY_INSTRUCTOR_PIDM=5120 AND RD.TERM='200720' AND RD.CRN='21070' ORDER BY S.SUBJECT,S.COURSE_NUMBER,S.TERM
Result TERM SUBJECT COURSE_NUMBER S_NAME ------ ------- ------------- --------------------- 200540 MIS 320 Hyun Park 200640 MIS 421 Hyun Park 200710 MIS 421 Alexander Nichols 200710 MIS 421 Daniel Stead 200720 MIS 421 Adam Opitz 200720 MIS 431 Alexander Nichols (6 row(s) affected)
Introduction to SQL Server Is it any different from Access?
SQL Server • Microsoft Product • Originally developed by Sybase and purchased by Microsoft • “Recent” version: SQL Server 2000 • “Current” version: SQL Server 2005 • A.k.a. Yukon • Integrated with Whidbey – next version of Visual Studio
What about Microsoft Access? • Both are DBMS • Both allow a lot of records • SQL-Server • More power in DDL, DML, DCL • Handles more data and more concurrent users • Access • Less expensive • More readily available • Includes user interface tools (forms and reports)
Hierarchy of Objects • The Server: RELIANT.CBE.WWU.EDU • The Instance: MIS424S05 or (port #)2767 • The Database: KKPMusicStoreDatabase Objects • Specific Object:tblCustomers
Security • On the server/instance • Logins • Server Roles • In the database • Users • Database Roles • Permissions • Action • Object
The DBA’s Three Favorite SQL Server Tools • Management Studio Object Explorer
The DBA’s Three Favorite SQL Server Tools • Management Studio Object Explorer • Graphical interface that allows • Creation of database objects (DDL) • Tables • Views • Stored procedures • User-defined functions • Specification of rights (DCL) • Logins • Server roles • Database roles • Allows direct editing of database data • Usually a bad idea!
The DBA’s Three Favorite SQL Server Tools • Management Studio Query • Object browser • Command line interface • Create and test statements • Execute statements – DDL, DML, DCL • Execution plan
The DBA’s Three Favorite SQL Server Tools • Books Online • Help facility • Help that’s actually helpful!
Application Architecture How do we get the information to and from the user?
N-Tier Architecture* * Graphic from P.J. Pratt and J.J. Adamski, Concepts of Database Management, 4th Ed.
Primary Concern of the DBA:Maintaining Data Integrity • Protect existence • Data are available when needed • Maintain quality • Data are accurate, complete, and current • Ensure confidentiality • Data are accessed only by those authorized to do so
How much access does the database administrator grant the programming staff? TablesViewsProcedures DBA Programmer
Maintaining Integrity in Tables • Field data type, size • Check constraints • Entity integrity (primary key) • Referential integrity (foreign key) • Triggers • Procedures that execute on insert, update, or delete actions on the table
Using Views to Maintain Integrity (In Access, views are called queries) • Specify only certain fields • Output fields can be renamed to “hide” internal structure • Filter the records • Conditions can be based on characteristics of the user, e.g., his/her ID, department, status
Stored Procedures • Similar to sub-routines in other languages • Provide all aspects of CRUD, most DDL, DCL • Very good way to return result sets • Transact-SQL (T-SQL) programming language • Procedural structure • Sequence • Iteration • Condition
Stored Procedures cont’d • Input and output parameters provide a consistent interface between programmer and database administrator • Facilitates data structure changes • Allow a great deal of internal error-checking and validation
Stored Procedure Example I CREATE PROCEDURE dbo.uspInsertNewPOS ( @WWUID nchar(10), @CurrID int, @StartQtr int, @Quarter int, @CourseAbbr nchar (4), @CourseNumb nchar(4), @OtherCrs nvarchar(20), @ElectSet smallint, @ElectCrs smallint, @CourseCategory nchar(2) ) AS SET NOCOUNT ON INSERT INTO tblStuPOS (WWUID,CurrID,StartQtr,ElectSet,ElectCrs,CourseAbbr,CourseNumb,Quarter,OtherCrs,CourseCategory) VALUES ( @WWUID, @CurrID, @StartQtr, @ElectSet, @ElectCrs, @CourseAbbr, @CourseNumb, @Quarter, @OtherCrs, @CourseCategory ) SET NOCOUNT OFF GO This procedure accepts input data via parameters and inserts it into a record in a table.
Stored Procedure Example II CREATE PROCEDURE dbo.uspFoundationCourses ( @CurrID int, @WWUID nchar(10) ) AS SET NOCOUNT ON SELECT * INTO #tblSelectedStuPOS FROM tblStuPOS WHERE WWUID=@WWUID SELECT C.CourseAbbr, C.CourseNumb, C.CurrID, C.DeptAbbr, C.DegreeName, C.Concentration,C.Requirement, isnull(P.Quarter,999999) as Quarter FROM dbo.vueCurrCourses C LEFT OUTER JOIN #tblSelectedStuPOS P ON C.CourseAbbr=P.CourseAbbr AND C.CourseNumb = P.CourseNumb WHERE (C.Requirement='FD') and C.CurrID=@CurrID ORDER BY C.CourseAbbr, C.CourseNumb SET NOCOUNT OFF GO This procedure creates a temporary table containing an individual’s data and outputs a list of “foundation” courses the individual must take for a specific major (CurrID).
Stored Procedure Example III CREATE PROCEDURE dbo.uspDeleteOldPOS ( @WWUID nchar(10) ) AS SET NOCOUNT ON DELETE FROM tblStuPOS WHERE WWUID=@WWUID SET NOCOUNT OFF GO This procedure accepts a WWU ID number via a parameter and deletes corresponding records in the table.
User-Defined Functions • Similar to functions in other languages • Parameters for input • Single result returned • T-SQL programming language • Procedural structure • Sequence • Iteration • Condition
User-Defined Function Example I CREATE FUNCTION dbo.fnPassedCourse ( @dgrade float, @grade nvarchar(3) ) RETURNS bit AS BEGIN DECLARE @output bit IF @dgrade > 1.5 or upper(@grade) = 'S' SET @output = 1 ELSE SET @output = 0 RETURN @output END This function returns a 1 if the person received a grade of S, C– or better, a 0 otherwise. The actual grade is passed to the function, but concealed in output.
User-Defined Function Example II CREATE FUNCTION dbo.fnLatestDepartment ( @Person2Edit CHAR(9) ) RETURNS nvarchar(4) AS BEGIN DECLARE @output nvarchar (4) SELECT @output = DeptAbbr FROM dbo.tblAppointment WHERE PersonID=@Person2Edit AND TermCode = (SELECT MAX(TermCode) FROM dbo.tblAppointment WHERE PersonID = @Person2Edit) RETURN @output END Given a professor’s WWU ID, this function returns the abbreviation of the department associated with his or her most recent appointment.
Triggers • Attached to a table • “Fires” on insert, update, or delete • Able to access • Old (deleted or updated) values • New (inserted or updated) values • Trigger can reference and change other tables
Trigger Examples CREATE TRIGGER ut_CreateSale ON [dbo].[tblSaleItem] FOR INSERT, UPDATE AS UPDATE vueProductInventory SET QuantityOnHand = QuantityOnHand - (SELECT QuantitySold FROM INSERTED) WHERE ProductCode = (SELECT ProductCode FROM INSERTED) CREATE TRIGGER ut_DeleteSale ON [dbo].[tblSaleItem] FOR UPDATE, DELETE AS UPDATE vueProductInventory SET QuantityOnHand = QuantityOnHand + (SELECT QuantitySold FROM DELETED) WHERE ProductCode = (SELECT ProductCode FROM DELETED) These triggers change a quantity-on-hand field in another table whenever a sale is inserted, updated, or deleted.