1 / 42

MIS 424 Guest Lecture

MIS 424 Guest Lecture. Welcome. MIS 424 Dr. Steve Ross April 30, 2007. Overview of SQL Server.

kaili
Download Presentation

MIS 424 Guest 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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. MIS 424 Guest Lecture

  2. Welcome

  3. 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

  4. Presentation Outline • Overview of Relational Database Management • Introduction to SQL Server • Application Architecture

  5. Overview of Relational Database Management How do databases “think”?

  6. 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

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

  8. CRUD – SQL Commands • Create = INSERT • Read = SELECT • Update = UPDATE • Delete = DELETE

  9. 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

  10. 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 …)

  11. 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

  12. 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)

  13. Introduction to SQL Server Is it any different from Access?

  14. 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

  15. 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)

  16. Hierarchy of Objects • The Server: RELIANT.CBE.WWU.EDU • The Instance: MIS424S05 or (port #)2767 • The Database: KKPMusicStoreDatabase Objects  • Specific Object:tblCustomers

  17. Security • On the server/instance • Logins • Server Roles • In the database • Users • Database Roles • Permissions • Action • Object

  18. The DBA’s Three Favorite SQL Server Tools • Management Studio Object Explorer

  19. 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!

  20. The DBA’s Three Favorite SQL Server Tools

  21. The DBA’s Three Favorite SQL Server Tools

  22. 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

  23. The DBA’s Three Favorite SQL Server Tools

  24. The DBA’s Three Favorite SQL Server Tools • Books Online • Help facility • Help that’s actually helpful!

  25. Application Architecture How do we get the information to and from the user?

  26. N-Tier Architecture* * Graphic from P.J. Pratt and J.J. Adamski, Concepts of Database Management, 4th Ed.

  27. 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

  28. How much access does the database administrator grant the programming staff? TablesViewsProcedures DBA Programmer

  29. 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

  30. 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

  31. 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

  32. 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

  33. 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.

  34. 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).

  35. 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.

  36. User-Defined Functions • Similar to functions in other languages • Parameters for input • Single result returned • T-SQL programming language • Procedural structure • Sequence • Iteration • Condition

  37. 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.

  38. 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.

  39. 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

  40. 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.

  41. What are your questions?

  42. Thank You for your attention and interest

More Related