Understanding SQL Constraints and Triggers in Database Systems

SQL (Structured Query Language) provides a high-level approach to database management by focusing on what needs to be done rather than how. Constraints and triggers play vital roles in ensuring data integrity and enforcing specific actions. Keys, foreign keys, value-based constraints, tuple-based constraints, and assertions are essential elements in maintaining data accuracy. By studying database systems, complexities in data storage and manipulation can be efficiently managed to ensure reliability, security, and consistency.

• SQL
• Constraints
• Triggers
• Database Systems
• Data Integrity

ndunn Follow

Uploaded on Sep 26, 2024 | 0 Views

Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

Presentation Transcript

1. CSCE-608 Database Systems Spring 2024 Instructor: Jianer Chen Office: PETR 428 Phone: 845-4259 Email: chen@cse.tamu.edu Notes 13: SQL Constraints and triggers

2. SQL: Structured Query language a very-high-level language. * say what to do rather than how to do it. * avoid a lot of data-manipulation details needed in procedural languages like C++ or Java. Database management system figures out the best way to execute queries * called query optimization For both data definition and data manipulation.

3. Database is just another model of information processing Then why study DB? Much more data, regular data techs would be very inefficient: How should data be stored? Operations are simpler & more specific: How do we take advantage of it? New programming languages for the above. (ACID) Reliability, security, consistency, currency , , , , , \, , , C Data (in disks) SQL

4. Constraints and Triggers A constraint is a relationship among data elements that the DBMS is required to enforce. -- e.g., key constraints. A trigger is an action only executed when a specified condition occurs, e.g., insertion of a tuple. -- easier to implement than complex constraints.

5. Constraints and Triggers A constraint is a relationship among data elements that the DBMS is required to enforce. -- e.g., key constraints. A trigger is an action only executed when a specified condition occurs, e.g., insertion of a tuple. -- easier to implement than complex constraints.

6. Kinds of Constraints Keys (unique, cannot be NULL). Foreign-key -- referential-integrity. Value-based constraints. -- constrain values of a particular attribute. Tuple-based constraints. -- relationship among components. Assertions. -- any SQL boolean expression.

7. Kinds of Constraints Keys (unique, cannot be NULL). Foreign-key -- referential-integrity. Value-based constraints. -- constrain values of a particular attribute. Tuple-based constraints. -- relationship among components. Assertions. -- any SQL boolean expression.

8. Sells(bar, beer, price) Beers(name, manf) Foreign Keys Consider the relation Sells(bar, beer, price). We might expect that a beer value in Sells is a real beer --- something appearing in Beers.name. 8

9. Sells(bar, beer, price) Beers(name, manf) Foreign Keys Consider the relation Sells(bar, beer, price). We might expect that a beer value in Sells is a real beer --- something appearing in Beers.name. A constraint that requires a beer in Sells to be a beer in Beers is called a foreign-key constraint. 9

10. Expressing Foreign Keys Use the keyword REFERENCES, either: -- Within the declaration of an attribute (only for one-attribute keys). -- As an element of the schema: FOREIGN KEY (<attributes>) REFERENCES <relation> (<attributes>) 10

11. Expressing Foreign Keys Use the keyword REFERENCES, either: -- Within the declaration of an attribute (only for one-attribute keys). -- As an element of the schema: FOREIGN KEY (<attributes>) REFERENCES <relation> (<attributes>) Referenced attributes must be declared PRIMARY KEY or UNIQUE in <relation>. 11

12. Sells(bar, beer, price) Beers(name, manf) Example: With Attribute CREATE TABLE Beers ( name manf CHAR(20) PRIMARY KEY, CHAR(20) ); 12

13. Sells(bar, beer, price) Beers(name, manf) Example: With Attribute CREATE TABLE Beers ( name manf CHAR(20) PRIMARY KEY, CHAR(20) ); CREATE TABLE Sells ( bar beer price CHAR(20), CHAR(20) REFERENCES Beers(name), REAL ); 13

14. Sells(bar, beer, price) Beers(name, manf) Example: With Attribute CREATE TABLE Beers ( name manf CHAR(20) PRIMARY KEY, CHAR(20) ); CREATE TABLE Sells ( bar beer price CHAR(20), CHAR(20) REFERENCES Beers(name), REAL ); 14

15. Sells(bar, beer, price) Beers(name, manf) Example: As Element CREATE TABLE Beers ( name manf CHAR(20) PRIMARY KEY, CHAR(20) ); CREATE TABLE Sells ( bar price FOREIGN KEY(beer) REFERENCES Beers(name)); CHAR(20), REAL, 15

