CSC321: Database Management Systems

1. CSC321: Database Management Systems Dr. Zhen Jiang Computer Science Department West Chester University West Chester, PA 19383

2. Why this class? • Job hunting • WCU graduated, Database, Software Engineering, … vs. • UPenn, Temple, Drexel, … • Something help you to stand out! — How do you like to be introduced?

3. Research project • Critical thinking, communication, well-plan, etc. • Real competition, deadline, pressure • More realistic system, complex programming, for instance, http://www.cis.temple.edu/~jiang/RSU_van.pptx. • Student opportunities: http://www.cs.wcupa.edu/~zjiang/student_info.htm • Sponsorship • NSF, NSA, etc • Industry interns

4. Student Opportunities Collaboration at international level Course project NSF REU Collaboration at inter-disciplinary level Collaborative work at inter-department level

5. Industry partners • Such as Gnostech, Chase, etc. • Invited talks in courses • Seminars or guest speeches • Curriculum review/assessment • Permanent intern opportunities

6. Goal • Competitive students entrepreneurially and computationally empowered citizenries • Temple, NYU, etc. • IBM, SAP, Vanguard, Bell lab, etc. Key • Attract students and keep them continuously working hard Strategy • Real-world computing paradigms and principles in an obstacle-less learning

7. No pain, no gain • Need to handle those things coming from pressure: frustration, upset, madness, etc. • Looking for solution, not excuse. • Deadline!

8. Class website (including ppt)+ google search • Hands-on experience • Group discussion • Happy ending!

9. Database, Database management system, and Access • Shelly, Cashman, Pratt, “Microsoft Office Access 2007 Comprehensive Concepts and Techniques”, Course Technology

10. Access • <Start>  <All program>  … <Microsoft Office Access 2007>

11. Input each piece of information manually

14. √

15. Design view to change the format (especially useful for currency and date input)

17. Database Systems & Access • Page: AC14 • Page: AC44 • Exercise 1: http://www.cs.wcupa.edu/~zjiang/csc321_lab1.doc

18. Creating a report • Report wizard, page AC50-AC56

19. Input & Output • Load/import data from external resource, Page AC211

20. Plain text file

27. Excel file (xls or xlsx)

30. Querying • Creation (Query wizzard) • Save • Use

31. Wildcard (*, ?), page AC87 • Use criteria for a field not included in the result list, page AC88 • Parameter Query, page AC89 • Join table, page AC103 • Comparison Operator, page AC94 • Compound criterion (AND, OR), page AC 95 • Sorting, page AC97 • Omitting duplicates, page AC 100 • Multiple keys (high priority from left to right), page AC101 • Top-value query, page AC 102 • Calculated field, page AC 113 • Calculating Statistics (built-in statistics, group), page AC 117

32. Exercise 2: http://www.cs.wcupa.edu/~zjiang/csc321_lab2.doc

35. Show the name and age and salary of all employees who are younger than 25.

36. Double click the items to include in the list

40. Add criteria to meet the certain requirement

42. Datasheet view • Design view

44. Hidden column for criteria (not displayed in the list)

45. Wildcard (*, ?), such as “Ber*” in City field • Use criteria for a field not included in the result list, disabled show property • One table, decide it early and add all fields at the beginning • Comparison Operator, such as >10000 in amount paid field • Compound criterion (AND => 1 row, OR => another row) • Sorting • Omitting duplicates, “unique record” in property sheet • Multiple keys (high priority from left to right) • Top-value query, “return” in top design view (very early stage) • Calculated field, “zoom” in shortcut menu (e.g., type “Total Amount:[Amount Paid]+[Current Due]”) • Calculating Statistics (built-in statistics, “group by”)

46. Show the Enum, name, salary of all employees whose names have 5 letters.