SQL-99 : Schema Definition, Constraints, Queries, and Views

1. SQL-99 :Schema Definition, Constraints, Queries, and Views Ms. Hatoon Al-Sagri CCIS – IS Department

2. Database Design Steps in building a database for an application: Real-world domain Conceptual model DBMS data model Create Schema (DDL) Load data (DML)

3. DDL Statements The main SQL data definition language statements are used to CREATE, DROP, and ALTER the descriptions of the tables (relations) of a database. CREATE SCHEMA DROP SCHEMA CREATE DOMAIN ALTER DOMAIN DROP DOMAIN CREATE TABLE ALTER TABLEDROP TABLE CREATE VIEW DROP VIEW CREATE INDEX DROP INDEX

4. Notations Notations to define SQL statements: • UPPER-CASE letters represents reserved words • Lower-case letters represents user-defined words • | indicates a choice among alternatives; (e.g. a | b | c) • { } indicates a required element • [ ] indicates an optional element • … indicates optional repetition of an item zero or more times • Underlined words represent default values

5. Identifiers • May contain A-Z, a-z, 0-9, _ • No longer than 128 characters • Start with letter • Cannot contain spaces

6. Attribute data types and domains in SQL

7. Attribute data types and domains in SQL

8. Attribute data types and domains in SQL

9. Schema concepts in SQL • Schema is a named collection of related database objects (tables, views, domains, ..) who belong to the same database application. • Specifies a new database schema by giving it a name. • An SQL schema is identified by a schema name and includes an authorization identifier to indicate the user or account who owns the schema.

10. Creating a DB • Syntax CREATE SCHEMA [Name | AUTHORIZATION Identifier] • Example CREATE SCHEMA COMPANY AUTHORIZATION Jsmith;

11. Domains in SQL • It is possible to specify the data type of each attribute directly or a domain can be declared and the domain name used with the attribute specification. • Syntax: CREATE DOMAIN domainName [AS] datatype [DEFAULT default option] [CHECK (search condition)]; Example: CREATE DOMAIN SSN_TYPE AS CHAR(9);

12. Domains in SQL • Example: CREATE DOMAIN DepartmentNo AS INT; . . DnoDepartmentNo,

13. Creating a Table Syntax CREATE TABLE tablename { ( {columnNamedataType [NOT NULL | NULL] [UNIQUE] [DEFAULT defaultOption,] [CHECK (search condition),] (,…) } [PRIMARY KEY (column (,…) ),] [UNIQUE (column (,…) ),] [FOREIGN KEY (FK column (,…) ) REFERENCES tablename [(CK column (,…))] [ON UPDATE ReferentialAction] [ON DELETE ReferentialAction], ] [CHECK (search condition)] ) }

14. Creating a Table • Specifies a new relation by giving it a name, and specifying each of its attributes and their data types. • A constraint NOT NULL may be specified on an attribute. • Example:CREATE TABLE DEPARTMENT (Dname VARCHAR(10) NOT NULL,Dnumber INTEGER NOT NULL,Mgr_ssn CHAR(9),Mgr_start_date CHAR(9) );

15. DROP Schema • The DROP command can be used to drop the whole schema or drop table and its definition. • Two drop behavior options: • CASCADE, drops all objects associated with schema • RESTRICT, schema must be empty . • Syntax DROP SCHEMA Name [RESTRICT | CASCADE] • Example: DROP SCHEMA COMPANY CASCADE;

16. DROP Table • The relation can no longer be used in queries, updates, or any other commands since its description no longer exists. • Syntax: DROP TABLE tablename [RESTRICT | CASCADE]; • Example: DROP TABLE DEPENDENT CASCADE; • Two drop behavior options: • CASCADE, drop operation drops all column from objects it is referenced by • RESTRICT, drop operation is rejected if the column is referenced by another database object.

17. DROP Domain • Syntax: DROP DOMAIN DomainName [RESTRICT | CASCADE]; • RESTRICT, domain must not be used in any existing table, view or assertion. • CASCADE, any column based on the domain is automatically changed to use the underlying data type, column constraint and default clause.

