SQL for Database Construction and Application Processing - Database Processing: Fundamentals, Design, and Implementation - page 280

CUSTOMER

CustomerID

ARTIST

TRANS WORK

ArtistID

LastName
FirstName
AreaCode
LocalNumber
Street
City
State
ZipPostalCode
Country
Email (AK1.1)

PURCHASES/SOLD_TO ACQUIRED CREATES/CREATED_BY

TransactionID WorkID

LastName (AK1.1)
FirstName (AK1.1)
Nationality
DateOfBirth
DateDeceased

DateAcquired
AcquisitionPrice
DateSold
SalesPrice
AskingPrice
WorkID (FK)
CustomerID (FK)

Title (AK1.1)
Copy (AK1.2)
Medium
Description
ArtistID (FK)

CUSTOMER_ARTIST_INT

CustomerID (FK)
ArtistID (FK)

HAS_INTEREST_IN ADMIRED_BY

Figure 7-1
Final View ridge Gallery
Database Design

design for View Ridge Gallery is shown in Figure 7-1. In this chapter, we will use SQL to build
the View Ridge Gallery database based on that design.

SQL DDL and DML

Figure 7-2 summarizes the new SQL DDL and DML statements described in this chapter. We
begin with SQL DDL statements for managing table structures, including CREATE TABLE,
ALTER TABLE, DROP TABLE, and TRUNCATE TABLE. Using these statements, we will build
the table structure for the View Ridge Gallery database. Then we present the four SQL DML
statements for managing data: INSERT, UPDATE, DELETE, and MERGE. Next, we will discuss
the SQL statements used to create, use, and manage SQL views. We will end the chapter with a
discussion of SQL/Persistent Stored Modules (SQL/PSM) and of functions, triggers, and stored
procedures.

Figure 7-2
Chapter 7 SQL Elements

• SQL Data Definition Language (DDL)
— CREATE TABLE
— ALTER TABLE
— DROP TABLE
— TRUNCATE TABLE
• SQL Data Manipulation Language (DML)
— INSERT
— UPDATE
— DELETE
— MERGE
• SQL Views
— CREATE VIEW
— ALTER VIEW
— DROP VIEW
• SQL Persistent Stored Modules (SQL/PSM)
— Functions
— Triggers
— Stored Procedures