Below is given a Logical Model in the form of an Entity Set that describes only the core of the process for the Warehouse Storage.

 

REGION [REGION_ID, REGION_NAME]

 

DIV      [DIVISION_ID, DIVISION_NAME, REGION_ID (FK)]

 

WHS   [WAREHOUSE_ID, CITY, rating, FOUND_DATE, DIVISION_ID (FK)]

 

                                                                                                                            

SEC     [WAREHOUSE_ID (FK), SECTION_ID, DESCRIPTION, capacity]

 

Data already loaded into the tables are shown on the next page

 

 

 

> Select * From WHSE;

 

WAREHOUSE_ID SECTION_ID DESCRIPTION                               CAPACITY

------------ ---------- ---------------------------------------- ----------

        101         1 For basic maintenance purposes              500000

        101         2 For huge tools and                           80000

        101         3 Office supplies and                        

        102         2 Office furniture and                          3000

        102         2 Office furniture and                          3000

        201         1 Electrical Parts                             15000

        401         1 Chemical tools                              20000

        401         2 Various Chemicals                                  

EMPLOYEE_ID ENAME     JOB          MGR_ID HIREDATE     SALARY      COMM DIVISION_ID

----------- ---------- --------- ---------- --------- ---------- ---------- -----------

      7839 KING      PRESIDENT      7839 17-NOV-81      5000         0         10

      7698 BLAKE     MANAGER        7839 01-MAY-81      2850         0         30

      7782 CLARK     MANAGER        7839 09-JUN-81      2450         0         10

      7566 JONES     MANAGER        7839 02-APR-81      2975         0         20

      7654 MARTIN    SALESMAN       7698 28-SEP-81      1250      1400         30

      7499 ALLEN     SALESMAN       7698 20-FEB-81      1600       300         30

      7844 TURNER    SALESMAN       7698 08-SEP-81      1500         0         30

      7900 JAMES     CLERK          7698 03-DEC-81       950         0         30

      7521 WARDAR    SALESMAN       7698 22-FEB-81      1250       500         30

      7902 FORD      ANALYST        7566 03-DEC-81      3000         0         20

      7369 SMITH     CLERK          7902 17-DEC-80       800         0         20

      7788 SCOTT     ANALYST        7566 09-DEC-82      3000         0         20

      7876 ADAMS     CLERK          7788 12-JAN-83      1100         0         20

      7934 MILLER    CLERK          7782 23-JAN-82      1300         0         10

      8777 HOLMES    SALESMAN       7698 23-OCT-87      1800      1200         30

 

> Select * From DIV

DIVISION_ID DIVISION_NAME             REGION_ID

----------- ------------------------- ----------

        10 Admin                             3

        20 Chem                              2

        30 Maint                             2

        60 Mech                              1

        80 Misc                              4

 

 

 

 

1 Create a table called REG (meaning Region).

It will have columns REGION_ID and REGION_NAME.

Region id will be a PK done at the table level with a standard proper naming used.

Region name will be NN with no naming needed and done at the column level.

 

SQL here

 

 

 

 

2 SQL to show the structure of the table you just created and show the result from that SQL statement.

 

 

 

 

3 For table SEC add the PK and FK

SQL                  

 

 

 

 

 

4 Add a new row of data to the SECTION table for Section 3 in Warehouse 402. For the description use your name and capacity 7000.

SQL here:

 

 

 

 

 

5 Do a select that shows all rows and columns in SECTION.

SQL here:

 

 

 

 

 

6 Create a sequence for WHSE that will start at 500 and increment by 5 to a maximum of 730. Do not store the values in memory.

SQL here:

 

 

 

 

7 Add the following rows of data to WHSE

 

warehouse_id  city  rating        Found_date Division_id

 

Hamilton  nothing    Use current date 60

 

            Niagara Falls    A Use current date 60

 

Quebec G Use current date 60

 

SQL here:

 

 

 

8 Delete the all rows that have a rating of A.

SQL only here:

 

 

 

 

9 Update all rows with a rating of B to A.

SQL only here:

 

 

 

10 Show all rows in WHSE and all columns.

SQL here:

 

 

 

 

11 Create a table called MANAGERS from the EMP table that contain only those with a JOB called 'Manager'. Do not create the table from scratch.

SQL here:

 

 

 

 

 

12 Show the select * results on table MANAGER,

SQL here:

 

 

 

 

13 Create view that shows

- manager name

- department name of manager

- employee name

- department name of employee

 

SQL here for the view:

 

 

 

 

 

 

 

14 SQL to show results of executing the view:

SQL here:

 

15 If you want to modify the view what is the first line of the command.

 

 

 

 

16 After a lot of testing for a new system the computer specialist discovered 2 tables called EMP1 and EMP2. What they want is a list of any duplicates. Just state the one-word key name in the join.

 

 

 

17 Same tables as in question 15, but only want to see the ones unique to EMP2. Again what is the one-word key name.