18. Specifying attribute constraints • Three types of attribute constraints: • Required data • Defaults value • Domain values constraints

19. 1. Requireddata • Null is distinct from blank or zero. • When NOT NULL is specified, the system rejects any attempt to insert a null in the column. • Syntax: columnNamedataType [NOT NULL | NULL] • Example: Dname VARCHAR(10) NOT NULL,

20. 2. Defaults value • It is possible to define a default value for an attribute by appending the DEFAULT value to an attribute definition. • Example: Dno INT NOT NULL DEFALT 1,

21. 3. Domain values constraints: CHECK Restrict attribute values using CHECK. Syntax: CHECK (search condition) Example: Attribute (Column-Based CHECK) Dnumber INT NOT NULL CHECK(Dnumber>0 AND Dnumber<12), Domain definition (Column-Based CHECK) CREATE DOMAIN D_numAS INTEGER CHECK(D_num>0 AND D_num<12);

22. Domain values constraints: CHECK Examples • sex CHAR NOT NULL CHECK (sex In (‘M’, ‘F’)) • salary DECIMAL NOT NULL CHECK (salary > 10000); • CREATE DOMAIN SexType AS CHAR DEFAULT ‘M’ CHECK (VALUE IN (‘M’, ‘F’));

23. Domain values constraints: CHECK • Other table constraints can be specified through additional CHECKclauses at the end of a CREATE TABLEstatement. • Calledtuple-based constraints because they apply for each tuple individually and are checked whenever a tuple is inserted or modified. • Example: CHECK (Dept_create_date <= Mgr_start_date)

24. Specifying key and referential integrity constraints PRIMARY KEY UNIQUE FOREIGN KEY

25. Specifying key and referential integrity constraints PRIMARY KEY clause specifies one or more attributes that make up the primary key of a relation. Syntax: PRIMARY KEY (attribute (,…)) SQL rejects any operations that attempt to create duplication in the PK column. PK forbids NULL value.

26. Specifying key and referential integrity constraints If the primary key has a single attribute, the clause can follow the attribute directly. Example: Dnumber INTEGER NOT NULL, . . . PRIMARY KEY (Dnumber), or Dnumber INTEGER PRIMARY KEY, Composite PK: PRIMARY KEY (cno, pno),

27. Specifying key and referential integrity constraints The UNIQUE clause specifies alternate (Secondary). UNIQUE permits NULL value. Every column that appears in a UNIQUE clause must also be declared in as NOT NULL. Syntax: UNIQUE(attribute (,…))

28. Specifying key and referential integrity constraints • Example:UNIQUE can appear after a column definition or separately: Dname VARCHAR(10) NOT NULL, . . UNIQUE (Dname), Or Dname VARCHAR(10) NOT NULLUNIQUE, • Example: cnoVARCHAR(5) NOT NULL, pnoVARCHAR(5) NOT NULL, UNIQUE(cno, pno),

29. Specifying key and referential integrity constraints Referential integrity is specified via the FOREIGN KEY clause. FOREIGN KEY clause is defined in the CREATE & ALTER TABLE statements. Syntax: FOREIGN KEY (attribute (,…)) REFERENCES table_name [(attribute (,…))] Example: FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE (Ssn) SQL rejects any insert, update or delete operation that attempts to create a foreign key value without a matching PK value key. The schema designer can specify an alternative action by attaching a referential triggered action on UPDATE or DELETE operation.

30. REFERENTIAL INTEGRITY OPTIONS Four options are supported when the user attempt to delete or update a PK, and there are matching FKs: • CASCADE: automatically delete/update the PK row & all matching (FKs) rows in child table • SET NULL: delete/update the PK row & set the FK values to NULL • SET DEFAULT: delete/update the PK row & set the FK values to default. Valid only if DEFAULT clause is specified. • NO ACTION:rejects the delete or update operation. Syntax FOREIGN KEY (FK column (,…) ) REFERENCES tablename [(CK column (,…))] [ON UPDATE [CASCADE | SET NULL| SET DEFAULT| NO ACTION] ] [ON DELETE [CASCADE | SET NULL| SET DEFAULT| NO ACTION] ]

