SPECIAL IMPLEMENTATION CONSIDERATIONS - Database Design and Development: An Essential Guide for IT Professionals - page 390

CREATE SCHEMA RAINBOW-FLORIST
AUTHORIZATION Amanda-Russo;
CREATE DOMAIN ItemIdentifier CHARACTER (4) DEFAULT “ZZZZ”
CHECK (VALUE IS NOT NULL);
CREATE TABLE CUSTOMER (
CustNo CHARACTER (8),
CustAddr CHARACTER (45),
CustName CHARACTER (35),
PRIMARY KEY (CustNo) );
CREATE TABLE ORDER (
OrdrNo

CREATE TABLE FLOWER (
ProdID

ItemIdentifier,

ProdCost DECIMAL (5,2) NOT NULL,

FlwrName CHARACTER (25),

FlwrSize SMALLINT,

PRIMARY KEY (ProdID),
FOREIGN KEY (ProdID) REFERENCES PRODUCT (ProdID) );
CREATE TABLE ARRANGEMENT (
ProdID

ItemIdentifier,

ProdCost DECIMAL (5,2) NOT NULL),

CHARACTER (8), ArngeType CHARACTER (15),

OrdrDate DATE, PRIMARY KEY (ProdID),

FOREIGN KEY (ProdID) REFERENCES PRODUCT (ProdID) );
CREATE TABLE CHANNEL (
ChnlID

OrdrAmt DECIMAL (9,2) NOT NULL,

CustNo CHARACTER (8),

PRIMARY KEY (OrdrNo),
FOREIGN KEY (CustNo) REFERENCES CUSTOMER (CustNo)
ON DELETE CASCADE );
CREATE TABLE PAYMENT (
OrdrNo

ItemIdentifier,

ChnlName CHARACTER (35),

PRIMARY KEY (ChnlID) );
CREATE TABLE GROWER (
GrwrID CHARACTER (8), ItemIdentifier,

PmntNo CHARACTER (4), GrwrName CHARACTER (35),

PmntDate DATE, PRIMARY KEY (GrwrID) );

CREATE TABLE SHIPMENT (
OrdrNo CHARACTER (8),
ChnlID ItemIdentifier,
GrwrID ItemIdentifier,
ShipDate DATE,
ShipQty INTEGER,
PRIMARY KEY (OrdrNo, ChnlID, GrwrID),
FOREIGN KEY (OrdrNo) REFERENCES ORDER (OrdrNo),
FOREIGN KEY (ChnlID) REFERENCES CHANNEL (ChnlID),
FOREIGN KEY (GrwrID) REFERENCES GROWER (GrwrID) );

PmntAmt DECIMAL (9,2) NOT NULL,

PRIMARY KEY (OrdrNo, PmntNo),
FOREIGN KEY (OrdrNo) REFERENCES ORDER (OrdrNo) );
CREATE TABLE PRODUCT (
ProdID ItemIdentifier,
ProdCost DECIMAL (5,2) NOT NULL,
CHECK (ProdCost < 100.00 AND ProdCost > 1.00),
PRIMARY KEY (ProdID) );

Figure 13-9 Florist business: data definition in SQL.

the florist business. Walk through each data definition statement, note its purpose,
and see how it is constructed.

Data Retrieval in SQL

Whereas data manipulation in SQL deals with accessing and fetching desired data
from a relational database, data maintenance in SQL relates to the adding,
deleting, and changing of data. SQL provides commands for both types of data
handling—data retrieval and data maintenance. The SQL query is used to fetch
data from a relational database. Let us therefore begin our discussion on data
manipulation by examining the SQL query format.
The basic structure of an SQL query expression consists of three simple clauses
or commands: SELECT, FROM, and WHERE. Take a simple example. Let us say
that you want to list all sales persons working out of the Los Angeles office. For this
purpose, you need to write a query in SQL to retrieve the data about all the sales
persons from those rows in the SALESPERSON table where the SalesOffice
column contains the value “Los Angeles.” Here is this simple query in English:

What are the names of the sales persons in the Los Angeles office?

Now let us write the query using the data manipulation language (DML)
component of SQL.

SELECT SalespersonName
FROM SALESPERSON
WHERE SalesOffice = 'Los Angeles'

SELECT This clause contains the list of columns from the base table to be pro-
jected into the result relation. An asterisk (*) in the clause indicates projec-