02010_ch07

02010_ch07 - Database Systems: Design, Implementation, and...

Info iconThis preview shows pages 1–14. Sign up to view the full content.

View Full Document Right Arrow Icon
Database Systems: Design, Implementation, and Management Eighth Edition Chapter 7 Introduction to Structured Query Language (SQL)
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
th 2 Objectives In this chapter, you will learn: The basic commands and functions of SQL How to use SQL for data administration (to create tables, indexes, and views) How to use SQL for data manipulation (to add, modify, delete, and retrieve data) How to use SQL to query a database for useful information
Background image of page 2
th 3 Introduction to SQL SQL functions fit into two broad categories: Data definition language Data manipulation language Basic command set has vocabulary of less than 100 words American National Standards Institute (ANSI) prescribes a standard SQL Several SQL dialects exist
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
th 4 Data Definition Commands The database model In this chapter, a simple database with these tables is used to illustrate commands: CUSTOMER INVOICE LINE PRODUCT VENDOR Focus on PRODUCT and VENDOR tables
Background image of page 4
th 5
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
th 6 Creating the Database Two tasks must be completed: Create database structure Create tables that will hold end-user data First task: RDBMS creates physical files that will hold database Differs substantially from one RDBMS to another
Background image of page 6
th 7 The Database Schema Authentication DBMS verifies that only registered users are able to access database Log on to RDBMS using user ID and password created by database administrator Schema Group of database objects that are related to each other
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
th 8 Data Types Data type selection is usually dictated by nature of data and by intended use Supported data types: Number(L,D), Integer, Smallint, Decimal(L,D) Char(L), Varchar(L), Varchar2(L) Date, Time, Timestamp Real, Double, Float Interval day to hour Many other types
Background image of page 8
th 9 Creating Table Structures Use one line per column (attribute) definition Use spaces to line up attribute characteristics and constraints Table and attribute names are capitalized NOT NULL specification UNIQUE specification
Background image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
th 10 Creating Table Structures (continued) Primary key attributes contain both a NOT NULL and a UNIQUE specification RDBMS will automatically enforce referential integrity for foreign keys Command sequence ends with semicolon
Background image of page 10
th 11 SQL Constraints NOT NULL constraint Ensures that column does not accept nulls UNIQUE constraint Ensures that all values in column are unique DEFAULT constraint Assigns value to attribute when a new row is added to table CHECK constraint Validates data when attribute value is entered
Background image of page 11

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
th 12 SQL Indexes When primary key is declared, DBMS automatically creates unique index Often need additional indexes Using CREATE INDEX command, SQL indexes can be created on basis of any selected attribute Composite index Index based on two or more attributes Often used to prevent data duplication
Background image of page 12
th 13 Data Manipulation Commands INSERT SELECT COMMIT UPDATE ROLLBACK DELETE
Background image of page 13

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 14
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 51

02010_ch07 - Database Systems: Design, Implementation, and...

This preview shows document pages 1 - 14. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online