31. REFERENTIAL INTEGRITY OPTIONS • We can specify RESTRICT, CASCADE, SET NULL or SET DEFAULT on referential integrity constraints (foreign keys). CREATE TABLE DEPARTMENT (Dname VARCHAR(10) NOT NULL,Dnumber INTEGER NOT NULL,Mgr_ssn CHAR(9),Mgr_start_date CHAR(9), PRIMARY KEY (Dnumber),UNIQUE (Dname), FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE (Ssn) ON DELETE SET NULL ON UPDATE CASCADE);

32. REFERENTIAL INTEGRITY OPTIONS CREATE TABLE EMPLOYEE(Ename VARCHAR(30) NOT NULL,Essn CHAR(9),Bdate DATE,Dno INTEGER DEFAULT 1,Superssn CHAR(9),PRIMARY KEY (Essn),FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber)ON DELETE SET DEFAULT ON UPDATE CASCADE,FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(Ssn) ON DELETE SET NULL ON UPDATE CASCADE);

33. “COMPANY” Relational Database Schema

34. SQL Create Table data definition statements for defining the COMPANY Schema

35. SQL Create Table data definition statements for defining the COMPANY Schema

36. Specifying Constraints in SQL: Giving Names to constraints: • The constraints can be given a constraint name, following the keyword CONSTRAINT. • The name must be unique. • In order to modify or delete an existing constraint, it is necessary that the constraint have a name. Sex CHAR CONSTRAINTSexTypeValid CHECK (sex IN (‘F’, ‘M’) ) Dept CHAR(4) NOT NULL CONSTRAINTDepNoInList CHECK( Dno IN (SELECT Dept FROM DEPARTMENT)) CONSTRAINTIDISKey PRIMARY KEY (SSN)

38. Changing a Table Definition • ALTER consists of six options to: • Add a column to table • Drop a column from a table • Add a table constraint • Drop a table constraint • Set a default for a column • Drop a default for a column

39. Changing a Table Definition Syntax: ALTER TABLE tablename [ADD [COLUMN] ColumnNamedataType [NOT NULL] [UNIQUE] [DEFAULT defaultOption] [CHECK (search condition)] ] [DROP [COLUMN] ColumnName [RESTRICT | CASCADE]] [ADD [CONSTRAINT [Constraint Name]] TableConstraint Definition] [DROP CONSTRAINT ConstraintName [RESTRICT | CASCADE]] [ALTER ColumnName SET DEFAULT DefaultOption] [ALTER ColumnName DROP DEFAULT]; RESTRICT, drop operation is rejected if the column is referenced by another database object (e.g. view). CASCADE, drop operation drops all column from objects it is referenced by.

40. Changing a Table Definition Example: Add an attribute for keeping track of jobs of staff in the company schema ALTER TABLE staff ADD job VARCHAR(12); Example: Remove the address attribute from the staff table ALTER TABLE staff DROP address CASCASE;

41. Changing a Table Definition Example: Change the staff table by removing the default of ‘Assistant’ for the position column and setting the default for the sex column to female. ALTER TABLE staff ALTER position DROP DEFAULT; ALTER TABLE staff ALTER sex SET DEFAULT ‘F’;

42. Changing a Table Definition Example: Change the PropertyForRent table by removing the constraint that the staff are not allowed more than 100 properties at a time. ALTER TABLE PropertyForRent DROP CONSTRAINT StaffNotHandlingTooMuch CASCADE;

43. Material for this lecture was obtained from: • Fundamentals of Database Systems, Elmasri and Navathe,  5th edition, Addison-Wesley, 2007. • Database Systems: A Practical Approach to Design, Implementation and Management, Thomas Connolly, Carolyn Begg, 3rd edition, 2002. • The slides of dr. Lilac Safadi, King Saud University. • The slides of T. Abeer Al-Nafjan, AsmaAlSaleh Imam University.