Database Systems
66K - views

Database Systems

Basic SQL. Database Design -- Basic SQL. 1. Chapter 4 Outline. SQL Data Definition and Data Types. Specifying Constraints in SQL. Basic Retrieval Queries in SQL. INSERT. , . DELETE. , and . UPDATE. Statements in SQL.

Download Presentation

Database Systems




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

Database Design -- Basic SQL

38

Slide39

INSERT Statement

INSERT INTO Employee (fname, minit, lname, ssn, bdate, dno) values(‘Richard’, ‘S’, ‘Anderson’, ‘999333111’, ‘9/14/1980’, 4)

Database Design -- Basic SQL

39

Slide40

The DELETE Command

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

Database Design -- Basic SQL

44

Slide45

Slide46

Slide47

Slide48

Slide49

Slide50

Slide51

Slide52

Slide53