Database Design and Implementation
Database Design and Implementation ISM 4212
University of Central Florida
Popular in Course
Popular in Information Systems Management
This 42 page Class Notes was uploaded by Marco Moen on Thursday October 22, 2015. The Class Notes belongs to ISM 4212 at University of Central Florida taught by Staff in Fall. Since its upload, it has received 38 views. For similar materials see /class/227446/ism-4212-university-of-central-florida in Information Systems Management at University of Central Florida.
Reviews for Database Design and Implementation
Report this Material
What is Karma?
Karma is the currency of StudySoup.
Date Created: 10/22/15
Dr Leigh 7122010 857 AM ISM 4212 Handout 2 Page 1 of3 Introduction to SQL SQL Structured Query Language is the standardized language by which we can define and manipulate data in relational databases We communicate our logical and physical designs to the database software through SQL SQL is supported by the type of database software known as relational database management systems RDBMSs Vendors may support the complete SQL standard a subset of it or a variant of it Wellknown commercial RDBMSs which support SQL include DB2 ORACLE and SQL SERVER The basic component of the data definition language DDL supported by SQL is the CREATE TABLE statement The CREATE TABLE statement is used to define tables by specifying the table name followed by attribute names their data types and their roles as primary or foreign keys Standard SQL syntax is used herein Microsoft ACCESS does not use standard syntax for the Create Table statement For ACCESS SQL syntax see ACCESS help for CREATE TABLE statement SQL for CONSTRAINT clause SQL and for other SQL keywords The CREATE statements to establish an example database CREATE TABLE EMPLOYEE EMPNum INTEGER NOT NULL EMPName CHAR 30 NOT NULL EMPSalary DECIMAL 122 NOT NULL PRIMARY kEY EMPNum CREATE TABLE SALESPER SPNum INTEGER NOT NULL SPSalesyeartodate DECIMAL 122 NOT NULL PRIMARY kEY SPNum FOREIGN kEY SPNum REFERENCES EMPLOYEE EM PNum CREATE TABLE GUST CUSTNum INTEGER NOT NULL CUSTName CHAR 30 NOT NULL PRIMARY kEY CUSTNum CREATE TABLE ORDER ORDNum INTEGER NOT NULL ORDCust INTEGER NOT NULL ORDSPNum INTEGER NOT NULL PRIMARY kEY ORDNum FOREIGN kEY ORDCust REFERENCES GUST CUSTNum FOREIGN kEY ORDSPNum REFERENCES SALESPERSPNum The basic components of the data manipulation language DML of SQL are the INSERT UPDATE and SELECT statements INSERT is used to place additional rows of data in a data table UPDATE can modify data in a data table SELECT is the richest statement in terms of options and possibilities in the SQL standard SELECT is the primary vehicle for specifying queries to the RDBMS To place three rows of data into the CUST table INSERT INTO CUST VALUES 339JOHN39 INSERT INTO CUST VALUES 1439MARIA39 INSERT INTO CUST VALUES 4439EVELYN39 To modify one of those records Dr Leigh 7122010 857 AM ISM 4212 Handout 2 Page 2 of3 UPDATE CUST SET CUSTName39PHL39 WHERE CUSTNum44 Note that UPDATE is a set operation that is it can change any number of records which meet the criteria stated in the WHERE clause For example UPDATE CUST SET CUSTName39PHlL39 WHERE CUSTName ltgt 39MARIA39 would change two ofthe records in our CUST table and we would end up with two 39PHIL39s The simplest SELECT statement lists all ofthe rows and all ofthe columns in a table SELECT FROM CUST To list the data for only some ofthe columns enter only the column names wanted in the SELECT statement SELECT EM PNum EMPName FROM EMPLOYEE A selection criteria can be placed on the SELECT SELECT FROM CUST WHERE CUSTName39PHL39 Entry of SQL into MSACCESS In order to get a screen which will accept entry of SQL which must be in ACCESS s particular SQL syntax follow the following steps Open a new or old database Database in ACCESS refers to a physical file MDB which contains the database data tables and schema tables as well as the form report module etc definitions 1 In the Database window click Queries under Objects and then click New on the Database window toolbar 2 In the New Query dialog box click Design View and then click OK Without adding tables or queries click Close in the Show Table dialog box Switch to the SQL view using the leftmost menu on the tool bar Enter the SQL statement for your query Press the Exclamation Point to execute the query Switch back to the SQL view using the leftmost menu on the tool bar ooNower Clear the window and go back to step 5 Note only one SQL statement is accepted at a time in this window Because only one SQL statement at a time is accepted ACCESS lets the semicolon delimiter required at the end of a standard SQL statement to be optional Dr Leigh 7122010 857 AM ISM 4212 Handout 2 Page 3 of3 Exercises a What do all tables contain after the following stream of SQL is executed Remember each SQL statement is atomic with respect to recovery either all of the statement executes or none of it executes If there is a syntax error in the statement then the statement has no effect b Convert the names be consistent with RSN convert the syntax to MSACCESS and execute the stream of SQL in MSACCESS c What data types are supported by MSACCESS d Is INTEGER a good choice below in all ofthe cases in which it is used Why or why not 1 CREATE TABLE SALESPER SPNum INTEGER NOT NULL NAME CHAR 30 NOT NULL SALARY DECIMAL 122 PRIMARY KEY SPNum 2 CREATE TABLE CUST CUSTNum INTEGER NOT NULL NAME CHAR 30 NOT NULL PRIMARY KEY CUSTNum 3 CREATE TABLE ORDER ORDERNUM INTEGER NOT NULL ORDERCUSTNum INTEGER NOT NULL ORDERSPNum INTEGER PRIMARY KEY ORDERNum FOREIGN KEY CUSTNum REFERENCES CUST FOREIGN KEY SPNum REFERENCES SALESPER INSERT INTO CUST VALUES 39JOHN393 INSERT INTO CUST VALUES 3 39JOHN39 INSERT INTO ORDER VALUES 5 2 1 INSERT INTO ORDER VALUES 5 3 1 INSERT INTO SALESPER VALUES 1 39JOE3939PHL39 INSERT INTO SALESPER VALUES 1 39JOE39 5000 INSERT INTO ORDER VALUES 5 3 1 UPDATE ORDER SET CUSTNum3 INSERT INTO SALESPER VALUES 1539JOE395000 UPDATE SALESPER SET SP Num1439 x x x x xltoooIOOTJgt UPDATE SALESPER SET SPNum1 WHERE SPNum15 UPDATE SALESPER SET SPNum12 WHERE NAME39JOE39 Relational Databases Introduction to Normalization Database schema normalization is a database design technique Normalization is a stepbystep procedure for examining a database design and detecting any entity types which are modelled as tables that we may have missed Consider the following purchase order document P0 12356 Date 053091 Vendor 1245 Item 0tv quot 39 39 Color Color Price 123 12 Hammers 3 Black 434 236 2 Squeegees 4 Blue 121 238 4 Squeegees 3 Black 121 We can model the document directly as a single table P0 P0 Vendor Date Iteml Qtyl Descl Colorl Colorl Pricel Item2 Qty2 Desc2 Color2 Color2 Price2 Item3 Qty3 Desc3 Color3 Color3 Price3 ItemN QtyN DescN ColorN ColorN PriceN This database comprised of the single table above is considered to be in unnormalized form because it includes a repeating group This table specification has undesirable characteristics in several respects First we do not know how many items to allow for Second it is difficult to devise queries to manipulate item information as information on a particular item might appear in any one of N different sets of information A query to search for the quantity of all of a particular item which we have on order might look like SELECT FROM PO ITEM1QTYlITEM2QTY2ITEMNQTYN WHERE ITEM1234 OR ITEM2234OR ITEMN23439 This is unwieldy and it leads us to consider an alternative design for the table An alternative design removes the repeating group P0 P0 Item Vendor Date Qty Desc Color Color Price Now it is easy to devise queries which involve the selection of lines from the invoice This table is considered to be in First Normal Form because it contains no repeating groups Examine the database which has only one table as it looks with data ISM 4212 Relational Databases Introduction to Normalization 072110 1 PO Item Vendor Date Qty Desc Color Color Price 12356 123 1245 053091 12 Hammers 3 Black 434 12356 236 1245 053091 2 Squeegees 4 Blue 121 12356 238 1245 053091 4 Squeegees 3 Black 121 12357 123 334 060191 3 Hammers 3 Black 434 12357 236 334 060191 1 Squeegees 4 Blue 121 After first normal form is attained second normal form is sought in the process of normalization To go from first normal form to Second Normal Form you look for attributes that do not require the complete primary key for their identification It is said that you look for attributes that do not depend on the complete primary key In the above table of purchase order information it can be seen that Vendor and Date depend only on PO that is if you know the PO you can look up Vendor and the PO date as those values are uniquely defined by the PO Also you can see that some of the item information Description Price Color and Color depends only on Item To achieve second normal form we isolate the attributes that depend only on PO and those that depend only on Item and place them in separate tables P0 P0 Vendor Date Item Item Desc Price Color Color POLine EO Item Qty So all that is left dependent on the earlier primary key POItem is the quantity ordered In the process of deriving second normal form we have identified three entity types where at first we only saw a single one We now have three tables with three primary keys The database now contains less redundant data P0 P0 Vendor Date 12356 1245 053091 12357 334 060191 Item Item Desc Price Color Color 123 Hammers 434 3 Black 236 Squeegees 121 4 Blue 238 Squeegees 121 3 Black POL ine PO Item Qty 12356 123 12 12356 236 2 12356 238 4 12357 123 3 12357 236 1 Our next step in normalization is to discover a Third Normal Form This requires that we search the database schema for instances of attributes which depend on other nonprim ary key attributes An examination of the above database reveals that Color always depends on Color Color 3 always indicates quotBlackquot and Color 4 always indicates quotBluequot ISM 4212 Relational Databases Introduction to Normalization 072110 2 If we can assume that the data shown is completely representative of the actual situation and this is a big assumption we can normalize out Color as another entity type resulting in the database schema PO m Vendor Date Color Color Color ltem ltem Desc Price ColorColor POLine EO Item Qty The process of normalization results in a database design that minimizes database redundancy The normalized database is easier to understand and easier to query Normalization also removes update anomalies from the database Consider what would have been required to change an item price in the original unnormalized database design It would have been necessary to search all of the item attributes N of them for the item of the item to be changed and then changing the respective price value This would have been a sizeable processing job And if we missed one occurrence of the item our database would lose its integrity In the new design in third normal form we only need to change the price value in one place and it is effectively changed for all occurrences of the item in all lines of all purchase orders Normalization is a process that involves understanding the situation being modelled as well as an examination of example data concerning the situation Theories about dependencies which you develop from your understanding can be confirmed with examinations of sample data and by interviewing individuals who are knowledgeable of the situation In summary the steps in normalization to third normal form are 1 To go from unnormalized data to First Normal Form Remove repeating groups 2 To go from First Normal Form to Second Normal Form Remove partial key dependencies 3 To go from Second Normal Form to Third Normal Form Remove nonkey dependencies Each step in normalization involves identifying entity types and removing their information to their own tables Note that a table in Third Normal Form is also in Second and First Normal Forms Note that a table in First Normal Form might also be in Second Normal Form if it has no partial key dependencies and a table in Second Normal Form might be in Third Normal Form if it has no nonkey dependencies But a table is considered to be in First Normal Form if it has partial key dependencies even if it has no nonkey dependencies ISM 4212 Relational Databases Introduction to Normalization 072110 3 ISM 4212 7192010 Handout 3 Selections from MSACCESS Help System Page 1 of 5 Equivalent ANSI SQL Data Types The following table lists ANSI SQL data types their e ANSI SQL Microsoft Jet data type SQL data type BIT BIT VARYING BINARY See Notes Not supported BIT See Notes Not supported TINYINT Not supported COUNTER See Notes Not supported MONEY DATE TIME DATETIME TIMESTAMP Not supported UNIQUEIDENTIFIER DECIMAL DECIMAL Synonym VARBINARY BINARY VARYING BIT VARYING BOOLEAN LOGICAL LOGICALI YESNO INTEGERI BYTE AUTOINCREMENT CURRENCY DATE TIME See Notes GUID NUMERIC DEC SINGLE FL OAT4 IEEESINGLE quivalent Microsoft Jet database engine SQL data types and their valid synonyms It also lists the equivalent M1crosoft SQL ServerTM data types Microsoft SQL Server data type BINARY VARBINARY BIT TINYINT See Notes MONEY DATETIME UNIQUEIDENTIFIER DECIMAL DOUBLE PRECISION FLOAT DOUBLE FLOAT8 FLOAT FL OAT IEEEDOUBLE NUMBER See Notes SMALLINT SMALLINT SHORT INTEGERZ SMALLINT INTEGER INTEGER LONG INT INTEGER4 INTEGER INTERVAL Not supported Not supported Not supported IMAGE LONGBINARY GENERAL IMAGE OLEOB JECT Not supported TEXT See Notes LONGTEXT LONGCHAR TEXT MEMO NOTE NTEXT See Notes CHARACTER CHAR See Notes TEXTn CHAR VARCHAR NCHAR CHARACTER ALPHANUMERIC NVARCHAR VARYING CHARACTER STRING NATIONAL VARCHAR CHARACTER CHARACTER VARYING NCHAR NATIONAL NATIONAL CHARACTER CHARACTER NATIONAL CHAR VARYING NATIONAL CHARACTER VARYING NATIONAL CHAR VARYING See Notes Notes I The ANSI SQL BIT data type does not correspond to the Microsoft Jet SQL BIT data type It corresponds to the BINARY data type instead There is no ANSI SQL equivalent for the Microsoft Jet SQL BIT data type ecifying the optional TIMESTAMP is no longer supported as a synonym for DATETIME NUMERJC is no longer supported as a synonym for FLOAT or DOUBLE NUMERJC is now used as a synonym for DECIMAL A LONGTEXT field is always stored in the Unicode representation format Ifthe data type name M is used without length fo example TEXT25 a LONGTEXT eld is created rver sp r This enables CREATE TABLE statements to be written that will yield data types consistent with Microsoft SQL Se I A CHAR eld is always stored in the Unicode representation format which is the equivalent of the ANSI SQL NATIONAL CHAR data type I Ifthe data type name TEXT is used and the optional length is specified for example TEXT25 the data type of the eld is equivalent to the CHAR data type This reserves t ae without a length speci cation to be ali See Also backw ards compatibility for most Microsoft Jet applications while enabling the TEXT data gned with Microsoft SQL Server ISM 4212 7192010 Handout 3 Selections from MSACCESS Help System Page 2 of 5 Microsoft Jet Database Engine SQL Data ypes CONSTRAINT Clause A constraint is similar to an index although it can also be used to establish a relationship with another table You use the CONSTRAINT clause in ALTER TABLE and CREATE TABLE statements to create or delete constraints There are two types of CONSTRAINT clauses one for creating a constraint on a single eld and one for creating a constraint on more than one field Note The Microsoft Jet database engine does not support the use of CONSTRAINT or any of the data de nition language QDL statements with nonMicroso Jet databases Use the DAO Create methods instead Syntax Singlefield constraint CONSTRAINT name PRIMARY KEY UNIQUE NOT NULL REFERENCES foreigmable foreign eld foreign eld2 ON UPDATE CASCADE SET NULL ON DELETE CASCADE SET NULL Multiple eld constraint CONSTRAINT name PRIMARY KEY primaryILprimaryZ UNIQUE unique1 uniqueZ NOT NULL nomullI nomullZ FOREIGN KEY NO INDEX ref1 ref2 REFERENCES foreigmable foreign eld foreign ele ON UPDATE CASCADE SET NULL ON DELETE CASCADE SET NULL 39Ihe CONSTRAINT clause has these parts Part Description name The name of the constraint to be created primaryprimary2 The name of the eld or elds to be designated the primary key unique uniqueZ The name of the eld or elds to be designated as a unique ke notnull notnullZ The name of the eld or elds that are restricted to non1M values ref ref2 The name of a foreign key field or elds that refer to elds in another table foreigmable The name of the foreign table containing the field or fields specified byforeign eld foreign eldforeign ele The name of the eld or elds inforeigmable speci ed by ref ref2 You can omit this clause if the referenced field is the primary key offoreigmable Remarks You use the syntax for a singlefield constraint in the eldde nition clause of an ALTER TABLE or CREATE TABLE statement immediately following the specification ofthe eld39s data type You use the syntax for a multiplefield constraint whenever you use the reserved word CONSTRAINT outside afielddefinition clause in an ALTER TABLE or CREATE TABLE statement Using CONS39IRAINT you can designate a field as one of the following types of constraints ISM 4212 7192010 Handout 3 Selections from MSACCESS Help System Page 3 of 5 I You can use the UNIQUE reserved word to designate a eld as a unique key This means that no two records in the table can have the same value in this field You can constrain any eld or list of fields as unique If a multiple eld constraint is designated as a unique key the combined values of all fields in the index must be unique even if two or more records have the same value in just one of the elds I You can use the PRIMARY KEY reserved words to designate one eld or set of fields in a table as a primary key All values in the primary key must be unique and not Null and there can be only one primary key for a table Note Do not set a PRIMARY KEY constraint on a table that already has a primary key if you do an error occurs I You can use the FOREIGN KEY reserved words to designate a eld as a foreign key If the foreign table39s primary key consists of more than one eld you must use a multiplefield constraint de nition listing all of the referencing elds the name of the foreign e names of the referenced fields in the foreign table in the same order that the referencing elds are listed If the referenced field or elds are the foreign table39s primary key you do not have to specify the referenced fields By default the database 39 I the foreign 39 ke i the referenced elds Foreign key constraints define specific actions to be performed when a corresponding primary key value is changed You can specify actions to be performed on the foreign table based on a corresponding action performed on a primary key in the table on which the CONSTRAINT is de ned For example consider the following definition for the table Customers CREATE TABLE Customers CustId INTEGER PRIMARY KEY CLstNm NCHAR VARYING 50 Consider the following de nition of the table Orders which defines a foreign key relationship referencing the primary key of the Customers tab e CREATE TABLE Orders OrderId INTEGER PRIMARY KEY CustId INTEGER OrderNotes NCHAR VARYING 255 CONSTRAINT EKOrdersCustId EOREIGN KEY CustId REEERENCES Customers ON UPDATE CASCADE ON DELETE CASCADE Both an ON UPDATE CASCADE and an ON DELETE CASCADE clause are defined on the foreign key The ON UPDATE CASCADE clause means that if a customer39s identifier CustId is updated in the Customer table the update will be cascaded through the Orders table Each order containing a corresponding customer identi er value will be updated automatically with the new value The ON DELE39IE CASCADE clause means that if a customer is deleted from the Customer table all rows in the Orders table containing the same customer identi er value will also be deleted Consider the following different de nition of the table Orders using the SET NULL action instead of the CASCADE action CREATE TABLE Orders OrderId INTEGER PRIMARY KEY CustId INTEGER OrderNotes NCHAR VARYING 255 CONSTRAINT EKOrdersCustId EOREIGN KEY CustId REEERENCES Customers ON UPDATE SET NULL ON DELETE SET NULL The ON UPDATE SET NULL clause means that if a customer39s identi er CustId is updated in the Customer table the corresponding foreign key values in the Orders table will automatically be set to NULL Similarly the ON DELE39IE SET NULL clause means that if a customer is deleted from the Customer table all corresponding foreign keys in the Orders table will automatically be set to NULL To prevent the automatic creation of indexes for foreign keys the modifier NO INDEX can be used This form of foreign key definition should be used only in cases where the resulting index values would be frequently duplicated Where the values in a foreign key index are frequently duplicated using an index can be less ef cientthan simply performing a table scan Maintaining this type of index with rows inserted and deleted from the table degrades performance and does not provide any bene t See Also ADD USER Statement CREATE USER or GROUP Statement AL39IER USER or DATABASE Statement CREATE VIEW Statement ALTER TABLE Statement DROP Statement ISM 4212 7192010 Handout 3 Selections from MSACCESS Help System Page 4 of 5 CREATE INDEX Statement DROP USER or GROUP Statement CREATE PROCEDURE Statement GRANT Statement CREATE TABLE Statement REVOKE Statement Example CREATE TABLE Statement CONSRAINT Clause Example Example DROP Statement Deletes an existing table procedure or View from a database or deletes an existing index from a table Note The Microso Jet database engine does not support the use of DROP or any of the DDL statements with nonMicrosoft Jet databases Use the DA0 Delete method instead Syntax DROP TABLE table l INDEX index ON table l PROCEDURE procedure l VIEW View The DROP statement has these parts Part Description table The name of the table to be deleted or the table from which an index is to be deleted procedure The name of the procedure to be deleted View The name of the View to be deleted index The name of the index to be deleted from table Remarks You must close the table before you can delete it or remove an index from it You can also use ALTER TABLE to delete an index from a table You can use CREATE TABLE to create a table and CREATE INDEX or ALTER TABLE to create an index To modify a table use ALTER TABLE ISM 4212 7192010 Handout 3 Selections from MSACCESS Help System Page 5 of 5 FROM ACCESS HELP Execute Method ADO Connection Executes the specified query SQL statement stored procedure or providerspecific text Syntax For a nonirowretuming command string connectionExecute CommandText RecordsAffected Options For a rowretuming command string Set recordset connectionExecute CommandText RecordsAffected Options Return Value Returns a Recordset object reference Parameters CommandText A String value that contains the SQL statement table name stored procedure a URL or providerspecific text to execute RecordsAffected Optional A Long variable to which the provider returns the number of records that the operation affected Options Optional A Long value that indicates how the provider should evaluate the CommandText argument Can be one or more CommandTypeEnum or ExecuteOptionEnum values Remarks Using the Execute method on a Connection object executes whatever query you pass to the method in the CommandText argument on the specified connection If the CommandText argument specifies a rowretuming query any results that the execution generates are stored in a new Recordset object If the command is not a rowretuming query the provider returns a closed Recordset object The returned Recordset object is always a readonly forwardonly cursor If you need a Recordset object with more functionality first create a Recordset object with the desired property settings then use the Recordset object s 9m method to execute the query and return the desired cursor type The contents of the CommandText argument are speci c to the provider and can be standard SQL syntax or any special command format that the provider supports An ExecuteComplete event will be issued when this operation concludes Execute Requery and Clear Methods Example VB This example demonstrates the Execute method when run from both a Command object and a Connection object It also uses the Reguery method to retrieve current data in a Recordset and the Cl method to clear the contents of the Errors collection The Errors collection is accessed via the Connection object of the ActiveConnection property of the Recordset The ExecuteCommand and PrintOutput procedures are required for this procedure to run Public Sub ExecuteX Dim strSQLChange As String Dim strSQLRestore As String Dim strCnn As String Dim cnnl As ADODBConnection Dim cdehange As ADODBCommand Dim rstTitles As ADODBRecordset Dim errLoop As ADODBError 39 Define two SQL statements to execute as command text n strSQLChange quotUPDATE Titles SET Type amp 7 quot39self help39 WHERE Type 39psychology39quot strSQLRestore quotUPDATE Titles SET Type quot amp quot39psychology39 WHERE Type 39selfihelp39quot 39 Open connection strCnn quotProvidersqloledbquot amp 7 quotData SourcesrvTnitial CatalogPubsUser IdsaPassword quot Set cnnl New ADODBConnection cnnlOpen strCnn Create command object Set cdehange New ADODBCommand Set cdehangeActiveConnection cnnl cdehangeCommandText strSQLChange 39 Open titles table Set rstTitles New ADODBRecordset rstTitlesOpen quottitlesquot cnnl adedTable 39 Print report of original data DebugPrint 7 quotData in Titles table before executing the queryquot PrintOutput rstTitles 39 Clear extraneous errors from the Errors collection cnnlErrorsC1ear 39 Call the ExecuteCommand subroutine to execute cdehange command ExecuteCommand cdehange rstTitles 39 Print report of new data DebugPrint quotData iniTitles table after executing the queryquot PrintOutput rstTitles 39 Use the Connection object39s execute method to 39 execute SQL statement to restore data Trap for errors checking the Errors collection if necessary On Error GoTo Err Execute cnnlExecute strSOLRestore adExecuteNoRecords On Error GoTo 39 Retrieve the current data by requerying the recordset rstTitlesRequery 39 Print report of restored data DebugPrint quotData after executing the query quot amp quotto restore the original informationquot 7 PrintOutput rstTitles rstTitlesClose cnnlClose Exit Sub ErriExecute 39 Notify user of any errors that result from executing the query If rstTitlesActiveConnectionErrorsCount gt 0 Then For Each errLoop Tn rstTitlesActiveConnectionErrors MsgBox quotError number quot amp errLoopNumber amp VbCr amp errLoopDescription 7 Next errLoop End If Resume Next End Sub Public Sub ExecuteCommandcdeemp As ADODBCommand rstTemp As ADODBRecordset Dim errLoop As Error 39 Run the specified Command object Trap for errors checking the Errors collection if necessary On Error GoTo Err Execute cdeempExecute 7 On Error GoTo O 39 Retrieve the current data by requerying the recordset rstTempRequery Exit Sub ErriExecute 39 Notify user of any errors that result from 39 executing the query If rstTempActiveConnectionErrorsCount gt 0 Then For Each errLoop In Errors MsgBox quotError number quot amp errLoopNumber amp VbCr amp 7 errLoopDescription Next errLoop End If Resume Next End Sub Public Sub PrintOutputrstTemp As ADODBRecordset 39 Enumerate Recordset Do While Not rstTempEOF DebugPrint quot quot amp rstTemplTitle amp 7 quot quot amp rstTemplType rstTempMoveNext Loop End Sub BeginTrans CommitTrans and RollbackTrans Methods These transaction methods manage transaction processing within a Connection object as follows BeginTrans 7 Begins a new transaction CommitTrans 7 Saves any changes and ends the current transaction It may also start a new transaction RollbackTrans 7 Cancels any changes made during the current transaction and ends the transaction It may also start a new transaction Syntax l evel object Begin39l39rans obj eat Begin39l39rans obj eat Commit39I39rans obj eat Rollback39l rans Return Value BeginTrans can be called as a function that returns a Long variable indicating the nesting level of the transaction Parameters object A Connection object Connection Use these methods with a Connection object when you want to save or cancel a series of changes made to the source data as a single unit For example to transfer money between accounts you subtract an amount from one and add the same amount to the other If either update fails the accounts no longer balance Making these changes within an open transaction ensures that either all or none of the changes go through Note Not all providers support transactions Verify that the providerdefined property quotTransaction DDLquot appears in the C quot object s Properties quot quot indicating that the provider supports transactions If the provider does not support transactions calling one of these methods will return an error After you call the BeginTrans method the provider will no longer instantaneously commit changes you make until you call CommitTrans or RollbackTrans to end the transaction For providers that support nested transactions calling the BeginTrans method within an open transaction starts a new nested transaction The return value indicates the level of nesting a return value of quot1quot indicates you have opened a toplevel transaction that is the transaction is not nested within another transaction quot2quot indicates that you have opened a secondlevel transaction a transaction nested within a toplevel transaction and so forth Calling CommitTrans or RollbackTrans affects only the most recently opened transaction you must close or roll back the current transaction before you can resolve any higherlevel transactions Calling the CommitTrans method saves changes made within an open transaction on the connection and ends the transaction Calling the RollbackTrans method reverses any changes made within an open transaction and ends the transaction Calling either method when there is no open transaction generates an error Depending on the Connection object39s Attributes property calling either the CommitTrans or RollbackTrans methods may automatically start a new transaction If the Attributes property is set to aanctCommitRetaining the provider automatically starts a new transaction after a CommitTrans call If the Attributes property is set to aanctAbortRetaining the provider automatically starts a new transaction after a RollbackTrans call Remote Data Service The BeginTrans CommitTrans and RollbackTrans methods are not available on a clientside Connection object ISM 4212 Handout 05 Informal SQL NOTES 71 12010 Page 1 of 17 NOTE These notes are informal and not guaranteed to be correct and do not follow our latest class table and attribute naming standards Create table a a1 integer not null a2 integer not null Insert into a values 12 Insert into a values 12 Insert into a values 12 Insert into a values 12 Insert into a values 23 Select from a Returns all columns most likely in the order in which they were entered but this is not for sure 7 SQL does not usually worry about order 2 1 2 1 2 1 2 2 3 The asterisk pronounced star is shorthand in SQL for the list of all columns in the table from left to right In this case select from a is shorthand for select a1a2 from a sql works with rows The values in a row are always kept together We insert whole rows We delete whole rows We select whole rows etc SQL is setoriented and works with sets of rows Select al from a MHHHH select a2 from a WNNNN select a2a1 from a WNNNN Nb b b b l select a2a2 from a ISM 4212 Handout 05 Informal SQL NOTES 71 12010 Page 2 of 17 WNNNN WNNNN select from a order by a1 default order is ascending Nb b b b l WNNNN select from a order by a1 ascending access spells ascending as asc so the above Will get a syntax error in access Nb b b b A WNNNN select from a order by a2 desc b b b b N NNNNW select a2a1 from a order by a2 descending NNNNW b b b b N insert into aa1a2 select a1a2 from a I think ACCESS requires that the fields be listed in both places in the statement aboveinot quite right evidently if the attribute names are the same in the target and source tables you do not have to list the attribute names but this bears checking 7 listing the attribute names Will work in all cases though So the hypothesis is that in this case insert into a select from a Access Help system Syntax Multiple record append query ISM 4212 Handout 05 Informal SQL NOTES 71 12010 Page 3 of 17 INSERT INTO target IN externaldatabase fieldl field2 SELECT sourcefieldl field2 FROM tableexpression Singlerecord append query INSERT INTO target field1 field2 VALUES valuel value2 select from a 2 Nb b b b Nb b b l WNMNMWNMN Delete from a deletes all rows in a since we did not specify a set to delete all rows are deleted 7 the default is the whole set select from a returns no rows Now a is a table with 2 columns but with no rows A is now an empty table insert into a values 12 insert into a values 23 insert into a a1a2 select a2al from a select from a l 2 2 3 2 l 3 2 delete from a where a12 this time we have specified a specific set to delete in the previous query we did not specify the set and in that case the default is the whole table select from a 3 2 insert into a values 12 select from a 1 2 1 2 3 2 ISM 4212 Handout 05 Informal SQL NOTES 71 12010 Page 4 of 17 delete from a Where all and a2239 this deletes all rows which meet the Where criteria select from a39 3 2 insert into a values 12 delete from a Where all or a2239 select from a39 empty table insert into a values l239 insert into a values 3239 create table b bl integer not null b2 integer not null constraint b01 primary key bl39 insert into b values l239 insert into b values 2l39 aa1 a2 1 2 3 2 delete from a Where al in select b1 from b39 select from a39 3 2 the statement delete from a Where al in 12 Will do the same thing as will delete from a Where all or a1239 but doing it based on table b lets us change the values in table b and then change the results of this delete statement create table c cl integer c2 integer constraint c01 foreign key cl references bbl39 RSN for table c cbltblclgtc2 insert into a values 5539 select from a39 3 2 5 5 insert into a a1 select al from a39 not null is specified for column a1 so sql will not accept this statement39 sql inserts and deletes Whole rows and there is no value for the second column in this insert statement so sql won t execute the statement select from a39 5 5 insert into cc2 select al from a39 ISM 4212 Handout 05 Informal SQL NOTES 71 12010 Page 5 of 17 select from c we will use the symbol for null this means that there is no entry in that value this is not a standard symbol but we cannot see space remember our database structure aa1a2 bb1b2 cbltbl clgtc2 Note table a has no PK Table c has no PK but attribute c1 is a FK to b foreign keys only reference PKs so if you know that c1 is a FK to table b you know that cl references the PK of table b which is bl delete from c delete from b delete from a now in our database we have 3 tables abc Each of these tables has 2 attributes Each table is empty that is it has no rows aa1a2 bb1b2 cbltbl clgtc2 insert into a values 11 insert into a values 11 select from a 1 1 l 1 insert into b values 11 insert into b values 11 sql will not accept this second insert statement because of the PK in b that is the second insert statement violates entity integrity select from b insert into c values 11 insert into c values 11 select from c 1 1 1 1 insert into c values 23 this statement violates referential integrity that is there is no 2 in the PK column of table b create table d d1 integer not null d2 integer not null constraint d01 primary key dld2 insert into d values 11 ISM 4212 Handout 05 Informal SQL NOTES 71 12010 Page 6 of 17 insert into d values 12 insert into d values 21 insert into d values 23 NOTE THAT THERE WERE NO ENTITY INTEGRITY VIOLATIONS select from d39 1 1 1 2 2 1 2 3 Here in table d we have one PK but it is comprised of two attributes Thus 1 2 is a different PK value than 1 1 and so forth Create table e e1 integer not null e2 integer not null constraint e01 primary key e1 constraint e02 foreign key ele2 references ddld239 F Ks have to match PKs in number of attributes and data type of the attributes Database structure in relational schema notation RSN DOLLQ Edltd1d2e2gt FKs have to reference PKs and have to match the PK in data type and number of attributes DQQ 1 m N i 1 2 2 insert into e values 11 insert into e values 14 double entity integrity violation because there is already a l in the PK of e and there is a referential integrity violation because there is no 1 4 in d insert into e values 12 entity integrity violation insert into e values 23 select from e39 Edltd1d2e2gt 1 2 3 Aa1 a2 Bb1 b2 1 1 1 a ISM 4212 Handout 05 Informal SQL NOTES 71 12010 Page 7 of 17 4 4 5 6 6 l a we will now work with multitable queries that is we select rows from more than one table Select from ab39 This query retuIns the cartesian product of the tables a and b Al a2 bl b2 1 l l a l l 5 d l l l a 4 4 l a 4 4 5 d 4 4 l a 6 6 l a 6 6 5 d 6 6 l a Cartesian product is a set theory operation which takes all of the elements from one set and combines them with all of the elements of a second set to produce a cartesian product set Thus if the first set has 12 elements and the second set has 6 elements the cartesian product of the first set and the second set has 72 6 times 12 elements In the previous query the two sets are the two tables a and b a has 3 rows and b has 3 rows so the cartesian product of the rows of the two sets has 9 rows Select from ab where anl39 To process this query first we perform the cartesian product on sets a and b just as thought the query were only select from ab Al a2 bl b2 um l U b b UIb b l mammgmem mmmbbbt t H 1 mmmbbbt H 1 Second we filter the output of the cartesian product in accordance with the where criteria where anl Al a2 bl b2 ISM 4212 Handout 05 Informal SQL NOTES 71 12010 Page 8 of 17 4 4 d 4 4 1 a l a d l a So finally there are only 2 records output of the query select from ab where anl A1 a2 b1 b2 1 1 1 a 1 1 1 a consider the following database create table salesperson splD integer not null spname text not null spcommissionrate real not null constraint sp01 primary key splD39 create table invoice Inumber integer not null ldate date not null 1am ount currency not null lsplD integer not null constraint i01 primary key1number constraint i02 foreign key IsplD references salespersonsp1D39 insert into salesperson values 12 sammy 1239 insert into salesperson values 15 suzy 1039 insert into salesperson values 3 ed 0539 insert into invoice values 123112981245615 insert into invoice values 345621992345612 insert into invoice values 22211783331239 insert into invoice values 3334112873434312 The first query prepares the cartesian product of the invoice and salesperson tables This is not particularly useful select from salespersoninvoice The second query removes rows from the output where the commission rate does not match the salesperson for the invoice Now we have the commission rates and salesperson names matched up with the respective invoices select from salespersoninvoice where spidispid39 The third query projects only the columns we need to do a commission report select pid pname 39 39 iammmt from i J invoice where spidispid39 ISM 4212 Handout 05 Informal SQL NOTES 71 12010 Page 9 of 17 The fourth query extends the commission rate by the invoice amount for each invoice select pid pname A 39 39 iaiuouut as total 39 39 from invoice where spidispid39 J The next to final query computes a total commission for each salesperson select spidspnamesumspcommissionrate iamount as total commission from salespersoninvoice where spidispid group by spidspname39 The final query computes a total commission for each salesperson but only lists the salesprson id name and total commission for those salesperson who earned more than 1000 in commiss1ons select spidspnamesumspcommissionrate iamount as total commission from salespersoninvoice where spidispid group by spidspname having sumspcommissionrate iamountgt100039 We can save and name any query in access called a saved query in access and we can query that saved query as though it were a table This saved query is called a virtual table because it contains no actual data but is derived from other tables or it may be called a view In standard sQL but not in access we have the create view statement Create view CommissionView as select spidspnamesumspcommissionrate iamount as total commission from salespersoninvoice where spidispid group by spidspname having sumspcommissionrate iamountgt100039 This create view is exactly equivalent to the saved query in ACCESS Views can solve problems We may want to present a simplified view of the database to an end user We can set up views with only the information involved that that user needs and give the end user access to only those views Or we can break down complex querying processing tasks into several tasks through the use of views We could give the president access to a view called successfulSalespersons which contained the salespersons earning over 1000 The president could recognize these people and not be concerned with any other salespersons Create view as SuccessfulSalespersons select spidspnamesumspcommissionrate iamount as total commission from salespersoninvoice where spidispid group by spidspname having sumspcommissionrate iamountgt100039 And we could take the SuccessfulSalespersons view and subject it to further processing such as ordering the output in decreasing order by total commission Select from SuccessfulSalesperson order by total commission desc39 This way the presentation of this information is separated from concerns with definition of what a salesperson is That is we can redefine the successfulsalespersons view such as raising the requirement to 2000 without having to worry about the queries defined on it ISM 4212 Handout 05 Informal SQL NOTES 71 12010 Page 10 of 17 Steps in processing the select statement 1 prepare cartesian product of all input tables if multitable query 2 perform Where i ter 3 identify the groups 4 do aggregation function processing for each group 5 perform having filter on output of step 4 Aa1 a2 Bb1 b2 1 1 a 445d 661a select from ab39 1 prepare cartesian product of all input tables A1 a2 b1 b2 mmmbbeHH mmmbbhwww U b b UIb l m m mgme m 9 Hun 1H 2 perform Where filter no Where filter specified in query step not applicable 3 identify the groups no group by specifed in query step not applicable 4 do aggregation function processing for each group no aggregation functions such as sum avg min max specified in query step not applicable 5 perform having filter on output of step 4 no having specified in query step not applicable select from ab where anl39 1 prepare cartesian product of all input tables A1 a2 b1 b2 1 1 1 a 1 1 5 d 1 1 1 a ISM 4212 Handout 05 Informal SQL NOTES 71 12010 4 4 a 4 4 d 4 4 a 6 6 a 6 6 d 6 6 a Hun 1H 2 perform Where filter where an1 A1 a2 b1 b2 3 identify the groups no group by specifed in query step not applicable 4 do aggregation function processing for each group no aggregation functions such as sum avg min max specified in query step not applicable 5 perform having filter on output of step 4 no having specified in query step not applicable Final output is Al a2 b1 b2 1 1 1 a 1 1 1 a Page 11 of17 select suma2 from ab 1 prepare cartesian product of all input tables if multitable query A1 a2 b1 b2 mkbbt t H abbbt H H HHU HHUIH mmammgm ISM 4212 Handout 05 Informal SQL NOTES 71 12010 Page 12 of 17 6 6 5 d 6 6 1 a 2 perform Where filter no Where filter step not applicable 3 identify the groups no group by specifed in query step not applicable 4 do aggregation function processing for each group Since there is no group by specified for purposes of aggregation processing in this query there is only one qroup comprised of all the records in the cartesian product A1 a2 b1 b2 mambbhwi H mambbhwi H Ulb b Ulb A m m Naming 9 9 Hun 1H suma2 33 5 perform having filter on output of step 4 no having specified in query step not applicable Final output is Sum a2 but access Will generate a dummy attribute name such as exprlOOO 33 select suma2 from a39 1 prepare cartesian product of all input tables Not multitable query cartesian product not applicable 2 perform Where filter no Where filter step not applicable 3 identify the groups no group by specifed in query step not applicable 4 do aggregation function processing for each group Since there is no group by specified for purposes of aggregation processing in this query there is only one qroup comprised of all the records in the table ISM 4212 Handout 05 Informal SQL NOTES 71 12010 Page 13 of 17 A1 a2 1 1 4 4 6 6 suma2 11 5 perform having filter on output of step 4 no having specified in query step not applicable Final output is Suma2 but access Will generate a dummy attribute name such as exprlOOO 11 select suma2a1 from a39 1 prepare cartesian product of all input tables Not multitable query cartesian product not applicable 2 perform Where filter no Where filter step not applicable 3 identify the groups no group by specifed in query step not applicable 4 do aggregation function processing for each group Since there is no group by specified for pquoses of aggregation processing in this query there is only one qroup comprised of all the records in the cartesian product A1 a2 1 1 4 4 6 6 suma2a1 53 5 perform having filter on output of step 4 no having specified in query step not applicable Final output is Suma2a1 but access Will generate a dummy attribute name such as exprlOOO 53 select a1suma2a1 from a group by a139 ISM 4212 Handout 05 Informal SQL NOTES 71 12010 Page 14 of 17 1 prepare cartesian product of all input tables Not multitable query cartesian product not applicable 2 perform Where filter no Where filter step not applicable 3 identify the groups A1 a2 1 1 4 4 6 6 4 do aggregation function processing for each group Al a2 1 l suma2a1 l 4 4 suma2al l6 6 6 suma2al 36 5 perform having filter on output of step 4 no having specified in query step not applicable Final output is Al Suma2al but access Will generate a dummy attribute name such as exprlOOO l l 4 16 6 36 select alsuma2al from a group by a1 having suma2algt1039 1 prepare cartesian product of all input tables Not multitable query cartesian product not applicable 2 perform Where filter no Where filter step not applicable 3 identify the groups ISM 4212 Handout 05 Informal SQL NOTES 71 12010 Page 15 of 17 A1 a2 1 1 4 4 6 6 4 do aggregation function processing for each group A1 a2 1 1 suma2a1 1 4 4 suma2a1 16 6 6 suma2a1 36 5 perform having filter on output of step 4 having sum a2a1gt1 0 A1 a2 1 1 sm e lie19 1 4 4 suma2a1 16 6 6 suma2a1 36 Final output is A1 Suma2a1 but access will generate a dummy attribute name such as expr1000 4 16 6 36 1 a 4 4 5 d 6 6 1 a 4 3 select a1suma2a1 from a group by a1 having suma2a1gt10 ISM 4212 Handout 05 Informal SQL NOTES 71 12010 Page 16 of 17 1 prepare cartesian product of all input tables Not multitable query cartesian product not applicable 2 perform Where filter no Where filter step not applicable 3 identify the groups A1 a2 1 1 4 4 4 3 6 6 4 do aggregation function processing for each group Al a2 1 l suma2a1 l 4 4 4 3 suma2al 28 6 6 suma2al 36 5 perform having filter on output of step 4 having sum a2algtl 0 A1 a2 suma2al 36 Final output is Al Suma2al but access will generate a dummy attribute name such as exprlOOO 4 28 6 36 ISM 4212 Handout 05 Informal SQL NOTES 71 12010 Page 17 of 17 Chapter Three Data independence Physical data independence ProgrammerUser does not have to know the physical storage details to use the data Physical schemaimap of actual storage details Conceptual schema 7 logical layout of data External schema 7 layout of data as shown to user Conceptual data independence ProgrammerUser does not have to know complete conceptual schema to use data Schema is a map Conceptual and external schemas are speci ed using data de nition language DDL Data model is uni ed method for providing conceptual and external schemas constraints and operations on data through data manipulation language DML Commercial systems provide storage de nitioin language SDL to specify physical storage details Structured Query Language SQL is standardized DDL and DML and SDL Relational model is a data model Relation instance is a table with rows and named columns Rows are called tuples Number of columns is called arity of relation Number of tuples is called cardinality of relation Named columns are called attributes Value of a particular attribute in any row of a relation is drawn from a set called the attribute domain Relation schema name of the relation names of the attributes in the relation with domain names integrity constraints on relation Type constraint relation must have named columns and the column names must be the same as the attribute names and the values that appear in the columns must belong to the domain assigned to the attribute Relational database is a nite set of relations Database is a set of relation schemas and a set of corresponding relation instances Integrity constraints Type constraint is an example of interrelational constraint Key constraint a relation does not contain a pair of tuples whose values agree on the values of all of the attributes A subset of the attributes that is unique is a key A superkey is a subset of attributes that contains at least one key A relation can have several different keys Each is called a candidate key One is designated as the primary key Referential integrity The requirement on one relation that that the referenced tuples in another relation must exist is called referential integrity One important type of referential integrity is the foreignkey constraint Semantic constraints Type domain key and foreignkey integrity constraints deal with the structure of the data Semantic constraints implement business rules Create table transcript studit integer crscode char6 semester char6 grade charl check grade in A B C C F check studidgt0 and studid lt1000000000 Create table employee id integer name char20 salary integer mngrsalary integer check mngrsalarygtsalary Create table employee id integer name char20 salary integer departmentid char4 mngrid integer check 0ltselect countfrom employee check select count from manager ltselect count from employee General constraints Create assertion thoushaltnotfireeveryone check 0ltselect count from employee Create assertion watchadmincosts check select count from manager lt select count from employee Unlike the check conditions that appear inside a table definition those in the create assertion statement must be satisfied by the contents of the entire database rather than by individual tuples of a host table Userdefined domains Create domain grades charl check value in A B C D F I Create final grade courseid char5 sectionid integer studentid integer final grade grades Create domain upperdivisionstudent integer check value in select id from student where status in senior junior and value is not null Alter table employee add constraint empdeptconstr foreign key departmentid references department deptid Reactive constraints A reactive constraint is a static constraint coupled with a speci cation of what to do if a certain event happens Following statement specif1es four triggers Create table teaching profid integer crscode char6 semester char6 primary key crscode semester foreign key profid references professorid on delete no action on update cascade foreign key crscode references course crscode on delete set null on update cascade Database views Create view profstud profstud as select teachingprofidtranscriptstudid from transcriptteaching where transcriptcrscode eachingcrscode and transcriptsemester eachingsemester Modifying existing definitions Alter table student add column gpa integer default 0 Drop table employee Alter table student add constraint gparange check gpagt0 and gpalt4 SQLschemas The structure of a database is described in the system catalog A catalog is SQL s version of a directory in which elements are schema objects such as tables and domains An SQLschema is a description of a portion of a database that is under the control of a single user who has the authorization to create and access the objects within it Access Control Grant select update prof1d on sudregsystemteaching to johnsmith marydoe with grant option Authorization through views Grant select on profstud to alumnus profstud is a view Chapter 8 SQL embedded in C program page 270 EXEC SQL SELECT CEnrollment INTO numienrolled FRIM CLASS C WHERE CCrsCodecrs7code AND CSemester semester The designates a variable in C The result of this SELECT statement is a single value But what if the SELECT statement returns a set of rows Cursors page 276 SELECT statement retums a query result or result set The fundamental unit dealt with by an SQL statement is a set of tuples wheras the fundamental unit dealt with by a statement in the host language C VBA etc is a variable This difference is often called an impedance mismatch The SQL mechanism for solving this problem ist he cursor which allows the application program to deal with one row in a result set at a time Think of a cursor as a pointer to a row in the result set A FETCH statement fesches the row pointed to by the cursor and assigns the attribute values in the row to host language variable sin the program In this way variables need be allocated only for a single row Figure 84 is a fragment of an embedded SQL program that uses cursors Stored Procedures on the Server page 282 Many DBMS vendors allow stored procedures to be included as elements of the database schema The procedures can then be iinvoked by an application at a client site and executed at the server site Advantages Execution is on the server and only results are transmitted to the client SQL statements are precompiled on the server whereas when the client transmits the SQL statement to the server the server has to compile each one before processing it The application programmer need not know the details of the database schema and the processing that takes place in the stored procedure Maintenance of the stored procedure code is centralized Physical security of stored procedure code is enhanced Page 301 Executing Transactions By default the database is in autocommit mode when a connection is created Each SQL statement is treated as a separate transaction which is committed when that statement is completed Chapter 13 Transaction Processing Isolation Transactions executing concurrently do not interfere with each other Serializability The result of transactions executing concurrently is the same as some schedule of the transactions acting in serial order Twophase locking Concurrency control algorithm See page 459 1 if a transaction T requests to read an item and no other transaction holds a write lock on that item the dbms grants a read lock on that itme to T and allows the operation to proceed Note that since other transactions might be holding read locks that were granted at an earlier time read locks are often referred to as shared locks 2 If a transaction T requests to read an item and another transaction T holds a write lock on that item T is made to wait until T completes We say tat the requested read operation con icts with the preViously granted write operation 3 If a transadtion T requests to write an item and no other transaction holds a read or write lock on that item the dbms grants T a write lock on that item and allows the operation to proceed Because a write lock excludes all other locks it is often referred to as an exclusive lock 4 If a transaction T requests to write an item and another transaction T holds a read or write lock on that item T is made to wait until T completes We say that the requested write operation con icts with the preViously granted read or write operation 5 Once a lock has been granted to atransaction the transaction retains the lock A read lock on an item allows the transaction to do subsequent reads of that item A write lock on an item allows the transaction to do subsequent reads or writes of that item When the transaction completes it releases all locks it has been granted Deadlock Suppose that transactions T1 and T2 both want to deposit money into the same account and hence they both want to execute the sequence readBalance writeBalance In one possible partial scheudle Tl read locks and reads Balance T2 read locks and reads Balance Tl request to write Balance but is made to wait because T2 has a read lock on it T2 requests to write Balance but is made to wait because Tl has a read lock on it This situation is called a deadlock DBMS must detect and resolve deadlocks Page 472 Atomicity and Durability Atomicity requres that a transaction either successfully completes and commits or aborts undoing any changes it made to the database Durability requires that after a transaction commits the changes it made ot the database are not lost even if the mass storage deVice on which the database is stored fails ACID atomic consistent isolated durable Properties of transactions in DBMS Pages 2025 Chapter 7 A trigger is an element of the database schema that has the following structure On event If precondition Then action Event is a request for the execution of a particular database operation insert delete etc Precondition is an expression that evaluates to true or false Action is a statement of what needs to be done when the trigger is red Trigger uses Constraint maintenance Enforce business rules Monitoring sensor based systems Maintenance of auxiliary cached data For example materialized views Separate core program logic from exception handling Example in pseudocode On inserting a row in course registration table If over course capacity Then abort registration transaction Two time when triggers can be invoked Immediately when the triggering event is requested Deferred until after the transaction commites Also when triggers are considered immediately their action may be executed immediately or it may be deferred until the end of the transaction Also when triggers are considered immediately the trigger can be executed after the triggering event is carried out before the triggering event is carried out or instead of the triggering event Trigger Granularity Trigger invoked on change to a row or when a statement is executed For example think of on delete cascade as a trigger Exam Question 3 Contrive a database design with two tables one table with a foreign key referencing the other table Supply create table statements for those two tables Supply pseudocode for triggers which implement l on delete cascade 2 on update cascade Page 258 Example 731 Business Rule Enforced with a BEFORE trigger Create Trigger roomcapacitycheck Before insert on transcript Referencing new as n For each row When select c0unttstudid from transcript t where t crscodencrscode and tsemestermsemester gt select llimit from crslimits l where lcrscodencrscode and lsemestermsemester rollback Example 732 Business rule enforced with an AFTER trigger Create trigger limitsalaryraose After update of salary on employee Referencing old as 0 New as n For each row When nsalarydsalarygt005salary Update employee set salary l05osalary Where id oid Example 733 Statementlevel trigger Create trigger recordnewaverage After update of salary on employee For each statement Insert into log Values currentidate select ansalary from employee