chapter07 - Chapter 7 SQL The de facto standard language...

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

View Full Document Right Arrow Icon
Chapter 7 SQL The de facto standard language for creating and querying relational databases.
Background image of page 1

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

View Full DocumentRight Arrow Icon
Benefits of a Standardized Relational Language Reduced training costs. Productivity. Application portability. Application longevity. Reduced dependence on a single vendor. Cross-system communication.
Background image of page 2
SQL Environment Catalog a set of schemas that constitute the description of a database Schema The structure that contains descriptions of objects created by a user (base tables, views, constraints) Data Definition Language (DDL): Commands that define a database, including creating, altering, and dropping tables and establishing constraints Data Manipulation Language (DML) Commands that maintain and query a database Data Control Language (DCL) Commands that control a database, including administering privileges and committing data
Background image of page 3

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

View Full DocumentRight Arrow Icon
Figure 7-1: A simplified schematic of a typical SQL environment, as described by the SQL-92 standard
Background image of page 4
SQL Data types (from Oracle8) String types CHAR(n) – fixed-length character data, n characters long Maximum length = 2000 bytes VARCHAR2(n) – variable length character data, maximum 4000 bytes LONG – variable-length character data, up to 4GB. Maximum 1 per table Numeric types NUMBER(p,q) – general purpose numeric data type INTEGER(p) – signed integer, p digits wide FLOAT(p) – floating point in scientific notation with p binary digits precision Date/time type DATE – fixed-length date/time in dd-mm-yy form
Background image of page 5

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

View Full DocumentRight Arrow Icon
SQL Data types (from SQL Server 2008 R2) String types CHAR(n) – fixed-length character data, n must be a value from 1 to 8000. VARCHAR(n) – variable length character data, n must be a value from 1 to 8000. Numeric types INT – 4 bytes signed integer, from -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647). FLOAT – floating point, range from -1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308. Date/time type DATE Range: from January 1, 1 A.D. through December 31, 9999 A.D. DATETIME Date Range: from January 1, 1753, through December 31, 9999. Time range: from 00:00:00 through 23:59:59.997
Background image of page 6
Figure 7-4: DDL, DML, DCL, and the database development process
Background image of page 7

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

View Full DocumentRight Arrow Icon
SQL Database Definition Data Definition Language (DDL) Major CREATE statements: CREATE SCHEMA – defines a portion of the database owned by a particular user CREATE TABLE – defines a table and its columns CREATE VIEW – defines a logical table from one or more views
Background image of page 8
Table Creation Figure 7-5: General syntax for CREATE TABLE Steps in table creation: 1. Identify data types for attributes 2. Identify columns that can and cannot be null 3. Identify columns that must be unique (candidate keys) 4. Identify primary key- foreign key mates 5. Determine default values 6. Identify constraints on columns (domain specifications) 7. Create the table and associated indexes
Background image of page 9

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

View Full DocumentRight Arrow Icon
The following slides create tables for this enterprise data model
Background image of page 10
Figure 7-6 SQL database definition commands for Pine Valley Furniture Overall table definitions
Background image of page 11

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

View Full DocumentRight Arrow Icon
Defining attributes and their data types
Background image of page 12
Non-nullable specification Identifying primary key Primary keys can never have NULL values
Background image of page 13

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

View Full DocumentRight Arrow Icon
Non-nullable specifications Primary key Some primary keys are composite– composed of multiple attributes
Background image of page 14
Default value Domain constraint Controlling the values in attributes
Background image of page 15

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

View Full DocumentRight Arrow Icon
Primary key of parent table Identifying foreign keys and establishing relationships Foreign key of dependent table
Background image of page 16
Creating Referential Integrity Control. Restricted Update:
Background image of page 17

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

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

This note was uploaded on 10/07/2011 for the course BSTT 400 taught by Professor Sallyfreels during the Fall '11 term at Ill. Chicago.

Page1 / 50

chapter07 - Chapter 7 SQL The de facto standard language...

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

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