Database 2022-2 Inha University PDF

DATABASE 2022-2 Inha University Prof. Mehdi Pirahandeh Fundamentals of Database Systems Seventh Edition Chapter 6 Basic SQL Learning Objectives 6.1 SQL Data Definition and Data Types 6.2 Specifying Constraints in SQL 6.3 Basic Retrieval Queri...

DATABASE 2022-2 Inha University Prof. Mehdi Pirahandeh Fundamentals of Database Systems Seventh Edition Chapter 6 Basic SQL Learning Objectives 6.1 SQL Data Definition and Data Types 6.2 Specifying Constraints in SQL 6.3 Basic Retrieval Queries in SQL 6.4 INSERT, DELETE, and UPDATE Statements in SQL 6.5 Additional Features of SQL Inha University, Electronic Engineering Department Basic SQL SQL language – Considered one of the major reasons for the commercial success of relational databases SQL – The origin of SQL is relational predicate calculus called tuple calculus (see Chapter 8) which was proposed initially as the language SQUARE. Inha University, Electronic Engineering Department SQL Data Definition, Data Types, Standards Terminology: – Table, row, and column used for relational model terms relation, tuple, and attribute CREATE statement – Main SQL command for data definition Inha University, Electronic Engineering Department SQL Standards SQL has gone through many standards: starting with SQL-86 or SQL 1.A. SQL-92 is referred to as SQL-2. Later standards (from SQL-1999) are divided into core specification and specialized extensions SQL-2006 added XML features (Ch 13); apter SQL-3 is the current standard which started with SQL- 1999. Inha University, Electronic Engineering Department Schema and Catalog Concepts in SQL (1 of 2) We cover the basic standard SQL syntax SQL schema Schema elements include – Tables, constraints, views, domains, and other constructs Each statement in SQL ends with a semicolon Inha University, Electronic Engineering Department Schema and Catalog Concepts in SQL (2 of 2) CREATE SCHEMA statement – CREATE SCHEMA COMPANY AUTHORIZATION ‘Jsmith’; Catalog – Named collection of schemas in an SQL environment Inha University, Electronic Engineering Department The CREATE TABLE Command in SQL (1 of 3) Specifying a new relation – Provide name of table – Specify attributes, their types and initial constraints Can optionally specify schema: – CREATE TABLE COMPANY.EMPLOYEE... or – CREATE TABLE EMPLOYEE... Inha University, Electronic Engineering Department The CREATE TABLE Command in SQL (2 of 3) Base tables (base relations) – Relation and its tuples are actually created and stored as a file by the DBMS Virtual relations (views) – Created through the CREATE VIEW statement. Do not correspond to any physical file. Inha University, Electronic Engineering Department COMPANY Relational Database Schema Figure 5.7 Referential integrity constraints displayed on the COMPANY relational database schema. Inha University, Electronic Engineering Department Figure 5.6 One Possible Database State for the COMPANY Relational Database Schema (1 of 2) Inha University, Electronic Engineering Department Figure 5.6 One Possible Database State for the COMPANY Relational Database Schema (2 of 2) Inha University, Electronic Engineering Department Figure 6.1 SQL CREATE TABLE Data Definition Statements for Defining the Company Schema from Figure 5.7 (1 of 2) Inha University, Electronic Engineering Department Figure 6.1 SQL CREATE TABLE Data Definition Statements for Defining the Company Schema from Figure 5.7 (2 of 2) Inha University, Electronic Engineering Department Attribute Data Types and Domains in SQL (1 of 4) Basic data types – Numeric data types ▪ Integer numbers: INTEGER, INT, and SMALLINT ▪ Floating-point (real) numbers: FLOAT or REAL, and DOUBLE PRECISION – Character-string data types ▪ Fixed length: CHAR(n), CHARACTER(n) ▪ Varying length: VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n) Inha University, Electronic Engineering Department Attribute Data Types and Domains in SQL (2 of 4) – Bit-string data types ▪ Fixed length: BIT(n) ▪ Varying length: BIT VARYING(n) – Boolean data type ▪ Values of TRUE or FALSE or NULL – DATE data type ▪ Ten positions ▪ Components are YEAR, MONTH, and DAY in the form YYYY-MM-DD ▪ Multiple mapping functions available in RDBMSs to change date formats Inha University, Electronic Engineering Department Attribute Data Types and Domains in SQL (3 of 4) Additional data types – Timestamp data type Includes the DATE and TIME fields ▪ Plus a minimum of six positions for decimal fractions of seconds ▪ Optional WITH TIME ZONE qualifier – INTERVAL data type ▪ Specifies a relative value that can be used to increment or decrement an absolute value of a date, time, or timestamp – DATE, TIME, Timestamp, INTERVAL data types can be cast or converted to string formats for comparison. Inha University, Electronic Engineering Department Attribute Data Types and Domains in SQL (4 of 4) Domain – Name used with the attribute specification – Makes it easier to change the data type for a domain that is used by numerous attributes – Improves schema readability – Example: ▪ CREATE DOMAIN SSN_TYPE AS CHAR(9); TYPE – User Defined Types (UDTs) are supported for object- oriented applications. (See Ch 12) Uses the command: apter CREATE TYPE Inha University, Electronic Engineering Department Specifying Constraints in SQL Basic constraints: Relational Model has 3 basic constraint types that are supported in SQL: – Key constraint – Entity Integrity Constraint – Referential integrity constraints Inha University, Electronic Engineering Department Specifying Attribute Constraints 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); Inha University, Electronic Engineering Department Specifying Key and Referential Integrity Constraints (1 of 2) 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; Inha University, Electronic Engineering Department Specifying Key and Referential Integrity Constraints (2 of 2) 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 Inha University, Electronic Engineering Department Giving Names to Constraints Using the Keyword CONSTRAINT – Name a constraint – Useful for later altering Inha University, Electronic Engineering Department Figure 6.2 Default Attribute Values and Referential Integrity Triggered Action Specification Inha University, Electronic Engineering Department 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