August 1999
DB212 : DATABASE MANAGEMENT SYSTEMS

QUESTION 5

Total Marks: 20 Marks

 Click here to access other questions

SUGGESTED SOLUTIONS
Solutions and allocated marks are indicated in green.
Return to Question 5
Consider the following four tables.

 
Customer
CUSTNUM CUSTNAME CUSTADDR BALANCE CREDLIM REPNUMB
124 Adams, Sally 481 Oak, Lansing 418.75 500 3
256 Samuels, Ann 215 Pete, Grant 10.75 800 6
311 Charles, Don 48 College, Ira 200.10 300 12
315 Daniels, Tom 914 Cherry, Kent 320.75 300 6
405 Wiliams, Al 519 Watson, Grant 201.75 800 12
412 Adams, Sally 16 Elm, Lansing 908.75 1000 3
522 Nelson, Mary 108 Pine, Ada 49.50 800 12
567 Baker, Joe 808 Ridge, Harper 201.20 300 6
587 Rogerts, Judy 512 Pine, Ada 57.75 500 6
622 Martin, Dan 419 Chip, Grant 575.50 500 3

Order

ORDNUMB ORDDTE CUSTNUMB
12489 90291 124
12491 90291 311
12494 90491 315
12495 90491 256
12498 90591 522
12500 90591 124
12504 90591 522


ORDLNE

ORDNUMB PARTNUMB NUMBORD QUOTPRCE
12489 AX12 11 14.95
12491 BT04 1 402.99
12491 BZ66 1 311.95
12494 CB03 4 175.00
12495 CS11 2 57.95
12498 AZ52 2 22.95
12498 BA74 4 4.95
12500 BT04 1 502.99
12504 CZ81 2 108.99

PART

PARTNUMB PARTDESC UNONHAND ITEMCLSS WRHSNUMB UNITPRCE
AX12 IRON 104 HW 3 17.95
AZ52 SKATES 20 SG 2 24.9
BA74 BASEBALL 40 SG 1 4.95
BH22 TOASTER 95 HW 3 34.95
BT04 STOVE 11 AP 2 402.99
BZ66 WASHER 52 AP 3 311.95
CA14 SKILLET 2 HW 3 19.95
CB03 BIKE 44 SG 1 187.50
CX11 MIXER 112 HW 3 57.95
CZ81 WEIGHTS 208 SG 2 108.99

 
Based on the tables above, write SQL statements to carry out the following tasks:

 
(a) Create the PART table, using the following table definition.

PART

Column Type Length Decimal Places
PARTNUMB Char 4  
PARTDESC Char 10  
UNONHAND Numeric 3 0
ITEMCLSS Char 2  
WRHSNUMB Numeric 1 0
UNITPRCE Numeric 7 2

 

 [4]
CREATE TABLE PART
     (PARTNUMB CHAR(4), PARTDESC CHAR(10),
     UNONHAND NUMERIC(3,0), ITEMCLSS CHAR(2),
     WRHSNUMB NUMERIC(1,0) UNITPRCE NUMERIC(7,2));

 
(b) From the CUSTOMER table, which records can be removed without introducing any anomaly? Justify your answer. Give an SQL statement to remove one of these records. [3]
Customers with CUSTNUMB 405,412,567,587 and 622

NB: all 5 records needed!

This is possible as these customers do not have any order transactions. Removal of these records will therefore not cause any anomaly.

DELETE FROM CUSTOMER
   WHERE CUSTNUMB = 405;

 
(c) Find the number, name, current balance and sales rep number of those customers whose balance is greater than the balance of every customer of sales rep 12. [6]
SELECT CUSTNUM, CUSTNAME, BALANCE, REPNUMB
   FROM CUSTOMER
      WHERE BALANCE > ALL (SELECT BALANCE
                FROM CUSTOMER WHERE REPNUMB = 12);

 
(d) Create a view LISTING with headers ORDER_NUMBER and ORDER_TOTAL to list the order total for those orders whose total is more than $200. [5]
CREATE VIEW LISTING (ORDER_NUMBER, ORDER_TOTAL)
     AS
     SELECT ORDNUMB, SUM(NUMBORD * QUOTPRCE)
          FROM ORDLNE
          GROUP BY ORDNUMB
          HAVING SUM(NUMBORD * QUOTPRCE) > 200;

 
(e) Give two advantages of using the VIEW command. [2]
Use of a view can allow someone access to just the rows and columns that they need from the database tables, particularly restricting them from irrelevant and sensitive information.

By assigning a view name, users can retrieve the required information just by selecting the already created view by its assigned name, without having to explicitly query the database using the data manipulation language component of SQL.