In this worksheet you will learn how to use SQL as a data definition language $($DDL$).$ DDL commands allow you to create, alter, and drop tables. Selected questions refer you to pages in your textbook that you should study prior to answering the question.

Creating Tables

$1)$ In creating tables, you must describe each column in the table and specify the data types. We will use the data types listed in Table $5-2$: Sample Data Types on page $213.$ For each of the following, select a data type from the table that would be a suitable choice. In some cases, there may be more than one possible choice.

a$)$ social security number

b$)$ birthday

c$)$ two$-$character state abbreviation

d$)$ gpa $($assume $2$ decimal place accuracy$)$

e$)$ number of dependents

f$)$ first name

$2)$ Look at Figure $56$: SQL Database Definition Commands for Pine Valley Furniture Company, page $218,$ and answer the following questions. $($Ref: pages $217-218$: Creating Tables$)$

a$)$ What is the name of the table that stores information about customers?

b$)$ What is the name of the primary key constraint for the customer table?

c$)$ Can PRODUCT$\_$ID in the table ORDER$\_$LINE$\_$T be null?

d$)$ What is the data type for CUSTOMER$\_$ID$?$

e$)$ What is the name of the primary key constraint for table ORDER$\_$LINE$\_$T$?$

f$)$ What are the names of the foreign key constraints for table ORDER$\_$LINE$\_$T$?$

g$)$ What attribute$($s$)$ make up the primary key for table ORDER$\_$LINE$\_$T$?$

h$)$ What attribute$($s$)$ make up the foreign key in table ORDER$\_$LINE$\_$T that references table ORDER$\_$T$?$

$3)$ Write the SQL CREATE TABLE command to create two tables named VENDOR$\_$T and PART$\_$T$.$ Attributes for table VENDOR$\_$T are VendorID $($primary key$),$ VendorName, and PhoneNum. Attributes for table PART$\_$T are PartID $($primary key$),$ PartDesc, QtyOnHand, UnitPrice, and SupplierID $($foreign key into VENDOR$\_$T$).$ VendorID is a $3-$digit code, and PartID is a $5-$digit code. VendorName and PartDesc each have a maximum length of $50$ characters. Phone number is stored as a $15-$digit string. No unit price exceeds $$10,000\mathrm{.}00.$ PartDesc cannot be null. Assume updates cascade and deletes are restricted in VENDOR$\_$T$.($Ref: pages $217-219$: Creating Tables$)$

$4)$ If you want to add a column to table PART$\_$T or change an existing column's name, which SQL command do you use? $($Ref: pages $220-221$: Changing Table Definitions$)$

$5)$ Write the SQL command to remove the table PART$\_$T from the database. $($Ref: page $221$: Removing Tables$)$

$6)$ Indexing primary and$/$or secondary keys can increase the speed of $($Ref: page $225$: Internal Schema Definitions in RDBMSs$)$

$7)$ Dropping indexes can increase the speed of

$8)$ Write an SQL statement to create an index named CUST$\_$IDX for the CUSTOMER$\_$ID column of table ORDER$\_$T $($see Figure $5-6,$ page $218,$ for a description of table ORDER$\_$T$).$

$9)$ Write an SQL statement to drop the index you created above.