Data Modelling & Microsoft Access Basics

1. Data Modelling & Microsoft Access Basics Agenda • What is Access • Examples of Databases • Using Access as a Tool for Business • Analyze Business Situation • Data Modeling • Create Database • Develop Applications • Create the First Database! • Additional Resources Yulin Fang February 9th, 2005

2. Introduction – What is Access? A database management system, a program that facilitates the storage and retrieval of structured information on a computer’s hard drive. • Access is the most well-known desktop DBMS! Learn Access before approaching any of the databases below • Examples of industrial-strength databases that are widely used: • Oracle • DB2 • SQL Server • Informix • MySQL • Why not Excel • Excel is great for table making, calculating, and reporting on a limited set of data; • Excel doesn’t work well when you need to view a huge amount of data from different angles; However, Excel does have a very “basic version” of Access Database – Pivot table. But that is all Excel can do that is anywhere closer to Access • More importantly, Excel cannot eliminate redundancy, enforce data integrity Access Tutorial, YF

3. Examples of Database Applications In Reality Database applications are everywhere … • On your computer • Outlook • In your community • eZone (Account Information, Course Information, Exam Information) • Online library information • In the business world • ERP (e.g., SAP, PeopleSoft) • SABRE (e.g., air ticketing system) • E-commerce (e.g., www.eBay.com; www.expedia.com ) • …. Nearly anything on the web where you find a text box or drop-down list to fill in Access Tutorial, YF

4. How to Apply Database to Solve Business Problems Design conceptual model Analyze business situation Design and Create Database Develop Applications • What are the business needs? • What information is required? • Where to find these information in the business situation? • Develop queries • Develop forms • Develop reports • Develop web-pages • Specify relevant entities and relationships between the entities • Use Entity-Relationship modeling techniques to design a conceptual model • Create tables • Define primary key • Feed in data • Establish relationships between tables Pencil and Paper Using Access (or other dbs) Access Tutorial, YF

5. Conceptual Model Design - The Most Critical Phase … One thing I'd emphasize [with my dear students] is that many people mistakenly begin making a database by creating tables and fields without a clear plan. This is fine in the spreadsheet world, but a high quality database requires careful planning up front, and that's why data modeling is such a critical aspect of database development….. (Neufeld, 2005) Access Tutorial, YF

6. Analyze Business Situation Business needs • You are an internal budget analyst in a university • Professors share a number of budget pools (e.g., text books, phone calls, conference expense, other business traveling) • Your supervisor wants you to keep track of: • Which professors have claimed what expenses? • How much have they spent? • How a particular budget pool is used? • Other examples: • Consultants work on projects; Students take classes Information needs • What information should be captured? • Professor • Budget • Where to find these information? • HR • Finance Access Tutorial, YF

7. 1-to-1 1-to-Many Many-to-Many Conceptual Model Design – ER Modeling • Entity: Real-world object distinguishable from other objects. An entity is described (in DB) using a set of attributes. • Entity Set: A collection of similar entities. E.g., all employees • All entities in an entity set have the same set of attributes • Each entity set has a key attribute • Relationship: Association among two or more entities. • Three types of relationships … Access Tutorial, YF

8. First Name Last Name Name Budget EID BID • Employee • ID • First Name • Last Name • Title • Hire Date • … • Expenditure • Who • When • How much • What • … • Budget Code • ID • Description • Total Budget • … Spend Employees Budget Code What How much When Conceptual Model Design – ER Modeling E-R Model Database View Access Tutorial, YF

9. Design and Create Database In Access, you can create Tables, Queries, Forms, Reports, Data Access Pages, Macros, and Modules. Table is where data is stored • Tables: the main place where data is stored. They work very much like a spreadsheet. • Queries: filtered versions of a table of data. They are used for organizing data by sorting or only including records with a certain value. • - Forms: a way to make entering and searching for data more straightforward. • - Reports: a good way to present data for printing, emailing, or onscreen presentations. • - Data Access Pages to export data in a form-like setup. This is commonly used to show your data in a web page. • - Macros: a way to encode your database to automate certain tasks. For example, a macro could print out a copy of a report every time it is opened. • - Modules are an interface between the Visual Basic coding language and your database. Modules are most often used by database administration Access Tutorial, YF

10. Create a new table in an Access database Design and Create Database • Create and save a new database • Create a table in design view • Specify fields • Specify data types • Enter new data Access Tutorial, YF

11. Design and Create Database • Now get hands dirty with Access Access Tutorial, YF

12. Additional Resources • Conceptual design using Entity-Relationship Modeling • http://www.cs.wisc.edu/~dbbook/openAccess/firstEdition/slides/pdfslides/mod5l1-2.pdf • A comprehensive guide teaching you how to build a database for business transactions using Access • http://mis.bus.sfu.ca/tutorials/MSAccess/tutorials_main.asp • A quick start on how to use Access • http://www.bcschools.net/staff/AccessHelp.htm • An introduction to Database Management Systems • http://www.cs.wisc.edu/~dbbook/openAccess/thirdEdition/slides/slides3ed.html Access Tutorial, YF