1 / 19

Chapter 8 Part 1

Chapter 8 Part 1. SQL-99 Schema Definition, Constraints, Queries, and Views. Chapter Outline. SQL Data Definition and Data Type Schema and catalog concepts in SQL The CREATE TABLE command in SQL Attribute data types and domains in SQL Specifying Constraints in SQL

alisa-obrien
Download Presentation

Chapter 8 Part 1

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 8 Part 1 SQL-99 SchemaDefinition, Constraints, Queries, and Views

  2. Chapter Outline • SQL Data Definition and Data Type • Schema and catalog concepts in SQL • The CREATE TABLE command in SQL • Attribute data types and domains in SQL • Specifying Constraints in SQL • Specifying attribute constraints and attribute defaults • Specifying key and referential integrity constraints • Giving names to constraints • Specifying constraints on tuples using CHECK • Schema Change Statements in SQL • The DROP command • The ALTER command • Basic Queries in SQL

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

  4. Introduction • SQL (Structured Query Language) • SQL was called SEQUEL (Structured English QUEry Language) was designed and implemented by IBM • SQL is now the stander language for commercial relational DBMSs. • SQL was standardized first by the ANSI and ISO. called SQL1 or SQL-86 • Much expanded standard called SQL2 or SQL-92 • The next standard that is well-recognized is SQL-99 or SQL3

  5. 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 DOMAIN ALTER DOMAIN DROP DOMAIN CREATE TABLE ALTER TABLE DROP TABLE CREATE VIEW DROP VIEW CREATE INDEX DROP INDEX

  6. SQL Data Definition and Data Type: The CREATE TABLE command in SQL • Specifies a new base relation by giving it a name, and specifying each of its attributes and their data types (INTEGER, FLOAT, DECIMAL(i,j), CHAR(n), VARCHAR(n)) • A constraint NOT NULL may be specified on an attributeCREATE TABLE DEPARTMENT (Dname VARCHAR(10) NOT NULL,Dnumber INTEGER NOT NULL,Mgr_ssn CHAR(9),Mgr_start_date CHAR(9) );

  7. CREATE TABLE (contd.) • In SQL2, can use the CREATE TABLE command for specifying the primary key attributes, secondary keys, and referential integrity constraints (foreign keys). • Key attributes can be specified via the PRIMARY KEY and UNIQUE phrases 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));

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

  9. SQL Data Definition and Data Type: Attribute data types and domains in SQL

  10. SQL Data Definition and Data Type: Attribute data types and domains in SQL(contd.)

  11. SQL Data Definition and Data Type: Attribute data types and domains in SQL(contd.)

  12. “COMPANY” Relational Database Schema

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

  14. SQL Create Table data definition statements for defining the COMPANY Schema (contd.)

  15. Specifying Constraints in SQL: Specifying attribute constraints and attribute defaults Three types of attribute constraints: • Required data: a constraint NOT NULL may be specified if NULL is not permitted for a particular attribute. Dname VARCHAR(10) NOT NULL, • Defaults value: it is possible to define a default value for an attribute by appending the DEFAULT <value> to an attribute definition. Dno INT NOT NULL DEFALT 1, • Domain values constraints: restrict attribute values using CHECK followingan attribute or domain definition. Dnumber INT NOT NULL CHECK(Dnumber>0 AND Dnumber<12),

  16. Specifying Constraints in SQL: Specifying key and referential integrity constraints • PRIMARY KEY clause specifies one or more attributes that make up the primary key of a relation. If the primary key has a single attribute, the clause can follow the attribute directly. 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 );

  17. Specifying Constraints in SQL: Specifying key and referential integrity constraints Referential integrity is specified via the FOREIGN KEY clause. 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. • 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 operation

  18. REFERENTIAL INTEGRITY OPTIONS (contd.) • 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);

  19. REFERENTIAL INTEGRITY OPTIONS (contd.) 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);

More Related