Description
Please submit a word document.
Assignment Description
After reading Henry Book database PP slides answer the following questions:
1) Write Seven (7), SQL statements using this database and explain each query. (84 points)
For example, if your query is
SELECT Publisher.PublisherName
FROM Publisher
WHERE (((Publisher.City)<>“New York”));
Your explanation should be: This query will list the publisher’s name from Publisher table where Publisher City is Not New York
2) Describe what are the tables in this database and what is/are the relationships between these tables. (16 points)
Unformatted Attachment Preview
Chapter 8
Advanced SQL
Learning Objectives
• After completing this chapter, you will be able to:
• Use SQL to create a table manually
• Use SQL to create a copy of a table using a subquery
• Manipulate the structure of existing tables to add, modify, and remove columns and
constraints
• Use SQL to do data manipulation (insert, update, and delete rows of data)
• Use SQL to create database views, including updatable views
• Use Procedural Language SQL (PL/SQL) to create triggers, stored procedures, and
PL/SQL functions
• Create embedded SQL
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
2
Data Definition Commands (1 of 3)
• Starting database model
• Refer to Figure 8.1
• Creating the database
• Before a new RDBMS can be used, the database structure and the tables that will hold
the end-user data must be created
• The database schema
• Logical group of database objects—such as tables and indexes—that are related to
each other
• Data types
• Character, numeric, and date
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
3
Data Definition Commands (2 of 3)
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
4
Data Definition Commands (3 of 3)
TABLE 8.1: Some
Common SQL Data Types
Data Type
Format
Comments
Numeric
NUMBER(L,D)
or
NUMERIC(L,D)
The declaration NUMBER(7,2) or NUMERIC(7,2) indicates that numbers will be stored with
two decimal places and may be up to seven digits long, including the sign and the decimal
place (for example, 12.32 or −134.99).
INTEGER
May be abbreviated as INT. Integers are (whole) counting numbers, so they cannot be used if
you want to store numbers that require decimal places.
SMALLINT
Like INTEGER but limited to integer values up to six digits. If your integer values are relatively
small, use SMALLINT instead of INT.
DECIMAL(L,D)
Like the NUMBER specification, but the storage length is a minimum specification. That is,
greater lengths are acceptable, but smaller ones are not. DECIMAL(9,2), DECIMAL(9), and
DECIMAL are all acceptable.
CHAR(L)
Fixed-length character data for up to 255 characters. If you store strings that are not as long
as the CHAR parameter value, the remaining spaces are left unused. Therefore, if you specify
CHAR(25), strings such as Smith and Katzenjammer are each stored as 25 characters.
However, a U.S. area code is always three digits long, so CHAR(3) would be appropriate if you
wanted to store such codes.
VARCHAR(L) or
VARCHAR2(L)
Variable-length character data. T he designation VARCHAR2(25) or VARCHAR(25) will let you
store characters up to 25 characters long. However, unlike CHAR, VARCHAR will not leave
unused spaces. Oracle automatically converts VARCHAR to VARCHAR2.
DATE
Stores dates in the Julian date format.
Character
Date
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
5
Creating Table Structures (1 of 2)
• CREATE TABLE command
CREATE TABLE tablename (
column1
data type
column2
data type
PRIMARY KEY
(column1
FOREIGN KEY
(column1
CONSTRAINT
constraint ] );
[constraint] [,
[constraint] ] [,
[, column2]) ] [,
[, column2]) REFERENCES tablename] [,
• SQL constraints
• FOREIGN KEY
• NOT NULL
• UNIQUE
• DEFAULT
• CHECK
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
6
Creating Table Structures (2 of 2)
• Create a table with a SELECT statement
• Rapidly creates a new table based on selected columns and rows of an existing table
using a subquery
• Automatically copies all of the data rows returned
• SQL indexes
• CREATE INDEX improves the efficiency of searches and avoids duplicate column values
• DROP Index deletes an index
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
7
Altering Table Structures (1 of 3)
• All changes in the table structure are made by using the ALTER TABLE command
followed by a keyword that produces the specific change you want to make
• ADD, MODIFY, and DROP
• Changing a column’s data type
• ALTER
• Changing a column’s data characteristics
• If the column to be changed already contains data, you can make changes in the
column’s characteristics if those changes do not alter the data type
• Adding a column
• You can alter an existing table by adding one or more columns
• Be careful not to include the NOT NULL clause for the new column
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
8
Altering Table Structures (2 of 3)
• Adding primary key, foreign key, and check constraints
• Primary key syntax:
ALTER TABLE
ADD
• Foreign key syntax:
ALTER TABLE
ADD
• Check constraint syntax:
ALTER TABLE
ADD
PART
PRIMARY KEY (PART_CODE);
PART
FOREIGN KEY (V_CODE) REFERENCES
VENDOR;
PART
CHECK (PART_PRICE >= 0);
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
9
Altering Table Structures (3 of 3)
• Dropping a column
• Syntax:
ALTER TABLE VENDOR
DROP COLUMN V_ORDER;
• Deleting a table from the database
• Syntax:
DROP TABLE PART;
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
10
Data Manipulation Commands (1 of 2)
• Adding table rows
• INSERT command syntax:
INSERT INTO tablename VALUES (value1, value2, …, valuen)
• Inserting rows with null attributes: use NULL entry
• Inserting rows with optional attributes: indicate attributes that have required values
• Inserting table rows with a SELECT subquery
• Add multiple rows to a table, using another table as the source, at the same time
• SELECT syntax:
INSERT INTO
target_tablename[(target_columnlist)]
SELECT
source_columnlist
FROM
source_tablename;
• Saving table changes
• COMMIT command syntax:
COMMIT [WORK]
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
11
Data Manipulation Commands (2 of 2)
• Updating table rows
• UPDATE command is used to modify data in a table
• UPDATE syntax:
UPDATE
tablename
SET
columnname = expression [, columnname =
expression]
[WHERE
conditionlist ];
• Deleting table rows
• DELETE statement syntax:
DELETE FROM
[WHERE
tablename
conditionlist ];
• Restoring table contents
• ROLLBACK command is used restore the database to its previous condition
ROLLBACK;
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
12
Virtual Tables: Creating a View
• View: virtual table based on a SELECT query
• Base tables: tables on which the view is based
• CREATE VIEW statement: data definition command that stores the subquery
specification in the data dictionary
• CREATE VIEW command syntax:
CREATE VIEW viewname AS SELECT query
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
13
Updatable Views
• Used to update attributes
• Batch update routine: pools multiple transactions into a single batch to update a
master table field in a single operation
• Updatable view restrictions
• GROUP BY expressions or aggregate functions cannot be used
• Set operators cannot be used
• Most restrictions are based on the use of JOINs or group operators in views
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
14
Sequences (1 of 2)
• Many similarities in the use of sequences across these DBMSs
• Independent object in the database
• Have a name and can be used anywhere a value expected
• Not tied to a table or column
• Generate a numeric value that can be assigned to any column in any table
• Table attribute with an assigned value can be edited and modified
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
15
Sequences (2 of 2)
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
16
Procedural SQL (1 of 3)
• Performs a conditional or looping operation by isolating critical code and
making all application programs call the shared code
• Better maintenance and logic control
• Persistent stored module (PSM): block of code
• Contains standard SQL statements and procedural extensions that is stored and
executed at the DBMS server
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
17
Procedural SQL (2 of 3)
• Procedural Language SQL (PL/SQL)
• Use and storage of procedural code and SQL statements within the database
• Merging of SQL and traditional programming constructs
• Procedural code is executed as a unit by DBMS when invoked by end user
• Anonymous PL/SQL blocks
• Triggers
• Stored procedures
• PL/SQL functions
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
18
Procedural SQL (3 of 3)
Table 8.4
PL/SQL BASIC DATA TYPES
DATA TYPE
DESCRIPTION
CHAR
Character values of a fixed length; for example:
W_ZIP CHAR(5)
VARCHAR2
Variable-length character values; for example:
W_FNAME VARCHAR2(15)
NUMBER
Numeric values; for example:
W_PRICE NUMBER(6,2)
DATE
Date values; for example:
W_EMP_DOB DATE
%TYPE
Inherits the data type from a variable that you declared previously or
from an attribute of a database table; for example:
W_PRICE PRODUCT.P_PRICE%TYPE
Assigns W_PRICE the same data type as the P_PRICE column in the
PRODUCT table
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
19
Triggers (1 of 2)
• Procedural SQL code automatically invoked by RDBMS when given data
manipulation event occurs
• Parts of a trigger definition
• Triggering timing: indicates when trigger’s PL/SQL code executes
• Triggering event: statement that causes the trigger to execute
– Triggering level: statement- and row-level
– Triggering action: PL/SQL code enclosed between the BEGIN and END keywords
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
20
Triggers (2 of 2)
• DROP TRIGGER trigger_name command
• Deletes a trigger without deleting the table
• Trigger action based on conditional DML predicates
• Actions depend on the type of DML statement that fires the trigger
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
21
Stored Procedures
• Named collection of procedural and SQL statements
• Stored in the database
• Can be used to encapsulate and represent business transactions
• Advantages
• Reduce network traffic and increase performance
• Decrease code duplication by means of code isolation and code sharing
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
22
PL/SQL Processing with Cursors (1 of 3)
• Cursor: special construct used to hold data rows returned by a SQL query
• Implicit cursor: automatically created when SQL statement returns only one value
• Explicit cursor: holds the output of a SQL statement that may return two or more rows
• Syntax:
CURSOR cursor_name IS select-query;
• Cursor-style processing involves retrieving data from the cursor one row at a
time
• Current row is copied to PL/SQL variables
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
23
PL/SQL Processing with Cursors (2 of 3)
Table 8.5
Cursor Processing Commands
Cursor Command
Explanation
OPEN
Opening the cursor executes the SQL command and populates the cursor with data,
opening the cursor for processing. The cursor declaration command only reserves a
named memory area for the cursor; it does not populate the cursor with the data.
Before you can use a cursor, you need to open it. For example:
OPEN cursor_name
FETCH
Once the cursor is opened, you can use the FETCH command to retrieve data from
the cursor and copy it to the PL/SQL variables for processing. T he syntax is:
FETCH cursor_name INTO variable1 [, variable2, …]
The PL/SQL variables used to hold the data must be declared in the DECLARE
section and must have data types compatible with the columns retrieved by the SQL
command. If the cursor’s SQL statement returns five columns, there must be five
PL/SQL variables to receive the data from the cursor.
This type of processing resembles the one-record-at-a-time processing used in
previous database models. The first time you fetch a row from the cursor, the first
row of data from the cursor is copied to the PL/SQL variables; the second time you
fetch a row from the cursor, the second row of data is placed in the PL/SQL variables;
and so on.
CLOSE
The CLOSE command closes the cursor for processing.
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
24
PL/SQL Processing with Cursors (3 of 3)
Table 8.6
Cursor Attributes
Attribute
Description
%ROWCOUNT
Returns the number of rows fetched so far. If the cursor is not OPEN, it
returns an error. If no FETCH has been done but the cursor is OPEN, it
returns 0.
%FOUND
Returns TRUE if the last FETCH returned a row, and FALSE if not. If the cursor
is not OPEN, it returns an error. If no FETCH has been done, it contains NULL.
%NOT FOUND
Returns TRUE if the last FETCH did not return any row, and FALSE if it did. If
the cursor is not OPEN, it returns an error. If no FETCH has been done, it
contains NULL.
%ISOPEN
Returns TRUE if the cursor is open (ready for processing) or FALSE if the
cursor is closed. Remember, before you can use a cursor, you must open it.
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
25
PL/SQL Stored Functions
• Stored function: named group of procedural and SQL statements that returns a
value
• Indicated by a RETURN statement in its program code
• Can be invoked only from within stored procedures or triggers
• Cannot be invoked from SQL statements unless the function follows some very
specific compliance rules
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
26
Embedded SQL (1 of 4)
• SQL statements contained within an application programming language
• Host language: any language that contains embedded SQL statements
• Differences between SQL and procedural languages
• Run-time mismatch
– SQL is executed one instruction at a time
– Host language runs at client side in its own memory space
• Processing mismatch
– Conventional programming languages process one data element at a time
– Newer programming environments manipulate data sets in a cohesive manner
• Data type mismatch
– Data types provided by SQL might not match data types used in different host languages
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
27
Embedded SQL (2 of 4)
• Embedded SQL framework defines:
• Standard syntax to identify embedded SQL code within the host language
• Standard syntax to identify host variables
• Communication area used to exchange status and error information between SQL and
host language
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
28
Embedded SQL (3 of 4)
Table 8.7
SQL Status and Error
Reporting Variables
Variable Name
Value
SQLCODE
Explanation
Old-style error reporting supported for backward compatibility only;
returns an integer value (positive or negative)
0
Successful completion of command
100
No data; the SQL statement did not return any rows and did not
select, update, or delete any rows
-999
Any negative value indicates that an error occurred
SQLSTATE
Added by SQL-92 standard to provide predefined error codes;
defined as a character string (5 characters long)
00000
Successful completion of command
Multiple values in the format XXYYY where:
XX-> represents the class code
YYY-> represents the subclass code
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
29
Embedded SQL (4 of 4)
• Static SQL: programmer uses predefined SQL statements and parameters
• SQL statements will not change while application is running
• Dynamic SQL: SQL statement is generated at run time
• Attribute list and condition are not known until end user specifies them
• Slower than static SQL
• Requires more computer resources
• Inconsistent levels of support and incompatibilities among DBMS vendors
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
30
Summary (1 of 2)
• The ANSI standard data types are supported by all RDBMS vendors in different
ways
• The basic data definition commands allow you to create tables and indexes
• Data manipulation commands allow you to add, modify, and delete rows from
tables
• Views can be created to expose subsets of data to end users primarily for
security and privacy reasons
• In Oracle and SQL Server, sequences may be used to generate values to be
assigned to a record
• Procedural Language SQL (PL/SQL) can be used to create triggers, stored
procedures, and PL/SQL functions
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
31
Summary (2 of 2)
• A stored procedure is a named collection of SQL statements
• When SQL statements are designed to return more than one value inside the
PL/SQL code, a cursor is needed
• Embedded SQL refers to the use of SQL statements within an application
programming language such as Visual Basic .NET, C#, COBOL, or Java
© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website
for classroom use.
32
Following are 7 Sample queries for Henry Book Database
You can use these queries as guide to do your assignment. However,
you cannot use the same queries. Your queries must be different
1) Sample Query 1
SELECT Publisher.PublisherName
FROM Publisher
WHERE (((Publisher.City)”New York”));
2) Sample Query 2
SELECT Copy.BookCode, Copy.Price
FROM Copy
WHERE (((Copy.Price)>7));
3) Sample Query 3
SELECT Book.Title, Book.Type
FROM Book
WHERE (((Book.Type)=”MYS”));
4) Sample Query 4
SELECT Count(Copy.CopyNum) AS CountOfCopyNum
FROM Copy
WHERE (((Copy.Price)>20 And (Copy.Price)20 And (Copy.Price)=’10.20’ OR
Bookcode =’3350’;
FIGURE 1-18: Sample data that relates books to authors and books to branches
for Henry Books (continued)
9
Introduction to Henry Books Database
Case (continued)
FIGURE 1-19: E-R diagram for the Henry Books database
10
Summary
• Problems with nondatabase approaches to data
management: redundancy, difficulties accessing
related data, limited security features, limited data
sharing features, and potential size limitations
• Entity: person, place, object, event, or idea for
which you want to store and process data
• Attribute, field, or column: characteristic or property
of an entity
• Relationship: an association between entities
11
Summary (continued)
• One-to-many relationship: each occurrence of first
entity is related to many occurrences of the second
entity and each occurrence of the second entity is
related to only one occurrence of the first entity
• Database: structure that can store information
about multiple types of entities, attributes of
entities, and relationships among entities
• Premiere Products requires information about reps,
customers, parts, orders, and order lines
• Entity-relationship (E-R) diagram: represents a
database visually by using various symbols
12
Summary (continued)
• Database management system (DBMS): program
through which users interact with a database; lets
you create forms and reports quickly and easily
and obtain answers to questions about the data
• Advantages of database processing: getting more
information from the same amount of data, sharing
data, balancing conflicting requirements, controlling
redundancy, facilitating consistency, improving
integrity, expanding security, increasing
productivity, and providing data independence
13
Summary (continued)
• Disadvantages of database processing: larger file
size, increased complexity, greater impact of
failure, and more difficult recovery
• Henry Books needs to store information about:
branches, publishers, authors, books, inventory,
and author sequence
14
Database Systems: Design, Implementation, and Management
Structured Query Language (SQL)
1
Objectives
• In this chapter, you will learn:
– 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
– How to use SQL JOIN operator syntax
2
SQL Overview
• SQL — Structured Query Language
• SQL is a simple non-procedural database access language
• Standard for relational database management systems
(RDBMS – system that manages data as collection of tables in
which all relationships are represented by common values in
related tables)
• It was originally developed and defined by IBM research in
early 1970’s
• ANSI/ISO prescribes a standard SQL
• Supported by just about every database product in the
market today
• Several SQL dialects exist
3
History of SQL
• 1970–E. Codd develops relational database concept
• 1974-1979–System R with Sequel (later SQL) created at
IBM Research Lab
• 1979–Oracle markets first relational DB with SQL
• 1986–ANSI SQL standard released
• 1989, 1992, 1999, 2003, 200n–Major ANSI standard
updates
4
Purpose of SQL Standard
• Specify syntax/semantics for data definition and
manipulation
• Define data structures
• Enable portability
• Specify standards
• Allow for later growth/enhancement to standard
5
SQL is considered to be a 4GL.
Why is that important?
• A Fourth Generation Language (4GL) is a nonprocedural
language, (i.e.. its user merely commands what is to be done
without having to worry about how it’s to be done).
• Contrast this approach to that taken by such 3GL
procedural languages as COBOL, BASIC, or Pascal.
• Given this characteristic, 4GL languages are much
easier to use than their 3GL predecessors.
6
SQL – An Ideal Database
Language
An ideal database language must be able to:
– Create database and table structures.
SQL has
a data definition component that gives users the ability to
meet this requirement.
– Manage the data component of the database. SQL
gives users a set of commands to enter, correct, delete, and
update data within database tables.
– Provide detailed data query capability. “Standard”
SQL uses a set of approximately thirty commands that allow
users to retrieve data and to convert raw data into useful
information.
7
SQL – An Ideal Database
Language
– Its English-like query structure is relatively easy to
learn and use.
– It has been said that “if database system does not
use SQL, it’s not relational.” Since
relational
database is the current standard, it’s easy to argue
that SQL plays an important role in database
management and use.
8
SQL Environment
• Catalog
– Set of schemas that constitute the description of database
• Schema
– Structure that contains descriptions of objects created by
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 access to database, including
administering privileges and committing data
9
DDL Commands
• Used to create and modify the structure of database objects
such as tables, indexes, and views
– CREATE
– ALTER
– DROP
• Create commands to define access rights to those database
objects by using DCL commands
_ GRANT
_ REVOKE
(DCL commands are used to enforce database security in a multiple user
database environment)
• DDL commands execute as soon as they are issued, and do not
need to be explicitly saved, or committed
10
SQL Data Definition Commands
11
DML Commands
• Used to insert, view, and modify database data
– INSERT
– UPDATE
– DELETE
– SELECT
• DML commands need to be explicitly saved or
rolled back
– COMMIT
– ROLLBACK
12
Data Manipulation Commands
13
Example of A Simple Database
Model
– Simple Database — PRODUCT and VENDOR tables
• Each product is supplied by a single vendor.
• Each vendor may supply many products.
(Understand the data environment)
14
The Database Model
15
Creating the Database
• Two tasks must be completed:
– create the database structure
– create the tables that will hold the end-user data
• First Task – Create database structure
– RDBMS creates physical files that will hold the
database
– For an enterprise RDBMS, Database administrator
creates structure or schema
• Logical group of tables or logical database
• Groups tables by owner
• Enforces security
16
The Database Schema
• Authentication
– Process through which the DBMS verifies that only
registered users are able to access the database
– Log on to the RDBMS using a user ID and a password
created by the database administrator
• Schema
– Group of database objects—such as tables and
indexes—that are related to each other
CREATE SCHEMA AUTHORIZATION
Example:
CREATE SCHEMA AUTHORIZATION JONES
For most RDBMSs, creating schema authorization is optional, thus, focus is
mainly on creating and manipulating tables
17
Data Types
• Data type selection is usually dictated by the
nature of the data and by the intended use
• Pay close attention to the expected use of
attributes for sorting and data retrieval purposes
18
Basic SQL Data Types
19
Creating Table Structures
Steps in table creation:
• Tables store end-user data
CREATE TABLE
();
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
20
Rules for 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
• RDBMS will automatically enforce referential
integrity for foreign keys
• Command sequence ends with semicolon
21
Viewing Table Information
• Viewing a table’s structure:
•
– Use the DESCRIBE command to display column
names:
– SQL> DESCRIBE tablename;
– SQL>DESC tablename;
22
Example
SQL> CREATE TABLE my_students
2 ( s_id NUMBER(6),
3 s_name VARCHAR2(30),
4 s_dob DATE,
5 s_class CHAR(2));
Table created.
SQL> DESC my_students;
Name
Null? Type
——————————- ——– —S_ID
NUMBER(6)
S_NAME
VARCHAR2(30)
S_DOB
DATE
S_CLASS
CHAR(2)
23
Declaring the Primary Key during
table creation
CREATE TABLE VENDOR
(V_CODE
CHAR(5)
V_NAME
CHAR(35)
V_CONTACT
CHAR(15)
V_AREACODE
CHAR(3)
V_PHONE
CHAR(13)
V_STATE
CHAR(2)
V_ORDER
CHAR(1)
PRIMARY KEY (V_CODE));
NOT NULL,
NOT NULL,
NOT NULL,
,
NOT NULL,
,
,
NOTE: Concept: NULL — unknown, undefined
It is not 0, not a blank character
No operation for it, no comparing for it,
A null is not equal to another null.
24
Declaring the Primary & Foreign Keys
during table creation
CREATE TABLE PRODUCT(
P_CODE
CHAR(10)
NOT NULL
P_DESCRIPT
CHAR(35)
NOT NULL,
P_INDATE
DATE
NOT NULL,
P_ONHAND
NUMBER(3)
NOT NULL,
P_MIN
NUMBER(3)
NOT NULL,
P_PRICE
NUMBER(8,2)
NOT NULL,
P_DISCOUNT
NUMBER(4,1)
NOT NULL,
V_CODE
NUMBER(3),
PRIMARY KEY (P_CODE),
FOREIGN KEY (V_CODE) REFERENCES VENDOR
(V_CODE);
UNIQUE,
25
Constraints
• Rules that restrict the values that can be
inserted into a field
• Types of constraints:
– Integrity: define primary and foreign keys
– Value: specify values or ranges of values that
can be inserted
26
Constraint Levels
• Table constraint
– Restricts the value of a field with respect to all other table
records
– Example: primary key value must be unique for each
record
• Column constraint
– Restricts values in a specific column
– Example: values in a S_GENDER field must be ‘M’ or ‘F’
27
Constraint Names
• Internal name used by DBMS to identify the
constraint
• Each constraint name in a user schema must be
unique
• If you do not name a constraint, the system will
automatically generate an unintuitive name
28
Constraint Names
•
Constraint naming convention:
tablename_fieldname_constraintID
–
•
Constraint ID values:
–
–
–
–
–
•
In reality, this won’t be possible because of the character limitation on names inside
Oracle. When name is too long, follow these steps in order:
Abbreviate table name with table initials (for example, users -> u and users_contact -> uc).
Truncate the column name until it fits.
Primary key: pk
Foreign key: fk
Check condition: cc
