data modelling microsoft access basics l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Data Modelling & Microsoft Access Basics PowerPoint Presentation
Download Presentation
Data Modelling & Microsoft Access Basics

Loading in 2 Seconds...

play fullscreen
1 / 12

Data Modelling & Microsoft Access Basics - PowerPoint PPT Presentation


  • 287 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Data Modelling & Microsoft Access Basics' - johana


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
data modelling microsoft access basics

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

introduction what is access
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

examples of database applications in reality
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

how to apply database to solve business problems
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

conceptual model design the most critical phase
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

analyze business situation
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

conceptual model design er modeling

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

conceptual model design er modeling8

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

design and create database
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

create a new table in an access database
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

design and create database11
Design and Create Database
  • Now get hands dirty with Access

Access Tutorial, YF

additional resources
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