Database Management Systems Lecture PDF

Database Management Systems SQL overview, Data definition, Structure of SQL queries Database Management Systems Unit 2: Relational Model and Database Design Slides adapted from Author Slides of “Database System Concepts”, Silberschatz, H Korth and S Sudarshan, McGrawHill, 7th Edition, 2019. And Au...

Database Management Systems SQL overview, Data definition, Structure of SQL queries Database Management Systems Unit 2: Relational Model and Database Design Slides adapted from Author Slides of “Database System Concepts”, Silberschatz, H Korth and S Sudarshan, McGrawHill, 7th Edition, 2019. And Author Slides of Fundamentals of Database Systems”, Ramez Elamsri, Shamkant B Navathe, Pearson, 7th Edition, 2017.  Database Management Systems Unit 2: overview: 1. Relational Query language, Algebra & Unary Operations 2. Binary Operations 3. Set Operations, Equivalent Queries 4. Aggregate Functions and Grouping 5. SQL overview, Data definition, Structure of SQL queries 6. Database Modification 7. Additional Basic Operations 8. Set Operations and Null Values 9. Aggregate Functions 10. Nested Subqueries 11. Join expressions 12. Index definitions, Authorization 13. Functions, Procedures 14. Triggers and Recursive Queries 15. Query Processing, Query Optimization  Database Management Systems Specifying Constraints in SQL Constraints in SQL refer to the conditions and restrictions that are applied on the database This further means that before inserting data into the database, we are checking for some conditions. If the condition we have applied to the database holds true for the data that is to be inserted, then only the data will be inserted into the database tables. Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement Database Management Systems Specifying Constraints in SQL Basic constraints: Relational Model has 3 basic constraint types that are supported in SQL: Key constraint: A primary key value cannot be duplicated Entity Integrity Constraint: A primary key value cannot be null Referential integrity constraints : The “foreign key “ must have a value that is already present as a primary key, or may be null Database Management Systems Specifying Constraints in SQL Other Restrictions on attribute domains: Default value of an attribute DEFAULT NULL is not permitted for a particular attribute (NOT NULL) CHECK clause Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21);  Database Management Systems Specifying Constraints in SQL NOT NULL Because SQL allows NULLs as attribute values, a constraint NOT NULL may be specified if NULL is not permitted for a particular attribute. This is always implicitly specified for the attributes that are part of the primary key of each relation, but it can be specified for any other attributes whose values are required not to be NULL Example:  Database Management Systems Specifying Constraints in SQL DEFAULT It is also possible to define a default value for an attribute by appending the clause DEFAULT to an attribute definition. The default value is included in any new tuple if an explicit value is not provided for that attribute If no default clause is specified, the default default value is NULL for attributes that do not have the NOT NULL constraint. Example: Database Management Systems Specifying Constraints in SQL PRIMARY KEY clause Specifies one or more attributes that make up the primary key of a relation Dnumber INT PRIMARY KEY; UNIQUE clause Specifies alternate (secondary) keys (called CANDIDATE keys in the relational model). Dname VARCHAR(15) UNIQUE; Database Management Systems Specifying Constraints in SQL FOREIGN KEY clause Default operation: reject update on violation Attach referential triggered action clause Options include SET NULL, CASCADE, and SET DEFAULT Action taken by the DBMS for SET NULL or SET DEFAULT is the same for both ON DELETE and ON UPDATE CASCADE option suitable for “relationship” relations  Database Management Systems Specifying Constraints in SQL CREATE TABLE EMPLOYEE ( … , Using the Keyword CONSTRAINT Name a constraint Dno INT NOT NULL DEFAULT 1, Useful for later altering CONSTRAINT EMPPK PRIMARY KEY (Ssn), CONSTRAINT EMPSUPERFK FOREIGN KEY (Super_ssn) REFERENCES The general structure of the SQL EMPLOYEE(Ssn) CONSTRAINT is defined as: ON DELETE SET NULL ON UPDATE CASCADE, The CONSTRAINT keyword is followed CONSTRAINT EMPDEPTFK by a constraint name followed by a FOREIGN KEY(Dno) REFERENCES column or a list of columns. DEPARTMENT(Dnumber) ON DELETE SET DEFAULT ON UPDATE CASCADE); Database Management Systems Example for Specifying Constraints in SQL Database Management Systems Specifying Constraints in SQL CREATE TABLE DEPT_LOCATIONS ( Dnumber INT NOT NULL, Dlocation VARCHAR(15) NOT NULL DEFAULT ‘Banglore’, PRIMARY KEY (Dnumber, Dlocation), Constraint “fk_dno ” FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT(Dnumber) ); Referential actions FOREIGN KEY (foreign_key_columns) REFERENCES parent_table(parent_key_columns) ON UPDATE action ON DELETE action;  Database Management Systems Referential actions Delete actions of rows in the parent table If you delete one or more rows in the parent table, you can set one of the following actions: ON DELETE NO ACTION: SQL Server raises an error and rolls back the delete action on the row in the parent table. ON DELETE CASCADE: SQL Server deletes the rows in the child table that is corresponding to the row deleted from the parent table. ON DELETE SET NULL: SQL Server sets the rows in the child table to NULL if the corresponding rows in the parent table are deleted. To execute this action, the foreign key columns must be nullable. ON DELETE SET DEFAULT SQL Server sets the rows in the child table to their default values if the corresponding rows in the parent table are deleted. To execute this action, the foreign key columns must have default definitions. Note that a nullable column has a default value of NULL if no default value specified. By default, SQL Server applies ON DELETE NO ACTION if you don’t explicitly specify any action. Database Management Systems Referential actions Update action of rows in the parent table If you update one or more rows in the parent table, you can set one of the following actions: ON UPDATE NO ACTION: SQL Server raises an error and rolls back the update action on the row in the parent table. ON UPDATE CASCADE: SQL Server updates the corresponding rows in the child table when the rows in the parent table are updated. ON UPDATE SET NULL: SQL Server sets the rows in the child table to NULL when the corresponding row in the parent table is updated. Note that the foreign key columns must be nullable for this action to execute. ON UPDATE SET DEFAULT: SQL Server sets the default values for the rows in the child table that have the corresponding rows in the parent table updated. Database Management Systems Specifying Constraints on Tuples Using CHECK Additional Constraints on individual tuples within a relation are also possible using CHECK CHECK clauses at the end of a CREATE TABLE statement Apply to each tuple individually CHECK (Dept_create_date