group project

Deliverables will include:

1. Problem Definition

2. Stakeholder Analysis with 3-5 business questions for each stakeholder type

3. Solution Objectives

4. Create Database

a. Create the ERD/class diagram.

b. Create the tables in MS SQL Server along with all necessary relationships and integrity constraints.

c. Enter sample data into the tables to test your design of at least 10 records per table.

d. Create at least 10 queries including single table, multiple table, calculation, aggregate function, union, subquery, insert, update, delete. Also, create a query to answer each of the questions asked at the end of each case. (You can double count, so if an answer to a question has you make a calculation query then you have fulfilled the calculation requirement. You will end up with 10 - 17 queries.)

e. Create at least 2 views, one stored procedure, and one trigger.

5. Security Analysis (include policies and procedures)

6. Disaster Recovery/Business Continuity Plan (especially backup plan)

7. Evaluate your results (compare actual to expected, suggestions, modifications, evaluation criteria, etc.)

This project will be presented during the last week of class by the group. Every group member must participate.

Rubric

Projects Crystal Tigers 3 Capitol Artists 4 Offshore Speed 5 Standup Foods 6 EnviroSpeed 7 Many Charms 8 RMN Auto Sales 9 Purchases 9 Sales 9 Warranties and Payments 10 Monster Video 12 Initial Appraisal 12 Proposal Options 13 Installation 13 Big Chemical, Inc. 16 Employee Information 16 Sensors 16 Employee Access 17 Big Teeth Reality TV 19 Applications 19 Episodes and Events 20 Cerulean Bee 22 Artwork 22 Orders 24 Orange Blossom Nursery 25 Plant Orders 25 Payments and Billing 28 Delivery 28 Purchases 29 Round Avon Theater Troupe 30 Shows 31 Bills and Salaries 31 Traveling Shows 32 Sales Analysis 33 Joe’s Chops 34 Choosing Options 34 Web Site Communications 35 Itemized Billing 36 Rational Room Reservations 37 Rooms 37 People 38 Reservations 39 Pliable Plastic Production 41 Design 41 Input Purchases 42 Production 43 Shipping 44 On the Hoof Dairy 45 Cows 45 Feed 46 Medical Treatments 47 Cool Wheels Shipping Company 48 Customers 48 Shipping Requests 48 Drivers 49 Trucks 49 Trips 50 e-Gadget Production 52 Product Designs 53 Component Quality 53 Production Stage 54 Shipments 54

Crystal Tigers is a service club with about 150 members. The club primarily sponsors events such as community pancake breakfasts, local concerts, and sporting competitions. The club successfully uses the events to raise money for various charitable organizations. The club needs a database to help track the roles of the various members, both in terms of positions within the organization and their work at the events. The following form represents the basic data that needs to be collected.

1. Analyze the form and create the main classes and associations needed to

maintain the data for this organization.

Enter sample data for the Crystal Tigers service club database. You can make up data, but remember that it has to be consistent. You might want to share data with other students so that everyone has a larger database to work with. Then create queries to provide the following business information.

1. List all of the members who have been president of the organization.

2. List the charities for which the club has raised more than $1,000.

3. Pick an event and list all of the members who worked at that event.

4. Count the number of events and the amount of money raised for each charity.

5. List the total number of service hours provided in the latest year.

6. List the number of service hours provided by each member.

7. List the members who have held the most number of officer positions.

Capitol Artists is a partnership among several commercial artists that work on freelance and contract jobs for various clients. Some jobs are contracted at a fixed price, but complex jobs require billing clients for the number of hours involved in the project. To help the artists track the time spent on each project, the firm wants you to build an easy-to-use database. On a given day, the artist should be able to select the time slot, then choose a category and a job. All jobs are given internal numbers, and each job has only one client. But, it is helpful to list the client information on the form once the job has been selected. The artist then enters a short task description, the billing rate, and any out-of-pocket expenses. The billing rate is somewhat flexible and depends on the client, the job, the task, and the artist. For example, the company can charge higher rates for an artist’s creative work time, but lower rates for copying papers. The following form contains the basic information desired.

1. Analyze the form and create the main classes and associations needed to maintain the data for this organization.

2. Enter sample data for the Capitol Artists business. You can create random data, but remember that it has to be consistent. You might want to share data with other students so that everyone has a larger database to work with. Then create queries to provide the following business information.

1. Pick a date and an employee and list all of the tasks by that person on that date.

