Database Languages
eXercise #13 - Trial Test
Mogul- The Multi-Media Wholesalers
Mogul is a large
media wholesaling company, supplying Audio Visual media to a large retail
client base. Mogul specializes in Entertainment media,
namely CD's, Cassettes, Videos and Records(LP's).
MOGULs client base is their most valuable asset. Details
about the businesses that buy through MOGUL, along with a contact person
is kept up-to-date. All customers have allocated to them a credit limit.
This determines the maximum debt allowable. It can be exceeded only
with the Managers permission.
Customers place orders with MOGUL for items. An order
may consist of a number of different items (each of which has an order
quantity and price). Orders for individual items are dispatched as they
become available. If those items are currently in stock, they are dispatched
to the customer as quickly as possible.
MOGUL products are uniquely identified by a 10 CHARACTER
CODE. The CD, CASSETTE and VIDEO version of an album, say, all have
different serial_numbers to uniquely identify them. When items
fall below a particular level, they are re-ordered (using a quanity
determined by the manager). This re-order process is initiated manually.
The style of the product, as determined by the
Manager, is one of the following:
CWST -
Country and Western
ROCK - Popular/Rock
CLAS - Classical
INST - Instrumental (Non-Vocal)
METL - Heavy Metal
KIDS - Childrens
LIFE - Exercise/Lifestyles
Assume you have access to the functions days(yyyymmdd)
and ddate(days) that allow you to easily convert from
decimal date to the number of days since 1900 and back
again respectively should you need to.
ALL dates are decimals of form YYYYMMDD (eg
19920612 = 12th June 1992).
You also have access to the system variable sysddate
which returns the current date in decimal date format
MOGUL use a computerised Information System to manage
the retail side of their operation.
The table definitions are as follows:
Table: Customers
cust_id key/dec(8) a
unique code identifying each customer
contact ma/char(15) the contact
person in that company
bus_name op/char(15) the name of
the business
bus_phone ma/dec(15) the business
phone number
street ma/char(20) the NUMBER and
STREET part of address
town ma/char(15) the suburb or town
of address
pcode ma/dec(4) the POST CODE
part of the address
cr_limit ma/dec(10) maximum
DEBT allowable (unpaid)
curr_bal ma/dec(10) current
amount OWING
Table: Orders
order_num key/dec(8) a
unique code identifying each order
order_date ma/dec(8) date
of order placement in form YYYYMMDD
cust_id ma/dec(8) from Customers
table
Table: Order_Details
order_num key/dec(8) from
Orders table
serial_num key/char(10) from Products
table
order_qty ma/dec(5) number of that
item ordered
order_price ma/dec(10) cost of that
group of items
send_date op/dec(8) dec. date
of dispatch (null if not sent)
Table: Products
serial_num key/char(10) unique
identification for an item
title op/char(15) the name
of the item
artist op/char(15) who performed/authored
this title
style ma/char(4) 'in-house'
classification of subject type
media ma/char(4) CASS, LP, CD,
VID
on_hand_qty ma/dec(5) number in stock
(0 means out of stock)
reorder_level ma/dec(5) buy more if
on_hand less than this
list_price ma/dec(10) customer price
per unit
1. TABLE STRUCTURE
a. Regarding the Order_Details table,
i. What is the primary key?
ii. Describe what makes this the logical choice for the key
iii. The Order_Details table contains what appears
to be a redundancy (ie. a derivable fact)- order_price.
Suggest a good reason why this column has been included,
given the nature of the system it is part of.
b.
- write a valid create table to bring the order details table into existence
- write a valid insert into command to correctly register an order in the orders table (make up the relevent data).
2. QUERYING THE SYSTEM
Write single select statements
that form result tables for each of the following :
a. List the business name, phone number
and contact person of all known customers
b. List the title and number on hand
of all CDs that need re-ordering (ie. their on_hand_qty
is less than the reorder_level)
c. List the media types currently in stock
for the title 'Teletubbies Sing KORN's greatest hits'.
d. Given that style in the Products table
is one of the following:
i. List the total list_price for all Classical
stock.
ii. List the titles of all childrens videos
currently in stock.
iii. List the order numbers of all orders that
include heavy metal videos.
iv. List the number of different titles in
stock grouped by media.
e. List the business names of customers that currently
exceed their allowable credit limit.
f. Given that an order may have different quantities
of different products:
i. List the total number of different products
in order number 215
ii. List the total value of order number
215
g. List the order numbers of those orders not yet
fully dispatched.
h. List the serial number of the product
that is worth the most (ie. on_hand_qty x list_price)
i. List the serial number and total quantity
currently on hand of the product that has been ordered
the most (ie. the most popular product).
j. List the average list price of CD's
in stock.
k. List the number of orders that were received
after January 1, 1992
l. List the serial number and title
of all products that were ordered at least twice
in May 1992.
m. List the title and media of all
products that are currently out of stock
n. Suppose that the Video 'Kylie sings MegaDeaths's
Greatest' is no longer available (ie. your supplier actually listened
to it and decided to ban it). Complete the following query
that will list the phone number, business name
and contact persons name of all those customers
that have ordered this title (so we can ring and apologise).
select bus_phone, bus_name, contact
from customers
where cust_id in
(select cust_id
from orders
where order_num in
p. Explain, in Plain English, what the following query
does:
select A.order_date
from orders A, order_details B
where A.order_num = B.order_num
and A.order_date between 19910601
and 19910630
group by A.order_date
having sum(order_price) > 1000
q. Write a query that will list the most successful day
of business on record in terms of the value
of orders taken on that day.
3. SYSTEM MAINTENANCE
a. Write a valid create table command that would
bring into existence the orders table implementing all
of the visible characteristics. You will assume that you are already
logged to the relevant path, the database is loaded,
and you have access privileges to perform the creation.
b. Draw a suitable ORDER FORM, inclide on it all
detail you feel is necessary when taking an order. Check it by filling
it out with an order
i. Using information provided in your form, write the necessary
insert into commands so that this order becomes part
of the system. You may use a number of different commands if you
feel you need to. You will need to assume that this order is not
yet dispatched.
ii. Write down two (2) of the many things that the system
would need to check before allowing these changes to be committed
to of the database:
c. Regarding orders:
i. Describe, in English the update process, in terms
of this information system, that must be undertaken each time part
of an order is dispatched. Mention all tables and
columns involved, as well as outline the changes necessary on the
page that follows:
ii. Write the update table command necessary to
update the products table from part (i) of this question.
Assume that order number 42 has just been completely
dispatched (ie. today) and the other table(s) are also already
updated. Your update command will need to contain a correlation.
d. Suppose, as system designer, you are approached to create a
query that contained a summary of customer IDs,
the business name and the number of outstanding
orders (ie. not completely dispatched)
e. Suppose you, as system designer, find out that your data entry
operator has made a number of major errors, and he has entered a large
number of rows into the orders table without entering
corresponding customer IDs.
The database definition should prevent this from happening - how?
f. Suppose the same careless data entry operator (who is now looking
for another job) also, by accident, entered into the customers
table a number of duplicate names. If we again assume that the system
security was relaxed so this was allowed to happen, write a valid
delete from command that eliminates all but one
copy of each customers information on the page that follows:
