The Evolution of an Advanced Database Course in an Information Systems Curriculum Behrooz Seyed-Abbassi Department of Computer and Information Sciences, University of North Florida Jacksonville, Florida 32224, USA Abstract With more areas in the field of database achieving capabilities for practical utilization in the business environment, it is becoming more and more imperative for academic database courses to re-evaluate and consider changes in the course content as well as teaching methodologies. The significant increase in the number of topics that could be covered in a database course requires considerations for shifting topics in the introductory database course and for adding an advanced database course to the curriculum. In response to the need to provide undergraduate students with an opportunity to learn more about current technology areas, such as data warehousing, data mining, and web databases, a second database course was recently added to the Information Systems curriculum at the University of North Florida. However, teaching advanced topics in a way that incorporates practical experiences with theoretical concepts presents a unique challenge for database instructors. This paper describes the experiences encountered with the addition of a second database course and the course content presented to undergraduate Information Systems students. Keywords: IS education, advance database course, emerging technology, topic ranking, assignments 1. INTRODUCTION Over the last decade, the scope of database technology has course, Database Systems, has been similar to courses dramatically expanded with the migration of many legacy offered at other educational institutions (Harder 2001). systems to relational systems which has subsequently After a brief review of database history, access methods generated an explosion of innovative developments in and data structures, the course covered basic database information technology. Leading-edge technology areas, foundation topics, such as database architecture, entity- such as data warehousing, web-based applications, object relationship, relational model, normalization, relational oriented databases, distributed databases, and front end algebra, SQL, design methodology, application interfaces tools are being increasingly utilized by organizations to with an emphasis on relational database management manage the large volume of generated data and systems. Advanced database topics were rarely or information (Robbert 2000). As a result, database systems minimally covered due to time constraints. However, as are rapidly becoming the underpinning that supports many these advanced areas become more prevalent, it is areas in computing technology necessitating that many essential for students to be exposed to concepts beyond the jobs in today's businesses require more than a basic ones typically taught in a traditional, one-semester background in relational database (Springsteel 2000) database course (Springsteel 2000). (Urban 2001). The need to adapt the database curriculum to provide valuable educational experiences in these areas In an endeavor to fulfill the needs of local businesses for is increasingly crucial for Information Systems (IS) workers with current technological skills and requests graduates to be both successful and productive in the work from students for further database instruction, Database environment. Systems II (DBII) was developed in 1999. It is geared for IS students desiring to extend the basic knowledge learned Until recently, the IS Program in the Department of in the introductory course into more advanced database Computer and Information Sciences (CIS) at the areas. Since the addition of DBII, it has become a popular University of North Florida offered one required database major elective providing students with a broader course which followed completion of prerequisite courses foundation of database knowledge to utilize in their in file structures and data structures. The content of the current and future computing careers. This paper describes the experiences and challenges encountered in spring semester. The overlap figures have been affected the development of the advanced database course and the by the gradual addition of new technology topics, as well gradual integration of emerging technology since its as students entering DBII after taking the introductory inception. database course with different instructors. To help synchronize the review component, another questionnaire 2. DEVELOPMENT OF DATABASE SYSTEMS II has been initiated at the beginning of the semester requesting students to rank their level of knowledge (not at In the Database Systems II course, the instructional goals all, some, average, very good, excellent) in several for the students are to learn about current areas in database topics (such as SQL, entity relationship traditional and emerging database technologies by modeling, normalization...). This information is used to studying the methodologies and issues in the design and minimize overlapping materials and at the same time, implementation of such areas as relational, data include the review material needed to ensure that the warehouse, web databases, and object oriented databases. students are at a similar level of knowledge. An essential part of the course is to balance the concepts with practical experiences in the utilization of software Another question requests the students to consider a list of applications for database design, development, topics related to database and provide a ranking of implementation, and interfaces for optimized query importance for being included in the Database Systems II processing and retrieval of information. The process to course. The ranking is done on a scale from 1-5 with 5 achieve this balance has presented a variety of challenges. being the most beneficial. The topics include Interactive For this instructor, the primary challenge has been SQL, PL/SQL, embedded SQL (with COBOL, C, and learning and keeping up with current and emerging Java), MS Access, basic database administration technologies in a constantly changing field as evidenced responsibilities, design and modeling, object oriented by frequent textbook and software revisions. This coupled design, web database design, software (ColdFusion, with time constraints and academic responsibilities have WebDB, HTML, PHP) for web design, and data made the preparation of pertinent lectures, software warehouse. Since many of the students at the University demonstrations, and valuable assignments to support the of North Florida currently work either full time or part theoretical concepts with hands-on experience a daunting time in technology related areas, they have a good task. understanding of the skills that are currently needed by local businesses. The results of the rankings since 1999 Initially, the content of the DBII course centered on more has helped to provide an overview of how various areas advanced database concepts and practical experiences to are perceived by graduating seniors and what areas to fine-tune database designs and normalization techniques in concentrate on in upcoming semesters. relational database with considerations for object oriented methodologies, optimization, design tools, and issues Results of Ranking involving naming conventions, documentation, and Figure 1 shows the collected average from student standards. These areas were more natural extensions from responses for 5 semesters over 3 years. The average size the basic database course and required more in-depth of the classes was 20 undergraduate, senior students. The utilization of database design tools (ERwin and System topics that have increasingly demonstrated a very strong Architect) and database systems (Oracle and MS Access) student interest include web database design (90.83%), as well as development of more complex assignments data warehouse (90.4%), PL/SQL (87.7%) and database (Computer Associates 2002) (Popkin 2002). From this administration (81.03%). The interest in Interactive SQL point, topics have gradually been added each semester. using Oracle (or another database system) has also been With each addition, shifts in the content have been consistently high at 80.05%. One interesting area of required to present the material in an appropriate sequence change has been the increasing interest in embedded Java for assignments and to prevent the course from becoming (79.43%) and the decreasing interest in embedded a software training class. COBOL (48.1%) since the addition of object oriented (OO) programming to the IS curriculum 2 years ago. Utilization of Questionnaires At the end of each semester, a questionnaire is given to the Course Content students to provide feedback about the course. The Over the past three years, DBII has gradually evolved information gained from the questionnaires has been from a course teaching advanced database design and valuable in the continual process to reassess and improve implementation techniques to a course with significant the content of the course. The first two questions are 1. components of current technology and advanced Did the content of the course have too much overlapping techniques. In the first course offering of DBII, object with the introductory Database Systems course? and 2. If oriented database design and embedded SQL with Java yes, please list the topics that you would like to reduce or were introduced. Utilization of embedded SQL with Java remove. Since the first semester (fall of 1999) when 66% teaches the students about interface designs using database of the students responded that there was too much overlap, the percentage has gradually decreased to 15% in the past businesses and students' needs for current skills as well as Collected Data guidelines under development as part of the IS 2002 curriculum model (Davis 2001). Data Warehouse Web Design At present, the following list of topics reflects the tentative schedule for the 16 week (one semester) Database Systems OO-Design II course. Data Modeling DBA Major Topics Covered in the DBII Course (W=weeks) MsAccess 1. Review of general database and design concepts (1 W) 2. Overview of system development and database life Embedded SQL&Java cycles (.5 W) Embedded SQL&C 3. Advanced design diagrams (ER, EER, IE, IDEF1X, Embedded SQL&COBOL shading, UML) and design tools (1.5 W) PL/SQL 4. Documentation, data dictionary, and standards (.5 W) 5. Advanced SQL and PL/SQL (1.5 W) Interactive SQL 6. Database normalization, denormalization and 0 10 20 30 40 50 60 70 80 90 100 considerations in different designs (.5 W) 7. Data warehouse (4 W) 8. Database and the Internet (E-commerce) (2.5 W) Figure 1. Collected average from student responses for 5 9. Basic DBA responsibilities (1 W) semesters over 3 years. 10. Object oriented databases (1 W) 11. Team Presentations (1 W) 12. Exams (1 W) access by manipulation of data through an object oriented programming language. With the recent addition of object oriented programming into the curriculum, object oriented Technical Support Another challenge encountered in offering advanced design concepts and embedded Java are currently in the topics is the provision for reliable hardware and software process of being integrated into the introductory database systems to meet the course requirements for up-to-date course. technological experiences. The support of the lab personnel and the administration are critical for the During the second semester of teaching DBII, PL/SQL success of an advanced database course. The classroom is was added to lectures. Students learned how to use SQL equipped with a machine that is connected to the database and database programming language directly at the server servers in the computer labs. The lectures are presented level to optimize their query processing and to write with different presentation and demonstration tools using a procedures, triggers, and packages for different cases and projector. Lecture notes and practice problems are conditions during query processing in their projects. available on Blackboard for the students to study. In the third semester, data warehousing concepts including design methodologies, implementation, optimization and 3. ASSIGNMENTS data mining were introduced into the course (Teorey 1999) The students integrate their knowledge through individual (Seyed-Abbassi 2001). To allow adequate time to cover assignments and team assignments. Using a mixture of these concepts, the time for review material was individual and team assignments provides students with significantly reduced. Based on information from the the opportunity to develop personal skills in problem lectures, the students completed two data warehouse team solving, and at the same time, provides experience in assignments. In addition, basic database administration sharing responsibilities as a team member. The responsibilities and skills were also integrated into the assignments vary somewhat semester to semester to lectures and demonstrations. present new challenges to each group of students and to add new components in different combinations. The A section on database and the Internet (E-commerce) was following are brief descriptions of the individual and team initiated with lectures and software demonstrations during assignments that are typically given in a semester. Due to the fourth semester (Rob 2002). Students used the order of the lecture material presented and the time ColdFusion to practice accessing a database on the server. requirements for completion of the team projects, there is Increased involvement of web utilization is anticipated as some overlap between the individual assignments and the students begin to incorporate web-oriented query projects. processing in their projects. The continual changes and evolution of topics, such as Individual Assignments 1. The first assignment that requires students to use the data warehousing and E-commerce in the DBII course, library and the web to research an area related to database consider innovations in database technology required by and then compile the information into a 4-5 page 6. In the database and Internet component, students learn typewritten paper with references. The topics vary each about interface writing using Cold Fusion or Oracle semester. The most recent topic involved researching the WebDB for simple programs to access the database over history of an assigned major software company (Oracle, the Internet. An assignment is given to access information Informix, IBM, Sybase, Microsoft) including the direction in a database example (banking) by using a web browser. of the company's database technology and the development of their software products. This assignment Team Assignments enhances research and writing skills while learning about During the semester, the students work in teams for two an area in database. projects. The typical number of team members is 3-5 depending on the class size. Working together to analyze, Assignments #2 and #3 are geared to familiarize all the develop, implement, document, and present a project students with the available software tools for design and provides valuable experiences in group interaction similar implementation. These assignments have a quick turn to what they will encounter in the work environment. The around time. teams are determined by the students or selected by drawings depending on the decision of the class. The 2. Assignments #2 and #3 involve the utilization of instructor is available for consultation through various design tools (ERwin, System Architect, and UML Blackboard, email, office hours, and special group with Rational Rose) for the logical and physical design of meetings to help the teams in the development of the a database with considerations for the more advanced projects. After the first project, the students sometimes design diagrams (ER, EER, IE, IDEF1X, shading, and change teams for the final project. The following brief UML). These assignments support the high interest descriptions illustrate the typical type of team projects (70.83%) expressed by students in the past 5 semesters on given during the semester. the questionnaire to learn and use different design tools. The first part is the development of a logical diagram 1. For the initial team project, the students are required to using one of the tools to design a database (such as, design a logical and physical diagram based on the suppliers-parts-projects, banking system, and department- requirements for a given data warehouse. Then, they faculty-course) with given requirements. The second part create a star schema; list and define necessary attribute of assignment #2 is the conversion of the logical diagram hierarchies; convert to a snowflake schema using to a physical level diagram with Oracle (or any other normalization and denormalization; and implement the database) as the target database and real world values for data warehouse design using MS Access. The students are the data type and size for each attribute name. Using the required to turn in the entity relationship diagrams, the design tool, the students create the SQL code for their information about the attribute hierarchies, and a diskette designs. with the old/new tables and queries. The assignment is completed in 3 weeks. 3. With the SQL created from assignment #2, students create an MS Access DB-space to create the tables in the 2. Different types of final team projects have been utilized database and then, insert tuples to each table for the first over the past five semesters. The final projects are part. In the second part, Oracle is used to create the tables completed in 5 weeks. For the final project, the team with the same attribute names and tuples with Oracle data members submit a confidential team evaluation form on types. which a team member evaluates his/her performance as a team member and the performance of the other team 4. After lectures and demonstrations on PL/SQL, stored members during the project development. The evaluation procedures, triggers, and packages, each student develops form directly affects each student's final grade. a PL/SQL program using cursor to insert records to a designed table, such as a checking account table. Then, 2.a Initially, the projects involved the design and the records are sorted in the table by transaction amount in implementation of a database for a particular organization descending sequence into a new table. The total selected by the team. The teams developed the data transaction amounts are calculated by transaction type and dictionary, naming convention, valid records, and inquiry stored in a report table. Students often chose to use statements. The turn-ins included entity relationship PL/SQL in the data warehouse team assignment. diagrams; documentation for naming convention and normalization; data dictionary reports; DDL documents; 5. In this assignment, students use embedded SQL with create, insert and DML commands; database tables with Java to retrieve required information from database tables FD diagrams; and queries with results. There was and to design an interface to the database. Building on flexibility for the teams to integrate their experience with OO experiences from two Java courses and the object oriented concepts, embedded languages, interfaces, introductory database course, students learn to implement PL/SQL, and web access depending on the decisions of more complex queries with well-designed interfaces to the the team. Each team presented the final outcome for their database. organization to the class. 2.b Another team project involved the design, 5. REFERENCES implementation, mining, and documentation of several data warehouse projects. The students selected the areas Computer Associates: for the data warehouses. The guidelines for development http://www3.ca.com/Solutions/Product.asp?ID were based on a list of considerations that the data =260 warehouse needed to support, such as flexibility for future expansion, capability to store detailed and summarized Davis, Gordon et al., 2001, "IS 2002: An Update of the information, star schema with at least 6 tables, and Information Systems Model Curriculum," dimensional tables of 150 tuples. The students determined International Academy for Information the utilization of interactive SQL, PL/SQL, and embedded Management Proceedings, December 14-16, SQL using Java and/or other programming languages. pp. 389-397. The final submission included description of the data warehouse with its business purpose/requirements; Harder, Joseph, 2001, "A Survey of Database Curricula reasoning with assumptions for tables and attributes; Using Web-Posted Syllabi," International attribute hierarchy; entity relationship diagrams; all Academy for Information Management commands and tables; reasoning for financial, aggregated, Proceedings, December 14-16, pp. 135-140. and forecast criteria; mining queries with results; and suggestions for future improvements. The teams Popkin: presented their projects to the class. http://www.popkin.com/products/sa2001/syste marchitect.htm 2.c Using the team projects described in 2.b, an alternative project considered modifications and enhancements to the Rob, Peter and C. Coronel, 2002, Database Systems previously designed data warehouses. The teams needed Design, Implementation, and Management, to analyze the existing data warehouse documentation and Course Technology. coding. Then, they applied new requirements to the design, implementation, data mining, and documentation. Robbert, Mary Ann, Ming Wang, Mario Guimaraes, and The turn-in materials were similar to 2.b. The teams Martha Myers, 2000, "The Database Course: presented their projects to the class. What Must Be Taught," SIGCSE Bulletin, Proceedings of 31st SIGCSE Technical 4. CONCLUSION Symposium on Computer Science Education, March 8-12, pp. 403-404. The recognition of the need for an advanced database course and its addition to the curriculum proved to be only Seyed-Abbassi, Behrooz, 2001, "Teaching Effective the initiation of a course that has been a rewarding Methodologies for Designing a Data challenge to teach. The gradual addition of topics to the Warehouse," 18th Annual Information Systems course has enabled the instructor to prepare appropriate Education Conference Proceedings, lectures, software demonstrations, and assignments in the November 1-4, CD#35C. areas of advanced design and database utilization, PL/SQL, data warehousing, web database design, and Springsteel, Frederick, Mary Ann Robbert, and Catherine database administration responsibilities. The course and Ricardo, 2000, "The Next Decade of The topics have been very well received by IS undergraduate Database Course: Three Decades Speak to the students who are interested in continuing their study of Next," SIGCSE Bulletin, Proceedings of 31st database. Other areas that will be considered as the course SIGCSE Technical Symposium on Computer develops include client-server, distributed, temporal, Science Education, March 8-12, pp. 41-45. multimedia, and deductive databases. As technology changes, continual re-evaluation of traditional topics and Teorey, Toby, 1999, Database Modeling & Design, considerations to shifting topics in both introductory and Morgan Kaufmann. advanced database courses is essential to expose students to current and emerging database areas. Urban, Susan and Suzanne Dietrich, 2001, "Advanced Database Concepts for Undergraduates: Experience with Teaching a Second Course," SIGCSE Bulletin, Proceedings of 32nd SIGCSE Technical Symposium on Computer Science Education, February 21-25, pp. 357-361.