16. Sells(bar, beer, price) Beers(name, manf) Example: As Element CREATE TABLE Beers ( name manf CHAR(20) PRIMARY KEY, CHAR(20) ); CREATE TABLE Sells ( bar price FOREIGN KEY(beer) REFERENCES Beers(name)); CHAR(20), REAL, 16

17. Sells(bar, beer, price) Beers(name, manf) Example: As Element CREATE TABLE Beers ( name manf CHAR(20) PRIMARY KEY, CHAR(20) ); CREATE TABLE Sells ( bar price FOREIGN KEY(beer) REFERENCES Beers(name)); CHAR(20), REAL, can be a list of more than one attributes 17

18. Sells(bar, beer, price) Beers(name, manf) Example: As Element CREATE TABLE Beers ( name manf CHAR(20) PRIMARY KEY, CHAR(20) ); Remark. Attributes in a foreign key MAY have value NULL CREATE TABLE Sells ( bar price FOREIGN KEY(beer) REFERENCES Beers(name)); CHAR(20), REAL, can be a list of more than one attributes 18

19. Enforcing Foreign-Key Constraints If there is a foreign-key constraint from attributes of relation R to a key of relation S, two violations are possible: 19

20. Enforcing Foreign-Key Constraints If there is a foreign-key constraint from attributes of relation R to a key of relation S, two violations are possible: -- An insert or update to R introduces values not found in S. 20

21. Sells(bar, beer, price) Beers(name, manf) Enforcing Foreign-Key Constraints If there is a foreign-key constraint from attributes of relation R to a key of relation S, two violations are possible: -- An insert or update to R introduces values not found in S. Sells (R) Beers (S) bar beer price name manf Joe s Bud 3.00 Bud A.B. Sue s Miller 3.50 Miller MBC 21

22. Sells(bar, beer, price) Beers(name, manf) Enforcing Foreign-Key Constraints If there is a foreign-key constraint from attributes of relation R to a key of relation S, two violations are possible: -- An insert or update to R introduces values not found in S. Sells (R) Beers (S) bar beer price name manf Joe s Bud 3.00 Bud A.B. Sue s Miller 3.50 Miller MBC Sue s Bud Lite 3.20 22 No Bud Lite

23. Sells(bar, beer, price) Beers(name, manf) Enforcing Foreign-Key Constraints If there is a foreign-key constraint from attributes of relation R to a key of relation S, two violations are possible: -- An insert or update to R introduces values not found in S. -- A deletion or update to S causes some tuples of R to dangle. Sells (R) Beers (S) Sells (R) Beers (S) bar beer price name manf bar beer price name manf Joe s Bud 3.00 Bud A.B. Joe s Bud 3.00 Bud A.B. Sue s Miller 3.50 Miller MBC Sue s Miller 3.50 Miller MBC Sue s Bud Lite 3.20 23 No Bud Lite

24. Sells(bar, beer, price) Beers(name, manf) Enforcing Foreign-Key Constraints If there is a foreign-key constraint from attributes of relation R to a key of relation S, two violations are possible: -- An insert or update to R introduces values not found in S. -- A deletion or update to S causes some tuples of R to dangle. Sells (R) Beers (S) Sells (R) Beers (S) bar beer price name manf bar beer price name manf ? Joe s Bud 3.00 Bud A.B. Joe s Bud 3.00 Bud A.B. Sue s Miller 3.50 Miller MBC Sue s Miller 3.50 Miller MBC Sue s Bud Lite 3.20 24 No Bud Lite

25. Sells(bar, beer, price) Beers(name, manf) Actions Taken Suppose R = Sells, S = Beers. 25

26. Sells(bar, beer, price) Beers(name, manf) Actions Taken Suppose R = Sells, S = Beers. An insert or update to Sells that introduces a nonexistent beer must be rejected. 26

27. Sells(bar, beer, price) Beers(name, manf) Actions Taken Suppose R = Sells, S = Beers. An insert or update to Sells that introduces a nonexistent beer must be rejected. A deletion or update to Beers that removes a beer value found in some tuples of Sells can be handled in three ways: 27

28. Sells(bar, beer, price) Beers(name, manf) Actions Taken Suppose R = Sells, S = Beers. An insert or update to Sells that introduces a nonexistent beer must be rejected. A deletion or update to Beers that removes a beer value found in some tuples of Sells can be handled in three ways: -- Default: Reject the modification. 28

