Database Technology and Church Management

1. Database Technology and Church Management MSCM 8630 IT for Church Management Dr. Wagner October 9, 2008

2. This week in class… Two on-line sessions this week Thursday night 7:30 p.m. ET Tuesday morning 9:30-10:30 ET (office hour) No class during fall break Next sessions on Tues 10/21 and Thurs 10/23 Continue reading Go over articles from CC magazine Nicht vergessen! 2nd assignment due Discuss database technology Do Access (or other Database package) tutorials in Element K as needed Third assignment posted (database)

3. Questions about class so far?

4. Calendaring in the Church(article) 3 calendaring needs in churches Resource calendaring Rooms and equipment Publication calendaring Flyers, bulletins Web authoring? Personal/team calendaring MS Outlook/Exchange? Portable, increases team collaboration, backed up Access via PDA or smartphone? “To Do” lists? Challenges?

5. Is Your Church Up Against the 3 C’s?(article) • Communication, Community Outreach, Coordination of Events • Past IT solutions were too complicated and expensive • Internal communications supported with www.calendarcompanion.com • Reminders, prayer list, real-time works with www.sermoncompanion.com • Unlimited space, easy to administer • Reservation system • www.eventpro.net

6. Database Technology Why is this important? What are the basics? Look at some examples Practice in Access

7. What kind of data do churches manage? • Sermons, research prep • Letters, email – internal and external • Databases • Member lists, contacts, contributions, baptisms, attendance • Problems with multiple databases • Multimedia files – photos, videos, audio, bulletins • Governance docs – minutes, agendas, meeting notices • Custom templates, programs, etc.

8. Employee Record 1 Employee Record 2 Employee Record 3 Employee Record 4 Name SS Salary Name SS Salary Name SS Salary Name SS Salary Data Data Data Data Data Data Data Data Data Data Data Data Logical Data Elements Personnel Database Payroll File Benefits File

9. Database Management Systems • Program(s) through which users interact with database • Popular DBMSs include • Access • Filemaker Pro • Oracle • DB2 • SQL Server

10. Demo of MS Access Application • Northwind Foods database • Available for download on WebCT

11. Why not just use spreadsheets? • Redundancy • Duplication of data or the storing of the same data in more than one place • Difficulty accessing data • Lack of powerful query capabilities • Limited security • Size limitations

12. Premiere Products Sample Order

13. Sample Orders Spreadsheet

14. Premiere Products Required Information • Sales Reps • Sales rep number, last name, first name, address, total commission, commission rate • Customers • Customer number, name, address, current balance, credit limit, customer sales rep • Parts Inventory • Part number, description, number units on hand, item class, warehouse number, unit price

15. Premiere Products Customer Order • Order • Order number, order date, customer number • Order line • Order number, part number, number units ordered, unit price • Overall order total • Not stored since it can be calculated

16. One-to-Many Relationship

17. Types of Relationships • One-to-one (1:1) • 1 laptop is assigned to one teacher or student • Foreign key can be stored on either side of relationship • One-to-many (1:M) • 1 rep is assigned to 1 or more customers • Foreign key is stored on the “many” side • Many-to-many (M:N) • A student can take multiple classes and each class can have multiple students in it • Create a bridge table with a composite key using both of the primary keys from the linked table

18. Rep and Customer Tables

19. Orders and OrderLine Tables (con’t.)

20. Part Table (con’t.)

21. Premiere Products Data Model

22. Building a Database • Database design determines the structure of a database • Design entered into DBMS during construction • Tables – stores data • Forms – screen objects used to maintain, view, and print from a database • Reports – provides formatted output • queries • Switchboards – a set of special forms used to provide controlled access to the data, forms, report and other objects in a database

23. Part and Order Forms

24. Parts Report

25. Main Switchboard

26. Main Data Switchboard

27. Let’s try some database practice MS Access 2003 You can also get to a version of this by going to https://citrixweb.villanova.edu Login with VU userID and PW Navigate to “General Applications” folder You should see both Office 2003 and Office 2007

28. Query-by-Example (QBE) • Query • Questions represented in a way the DBMS can recognize and process • QBE • Visual approach to writing queries • Used in MS-Access and other personal DBMSs

29. Types of Relational Queries • Single or Multiple Tables • must show link • Simple or Compound Conditions • “and” vs. “or”

30. Access Query Operators • = • <, >, <=, >= • <> • like • not • *, or ? (wildcards in Access)

31. Access Query Operators • Built-in operators • Max, min, count, sum, avg, std, var, first, last • Computed fields • Joining tables

32. Simple Queries

33. Assignment for next week • Start using the Access tutorials on Element K (if necessary) • Start thinking of project ideas and write up a 1 page proposal • Work on next database assignment • Not due until Oct 27th • Create 3 tables; Members, Groups, Priests • Populate with dummy data • Create links between them • Create 1 report, 1 form, and 1 saved query

34. Questions?