Download Presentation - The PPT/PDF document "Database Systems" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Presentation on theme: "Database Systems"— Presentation transcript:
Slide1
Database Systems
Basic SQL
Database Design -- Basic SQL
1
Slide2
Chapter 4 Outline
SQL Data Definition and Data TypesSpecifying Constraints in SQLBasic Retrieval Queries in SQLINSERT, DELETE, and UPDATE Statements in SQLAdditional Features of SQL
Database Design -- Basic SQL
2
Slide3
Basic SQL
SQL language Considered one of the major reasons for the commercial success of relational databasesSQL Structured Query LanguageStatements for data definitions, queries, and updates (both DDL and DML)Core specificationPlus specialized extensions
Database Design -- Basic SQL
3
Slide4
SQL Terminology
Terminology:Table, row, and column used for relational model terms relation, tuple, and attributeCREATE statementMain SQL command for data definitionMuch of what you’ll see in Data Definition Language is normally done with user-friendly tools like SQL Server Management Studio, etc.
Database Design -- Basic SQL
4
Slide5
Schema and Catalog Concepts in SQL
SQL schema (In most systems, a Database)Identified by a schema nameIncludes an authorization identifier and descriptors for each element Schema elements include Tables, constraints, views, domains, and other constructsEach statement in SQL ends with a semicolon (not really needed)
Database Design -- Basic SQL
5
Slide6
Schema and Catalog Concepts in SQL
CREATE SCHEMA statementCREATE SCHEMA COMPANY AUTHORIZATION ‘Jsmith’;CatalogNamed collection of schemas in an SQL environmentSQL environmentInstallation of an SQL-compliant RDBMS on a computer system
Database Design -- Basic SQL
6
Slide7
The CREATE TABLE Command in SQL
Specify a new relation Provide nameSpecify attributes and initial constraintsCan optionally specify schema:CREATE TABLE COMPANY.EMPLOYEE ... orCREATE TABLE EMPLOYEE ...
Database Design -- Basic SQL
7
Slide8
The CREATE TABLE Command in SQL
Base tables (base relations)Relation and its tuples are actually created and stored as a file by the DBMSVirtual relationsCreated through the CREATE VIEW statement
Database Design -- Basic SQL
8
Slide9
Database Design -- Basic SQL
9
Slide10
Database Design -- Basic SQL
10
Slide11
CREATE TABLE Command
Some foreign keys may cause errors Specified either via: Circular references Or because they refer to a table that has not yet been createdForeign Key: a field that is not a key in the table in which it occurs but which is a priamary key in another table in the same database. Used to allow tables to refer to each other.
Database Design -- Basic SQL
11
Slide12
Attribute Data Types and Domains in SQL
Different dialects of SQL (Microsoft, Oracle, IBM DB2) may have different types)Basic data typesNumeric data types Integer numbers: INTEGER, INT, and SMALLINTFloating-point (real) numbers: FLOAT or REAL, and DOUBLE PRECISIONCharacter-string data types Fixed length: CHAR(n), CHARACTER(n)Varying length: VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n)
Database Design -- Basic SQL
12
Slide13
Attribute Data Types and Domains in SQL
Bit-string data types Fixed length: BIT(n)Varying length: BIT VARYING(n)Boolean data type Values of TRUE or FALSE or NULLDATE data type (Use DateTime instead)Ten positionsComponents are YEAR, MONTH, and DAY in the form YYYY-MM-DD
Database Design -- Basic SQL
13
Slide14
Attribute Data Types and Domains in SQL
Additional data typesTimestamp data type (TIMESTAMP)Includes the DATE and TIME fieldsPlus a minimum of six positions for decimal fractions of secondsOptional WITH TIME ZONE qualifierINTERVAL data typeSpecifies a relative value that can be used to increment or decrement an absolute value of a date, time, or timestamp
Database Design -- Basic SQL
14
Slide15
Attribute Data Types and Domains in SQL
Domain Name used with the attribute specificationMakes it easier to change the data type for a domain that is used by numerous attributes Improves schema readabilityExample:CREATE DOMAIN SSN_TYPE AS CHAR(9);
Database Design -- Basic SQL
15
Slide16
Specifying Constraints in SQL
Basic constraints:Key and referential integrity constraints (Referential Integrity means that the foreign key in one table refers to something that actually exists as a primary key in another table)Restrictions on attribute domains and NULLsConstraints on individual tuples within a relation
Database Design -- Basic SQL
16
Slide17
Specifying Attribute Constraints and Attribute Defaults
NOT NULL NULL is not permitted for a particular attributeDefault valueDEFAULT <value> CHECK clauseDnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21);
Database Design -- Basic SQL
17
Slide18
Database Design -- Basic SQL
18
Slide19
Specifying Key and Referential Integrity Constraints
PRIMARY KEY clause Specifies one or more attributes that make up the primary key of a relationDnumber INT PRIMARY KEY;UNIQUE clause Specifies alternate (secondary) keysDname VARCHAR(15) UNIQUE;
Database Design -- Basic SQL
19
Slide20
Specifying Key and Referential Integrity Constraints
FOREIGN KEY clauseDefault operation: reject update on violationAttach referential triggered action clauseOptions include SET NULL, CASCADE, and SET DEFAULTAction taken by the DBMS for SET NULL or SET DEFAULT is the same for both ON DELETE and ON UPDATECASCADE option suitable for “relationship” relations
Database Design -- Basic SQL
20
Slide21
Giving Names to Constraints
Keyword CONSTRAINTName a constraintUseful for later altering
Database Design -- Basic SQL
21
Slide22
Specifying Constraints on Tuples Using CHECK
CHECK clauses at the end of a CREATE TABLE statementApply to each tuple individuallyCHECK (Dept_create_date <= Mgr_start_date);
Database Design -- Basic SQL
22
Slide23
Basic Retrieval Queries in SQL
SELECT statementOne basic statement for retrieving information from a databaseSQL allows a table to have two or more tuples that are identical in all their attribute valuesUnlike relational modelMultiset or bag behavior (sets don’t allow identical members)
Database Design -- Basic SQL
23
Slide24
The Structure of Basic SQL Queries
Database Design -- Basic SQL
24
Basic form of the SELECT statement:
Slide25
The Structure of Basic SQL Queries
Logical comparison operators=, <, <=, >, >=, and <>Projection attributesAttributes whose values are to be retrievedSelection conditionBoolean condition that must be true for any retrieved tuple
Database Design -- Basic SQL
25
Slide26
Some Queries
Database Design -- Basic SQL
26
Slide27
Some Queries
Database Design -- Basic SQL
27
Slide28
Ambiguous Attribute Names
Same name can be used for two (or more) attributes As long as the attributes are in different relationsMust qualify the attribute name with the relation name to prevent ambiguity
Database Design -- Basic SQL
28
Slide29
Aliasing, Renaming, and Tuple Variables
Aliases or tuple variablesDeclare alternative relation names E and SEMPLOYEE AS E(Fn, Mi, Ln, Ssn, Bd, Addr, Sex, Sal, Sssn, Dno)
Database Design -- Basic SQL
29
Slide30
Unspecified WHERE Clauseand Use of the Asterisk
Missing WHERE clause Indicates no condition on tuple selectionCROSS PRODUCTAll possible tuple combinations
Database Design -- Basic SQL
30
Slide31
Unspecified WHERE Clauseand Use of the Asterisk
Specify an asterisk (*)Retrieve all the attribute values of the selected tuples
Database Design -- Basic SQL
31
Slide32
Tables as Sets in SQL
SQL does not automatically eliminate duplicate tuples in query results Use the keyword DISTINCT in the SELECT clauseOnly distinct tuples should remain in the result
Database Design -- Basic SQL
32
Slide33
Tables as Sets in SQL
Set operationsUNION, EXCEPT (difference), INTERSECTCorresponding multiset operations: UNION ALL, EXCEPT ALL, INTERSECT ALL)
Database Design -- Basic SQL
33
Slide34
Substring Pattern Matching and Arithmetic Operators
LIKE comparison operatorUsed for string pattern matching% replaces an arbitrary number of zero or more charactersunderscore (_) replaces a single characterStandard arithmetic operators:Addition (+), subtraction (–), multiplication (*), and division (/) BETWEEN comparison operator
Database Design -- Basic SQL
34
Slide35
Ordering of Query Results
SQL results are inherently not ordered. To change this, use ORDER BY clauseKeyword DESC to see result in a descending order of valuesKeyword ASC to specify ascending order explicitlyORDER BY D.Dname DESC, E.Lname ASC, E.Fname ASC
Database Design -- Basic SQL
35
Slide36
Exists vs. In
IN does a direct match between the column specified before the IN keyword and the values returned by the subquery. When using IN there can only be a single column specified in the select clause of the subqueryExists doesn’t check for a match, it doesn’t care in the slightest what values have been returned from the expression, it just checks for whether a row exists or not. Because of that, if there’s no predicate in the WHERE clause of the subquery that compares rows in the subquery with rows in the outer query, EXISTS will either return true for all the rows in the outer query or it will return false for all the rows in the outer queryExists is better for when comparisons are needed on two or more columns. This cannot be done easily with an IN
Database Design -- Basic SQL
36
Slide37
Modifying the Database Data
Three commands used to modify the database: INSERT, DELETE, and UPDATE
Database Design -- Basic SQL
37
Slide38
The INSERT Command
Specify the relation name and a list of values for the tuple
Removes tuples from a relationIncludes a WHERE clause to select the tuples to be deleted
Database Design -- Basic SQL
40
Slide41
The UPDATE Command
Modify attribute values of one or more selected tuplesAdditional SET clause in the UPDATE command Specifies attributes to be modified and new values
Database Design -- Basic SQL
41
Slide42
Additional Features of SQL
Techniques for specifying complex retrieval queriesWriting programs in various programming languages that include SQL statementsSet of commands for specifying physical database design parameters, file structures for relations, and access pathsTransaction control commands
Database Design -- Basic SQL
42
Slide43
Additional Features of SQL
Specifying the granting and revoking of privileges to usersConstructs for creating triggersEnhanced relational systems known as object-relationalNew technologies such as XML and OLAP
Database Design -- Basic SQL
43
Slide44
Summary
SQL Comprehensive language Data definition, queries, updates, constraint specification, and view definitionCovered in Chapter 4:Data definition commands for creating tables Commands for constraint specificationSimple retrieval queriesDatabase update commands