29. Sells(bar, beer, price) Beers(name, manf) Actions Taken Suppose R = Sells, S = Beers. An insert or update to Sells that introduces a nonexistent beer must be rejected. A deletion or update to Beers that removes a beer value found in some tuples of Sells can be handled in three ways: -- Default: Reject the modification. -- Cascade: Make the same changes in Sells. 29

30. Sells(bar, beer, price) Beers(name, manf) Actions Taken Suppose R = Sells, S = Beers. An insert or update to Sells that introduces a nonexistent beer must be rejected. A deletion or update to Beers that removes a beer value found in some tuples of Sells can be handled in three ways: -- Default: Reject the modification. -- Cascade: Make the same changes in Sells. * Deleted beer: delete Sells tuple. 30

31. Sells(bar, beer, price) Beers(name, manf) Actions Taken Suppose R = Sells, S = Beers. An insert or update to Sells that introduces a nonexistent beer must be rejected. A deletion or update to Beers that removes a beer value found in some tuples of Sells can be handled in three ways: -- Default: Reject the modification. -- Cascade: Make the same changes in Sells. * Deleted beer: delete Sells tuple. * Updated beer: change value in Sells. 31

32. Sells(bar, beer, price) Beers(name, manf) Actions Taken Suppose R = Sells, S = Beers. An insert or update to Sells that introduces a nonexistent beer must be rejected. A deletion or update to Beers that removes a beer value found in some tuples of Sells can be handled in three ways: -- Default: Reject the modification. -- Cascade: Make the same changes in Sells. * Deleted beer: delete Sells tuple. * Updated beer: change value in Sells. -- Set NULL: Change the beer (in Sells) to NULL. 32

33. Sells(bar, beer, price) Beers(name, manf) Example: Cascade 33

34. Sells(bar, beer, price) Beers(name, manf) Example: Cascade Delete the Bud tuple from Beers: -- Then delete all tuples from Sells that have beer = Bud . 34

35. Sells(bar, beer, price) Beers(name, manf) Example: Cascade Delete the Bud tuple from Beers: -- Then delete all tuples from Sells that have beer = Bud . Sells (R) Beers (S) bar beer price name manf Joe s Bud 3.00 Bud A.B. Sue s Miller 3.50 Miller MBC Sue s Bud 3.20 35

36. Sells(bar, beer, price) Beers(name, manf) Example: Cascade Delete the Bud tuple from Beers: -- Then delete all tuples from Sells that have beer = Bud . Sells (R) Beers (S) bar beer price name manf Joe s Bud 3.00 Bud A.B. Sue s Miller 3.50 Miller MBC Sue s Bud 3.20 36

37. Sells(bar, beer, price) Beers(name, manf) Example: Cascade Delete the Bud tuple from Beers: -- Then delete all tuples from Sells that have beer = Bud . Sells (R) Beers (S) bar beer price name manf Joe s Bud 3.00 Bud A.B. Sue s Miller 3.50 Miller MBC Sue s Bud 3.20 37

38. Sells(bar, beer, price) Beers(name, manf) Example: Cascade Delete the Bud tuple from Beers: -- Then delete all tuples from Sells that have beer = Bud . Update the Bud tuple by changing Bud to Budweiser : -- Then change all Sells tuples with beer = Bud so that beer = Budweiser . Sells (R) Beers (S) bar beer price name manf Joe s Bud 3.00 Bud A.B. Sue s Miller 3.50 Miller MBC Sue s Bud 3.20 38

39. Sells(bar, beer, price) Beers(name, manf) Example: Cascade Delete the Bud tuple from Beers: -- Then delete all tuples from Sells that have beer = Bud . Update the Bud tuple by changing Bud to Budweiser : -- Then change all Sells tuples with beer = Bud so that beer = Budweiser . Sells (R) Sells (R) Beers (S) Beers (S) bar beer price name manf name manf bar beer price Joe s Bud 3.00 Bud A.B. Bud A.B. Joe s Bud 3.00 Sue s Miller 3.50 Miller MBC Miller MBC Sue s Miller 3.50 Sue s Bud 3.20 Sue s Bud 3.20 39

40. Sells(bar, beer, price) Beers(name, manf) Example: Cascade Delete the Bud tuple from Beers: -- Then delete all tuples from Sells that have beer = Bud . Update the Bud tuple by changing Bud to Budweiser : -- Then change all Sells tuples with beer = Bud so that beer = Budweiser . Sells (R) Sells (R) Beers (S) Beers (S) bar beer price name manf name Budweiser manf bar beer price Joe s Bud 3.00 Bud A.B. Bud A.B. Joe s Bud 3.00 Sue s Miller 3.50 Miller MBC Miller MBC Sue s Miller 3.50 Sue s Bud 3.20 Sue s Bud 3.20 40

