Design and Implementation of a new course: Creating databases for web applications

1. Design and Implementation of a new course: Creating databases for web applications Jeanine Meyer & Michael Conry Mathematics/Computer Science & New Media Purchase College/SUNY CCSC NE Worcester, MA

2. Background on students • Liberal Arts & Arts school • Math/Computer Science and New Media majors • Existing courses include: Creating Web Documents, Creating Dynamic Web Documents, design courses, CSI, CSII, Networking & Security. • require more upper-level electives • spaces reserved for Continuing Education students CCSC NE Worcester, MA

3. Course Content • Server-side programming ("middleware") • Design/planning • systems design & analysis; data base management systems • Issues • software choice: • Implementation: Open source versus proprietary software • Design (CASE tools?) • textbook(s) CCSC NE Worcester, MA

4. Course logistics • ~4 hours / once a week • share (compete) computer classroom • our own server • Windows NT, running php and MySql, asp & Access. • asp & Access gave more problems. One php problem concerned uploading and using files. • class size capped at 25. 17 finished. CCSC NE Worcester, MA

5. Decisions • s/w: Open Source and Microsoft • Cold Fusion was/is an alternative. • Choose JavaScript for asp (alternative is VBScript) • encourage students to give extra credit talks • No textbook • made finding on-line resources part of the course • posted class notes • prepared CD of s/w (Personal Web Server, php, MySql, not Access) • students could also use ftp to the server. CCSC NE Worcester, MA

6. Decisions, continued • "Seminar": students study & present prepared projects • order system & quiz show times 2 • Student project • choice with option to expand prepared projects (which only one person did) • multi-part: announcement, presentation of design diagrams, final presentation • Quiz students on both sets of s/w: practice quizzes, open notes CCSC NE Worcester, MA

7. Instructional support • Blackboard/courseinfo • Provides place for [changing] schedule, notes, Discussion Forum, e-mail, surveys, on-line [practice] quizzes when it is operational • courseinfo.purchase.edu Use Preview to find course • Backup site: members.bellatlantic.net/~vze2s839 CCSC NE Worcester, MA

8. CCSC NE Worcester, MA

9. CCSC NE Worcester, MA

10. CCSC NE Worcester, MA

11. Design • Operationalize design process by teaching and assigning • entity-relationship diagrams • process (data flow) diagrams • storyboards (my ad hoc design) • Chose not to assign/require/teach any CASE tool. CCSC NE Worcester, MA

12. Levels • Programming: • HTML, • php or asp/JavaScript, and • SQL • Client, server, data base engine • presentation, systems logic, data base • Discussed generally: • separating form and content. XML/XSL CCSC NE Worcester, MA

13. Course examples • Models done in both systems • Teams of students presented these to the class • Product ordering • Quiz show CCSC NE Worcester, MA

14. Players table player_id Player name Score lastplayed date Question databank table question_id Question Answer (answer pattern) Value Category 0 history table question_id player_id whenplayed correct 0 CCSC NE Worcester, MA

15. Data flow diagram (process diagram) for quiz show Player Questions DB Play game History DB Edit questions Player scores Editor CCSC NE Worcester, MA

16. Include/Required file: opendbq Create quiz tables (php only) Input Questions (handles form input) Choose category Show scores Ask question Check answer Clear tables CCSC NE Worcester, MA

17. CCSC NE Worcester, MA

18. CCSC NE Worcester, MA

19. ER diagram for on-line store Customer list Customer ID first name last name Billing information E-mail Password Product catalog Product id Product name Picture Cost 0 0 Order list Order ID Customer ID Date Status Total Ordered items Order ID Product Quantity CCSC NE Worcester, MA

20. Data flow (process) diagram for on-line store. Catalog Browse/Order Customer Customer list Billing Current orders Ordered items Shipping Billing system (timed event) Shipping clerk Note that this is the information/data flow, not the flow of goods. The shipping operation produces a physical product: the collection of ordered items, packed and set off for delivery. CCSC NE Worcester, MA

21. Storyboard of partial implementation: ordering Create tables (php only) Include/require: opendbo Input products Delete current customer cookie Order product makeorder Submit order Shopping cart Include/require: displaycartfunction CCSC NE Worcester, MA

22. CCSC NE Worcester, MA

23. CCSC NE Worcester, MA

24. SQL from php quiz $sel="SELECT question_id, question, ans, value from questions "; $sel= $sel . " WHERE category= '" . $pickedcategory. "'"; $result=mysql_db_query($DBname, $sel, $link); $NoR=mysql_num_rows($result); CCSC NE Worcester, MA

25. SQL from asp quiz sel="SELECT question_id, question, ans, points from questions "; sel= sel + " WHERE category= '" + pickedcategory+ "'"; rs=Server.CreateObject("ADODB.RecordSet"); rs.Open(sel, Conn, 1, 3); NoR=rs.RecordCount; CCSC NE Worcester, MA

26. Use of session variables in php orders if (!session_is_registered("cart")) { $cart = array(); session_register("cart"); $items = 0; session_register("items"); $totalprice = 0.00; session_register("totalprice"); } CCSC NE Worcester, MA

