A second Course in Database Management Systems: A Rationale and a Proposed Course Outline Ahmad Abuhejleh Associate Professor College of Business & Economics Computer Science & Information Systems University of Wisconsin ? River Falls River Falls, WI 54022, USA Email: Ahmad.Abuhejleh@uwrf.edu ABSTRACT A second course in Database management Systems is needed to help provide the competencies and skills needed by entry-level systems analyst or programmers. This course is about understanding and developing application logic that brings the Web and databases together. The course introduces a number of the different approaches to integrating DBMSs into the web environment including CGI, server extensions, Java, Scripting languages, Active Server Pages, Oracle?s Universal Data Server. The author taught the course many times as an elective course concentrating on using MySQL, an open source DBMS known for its suitability to applications that require speed but low resource overheads. The main scripting language used in the course is PHP, which is best known for its function libraries that interact wth more than fifteen relational database systems and the web environment. Universities who offer this course are certainly on the right track because information technology recruiters admit that graduates are difficult to find who have sufficient knowledge in Web Database applications. 1. INTRODUCTION Information systems curriculums generally include a database management systems course. Most professors who teach this course concentrate on the relational theory. In this traditional course students must learn many principles that relate to database management and/or database design. These principles include the conceptual and logical database design, based on the widely accepted Entity-Relationship model, with normalization used as a validation technique. The course also covers the mapping of the logical design to a physical implementation. SQL (Structured Query Language) is also strongly emphasized (Connolly and Begg, 1999). This first course provides students with a solid foundation in the relational theory and SQL. But with growth of the web over the past decade, there has been a similar growth in services that are accessible over the web. Many new services are web sites that are driven from data stored in databases. Examples of web database applications include new services that provide access to large data repositories, e-commerce applications such as online stores, and business-to-business support products. These topics that integrate the DBMSs with the web are not covered in the first course. In conversations with Information Technology managers from 3M, Mayo Clinic, Ecolab, and Medtronic and others, they often express concern that their new hires simply lack the knowledge to become web database developers. 2. THE SECOND COURSE: WEB DATABASE APPLICATIONS 2.1 Prerequisite First course in DBMS; Third-generation programming language such as C, C++, Java, or Visual Basic; Some basic HTML/DHTML. 2.2 Recommended Database The author recommends MySQL DBMS to be used in this course. MySQL is an open source relational database management system developed and provided by the MySQL AB Company. As open source software it can be downloaded from the Internet and modified by the user to meet their needs. MySQL AB claims that MySQL ?is the world?s most popular Open Source Database, designed for speed, power and precision in mission critical heavy load use?. They provide different versions of their software for use with different operating systems. The company also offers Modoc, a program that allows other database programs to interface with MySQL databases. Open Database Connectivity (ODBC) is a widely accepted application-programming interface for accessing databases. MySQL is based on SQL. One big drawback of MySQL is that it does not enforce referential integrity (Williams and Lane 2002). 3. COURSE CONTENT 3.1 Objectives Upon completion of this course students will be able to: (1) Learn the three-tier architecture model used in many web database applications. The database tier, middle tier, and the client tier. (2) Learn a scripting language. The author used PHP scripting language. Students need to know the basic programming constructs, variables, types, functions, arrays, strings, techniques, and common sources of bugs. (3) Students learn the MySQL database and how to interact with it using SQL. This includes the MySQL command interpreter and the basic features of MySQL; using SQL to create and modify databases, tables, and indexes; using SQL to insert, delete and update data; SQL select; and SQL functions (4) Learn how to query web databases. This is a very significant topic in the course where the author teaches the students how to use a scripting language such as PHP to query the database tier and render HTML in a client-tier web browser. (5) Learn how to write to web databases. Students learn how to use the HTML forms to capture input for database writes. Then they learn how files can be uploaded from a web browser to a web server and the data inserted into a MySQL table. Students also learn the basic management techniques of locking and unlocking tables. (6) Students learn the principles and techniques for user-input validation. (7) Learn the principles of adding session management to web database applications. Session management allows the interactions between a user and the application to be related so that, for example, a user can log in and log out of an application and be guided through a series of steps in a process. (8) Students learn how a scripting language such as PHP can be used for basic authentication, how databases can manage many users, and how communications can be secured with the network-level secure sockets layer. (9) Students learn how to work in a small group. Each group takes on a real-world problem and implements a web database application. 3.2 Topics Table 1 outlines the topics covered and provides examples of required readings Table 1: Syllabus Week Topic Readings (Williams 2002) Chapter 1 Database Application and the Web Three-Tier Architectures N.A. 2 PHP Conditions and Branches, loops, arrays, strings, user-defined functions, Objects 1,2 3 MySQL and SQL MySQL command interpreter, managing Databases, tables, and indexes 3 4 Querying Web Databases Connecting to a MySQL database, formatting, interacting with other DBMSs using PHP 4 5 Students Choose Projects 6 User-Driven Querying User-Input, querying with user input 5 7 Writing to Web Databases Database inserts, updates, and deletes 6 8 Validation on the Server and Client Server-side validation, client-side validation 7 9 Sessions Building applications that keep state, session management over the web, PHP session management 8 10 Authentication HTTP authentication, HTTP authentication with PHP, authentication using a database, web database authentication 9,10 11 Security N.A. 12 Testing N.A. 13 Student Presentations 14 Student Presentations 3.3 Teaching Tips This course provides students the opportunity to work in groups. Most companies give universities low marks in providing grads the knowledge and experience in working within groups. The author divided the class into 4-5 teams. Each team has 3-4 students. The author chose MySQL/PHP platform but other platforms such as Access/ASP can be used without any major differences. 4. CONCLUSION A second database course emphasizing web database application is a must. Information Systems departments should offer multiple learning experiences in database management and design. The author encourages departments to expand this vital area of the curriculum. The author receives very strong recommendation from many companies to make this course as a requirement. Graduates with the knowledge of web database applications definitely have a big advantage. 5. REFERENCES Connolly T. and C. Beggs. (1999) Database Systems A Practical Approach to Design, Implementation, and Management, Addison-Wesley, New York. Williams H. and D. Lane. (2002) Web Database Applications with PHP and MySQL, O?reilly.