41. Sells(bar, beer, price) Beers(name, manf) Example: Cascade Delete the Bud tuple from Beers: -- Then delete all tuples from Sells that have beer = Bud . Update the Bud tuple by changing Bud to Budweiser : -- Then change all Sells tuples with beer = Bud so that beer = Budweiser . Sells (R) Sells (R) Beers (S) Beers (S) bar beer price name manf name Budweiser manf bar beer Budweiser price Joe s Bud 3.00 Bud A.B. Bud A.B. Joe s Bud 3.00 Sue s Miller 3.50 Miller MBC Miller MBC Sue s Miller Budweiser 3.50 Sue s Bud 3.20 Sue s Bud 3.20 41

42. Sells(bar, beer, price) Beers(name, manf) Example: Set NULL 42

43. Sells(bar, beer, price) Beers(name, manf) Example: Set NULL Delete the Bud tuple from Beers: -- Change all tuples of Sells that have beer = Bud to have beer = NULL. 43

44. Sells(bar, beer, price) Beers(name, manf) Example: Set NULL Delete the Bud tuple from Beers: -- Change all tuples of Sells that have beer = Bud to have beer = NULL. Sells (R) Beers (S) bar beer price name manf Joe s Bud 3.00 Bud A.B. Sue s Miller 3.50 Miller MBC Sue s Bud 3.20 44

45. Sells(bar, beer, price) Beers(name, manf) Example: Set NULL Delete the Bud tuple from Beers: -- Change all tuples of Sells that have beer = Bud to have beer = NULL. Sells (R) Beers (S) bar beer price name manf Joe s Bud 3.00 Bud A.B. Sue s Miller 3.50 Miller MBC Sue s Bud 3.20 45

46. Sells(bar, beer, price) Beers(name, manf) Example: Set NULL Delete the Bud tuple from Beers: -- Change all tuples of Sells that have beer = Bud to have beer = NULL. Sells (R) Beers (S) bar beer price name manf Bud NULL Joe s 3.00 Bud A.B. Sue s Miller NULL 3.50 Miller MBC Sue s Bud 3.20 46

47. Sells(bar, beer, price) Beers(name, manf) Example: Set NULL Delete the Bud tuple from Beers: -- Change all tuples of Sells that have beer = Bud to have beer = NULL. Update the Bud tuple by changing Bud to Budweiser : -- the same change as above. Sells (R) Beers (S) bar beer price name manf Bud NULL Joe s 3.00 Bud A.B. Sue s Miller NULL 3.50 Miller MBC Sue s Bud 3.20 47

48. Sells(bar, beer, price) Beers(name, manf) Example: Set NULL Delete the Bud tuple from Beers: -- Change all tuples of Sells that have beer = Bud to have beer = NULL. Update the Bud tuple by changing Bud to Budweiser : -- the same change as above. Sells (R) Sells (R) Beers (S) Beers (S) bar beer price name manf name manf bar beer price Bud NULL Joe s 3.00 Bud A.B. Bud A.B. Joe s Bud 3.00 Sue s Miller NULL 3.50 Miller MBC Miller MBC Sue s Miller 3.50 Sue s Bud 3.20 Sue s Bud 3.20 48

49. Sells(bar, beer, price) Beers(name, manf) Example: Set NULL Delete the Bud tuple from Beers: -- Change all tuples of Sells that have beer = Bud to have beer = NULL. Update the Bud tuple by changing Bud to Budweiser : -- the same change as above. Sells (R) Sells (R) Beers (S) Beers (S) bar beer price name manf name Budweiser manf bar beer price Bud NULL Joe s 3.00 Bud A.B. Bud A.B. Joe s Bud 3.00 Sue s Miller NULL 3.50 Miller MBC Miller MBC Sue s Miller 3.50 Sue s Bud 3.20 Sue s Bud 3.20 49

50. Sells(bar, beer, price) Beers(name, manf) Example: Set NULL Delete the Bud tuple from Beers: -- Change all tuples of Sells that have beer = Bud to have beer = NULL. Update the Bud tuple by changing Bud to Budweiser : -- the same change as above. Sells (R) Sells (R) Beers (S) Beers (S) bar beer price name manf name Budweiser manf bar beer NULL price Bud NULL Joe s 3.00 Bud A.B. Bud A.B. Joe s Bud 3.00 Sue s Miller NULL 3.50 Miller MBC Miller MBC Sue s Miller NULL 3.50 Sue s Bud 3.20 Sue s Bud 3.20 50

Load More ...