Part 28
Exercises
Lab 1 (Short Form) - Entity/Attribute/Value Identification
For each italicized item below, state whether the item is an
· entity class
· entity instance
· attribute
· value
1a. A red Chevrolet automobile
1b. A red Chevrolet automobile
1c. A red Chevrolet automobile
2a. A large can for gasoline that is painted an off-shade of the color red
2b. A large can for gasoline that is painted an off-shade of the color red
2c. A large can for gasoline that is painted an off-shade of the color red
3. A broken cane fishing pole
4. The six employees in the personnel department
5. I called service because lightning struck my computer last night
Lab 1 - Entity Identification
Exercise 1 - Part X
Describe something you own to your partner. Perhaps it is something you are particularly proud of, enjoy using, or that has an interesting story behind it. Your partner will be listening, but may interrupt you to ask questions. Feel free to answer any questions. Your partner will be filling out a form. Feel free to look at the form and help your partner with this form by volunteering information that you think your partner is not getting correctly placed on the form.
Do not show your partner this sheet.
Lab 1 - Entity Identification
Exercise 1 - Part Y
Your partner will be describing something to you. You may interrupt to ask clarifying questions. Your job is to identify:
· the entities being described
· the attributes for each of the entities identified above
· the values for the attributes listed above
· relationships between the items listed above
After you have listened to the description and completed the above tasks, then
· single out the entity of greatest interest
· if you were to create a database out of this entity of greatest interest,
· what attribute(s) would you select as an identifier (I.D.)
· what attributes do you suppose would be
(RET) most frequently used for retrieval
(UPD) most frequently in need of update
(IMP) impractical to store
(OMIT) not worth the effort to store (will probably never be used)
When you are finished, show your work to your partner
Lab 1 - Entity Identification
Exercise 1 - Part Y Table
entity / attribute / value / relationships / I.D. / Ret / Upd / Imp / Omit
Lab 1 - Entity Identification
Exercise 2 - Part Y
Describe to your partner an event that occurred in your life. Try to tell the story chronologically from beginning to end without backtracking (i.e., follow a time-line rather than organizing the story by subjects). Your partner will be listening, and has been instructed not to interrupt you to slow you down, ask for repetition, or ask any questions at all. If your partner does attempt to interrupt, just ignore your partner. Do not look at your partner's sheet or attempt to help your partner in any way. Leave when you have finished your story.
Do not show your partner this sheet
Lab 1 - Entity Identification
Exercise 2 - Part X
Your partner will be describing something to you. You must just listen without asking questions or interrupting. You may not look at your partner's sheet. Your job is to identify:
· the entities being described
· the attributes for each of the entities identified above
· the values for the attributes listed above
· relationships between the items listed above
After you have listened to the description and completed the above tasks, then
· single out the entity of greatest interest
· if you were to create a database out of this entity of greatest interest,
· what attribute(s) would you select as an identifier
(I.D.)
· what attributes do you suppose would be
(RET) most frequently used for retrieval
(UPD) most frequently in need of update
(IMP) impractical to store
(OMIT) not worth the effort to store (will probably never be used)
Lab 1 - Entity Identification
Exercise 2 - Part X Table
entity / attribute / value / relationships / I.D. / Ret / Upd / Imp / Omit
Lab 2 - Structure Identification
Identify a database application in your organization. (You may be asked to share your results with the class, so please don't select a confidential application.) Identify the major entities in this application. Identify the relationships between these entities (one-to-one, one-to-many, many-to-many, hierarchical, network, recursive, etc.).
Make two copies - one to submit to the instructor, and one to keep with you to help you focus on a specific application for the remainder of the course.
Lab 2 - Worksheet
Copy 1 - to submit
Lab 2 - Worksheet
Copy 2 - to keep
Lab 3 - Relational Operators
A relational database model has been sketched out for an order processing application. The proposed schema is shown below and on the next page. Customers place orders for products that are supplied by vendors according to supply agreements.
· The same product can be supplied by many different vendors.
· One vendor can have supply agreements for many different products.
· The same vendor may not have more than one supply agreement for the same product during the same period of time (no supply agreements with overlapping start and end dates for a vendor and a product).
Customer / Product / Vendorcustomer# / product# / vendor#
cust_name / description / st_address
st_address / units / city
city / quantity_on_hand / state
state / price / zip_code
zip_code
Order / Line_Item / Supply_Agreement
order# / order# / vendor#
customer# / line# / product#
order_date / product# / start_date
date wanted / order_quantity / end_date
deposit / price / cost
Lab 3 - Explanations
CUSTOMER (The person who is buying from you)
ORDER (The overall order information)
PRODUCT (What is being bought from vendors and sold to customers)
price (Suggested retail)
LINE_ITEM (The individual items on the order)
price (Actually charged customer)
VENDOR (The companies that supply you with goods to sell to your customers)
SUPPLY_AGREEMENT (The purchasing arrangements you have with your vendors)
Using the above relational schema, produce the results requested on the following page using the relational operators or using SQL
Lab 3 - Questions
1. Customers (number, name, and street address) for all customers in Detroit.
2. Orders (all order attributes, customer name) for all customers in Detroit.
3. Shipping tickets (all line item attributes) for all orders for all customers in Detroit.
4. “Plain-text” shipping tickets (all line item attributes, product description) for all orders for all customers in Detroit.
5. Using the model supplied, do your best to calculate profit margin for each order dated after 01/01/97. Profit margin is the difference between what you actually charged the customer for an item minus what you paid to the vendor for that item that you sold.
6. Comment on the appropriateness of the primary key selected for each of the relations. (At least one is inappropriate.) For those table(s) where the primary key is inappropriate, specify an appropriate key.
7. Given the correction(s) in part #6 above, does this help to get an answer (or a better answer) for part #5 above? At this point, what information would you like to add to this database?
8. Comment on the ability to do vendor tracking through the system, assuming you could modify it as you suggested in parts 6 and 7. (Vendor tracking is the ability to identify which vendor’s product was sent to which customer to fill an order, and which vendor’s products are in inventory.) Is vendor tracking tied to the ability to calculate profit margin? If so, how? If not, why not?
Lab 3 - Worksheet
Lab 3 - Solutions - Relational Algebra
1. R1 = Customer WHERE city = 'Detroit'
R2 = pR1 (customer#, cust_name, st_address)
2. R3 = pR2 (customer#, cust_name)
R3 = R2 ´ Order WHERE R3.customer# = Order.customer#
3. R5 = R1 ´ Order WHERE R1.customer# = Order.customer#
R6 = R5 ´ Line_Item WHERE R5.order# = Line_Item.order#
4. R7 = pProduct (product#, description)
R8 = R6 ´ R7 WHERE R6.product# = R7.product#
5. ?
Lab 3 - Solutions - SQL
1. SELECT customer#, cust_name, st_address
FROM Customer
WHERE city = 'Detroit'
2. SELECT Customer.customer#, Customer.cust_name, Order.order#, Order.order_date, Order.date_wanted, Order.deposit
FROM Customer, Order
WHERE Customer.customer# = Order.customer#
AND Customer.city = 'Detroit'
3. SELECT Customer.customer#, Customer.cust_name, Order.order#, Order.order_date, Order.date_wanted, Order.deposit, Line_Item.line#, Line_Item.product#, Line_Item.order_quantity, Line_Item.price
FROM Customer, Order, Line_Item
WHERE Customer.customer# = Order.customer#
AND Order.order# = Line_Item.order#
AND Customer.city = 'Detroit'
4. SELECT Customer.customer#, Customer.cust_name, Order.order#, Order.order_date, Order.date_wanted, Order.deposit, Line_Item.line#, Line_Item.product#, Line_Item.order_quantity, Line_Item.price, Product.description
FROM Customer, Order, Line_Item, Product
WHERE Customer.customer# = Order.customer#
AND Order.order# = Line_Item.order#
AND Line_Item.product# = Product.product#
AND Customer.city = 'Detroit'
Semantic Disintegrity
It is possible to obtain invalid results even though a query “appears” to be correct (at least is syntactically correct)
Causes of the error
· Operations performed on unnormalized data
· Operations performed on incorrectly modeled data
· Misunderstanding the semantics regarding the composition of the file
· Misunderstanding the semantics regarding the relationships among the entities represented in the files
Preventing semantic disintegrity
· On projection, the resulting relation must contain the primary key or a candidate key
· On join, at least one of the two join attributes must be a primary key or a candidate key
· The above two are sufficient to prevent the problem, but are overly restrictive
Lab 4 - Normalizing an Order Form
1. Attached is a University of St. Thomas purchase requisition. List the "fields" on the attached order form.
2. Attempt to structure these fields into a single record in “0th” normal form.
3. Using the normalization techniques, project the fields into an increasing number of relations until the “database” is in 5th Normal Form.
(Replace this page with a University of St. Thomas Purchase Requisition)
Lab 4 - Overall Purchase Requisition Description
The information below is intended to assist you in understanding the fields on the form, their meaning, their use, their relationships to each other, and their relationships to the overall University of St. Thomas information system. The explanation follows a more-or-less logical flow from top to bottom of the form.
A requisition is just that - a request which may or may not be filled. Anything can be requisitioned, from a pencil to a new building, from an order to be filled to an order already filled, and from a payment going to a company to a payment going to an employee. The date at the top is the date the purchase requisition form was filled out. Requisition numbers are unique, but not tracked or accounted for. Some requisition forms are lost or destroyed and never filed. Some are denied and never result in a purchase. Those requisitions that are honored result in a purchase order being “cut.” A purchase order is both an order to a vendor and a promise to pay. Purchase order numbers are unique, tightly controlled by the purchasing department, and accounted for. If the requisition is honored, the purchasing department fills out the “purchasing use only” line with the vendor number, purchase order number, date the purchase order was cut, and the name of the person in the purchasing department who cut the purchase order. Each requisition results in at most one purchase order being cut. However, a single purchase order may consolidate requests on several requisitions. The vendor number is a unique identifier of someone to purchase from and pay, and is controlled by the purchasing department. If a request is made to buy from someone new, purchasing assigns a new vendor number.
Any employee can request anything and request that it be charged to any budget and shipped anywhere. However, this is a lot less chaotic than it sounds. When a request is made to spend money, the request must specify which “department” will be charged. (Actually, the “department” is really a “fund,” but that’s because we do fund accounting, a detail you probably don’t have to worry about.) Each department has one head, however one person may be the head of many departments (because they may control many funds). Before a requisition can be honored by purchasing, it must bear the signature of the department head, so that no one can spend a department’s money without the department head’s permission.
Lab 4 - Line-by-Line Purchase Requisition Description
Clarification of selected fields on the form:
Certain requisitions require a vice-president’s approval. These include requests to pay yourself, requests over budget, and very large requests. There exists a list of vice presidents and the departments for which they can make approvals.
The PHONE field is the telephone number of the requester.
The Soc.Sec.No. is the social security number of the individual to whom payment is requested in those cases in which the requisition is for payment to an individual.
The Change Order field is used in those situations in which a purchase order already exists and there is a request to change the contents of an existing purchase order. In this case, the Change Order field contains the purchase order number (not the requisition number) of the purchase order to be changed.
Requisitions can specify that several budget accounts be used to divide up the cost. Each department has many account numbers, but each account number refers only to one department. A requisition could divide the costs between several accounts in the same department, or accounts in different departments. That division can be made either on a percentage basis, or on a fixed dollar basis. So for each line with account number/percent/amount, the line will contain either a percent or an amount.
In the bottom section, items ordered are given a line number, starting at 1. This number goes in the NO. field at the beginning of the line describing the item.
The UNIT field is used to describe the unit of measurement. If I order 24 pencils, but pencils are only sold by the dozen, then quantity = 2 and unit = dozen.
The line total field is just the quantity times the unit price.
Lab 4 - Worksheet