Business Database Applications

In 1996, Arnold and Maria Starks opened the Fit-4-Life Fitness Center in Cocoa Beach, Florida. This business was the culmination of a dream of the Starks to open their own health and fitness business, after having worked in this industry for others for several years. As husband and wife, Arnold and Maria have thoroughly enjoyed the opportunity to work together over the last five years in building a successful family business. Fit-4-Life is somewhat different than many other businesses in the personal fitness industry in that it is not simply a gymnasium facility stocked with exercise equipment for use by paying customers. Instead, the Starkss business plan was to create a learning center that emphasized offering classes on fitness techniques and skills that could be learned by patrons from trained instructors. The fees paid by members of the center for these classes make up the largest of the three sources of revenue for the business. Another revenue source is the initial membership fee paid by center members upon joining Fit-4-Life. The other revenue source is the sale of fitness supplies in the store located in the center. This store carries both fitness clothing (e.g., work-out suits, sweat bands, etc.) and also nutritional supplements (e.g., vitamins, protein powders, etc.). As the business has grown and developed, Arnold and Maria have found that the volume of paperwork associated with running the business has also substantially increased, and has become very difficult to properly maintain. They are concerned that their business will not be able to successfully continue growing without a more timely and accurate means of maintaining the records of the business. This realization has led them to decide that the adoption of a computerbased record keeping system is in their best interests. They have invited several system development consulting firms, including yours, to visit them to discuss their business needs, and to propose an automated solution. They will review the proposed solutions from the competing consulting firms and will ultimately select one firm to develop and implement their final system. A systems analyst with your consulting firm has already conducted an initial interview with the Starks and has provided the information contained below on the Fit-4-Life Fitness Center. You and the other members of your consulting group will take this information and go forward with your plan for presenting the Starks with a system solution that you hope will win their approval. 2 In addition to Arnold and Maria Starks, Fit-4-Life employs a number of other employees, primarily as fitness course instructors. Other positions filled by employees include the clerks in the fitness store and administrative personnel (e.g., billing clerks, equipment managers, etc.). Records on each past and present employee of the company are maintained that include the employee name, address, phone number, hire date, position, and status as either a current or former employee. Employees also have a unique sequential four-digit Employee ID number assigned to them when they are originally hired. (Payroll is currently outsourced to an accounting firm. This is expected to continue and will not be a part of the system to be developed.) Members of the Fit-4-Life Fitness Center also have information recorded on them, of course. When joining the center, individuals have a unique sequential six-digit Member ID number assigned to them. Also recorded for each member is his or her name, address, phone number, gender, birth date, and date of joining Fit-4-Life (which allows anniversary marketing letters). Each member, when originally joining, decides on one of four available membership types: Platinum, Gold, Silver, and Bronze. Each of these membership types has a fixed joining fee associated with it. These fees, in descending order from Platinum to Bronze membership types are $500, $300, $100, and $10. This is a one-time fee that establishes a lifetime membership in the center. The reason for the varying joining fees is that each membership type also has an associated discount percentage associated with it. This is a percentage amount that the member will have deducted from all their future expenditures at the center, applied to both course enrollments and purchases in the center store. The specific discount percentages, in descending order from Platinum to Bronze membership types are 30%, 20%, 10%, and 0%. Thus, the joining member chooses a membership type (and its related joining fee) based on their expectation of the value of the discounts to be received from their purchases in the future. (E.g., a member expecting to take many courses and/or purchase many items from the center store would have incentive to purchase one of the more expensive membership types.) As an incentive to get new members to initially purchase the highest priced memberships possible, Fit-4-Life does not allow upgrades to the membership type once purchased. (A member wishing to purchase a higher membership type must rejoin, paying a full joining fee and being assigned a new Membership ID.) Fit-4-Life offers over thirty different personal fitness courses to its members. Courses include such exercise-based subjects as yoga, tai chi, weight training, and aerobics, and also other fitness-oriented topics such as nutrition and natural medicine. All courses are taught by Fit-4- Life instructors. Before teaching a course offering, an instructor employee of Fit-4-Life must first qualify to teach the course. Both Arnold and Maria Starks are experts in all subject matter taught at Fit-4-Life and are the judges of when an instructor is qualified to teach a course. The date that an instructor qualifies for teaching a particular course is recorded by the Starks. Each course has a unique Course No. assigned to it when it is created. The course number consists of three letters followed by three digits (e.g., AER101 for Introduction to Aerobics). Additional important information about each course is the course name, description, category (e.g., novice, intermediate, advanced), duration (the number of weeks that the course is designed to run), and fee (the price of the course, prior to any applied discounts). There are no prerequisites, as such, for taking courses, but members are encouraged to take courses in a logical progression (e.g., novice classes prior to intermediate classes in a particular subject area). Courses are offered periodically in the form of classes. (A class is a specific offering of a specific course.) Each class has a unique identifying number called a Reference No. This is a 3 five-digit sequential number (e.g., 00256) that is assigned to a course offering when it is placed on the center teaching schedule. Also recorded on the teaching schedule for each class is the start date for the class, the day(s) of the week the class will be held (e.g., M, MWF, TR, etc.), the time period that the class will be held (e.g., 8:00am-10:00am, 7:30pm-9:00pm, etc.), and the instructor. Also required is to specify which of the centers seven rooms is to be used to host the class. Each room has a unique sequential room number and a class capacity associated with it. The latter is the maximum number of members that can participate in a class being held in that room. That number is important in preventing classes from being overenrolled. Some of the rooms at the center contain pieces of exercise equipment. Each such piece of equipment has a unique serial number (provided by its manufacturer) that is used to track it in the center. Also recorded for each piece are its purchase date and the date of its last maintenance. (All equipment at the center is routinely inspected and refurbished on a routine schedule.) Each piece of equipment belongs to a specific equipment type. (E.g., the center might own five different StairMaster 5000 machines, each having a unique serial number, all considered to be a single equipment type.) Each equipment type is assigned a unique sequential three-digit identification number. Also recorded for each type are its description, the manufacturers model number, and the recommended maintenance interval for that model of equipment. Some courses require the use of a particular type of equipment, and that need must be accommodated when scheduling classes. (No course requires the use of more than one type of equipment, however.) To facilitate the maintenance of the equipment, information on the manufacturer of the equipment is maintained. (Due to warranty requirements, the maintenance of the equipment is performed exclusively by the manufacturer of the equipment.) Each equipment type is associated with a single manufacturer. Each manufacturer is referenced by an assigned unique sequential two-digit manufacturer ID number. Additional information maintained on each manufacturer is the company name, address, phone number, and fax number. When maintenance is required on a piece of equipment, a work order is generated. Each such work order is given a unique sequential five-digit number. Also recorded for each work order is the date of the order, the serial number of the piece of equipment involved, the manufacturer performing the maintenance, and (when the work is completed) the amount charged for the work. Each piece of equipment being worked on requires a separate work order. As mentioned earlier, Fit-4-Life also maintains an in-center store for the sale of merchandise to members. The items for sale in the store are each identified by a unique item ID (e.g., VIT-1342, SWB-0224, etc.). Also recorded for each item is its description, standard price (the price before any applicable member discounts), the quantity on hand (inventory amount), and the reorder point. This latter quantity is the minimum quantity of the item that should be kept on hand. If an items inventory level falls below this point, the item is noted for reorder from the vendor that supplies it. (All items have only a single source.) The vendors of the items also have information recorded about them. Each is assigned a unique sequential two-digit ID number. Also recorded is each vendors company name, address, phone number, and fax number. A supply order is placed with each vendor periodically (typically around once a week for most vendors). Each such order is assigned a unique order number by Fit-4-Life. A specific format is used for order numbers. An order number is a combination of the two-digit vendor number, and an additional sequential five-digit number (e.g., 12-12387). The date the order is placed is also recorded, as is the date that the order is received. (No backordering of merchandise is accepted 4 by the center, so there is only one date received for each order placed.) The specific item(s) ordered, the quantity ordered of each item, the quantity received of each item, and the cost charged by the vendor per unit of each item are recorded for each order. Also noted is the employee placing the order. The overall order total cost is also calculated for each order. Whenever a Fit-4-Life member comes into the store and purchases one or more items, a unique sequential seven-digit transaction number is generated (e.g., 0003456). The member making the purchase and the date of the transaction are also recorded. Of course, the specific items purchased, along with the quantity of each item, and the price per unit of each item, are also necessarily recorded. (The unit price charged for an item must reflect any discount from the items standard price to which the purchasers membership type entitles them.) The employee making the sale is also noted. The overall purchase total is also calculated for each transaction.

