A Practical Approach to Teaching and Learning Database Systems in Applied Computing Science
Mila Kwiatkowska
Department of Computing Science
The University College of the Cariboo
ABSTRACT
This paper describes curriculum development, learning environment, and delivery methods used in database courses taught in the Bachelor of Technology in Applied Computing Science program at UCC. It presents the author's experience, observations, and plans. The paper suggests that course development and delivery should be based on the following premises:
Course content should offer a good
balance between theoretical and practical components. Both components should be
well integrated.
Each theoretical unit should be
supported by an applied component using realistic examples, in-class exercises,
assignments, and client-based projects.
Implementation of the theoretical
components should be based on current and future IT industry standards. The
practical components should correspond to the professional competencies
expected from BTACS graduates.
Students should be provided with full
access to computing resources at school and at home. Each course should have
WebCT support. Students should have easy (preferably online) access to
diversified and specialized literature and documentation. They should also have
access to field specialists other than the instructor.
Each course should involve at least
one component designed and implemented for a client-based project that will
serve the local community.
Keywords: Database Learning, Project, Electronic Commerce
1. Introduction
In 1997, the Computing Science Department at UCC commenced a "2+2" degree in Applied Computing Science [PBDB99]. The Bachelor of Technology in Applied Computing Science (BTACS) program provides a strong theoretical foundation in computing science and, at the same time, offers practical technology-oriented skills that meet the current and future needs of the IT industry. Currently, the BTACS program offers three courses in database systems: Database Systems (COMP361), Advanced Database Systems (COMP461), and E-commerce Systems Development (a course on database-driven Web site development COMP462)[FEILER99]. The content of each course is established based on ACM degree guidelines (ACM IS'97 Curriculum Report) [IS97], the ACM SIGSCE survey of other similar programs [SRR2000], and input from the IT industry. The courses' practical components are tailored to meet at least some of the current and future needs of employers in British Columbia and Alberta, since most graduates seek employment in these two provinces. The specific technological skills (competencies) are established using diverse sources: informal surveys of current and potential employers of BTACS graduates, feedback from the Co-Operative Education coordinators and students, meetings and discussions of IT professional organizations (CIPS), and a survey of typical job advertisements.
1.1 Database Courses and their Content
The first Database System course includes a set of traditional topics from data modeling, database design, ERD, EERD, relational algebra, normalization, SQL, data security, and database administration. The new topics include: client/server architecture, Web access to the database, Web publishing from a database, Object-Relational Model, Object Oriented Model. These topics are integrated with the CASE tool for data modeling and database design (DataArchitect), two DBMS: Oracle8 and Access 97, SQL*PLUS (Oracle 8), and both SQL and QBE for Access, VBA for basic GUI interface, and ODBC for client/server and Web publishing using Access. Object Oriented Design is taught using UML standards and class diagrams in Rational Rose.
The Advanced Database Systems course includes advanced application design and programming, triggers, stored procedures, data warehousing, ODMG standards, ORDBMS, and ODBMS. The technologies include: CASE tools for database design and implementation, and reverse engineering (DataArchitect), PL/SQL (Oracle 8), Developer 2000 for GUI interface (Oracle Forms and Reports), CASE tool for data warehouse logical and physical design (WarehouseArchitect component of PowerDesigner 6.1 from Sybase). This course involves a client-based team project that allows the students to choose additional technologies: Java, JDBC, VB, VBA, Jasmine (ODBMS).
The E-commerce Systems Development course was recently developed by the author and offered for the first time in the Winter 2000 semester. It is designed as an upper level course with two prerequisites: Database Systems and Web Programming. This course is taught as a "merge" between Web and database systems. The term "e-commerce" is used in the broad sense as any services or products delivered through Internet; including commercial products and services, as well as educational systems, entertainment, informational systems. The database topics include database design for Web, especially multimedia issues, data validation, transaction processing, data security. The core technologies are PWS, IIS web servers, Active Server Pages (ASP), JavaScript (client side processing), VBScript. and ColdFusion. Additional technologies are presented and used depending on the individual student interest for example: XML, PHP, and Perl.
2. Delivery Methods used to Integrate Theory and Practice
2.1 Integrating Lectures and Labs into Synergetic Units
In the traditional academic setting, courses are divided into two parts: lectures (3 hours) and laboratories (1 hour). These two components are often taught independently from each other (in most cases by a professor and a TA). Lectures are conducted for large groups of students (more than 40), whereas laboratories divide students into smaller groups (groups of 20 are typical ) to work in programming labs on practical skills. The author argues that this setting is the worse possible scenario for technology and practice oriented courses in database systems. The BTACS program offers the significant advantage of small classes (maximum 20 students) for courses in database systems. Furthermore, the same instructor conducts lectures and laboratories. This environment allows for merging theory and practice into short units. The short lectures (on average, 20 minutes) are blended with practical exercises and participatory/co-operative group work. Students are required to learn both by immersion (working on real-life projects) and by discovery (through experimentation). The close-knit learning environment allows the instructor to monitor students' individual progress as well as to consult, mentor, and encourage rather than lecture "ex cathedra." The students' evaluations of courses and informal discussions show that for database courses at least, shorter lectures followed by hands-on implementation sessions work better than separate hour-long lectures with once-a-week labs. With this kind of teaching method, learning occurs mostly in the lab context, where the instructor discusses concepts and then illustrates them using a projecting unit linked to the instructor's computer. Students work through the same examples together with the instructor; then they work on-line to solve assigned problems. On-line course materials include exercises, logical models and physical models ready to be downloaded. Programming components use many program templates (for example VBA modules for database access, typical triggers and stored procedures for Oracle 8) that are modified and reused by students. This approach (learning from examples) appears to be the optimal way to acquire programming skills. Students are required to read appropriate materials before each class (these may include textbooks, WebCT materials, and Oracle on-line documentation).
2.2 Real-Life Examples from IT Industry
All database courses involve real-life samples collected by the author from various projects. In one exercise, the instructor shows students a relatively small physical database model for Meter Proving System (Canadian Utilities Ltd.) for Gas Billing Project. This sample has 32 tables (this is considered a very small database according to industrial standards). The students are asked to list the naming conventions and standards. There are two issues in this exercise: the importance of standardized and meaningful names for columns (students do not have problems with names like: MODEL_SEQ_NUM, MODEL_DESC); the second issue involves two columns that are in every table: DATE_STAMP (last update date) and USER_STAMP (user id). Interestingly, textbook examples never include these two fields. In the writer's experience, typical textbook examples and exercises greatly oversimplify database design and implementation. They do not offer good standards and flexible database design. In the Advanced Database System course (Winter 2000), students were using the Northwoods University Student Registration Database, provided with Morrison/Morrison's book "Guide to Oracle 8". This database includes names like SPIN (STUDENT_PIN_NUMBER) and FRANK (FACULTY_RANK); furthermore, the FRANK column has the following values: "ASSO", "FULL", "ASST", and "INST".
2.3 Use of Projects and Client-based Projects in Database Courses
Each BTACS database course involves projects. The introductory Database Systems course involved an individual client/server project. This project was divided into three phases: database logical and physical design and implementation; client/server architecture implementation with client application implemented in VBA and Access; and Web publishing from a database. All students were working on the same set of requirements: a reservation and registration system for a large campground organization.
The Advanced Database course (Winter 1999) involved a team project for the Faculty Association of the University College of the Cariboo. This project performed analysis, design, and implementation of a small data warehouse system for tracking part-time employees' workload statistics. This project was treated as a prototype and learning experience for the students. The main emphasis was on the process of meeting the non-technical client, defining the requirements from the data warehousing perspective, designing a small database in Oracle 7, and a GUI interface. The Oracle 7 was required as a standard DBMS, and some stored procedures were mandatory; however, the user interface was left to the creativity of the students. There were four teams (with four or five members per team) and one individual project (for the mature student working full-time in Merritt). The teams implemented interfaces using Oracle Forms, Visual Basic, and even Java applets on Web.
The E-Commerce Systems Development course (Winter 2000) developed an e-commerce site for a small local company called Kamloops Rockworks Ltd. ( http://www.kamloopsrockworks.com/. This project was divided into two phases:
Phase 1: Design and implement static
Web site including the following information: contact information; local rocks
to be found in the Kamloops area and those sold in the store; information about
Thompson Valley Rock Club and Kids Rock Club; information about rock hunting
tours organized by Kamloops Rockworks; biographical notes about the store owner
and our great client, Robert Davis; and links to other sites promoting Kamloops
as the best city in the world.
Phase 2: Database-driven dynamic Web
site providing the following features: rock catalogues, tour information,
bulletin board for the Thompson Valley Rock Club and the Kids Rock Club,
educational component (games, quizzes, puzzles, etc.) for kids interested in
local rocks, site administration (database updates) via the Web for the
non-technical site administrator.
The first phase was completed as a parallel project for four groups of students (each group had three to five students). During the first week of classes, the students were introduced to the project by the instructor and supplied with some paper-based materials and rock pictures. In the second week, students met with the client (outside of class time) and defined the basic requirements for the first phase. The teams had the next four weeks to complete their projects. On the due date, all projects were uploaded to the WebCT site and students compared the results. The projects were reviewed by the client, a Digital Art instructor, and the instructor. The students met with the client to evaluate each design and specify requirements for the second phase of the project. One of the projects was chosen for posting to the site of the local hosting company, MediaWeb Solutions. The next meeting was arranged with the owner of the hosting company and one of the key programmers. This meeting was held to address the technical constraints for the implementation of the second phase. Students learned that the database should be implemented in MS SQL/Server, not Access 97; that the files are uploaded using the ASPUpload component. After the initial requirements statement, the project was divided into four subsystems (components) that were assigned to each group. Each group was working on one component of the system: system management, products, tours, and rock clubs. Students had about five weeks to finish the project.
2.4 Lessons learned
The parallel model of teams working on the same project was easier for the instructor to co-ordinate and evaluate. However, the fact that only one team's work can be posted to the site was frustrating to the students. The second phase, when all teams were co-operating, was very challenging for the instructor. The database design was initially prepared in a "jam session" of all teams. However, the final design and implementation was done by the instructor. The common database used in the development process was to be administered by a single DBA; due to the time constraints, the students had to assign DBA tasks to the instructor. The hosting company provided the students with a test site with FTP interface. The students were able to upload their ASP pages to the Internet site and test it from home or school labs.
2.5 General comments
The inclusion of client-based projects in the curriculum adds opportunities otherwise unavailable such as: meeting with non-technical users from various backgrounds, learning communications skills and group dynamics, and performing project management (managing the group members, the scope of the project, and the time constraints). Using Rapid Application Development methods, students learn to view database design and implementation as part of the systems analysis and design process. Students perform fast analysis of the problem, generate solutions, design the database, implement applications, present the system to the user, submit the system for acceptance testing and evaluate the system based on initial system objectives. Student evaluation of these project-based assignments underlined their worth. Students were well motivated and encouraged to learn additional skills and try new methods. The real-life examples and the real-user projects served to illustrate a crucial but often ignored aspect of the IT world: IT professionals spend most of their time not on the technical issues but on understanding particular business domains. Traditionally, the university student focuses on technical aspects. Yet students must be prepared to work with clients; they must realize that clients expect an IT professional to understand their business, and that such understanding is prerequisite to any "technical" solution.
3. Learning Environment Conductive to the Applied Approach
The database courses are provided in a synergistic learning environment. It is assumed that learning is a continuous process that occurs mostly outside of the lecture halls. The synergistic approach to learning includes all possible components: human resources (peer groups, instructors, project clients, IT professionals); information resources: paper-based course materials, Web-based, and computing resources (hardware and software). In database courses, students must use up-to-date products reflecting current industry standards as well as experimental and/or cutting-edge software. The changing nature of the technical environment requires that students become familiar with various software packages: MS Access 97, MS SQL Server 7, Oracle 8, Oracle 7, Jasmine, PowerDesigner, Rational Rose '98, IIS, ASP, ColdFusion. The use of several products requires in turn familiarity with a wide range of complex and lengthy product-specific documentation. Students must be able not only to use but also to compare and evaluate software products. Finally, they must become proficient in finding information on the Web, using reference manuals, and searching in on-line documentation.
3.1 Web Based Support for Database Courses
UCC uses World Wide Web Course Tools (WebCT) as its standard Web support tool [WEBCT99]. WebCT is used to create Web-based educational environments. Each database course has its own password-protected Web site, which provides students with various course materials including: detailed calendars, lecture notes, assignments and answers to assignments, reference materials, exercises, lists of Web links, glossaries of terms, bulletin boards, and student marks. Experience shows that students use these Web materials on an everyday basis to prepare for classes, check their progress, or post their assignments. It should be noted, however, that use of WebCT does require a substantial time commitment from the instructor.
3.2 Development Standards and CASE Tools: A Must for Database Courses
In my industrial experience, I have never seen hand-drawn ERDs or other diagrams. Students must adhere to typical industrial development standards using both Structured and Object Oriented methods (Unified Modeling Language). BTACS students taking database courses use the following CASE tools: PowerDesigner, Oracle Designer 2000, and Rational Rose. PowerDesigner (DataArchitect) is used throughout the introductory course. Beginning students find this CASE tool easy to use. They are able to create conceptual models (CDM), generate physical models (PDM), and generate corresponding databases either on-line (via ODBC) or by creating a DDL script. The Advanced Database Systems course uses Oracle Designer/2000. Students in all database courses are introduced to UML and Rational Rose.
3.3 Course Textbooks and Reference Materials for Applied Computing Science
Various standard textbooks for database systems are used for university level courses: Date's Introduction to Database Systems; Elmasri/Navathe's Fundamentals of Database Systems; Kroenke's Database Processing; McFadden/Hoffer/Prescott's Modern Database Management; Ramakirshnan's Database Management Systems ([DATE2000],[EN2000],[KROENKE99], [MHP99]). In the writer's opinion, students need more than one typical textbook. They need a "theoretical" book and a "practical" reference book. There is no single book on database systems covering the entire material from theoretical and practical perspectives. The author has tried the following approach: one theoretical book and one product reference book. One of the deciding factors in choosing the textbooks for the database courses was the fact that the publisher included software (Oracle) with its textbook. All three courses offered in the BTACS program require in-depth knowledge of Oracle products. Students have access to the Oracle 8 database server from university labs (open 24 hours). However, they are not provided with access to the server via Internet. Since many students travel to UCC from other towns and often work part-time, they need access to the software products at home. Currently, there are two publishers offering their textbooks with Oracle software products on CD-ROMs [MORRISON99],[MHP99].
3.4 Oracle Documentation and Microsoft Online Help
Students were required to use Oracle documentation to complete their assignments. They had to apply syntax and analyse the BNF and FSM notations. The Oracle documentation was challenging for the introductory level students. For example, the syntax for ALTER TABLE statement spans seven pages. As an exercise, students printed the entire syntax and highlighted the options required by the course (ADD, MODIFY, DROP).
4. Evaluation of Students using Applied Approach
The evaluation process is based on institution-wide standards: assignments, projects (team marks), quizzes, and exams. The exams are conducted both in labs (with access to computing resources) and in classrooms. The students have a choice between open- and close-book exams. Most students prefer a combination of the two. Interestingly enough, students find the on-line exams very stressful and will avoid them if offered a choice.
In the author's opinion, this traditional approach to evaluation is very subjective and based on the individual instructor's exams. The question about more standardized methods of evaluating students' proficiency in database systems remains unanswered. The basic questions are: How does one provide universal and standardized measurements? How can future employers evaluate graduates from different programs? The applied approach to computing science requires clear definitions of the minimum requirements proficiency levels. Industry certification exams can be used to assess practical skills. Therefore, the author is investigating possible participation in the Oracle Academic Initiative Program which may provide good guidelines for teaching, learning, and evaluating the required technical skills.
5. Future Plans
The methods used for the integration of theory and practice require continuous revision and re-evaluation. Teaching (and learning) database systems within the changing environment is very challenging. The author plans to gather from students and graduates more formal evaluations of this teaching method. The small size of classes in the BTACS program and the availability of the lab resources create a unique opportunity to offer a high-quality, student-oriented environment. However, larger classes may require some modification of the presented methods. The author plans to continue with client-based projects (off-campus and on-campus). The Kamloops RockWorks Project will be included in the next offering of the E-Commerce course (Fall 2000). The existing components will be dissected and used to illustrate theory and practice. Later in the course, the students will be required to add components to the existing database and ASP pages. The data warehouse project will be revised in the Winter 2001 offering of Advanced Database Systems. The author is very interested in your opinions and experience. Any feedback you can provide will be greatly appreciated!
References
1. [DATE2000] Date, C. J. Introduction to Database Systems, 7th ed., Addison Wesley, 2000.
2. [EN2000] Elmasri, R. and Navathe, S. Fundamentals of Database Systems, 3rd ed., Addison-Wesley, 2000.
3. [FEILER99] Feiler, J. Database-Driven Web Sites, Morgan Kaufmann Publishers, Inc.
4. [IS97] IS '97 Model Curriculum and Guidelines for Undergraduate Degree Programs in Information Systems. Online. Internet. November 24, 1999. Available WWW: http://acm.org/education/curricula.html.
5. [KOCH99] Koch, G. and Loney, K. ORACLE8: The Complete Reference, Osborne McGraw-Hill.
6. [KROENKE99] Kroenke, D. Database Processing, 7th ed., Prentice Hall, 1999,
7. [MHP99] McFadden, F., Hoffer, J and Prescott, M. Modern Database Management, Fifth Edition. (CASE tools Oracle Edition), Addison-Wesley 1999.
8. [MORRISON99] Morrison, J. and Morrison, M., A Guide to Oracle8, Course Technology.
9. [PBDB99] Paweska R., Brouwer R., Dhanjal S., Babinchuk W., Meeting the Modern Challenge-Integrating Computing Science Undergraduate Education at the University College of the Cariboo. Proceedings of WCCCE 1999. http://www.panache.cs.ubc.ca/wccce/
10. [SRR2000] Springsteel, F., Robbert M., Ricardo, C. M. The Next Decade of the Database Course. Three Decades Speak to the Next. SIGCSE Bulletin, 32,1,(March 2000), 41-45.
11. [WEBCT99] Web Course Tools, WebCT. Online. Internet. September 10, 1999. Available WWW: http://www.webct.com
Mila Kwiatkowska
Computing Science Department
The University College of the Cariboo
Box 3010
Kamloops, British Columbia V2C 5N3, Canada