Week 5 DDL+Constviol PDF

SQL-DDL and Constraint Violation CMPS 342 Database Systems Spring 2022 Database Systems 1 Formal Relational Model Terminology Database Systems 2 Relational Mod...

SQL-DDL and Constraint Violation CMPS 342 Database Systems Spring 2022 Database Systems 1 Formal Relational Model Terminology Database Systems 2 Relational Model Notation Relation schema of degree 𝑛: 𝑅(𝐴1 , 𝐴2 , … , 𝐴𝑛 ) Relation name: 𝑅 Relation state: 𝑟(𝑅) Tuple: 𝑡 = < 𝑣1 , 𝑣2 , … , 𝑣𝑛 > Attribute: 𝐴 Tuple value in an attribute: 𝑡[𝐴𝑖 ] Domain of an attribute: 𝑑𝑜𝑚(𝐴𝑖 ) Database Systems 3 Relation Characteristics Ordering of tuples Ordering of values within a tuple Values and NULLs in Tuples Interpretation (Meaning of a Relation) Database Systems 4 Relational Database Schema A set of relation schemas denoted as 𝑆 = {𝑅1 , 𝑅2 , … , 𝑅𝑘 } and integrity constraints 𝐼𝐶 Database Systems 5 Relational Database State Database Systems 6 Phase 3 Database implementation using a DBMS Transform conceptual high level data model to implementation (DBMS specific) data model Microsoft SQL Server MySQL Oracle What is a Database Management System Computerized systems that enables users to create and maintain a database General purpose software system DBMS DBMS operations on a database 1. Define 2. Construct 3. Manipulate 4. Share DATABASE 5. Protect 6. Maintain Database Systems 9 Structure Query Language Originally was called SEQUEL (Structured English QUEry Language) Was designed and implemented at IBM research Now the standard language for commercial relational DBMSs Has statements for applying the DBMS operations Data Definition Language (DDL) Data Manipulation Language (DML) Data Control Language (DCL) Transaction Control Language (TCL) DDL + DML + DCL + TCL Database Systems 10 SQL Commands Chart SQL DDL DML DCL TCL Define Manipulate Control Schema Query data User access relation Update data Permissions datatype Domain views Database Systems 11 Let’s start with defining a database Main command for data definition is CREATE statement Can be used to Create schema Create table (relation) Create datatype Create domain Create view Database Systems 12 Creating Schema CREATE SCHEMA schema_name Elements can be defined later Not all users are authorized to create schemas and schema elements Privilege granted by the system administrator Database Systems 13 Creating Table Table or relation Name Attributes Name Datatype Domain Attribute constraints Initial constraints Key Entity integrity Referential integrity Database Systems 14 Creating Table CREATE TABLE schema_name.table_name (Attribute_name datatype attribute_constraint, Attribute_name datatype attribute_constraint, PRIMARY KEY (pk_attribute)); Constraints can be added later using the ALTER TABLE command Created tables are called base tables and stored as a file by the DBMS Attributes are ordered in the sequence they are specified here Database Systems 15  Creating Table Use wizard to create the Employee relation Execute an SQL query to create the department relation CREATE TABLE DEPARTMENT (Dname VARCHAR(15) NOT NULL, Dnumber INT NOT NULL, Mgr_ssn CHAR(9) NOT NULL, Mgr_start_date DATE, PRIMARY KEY (Dnumber), UNIQUE (Dname)); Database Systems 16 Attribute Datatypes Numeric INTEGER, INT, SMALLINT FLOAT, REAL, DOUBLE PRECISION DECIMAL(i,j) Character String CHAR(n), VARCHAR(n) Boolean Date Bit-string Database Systems 17 Creating Domain Domain Domain name Domain specification CREATE DOMAIN domain_name AS datatype Database Systems 18 Attribute Constraints NULL vs NOT NULL Implicitly specified for attributes that are part of the primary key Can be specified for any other attribute Attribute default value DEFAULT default_value A default value is added if no value is provided Condition on values CHECK (condition_statement) A default value is added if no value is provided Database Systems 19 Key Constraint Primary key PRIMARY KEY(attribute or multiple attributes) Follow the attribute definition with PRIMARY KEY Candidate key Similar to before but use UNIQUE Database Systems 20 Referential Integrity Constraint FOREIGN KEY (attribute) REFERENCES table(attribute) Added after the attributes like the key constraint You can also add actions for integrity violation ON DELETE and ON UPDATE RESTRICT to reject operation SET NULL sets the FK attribute values to NULL CASCADE modify FK attribute values SET DEFAULT set the FK attribute value to default Database Systems 21 Other DDL commands DROP TABLE table_name Delete table TRUNCATE TABLE table_name Delete all entries in the table without deleting its structure Database Systems 22 Adding Key and Integrity Constraints Database Systems 23 Translate the EMPLOYEE entity type into a relation Database Systems 24 What about Weak Entity DEPENDENT? Database Systems 25 MANAGES 1:1 Relationship ALTER TABLE DEPARTMENT Add relationship attribute Add a foreign key as an attribute in attribute referencing the DEPARTMENT table Ssn attribute Set it as UNIQUE why? Database Systems 26 WORKS_FOR N:1 Relationship ALTER TABLE EMPLOYEE Add a foreign key referencing the Dnumber attribute How is it different from 1:1 relationship? How to ensure total participation? Database Systems 27 Do the same for all 1:1 and 1:N Relationships ALTER TABLE PROJECT Dnum INT NOT NULL FOREIGN KEY (Dnum) REFERENCES DEPARTMENT(Dnumber) ALTER TABLE EMPLOYEE Super_Ssn INT NULL FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn) Done as part of the weak entity type mapping Database Systems 28 WORKS_ON M:N Relationship Database Systems 29 Multivalued Attribute Locations Database Systems 30  SQL DDL DML DCL TCL Define Schemas, create roles, Transaction Relations, Domains, Query Data permissions, access specific Views CREATE Update Data DROP ALTER TRUNCATE Database Systems 31 Relational Data Model Operations Query Operations Update Operations Querying a database to retrieve Change the state of the database information relations User formulates query for data Insert of interest Delete Update A new relation is formed by applying the query Must satisfy integrity constraints Database Systems 32 Manipulating the Database by Updating Data Three commands are used to modify the database content INSERT DELETE UPDATE Database Systems 33 Inserting Data Use INSERT to add a single tuple o a relation Values should be listed in the order of the attributes If no attributes are specified, add values for all relation attributes Must insert values for NOT NULL attributes INSERT INTO RELATION(attributes) VALUES (‘attributeval’, ‘attributeval’,…); Database Systems 34 Inserting Data Database Systems 35 Deleting Data Use DELETE to remove tuples from a relation Includes a WHERE clause to select tuples to be deleted If no where clause is added all tuples are deleted DELETE FROM RELATION WHERE deletion_condition Database Systems 36 Deleting Data Database Systems 37 Updating Data Use UPDATE to modify attribute value(s) of tuple(s) in one relation WHERE clause in the command selects the tuples to be modified from a single relation. An additional SET clause in the command specifies the attributes to be modified and their new values. UPDATE RELATION SET attribute = value WHERE update_condition Database Systems 38 Updating Data change the location and controlling department number of project number 10 to ‘Bellaire’ and 5, respectively Database Systems 39 Updating Data Give all employees in the ‘Research’ department a 10% raise in salary Database Systems 40 Relational Data Model Operations Query Operations Update Operations Querying a database to retrieve Change the state of the database information relations User formulates query for data Insert of interest Delete Update A new relation is formed by applying the query Must satisfy integrity constraints Database Systems 41 Insert Operation Provide a list of attribute values for a new tuple 𝑡 to be inserted to a relation 𝑅 Attribute Key value value not already in domain exists Foreign Primary key has no key is reference NULL value Database Systems 43 Database Systems 44 Database Systems 45 Database Systems 46 Database Systems 47 Handling Insert Constraint Violation Reject insertion Attempt to correct reason for rejecting insertion ➔ Ask user to provide another value of department number or set it to NULL Database Systems 48  Delete Operation Select a tuple to be deleted by specifying corresponding attribute values Can only violate referential integrity Deleted tuple is referenced by a foreign key Database Systems 49 Database Systems 50 Database Systems 51 Database Systems 52 Handling Delete Constraint Violation Reject the deletion Cascade or propagate the deletion by deleting tuples that reference the deleted tuple Set referencing value to NULL if not primary key ON DELETE RESTRICT to reject operation SET NULL sets the FK attribute values to NULL CASCADE modify FK attribute values SET DEFAULT set the FK attribute value to default Database Systems 53 Handling Delete Constraint Violation ALTER TABLE EMPLOYEE FOREIGN KEY (Dno) REFERENCES DEPARTMENT (Dnumber) ON DELETE CASCADE; Database Systems 54 Update Operation Change values of one or more attributes in a tuple of some relation Attribute Key value value not already in domain exists Foreign Primary key has no key is reference NULL value Database Systems 55 Database Systems 56 Database Systems 57 Database Systems 58 Database Systems 59 Handling Update Constraint Violation Reject the update Cascade or propagate the update by updating tuples that reference the updated tuple ON UPDATE RESTRICT to reject operation CASCADE modify FK attribute values ALTER TABLE EMPLOYEE FOREIGN KEY (Dno) REFERENCES DEPARTMENT (Dnumber) ON DELETE CASCADE ON UPDATE RESTRICT; Database Systems 60 Reading Reference Database Systems 61