Unit 4_Relational Data Model and Relational Database Constraints.pdf

UNIT: 4 RELATIONAL DATA MODEL AND Course Facilitator: Gaurav Bhattarai RELATIONAL DATABASE CONSTRAINTS 1. Relational Model Concepts The Schema of a Relation: R (A1, A2,.....An) Relation schema R is defined over attributes A1, A2,....An...

UNIT: 4 RELATIONAL DATA MODEL AND Course Facilitator: Gaurav Bhattarai RELATIONAL DATABASE CONSTRAINTS 1. Relational Model Concepts The Schema of a Relation: R (A1, A2,.....An) Relation schema R is defined over attributes A1, A2,....An Example: CUSTOMER (Cust-id, Cust-name, Address, Phone#) CUSTOMER is a relation defined over the four attributes Cust-id, Cust- name, Address, Phone#, each of which has a domain or a set of valid values. For example, the domain of Cust-id is 6 digit numbers. Relational Model Concepts A domain has a logical definition. “USA_phone_numbers” are the set of 10 digit phone numbers valid in the U.S. A domain may have a data-type or a format defined for it. The USA_phone_numbers may have a format: (ddd)-ddd-dddd where each d is a decimal digit. Dates have various formats such as month_name, date, year or yyyy-mm- dd, or dd mm,yyyy etc Relational Model Concepts Relational model in DBMS is an approach to logically represent and manage the data stored in a database by storing data in tables. Relational Model (RM) represents the database as a collection of relations. A relation is nothing but a table of values. Relational Model Concepts Relational Model Concepts in DBMS Attribute: Each column in a Table. Attributes are the properties which define a relation. e.g., Student_Rollno, NAME, etc. Tables: In the Relational model the, relations are saved in the table format. It is stored along with its entities. A table has two properties rows and columns. Rows represent records and columns represent attributes. Tuple: It is nothing but a single row of a table, which contains a single record. Relation Schema: A relation schema represents the name of the relation with its attributes. Degree: The total number of attributes which in the relation is called the degree of the relation. Cardinality: Total number of rows present in the Table. Column: The column represents the set of values for a specific attribute. Relation instance: Relation instance is a finite set of tuples in the RDBMS system. Relation instances never have duplicate tuples. Relation key: Every row has one, two or multiple attributes, which is called relation key. Attribute domain: Every attribute has some pre-defined value and scope which is known as attribute domain  Relational Model Constraints Relational models make use of some rules to ensure the accuracy and accessibility of the data. These rules or constraints are known as Relational Integrity Constraints. These constraints are checked before performing any operation like insertion, deletion, or updation on the data present in a relational database. These constraints include: Domain Constraint : It specifies that every attribute is bound to have a value that lies inside a specific range of values. It is implemented with the help of the Attribute Domain concept. Key Constraint : It states that every relation must contain an attribute or a set of attributes (Primary Key) that can uniquely identify a tuple in that relation. This key can never be NULL or contain the same value for two different tuples. Referential Integrity Constraint : It is defined between two inter-related tables. It states that if a given relation refers to a key attribute of a different or same table, then that key must exist in the given relation. Relational Model Constraints 1. Domain (column) constraints Domain constraints can be defined as the definition of a valid set of values for an attribute. The data type of domain includes string, character, integer, time, date, currency, etc. The value of the attribute must be available in the corresponding domain. Example: The Relational Data Model and Relational Database Constraints Unit 4 2. Entity integrity constraints (Primary key) The entity integrity constraint states that primary key value can't be null. This is because the primary key value is used to identify individual rows in relation and if the primary key has a null value, then we can't identify those rows. A table can contain a null value other than the primary key field. Example: The Relational Data Model and Relational Database Constraints Unit 4 3. Referential Integrity Constraints A referential integrity constraint is specified between two tables. In the Referential integrity constraints, if a foreign key in Table 1 refers to the Primary Key of Table 2, then every value of the Foreign Key in Table 1 must be null or be available in Table 2. Example: 4. Key constraints Keys are the entity set that is used to identify an entity within its entity set uniquely. An entity set can have multiple keys, but out of which one key will be the primary key. A primary key can contain a unique and null value in the relational table. Example: The Relational Data Model and Relational Database Constraints Unit 4 RELATIONAL MODEL CONSTRAINTS TRIGGER A trigger is a statement that is executed automatically by the system as a side effect of a modification to the database. To design a trigger mechanism, we must: Specify the conditions under which the trigger is to be executed. Specify the actions to be taken when the trigger executes. The SQL standard does not include triggers, but many implementations support triggers. The Relational Data Model and Relational Database Constraints Unit 4 TRIGGER A trigger is a stored procedure in database which automatically invokes whenever a special event in the database occurs. For example, a trigger can be invoked when a row is inserted into a specified table or when certain table columns are being updated. Syntax: create trigger [trigger_name] [before | after] {insert | update | delete} on [table_name] [for each row] BEGIN [trigger_body] END [for each row]: This specifies a row-level trigger, i.e., the trigger will be executed for each row being affected. [trigger_body]: This provides the operation to be performed as trigger is fired CREATE TRIGGER 'student_marks' BEFORE INSERT ON 'student' FOR EACH ROW BEGIN SET NEW.total = NEW.subj1 + NEW.subj2 + NEW.subj3; SET NEW.per = New.total * 60/100; END The Relational Data Model and Relational Database Constraints Unit 4 RELATIONAL MODEL CONSTRAINTS ASSERTION: An assertion is predicate expressing a condition that we wish the database always to satisfy. An assertion in Syntax takes the form: When an assertion is made, the system tests it for validity. DBMS checks the assertion after any change that may violate the expression; hence assertions should be used with great care. The SQL standard does not include Assertion, but many implementations support Assertion. Assertion: Assertion:  Assertion: CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CHECK (Age>=18) ); CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, City varchar(255), CONSTRAINT CHK_Person CHECK (Age>=18 AND City=‘Chitwan') ); ALTER TABLE Persons ALTER TABLE Persons ADD CONSTRAINT CHK_PersonAge CHECK (Age DROP CONSTRAINT CHK_PersonAge; >=18 AND City=‘Jhapa '); RELATIONAL DATABASE SCHEMAS A relational database schema is an arrangement of relation states in such a manner that every relational database state fulfills the integrity constraints set on a relational database schema. The Relational Data Model and Relational Database Constraints Unit 4 UPDATE OPERATIONS On the database modification or update operations. There are three basic operations that can change the states of relations in the database: Insert, Delete, and Update (or Modify). They insert new data, delete old data, or modify existing data records. 1. Insert is used to insert one or more new tuples in a relation 2. Delete is used to delete tuples 3. Update (or Modify) is used to change the values of some attributes in existing tuples. Whenever these operations are applied, the integrity constraints specified on the relational database schema should not be violated. TRANSACTION A database application program running against a relational database typically executes one or more transactions. A transaction is an executing program that includes some database operations, such as reading from the database, or applying insertions, deletions, or updates to the database. At the end of the transaction, it must leave the database in a valid or consistent state that satisfies all the constraints specified on the database schema. For example, a transaction to apply a bank withdrawal will typically read the user account record, check if there is a sufficient balance, and then update the record by the withdrawal amount. A large number of commercial applications running against relational databases in online transaction processing (OLTP) systems are executing transactions at rates that reach several hundred per second. Transaction processing concepts, concurrent execution of transactions, and recovery from failures. Discuss different types of update operations in relation to the integrity constraints which must be satisfied in a relational database model? DEALING WITH CONSTRAINT VIOLATIONS The following are the different types of update operations in relation to the integrity constraints which must be satisfied in a relational database model. Insert- This implies addition of a new tuple in the existing relation. This operation can violate any of the integrity constraints. Delete- This operation is used to remove an existing tuple from the relation. This operation can violate Referential Integrity Constraints, if the removed tuple is reference by foreign key from other tuples in the database. Modify- This operation is used to make changes in the existing values of the records in a data table. Generally, this operation does not lead to any violation problems, if the modification is done except on Primary key and on Foreign keys. Hence, we can conclude that after applying these update operations, integrity constraints are specified on the relational database schema. Explain the operations and constraint violations? The Relational Data Model and Relational Database Constraints Unit 4 THANK YOU