A Database Design and Development Case: Laophong Nang Veteran Association Reunion System Ranida B. Harris rbharris@ius.edu Michael Cline micline@ius.ed Jeremy Cooley jwcooley@ius.edu School of Business Indiana University Southeast New Albany, IN 47150, USA Abstract Many IS educators have suggested and empirically shown the benefits of active learning methodologies. One such technique involves the use of case studies, as they enable students to think analytically and solve a real-world problem. This paper presents a case designed to be used for group projects in a database design and development course. The case is about the Laophong Nang Veteran Association Reunion System, which is adapted from a real-world problem with names being changed. The case provides students with a challenging real-world exercise in relational database design. The case provides enough information to be stimulating and demanding, but not so much that students are overwhelmed with the challenges in the case. The project can be structured as an overall project, multiple deliverables, or even singular tasks if aspects of the system are separated. Suggested deliverables include the design of entity-relationship diagrams, relational schema, normalized relational schema, data dictionary, SQL query, forms and reports, stored procedures and triggers, and system documentations. Keywords: database design, database development, normalization, entity-relationship diagram, case 1. INTRODUCTION One of the many challenges for information systems (IS) educators is to help students apply the material read in the book and discussed in the classroom to real situations. To help achieve an “active” approach to learning, many instructors utilize cases in their classes (Bonwell & Eison, 1991; Hackney, McMaster, & Harris, 2003; Scott, 2006; Thomas, 2001). This methodology is especially important for database design and development courses, as one of the best ways to learn this material is through actually solving a problem (as cases present) (Abdullat, 2001). Along similar lines, numerous research efforts have shown that employers value problem solving and analytical abilities (e.g., Cappel, 2001-2002; Hingorani & Sankar, 1995; Janicki, Kline, Gowan, & Konopaske, 2004) and the utilization of case studies allows students to develop these skills (Barnes, Christensen, & Hansen, 1994; Shaw, Woodford, & Benwell, 2007). Thus, it is important for database design and development educators to have access to a number of cases. Although many textbooks have cases at the end of chapters or at the end of the book, there is often a need to supplement courses with other cases. Additionally, many instructors like to use full-term cases that tie together multiple elements of the material covered in class. To help fill this need, this paper presents a database design and development case on the Laophong Nang Veteran Association (LNVA) Reunion System. This case is based on a real-world organization, with names and other identifying information changed. The case is challenging, and provides sufficient details and information to be stimulating and demanding, but not so much that students are overwhelmed with the challenges of the case. With that being said, instructors can change aspects of the case to make it more or less difficult depending on the skills and expectations from the class. The options for completion are variable (team vs. individual project, one deliverable vs. multiple deliverables vs. only a single assignment covering a small portion of the case) and can be catered to the desires of the instructor. Suggested deliverables, which include the design of entity-relationship diagrams, relational schema, normalized relational schema, data dictionary, SQL query, forms and reports, stored procedures and triggers, and system documentations, and solutions are provided. Upon completion of the case, students should have a better understanding of the database concepts, know how to apply them to a real-world situation, and have the ability to relate this knowledge to other situations and problems they may face in the future. 2. CASE SUMMARY Laophong Nang1 is a small district in the central region of Vietnam. During the Vietnam War, a large number of servicemen served in the Laophong Nang area. These veterans served lengthy tours of duty, and in the process formed many lasting friendships. As with many veterans who served in different wars, these men share a bond as they risked their lives for the United States and to protect each other. Since the end of the Vietnam War, veterans who served in the Laophong Nang area have wanted to get together to share memories, remember their fallen comrades, engage in fellowship, and just enjoy the company of each other. To accomplish this goal, the veterans of Laophong Nang have a veterans reunion group who help to organize reunions every two years. These reunions take place in different locations and veterans of Laophong Nang, often accompanied by their spouses and families, enjoy them immensely. However, as the years have gone by, people keep moving to different locations, some people no longer stay in personal touch, and as the activities involved in the different reunions have grown, there has been an increased need for a more sophisticated and complex database. Based on your knowledge of the organization and database design, you will need to develop an entity-relationship diagram that reflects the data requirements. Then, based on the design of the entity-relationship diagram, you will determine the relational schema for each relation. You will then normalize each relation and create a data dictionary. With the system functionalities in mind, you will create a working prototype for the database complete with forms, reports, stored procedures, and triggers. Finally, you will develop documentation for the complete system consisting of an implementation plan and user manual. 3. CASE TEXT 3.1 The current system and situation The LNVA has become the way that veterans stay in touch with each other. The LVNA is responsible for maintaining the information about veterans and their families, sending out a newsletter, and the primary activity of planning and organizing the biannual reunion. Up to this point, the data on the veterans and their families have been kept in a spreadsheet format. Each member of the LVNA had a copy of the spreadsheet and was responsible for informing one of the officers (chairman, treasurer, secretary, hospitality, chaplain, newsletter) about any changes to their information. This technique worked well at the beginning. However, as the membership grew, LVNA officers changed over time, and people got busier (e.g., work careers, children and often grandchildren, health issues), some officers forgot to notify the others about member updates and many updates were not recorded on the spreadsheet. As a result, data on the spreadsheet was often inconsistent and officers did not know which of their independent spreadsheets was the most recent. Additionally, information about previous reunions was simply kept in paper folders, making it very difficult to share with others (as the officers lived in various locations), organize, actually use, and definitely reuse - especially when it came to contact information for hotels, attractions, and souvenir vendors. Although LNVA is a not-for-profit organization, the officials also would like to have financial data from the past reunions available for management and accounting purposes. Thus, after talking to a number of people about the LNVA’s predicament, the officers have decided that they need a database system to help them keep track of their members, all reunion-related transactions, and information desired to run the association as effectively as possible. Your job is to help them analyze, design, and construct a working prototype for this database system. The system should have an easy to use interface because many officers have limited experience with computers, and even less with a complicated database software. Before contacting you, the LNVA considered a number of off-the-shelf software options, but most of them could do more than what they wanted and, as a result, costed more than what the association could afford. LNVA set aside a small budget for this project, which should include a database system, a new laptop computer, and a portable printer. 3.2. Current data processing descriptions 3.2.1 Planning the reunion city location: 1. The officers discuss the exact dates for the reunion. Typically the reunion is the same weekend in late spring, but sometimes there are extenuating reasons why this is changed. 2. The officers discuss possible locations in the United States. As many veterans usually plan their family vacations around the same time and location as the reunion, they prefer that the location has not been previously used as a reunion location for the LNVA. 3. Officers discuss reunion locations in detail, including area attractions and other things to do in the city, and select a top choice, as well as a backup location in case anything happens to the top choice. 3.2.2 Planning the reunion once the city has been chosen: 1. Planning the actual reunion location: a. Hospitality officer and chairman locate potential sites (e.g., convention hall). b. Hospitality officer contacts the sites for availability and prices. c. Hospitality officer compiles the list of options and conveys this information to the chairman and treasurer. d. Hospitality officer, chairman, and treasurer choose the location for the actual reunion. 2. Hospitality officer books the site and, if necessary, pays the deposit for the reunion. 3. Booking the hotel(s) for the reunion: a. Hospitality officer contacts hotels near (in distance to) the reunion location to inquire about availability, capacity, and pricing. b. Hospitality officer reserves a block of rooms at the hotel(s). 3.2.3 Send out a newsletter promoting the reunion: 1. The newsletter officer contacts the other officers asking for any updates to the spreadsheet concerning veteran information or other information to be included in the newsletter. 2. Officers send the newsletter officer any updates to veteran information spreadsheets and general information for the newsletter. 3. Newsletter officer works on a brief newsletter providing updates about veterans, other news, and promoting the reunion (includes information about dates, city, location of the reunion within the city, hotels, and attractions). The final draft of the newsletter is reviewed by the officers before receiving the final approval. 4. Newsletter officer compiles the updated spreadsheet of veteran contact information. 5. Newsletter officer mails out the newsletter to all members of the LNVA. 3.2.4 LNVA Treasurer Receives Registration Forms and Updates Spreadsheet with Demographic Information 1. Treasurer checks to see if the registrant has attended a LNVA reunion before. a. If they have: i. Checks for accuracy in the current spreadsheet of the name, address, phone, email address (if they have one), and squadron information for each veteran. ii. Records the following information for each additional attendee (i.e., members of the Veteran’s family): name, address, phone, email address (if they have one), Veteran’s name, and relationship (i.e., spouse, son, daughter, etc.). b. If they have not: i. Then the personal information consisting of the name, address, phone, email address (if they have one), and squadron for each veteran are added to the spreadsheet. ii. Records the following information for each additional attendee (i.e., members of the Veteran’s family): name, address, phone, email address (if they have one), Veteran’s name, and relationship (i.e., spouse, son, daughter, etc.). 3.2.5 LNVA treasurer receives registration forms and records payments 1. Treasurer checks to see if the registrant has previously attended a LNVA reunion. a. If they have: i. Then the treasurer checks the spreadsheet to make sure there are no outstanding bills. ii. Reads the pre-registration form and calculates the number of attendees for each veteran (i.e., total number of family members plus the veteran). iii. Calculates the total cost for each veteran based on the pre-registration forms (see Table 1 for the cost breakdown). iv. Verifies the payment enclosed (check) by the veteran. (1) If the enclosed payment amount is correct, then there is no need to do anything else. (2) If the enclosed payment amount is incorrect, the difference is noted. If the amount enclosed was too little, send the veteran a letter explaining the difference and the amount owed. If the amount enclosed was too much, note this in the file and inform the veteran at the reunion. v. Updates the spreadsheet with the total number of attendees for the veteran, total calculated cost, the amount of payment enclosed, the check number, and the date of the check. b. If they have not: i. The treasurer reads the pre-registration form and calculates the number of attendees for each veteran (i.e., total number of family members plus the veteran). ii. Calculates the total cost for each veteran based on the pre-registration form. iii. Verifies the payment enclosed (check) by the veteran. (1) If the enclosed payment amount is correct, then there is no need to do anything else. (2) If the enclosed payment amount is incorrect, the difference is noted. If the amount enclosed was too little, send the veteran a letter explaining the difference and the amount owed. If the amount enclosed was too much, note this in the file and inform the veteran at the reunion. iv. Updates the spreadsheet with the total number of attendees for the veteran, total cost owed, the amount of payment enclosed, the check number, and the date of the check. Table 1 Cost Breakdown for Different Events/Items: Registration fee (per Veteran family – includes one T-shirt) $60.00 Dinner (per person) $25.00 Souvenirs (each) $5.00 Additional T-Shirts (each) $10.00 Additional Polo shirt (White, embroidered) – regular sizes (S-XL) (each) $25.00 Additional Polo shirt (White, embroidered) – plus sizes 2XL-3XL (each) $27.00 Enamel Pins (each) $5.00 3.2.6 Treasurer prints and mails receipts 1. Treasurer opens up a word document and types up a receipt. 2. Three copies of the receipt are printed. a. One copy of the receipt is filed into the folder to be brought to the reunion and to be used during registration. b. One copy of the receipt is filed in a permanent folder to be used for record keeping and management purposes. c. One copy of the receipt is mailed in an envelope to the registrant. 3.2.7 Perform a final update of the veterans’ information spreadsheet 1. Approximately two weeks before the reunion, the treasurer makes sure that the spreadsheet containing the veterans’ information is up to date. 2. The treasurer emails the updated spreadsheet to all officers. 3.2.8 Confirm reunion details 1. One week before the reunion, the hospitality officer contacts the treasurer to make sure that there have been no changes to the spreadsheet since the treasurer sent it out. 2. The hospitality officer uses the final number from the most updated spreadsheet to calculate the number of pre-registrations. 3. The hospitality officer contacts the reunion location to confirm dates, times, and cost. 4. The hospitality officer contacts the hotel(s) to confirm the dates, cost, and the estimated number of veterans staying at the hotel. 5. The hospitality officer uses the updated spreadsheet to print the name tags for all who will be in attendance. 3.2.9 Process information at the reunion 1. Hospitality and treasurer officers set up a table at the front of the reunion location. 2. Veterans and their families come to the table at the reunion location to check in. 3. The treasurer asks the veteran his name, and if he has pre-registered for the reunion. a. If the veteran is pre-registered, the hospitality officer searches for the veteran’s information in the folder with all name tags and registration receipts. i. The hospitality officer then gives the veteran and his family their name tags. ii. The treasurer provides the veteran with a copy of the pre-registration receipt. iii. The treasurer asks the veteran if he would like to make any additional purchases (souvenirs or additional t-shirts, etc.) (1) If the answer is no, the hospitality officer gets any merchandise the veteran pre-registered to purchase, gives it to the veteran, and the veteran is then allowed to enter the reunion location. (2) If the answer is yes, the veteran provides the additional payment by check or cash, the hospitality officer gets the pre-registration purchases as well as the additional purchases, gives them to the veteran, records the payment and prints an additional receipt. The veteran is then allowed to enter the reunion location. b. If the veteran is not pre-registered, the treasurer provides the veteran with the form to be completed. i. The veteran completes the form and hands it to the treasurer. ii. The treasurer asks the veteran if he would like to purchase any souvenirs. iii. The treasurer then calculates the total amount owed by the veteran. iv. The veteran gives the treasurer either a check or cash to pay for the amount owed (for the reunion, dinner, t-shirts, etc.). v. The hospitality officer gives the veteran and his family blank name tags and a marker to write their names. vi. The hospitality officer gives the veteran any t-shirts and souvenirs that he has ordered, and the veteran is then allowed to enter the reunion location. 3.2.10 Immediately after the LNVA reunion 1. The treasurer calculates the total amount of income received before the reunion (i.e., pre-registration). 2. The treasurer calculates the total amount of income received at the reunion (on-site registration and sales of souvenirs). 3. The treasurer calculates the total expenses for the reunion and all related events. 4. After calculating the profit or loss for the reunion, the treasurer emails this information to the other officers. a. If there is a profit, the officers see how much it is. i. If the profit is considerable, the officers may consider price reductions for the next reunion. ii. If the profit is small, the officers will take no pricing actions for future reunions. b. If there is a loss, the officers determine how much it is. i. If the loss is considerable, the officers try to determine the reason for the loss. Additionally, there may be discussions related to raising registration costs and other prices for the next reunion. ii. If the loss is small, the officers will take no pricing actions for future reunions. 3.2.11 Election of Officers: Approximately one month after the reunion, new officers are elected for two year terms. 1. New officers get in contact with the old officers of the same position. 2. New and old officers make plans to exchange information and files. Currently, the officers’ information is kept in a spreadsheet format. The available positions are chairman, secretary, treasurer, hospitality, newsletter, and chaplain. For each of the officers, the following information has to be recorded: name, address, telephone number, and email. LNVA officers would like to have a better way to keep track of, for each two year term, people who serve as LNVA officers and their positions. This way they can tell how long each person has been an officer and in what position, recognize those who have served for long periods, and if it is time to invite new people to become officers. 3.3 Additional business rules To keep things simple for purposes of the case, assume that the LNVA only accepts checks by mail and accepts cash and checks at the reunion. No credit cards are accepted. However, your instructor may change this business rule as desired. Cancellation Policy – Cancellations up to two weeks before the reunion date will receive a full refund of the registration fee. Cancellations received in the two-week period immediately before the reunion will receive a 50% refund of the registration fee. The maximum number of family members a veteran may bring for the same registration fee is 5 (in addition to himself). If a veteran brings more than 5 people, there will be an additional registration fee cost of $12/person. 4. DESIRED SYSTEM OUTPUTS LNVA officers would like the new database to be able to generate the following reports: 1. Complete Veteran Listing: This report lists all veterans in the database and includes all the information about them. At a minimum, the report should be organized by state where each veteran resides in alphabetical order. The list of veterans in each state should be sorted by their last name, then first name. For added functionality, LNVA officers would like to be able to print ad hoc veteran listings per their request (e.g., a list for a specific state where they live or a list for a squadron where they served during the war). 2. Veteran Summary Report: This report lists all veterans in the database and includes only their names, city and state where they live, email addresses, and telephone numbers. At a minimum, the report should be organized by state where each veteran resides in alphabetical order. The list of veterans in each state should be sorted by their last name, then first name. For added functionality, LNVA officers would like to be able to print summary reports per their request (e.g., list for a specific state where they live or a list for a squadron where they served during the war). 3. Reunion Registration Report: This report includes a complete list of veterans who registered for the reunion for a given year. At a minimum, the report should include the veteran names, number of attendees for each veteran, and payment information. Information on the report should be organized by the date of registration. For added functionality, LNVA officers would like to be able to view a registration record for a specific veteran by entering only their last name. 4. Reunion Participation Report: This report includes a complete list of veterans who actually participated in the reunion for a given year. The report should include the veterans’ names, and guests and their relationships with the veteran (i.e., spouse, child, etc.). Information on the report should be organized by the veteran’s last name. 5. Inventory report: This report lists all souvenirs, t-shirts, and enamel pins available for sale. 6. Merchandise sales report: This report lists sales of all souvenirs, t-shirts, and enamel pins sold. 7. Officers Assignment Report: This report lists all officers and the veterans who hold that spot currently, as well as those who have served as officers and in what positions in the past. 5. TEACHING INFORMATION This case is designed to be used as a semester-long team project assignment in a datbase design and development class. Feedback from students was very positive as the case itself proposed an interesting situation where a database is needed. A number of unique data and report requirements combined with traditional membership and sales management systems provide a challenging case for students to create a database prototype, design user interface (forms), and practice writing queries. 5.1 Case purpose/objectives The main purpose of this case is to provide hands-on practice for students enrolled in a database design and development class. Students apply database concepts they learn to this real-life application in the following areas: * Analyzing data-related problems * Designing the database using an entity-relationship model * Constructing a working relational database prototype * Writing queries using SQL * Creating an implementation plan for the system * Generating system documentations for technical personnel * Creating a user manual for end users 5.2 Teaching suggestions The project deliverables are divided into several “milestones.” Students are required to turn in each milestone and wait for feedback from the instructor before moving on to the next step. The outline for the requirements for each milestone are provided below: * Milestone 1: Project description, entity-relationship diagram, and relational schema. The deliverable for this milestone will be a report composed of: Description of the organization, entity-relationship diagram, metadata, relational schema, and sample data. * Milestone 2: Input screen design, report layout, and implementation plan. The deliverable for this milestone will consist of a revised report from Milestone 1 (if necessary), user screen design, layout of reports the system is to generate, and description of implementation plan. * Milestone 3: The team presentation. Each project team will present their work to the class. The presentation should include both the functional and technical features of the system. * Milestone 4: Database prototype, system documentation, and user manual. Deliverables for this milestone include a copy of the prototype system, documentation for system administrators and programmers who maintain the system, and a user manual for end users who actually use the system. * Milestone 5: Peer Evaluation. At the conclusion of the project, each student will be evaluated by other members in their team. The average score from peer evaluations constitute 20% of the overall individual project grade. 5.3 Proposed Solutions The proposed relational schema for the database might include the following: Veterans (VeteranID, SquadronID, FirstName, MiddleName, LastName, Nickname, DateOfBirth, Gender, Address1, Address2, City, State, Zip, HomePhone, WorkPhone, CellPhone, Email, Rank, ServiceStartDate, ServiceEndDate, Privacy, Expired, Notes) Squadron (SquadronID, SquadronName) Guest (GuestID, VeteranID, FirstName, MiddleName, LastName, DateOfBirth, Gender, Address1, Address2, City, State, Zip, Phone, Email, Relationship) Event (EventID, EventYear, Name, Location, Address1, Address2, City, State, Zip, StartDate, EndDate, StartTime, EndTime, Notes) Registration (RegistrationID, VeteranID, EventID) Payment (PaymentID, RegistrationID, CheckNumber, Amount, Date) Order (OrderID, RegistrationID) Souvenir (SourvenirID, Description, Cost, Type) Shirt (ShirtID, Size, Type, Color, Cost) ActiveCommittee (ActiveCommitteeID, CommitteeID, VeteranID, Year) Committee (CommitteeID, Position) 6. CONCLUSION In conclusion, this paper is intended to help database design and development instructors. A thought-provoking case on the Laophong Nang Veteran Association Reunion System is presented. This case is intended to provide students with a challenging real-world exercise in relational database design. Students are able to analyze the situation and use their problem solving skills to design and develop a database for a real organization. This case, although structured and with specific learning goals, can be modified as needed to fit the instructors’ needs, teaching style, and the desired project objectives in the course. 7. REFERENCES Abdullat, A. A. (2001) “Teaching a database systems design course: Is it theory or practice?” Proceedings of ISECON 2001, Cincinnati, OH. Barnes, L. B., Chris Christensen, and A. J. Hansen (1994) Teaching and the case method, 3rd edition. Boston: Harvard Business School Press. Bonwell, Charles, C. and James A. Eison (1991) Active learning: Creating Excitement in the Classroom. Clearinghouse on Higher Education. The George Washington University, Washington, D.C. Cappel, James, J. (2001-2002) “Entry-level IS job skills: A survey of employers.” Journal of Computer Information Systems, 42(2), pp. 76-92. Hackney, R., Tom McMaster, and Al Harris (2003). “Using cases as a teaching tool in IS education.” Journal of Information Systems Education, 14(3), pp. 229-234. Hingorani, K. and C. S. Sankar (1995) “Entry level MIS jobs: Industry expectations versus academic preparation.” Journal of Computer Information Systems, Summer, pp. 18-27. Janicki, T. N., Douglas Kline, J. Art Gowan, and Robert Konopaske (2004) “Matching employer needs with IS curriculum: An exploratory study.” Information Systems Education Journal, 2(21). Scott, E. (2006) “Systems development group project: A real world experience.” Information Systems Education Journal, 4(23). Shaw, D., Brendon J. Woodford, and George L. Benwell (2007) “Educating future IS professionals through real-world integration.” International Journal of Teaching and Case Studies, 1(1-2), pp. 66-83. Thomas, Alan (2001) “Art and craft of case writing/Being good at teaching: Exploring different ways of handling the same subject in higher education.” Management Learning, 32(1), pp. 143-145. 1 Names of locations and other details have been changed, but the case is based on a real organization. ?? ?? ?? ??