2. List all of the tasks performed for a specific job (e.g., Job #1173).

3. List all of the client jobs that had active tasks on a specific date.

4. Count the number of meetings held regarding one client (pick any client).

5. List the employees who have attended the most number of meetings.

6. Pick a job and compute the amount of money billed (hours * rate).

7. List the clients in order of the ones that have provided the greatest revenue (billing + expenses).

The Offshore Speed company sells parts and components for high-performance boats. Some of the customers modify the boats for racing, others simply want faster boats for informal races. The engine parts tend to be highly specialized and new variations are released each year by manufacturers. Compatibility of parts is always a major issue, but most are tested by the manufacturers with data available from their websites. Customers tend to order parts through the store, but sometimes they will buy off-the-shelf components. The store also keeps many spare parts in stock because customers tend to break them often and the profit margins are good. The store also has arrangements with other firms that can help customers redesign and upgrade interiors and cabins, for example, provide new upholstery for seats and complete systems for beds and sinks for cabins. Lately, the store has been successful in selling and installing high-end GPS and communication systems. The form below is used to place custom orders for the clients. Discounts are given to customers based on several subjective factors that will not be entered into the database.

1. Analyze the form and create the main classes and associations needed to maintain the data for this organization.

2. Enter sample data for the Offshore Speed company. You can create random data, but remember that it has to be consistent. You might want to share data with other students so that everyone has a larger database to work with. Then create queries to provide the following business information. If you have not created data that matches these questions, either add more data, or change the query to match your data. For instance, if you do not have any sales of propellers, pick a category of item that you have sold several times.

1. Pick a month and list all of the customers who purchased propellers (Category).

2. List all of the parts sold on a particular day.

3. What is the most expensive steering wheel we have sold?

4. List the manufacturers sorted by the number of parts we sell from each one.

5. List the employees to identify the best salespeople in terms of value.

6. List the brands of boat for which we sell the most oil pumps (Description).

7. For a given order, compute the total value of the order and the sales tax, assuming a 6 percent tax rate.

Laura runs a catering company that focuses on Hollywood movie studios. Her chefs prepare hors d’oeuvres, sandwiches, and other food items that are served to the cast and crew of various movies and studios. To be fresh, the food is prepared each day in the main kitchens, and meals are then assembled and displayed onsite. For some clients, the company vans deliver fresh food every few hours. To hold costs down, many of Laura’s employees are part time—only a few chefs and managers are full-time employees. Some of Laura’s clients call at the last minute, so she maintains a large list of potential workers who can perform a variety of tasks, from driving to food preparation and display, as well as cleanup. The chefs and managers evaluate workers after each job in terms of timeliness, appearance, friendliness, and the ability to take orders and accomplish tasks. Workers often perform many tasks at a given event. For instance, a driver might also be a server. But some tasks require specific certifications. Not all workers are licensed to drive, and only a few have been trained to perform some tasks such as cutting meats. Most of the employee ratings are somewhat informal at the moment, but she would like to computerize them to help her select the best workers for future jobs. At some point, she would like to offer bonuses or higher pay to workers who routinely perform well. Another challenge Laura faces is that some clients are finicky about certain types of food. In particular, some movie clients have special preferences as well as some items that cause allergic reactions. The chefs currently keep these two lists in paper folders for some major performers and actors. But to be safe, Laura wants to computerize the lists and, ultimately, the recipe ingredients. Then when a chef plans the meals, the computer could check the list of main guests and their allergies against the recipe list to identify potential problems.

Laura’s business has been established for several years. Many of her clients are old customers, and she has a couple of thousand in her files—although some have gone out of business. Her business has grown considerably based on referrals from existing clients. She gets so many good comments and referrals, she is thinking that she needs to track which customers pass her name on to others so she can call them or send thank-you gifts. But, her more immediate concern is tracking employees. Over the course of a year, she has a relatively high turnover in some positions. Other employees have been with her for years. In total, she probably deals with 400 to 500 employees a year. Employees are rated after each job, and typically employees work 15 to 20 jobs a year for her. On average, employees tend to have three tasks per event. For instance, a driver will also be a server, and possibly also a busboy or dishwasher. They are evaluated on 10 items for each task they perform, as well as given an overall rating. Client food preferences are somewhat more complex, so Laura wants the capability to add free-form comments to cover extreme cases. For common elements, such as allergies to nuts, she wants to keep itemized lists—both for desired items and forbidden items. Some clients are easy going, but this is Hollywood, so many have long lists of items—often ranging to 50 or even up to 100 items.

1. Define the final tables needed for this case.

2. Create the database.

You will need to create some additional sample data for each table. Laura knows she will want certain information on a weekly basis, but she will not be able to build complex queries to retrieve the data. You will have to build a few queries for her that can be run to display results or change prices. Some of the queries should be parameter queries so Laura can easily select the values she needs to control the results. Note: you will have to modify the queries slightly to match the data that you have entered.

1. Identify the employees who have below average overall job evaluations.

2. Identify the main menu items that have not been served to a particular director or other celebrity (pick one from your list who wants something different).

3. Which customers have not yet referred her business to other clients?

4. Create a category table to segment the employee ratings (excellent, good, average, weak). Use the table to identify the employees with excellent evaluations as both server and dishwasher.

5. Create a temporary table and copy into it information about employees who have worked as drivers but have not driven within the last month.

6. Delete from the temporary table in the previous question the drivers whose average evaluations are less than 6 (on the 10-point scale).

7. Write a parameterized query that enables Laura to increase the base wage rate of employees by specifying a category, a minimum overall average evaluation, and the percentage increase.

Brennan and Tyler are owner/managers of a consulting firm that specializes in environmental issues. In particular, the company’s scientists are experts in cleanups for chemical spills. For example, if a tanker crashes and spills chemicals on a highway, the company can quickly evaluate the potential problems and identify the best method to clean up the spill and prevent problems. The company itself does not clean up the spill, but it has contacts with several crews around the globe that it can call if local emergency workers need additional help. The primary focus of the company is to provide expert knowledge in the time of a crisis. This task requires specialized scientists, good communication systems, and in-depth training and practice. Brennan wants to improve the existing information system to maintain a database of case histories. Then, if a similar problem arises in the future, the scientists can quickly search the database and identify secondary problems to examine which solutions and ideas were successful and which ones caused more problems. Tyler has explained that at a minimum, the database has to hold the contact information for all of the scientists and emergency crews. It must also list the specialties, training, and skill levels of each person in a variety of areas. In terms of actual situations, the database should track the identities and roles of the various people and the key time frames (when reported, response time, and so on). Scientists also need the ability to list all of the chemicals involved and details about the terrain (hills, water, soil composition). More subjective data must also be captured, including comments by the onsite team and a description of the problem and secondary factors. All proposed solutions should be entered into the database, along with comments regarding their strengths and weaknesses as well as the final selections and an evaluation of the result. It is important to track potential solutions that were discarded. Even if they did not apply to the original problem, they might be useful for a future event with different circumstances.

For good or bad, Tyler and Brennan have been busy. Their firm has been averaging four to five cleanups a week. Although there are not many permanent employees (fewer than 100), they have close associations with about 200 experts in various areas. All of these people need access to the environmental documents and other information. Additionally, about 400 crews around the world are called in to work on various problems. The crews consist of 10 to 20 people. Initially, experts contribute the most information. Sometimes an expert will contribute hundreds of pages of documents and comments. Once an incident is opened, most of the new data and the searches come from the emergency crews. Time schedules, environmental factors, and comments can arrive quickly from all of the crew members. Some of the notes are on paper and saved until the emergency is over, when clerks enter the basic data to the database. A typical incident can generate dozens of pages of notes and schedules from each crew member. Although there are hundreds of possible chemicals, the firm has found that only about 50 major chemicals are typically involved in critical incidents. One important aspect of this case is the need for experts and crew members to search through documentation based on key words. For example, crews will need to search for certain chemicals, possibly in combination with other chemicals, and often include the type of problem, such as water or road spill. Brennan estimates a typical document needs to include at least 20 keywords to identify the exact purpose of the document.

1. Define the final tables needed for this case.

2. Create the database.

You will need to create some additional sample data for each table. Brennan and Tyler know that they will want certain information on a weekly basis, but they will not be able to build complex queries to retrieve the data. You will have to build a few queries for them that they can run when they want to see the results or need to change prices. Some of the queries should be parameter queries so they can easily select the values they need to control the results. Note: You will have to modify the queries slightly to match the data that you have entered.

1. List the experts who have worked with two or more crews in the same month.

2. Which experts have not contributed any documents within the last three months?

3. List the crews that are more than 25 percent larger than the average crew.

4. Create a table to categorize the expensiveness of cleanups. For example, spills that cost more than $1 million to clean up are expensive; splits that cost $500,000 to $1 million are merely costly; and so on. Create a query to apply these categories to the actual spills.

5. Write a query that retrieves documents based on a list of keywords entered by a user. The keywords might appear anywhere in the document, and the final query should sort the list based on the number of matches.

6. Write a parameterized query to update a severity value for an incident by allowing the user to enter a chemical name and a point-wise increase in severity.

7. Write a query to copy the data on experts to a new table who have participated in a total of at least three incidents in the last year.

Madison and Samantha, friends of yours, have a small business selling charms for bracelets and necklaces. They buy some of the charms they sell; others they make. So far, they have run the business as a hobby, selling primarily to friends and relatives. But they have recently established a website to display pictures and prices of some of the charms. You have agreed to build a database for them to track their inventory, customers, and sales. Any orders they receive through the website will be e-mailed, so the website does not have to be connected live to the database. The database is a relatively traditional sales system, but it is slightly complicated by the nature of the charms. Charms come in a variety of shapes, sizes, and materials. For example, customers who want a quarter-moon charm have a choice of 4 mm or 8 mm; and of silver, gold, gold plate, bronze, or painted ceramic. Charms are also offered in categories such as animals, hearts, birthdays, and so on. Additionally, the duo offers a variety of chains and pins to hold the charms. Eventually, they want to track the sales by all of these categories, so they will know which items are selling the best and which make the most profit. Costs and prices tend to fluctuate. If they purchase items in large bulk, the per-piece cost is lower, but they need to know they can sell the entire shipment. If an item sits around too long, they find that they have to significantly cut the price just to clear out the stock. Of course, gold items are more expensive, making them more difficult to sell, and they are reluctant to tie up their money in high-priced merchandise.

Samantha and Madison want you to build the database for their charms sales. They emphasized that the system has to be easy to use. They also pointed out that a key element of their business is tracking all of the products and the various suppliers, then monitoring the costs so they can set their prices accurately. They are also concerned about monitoring how quickly their charms sell. They figure they will need to start with at least 200 basic charms, but most charms come in two sizes, along with the different metals and finishes. When asked, the women indicate they are uncertain how many customers they will have but would like to get at least 50 sales a week. Although some of the sales might be small, they hope to build a solid list of clients who return for new purchases on a monthly basis. To encourage return customers, they are thinking about offering some type of frequent-buyer program, where customers receive discounts or maybe a free charm, after purchasing a specified number of charms.

1. Define the final tables needed for this case.

2. Create the database.

You will need to create some additional sample data for each table. Madison and Samantha know that they will want certain information on a weekly basis, but they will not be able to build complex queries to retrieve the data. You will have to build a few queries for them that they can run when they want to see the results or need to change prices. Some of the queries should be parameter queries so they can easily select the values they need to control the results. Note: You will have to modify the queries slightly to match the data that you have entered.

1. Which of the customers who ordered bracelets have not ordered necklaces?

2. Which customers bought more gold charms than silver ones?

3. Which categories generated the most profit over a parameterized time period?

4. Are expensive charms more profitable than mid-priced or low-priced charms? Hint: Create categories based on the prices.

5. Create a parameterized query to enable Samantha to increase prices of a certain category of charms by a given percentage.

6. Create a new table with SQL and copy into it all of the customers who have not purchased items within the last three months.

7. Delete customers from the new table in the prior exercise who have spent more than $100 in the past year.

RMN Auto Sales

Figure 1

RMN, a local used-car dealer needs a database to track the cars purchased and sold. Most cars are bought at auction, but a few are picked up from other dealers. The company pays cash at the auctions and brings the cars back to clean them up. When the cars are sold, the company generally finances the sale. Customers rarely have good credit, so the company prices the cars so that buyers cover the original purchase cost of the car with a down payment. The rest of the purchase price is financed, but even if the customer stops paying on the loan, the company has not lost any money. Consequently, a key aspect of the company’s profitability is finding cars at low prices.

The company has buyers that travel the state to attend various auctions looking for good deals. To get the best prices, the company is willing to pick up cars that other buyers do not want. In particular, the cars often need minor repairs. These repairs and costs are estimated at the time of the purchase by the buyer, and then actual costs are recorded as the work is done. The repairs generally involve simple body work to repair scratches and dents or cosmetic work on the interior. The company does not purchase cars that need major repairs. Consequently, all work is performed by the dealer at a small shop. The cost of the repair includes the materials and the labor costs—usually young workers just out of high school working for low wages to gain experience. Figure 1 shows the basic form the buyers fill out when a car is purchased. The individual problems spotted are numbered, but not in any particular order. Remember that the buyer is looking at dozens of cars at a time and needs to quickly record basic data about the car during an initial inspection before the bidding begins. The book value is the average price for the vehicle as listed in the industry guide books, but the owners never allow the buyer to pay that much. At some point, the buyers would like a portable version of the database so they can fill out this form and have it calculate totals while at the auction.

Sales of the cars are relatively traditional. A salesperson helps the customer select a car and negotiate the price. Figure 2 shows the basic form to record the sale. The employee commission is usually added to the form after the customer signs it, but

Figure 2

sometimes employees list a lower-than-normal commission to help sell the car. Because RMN finances part of the price, customers are asked to list their current and last few employers—in case the company needs to repossess the vehicle.

RMN also offers a variety of warranties as a major selling strategy. The warranties help to sell cars because people believe a warranty lowers the risk. Yet, because the cars are relatively sound to begin with, few warranty claims arise, so the company makes a solid profit on the warranties. Consequently, salespeople are encouraged to sell the warranties and receive a relatively high 25 percent commission on them. As shown in Figure 3, separate warranties are offered for various components. For instance, an exterior warranty covers the paint, while a drive-train warranty covers various engine and transmission elements. Note that each warranty only covers the listed items. Customers are given several options,

Figure 3

Figure 4

including the length of the warranty and the level of the deductible. Of course, longer warranties and lower deductibles cost more. These values are specified in the warranty policies, but the salesperson needs to enter them on this form so everyone knows exactly which warranty was purchased. The customer also has the option of paying the warranty fees upfront as a lump sum, but most decide to finance them over time in the form of monthly payments. These values are entered by the salesperson using a chart—essentially the company charges a one percent per month interest rate for financing the warranty costs.

To provide documentation for situations when complaints arise, or when RMN has to repossess a car, the company records all of the payments made by the customer. Figure 4 shows the basic form. To provide faster lookups (on the paper forms), the clerk also updates the number of late payments and the average number of days late whenever a payment is late. The company uses date of birth, gender, and taxpayer number to obtain credit reports on the customer. Really bad customers are also reported to the credit bureaus.

On average, the company sells 5-10 cars a week, and generally tries to keep no more than 50 cars on the lot at one time. However, if good deals pop up at auctions, the buyers are encouraged to purchase them, but then they hold off on buying the marginal cars.

Monster Video

Figure 1

Monster Video is a company that specializes in designing and installing home theater systems. Most of the time, these are complex systems costing thousands of dollars. Some vendors (notably Sony) are offering customized equipment that can be tailored for the specific environment—for the truly discerning consumer. With multiple speakers, large video screens, and high-power amplifiers, these systems require careful design to match the room characteristics. It often takes several days to install the systems—mostly because of upgrades to the electrical and lighting systems. Installing cable for the speakers also takes time—particularly in older houses. The company needs a database to track the customers, estimates, sales, and installations.

A key element in the success of the firm is the initial evaluation of the location by a trained salesperson. Figure 1 shows the basic elements collected during this appraisal. The salesperson records the dimensions of the room, the most likely location of the seating arrangements, and creates a small drawing to show the overall layout of the room. Later, engineers will select the best locations for the speakers. Usually, the customer already has an opinion about where to put the video screen. The salesperson records existing lighting, and adds notes about possible improvements. The number of windows plays an important role—in terms of positioning the screen and in the need for supplementary lighting. The floor and ceiling affect the acoustics, as well as limit the potential locations for speakers. Additional acoustic comments (such as “too much echo”) are given to the engineers. Sometimes, the engineers need to add acoustical tiles or even acoustical blocks to reduce or change the reflected sound.

The salesperson also identifies possible locations for speakers—modern systems require six speakers at a minimum: subwoofer, center channel, front-left, front-right, rear-left, and rear-right. The placement of the front and rear channel speakers can sometimes be difficult—particularly in modern-style houses with non-uniform walls and high ceilings. Ultimately, an engineer will narrow down

Figure 2

the final location, but due to the training, the salespeople are usually close. The distance from the speakers to the main seating position are critical, as is the maximum size allowed. For example, some people object to having large speakers at the front of the room, and in other cases, the walls may not be wide enough to hold large hidden speakers. Additional comments are used to warn the engineer about potential problems.

An engineer looks over the room and the various comments and then creates three or four proposals for the room. Generally, the options on a proposal are similar in location and installation issues, but contain different levels of hardware. For example, one proposal might use a video projector, while a second uses a flat-panel display. To the customer, the major difference between options is generally the price; but the functionality of the options is also different. If the customer truly gets bogged down on the price, the salesperson can generate additional options by swapping in less-expensive equipment for some areas (such as a less powerful amplifier). As shown in Figure 2, some options have higher installation costs. For example, a video projector requires expensive cable between the amplifier and the projector. To make this difference clear the installation cost is listed separately for each option. After consulting with the salesperson, and sometimes the engineer, the customer chooses one of the options. However, customers sometimes change components out of the options—such as a cheaper DVD player.

Installation is always a challenge and must be scheduled around the customer. First, the electrician has to install cables and outlets. Sometimes the circuits or even the main power must be upgraded. A finishing-carpenter usually works with the electrician to minimize damage to walls and woodwork. Sometimes customers have the electrician install the wiring during construction of a new house—saving time and hassle. In fact, an increasing number of jobs come through builders who offer the home theater as an (expensive) option. In these cases, the salesperson has to be careful to identify the actual customer and the separate billing client (the builder).

Figure 3

Figure 3 shows the basic worksheet filled out by the installers. Many of the installers work for Monster Video, but when the company is busy, it hires external contractors—particularly electricians. In these cases, the electricians staple their business cards to the sheet to ensure the checks get sent to the correct address.

The main worksheet itemizes the audio cables, in terms of the type of cable, the length, and which speaker it connects. Additional electrical work includes power and cables needed for the video (particularly for projectors), the amplifier, lights, and any additional circuit, main line, or breaker work needed.

Speakers are also listed on the installation sheet because they are often installed at the same time as the cables. For large-scale systems, the speakers can be expensive, so each one is listed separately. Sometimes the installation is tricky and requires additional finish work to hide the speakers. In all cases, Monster Video tracks the name of the actual installer in case there are problems, and to identify the good contractors for use on later jobs.

The amplifiers, DVD players, tuners, satellite dishes, and video screens can all be expensive in these large systems. Also, they often need additional components, such as cables and signal boosters. These items are installed and fine-tuned by technicians working for Monster Video. One employee is always in charge of this final installation and tuning to minimize problems. Figure 4 shows the selection of the final components and their total costs. Although it is not shown here, customers are given a final bill that summarizes all of the individual subtotals. Monster

Figure 4

Video does not finance the purchases, but it does accept credit cards. It also has contacts with various finance agencies for people who want to borrow the money. In most cases, Monster Video asks for a 30 percent deposit before installation starts. But, then it is willing to wait a month after the final installation to collect the total payment. This feature is appealing to some customers in case they have problems in the first few days.

As shown in Figure 5, Monster Video also offers extended warranties or service contracts on most of the equipment. For example, bulbs for video projects generally last only a year, so customers like to buy a contract where a technician comes out once a year and replaces the bulb and cleans the projector. The form is also useful in reminding the customer and the company when each warranty expires and when the overall system is no longer under any warranty. Generally, the salesperson writes down the serial numbers and other information needed to obtain warranty service from the manufacturer. If anything does go wrong, the customer simply calls the salesperson who looks up the appropriate numbers and obtains a replacement part from the vendor. This high level of service has made Monster Video popular among the wealthier clients and has led to many referrals.

Figure 5

Currently, the company has been in business for a little over three years, and has installed almost 500 systems. Although the firm has 10 full-time employees, the margins on the equipment and the service charges have enabled the firm to be profitable for the last two years. The owners are hoping to double the business in the next couple of years. Based on the number of appraisals under way, this level of sales might be possible. Depending on the economy, salespeople often generate 5-10 appraisals before a sale is made.

Big Chemical, Inc.

Big Chemical, Inc is a large company specializing in custom chemistry research and production. Its researchers often work on highly-sensitive projects for various government agencies. Even the work it does for other companies tends to be secretive because client firms do not want others learning about their new product ideas. Consequently, the company is extremely sensitive about security issues. Although they are cautious about hiring and use background checks, they still want to keep closer track of their employees. They are in the process of installing sensors on every door and gate in building on the main campus. Employees will be issued badges that are automatically read at each point. The system will be able to track each person crossing through a door. But, the company also wants the ability to check data for the last week or month, so it needs a database to hold the data and produce reports on employee activity.

Figure 1 shows the basic data collected on employees. Note that the company randomly tests employees for the presence of various drugs (including alcohol). The results of the tests and any comments are summarized. The details of the test are kept in a different system indexed by the LabTestID. On this form, the results can be one of three values: Clear, Fail, Caution. After two cautionary results or one failure, the employee is fired.

Figure 2 shows the location of the sensors. Each door will be fitted with a sensor that will send the data to the main system as a stream of bytes. This system will be able to insert rows into data tables created in the new application. Several types of sensors are used to compensate for different sizes and characteristics of doors. The company needs to keep track of the type of sensor in case one needs to be replaced. Also, most of the sensors are capable of indicating the direction the person

Employee

TaxpayerID

Internal ID

Date Hired

Department

Address Home Phone

Supervisor

City, State ZIP

Education

School

Start Date

End Date

Degree

GPA

Drug Tests

Date

Lab Used

Test Used

Lab Test ID

Results

Comments

Figure 1

Sensor List

Figure 2

traveled through the door—which is labeled “in” or “out” by the installer. Ultimately, all of the sensors will be displayed on an electronic map of the building.

Figure 3 is a basic sensor repair log. Although the sensors are fairly reliable, the company needs to track any downtime. If a sensor is not working, some queries will give misleading results. For example, it might appear that a person never leaves a room if the room has two doors and one of the sensors is broken. While this log does not completely solve the problem, at least it will make it possible for users to realize that a problem might exist.

As shown in Figure 4, employees have to be graded access to specific buildings and rooms. Data from this from is transferred to the entry control system for the building. Some rooms are not locked, but still have sensors. Other rooms are locked, and the sensors open the door only for employees who have been granted access. Access is granted by project directors who have control over certain rooms and the employees who report to them. Directors are responsible for keeping the lists up to date. To provide stronger security, directors are required to enter a starting and ending date for the access right. Beyond the ending date, employees will be denied access. In general, employees are also restricted to specific times. For example, an employee could be allowed into a research lab during normal working hours, and then locked out at night. The main gate sensor is similar to the door sensors, but it is monitor differently. Some employees are required to be off the premises after a certain time of day.

Figure 5 would never be printed in real life, because there would be too much data. It represents the sensor activation data collected by every sensor in the company. The sensors record the date and time as well as the person going through the door. The direction of travel is also noted. The sensors are relatively accurate and can even handle two people entering a door at approximately the same time. If one person enters a room and immediately turns around, it would be recorded

Sensor Repair Log

Figure 3

Figure 4

as an entrance followed by an exit—adding two rows of data. With almost 20,000 employees on the main campus, a dozen buildings with several floors of offices and labs, the sensors collect a large amount of data every day.

In general, security personnel want the ability to track the movements of employees throughout the day. In most cases, the report shown in Figure 6 is displayed only when security personnel become suspicious of an employee. The report is slightly tricky to create when rooms have several doors. Also, remember that sometimes sensors fail, so the system might not record a person entering or leaving a room. Ultimately, managers would like to see a graphical display of this data—based on an electronic map of the campus. But, for now, they will be happy with this report that shows all of the activities of a suspicious employee.

Sensor Activation

Figure 5

Figure 6

Big Teeth Reality TV

Figure 1

A new reality show is entering production. A cross between Animal Planet and Fear Factor, it focuses on conflicts between humans and animals with big teeth (crocodiles, tigers, lions, and so on). Contestants never know if they will be chased by the animals or if they will be eating them. The insurance costs will be huge, but the producers think there is a big audience. Producers always think that, but at least they are willing to pay money to find out. The next step is to find people crazy enough to sign the waivers and participate in the show.

Figure 1 shows the basic contestant application form, but this copy does not include the lengthy waivers that must be signed. The producers are adding a twist and recruiting worldwide. The goal is to build more suspense by adding communication problems among contestants and perhaps to foster some nationalistic audience participation. Contestants are asked to submit a photo, which is scanned and stored in the database. They are also encouraged to submit a short video interview. These interviews are only used for evaluating the final tier of candidates and portions might be used during a broadcast. The videos for the most likely applicants are copied to a master tape and given a location identifier. The lead producer and the director evaluate each applicant and provide a quick rating. The highestrated candidates get a second look and become a finalist in the selection process.

All applicants undergo a background check. Several reality-based shows have experienced problems when contestants were revealed to have unsavory pasts— including being arrested for violent crimes. Although the producers want risk-taking contestants, they also want to avoid embarrassing public disclosures. Figure 2 shows the basic background data that investigators obtain on the finalist contestants. It is not foolproof, but by making a few phone calls, the investigators get a reasonable idea of the candidate’s background. All finalists are interviewed on

Figure 2

camera and evaluated in terms of appearance and physical strength. The producers and directors then select the contestants for an episode.

Actual episodes consist of one or more events. Generally, there is only time for one or two events per episode, but the producers are thinking about the possibility of an hour-long special. Figure 3 shows the two main aspects of the event: the setup from the perspective of the director, and the results from the perspective of the contestants. The director focuses on the sequence of actions, where the cameras will be located, and the estimated time of each section. For each event a contestant is given one primary task. Sometimes they work in teams, so two or three people might be given the same task. The task result might just be a completion,

Event

Title

Description

Estimated time

Estimated danger

Producer

Director

Episode

Contestants

Name

Task

Result

Points

Prize

Actions

Seq.

Description

Cameras

Est. Time

Figure 3

Figure 4

or it might include a time for races. Either way, contestants are awarded points (sometimes negative points) for their role in the task. Occasionally, small prizes are awarded for completing a task, but contestants are really competing for the overall prizes in the event.

When the episode is aired, the audience is asked to participate by voting for favored contestants. The vote totals are used to determine which contestants to bring back for future episodes, and to give prizes for the overall season leader. The executive producers are trying to entice advertisers by supporting several means of voting. Consequently, they want to track the actual method used to cast a vote. Common methods include telephone, cell phone/text messaging, e-mail, and a website. They also need to track audience participation by geographic region. On the report shown in Figure 4, the regional level is global (largely by continent), but at a minimum, statistics have to be kept at the national level, and probably even down to state level in the United States.

Cerulean Bee

Figure 1

Bob Bee is an artist who has chosen to make his living designing and printing shirts and jerseys for various clubs and events. He named the company after himself and his favorite color. Although he has been in business only a couple of years, it has been successful. Several local clubs sponsor several events each year and want commemorative apparel that they either give to everyone who registers for the event or sell as a separate item. Either way, Bob makes money because he is paid for the work regardless of the number of items that actually sell.

Sometimes customers have only a vague idea of the artwork needed. Other times, they come with computer-printed designs. In most cases, an artist still has to clean up the artwork so that it will display reasonably well on the desired item. Ultimately, the art must be color-separated so that each color has its own film. The individual films are assigned to a single print color on the press, and the colors are placed in layers on the final object. Registration, or matching the color separations so the colors do not overlap and the separate layers align perfectly, is a key element in creating a professional product. As shown in Figure 1, an individual item, such as a shirt, could have art in several locations (front, back, sleeve, and so on). Each location requires a separate pass through the print process. Each color requires a separate film and a separate imprint. Although the newer print systems automatically rotate an object through several colors, each print station requires a separate setup, so customers are charged by the number of artwork elements and the number of colors. The total price of the artwork generally includes a setup fee as well as the per item charges. Some organizations are given discounts, but these are somewhat arbitrary and determined by Bob. Most customers create t-shirts associated with some event, such as a festival, race, or bike ride. The artists work the event’s theme into the designs.

Figure 2

As shown in Figure 2, all workers keep a log of the time spent on each project. The most important times are the hours spent on the artwork, but the log does include time spent running the printing press. The log is relatively simple, and employees basically fill out a time card each day. Each major customer order is given a project number, and employees enter this number into their logs. So far, the company does not have a standard method to identify the individual art items for a project, so employees tend to make up descriptions.

Figure 3

Figure 4

Once in a while customers just place orders for the artwork, but generally, they really want the art imprinted onto some item—usually a t-shirt. Figure 3 shows the main print order form. This form is also used to guide the overall project timeframe, so several dates are added to it as the order progresses through the system. The first major step is to select the apparel item. The company keeps samples of common items and colors on hand to help the customer visualize the final product. Many other items can colors can be ordered from a selection of vendors. The price of the item is an important consideration in the selection, and vendors have different prices based on fabric and size. Basic sizes are listed at a standard price, but the larger sizes carry an additional charge. The customer has to estimate the number of items desired at each size. Per unit prices also depend on the quantity ordered—with typical breakpoints of 100, 500, and 1000 unit orders. The other interesting twist is that most vendors charge higher prices for darker-colored items. The actual costs and the price Bob charges fluctuate, so they are determined by the clerks at the time of the sale. This form also contains the pricing breakdown for printing the individual artwork. The price is determined by the number of imprints and the number of colors. It is critical for clerks to enter all of the exact colors on the form.

At the end of the month, Bob develops a report that displays a cost analysis for each recent project. He uses the information to help set prices and to identify which jobs are the most profitable. Figure 4 shows the basic arrangement of the report. Bob usually looks at the net profit data first followed by the subtotal breakdown. He only looks through the detail material and labor costs in cases that seem unusual. Note that all items used in the production of a particular item are listed—including the apparel, film sets, and ink. The labor costs are summarized by task. Even though an employee might have worked on the artwork three different times over four days, there will be one entry for the artwork development. One of the important steps in this case is identifying a standard list of tasks—because employees tend to invent strange names every month, and it is difficult to put the data together to create this report.

Orange Blossom Nursery

The Orange Blossom is a local plant nursery that specializes in providing hardto-find plants to contractors and homeowners. The company also carries most of the common plants and trees, but the owners take pride in their ability to grow more unusual varieties and colors. Contractors and landscape design firms within a couple hundred miles come to the Orange Blossom for unique specimens, often used as highlights in their designs. Prices reflect the uniqueness and high quality of items offered by the firm.

Despite the company’s reputation, Orange Blossom faces the same pressures of any nursery. Weather can be a problem—particularly dry spells or hail. But, bad weather can also boost sales. Homeowners and businesses lose plants during difficult times and they come to Orange Blossom to replace them. The biggest challenge is trying to forecast the demand for new housing and landscaping. It takes time to grow many of the plants to the size requested by contractors and landscapers, so Orange Blossom managers have to plan ahead and estimate the number of plants that will be needed. If the local economy slumps, building rates fall, and the company is left with unsold plants. Fortunately, most of them can be protected and sold in future years, but the costs of growing the plants cuts into current-year profits.

As much as possible, Orange Blossom managers encourage contractors and landscapers to order plants in advance. Many large projects are designed a year or two in advance of construction, providing time for the nursery to grow or locate the larger specimens required. Of course, homeowners rarely plan ahead, so almost

Figure 1

all of the advance orders are placed by established clients. To assist the clients, Orange Blossom wants to keep many of the plan details in the computer system. Clients will be able to select a project and find the list of plants, along with the planned locations of each plant. Locations of flowers and small plants are simply specified in terms of a growing area. Trees and highlight specimens are given more detailed locations. Traditionally, landscape designers use a grid system. Each section of the landscape design will be drawn on a scale grid that matches the property. Each major tree will be displayed as a circle centered on a grid point that corresponds to a location in the ground. A few designers of large projects have started listing GPS coordinates (latitude and longitude).

Figure 1 shows the basic information collected on the order form. Large projects are often completed in stages or phases, so the form has a place for clients to record that information. Plants are ultimately identified by their species and variety using specific Latin names. However, general descriptions are provided using common names. Plants and trees have different types of measurements. Trees are generally measured by their caliper or diameter, usually in inches. Small shrubs or flowers can be measured by the container size (such as 1 or 5 gallon). Larger shrubs or even smaller trees might be measured by height, in feet.

Figure 2

Figure 3

Landscape designers and contractors purchase items for other customers. As a result, Orange Blossom maintains accounts for its major clients. Ultimately, the contractors and designers are responsible for payment, but it takes time to install a project and collect money from the end customers. Orange Blossom allows clients to identify a project when making a payment, and tracks their spending for them. Consequently, Orange Blossom allows its clients to pick up items and pay for them later. If “later” turns into months, it can be a problem. So, the accounting manager wants to track payments based on projects and be able to send billing notices to the contractors. After more than two months, the bills should include an interest charge. Additionally, expensive plants, special orders, and large orders require down payments. Designers almost always collect some money upfront from customers, so Orange Blossom wants some of the money to avoid problems with nonpayment. Some clients make payments over time—particularly as a project progresses. As shown in Figure 2, Orange Blossom sends a monthly billing statement that lists the balance at the start of the period, new purchases, and any payments received.

Orange Blossom also delivers items and will plant larger trees. Few contractors have the equipment to move or plant the big trees, so it is a popular service. To schedule deliveries efficiently, the planting manager requires clients to schedule

Figure 4

deliveries in advance. The delivery charge depends on the distance traveled. Orange Blossom drivers have the equipment to dig holes to plant the trees, but the company requires that the buyer contact the utility companies to have the location inspected and marked for underground utility lines. The installation or planting charge depends on the size of the tree and the time and labor required. Figure 3 shows the basic form that is used for scheduling deliveries. It is also completed by the driver after an item has been delivered. The Delivery Date is initially filled out by the clerk to match the requested date. It is either accepted or changed by the driver when the items are delivered. Similarly, the Delivery Charge and Planting Charge are estimated by the clerk when the request is made, and the final planting charge is computed later based on the actual planting time.

Orange Blossom Nursery purchases several items from manufacturers and large growers. The company goes through thousands of pots every year, along with tons of fertilizer and other chemicals. Most of the products are used to grow and sell the plants. A few are sold directly to clients. Additionally, some plants are grown or started at large wholesale nurseries and purchased in bulk. Some of the vendors have multiple locations, so the purchase order generally specifies which location was contacted to provide the products. Item numbers are tricky. Each vendor uses a different numbering system, and Orange Blossom employees are careful to list both the vendor’s number and the Item ID assigned by Orange Blossom.

Figure 4 shows the details of the purchase order form. Some of the key features are shown in the detail section for the items ordered. Orange Blossom employees inspect all of the items as they are received. If the quality is too low, the items are rejected and Orange Blossom will not pay for them. Also, special orders for clients are recorded on the form to help the accounting manager track unique items. The Orange Blossom OrderID is entered onto the appropriate line as a cross reference.

Round Avon Theater Troupe

The Round Avon Theater Troupe is a regional group of actors that commonly work out of one theater, but also stages plays and variety shows at several theaters in neighboring cities in a three-state area. The group’s name is a play on Shakespeare and the actors jokingly call themselves RATTs. With several dozen active actors, hundreds of donors, and close to 20 new shows a year, the troupe finds itself in need of a database application. But, like many arts organizations, it has almost no money. In fact, several commercial systems exist to handle ticket sales, but the main theater still relies on sales of paper tickets at the box office because the business manager is unwilling to spend the money for an electronic system. If it can be built for a reasonable cost and run reliably with minimal intervention, the business manager is interested in talking about a simple Web-based system to sell tickets to shows. However, the most important project at the moment is a system to track individual shows, actors, and schedules.

The troupe is organized as a not-for-profit company, but is run by a general manager and governed by a board of directors. Most of the members of the board are donors interested in theater, but it includes business people. A business manager handles the money and accounting. The company also has a marketing manager to maintain contacts with the press, help design publicity campaigns, and interact with other theaters. Individual producers and directors are hired for each show. In this organization, the producer is responsible for organizing the business details and helping with marketing the project to other theaters.

Figure 1

A show is a play (or similar event) that runs for several weeks. It generally premieres at the troupe’s home theater where it runs for a couple of weeks. Then the group takes the show on the road to other regional theaters. Sometimes they do classic Broadway shows; other times they do experimental or first-run plays. In many ways, a show is an exercise in project management. There is a scheduled opening date, actors to hire and train, sets to construct, lighting to create, and dozens of employees to coordinate. The issue of actors (and employees) is important. For long-running plays, it is critical to have backup actors for all of the characters. The Troupe often schedules multiple versions of a show using different combinations of the cast. For example, every year they do a special show near the first of July that takes place at several of the regional theaters. Different cast members are in each version of the play to reduce travel costs. For these shows, the backup for the lead characters is usually handled by actors with minor roles. If an actor falls ill, the backup fills the spot and the minor character is usually deleted for that performance.

Figure 1 shows a version of the form that producers use to track progress on a show. The actors/backups list is relatively flexible in the early days. Some directors will experiment with several different actors at the early stages. The production milestones have evolved over the years into a relatively solid list of steps followed in every production. They include casting actors, getting the sets built, establishing the lighting, producing the marketing materials, getting costumes fitted, and the major rehearsals. Although the steps remain the same, the sequence can vary depending on the complexity of the production. For instance, sometimes the set construction is finished early, but for detailed sets, it might not be finished

Figure 2

until just before the opening day. At each milestone, the director and producer evaluate the overall status, adjust timing of other events, and make suggestions for improving the process in the future.

Actors are paid by the week when they are performing. Typically each actor receives $200, but a few of the experienced actors negotiate slightly higher salaries. Many of the other jobs are staffed by unpaid volunteers who get free tickets for themselves and a couple of friends. Expenses for sets and utilities (it costs a lot to heat and cool a theater), are largely paid from donations. The general manager, office manager, and marketing manager are paid a salary that also comes from the general budget. Budgets are relatively simple and are estimated before a show is selected.

As shown in Figure 2, actual expenses are tracked weekly by the business manager. Fixed costs are assigned to productions whenever possible and include materials for sets (e.g., wood and paint), as well as estimates of utilities for a show’s rehearsal and performance. Vendor information includes the standard name, address, city, state, and ZIP Code data. Items are given categories such as utilities, costumes, music, lighting, and paint, so the manager can print summary reports. There can be several productions in progress during any week, but rarely more than five. Operating expenses are costs that are not assigned to specific productions. Manager salaries, rent, some utilities, and some contractor fees fall into this category. The decision of which costs to include in a production versus operating

Figure 3

expenses is generally left to the business manager, but sometimes directors argue and costs are reorganized.

The Troupe handles travel arrangements and pays for lodging and a basic stipend for meals for actors when the group is traveling. Depending on the distance, the Troupe might rent a bus to carry everyone. Otherwise, individual actors simply drive to the destination. Many of them car pool, but they are not reimbursed for travel costs for these shorter trips. The Troupe itself gets a percentage of the box office receipts from other theaters. This percentage is usually a fixed number that is negotiated with individual managers. However, sometimes the percentage depends on the day of the week or is tied to the attendance levels. Taking the shows on the road requires selling the theater managers on the value of the play and convincing them that enough customers will show up. Often, the statistics from the initial runs at the home theater are used to demonstrate demand for a specific play. Towards that end, the marketing manager would like to find some way to track sales, even if the list is only totals for each day. Figure 3 shows the summary report that producers use to analyze expenses and receipts for the road shows.

Figure 4 shows the summary revenue report that the business manager prints for the producers. It breaks receipts into three categories: (1) Donations specifically listed for the show, which include cash or products, (2) Revenue received from the

Figure 4

partner theaters for traveling shows; and (3) Daily receipts from the home theater. The daily receipts include estimates of the audience size based on the number of adults, students, and free tickets. These detail numbers are often provided to other theaters to help them set ticket prices and estimate comparative demand for planning purposes. Many shows also require performance royalty payments to the copyright holders based on the revenue. The fee could be a fixed number or it could be a percentage of the revenue. The details are set by the copyright holders. This report simply lists the current royalty total that needs to be paid for the listed show.

Joe’s Chops

Customizing cars is popular. Several TV shows even evolved to show how small firms can take a car and customize it with a larger engine, new suspension, fancy paint jobs, and plush interiors. In most cases, the car is stripped down to its frame, sanded, repainted, and rebuilt with new components from the ground up. Joe’s Chops is one of those small garages, led by Joe, who has a vision of creating unique rides for people with the money (and sometimes courage) who want to drive something different from everyone else. Joe’s has done well over the past few years, garnering several first-place awards at some of the regional and national competitions. The shop has built cars for several celebrities and gets considerable business through word-of-mouth. Even with only simple advertising, the company receives enough work to keep the shop busy through the year. A few customers have suggested that Joe expand or even open a second shop, but he feels it would be hard to manage and difficult to find the quality workers he needs, so he is content to stick with the single location. However, Joe routinely encounters two basic problems: (1) As his customer base move upscale, he finds they have almost no time to spend discussing options or making decisions; and (2) He hates doing paperwork, so his accountant keeps yelling at him about documenting expenses and the need to do a better job at pricing the jobs to make enough profit.

Joe needs a better way to communicate with customers. In some cases, the customer drops off the vehicle and lets Joe and his team work their magic any way they want. Other customers want more control over options (and costs). Some-

Figure 1

times Joe encounters problems getting parts or comes up with a new idea in the middle of a renovation. In these situations, he wants to be able to show the choices to the customer and let them make a decision. But, he cannot afford to wait for several days or weeks for the customer to make the trip to the shop. Also, he wants to post daily photos on a Web site so the customer can check on the progress.

Figure 1 shows the basic information that is collected when a customer wants a vehicle customized. The initial plan can be relatively general, but sometimes the employee (or customer) lists very specific engines. For example, it is relatively common to list a particular engine—in part because some cars can handle only one or two engine types. Pricing is somewhat subjective. The employee writing up the initial order writes estimates for the cost of parts and labor when they are known. All orders are eventually examined by the business manager and by Joe. Highly specialized items or hard-to-find parts are often not priced until they are located and purchased from a vendor. With considerable experience, Joe is pretty good at estimating the overall time and arriving at an estimated price. However, he has suggested that it would be helpful to have a simple report that shows the actual costs incurred for similar vehicles.

After a little persuasion, Joe has agreed that a Web site would be a convenient way for customers to check on the progress of a conversion and to answer questions or provide feedback. He is willing to get a digital camera and upload photos. Most of the employees take pride in their work and he figures this is also a good way to motivate the employees who work on the earlier stages, such as sanding and painting. For the basic site structure, each customer will have a separate page. Once the customer logs in, the page will display the most recent photo for the day. As shown in Figure 2, critical questions will also be listed on the main page. When the customer clicks the question, the details will be provided along with a simple form for the customer to answer the question or provide additional details. E-mail addresses and phone numbers of the employees will also be provided in case the customer wants to call and discuss more of the details before making a decision. To make it easy to search the questions and answers, they should be stored in the database. Depending on the final DBMS chosen, the photos will probably be stored as digital files on the server, with the link stored as a filename in the database. Observe that one of the options is a slide show that lets the customer scroll

Figure 2

Figure 3

through the photos sequentially. This slideshow should be handled by assigning a date and sequence to the photos when they are uploaded. Similarly, be sure to include dates for any answers or questions added by employees or customers.

When the vehicle rebuild is completed, the customer is given a detailed bill that itemizes the various parts. For total overhauls, this list could be lengthy. However, major subsystems are often contracted to outside partners and the bill will show only the total cost charged by the contractor. For instance, Joe rarely rebuilds engines. Instead, he prefers to buy them from a custom engine shop across town. They make most of the components and will customize them to fit specific vehicles and then bill a single price for the work. Items are usually purchased from manufacturers. Ultimately, the accountant records the cost as well as the price charged to the customer, but the cost is not listed on the billing form. Joe usually bills the labor costs at the prices that he pays the workers. He then covers his fixed costs (utilities and so on) and profit by adding an overhead charge to the bill. He figures this method is fairer and more accurate than pretending that his employees receive $150 an hour and pocketing the difference. Figure 3 shows the basic billing form. It lists parts and labor separately. This approach results in some duplication. It would be possible to display the labor needed to install a part on the same line as the part itself. However, Joe likes to keep the parts and labor values separate. Partly because it highlights the importance of the labor that is a critical factor in his shop, and partly because he likes to see the separate totals when he looks back over old projects when estimating costs of new jobs. Notice that both the parts and labor are identified by phases of the project. For complete overhauls, these phases are relatively standard, including dismantling, work on the frame, body work, painting, interior, engine, exhaust, and electrical. The phases are used as categories that he uses to compare time and cost values across projects. For instance, he wants to be able to run a query that tells him the average time spent on building interiors.

Rational Room Reservations

Most companies and universities have a variety of conference rooms and theaters that are shared by people in the organization for meetings and events. Most rooms are relatively small, with a large table and chairs for 10-20 people. These rooms are relatively standard with a small projection screen and a white board. Meetings in these rooms are usually informal and people often want to use them on short notice. A few larger rooms exist for special meetings. Theater-type rooms have formal stages with large screens, lighting, and sound systems. Some have theatertype chairs that are fixed. A few of the larger rooms are more like ballrooms with a large open floor with the ability to set up rows of chairs. The company wants a Web-based system so that employees can check to see which rooms are open and reserve rooms in advance. At first glance, this goal seems straightforward; however, it is more challenging than it appears. This case describes some of the problems and desired options. Some of the options might take too long to implement, so a key feature of the project is to determine how to design and create an initial functioning system and then explain how features can be added later.

The first step is to create an inventory of the available rooms that will be entered in the system. Initially, this task seems easy—even if there are dozens of rooms that could be reserved. Figure 1 shows the basic problem: Each room includes different items that might be needed for a meeting. Some of these items are fixed in place; others can be added if needed. The items to be added need to be provided by facilities management, and that step requires additional contacts and separate reservations. For the most part, these separate requests are granted, but sometimes conflicts arise because other people are using the equipment or equipment breaks down and is not available.

Each room has an official owner—a group or person who controls the room. Some of the larger rooms are completely controlled by the facilities management group. Others are owned by individual departments. For example, a conference room within the accounting group is controlled by that group and is generally

Figure 1

used by them. If they want to add special equipment to the room, it comes out of their budget. Consequently, they usually have priority in using and scheduling that room. Other departments have similar arrangements. Conflicts do not arise very often because small groups do not want to travel to other departments to hold a meeting unless there is some reason to include both departments.

The size of the room is listed in square feet. Where possible, the height of the room is included as well. Sometimes people want to bring in large displays and they need to know if the room has enough clearance. In cases where the height is variable, the listed height is the main display area. For instance, a theater might have a sloping floor, but the relevant height is the space available on stage itself. Capacity is the number of people allowed in the room. This number might be set by the number of seats or by the fire codes if standing room is allowed.

Each room has a list of items that are either fixed in the room or can be provided relatively easily. For example, rooms typically have chairs and tables as well as projection equipment and screens. Most of the fixed items are provided with the room. Sometimes additional items can be added such as more chairs or room dividers. For larger spaces, there is usually a charge for this service—because it costs money to pay people to move and install the equipment. The non-fixed items listed here are items that are usually available for this room. It is also possible to obtain specialty items, such as food service or special projection screens; but these are not listed as part of the room inventory because they are generic. The items in this list are those that are typically associated with a specific room.

One of the more challenging aspects of rooms is that the larger spaces can be subdivided. For example, the ballrooms have built-in dividers and multiple entry doors. By opening or closing partitions, the space can become one large room or a collection of smaller rooms. You need to decide how to handle this flexibility.

Somehow the system needs to identify people who can make reservations. In a large organization, some of this data can be provided by the internal login database. For example, many companies have a lightweight directory access protocol (LDAP) database or Kerberos facility that handles logins. If the Web server is connected to one of these systems, the application can pick up the user’s name from the login credentials. For instance, Microsoft Active Directory uses a Kerberos authentication scheme and if the Web server is connected to that system, it can use that system to identify the user. Additional user information can be added to that directory. This approach has the benefit of consolidating user information and login credentials to a single system. But, you need the running system and its connections to your server to set up and test the application. Initially, it is easier to build the system with its own list of people, which makes it easier to test the features and identify exactly what information is needed.

Figure 2 shows that the basic information need on each person consists of their name, phone, and e-mail address. For login purposes, the e-mail address can serve as the username or identifier because it must be unique. A password column is used to authenticate the user. The password should be encrypted, but that process is not critical at the start. Ultimately, the username, password, and authentication will be handled by the LDAP or Kerberos system. An important element of defining people is to identify them with a department. Remember that some rooms are owned by various departments, so you need to know which people are in each department so that you can give priority to them for rooms within their departments.

Figure 2

However, the concept of departments can be more complicated in large organizations. Departments can be hierarchical. For example, Research might be a toplevel department that has several subdivisions. If the Research department owns a building, it could control several meeting rooms; but each sub-department might control individual rooms as well. An employee is probably listed as a member of the lowest-level sub-department. So, the system needs to track the hierarchy of departments to know that someone in the Failure Testing department is also a member of the Research department.

In any system where people compete for fixed resources, conflicts can arise and you need some method to resolve these conflicts. In this case, what happens if someone wants to reserve a room that is already reserved by another person? Should first-come, first-served always be the rule? What if the president needs a specific room for a major presentation, but some group of employees reserved it weeks ago for a rehearsal space? Do you want to be the one to explain to the president that the room is not available? Not if you want to keep your job. In effect, people have priority levels. If a conflict arises, the person with the higher priority should have the ability to override a reservation made by a lower-level employee. As indicated in Figure 2, the process gets more complicated because the president is unlikely to make his or her own reservations. Instead, an administrative assistant makes the reservations for the president. But, the administrative assistant has a relatively low personal priority level. So, some executives need the ability to assign higher priority levels to proxies—people who work for them. Without proxies, the executive would have to give his or her personal login information to the assistant—which is a really bad idea if the system is going to use the company-wide login system. Because some executives might have several assistants and want to give them different priority levels, the figure contains a repeating list of proxy entries. For added security, the system might want to require a separate password for the proxy user to invoke the higher priority level.

Figure 3 shows that the basic reservation data is straightforward. Although it is not shown, each reservation should be given a unique ID. At a minimum, a reservation must specify the room, the starting and ending date and time, the date the reservation is made, and the person reserving the room. For rooms that require chargebacks or additional items, you will also need the internal account number that will be used to pay for the charges. Note that users should include any preparation and clean-up time when making the reservations. So if it takes an hour to

Figure 3

set up the room before a meeting, that extra hour should be used to move up the start time. The individual making the reservation is responsible for determining the specific time requirements, but it would be helpful if the room descriptions included some time estimates for basic tasks.

To track who actually made the reservation, the system should record the person making the reservation—who might be a proxy. And it must track who is actually going to use the room (e.g., the responsible executive). The purpose is basically a short description that can be posted online so other people know how the room is being used. For instance, even though the president has higher priority than a marketing manager, the president might choose not to override a meeting scheduled with key customers.

A person can enter a priority level for the room reservation. This priority can be anything from that person’s level or lower. For example, a top executive might reserve a room to use for rehearsing a presentation and assign a lower priority in case someone else needs the room.

You should also think about what to do if a reservation is overridden and replaced with a higher-priority event. In particular, you might want to keep the original reservation and flag it instead of deleting it. Then, if the new meeting is canceled for some reason, you could contact the original person who reserved the room and ask if it should be rescheduled.

Remember that concurrent access could be a significant problem. You do not want to allow two people to reserve the same room at the same time. Web systems typically use optimistic concurrency, but you might want to think about building some type of pessimistic locking. For instance, you might temporarily lock out a room when one person starts making a reservation for it, so that others cannot even start a reservation of that room at the same time. It would be frustrating for a user to go through all of the steps to reserve a room only to find that someone else was faster and had just reserved the room.

Pliable Plastic Production

To an economist, manufacturing seems like such a simple concept: Buy some equipment (capital), purchase a bunch of raw materials (inputs), hire some workers (labor), and make a product. Refine the process and drive down costs, make enough products and sell them at high enough prices to cover the fixed and marginal costs. Today’s manufacturing is more complex—even for relatively small shops. Flexibility is critical to most manufacturers—particularly those who focus on custom products. This company specializes in molding and fabricating plastic items—and many of the things you buy today use plastic components. Entrepreneurs who invent new products typically require many plastic components. All of these have to be designed, custom molds built, and the factory has to schedule inputs and production time on the machines. The goal of this case is to build a database that helps track the main steps of design, purchase, and production.

The first step in creating a new product is to design it. In terms of production of plastic parts, design consists of detailed diagrams of the product along with molds and various extrusion techniques. The type of plastic also needs to be specified because each type has different levels of hardness, flexibility, and strength. For a moment, think about a small plastic box with an attached lid and snap clasp. With the proper equipment, this box can be produced in one piece by extrusion—pushing plastic through a set of molds that shape and contour each element of the box. The key lies in the design and then building a prototype to test and refine the design. In a custom shop, the designs are provided by outside companies who need the product.

Figure 1 shows the basic data needed regarding a design. Typically, designs go through several versions—both during prototyping and once production starts.

Figure 1

Figure 2

The basic product information identifies the general product and the customer. Each version has a design file that specifies the details. The database will store the name of the file, but the file is stored separately on the computer system. Typically, the associated file is for the CAD/CAM production system. It contains drawings and exact specifications for the entire product. Engineers generally save the file as a new version when changes are made to the design. Engineering and production notes are stored in the separate file so they can be attached to individual components of the product. The design file also contains information to configure the production machines electronically. When the design file is loaded into the computer-aided manufacturing system, it sends configuration commands to the machines.

Each product requires the purchase of input items—primarily plastic pellets and color dyes. The quantity needed depends on the size of the product, the number of units to be produced, and the waste percentage. This company maintains inventories of some basic plastics, but generally prefers to place just-in-time orders to have vendors deliver the materials the day they are needed. Ideally, when a production job is scheduled for a specific design, the system would compute the required number of inputs and place the orders electronically with the standard suppliers. To know how much to order, the company engineers need to estimate the various input quantities for each design based on the number of units produced. This data has to be stored along with the design. The bottom section of the Figure 1 shows the basic data needed for the input, the quantity needed per 1,000 units produced, and the standard vendor for that item. The message configuration field contains the information needed to send an electronic order to that vendor. Ultimately, the order would include the exact quantity and delivery date needed.

These values could be plugged into the message as parameters.

Figure 2 shows the basic order that is generated when it is placed with a supplier. Most orders contain only a limited number of items, but each product might involve scheduling deliveries from several different vendors. When the job is scheduled, the system transmits this information to the supplier. The vendor’s electronic system returns a confirmation number which needs to be stored in case questions arise later. Each order is tied to a specific customer job and product design. Sometimes multiple jobs are scheduled on the same day and use similar

Figure 3

inputs. In these situations, a vendor might receive multiple orders. Most vendors simply add up the total amount needed for the similar items and make one shipment. When the shipment arrives, it is checked in against the multiple orders—so even though it is one shipment, it is treated as if multiple items were received. The shipping costs are then split among the multiple orders based on the percentage of product needed.

Many production companies record detailed information about each production step, such as the time, the employees involved, the quantity produced, and quality control measures from the individual machines and the inputs. It is also helpful to record any problems that arise such as broken parts and power outages. For an initial design, it is easier to start with the basic production information. Figure 3 tracks production by steps based on the individual machines. The figure shows basic data for one machine, but a production run can often use multiple machines. Note that a production can use a specific version of a product—which is not necessarily the latest version on file. Sometimes customers want to use earlier versions, so this version needs to be tracked. Each machine is run by an employee operator. The total quantity produced is recorded as well as the number of defective items that are discarded or recycled. An inspector records the overall quality and lists any specific comments or problems that were found.

The firm also needs to track the amount of each input used at each step of production. These values are processed by engineers to refine the design estimates and to track the amount of wastage. When possible, the vendor of the input is recorded to see if some inputs are more efficient than others. Input quality control examines the input resources for obvious defects and records any problems in the comments section. More specific quality control measures and testing processes are conducted separately with that data recorded in a different system. For example, chemists perform tests on samples of the inputs to determine if they meet the desired standards. Because this process is handled outside of the normal production, the results are stored in a different system.

Figure 4

Once the items are produced, they are boxed and shipped to a location specified by the customer. Often, the destination is overseas so customs forms have to be filed. These steps are handled by an international expediter. The expeditor is also responsible for ensuring delivery and handling any taxes and insurance. Once the items are picked up by the expeditor, our role is over and the customer is billed based on the negotiated prices.

Figure 4 shows the basic information collected for the shipping. Each box shipped is entered as a separate row. A box contains only one type of item. If the boxes are small, the expediter might combine them into larger boxes, but we track them at the detail level. The Order ID represents the specific order placed by the customer. The production run is an identifier that enables the company to track a product back to the day it was produced in case the customer claims there are problems. To assist in quality control tracking, individual boxes contain items only from a single production run. The quantity of items in the box is tracked along with the weight of the entire box. The weight is basically included to assist the expediter and represents the shipping weight including the box.

On the Hoof Dairy

To the casual observer, farming might seem like a simple business little changed over the centuries. But even as a casual food shopper, you must realize that food prices have remained relatively steady over many years—although demand has steadily increased through population growth. The only way prices can remain stable or even decrease is because farming has become increasingly more efficient. Management principles including information systems are important aspects to running agricultural businesses. Consider some of the basic aspects of the dairy industry. Efficiency and production are heavily dependent on individual cows. Animals exhibit a wide variance in the production of milk. When building a herd it is critical to breed the most productive cows, using selective genetics to increase the overall herd production capabilities. Other factors such as feed inputs, disease, and weather need to be tracked so that patterns affecting production can also be identified and potentially controlled.

Monitoring and caring for cows is obviously important to a dairy farm. Figure 1 shows some of the basic items that need to be tracked. The basic genetics include tracking the sire and cow (father and mother). Many cows are bred through artificial insemination so the details are generally available from the source provider.

For each cow, the company tracks the amount of milk given on each date. At the farm, milk is weighed in pounds. From this data, the firm creates reports that display average production by day and week. By tracking the births and the resulting genealogy, the company can compute the average production of the offspring of a given cow to see how production capability is passed genetically. Sometimes cows die, and other cows are sold. This information is recorded as the date along with the cause; so the managers know which cows are no longer active.

Figure 1

Figure 2

Feed costs are a major component of expenses. Some feed is grown on the farm itself, but this feed is not free because it requires land, capital, fertilizer, and labor to produce. The firm does track individual expenses for the production of feed crops, but the simpler way to measure the cost of home-grown feed is to use the market cost of equivalent feed. Economically, it is the opportunity cost because the grown products could be sold on the open market for that price. Effectively, if the farm can produce feed cheaper than the cost of purchasing it, those profits are assigned to the farm, not to the production of milk.

Figure 2 shows the basic purchase order for feed. It is similar to a purchase in any industry, except notice the addition of the protein data. Sophisticated feed analysis would track the complete nutrient levels of all inputs. For now, it is sufficient to track the protein content because protein is a critical element in the feed of any animal. Later, you can think about how to track the complete nutrient profile.

Purchases are often made in bulk—particularly for pseudo-purchases of homegrown feed. The silage is stored and fed to animals each day. It would be nice to track the precise amount of feed eaten by each cow—but that is exceedingly difficult. Instead, as shown in Figure 3, animals are grouped into herds and the daily feed for the herd is recorded. Some animals will eat more than others, but if the herd is relatively evenly balanced, the average consumption should be reasonably accurate. With multiple barns and feed stations, herds might be defined in terms of barns; otherwise herds have to be rotated in and out of the feeding and milking

Figure 3

Figure 4

stations and data recorded each time a herd is moved. One complication that is not obvious in Figure 3 is that the farmers might use multiple feed sources at any specific feeding time. For instance, they might feed 500 pounds of basic silage and 200 pounds of corn mixed into one feeding. The silage and corn come from different sources and both have to be recorded.

Basic medical data is also tracked by date in terms of treatment and any infections or diseases. Figure 4 shows the basic data tracked for a standard vet service. Note that some treatments are preventative and not necessarily a response to a problem. For example, any medications added to the feed are recorded as medication with no symptoms, and sometimes without a vet visit. Also notice that the company tracks the price or cost of treatments to help determine the ultimate value of the cow. For some treatments, the cow is weighed—because medications are sometimes based on the weight of the cow. Tracking the weight is useful to provide a basic medical and growth history, but it is time consuming to weigh animals, so this step is often skipped. The farm is thinking about installing a walk-on scale on the track to the feed troughs so cows can be weighed more often, but cost is an issue as is problem of keeping it clean. If the scale is added, the weights could be tracked in on this form with null values for treatment, disease, and cost.

Cool Wheels Shipping Company

This firm handles shipping for various companies. It has around 10 trucks and 20 – 30 drivers at any time. The business office signs contracts to pick up and deliver products on specific days. Some deliveries are short hauls, often with smaller trucks, other deliveries might be cross country. In these cases, the company books additional trips back so drivers do not have to wait and the company gets paid for the return trip. Trucks are loaded by customers—usually they are the big trailers, and drivers simply pick up and drop off the trailers. The company wants to enable customers to book trips and make it easy for truckers to check on their schedules. Ideally, the application would be built as a Web site or perhaps a mobile application. The actual schedules are entered manually by the shipping manager, so there is no need to try and compute optimal routes at the moment. But several of these administrative inputs are needed to make the system work. Managers would also like to track mileage and maintenance schedules. At some point, the managers would like to track the progress of trucks—either by automatic GPS units or by having drivers enter waypoints throughout the day. But this feature has a lower priority.

Business managers or salespeople are responsible for finding customers and keeping current data on them. Some of the customers can be large, with multiple shipping points including factories, distribution centers, or transportation hubs such as shipping ports. Other customers are smaller, and a few are one-time jobs that require a simple pick-up and drop-off; usually for large items. Figure 1 shows the basic form used to collect customer data. The main purpose of the form is to identify contacts and addresses to be used for billing and resolving any problems. The data is also useful for marketing, to help marketing managers track jobs and contact customers who have not used the company’s services in a while. Note that the locations are basic points where the customer commonly ships or receives items, and any company might have several locations.

Figure 1

When a customer wants to schedule a shipment, the shipping manager fills out a shipment request. Customers can fill out some of the information, but ultimately, the Cool Wheels business managers set the fees and mileage charges. If a customer asks for a shipment with minimal notice (such as next day), Cool Wheels might add on an express charge. The value depends on whether the company is fully booked and needs to alter other shipments or hire additional drivers. Cool Wheels has a reputation of trying to meet customer needs even on short notice, so it uses the express charge to cover its costs. Basic content descriptions are needed along with estimated weight to help select the proper truck, and sometimes it influences the routing if the cargo is particularly heavy. Eventually, locations need to be geocoded so the routing and tracking can be automated. But, Google maps can always be used to convert addresses to latitude and longitude when needed. Figure 2 shows the basic form data used for a single shipping request.

Figure 2

The HRM department keeps detailed data on drivers and other employees. But the scheduling system needs to include basic information about drivers so managers can contact them. Figure 3 shows the basic data needed. In addition to contact information, the system includes some licensing information. In some states, the licensing data can be more detailed, such as special training for large trailers (dual

Figure 3

or triple), or training for hazardous materials handling. These additional elements are lower priority at the moment. But tracking license expiration is useful because it helps the scheduler know if a driver might run into problems with a long-haul trip that might take several days. Although, the good drivers all renew their licenses well in advance of the expirations; sometimes events happen that cause delays such as illness. Some drivers also have preferences about trucks or do not want to handle long-distance loads.

Trucks are the other basic element that needs to be tracked. Figure 4 shows the basic data needed by this system. Mechanics keep additional data, and accounting managers have detailed financial data on the purchase and financing arrangements. But the basic maintenance log is helpful when scheduling trucks. If the shipping manager sees that a truck is going to be due for extensive maintenance, the truck can be scheduled for short hauls until the maintenance is performed, reducing the cost of having to handle the maintenance on the road. Simpler things such as oil changes are often handled on the road using vendors that have facilities scattered around the U.S. near major truck stops. Cool Wheels has standing contracts with some of these companies, but ultimately the shipping manager and the driver settle on a location and vendor for each stop. Trucks can be identified by the vehicle identification number (VIN) which is alphanumeric and defined by the manufacturer and guaranteed to be unique.

Figure 4

Actual shipments or trips combine the data from the previous items but the form shown in Figure 5 is a bit more complex than the others. When booking a trip weight and miles are estimated. For the actual shipment, actual data has to be recorded. Weight is obtained from weigh stations scattered around most towns. Truck mileage is a little tricky because it is measured at several points: when the truck leaves the Cool Wheels location, when it arrives at the pickup location, and arrival at the customer destination. The last measure might be the value when the truck returns to Cool Wheels base. But for long-distance hauls, the truck probably picks up another load for a different customer. In this case, the “ending’ miles would be null for the first shipment, and the starting miles for the second shipment would be the same as the delivery miles for the first shipment. Draw a map and you can see the chain. Many trips require refueling stops. Long-haul trips might have multiple stops per trip. For short-haul trips, drivers are encouraged to top-off the truck fuel when returning to the Cool Wheel base so the truck has full tanks for the next trip. Cool Wheels has contracts with a couple of the big truck-stop vendors; and these vendors provide detailed electronic receipts, but drivers are encouraged to track their own data so it can be compared to the vendor receipts.

Figure 5

e-Gadget Production

Most common companies that sell electronic devices do not handle their own manufacturing. Production of devices such as cell phones, televisions, and headsets is handled in bulk. The production facilities to crank out huge numbers of devices are equally huge. Items have to be quickly built in large quantities. If a company built its own devices, the production facilities would be small and take a long time to put out enough items to meet demand. Or they would be huge and sit empty most of the time. Instead, companies such as e-Gadget have large factories, hire a huge number of workers, and have contracts with many suppliers. The company signs contracts and uses the facilities to produce massive quantities of one product at a time. A few weeks later, it shifts to the next product, usually for a different customer. The product design is specified by the original company (such as Apple or Microsoft), but e-Gadget helps with innovative products. Because of the close ties with suppliers, e-Gadget is able to suggest uses for new items or new manufacturing techniques. Quality control is critical to any production, but particularly for electronic items that have hundreds of component parts. Quality is evaluated on incoming shipments of parts, through final-product testing, and through returns of failed items. Additional tests are often performed at intermediate steps, but these tests typically relate to the quality of the workers (or sometimes the tools).

Figure 1

Products are ultimately designed by the outside companies, but the designers collaborate with the e-Gadget production engineers to ensure that the design is feasible with existing materials and that it can be produced at a reasonable cost. Figure 1 shows the most important design information that is collected during the design phases. It contains basic information about the customer and the product. The Target Cost and Target Date are the estimated cost that will be charged to the customer for the production of one item, and the target production date. Each design goes through several revisions and the target values can change along with the detailed designs. The detailed designs for each revision are specified in different documents. Most of these documents are electronic and each one has a primary person in charge of the document. Any questions are directed to that person. The person’s time zone is important to minimize problems with calling. Each revision has a list of components and the number required. The engineers for e-Gadget find an initial source for each component and estimate their cost. These costs are not released to the company designing the product, but are used by the cost engineers to estimate the production costs.

Almost every item that is delivered to e-Gadget goes through a quality inspection process. Electronic components are particularly critical. Because they are purchased in large quantities, inspectors usually draw random samples of the elements and run them through various tests. Most electronic tests have ranges (low/ high) for acceptable elements, such as resistance values for resistors. Inspectors run the sampled items through a collection of pre-defined tests and count the number of items that pass each test. As shown in Figure 2, these results are then combined into an overall score for the shipment. The scoring method is defined by the engineers and not handled within the database, but the total value is recorded. If the score is too low, the entire shipment might be rejected. The inspector writes up any overall comments, which become important if a shipment is rejected. Accepted shipments are assigned an internal lot number and each shipment is stored separately. The lot number is unique and important. When products fail or are returned, the lot number is used to track back to the original supplier and the inspection. If too many failures are noted, then the inspection criteria are tightened, and the company probably switches suppliers.

Figure 2

Figure 3

Each product goes through several stages of production. Most of the stages are handled with a production line approach where a worker at each stage handles specific tasks. Work product from one stage is passed to the next worker at the next stage. In theory, it would be useful to collect a huge amount of data at each stage, but this data collection cannot interfere with or slow down the production line. Figure 3 shows the basic data that is collected at each stage. Most of it concentrates on the productivity of the individual worker in terms of output at each stage. Tracking the input components proved to be difficult. To avoid recording every single input item from each lot for each assembly step, the company decided to simplify production. For a given shift stage, each worker gets input items from a single lot. For example, if a worker is installed a certain capacitor, all of those capacitors for that session come from a single lot. Then the supervisor or inspector simply has to count the number of rejected or broken items at the end of the shift. And every output device for that shift is recorded as using the components from the same lot ID. The final product is given a unique serial number which is assigned sequentially at the start of the production process. For a production shift, the starting and ending serial numbers are recorded so each step relates directly to a range of products.

As a final step, the factory packages items for bulk shipping. Items are individually boxed as a normal production stage, but shipping requires the boxes to be packaged into larger boxes and bundled into packets or pallets. In most cases, the packets are loaded onto standard shipping containers which are typically trucked to a sea port and loaded with other cargo. Most of the shipping details are handled by specialized shipping companies. Sometimes the customer handles the details of the shipping but in other cases, e-Gadget needs to track the ship and receive dates as well as a tracking ID. As shown in Figure 4, each packet (typically a bundled pallet) is given a unique ID. Again, to simplify record keeping, items are loaded sequentially so it is only necessary to track the starting and ending serial numbers; instead of recording the SN of every single package. Note that a few items are often removed for testing, and these serial numbers are recorded in a different database, so there might be gaps in the serial numbers for a specific packet, but these gaps are recorded at e-Gadget. It is also important to note that a given shipment usually consists of multiple packets and that some of these packets might be shipped to different destinations. Typically, they are handled by the same shipper, but they might be diverted to different cities at some point in the shipping so they are recorded separately. Generally, the customer is billed once the items are shipped, so this data is important to the accounting managers.

Figure 4

22