7 short labs in Database

GEIT 3341 DATABASE 1 LAB 5

GEIT 3341 Database I

Lab 5

Schema Creation using SQL

Due Date:

Objective(s) Target CLO(s) Reference 1. Practice the CREATE TABLE 6 Chapter 6,7

command of SQL DDL by creating two tables and linking them together.

2. Use the CHECK constraint to specify a domain for certain columns.

3. To use the INSERT command of SQL DML in order to populate the tables with data.

4. Use the ALTER TABLE command to add a new column.

5. Use the UPDATE command to assign a value of the new column for each row.

Name

ID

Section

GEIT 3341 DATABASE 1 LAB 5

Instructions:

Use the CREATE TABLE command you learned in Chapter 7 and the INSERT command

you learned in Chapter 6 to create the following schema consisting of 3 tables. For each

field, pick the most appropriate data type for it. While creating the STORE table, add a

CHECK constraint to ensure that values of the STORE_YTD_SALES$ are greater than

zero.

Table Name: EMPLOYEE Primary Key: EMP_CODE

EMP_CODE EMP_LNAME EMP_FNAME EMP_DOB EMP_PHONE 1 Williamson John 29-DEC-62 (06) 434-0095

2 Ratula Nancy 12-MAR-67 (02) 526-1192

3 Greenboro Lottie 02-NOV-59 (03) 231-6292

4 Rumpersto Jennie 11-APR-64 (06) 224-1122

5 Shawn Michael 23-DEC-60 (06) 599-0406

6 Jones Rose 13-SEP-65 (06) 111-6262

7 Rosten Peter 25-OCT-65 (02) 111-1133

8 Bret Hart 12-AUG-67 (02) 796-1122

9 Ron Frank 11-NOV-60 (03) 432-1356

10 Elain Roberts 23-FEB-62 (06) 732-1967

Table Name: STORE Primary Key: STORE_CODE Foreign Key: REGION_CODE references REGION_CODE in REGION table

STORE_CODE STORE_NAME STORE_YTD_SALES$ REGION_CODE

1 Access Jungle 20000.65 1

2 Database Corner 55000.00 2

3 PC Master 110000.44 2

4 Computer City 82000.11 1

GEIT 3341 DATABASE 1 LAB 5

Table Name: REGION Primary Key: REGION_CODE

REGION

REGION_CODE REGION_DESCRIPT

1 East

2 West

3 North

4 South

After creating the three tables, use the ALTER TABLE command to add a new column

STORE_CODE to the EMPLOYEE table. Then use the ALTER TABLE command again to

designate it as a foreign that references STORE_CODE in STORE table. After this, use the

UPDATE command to set the STORE_CODE of each employee according to the following

table:

EMP_CODE STORE_CODE 1 1

2 2

3 2

4 4

5 1

6 2

7 3

8 1

9 2

10 3

Hand in:

1. The three CREATE TABLE commands for the three tables. (5 points) 2. The ALTER TABLE command to add the STORE_CODE column. (1 points) 3. The ALTER TABLE command to designate STORE_CODE as a foreign key. (1 points) 4. The UPDATE commands you used to assign a STORE_CODE for each employee.

(2 points) 5. Screen shots of the content of the three tables. (1 points)