To do: a. Create the ERD without associative entities Using the Business Problem Description, create an Entity Relationship Diagram (ERD) to represent the data requirements of the business. Use Microsoft Visio to 1. 2. Each entity must be named and the identifier(s) (primary key) listed 3. Attributes other than the identifiers may be included b. Create the ERD with associative entities 1. Include in the ERD created in "a" above, the necessary associative entities and supply identifiers for these entities Attributes other than the identifiers may also be included in these associative entities. Clearly indicate all cardinalities and modalities on the ERD. 2. 3. One technique to try involves modeling "views" of the data. To use this technique 1. Consider each paragraph or line in the problem one at a time. Carefully analyze just the 2. Then draw the ERD for that one piece of the problem. It may be very small--that is 3. Move to the next paragraph/line and analyze it. Forget about the previous line--just 4. Continue through the problem--analyzing each piece for information and drawing the one line/paragraph for any information that should be used in the ERD OK. concentrate on this one. Draw the ERD for that paragraph/line ERD for that information. When you are finished, you should have a number of small ERD's--some may only be an entity with some attributes or maybe two entities that are related, but have no attributes specified. Combine all the little ERD's into one bigger one. If you have several ERDs that refer to Employee, for example, combine them all using one entity for Employee. Be sure to transfer all information from the little ERDs to the large one (attributes, identifiers, cardinality, etc.) 5. To do: a. Create the ERD without associative entities Using the Business Problem Description, create an Entity Relationship Diagram (ERD) to represent the data requirements of the business. Use Microsoft Visio to 1. 2. Each entity must be named and the identifier(s) (primary key) listed 3. Attributes other than the identifiers may be included b. Create the ERD with associative entities 1. Include in the ERD created in "a" above, the necessary associative entities and supply identifiers for these entities Attributes other than the identifiers may also be included in these associative entities. Clearly indicate all cardinalities and modalities on the ERD. 2. 3. One technique to try involves modeling "views" of the data. To use this technique 1. Consider each paragraph or line in the problem one at a time. Carefully analyze just the 2. Then draw the ERD for that one piece of the problem. It may be very small--that is 3. Move to the next paragraph/line and analyze it. Forget about the previous line--just 4. Continue through the problem--analyzing each piece for information and drawing the one line/paragraph for any information that should be used in the ERD OK. concentrate on this one. Draw the ERD for that paragraph/line ERD for that information. When you are finished, you should have a number of small ERD's--some may only be an entity with some attributes or maybe two entities that are related, but have no attributes specified. Combine all the little ERD's into one bigger one. If you have several ERDs that refer to Employee, for example, combine them all using one entity for Employee. Be sure to transfer all information from the little ERDs to the large one (attributes, identifiers, cardinality, etc.) 5