27. More complex SQL from quiz asp if (NoR>0) { query= "SELECT question_id FROM history where (player_id="+player_id; query= query + " AND (whenplayed=#"+today+"# OR correct=Yes))"; query="INSERT into past "+query; Conn.Execute(query); sel = "SELECT questions.question_id, question, answerpattern, points from questions"; sel =sel + " LEFT JOIN past ON questions.question_id = past.question_id WHERE "; sel = sel + " category='" + pickedcategory + "' AND past.question_id IS NULL"; } else { sel="SELECT question_id, question, answerpattern, points from questions "; sel= sel + " WHERE category= '" + pickedcategory+ "'"; } CCSC NE Worcester, MA

28. Results • Highly satisfying for instructor & technical support • After drop-off by many who thought it was more design, remaining students worked very diligently & appeared pleased. • Student projects were (mostly) impressive. • Several students commented on importance of design/planning/data base design CCSC NE Worcester, MA

29. Student projects • Many 'real' • dental 'hmo' referral system • company shop & swap • key ring advertising venture • (Masters thesis): data base for study of fish habitats, including two authentication systems • Other • library on-line sources • music samples, music reviews • help for ordering & troubleshooting • order games CCSC NE Worcester, MA

30. What did you like best about the course? 1.labs 2.The ability to email, call or stop by your office and get help when I needed it. If I asked you or Mike how to do something and you didn't know, you researched the problem and discussed your findings the next time we spoke. 3.I learned skills I did not know and I was surprised by what I was able to accomplish. 4.The best thing about the course was that it was geared toward achieving tangible, practical results from applying what we were learning. 5.the subject and the teacher. 6.I enjoyed the the variety of information. That was provided. I also enjoyed the professors that fact the professor was open to helping the students beyond what I expected. 7.Creating ASP pages that actually worked CCSC NE Worcester, MA

31. 8.I loved learning how databases work in regards to the Internet. I think it's GREAT!..Also, I feel my appreciation for programming/programmers has increased about 200%. I loved figuring out the 1st project (askquestions.asp)because that was something I didn't think I could do and I loved the challenge of the final project (even though I'm STILL trying to figure something out). 9.Learning about the most important aspect of the Internet -- moving data which is the engine that makes it all happen. 10.What I really liked best was the hands-on part - both the final project and all the other hands-on projects, both big and small. Being able to actually use what we were learning and seeing it work in practical terms was rewarding. 11.The course focused on simulating "real world" programming tasks and applications and provided a large quantity of example code for the student to review and build on. 12.I enjoyed the ability to use two different sets of tools to develop web pages (ASP/Access and PHP/MySQL) and got a good idea of the underlying concepts.... CCSC NE Worcester, MA

32. What would you change 1. add more time for people to experiment with the code. 2.Maybe a little more time in the Planetarium. Hold the class two nights a week. One for a three hour lab and another for an hour and a half lecture. 3.More lab time and examples, less lecture. 4.The change I would suggest is that it might focus on one programming language/database software instead of trying to combine two in a dual, concurrent focus. 5.have the lecture first then have the lab. 6.I would probably have the profesor explain some aspects of the course material as if each student did not have much computer or programming knowledge to gurantee the understanding of the subject material 7.The Projects (quiz and orders) were good learning experiences.I would assign one project in both PHP and ASP and give a short quiz on them instead of the presentations. CCSC NE Worcester, MA

33. 8.There were a few things I was unclear about and because we had SO much material to cover, somethings never got the full answer I wanted. ER/data flow Diagrams, for example, are still a "problem" area in my mind. Although I loved using JScript, maybe VBScript would be a good thing to teach as well (or instead of), considering that most ASP is done with VBScript. 9.The cousre should be in the lab for the entire session so the option to work hands on is more flexible. CCSC NE Worcester, MA

34. 10.I'd have more hands on experience in the lab. The lecture is necessary, but there were times when an extra half hour on the computer to really try out what we were learning while someone was there to answer questions, as opposed to doing it completely on our own at home, would have been good. It's frustrating to be almost there and have to leave what you're doing. Of course, there's no ideal situation since I, for one, will sometimes forget what time it is and keep at something for hours. A little more time would be nice, however. 11.More time should be spent in the lab, alternating between lecture and hands-on work. It was disappointing to have to rush through execution of the example programs following the lecture and then move to a different classroom before many of us had finished "trying out" the code. 12.I might consider showing some examples in Cold Fusion - but NOT expecting anyone to learn 3 separate toolsets.... CCSC NE Worcester, MA

35. Next time: Fall, 2002 • Changing course to twice a week • Course already full second day of registration! • Probably stay with no textbook • Stay with content & structure • same strategy for VBScript, Cold Fusion • add paging, perhaps more on ER diagrams • XML? Flash (server-side connection)? ASP.net? CCSC NE Worcester, MA

36. Comments or Suggestions? • jeanine.meyer@purchase.edu • newmedia.purchase.edu/~Jeanine • courseinfo.purchase.edu • Preview • members.bellatlantic.net/~vze2s839 CCSC NE Worcester, MA