A First Course in Database Management Jeanne M Baugh Computer and Information Systems Department Robert Morris University Moon Township, Pennsylvania 15108 Abstract This paper describes a course that provides Computer Information System students with an introduction to Database Management in which they choose their own semester database project. The course was designed to allow the student to work on his project while learning the database theory in a concurrent manner. The organization of the course is presented, including a database written to track the progress of the students with their work. This database is available for download. The results of the student projects as well as student attitudes are discussed and recommendations for course structure and content are presented. Keywords: database first course, introductory computer course, database project, IS education 1. 1. Introduction Robert Morris University in Pittsburgh, PA currently has approximately 900 students majoring in the area of Computer Information Systems. All students are required to take one database course as part of the core for the major (SIGCSE, 2001; IS2002, 2002). Students must have completed at least 75 credit hours before enrolling in the database course. Included in these credits is some experience in a programming language. In many cases, the student will not enroll in any additional advanced database course during the remainder of their academic career. Therefore it is essential that a core area of database topics be addressed (Robbert, 2000). During the spring of 2003 the database course was offered to 50 students in the format presented in this paper. The course was designed in the manner discussed here, not only to teach database management theory and application, but also to get the students as interested as possible in their database work. The result of their work is also presented. 2. Course Content This course provided the student with an introduction to the theory and practice of database management. The students applied this technology to the solution of business and other information-related problems. Experience was provided with database design and implementation based on a thorough analysis of requirements and information modeling (Robbert, 2000). Each student modeled a problem of his choice and created a custom database to meet the problem specifications. The database was created using Microsoft Access (Springsteel, 2000). While working on the project, the students studied the following topics, most of which are included in various reports on what the Body of Knowledge for a Database course in an Information Systems Curriculum should be: (SIGCSE, 2001; IS2002, 2002). Topic 1 Database Terminology * Components and terminology used in database management systems * Types and roles of the users of a DBMS * Logical vs. physical views of the data Topic 2 Database Design * Various data problems that may arise, such as data redundancy, and data inconsistency * Various methods of design tools, especially the E-R Model * Identify major data elements, such as relationships, attributes and constraints Topic 3 Relational Database * Translate a design into a complete relational database implementation * Creation of a relational database and all of its components * Definition of attributes and their domains * Definition of keys, both primary and foreign * Creation of relationships among the data in the database implementation * Creation of reports from the database implementation Topic 4 SQL Language * Use of various SQL statements to access data in the relational database implementation, such as create, modify, insert, select, delete, update, describe, commit, rollback. SQL statements created using Oracle and/or Microsoft Access * Creation of queries using multiple tables and criteria Topic 5 Normalization * Understand the various Normal forms and how they are relevant to a good design * Test if the design is in a specific Normal form Topic 6 Database Administration * Definition of the importance and responsibilities of the database manager and administrator. * Relevance of the data dictionary. * Critical elements for implementing and maintaining a DBMS. * Security issues in the implementation of a database * Documentation for the relational Database implementation – User’s Guide and Technical Reference Manual 3. Course Organization “Students need to be introduced to all the components of the database system and the role each plays.” (Springsteel, 2000) This course was designed to allow the student to work on an individual database project while learning the database theory in a concurrent manner. “New technologies bring new challenges that make computing exciting (and sometimes frustrating)” (Cheatham, 2000). The design of the database is the key to a successful database implementation (Riccardi, 2003). C.J. Date, in his book, An Introduction to Database Systems says “I believe very firmly that we must get the foundation right, and understand it properly, before we try to build on that foundation in any way” (Date 2000). The design of the user’s specifications is the foundation. Often it is just this area of study that gives the beginning database student the most problems. Giving the student a project for implementation, that had already been designed correctly would have been a challenge, but would not have allowed independent design work on the part of the student. First the student must thoroughly understand the user’s requirements. Many examples of user specifications and the related designs were presented to the student during the first few weeks of the course. The Entity Relationship model (ER) was used as the design tool (Kroenke, 1999). With the design examples, the student was also given the Microsoft Access database matching the ER design. An example of user specifications and the related ER diagram is provided in Figure 1. A sample screen of the related Access database is provided in Figure 2. At the beginning of the course, students reported that their Access skills were excellent. But it became clear that they were not as proficient with the software as they professed to be (Veal 2001). The students were expected to already have a working knowledge of Access, but review was necessary concerning even table, form and query creation. Seeing the results of a design (the physical database) at an early stage in the course produced some excitement on the part of the students, towards the individual database project they were to design and Figure 1. Salon Database specifications and ER chart implement. They were first asked to have a thorough understanding of the enterprise they were modeling. Emphasis was placed on what the database was to do. At this point, the student was to make no mention as to how it was to be done. They were encouraged to seek out a real project for a real user. They were told from the very first day of the course that they would have to come up with a topic for their project. They were to begin investigating ideas and opportunities right from that first day. A summary of the first assignment given to the students for their database project is as follows: Database Project Proposal Student Assignment: You are to provide a written summary of the database you will model this semester. In your summary please provide as much detail as possible about what you are modeling. You are not to talk about the tables you are thinking of creating. Instead, you are to give an overview of what your database will Figure 2. Access Screen for Appointment information for Salon Database be about. To help in this process, ask yourself the following questions: * What is the central focus of the database? * Why was this topic selected? * What kinds of things will this database be able to do? * What kinds of information will the database provide for the user? * Please indicate if this is a real project you are creating for your use or for another’s use. Before the student could proceed, his idea was approved. This involved an assessment of the details that would be needed in the implementation. The guidelines that were used in approval were as follows: * Was the idea a valid one? (a subjective judgment on the part of the instructor) * Was the problem something the student could reasonably finish in the time allotted? * Would the design provide approximately 10 to 15 tables once implemented? * Were there sufficient relationships among the data? (one to many, many to many) * Were there sufficient attributes to be defined? Seventy–seven percent of the students wrote proposals for real projects which ranged from a database to store cattle information for the family ranch to a patient database for a small hospital in South Africa. The projects students selected are listed in Table 1. (Note – three students dropped the course during the first few weeks of the semester.) The next step in the process was to create the Entity Relationship Diagram according to the approved proposal. A great deal of time was spent in individual help on this phase of the project. Establishing the organization's information requirements in a short period of time was accomplished by the individual student help sessions. It was necessary for the instructor to thoroughly understand what each student’s project was to do. Each student was guided in the specific questions he was to ask his user. At this point in the student’s database study, Accounts Payable/ cases/ invoices etc… Automotive parts supply Co. Baseball stats/ pitchers Beauty Salon/ customers/ employees/ appointments Cattle Farm/ breeding/Vet info… Comic Book Collection (2 different student versions) Computer Inventory and Maintenance Cultural Trust / employee scheduling Customer information (2 different student versions) Ebay family sales information Evaluation of Engineering Courses Family Pictures Hardware and Software inventory tracking Heating and Air Conditioning Business Hospital Information/patients/diagnosis (2 different student versions) Hotel Information/ Room Maintenance / employees Investments/clients Job Description Database Library information/ books/ patrons Liquor and beer inventory control for Restaurant Marine Personnel Information Mental Health Benefits for employees Music Store Inventory and purchasing Newspaper Route / customers/ payments/ Online CD shopping Phi Beta Lambda Student Business Organization Restaurant Employee Management (2 different student versions) RMU Track practice and Meet Information Role Playing Game (2 different student versions) Sales history/perspectives information Scanner Repair and Maintenance Science Center Projects/ supplies/ locations/students Security company scheduling Sports Cards Collection Transcription Service Business Travel Agency Video Store Web design company Women's Volleyball Team Stats. Table 1. Database Projects he did not know what he needed to ask. The process of defining the scope of the project enforced many of the concepts of database theory, including relationships, attribute definition, key values and user requirements. This essential step made the students take an in depth look at what the database was to do and how that would be accomplished with their design (Harrington, 2002). It was important that the student had a “clean” design before he proceeded with the physical implementation. The student then created the physical database from the logical design. It must be noted that as the course went along, in many cases, the designs changed as the students actually created the related databases. The process of refining the design mirrors what happens in the real world when creating a database, as it is a dynamic process (Riccardi, 2003). As the students worked through the user specifications and translated the design into the physical database, they often found that they needed to re-do their tables or create additional tables. Access was used because it is relatively quick and easy to create a professional looking database, which gave the student a sense of achievement (Springsteel, 2000). But using Access proved to be challenging to many students. The designs were somewhat complicated and included many tasks, such as sub forms within sub forms, multiple criteria and complicated queries. (Reports were not assigned at this phase.) Many Access tips were demonstrated in class to help with this process. This assignment for this phase of the project is summarized as follows: Phase I Requirements Your database project must meet the following specifications: * All tables are to be defined (keys, constrains, etc…) * All input forms for the tables are to be completed. * At least one sub form must be present * All forms are to have the same “professional look” * All forms are to have a header area * A main form must be present to link all other forms * Descriptive names are to be used for all field names on the forms * Two types of forms are to be represented (column and tabular) * Buttons must be present on all forms to control input (add, delete, save, etc..) * At least three pull down lists must be present * All forms are to have close and search buttons * Relationship screen must be completed * Enter a few records in each table The Phase I assignment was given at week 5 in the 15 week semester. The students were given 4 weeks to complete this phase. This phase of the project was challenging not only to the students, but also to the instructor. The students were all implementing different designs and each needed individual attention. Because most of the students were implementing projects for real users, it was necessary to closely monitor the progress so the database would “do” what the user required. Once the students turned in phase I of their project, a detailed evaluation was completed. Approximately 20 minutes of time per student was needed to evaluate each student’s database, which included both problems with the physical design matching the logical design as well as problems with the mechanics of Access. During this evaluation process, each student was also assigned three custom reports that were to be created during the next phase of the project. These reports were individualized for each student’s database and included queries from multiple tables as well as user prompted criteria. (The students also defined other reports of their own choice and design.) Many of the students made appointments for help (some were required to do this on their evaluation report). This required additional office hours set up in the week immediately following the date the students received their Phase I evaluation. The students signed up for help in half hour time slots. Although this was time consuming, it also was necessary. The issues to be discussed about each student’s project were so different than it would have been impossible to cover everything in class. This individual help gave the students a sense that they were not alone in trying to complete their work or fix their database problems. Often a student wanted Access to perform specific functionalities that other students did not need. A sample database was written containing many Access “tricks.” Time was made in class to demonstrate most of these features and the database was made available to the students for their reference. The following Access features are among those that gave the students the most problems: * Sub-forms linked to multiple fields on the master form (same problem with sub reports) * Update queries * Macros * Combo boxes * Field indexing * Expressions * Sub-query used as a criteria * Referring to values on other forms in queries, on reports, etc. In Phase II of the project, the student were required to address all problems or concerns found in the evaluation of the first phase as In order to keep track of evaluations presented to the student, a database was designed and written to store this information. Each student was provided a detailed report as to the status of his database project. The student may or may not have been required to make an appointment with the instructor to go over the problems and/or concerns. Figure 3 shows a sample screen from this evaluation database and Figure 4 shows a sample of the Phase I, Individualized Report generated for the student. This database made the evaluation process easier to implement. It also gave each student a structured personalized report detailing their progress. The task of tracking the progress of each student was feasible because of this database. well as new functionality. The Phase II assignment is summarized as follows: Phase II Requirements * Correct all problems found during Phase I (please note that not all problems may have been typed on the report. If you are required to make an appointment as listed on the report, additional problems may also be discussed at that time) * Create at least 8 reports (three of these have been defined on your Phase I report) * Of the eight reports above, you must make sure that you include at least one sub report * Add functions to at least two of your reports (sum, count etc…) * Create a form with buttons on it for all of your reports Figure 4. Sample of Individualized Phase I Student Report * Create at least one update query that will be run from a button on a form. * Create at least one delete query that will be run from a button on a form. * Create a macro that will be run from a button on a form * Create a pop up form showing information of your choice * Define a form that automatically opens when your database starts * Add an option box to one of your forms * Make sure that at least one of your forms comes from a query (will show data that is not being used to fill in a table – examples will be shown in class) * Create a query screen containing data in a tabular format that allows the user to do custom searches (this filtering process will be discussed in class) Create a button to access this screen The students were also required to document the database, including detailed ER design, Access relationships, report and query explanation, data dictionary, user specifications and table documentation. All items, along with an electronic copy of the database were placed in a sectional binder for final evaluation. 4. Student Results This course was not only designed to teach database theory, but also to help the students become excited and interested about the topic. Both tasks were accomplished. The final course grades support the fact that the students learned the database topics. The final grade was calculated using two exams, a comprehensive final, the project and various other assignments. The Final Grading was as follows: Grade Number of Students A 20 B 13 C 10 F 4 A student survey administered during the last week of the course supports the enthusiasm the course generated. The survey was completed by 40 of the 47 students. A summary of the survey questions and their student responses is listed in table 2. In all instances, the students answered in a positive manner concerning the course. A real world project allowed the students to “learn better through a particular domain of their interest” and “see the practical value of what they learned” (Robbert 2000). It is interesting to note that 90% of the students received individual help on the project, which supports the fact that individual time with each student is necessary if teaching introductory database management in this manner. Even though 98% of the students said that the project assisted them in learning the topic, there was no parallel course run in a format where a pre-designed project was assigned. This could be a topic for further research. Seventy-five percent of the students stated that they added additional functionality beyond what was required and 80% of them stated that they would take an advanced database course if given the opportunity. This supports the interest they had towards the project. There is existing research that supports the theory that one course is not enough for the computer student to adequately understand database topics (Urban, 2001; Seyed-Abbassi, 2002). Should the University require more than one database course of the Information Science Majors? This is an area beyond the scope of this paper, but should be researched further. Although students in the Information Systems curriculum at Robert Morris University are required to take courses in which Access is used and taught, only 36% stated that their level of Access knowledge was a 2 on a 1 to 5 scale. This self assessment of poor beginning Access skills, led to the amount of time that was spend in helping the students with Access related problems. This points to another issue. What are the Access skills currently being taught in related, prerequisite courses? This bears investigation, but also beyond the scope of this paper. The students were also asked to write comments concerning the database project or the course in general on the back of the survey form. There were only six negative comments, which all related to students wanting more lab time with Access and/or Oracle. Two examples of these comments are as follows: * “The only thing I didn’t like was how we didn’t have a lot of lab time” * “I would have liked more time with Oracle or any software outside of Access” The following statements (each from a different student) are examples of all of the other student comments: * “I think this may have helped me decide what career path I want to follow” * “We should be required to take an advanced Database course” * “This class is one of the most challenging I had. I am glad to have taken it” * “This project was a real challenge for me. I would definitely take an advanced course in database if offered” * “The database project was a great learning experience. I would love to take an advanced DB class” * “The project was a perfect learning tool” * “I’m really glad I got the opportunity to do a real project. I can show this project to potential employers and include this project in my portfolio” * “I hope I can get a job doing something with databases” * “Choosing the topic for our database individually made me very interested in learning the material” * “I am so glad I had the opportunity to take this class” * “I liked being able to work on something I was interested in. A pre-designed project would have been easier, but I would not have learned as much” * “I liked the fact that we had a project that was basically a semester long. As we learned new things in class- we could use them in our project.” 5. Conclusions and Recommendations The positive comments and the results of the survey were extremely meaningful to the instructor. For the most part, the projects were exceptionally well done. Because of the increased personal contact this course provided, a genuine bond was felt with many of the students. This was a result that was not anticipated, but much welcomed. The knowledge and interest level of the students in database design and implementation can be increased by an individually designed project. Students spent more time creating additional functionality beyond what was required. They were eager to learn new topics and techniques. They were excited to take their project idea from scratch and see it develop into a fully functional database. From a teachers perspective it was extremely rewarding to see the student’s confidence in his technical skills grow, through the undertaking of the project. This made all of the time required as an Instructor for this course worthwhile. Here are a few recommendations and concerns for a course of this type: * Teaching a first database course with individual projects is time consuming. Because the students were all doing different projects, it was important for them to feel that they could ask for help when they needed it. Often what one student needed Access to do for his database was different than what his fellow student needed Access to do. Extra office hours were added during the semester. Also, to be fair to all of the students needing help, a sign up sheet was placed on the office door and students could only sign up for a specific amount of time during the week, thus giving all a fair share of the available time. It also helped to be able to answer some of their questions over the phone in the evenings. * Structure how much time will be spent in class in the demonstration of Access features. Because students were excited about getting Access to do what their design required, they had a great deal of “how to” questions in class. Writing a sample database containing Access tricks, helped with this process. The student could see examples of the things shown in class. But, as the semester went on, the students wanted to learn more and more about Access. This led to time taken away from theory topics. Because of this, a careful account of time during the semester must be kept, so that some of the theory topics don’t need to be “crammed” into the last few weeks. For example, I don’t feel that enough time was spent on Normal forms, a topic I like to cover at the end of the course. A solution for this could be to set aside time for Access topics, on specific days of the week or for a specific period of time during the class period. Please indicate your experience with database work before you took this course No Experience 20% Very Minor Experience 55% Moderate Experience 25% Advanced Experience 0% On a scale of 1 to 5, with 1 being the lowest and 5 being the highest, please indicate your knowledge level of Access before you took this course 1 31% 2 36% 3 26% 4 2% 5 5% Compared to other course you have taken, how much time did you spend on this course? Less than other courses 5% About the same as other courses 20% Considerably more that other courses 75% How interested in the project were you? Not very Interested 2% Somewhat Interested 38% Very Interested 60% Did you spend more time working on the project (adding functionality, appearance etc…) beyond what was required in the project specifications? Yes 75% No 25% Did you receive individual help at any time from the instructor on this project? Yes 90% No 10% Do you feel that the project assisted in your learning of the database topics? Yes 98% No 2% Would you rather have been given a pre-designed database project to implement? Yes 25% No 75% Considering what your knowledge level of database work was at the beginning of the course, how do you feel you’re knowledge level on that topic has changed? No Change 2% Somewhat Increased 33% Considerably Increased 65% On a scale of 1 to 5, with 5 being the highest, please indicate your knowledge level of Access at the end of the course? 1 0% 2 2% 3 35% 4 47% 5 16% If given the opportunity, would you take an advanced database course? Yes 80% No 20% Would you recommend this course to others? Yes 100% No 0% Table 2. Database Final Student Survey * Be careful to limit the scope of each student’s project. The student may not realize that the project he wishes to do may be too much for someone new to database work. Perhaps deleting some of the functionality he may wish to include will be necessary. By the end of the semester, he should be able to incorporate additional functionality on his own. A couple of the student projects were so complicated that if it were to be done over, some of their desired functionality would have been excluded from the class project. For example, there was one student who did an Accounts Payable and Case tracking system for a Law firm. She initially wanted to just keep track of the cases for which each employee (lawyer) was responsible, along with the fees for those cases. This would have been a very suitable project. But then she also wanted to add the ability to track perspective clients and payments to vendors that were connected to a specific case. She was one of the many students very excited about getting all of it to work, and wanted to keep doing more and more. But this added additional time outside of class to help her figure out how to make Access handle her additional features. It is a judgment on the part of the instructor as to what is “enough” or “too much” in the design. A good approach would be to guide the student into doing the functionality of the database in sections as time permits during the course. With the above student, if I were to do it over again, I would council her to add the perspective client and vendor payment information after everything else was complete, including all documentation. * A detailed evaluation of the first phase is necessary in order for the student to complete the second phase. Each database was opened and all functionality was checked as it related to the student’s design. The evaluation database accomplished the task of organizing this information. As stated earlier in this paper, the database produces an individual, detailed report per student, listing problems, concerns and required reports, as well as grading with comments. This evaluation process was very time consuming, but the reports presented to the student not only detailed their work, it was a valuable example of an actual database application. The students were, for the most part, appreciative of the time that was spent in grading their work. I feel that giving them a professional detailed evaluation, showed them respect for the amount of time and effort they were giving to the course. A copy of this evaluation database can be acquired free of charge for personal use by emailing baugh@rmu.edu. * The students will need fairly good Microsoft Access skills to complete the project. Many of them came into the course stating that they knew Access or had Access in a previous course. What was observed was that most of them had a very minimal working knowledge of Access. Sometimes getting Access to “do” what the students wanted it to do was challenging. Although everything needed for Access was demonstrated to them in class, a number of students needed additional Access help. As stated earlier, these are upper level students, having completed 75 credits and they were expected to figure some things out on their own. (They were given help if they ran up against a brick wall) But the topics were not “spoon fed” which is what some would have liked. * A high speed Internet connection is a must for this course. This allows help to be given to students online. As those teaching computer courses know, one can not always diagnose a problem unless it is analyzed while the program is running. The ability to quickly download the database as an attachment from the student’s email is essential. * A solid design is the heart of the student’s database project. A great deal of care must be taken to ensure that each student’s design is correct before the physical implementation is begun. Meeting individually with each student about his design is necessary and adds to the time required for this course. Any professor teaching this course is basically designing a different database for each student. If one is willing to commit the time required, the payoff in student personal growth is an enormous compensation. * Finally, topics covered in a first database course should be monitored and updated as needed, in an effort to keep students current. An investigation of the need for an advanced database course in an undergraduate Information Systems Curriculum should also be done. Because technology areas such as web-based applications are being increasingly used by organizations to support their work, students will also need these skills to compete in the job market (Seyed-Abbassi 2002). Competency can be defined as “The ability to perform in the workplace” (Goldsworthy 1993). Keeping Information Students current with Database Technology should be a priority for those teaching Database Management (Barker 1994). 6. References Barker, Donald I. (1994). A Technological Revolution in Higher Education, Journal of Educational Technology Systems, Volume 23, Number 2. Cheatham, Thomas J. (2000). “A Web-Based Lab Manual for CS 1: An Experiment,” SIGCSE Technical Symposium on Computer Science Education, March, pp 105-106. Date, C.J. (2000). “An Introduction to Database Systems,” 7th ed., Addison Wesley. Goldsworthy, A.W. (1993). “IT and the Competency Debate- Skills vs. Knowledge A Major Issue,” The Australian Computer Journal, 25(3):p. 113-122 education, Phoenix, Arkansas. Harrington, Jan L. (2002). “Relational Database Design Clearly Explained,” 2nd ed, Morgan Kaufmann. IS2002, Model Curriculum and Guidelines for Undergraduate Degree Programs in Information Systems (2002). Curriculum effort of Association for Computing Machinery (ACM), Association for Information Systems (AIS), Association of Information Technology Professionals (AITP). Kroenke, D. (1999). “Database Processing” 7th ed., Prentice Hall. Moore, Thomas (2002). “Bringing The Enterprise into a Database Systems Course,” SIGCSE Bulletin Proceedings of 33st SIGCSE Technical Symposium on Computer Science Education Riccardi, Greg (2003). “Database Management with Web site Development Applications,” Addison Wesley. Robbert, Mary Ann, Ming Wang, Mario Guimaraes, and Martha Myers (2000). "The Database Course: What Must Be Taught," SIGCSE Bulletin Proceedings of 31st SIGCSE Technical Symposium on Computer Science Education, March, pp. 403-404. Seyed-Abbassi, Behrooz (2002). “The Evolution of an Advanced Database Course in an Information Systems Curriculum” ISECON 2002 19th Annual Conference on Information Systems Education. SIGCSE 2001, CS Body of Knowledge- Information Management Computing Curricula 2001. Springsteel, Frederick, Mary Ann Robbert, and Catherine Riccardo (2000). “The Next Decade of The Database Course: Three Decades Speak to the Next” SIGCSE Technical Symposium on Computer Science Education, March, pp.41-45. Urban Susan D., Dietrich W. Suzanne (2001). “Advanced Database Concepts for Undergraduates: Experience with Teaching a Second Course” SIGCSE Technical Symposium on Computer Science Education, February, Volume 33 Issue 1 pp357-361. Veal, D., S.P. Maj, and Rick Duley (2001). Assessing “hands on” skills on CS1 computer & network technology units, Technical Symposium on Computer Science Education Proceedings of the thirty second SIGCSE technical symposium on Computer Science Education.