Chat with us, powered by LiveChat 7 short labs in Database | Gen Paper
+1(978)310-4246 credencewriters@gmail.com
  

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)

error: Content is protected !!