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

&5($7( 7$%/( $57,67
$UWLVW,' ,QW 127 18// ,'(17,7<

/DVW1DPH &KDU 127 18//

)LUVW1DPH &KDU 127 18//

1DWLRQDOLW\ &KDU 18//

'DWH2I%LUWK 1XPHULF 18//

'DWH'HFHDVHG 1XPHULF 18//

&21675$,17 $UWLVW3. 35,0$5< .(< $UWLVW,'

&21675$,17 $UWLVW$. 81,48( /DVW1DPH )LUVW1DPH

&21675$,17 1DWLRQDOLW\9DOXHV &+(&.

1DWLRQDOLW\ ,1 &DQDGLDQ (QJOLVK )UHQFK
*HUPDQ 0H[LFDQ 5XVVLDQ 6SDQLVK
8QLWHG6WDWHV

&21675$,17 %LUWK9DOXHV&KHFN &+(&. 'DWH2I%LUWK 'DWH'HFHDVHG

&21675$,17 9DOLG%LUWK<HDU &+(&.

'DWH2I%LUWK /,.( >@>@>@>@

&21675$,17 9DOLG'HDWK<HDU &+(&.

'DWH'HFHDVHG /,.( >@>@>@>@

&5($7( 7$%/( :25.
:RUN,' ,QW 127 18// ,'(17,7<

7LWOH &KDU 127 18//

&RS\ &KDU 127 18//

0HGLXP &KDU 18//

>'HVFULSWLRQ@ 9DUFKDU 18// '()$8/7 8QNQRZQSURYHQDQFH

$UWLVW,' ,QW 127 18//

&21675$,17 :RUN3. 35,0$5< .(< :RUN,'

&21675$,17 :RUN$. 81,48( 7LWOH &RS\

&21675$,17 $UWLVW). )25(,*1 .(< $UWLVW,'

5()(5(1&(6 $57,67 $UWLVW,'
2183'$7( 12 $&7,21
21 '(/(7( 12 $&7,21

Figure 7-13
SQL Statements to Create
the VrG artISt and WOrK
tables with Default Values
and Data Constraints

SQL IN keyword is used to provide a list of valid values. The SQL NOT IN keyword also can be
used for negatively expressed domain constraints (not shown in this example). The SQL LIKE
keyword is used for the specification of decimal places. Range checks are specified using com-
parison operators such as the less than ( < ) and greater than ( > ) symbols. Because interrelation
constraints are unsupported, comparisons can be made as intrarelation constraints between
columns in the same table.

By ThE WAy DBMS products are inconsistent in their implementation of CHECK con-
straints. The use of the SQL LIKE keyword in Figure 7-13, for example, will
not work with Oracle Database 11 g Release 2. However, Oracle Database 11 g Release 2
implements other types of constraints. Unfortunately, you must learn the peculiarities of
the DBMS you use to know how best to implement constraints.

Creating the View Ridge Gallery Database Tables
Figure 7-14 shows SQL for creating all of the tables in the View Ridge Gallery database docu-
mented at the end of Chapter 6. Read each line and be certain that you understand its function
and purpose. Notice that deletions cascade for the relationships between CUSTOMER and
CUSTOMER_ARTIST_INT and between ARTIST and CUSTOMER_ARTIST_INT.
Any DBMS reserved words used as table or column names need to be enclosed in square
brackets ([ and ]) and thus converted to delimited identifiers. We have already decided to use