IPT home IPT A Virtual Approach by Peter Whitehouse
Quick Links:
 
 
IIS home
Information and Intelligent Systems Social and Ethical Implications Human Computer Interaction Software and Systems Engineering eXercise Files Course Outline and Assessment A-Z of Geeky Acronyms Terrace Work Program 2004 Sillybus FAQ = Frequently Asked Questions Help
 
 

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.

    1. write a valid create table to bring the order details table into existence

    2. 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:

Solutions

wonko@wonko.info
©Copyright t 1992..2017+. Edition 25.150117
wonkosite
Creative Commons License
This work is licensed under a
Creative Commons Attribution-NonCommercial-ShareAlike 2.1 Australia License
.