Powerpoints for final
Order Transaction Stored Procedure
Concepts to learn from this stored procedure ´INPUT and OUTPUT parameters for stored procedures ´TRANSACTTION—multiple modification DML statements are treated as one unit and all involved DML statements are either all completed or revoked to avoid invalid data states ´BEGIN TRANSACTION ´COMMIT/ROLLBACK TRANSACTION ´TRY...CATCH ´A TRY...CATCH construct catches all execution errors that have a severity level higher than 10 that do not close the database connection ´Security levels 0-10 informational messages ´Security levels 11-16—users can correct the related error ´Security levels 17-24—Users can't correct (informational) ´Use Error_Message() and Error_Severity() methods ´RETURN: exit from the procedure àthe rest of the code won't be executed ´Use global variables ´ ´
User-Defined Functions IIIThe GetLastName Function
Create FUNCTION dbo.ufnGetLastName (@fullname varchar(100)) returns varchar(50) AS BEGIN --declare the local variable: lastName --declare the index variable to find the index of the separator that separates last name from first name --get the separator index value --check if the default separator (,) exists --if it does, use the substring function to find the last name --if it does not, let's assume the space is the separator and the full name format is FirstName LastName --find the index for the space, then find the last name --return the last name END
Update LastName Trigger
--A trigger that will get the last name from the --inserted fullname and update the LastName field CREATE TRIGGER dbo.utrLastnameAfterInsertTblPersonNames ON dbo.tblPersonNames AFTER INSERT AS BEGIN --Declare variables --Get the inserted fullname value --Get the lastname using the function --Check if the fullname and lastname are provided --Based on the status of checking, update the lastName field END
Order Transaction Stored Procedure Test Scripts
/** Test the uspPersistentTran stored procedure with the following parameters: @sku int, @orderNum int, @warehouseID int, @quantity int, @price float, @extendedPrice float, @orderTotal float, @result int OUTPUT, @message varchar(100) OUTPUT */ select * from tblUSPSkuData select * from tblUSPInventory select * from tblUSPRetailOrder select * from tblUSPOrderItem select * from tblUSPInventory where WarehouseID=100 and SKU=201000; --Not available, SKU does not exist in the warehouse #100 Declare @result int, @message varchar(100) --parameters: @sku, @orderNum, @warehouseID, @quantity, @price, @extendedPrice, @orderTotal, @result, @message execute uspPersistentTran 201000, 311, 100, 100, 55.5, 5550, 500, @result OUTPUT, @message OUTPUT SELECT @result SELECT @message See the complete the script in the stored procedure video example assignment on Canvas
Update LastName Stored Procedure
/** Write a stored procedure to update the LastName and FirstName columns if the FullName column has a value in a record */ CREATE PROCEDURE dbo.uspUpdateLastFirstName AS BEGIN --DECLARE variables --DECLARE a cursor --OPEN the cursor --FETCH NEXT FROM the cursor --Process the records and update the LastName and FirstName fields: --If the FullName from a row is NOT null, but the LastName is NULL, extract the --last name from the fullname, then update the LastName column of the row. --Do the same for the FirstName column. --CLOSE the cursor; --DEALLOCATE the cursor; END
Create User Login Stored Procedure
/**This procedure is used to create class logins for a MIS 421 class. The Username should be stored in the ClassUser table. The Usernames can be downloaded from Canvas and imported into the ClassUser table that contains at least a Username field.*/ CREATE PROCEDURE uspCreateClassLogins AS BEGIN DECLARE @username varchar(100), @wdn char(3); DECLARE @sqlstmt varchar(200); set @wdn='WWU'; DECLARE un CURSOR for (select Username from dbo.ClassUser); OPEN un; FETCH NEXT FROM un INTO @username; WHILE @@FETCH_STATUS = 0 BEGIN Set @username = @wdn + '\' + @username; --Create LOGIN @username from WINDOWS; Set @sqlstmt = 'Create LOGIN [' + @username + '] from WINDOWS'; PRINT @sqlstmt; EXEC (@sqlstmt); --Add the user to the dbcreator server fixed role Set @sqlstmt = 'sp_addsrvrolemember [' + @username + '], ''dbcreator'''; PRINT @sqlstmt; EXEC (@sqlstmt); FETCH NEXT FROM un INTO @username; END CLOSE un; DEALLOCATE un; END
practice test
1. What is the purpose of the SQL Statement in Figure 1? NOTE: this includes by everything included in the SQL statement! e) To create the tblCommitteetableb.To create a surrogate key named CommitteeIDc.To create a primary key named CommitteeID 2. Besides the primary key in ARTIST, is there another candidate key (alternate key)in the table? C) Yes—(CommitteeID, CommitteeType) 3. Is(are) there any foreign key(s) in the table? e) No 4. What is the purpose of the tblCommitteeAKconstraintin the SQL Statement in Figure 1? a) To make sure that (CommitteeID, CommitteeType) is unique in the table data. 5. Is there a composite foreign key in tblSupportCommittee table in Figure 2? a) yes 6. For the tables tblCommitteeand tblSupportCommittee as defined in Figure 1 and 2,respectively, if we have a record in tblCommittee table with the values (1, 'Executive', 'S') for the columns (CommitteeID, CommitteeName, CommitteeType), respectively,and a record in tblSupportCommittee table with the values (1, 'S', 'Mayor', 1) for the columns (CommitteeID, CommitteeType, CityGovernmentContact), respectively, will this statement delete the record from tblCommitteesuccessfully: DELETE FROM tblCommitteeWHERE CommitteeID=1? b) No 7. What is the purpose of the ON UPDATE CASCADE in the SQL phrase in Figure 2? b) To make sure changes to any primary key value of the tblCommittee table are also made in the tblSupportCommittee table. 8. Given that we have created a tblCommitteetable with the columns shown in Figure 3where CommitteeIDis a surrogate primary key and only CommitteeID and CommitteeType do NOT take NULL, which INSERT statement is correct SQL statement or a set of SQL statements to add the data for Blossom Parad eto the tblCommitteetable(Assume that we don't have any data in the table)? b) SETIDENTITY_INSERTdbo.tblCommitteeONINSERTINTOtblCommittee(CommitteeID, CommitteeName, CommitteeType)VALUES(1,'Blossom Parade','S');SETIDENTITY_INSERTdbo.tblCommittee OFF 9. To empty the data of a table that has an identity primary key and the primary key is not referred to by a foreign key in any other tables, what SQL statement do you use if you do NOT want to reset the identity key values in the table? d) DELETE FROM {TableName} 10. When will the commands in questions 9 fail to operate? b) When the table is referenced in one or more referential integrity constraints 11. For Microsoft SQL Server, suppose that the SQL View vueNumOfCommitteePosition is defined as shown in Figure 4. The purpose of this view is most likely: To display the results of computations. To layer built-in functions. To provide isolation between table data and a user's view of the data d) .All of the above 12. Anon-clustered index is the one that c) has a structure separate from table data rows 13. For Microsoft SQL Server, suppose that the SQL View vueGeneralCommitteeInfo is defined as shown in Figure 5. The purpose of this view is most likely: d) To provide isolation between table data and a user's view of the data 14. Which of the following is not a SQL/PSM module? d) Constraint 15. For ALL DBMS products in general, Triggers can NOT be used with which of the following DML action(s): c) SELECT 16. For Microsoft SQL Server, the name of trigger is________ specific, meaning that you can't name two of those modules with the same name in that context: b) table 17. For ALL DBMS products in general, stored procedure can call/cause which of the following SQL/PSM? a. Functions b .Stored procedures c. Triggers d) All of the above 18. For Microsoft SQL Server, which of the following triggers types has(have)NOT been implemented? a) BEFORE 19. In Microsoft SQL Server, the NEW values are stored in which of the following table for an update statement? b) Inserted 20. Incremental backup (transaction log)will back up changes since `d) last backup of any type 21. Which of the following techniques is used to recover data lost due to erroneous action, such as a delete without where clause? a) Rollback 22. A fully qualified database object name in SQL Server includes four parts separated by a dot. The sec9hdpart is the name of a c) database 23. For Microsoft SQL Server,in order to give rights to readdatain a database, you must grant the database specific permission: d) db_datareader 24. Which of the following SQL PSMs can NOT be called from another application outside a SQL Server? Trigger Function d) A and C 25. Suppose that a function has a full name local variable @fullname= 'John Smith', where John is the first name and Smith is the last name. The last name and first name are separated by a space character. The function declares a local variable @index and assigns the value to the variable in this statement: SET @index = CHARINDEX(' ', @fullname). Which following statement extracts the last name without the space from @fullname and store the value in the variable @ln? b) SET @ln = SUBSTRING(@fullname, @index+1, (LEN(@fullname) -@index-1)); 26. What's the value returned from this function: SUBSTRING('Western Washington University', 1, 4)? d) West 27. With the sample data in tblSupportCommitteetable as shown in Figure 10 belowand the following statements in a stored procedure: SELECT*FROMtblSupportCommittee WHERE CommitteeID < 100;PRINT'@@ROWCOUNT='+CAST(@@ROWCOUNTasvarchar(3)); What will be the message shown in the Message window in Microsoft SQL Server Management Studio from the PRINT statement after the stored procedure is called? a.@@ROWCOUNT=7
Authorize a user
1.Two levels of authorization: server level (login)and database level (database user) 2.Create a database server login 1.Server roles (SQL Server 2012+) 1.dbcreator 2.securityadmin 3.serveradmin (configurations) 4.sysadmin (anything) 3.Assign the login to a database as a user of the database with certain predefined roles 1.Database roles (SQL Server 2012+) 1.db_datareader 2.db_datawriter 3.db_securityadmin 4.db_owner 4.Other than default roles, administrators can define user-defined server or database role: for specific application or function
Authorize a user 2
1.Two levels of authorization: server level (login)and database level (database user) 2.Create a database server login 1.Server roles (SQL Server 2012+) 1.dbcreator 2.securityadmin 3.serveradmin (configurations) 4.sysadmin (anything) 3.Assign the login to a database as a user of the database with certain predefined roles 1.Database roles (SQL Server 2012+) 1.db_datareader 2.db_datawriter 3.db_securityadmin 4.db_owner 4.Other than default roles, administrators can define user-defined server or database role: for specific application or function
Order Transaction Stored Procedure 2
CREATE PROCEDURE dbo.uspOrderTransaction (@sku int, @orderNum int, @warehouseID int, @quantity int, @price float, @extendedPrice float, @orderTotal float, @resultCode int OUTPUT, @message varchar(100) OUTPUT) AS BEGIN --Try catch all errors --Make it a transaction - if one statement fails, rollback all statements --Check availability of a SKU in inventory --If it is not available, abort the transaction and return an error code --If it is available, reduce the available number of SKU in inventory; if this fails, abort transaction and return an error code --Record the transaction in RETAIL_ORDER and ORDER_ITEM; if this fails, abort transaction and return an error code --Check if the order is already stored in the RETAIL_ORDER table. If not, add it; if this fails, abort transaction and return an error code --record the transaction in ORDER_ITEM table; if this fails, abort transaction code and return an error code --set the successful result code and commit the transaction END
Maintain O-M relationship with a trigger 8
CREATE TRIGGER utrInsteadDeleteTblOMEmployee ON tblOMEmployee INSTEAD OF DELETE AS BEGIN --Get the OLD department ID that will be deleted and the Employee ID --from the table "deleted" --Check if there is only one record with the department ID: using @@RowCount --global variable or a local variable @count --If there are more records with that department ID, the deletion is allowed— --execute the delete statement: delete the employee with the employee ID --If there is only one record with that department ID, the deletion is disallowed—Print a meaningful message END
Maintain O-M relationship with a trigger 4
CREATE TRIGGER utrInsteadInsertTblOMDepartment ON tblOMDepartment INSTEAD OF INSERT AS BEGIN --Get the new department ID from the table "inserted" --Check if there is a record in tblOMEmployee table with the DepartmentID --If there is, insert the record into the tblOMDepartment table --If there isn't, do not insert the record into the tblDepartment table --print a message END
Maintain O-M relationship with a trigger 5
CREATE TRIGGER utrInsteadUpdateTblOMDepartment ON tblOMDepartment INSTEAD OF UPDATE AS BEGIN --Update tblOMDepartment set DepartmentID=New_DeptID Where DepartmentID=Old_DeptID --Get the old Department ID from the table "deleted" --Get the new Department ID from the table "inserted" --Check if there is a record in tblOMEmployee table with the old DepartmentID --If there is, update the record in the tblOMEmployee with the new DepartmentID. --If the update is successful, move on to the next step. If not, exit the trigger --Update the tblDepartment table with the new DepartmentID value --If there isn't, do not update the record in the tblDepartment table --print a message END
Maintain O-M relationship with a trigger 7
CREATE TRIGGER utrInsteadUpdateTblOMEmployee ON tblOMEmployee INSTEAD OF UPDATE AS BEGIN --Get the replaced (OLD) department ID from the table "deleted" --and the replacing department ID and Employee ID from "inserted" --Check if there is only one employee with the OLD department ID that --will be updated: Use a select statement, then check @@RowCount or --declare a local count variable --If it is not, the update is allowed --execute the update statement: update the department ID with the NEW department ID for the Employee --If it is, the update is disallowed. Print a meaningful message END
01 introduction to ms
slides
02 introduction
slides
ch07-2
slides
ch07-3
slides
Database Administration
´All large and small enterprise databases need database administration. ´Data administration a function managing all data assets in an organization. ´Database administration or Database administrator (DBA) refers to a person or office specific to databases and their applications.
DBMS Security Guidelines 2
v´Manage accounts and passwords ´Use a low privilege user account for the DBMS service ´Protect database accounts with strong passwords ´Monitor failed login attempts: ´Server property->security ´Frequently check group and role memberships ´Assign accounts the lowest privileges possible ´Limit DBA account privileges ´Planning ´Develop a security plan for preventing and detecting security problems ´Create procedures for security emergencies and practice them
Authorizing 3
´ ´DCL can be used to grant permissions for accessing other database level individual objects, such as table, view, functions, triggers, stored procedures ´Better define roles than grant permissions for individuals ´GRANT [privilege] ON [object] TO [user] [WITH GRANT OPTION] ´GRANT SELECT ON Person.Address TO xchen_s WITH GRANT OPTION
Maintain O-M relationship with a trigger
´1:N and M:O relationship ´Set a foreign key in the child (many-side)table ´1:N and O-M relationship ´Each membership can be owned by zero or more people; Each person must have one and only one membership:1:N and M-O relationship, a foreign key implements the relationship ´Each membership must be owned by one or more people; Each person can have zero or only one membership:1:N and O-M relationship, no foreign key constraint will be defined in neither of the tables
Stored Procedures
´A stored procedure is a program that is stored within the database and is compiled when used. ´In Oracle, it can be written in PL/SQL or Java. ´In SQL Server, it can be written in TRANSACT-SQL or Other CLR supported language, such as VB.NET, C#, etc. ´Stored procedures can receive input parameters and they can return results in the form of output parameters. ´Stored procedures can be called from: ´Programs written in standard languages, e.g., Java, C#. ´Scripting languages, e.g., JavaScript, VBScript. ´SQL command prompt, e.g., SQL Plus, Query Analyzer. ´Stored procedures can be called from another stored procedure and a trigger, but NOT an SQL function
Triggers I
´A trigger is a stored program that is executed by a DBMS whenever an event occurs on a specified table or view. ´Action vs Event ´Trigger Event from users: Insert, Update, or Delete statement to modify data in a specific table ´Trigger Action: a trigger program stored in a table, which is initiated by a DBMS to respond to one of the trigger events from users ´Three trigger types: BEFORE, INSTEAD OF, and AFTER ´Each type can be declared for Insert, Update, and Delete. ´Resulting in a total of nine trigger types. ´SQL Server only supports INSTEAD OF and AFTER triggers (Oracle supports all three types; MySQL supports only BEFORE and AFTER) ´Has implications of what data in a table is available to an action
User-Defined Functions I
´A user-defined function (stored function) is a stored set of SQL statements that: ´is called by name from another SQL statement ´may have input parameters passed to it by the calling SQL statement, and ´returns an output value to the SQL statement that called the function.
Modifying tables: ALTER TABLE StatementAdding and Dropping Columns
´ALTER TABLE statement changes table structure, properties, or constraints after it has been created. ´Add a column to tblWork table: ALTER TABLE tblWork ADD ArtistID Int NOT NULL FOREIGN KEY REFERENCES tblArtist (ArtistID) ´You can drop an existing column with the statement: ALTER TABLE tblWork DROP COLUMN MyColumn;
SQL Views
´An SQL view is a virtual table that is constructed from other tables or views. ´It has no data of its own, but obtains data from tables or other views. ´SELECT statement is used to define view. ´Once a view is created, it can be used as a table. ´Views are updatable, subject to certain restrictions ´Referenced columns must come from one base table ´Columns can't be involved with GROUP BY, HAVING, or DISTINCT clauses or derived from calculation or aggregation
SQL Cursors in Program Code
´An example in SQL Server : DECLARE @sku int, @sku_description char(35) DECLARE SQLCursor CURSOR FOR (SELECT SKU, SKU_Description FROM INVENTORY); OPEN SQLCursor FETCH NEXT FROM SQLCursor INTO @sku, @sku_description WHILE @@FETCH_STATUS = 0 --0=successful, -1=failed BEGIN PRINT CAST(@sku as CHAR(6))+ 'à' + @sku_description FETCH NEXT FROM SQLCursor INTO @sku, @sku_description END CLOSE SQLCursor; --data structures still exist DEALLOCATE SQLCursor; -- data structures are released
Granting a user access to a database: Two steps
´Authenticating: create a login for a user - connecting to the SQL server Database Engine ´Authorizing: grant user permissions to database objects (assigning roles) ´Server instance (Server roles) ´Database (Database roles) ´Table, view, stored procedure, function, trigger
Authenticating
´Authentication Types ´Windows Authentication ´It can leverage Active Directory for security protection ´SQL Server Authentication
SQL DML— Bulk INSERT Example
´Bulk Insert example: (data in CHEN_MIS_421_Demo) ´Insert data from tblEmployees2 and tblIndividualCustomer2 into tblPerson2 ´Data needed: LastName, FirstName, Address, City, Region, PostalCode, Country, Phone INSERT INTO tblPerson2(LastName, FirstName, Address, City, Region, PostalCode, Country, Phone) SELECT LastName, FirstName, Address, City, Region, PostalCode, Country, Phone FROM tblIndividualCustomers2; INSERT INTO tblPerson2(LastName, FirstName, Address, City, Region, PostalCode, Country, Phone) SELECT LastName, FirstName, Address, City, Region, PostalCode, Country, HomePhone FROM tblEmployees2; --Can we combine these two insert statements into one? --UNION?
CREATE VIEW Command
´CREATE VIEW command: CREATE VIEW CustomerNameView AS SELECT LastName AS CustomerLastName, FirstName AS CustomerFirstName, FROM CUSTOMER; ´ ´Results: SELECT * FROM CustomerNameView ORDER BY CustomerLastName, CustomerFirstName;
CREATE TABLE constraints
´Constrains: ´Default, Null, Auto Incremental (Identity), Primary key, Foreign Key, Check (Data Constraints), and Unique ´Check for Data Constraints: domain, range, intrarelation ´Foreign key for interrelation data constraints ´Constraints at the column level: ´NULL status, IDENTITY (For SQL Server) ´DEFAULT ´Constraints at both the column and table levels ´CHECK ´PRIMARY KEY ´FOREIGN KEY ´UNIQUE ´A constraint involving more than one column needs to be defined as a table constraint ´A table constraint starts with the key word CONSTRAINT and must have a name and the name must be unique in the database: prefix a constraint name with the table name
Basics of T-SQL: Cursor-Embedding SQL in Program Code 2
´Cursors ´Cursors are an extension to result sets that allow processing one row at a time from a result set ´Pseudo Code to use a cursor ´Declare a cursor ´Open the cursor ´FETCH from the cursor ´Loop through the result set via the cursor (don't forget to FETCH inside the loop) ´Close the cursor ´Deallocate the cursor (remove it from the memory)
SQL Categories 2
´Data definition language (DDL) statements ´Used for creating tables, relationships, and other structures ´Covered in CH07 ´Data manipulation language (DML) statements ´Used for queries and data modification ´Covered in CH 2 ´SQL/Persistent Stored Modules (SQL/PSM) statements ´Add procedural programming capabilities ´Variables ´Control-of-flow statements ´Covered in Chapters: ´CH7 (general introduction) ´10A ´Transaction control language (TCL) statements ´Used to mark transaction boundaries and control transaction behavior ´Covered in Chapters: ´CH7 and CH9 (general introduction) ´10A (SQL Server 2012) ´Data control language (DCL) statements ´Used to grant (or revoke) database permissions to (from) users and groups ´Covered in Chapters: ´CH9 (general introduction) ´10A (SQL Server 2012) ´
Specify Column Properties andData Constraints
´Data type ´Default value (related to INSERT statement) ´Data constraints are limitations on data values: ´Domain constraint—column values must be in a given set of specific values: StudentType IN ('U', 'G') ´Range constraint—column values must be within a given range of values: PersonAge>=0 ´Intrarelation constraint—column values are limited by comparison to values in other columns in the same table: DeceaseDate > BirthDate ´Interrelation constraint—column values are limited by comparison to values in other columns in other tables . It will be implemented by foreign keys or triggers. ´Can be done with a script or at Table design window in SQL Server DBMS ´In SQL Server, when a table is in the Design window, right click the column à Check Constraint. Write the expression, such as BirthDate < DeceasedDate for the expression field and a descriptive name for the name field
Authorizing Database roles
´Database roles: ´db_accessadmin: manage login access to the database ´db_backupoperator: manage database backup activities ´db_datareader: read all data from all user tables ´db_datawriter: modify all data from all user tables ´db_ddladmin: run DDL command in a database ´db_denydatareader: CANNOT read any data in the user tables ´db_denydatawriter: CANNOT modify data in the user tables ´db_owner: can perform all configuration and maintenance activities ´db_securityadmin: can modify role membership and manage permissions ´public
Database Security
´Database security ensures that only authorized users can perform authorized activities at authorized times. ´It limits certain actions (permissions) on certain objects to certain users or groups (also called roles). ´Currently all commercial DBMSs use a form of username/password for security control. Security certificate is also supported
ALTER TABLE Statement Add CONSTRAINTS
´Example ALTER TABLE WORK ADD CONSTRAINT WorkAK2 UNIQUE(Title, Copy), CONSTRAINT ArtistFK2 FOREIGN KEY(ArtistID) REFERENCES ARTIST(ArtistID) ON UPDATE CASCADE ON DELETE NO ACTION Table constraints can't be modified: Drop a table constraint, then re-create it. ALTER TABLE CUSTOMER DROP CONSTRAINT NAME_OF_CONSTRAINT;
Checkpoint-a command to synch
´For performance reasons, modifications on database pages are done in memory, not in disk until a checkpoint command is issued on a database ´Applied to each database ´A checkpoint is a point of synchronization between the database on a disk and the changes in memory. ´DBMS refuses new requests, finishes processing outstanding requests, and writes its buffers to disk. ´The DBMS waits until the writing is successfully completed à the transactions in memory have been written to disk. ´Checkpoint interval is different from the transaction log backup interval ´Most DBMS products automatically issue checkpoint commands on databases based on server configurations (Server Property->Database Settings->Recovery Interval, default is one minute). But checkpoints can be issued manually too or by other operation commands, such as backup
Database object names
´Four parts of an object name: Server_name.Database_name.Schema_name.Object_name ´Server_name is the linked server that is defined in the current server instance ´A schema defines a namespace to group objects ´Database schema: a namespace (for the grouping purpose) used to reference objects in the database ´Each database can have multiple schemas ´Object names in each schema need to be unique ´Duplicate object names can exist in different schemas
Database Backup
´Full backup - a complete backup of everything ´Differential backup - backup the changes since the last full backup ´Incremental backup - backup the changes since previous backups of any type (Full, differential, and incremental) ´In SQL Server, this is the transaction log
DDL: CREATE TABLE
´General Structure: CREATE TABLE NewTableName( three-part column definition, three-part column definition, ... optional table constraints ... ); ´The three-part column definition: ´column name, data type, and optional column data constraints and default value. ´No comma between each element for a column definition
Basics of T-SQL: Variable-Global Variable
´Global variables (example in uspCreateClassLogins.sql) ´Not need to be declared. Value is set by DBMS ´start with @@ ´Common global variables ´@@Error--Returns an error number if the previous statement generates an error ´@@FETCH_STATUS, @@FETCH_ROWS (number of rows in the last cursor opened on a connection) ´@@ROWCOUNT ´number of records affected after select, update, delete, or insert statement ´It is reset after one use ´Declare @count int ´Set @count = @@ROWCOUNT ´@@identity (see the trigger example in IdentityDemo.sql) ´The identity field value just created
Stored Procedure Advantages
´Greater security as stored procedures are always stored on the database server ´Decreased network traffic ´SQL can be optimized by the DBMS compiler ´Code sharing resulting in: ´Less work ´Standardized processing ´Specialization among developers
Basics of T-SQL: Condition
´IF...ELSE ´With or without BEGIN and END pair ´IF @@RowCount >=1 BEGIN INSERT INTO tblCustomer... Update tblCustomer set LastName='Chen'... END ´IF @@RowCount >=1 INSERT INTO tblCustomer... Update tblCustomer set LastName='Chen'...
Triggers III
´INSTEAD OF trigger: trigger action (defined by your code) overrides the trigger event: Insert, Update, or Delete ´Trigger event will NOT be executed. Data in the table is not changed (no data is inserted, updated, or deleted) ´You will need to write the insert, update, or delete statement again in your trigger action if you decide the original triggering statement needs to be executed ´BEFORE trigger: trigger action defined in your trigger is activated before the standard trigger event is executed ´The trigger event (insert, update, or delete) will be executed after the trigger action is activated and executed ´Not implemented in SQL Server ´AFTER trigger: trigger action is activated after the standard trigger event is executed ´The trigger event (insert, update, or delete) has been executed ´The data are already changed in the table
Database Recovery
´In the event of system failure, that database must be restored to a usable state as soon as possible. ´Two recovery techniques: ´Recovery via reprocessing ´Recovery via rollback/rollforward
Create INDEX
´Index: find data more quickly and efficiently ´Change data? ´Clustered index vs. non-clustered index ´How rows are sorted and stored: data are physically sorted and stored based on the clustered index ´For each non-clustered index, a DBMS creates a structure for the columns and a pointer that indicates where the associated record is located in the physical storage ´Each table can have only one clustered index, but multiple non-clustered index ´CREATE INDEX ´CREATE INDEX index_name ON table_name (column_names) ´CREATE UNIQUE INDEX index_name ON table_name (column_names) à alternate keys ´Drop INDEX index_name ON table_name ´The syntax varies in different DBMS
Basics of T-SQL: Variable-Local Variable
´Local variables: ´DECLARE @var_name1 data_type1, @var_name2 data_type2; ´Set value ´SET @var_name1 = @var_name2 or SET @var_name1 = 'US' ´SELECT @rownum = COUNT(*) from tblInventory ´SELECT @lastName = LastName from tblCustomer Where CustomerID=100100 ´If your select statement returns more than one records, you may not want to use SELECT to assign a value to a variable. Cursor may be an option ´PRINT -- Hello World! ´Can be used in stored procedures and triggers ´Can't be used in functions
System Databases
´Master—all the system-level information for an instance of SQL Server ´Model—the template database for creating new databases ´MSDB—for scheduling alerts and jobs by SQL Server Agent ´Tempdb—holding temporary objects ´Cursors, intermediate tables, etc.
Basics of T-SQL: Cursor-Embedding SQL in Program Code
´Problem: paradigm mismatch between SQL and application programming language: ´SQL SELECT statements return sets of rows; an application works on one row at a time ´Problem: assigning SQL table columns with program variables ´SQL cursors are used to select one row at a time from a result set.
Recovery via Reprocessing
´Recovery via reprocessing: the database goes back to a known point (database save), then manually reprocesses the workload from there using the application (not the DBMS)
Rollback/Rollforward
´Recovery via rollback/rollforward using logs: ´Periodically save the database (full backup) and keep a database change log (differential or incremental backup) since the save. ´Database log contains records of the data changes in chronological order. ´When there is a failure, either rollback or rollforward is applied. ´Rollback: undo the erroneous changes made to the database and reprocess valid transactions ´Rollforward: restore database using saved data and valid transactions since the last save after a database server crash (harddrive failure) or other disasters
DBMS Security Guidelines
´Run DBMS behind a firewall ´Apply the latest operating system and DBMS service packs and fixes ´Use the least functionality possible ´Support the fewest network protocols possible ´TCP/IP ´Named Pipes (local area network) ´Shared Memory (local machine only) ´Delete unnecessary or unused system stored procedures ´Disable default logins and guest users, if possible ´REVOKE CONNECT FROM GUEST (at the database level) ´Protect the computer that runs the DBMS ´No user other than DB administrators allowed to work at the computer that runs the DBMS ´DBMS computer physically secured behind locked doors ´Access to the room containing the DBMS computer should be recorded in a log
SQL DML—DELETE
´SQL DELETE statement: DELETE FROM CUSTOMER WHERE CustomerID = 1000; ´If you omit the WHERE clause, you will delete every row in the table. ´If you delete all records with a DELETE statement without a WHERE clause, the identity field value will not restart from the seed (starting) value, meaning that the value of the identity field of a new record will be 1 plus the maximum of the deleted values
Removing Tables
´SQL DROP TABLE: DROP TABLE TABLE_NAME; ´If there are interrelation constraints (drop the constraints first): ALTER TABLE CUSTOMER_ARTIST_INT DROP CONSTRAINT Customer_Artist_Int_CustomerFK; ALTER TABLE TRANS DROP CONSTRAINT TransactionCustomerFK; DROP TABLE CUSTOMER; OR Drop the child tables before drop the parent table
SQL DML—INSERT
´SQL INSERT statement--syntax for inserting one record by one statement: INSERT INTO Table_name (Column_list) VALUES (Value_list) ´Example (CHEN_MIS_421_Demo) ´INSERT INTO tblArtist (LastName, FirstName, Nationality, DateOfBirth, DateDeceased)VALUES ('Tamayo', 'Rufino', 'Mexican', 1899, 1991); ´Bulk INSERT—syntax for inserting multiple records by one statement: INSERT INTO Table_name (Column_list) SELECT Statement ´Example -- ARTIST_WithNationality maINSERT INTO tblArtist (LastName, FirstName, Nationality, DateOfBirth) SELECT LastName, FirstName, Nationality,DateOfBirth FROM tblArtist_WithNationality WHERE ArtistID > 5 y have other columns
SQL DML—INSERT With A Surrogate Key
´SQL INSERT statement: ´SET IDENTITY_INSERT TABLE_NAME ON ´SET IDENTITY_INSERT TABLE_NAME OFF ´Example Insert Statement for not specifying a value for an identity field (This is the usual way to insert a record to a table that has an identity field as the primary key) INSERT into tblStudent2 (StudentName, StudentType) VALUES('Jackson, Smith', 'U'); -- In CHEN_MIS_421_Demo database ´Example Insert Statement with a specific value for an identity field SET IDENTITY_INSERT tblStudent2 ON INSERT into tblStudent2 (StudentID, StudentName, StudentType) VALUES(113,'Jackson, Smith', 'U'); SET IDENTITY_INSERT tblStudent2 OFF
SQL DML—MERGE
´SQL MERGE command (SQL Server): Update and Insert at the same time (Chen_MIS421_Demo) MERGE INTO tblArtist AS A USING (SELECT LastName, FirstName, Nationality from tblArtist_WithNationality) AS ADR ON A.LastName = ADR.LastName AND A.FirstName = ADR.FirstName WHEN MATCHED THEN UPDATE SET A.Nationality = ADR.Nationality WHEN NOT MATCHED THEN INSERT (LastName, FirstName, Nationality) values (ADR.LastName, ADR.FirstName, ADR.Nationality);--Must terminated by a semi-colon
SQL DML—UPDATE
´SQL UPDATE statement: UPDATE CUSTOMER SET City = 'New York City WHERE CustomerID = 1000;
SQL DML—UPDATE 2
´SQL bulk update (Go through each record in the target table): UPDATE tblArtist SET tblArtist.Nationality = ADR.Nationality FROM tblArtist_WithNationality AS ADR WHERE tblArtist.LastName = ADR.LastName AND tblArtist.FirstName = ADR.FirstName; NOTE: Alias can be created in FROM clause or MERGE clause, not in UPDATE clause
SQL Categories
´SQL statements can be divided into five categories: ´Data definition language (DDL) - create database objects ´Data manipulation language (DML) - SQL statements to retrieve, modify, insert, and delete data in tables ´SQL/Persistent Stored Modules (SQL/PSM) - programming code that may include SQL statements in modules for reuse ´Transaction control language (TCL) - SQL statements to set transaction boundaries ´Data control language (DCL) - SQL statements to grant permissions on database objects
SQL/Persistent Stored Modules (SQL/PSM)
´SQL/Persistent Stored Modules (SQL/PSM) is an ANSI/ISO standard for embedding procedural programming functionality into SQL ´Each DBMS product implements the standard SQL/PSM in a different way with some extensions. ´Microsoft SQL Server calls its version Transact-SQL (T-SQL). ´Oracle Database calls its variant Procedural Language/SQL (PL/SQL). ´MySQL implements SQL/PSM, but has no special name for its variant of SQL.
Database Administration 2
´Secure databases and servers ´Create and maintain databases: create/attach, delete/detach, backup, and restore databases; server update/upgrade; integrating database server with other enterprise services ´Support concurrent transactions ´Develop database applications, including SQL/PSM, business intelligence applications (data preparation and integration, KPIs...), etc.
Authorizing Server roles
´Server roles: ´bulkadmin: load data from a file system ´dbcreator: create, alter, drop, and restore any database ´diskadmin: managing disk files ´processadmin: End processes running in an instance of SQL Server ´public: any login will be in this role ´securityadmin: manage logins and their properties (Grant, Deny, and Revoke server-level permissions or database-level permissions if they have access to a database) ´serveradmin: change server-wide configurations, start/stop server ´setupadmin: manage linked servers ´sysadmin: perform any activity in the server
Authorizing server roles
´Server roles: ´bulkadmin: load data from a file system ´dbcreator: create, alter, drop, and restore any database ´diskadmin: managing disk files ´processadmin: End processes running in an instance of SQL Server ´public: any login will be in this role ´securityadmin: manage logins and their properties (Grant, Deny, and Revoke server-level permissions or database-level permissions if they have access to a database) ´serveradmin: change server-wide configurations, start/stop server ´setupadmin: manage linked servers ´sysadmin: perform any activity in the server
Basics of T-SQL: String functions
´String functions ´SUBSTRING (expression, startposition, length) -- startposition >= 1 ´SUBSTRING('Western Washington', 7, 8) à n Washin ´CHARINDEX (expressionToFind, expressionToSearch [, start_location]) ´return ´the index of the first character of expressionToFind ´zero when the expressionToFind is not found in expressionToSearch ´CHARINDEX('shing', 'Western Washington') à 11 ´CHARINDEX('shing', 'Western Washington', 12) à ? ´RTRIM, LTRIM, UPPER, LOWER, CONCAT, LEN ´Data conversion: CAST (data as new_data_type), CONVERT (new_data_type, data) ´Convert data type: CAST(124 as char(3)) ´Convert data type: CONVERT(char(3), 124)
SQL DML—TRUNCATE
´TRUNCATE: TRUNCATE TABLE CUSTOMER ´Similar to DELETE without WHERE clause, faster operation ´Identity field is reset to the seed value
User-Defined Functions II
´Table-valued functions ´return a table (can be used in FROM/WHERE/HAVING clauses) ´The create function definition defines what table columns will be returned. See more information here. ´Scalar functions ´return a single value ´Aggregate functions ´perform a calculation on a set of values and return a single value ´Involves an assembly that contains the implementation of the calculation and an aggregate function that calls the registered assembly (use CREATE ASSEMBLY) in an instance of SQL Server ´Frequently used with GROUP BY clause of SELECT statement (AVG, COUNT, MAX, MIN, etc).
Basics of T-SQL: Format the Code
´The BEGIN---END pair for the body of a module has the same indent as the CREATE key word for a module CREATE FUNCTION dbo.ufnGetLastName (@fullname varchar(100)) RETURNS varchar(50) AS BEGIN ............ END ´Same level of statements has the same indent ´Use a tab or 4 spaces or 2 spaces (be consistent) to indent the secondary statements ´The BEGIN---END pair has the same indent as the IF or WHILE loop ´Indent the statement(s) inside a BEGIN---END pair
Maintain O-M relationship with a trigger 3
´The DBMS won't be able to implement this requirement. Parent table primary key can't be an identity field ´The trigger on the parent table ´Before inserting a record into the parent table, the system needs to make sure the parent has at least one child; if not, the insertion to the parent table is disallowed—INSTEAD OF trigger ´Before updating a record in the parent table, the system needs to make sure at least one child record has the old value and update the old value with the new value; if update to the child table failed, the update on the parent table is disallowed; if yes, update the DepartmentID in the parent table—INSTEAD OF trigger ´No action needs to be taken when a record is deleted in the parent table since the parent table is optional—No trigger is needed
SQL/Persistent Stored Modules (SQL/PSM) 2
´The three module types of programs: User-defined functions, triggers, and stored procedures ´Difference among triggers, user-defined functions, and stored procedures ´Triggers are associated with a specific table or view event (DML events: insert, update, and delete; DDL events: CREATE, ALTER, and DROP). They are initiated by the DBMS ´User-defined functions and stored procedures are database-wide modules and can be called whenever they are needed. They are initiated by users ´Triggers can call functions and stored procedures ´Stored procedures may invoke triggers or call functions ´Functions can't call trigger and stored procedures
Maintain O-M relationship with a trigger 6
´The trigger on the child table ´No action needs to be taken when a record is inserted in the child table ´Before updating a record in the child table, make sure the child is not the last one for a parent; if it is, the update is disallowed ´Before deleting a record in the child table, make sure the child is not the last one for a parent; if it is, the deletion is disabllowed
Maintain O-M relationship with a trigger 2
´Two tables: tblOMDepartment and tblOMEmployee ´tblOMDepartment : DepartmentID INT NOT NULL PRIMARY KEY, DepartmentName VARCHAR(50) NULL, Phone VARCHAR(12) NULL, Email VARCHAR(100) NULL ´tblOMEmployee : EmployeeID INT NOT NULL PRIMARY KEY, LastName VARCHAR(50) NULL, FirstName VARCHAR(50) NULL, Phone VARCHAR(12) NULL, Email VARCHAR(100) NULL, DepartmentID INT NULL
Database-wise SQL/Persistent Stored Modules (SQL/PSM)
´User-defined functions VS stored procedures ´Functions must return a value, stored procedures do not have to ´Functions can only use SELECT statement, Stored Procedures can use DELETE/UPDATE/INSERT as well as SELECT ´Functions can be used in SELECT/FROM/WHERE/HAVING clauses, procedures can't ´Stored procedures are compiled once and reused for successive calls; Functions need to be compiled every time it is called ´Stored procedures can be invoked directly from application programs, such as VB, C, Java programs. Functions can only be used in SQL statements or other functions/stored procedures/triggers, but not directly from application programs ´Try-catch (for certain error severity > 10) and transaction management can be used in stored procedures, not in Functions SQL Server Database Engine Error Severities
Basics of T-SQL: Loop
´WHILE LOOP ´WHILE condition BEGIN other statements END ´Triggers/Procedures/Functions can be developed using VB, C#, and other language supported by Common Language Runtime (CLR) in SQL Server 2005 or later versions ´Microsoft SQL Server Data Tools need to be installed (depending on which version of VS)
Data for Activated Triggers
´When a trigger is activated, the DBMS supplies: ´Old and new values for the update ´New values for insert ´Old values for deletion ´The way the values are supplied depends on the DBMS product. In SQL Server ´New values for inserts and updates are stored in the table 'inserted' ´Old values for deletions and updates are stored in the table 'deleted' ´Triggers can be used for: ´Providing default values ´Enforcing data constraints ´Automatically updating tables/views ´Performing referential integrity actions ´
Authorizing
´authorize the login to access certain securables ´Mapping a login to a role ´Common Securables (objects) ´Server level ´Endpoints (connection points), Server, Communication, Logins ´Database level ´Database, Schema, Table, View ´Database roles, Users ´Triggers, Function, Stored Procedure