Introduction to Databases with Access 2010 Instructor: Aarthi

Introduction to Databases with Access 2010 Instructor: Aarthi

Introduction to Databases with Access 2010 Instructor: Aarthi Natarajan April 26th, 2017 Based on slides by Robert Grauer, Keith Mast, Mary Anne Poatsy COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. Objectives What are databases, and how do they differ from Excel tables, or table-structured spreadsheets? Relational databases Keys, tables, foreign keys Navigate among objects in Access database Practice good database file management Back up, compact, and repair Access files

Create filters Sort table data on one or more fields Know when to use Access or Excel to manage data COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 2 What is data? Data facts that can be recorded and have implicit meaning (Elmasri & Navathe) Today data is being generated at an exponential rate Financial market data, posts to social media sites, growing logs of web transactions, computation physicsBIG Data Rate at which data is generated (rapid, exponential growth) Rate at which data flows from different sources COMP1000 UNSW

Diversity in data types structured data (financial market data), semi-structured (XML, web logs, email), unstructured (audio, video streams) 3 What is a database? Data by itself is not very useful. Give a context to data to transform data into information e.g., the numbers 45,55,67 do not mean much, but given a context such as these are the marks of students in COMP 1000, this is now information DATA -> INFORMATION -> DECISION

A database is collection of related data, which is organised such it can be easily accessed to produce useful information, can be managed and updated. Databases are everywhere The Internet uses databases extensively. Every time you are asked to input data, youre accessing a database e.g., Google, Ebay, Amazon, Book Depository, Medicare, Tax Office, credit card, debit card, iTunes shop, Facebook, Twitter, WebCMS, UNSW Library card, Every time you use a loyalty card, you're inputting information about your buying habits into the database of the company in question. COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 4 Database concepts

A database is a coherent collection of related data A Database Management System (DBMS) is an application or collection of programs that allows users to create and maintain a database A database and DBMS are collectively referred to as a Database System A Relational Database Management System (RDBMS) is a special type of DBMS that: Stores data as tables Allows the user to create relationships between tables Applications access the database through the DBMS by: Defining queries that causes data to be retrieved Through transactions that cause some data to be written or deleted from the database COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 5 More database concepts

A data model describes how the data is structured in the database A database schema adheres to a data model and provides a logical view of the database, defining how the data is organised and the relationships between them and is typically set up at the beginning A database instance is the state of the database at a particular instance of time COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 6 Data Models There are several types of data models Relational model a data structure where data is stored as a set of records known as tables a table consists of rows of information (also called a tuple) each row contains fields known as columns

Document model data is stored in a hierarchical fashion e.g., XML Object-oriented model a data structure where data is stored as a collection of objects Object-Relational model a hybrid model that combines the relational and the object-oriented database models COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 7 Creating a database Choose a data model for the database Set up the structure of the database by defining a database schema for the database (e.g., for a relational data model, define the tables, rows and columns or field names and types of fields, constraints and

relationships) Create the initial state of the data by loading data into the database After this, typically schema doesnt change much, but data changes rapidly as new data is loaded or existing data is updated COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 8 Microsoft Access An application in Microsoft Office that is used to store and maintain data in a database Access is a RDBMS, using which you can: Create a database Create and edit tables in the database Create relationships between tables Build forms and reports that use these tables)

COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 9 Objects Objects The main components in Microsoft Access RDBMS that are created and used to make the database function Four most commonly used object types Tables (data) Queries (extract information from data) Forms (enter data into tables) Reports (meaningful output) Two less commonly used object types Macros Modules COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall.

10 Navigating Access Interface Navigation Pane Organizes and lists the database objects in an Access database. Objects can be tables, queries, forms, reports, macros or modules Access Ribbon Contains the icons that enable you to perform functions to maintain your database COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 11 Working with Table Views Access provides two different views to view a table

Datasheet View : A grid containing rows and columns similar to Excel and allows user to add, edit, delete records in a table Design View : allows user to create tables, add/delete fields, modify field data types and associated properties Data types define the type of data stored in a field such as text, number, currency, etc., e.g, to store the salary of an employee, create a field called salary and select the data type as currency Field properties define additional characteristics of fields e.g., pay rate to be >= $25 (defined using a validation rule) COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 12 Design View Custom ers table in

design view. Field names along left side. Each field has a COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 13 Datasheet View Custom ers table in

datashe et view. Field names along top. Data for first 27 records below. COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 14 Tables Objects Tables Fields (Columns)

Records (Rows) COMP1000 UNSW School Database Student Table Course Table S-ID S-FN S-LN C-ID C-Name S01 Bob Woods C01 Intro to Microcomputer Applications S02 Ted Trees C02 Computer Applications for Business S03 Carol Rose C03 Introduction to Computer

Science S04 Alice Lilies C04 Introduction to Programming Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 15 Tables in More Detail Records (one per line) Field Names ISBN Number 0-13-503393-4 0-13-503362-2 0-13-504016-9 0-13-503982-2 0-13-502344-1

Title Exploring PowerPoint Exploring Access Exploring Excel Exploring Word Introduction to Win95 Author Grauer/Barber Patel/Rao Flanagan Cornell/Arnol Shepherd

Year 1996 1995 1997 1996 1997 List Price $28.95 $35.50 $18.00 $42.25 $58.00 Publisher Prentice Hall Prentice Hall Addison Wesley Prentice Hall Addison Wesley

Data is stored in a database in the form of tables, a collection of related records Each row represents a record. We can add, remove and alter records. A record is a complete set of all data elements (fields) about one person, place or concept e.g., first name, last name, email address of a student Some times, records are also known as tuples. Each column represents a field. A field is the smallest data element contained in a table A field stores a specific type of data, i.e. number or character string or dollar value The field (or combination of fields) that is unique for each record is also known as primary key COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 16 Primary Keys A primary key is a field (or combination of fields) that is used to uniquely identify records.

The Autonumber data type is often used to create unique values by automatically incrementing the value each time a new record is created. An example of a primary key would be a students ID number or a drivers license number. COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 17 Query A query is an Access object that allows a user to ask questions about the data in the tables and the answer is shown in the query results A query displays records that meet a certain criterion

A criterion is a number, text phrase or an expression that allows records to be searched and filtered based on their field values e.g., the following criteria lists all records where Publisher = Prentice Hall , and Year > = 1996 ISBN Number Title Author Year List Price Publisher 0-13-503393-4 Exploring PowerPoint Grauer/Barber 1996 $28.95 Prentice Hall 0-13-503982-2 Exploring Word Cornell/Arnol 1996 $42.25 Prentice Hall

New records can be added or records can be modified based on a search criteria. For example, the following adds $10 to all the books published in 1997 ISBN Number 0-13-503393-4 0-13-503362-2 0-13-504016-9 0-13-503982-2 0-13-502344-1 COMP1000 UNSW Title Author Year List Price Publisher Exploring PowerPoint Grauer/Barber 1996

$28.95 Prentice Hall Exploring Access Patel/Rao 1995 $35.50 Prentice Hall Exploring Excel Flanagan 1997 $28.00 Addison Wesley Exploring Word Cornell/Arnol 1996 $42.25 Prentice Hall Introduction to Win95 Shepherd 1997 $68.00 Addison Wesley Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 18 Working in Storage and Memory Access works primarily from storage (hard-disk), unlike other

MS Office applications such as Word, Excel and PowerPoint When you change the content of a field (e.g., phone number), Access saves the change as you soon shift focus to another record Changes to the design of a table, query, report or form must be saved Multiple users can work on an Access database simultaneously as long as they are not working on the same record. One person can be adding records to the Employees table, while another queries the table. COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 19 Tips on Database Management Database files should be named using meaningful names Database files should be stored in meaningful

folders and subfolders Back up your files! COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 20 Compacting & Repairing a Database (1) Access databases increase in size over time Compacting Avoids loss of data Recovers unclaimed space Defragments fragmented databases Repairs corrupt databases Should compact your database daily COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall.

21 Compacting & Repairing a Database (2) Compact & Repair option COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 22 Backing Up a Database (1) Backups are critical to a database Access provides a default file name, including the original file name and adds on the current date Default extension for Access databases is

accdb COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 23 Backing-up Your Database (2) Back Up Database option COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 24 Demonstration 1 Open and Edit

Start Access, open a01h1traders.accdb and save a copy as a01h1traders_demo Click Enable Content Click Tables group in Navigation pane if necessary Double-click Employees table to open it Click Last Name field in row 4 (Peacock), double-click, replace with another name. Press Tab. Change name. Note pencil icon at left (means changed, not saved). Click in row 3. Pencil symbol vanishes change saved. Experiment with changing and undoing. Click Close (x at top right) or right-click Employees tab and click Close. COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 25 Demo 2 use Access form, add records

Click Tables group to collapse it, click Forms group to expand it. Double-click Products form to open it. Move between records using navigation buttons, bottom of window. Click Home / Find / Find. Type "Grandma" in Find What box, click Match arrow, select Any Part of Field. Click Find Next. Close Find dialogue box. Click New Blank Record on navigation bar. Enter BillW's Pecan Pie / Grandma Kelly's Homestead (click arrow) / Confections (click arrow) / 1 / 25.00 / 18 / 50 / 20 / No (check box not checked). (Product ID automatically assigned.) Click in this record. Click File / Print / Print Preview. Click Last Page in navigation bar, then Previous, to view record just

entered. Click Close Print Preview. Close Products form. COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 26 Demo 3 table/ form connection, delete record Click Forms group to collapse it, click Tables group to expand it. Double-click Products table to open it. Click Last Record in navigation bar. Navigate to 5th record, Chef Anton's Gumbo Mix Click Home / Find / Find. Type "Grandma" in Find What box, click Match arrow, select Any Part of Field. Click Find Next. Scroll right to Discontinued field. Click row selector box for 5th record. (Highlights with gold border.) Click Delete in Records group. Read error message. Click OK. Navigate to last record. Click row selector box. Click Delete. Read warning message. Click No.

COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 27 Demo 4 Compact & Repair, Backup database Click File tab. Click Compact & Repair Database Defragments and eliminates waste space. Click File / Save & Publish. Double-click Back Up Database. You do not want to lose your data if the original becomes corrupted. Click Save to accept default backup name with today's date. COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 28

Filters A filter displays a subset of records based on specified criteria Will always display the entire record selected based on criteria selected All data extracted must be contained within a single table Hides unwanted records i.e., records that do not match the selection criteria, but does not delete them COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 29 Types of Filters Filter by Selection Filter by Form

COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 30 Filter by Selection Displays only records that match selected criteria To implement a Filter by Selection Click in any cell field that contains the criterion on which you want to filter Click Filter by Selection in the Sort & Filter group Select Equals criterion from the list of options Only the records that match the selected criterion will be displayed 'find records like this one' COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall.

31 Filter by Form Displays table records based on multiple criteria. Allows the use of AND and OR conditions Allows the use of comparison operators Comparison operators include: Greater than Greater than or equal to COMP1000 UNSW > Less than >= Less than or equal to Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. < <=

32 Filter using ANDs and ORs ANDs restrict selection criteria ORs expand selection criteria COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 33 Filtering & Sorting Demo 1 Open a01h1traders_demo again Open Customers table, go to record 4, replace Contact Name field with COMP1000 Student Find & click City field in this record ("London") Click Sort&Filter>Selection>Equals "London" Click Sort&Filter>Toggle Filter Click Sort&Filter>Toggle Filter again

Find & click a Contact Title of Sales Representative Click Sort&Filter>Selection>Contains "Sales Repre" Check results, Toggle Filter, close Customers table (say No to "Save design changes?") COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 34 Filtering & Sorting Demo 2 Click Tables group in Navigation pane, then Queries group Locate & double-click Order Details Extended query Sort&Filter>Advanced>Filter by Form Click row 1 under First Name field, click First Name arrow Select our first name from the list. Ditto for Last Name. Find Extended Price field, click row 1, type "<50". Click Sort&Filter>Toggle Filter File>Print>Print Preview Click Print Preview>Close Print Preview.

Close Order Details Extended query (don't Save changes) COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 35 Filtering & Sorting Demo 3 Navigation pane>Queries group to close it Open Tables group, then double-click Customers Click a value in the Customer ID field, then Home>Sort&Filter>Descending. Find Country and City fields, click Country column heading Left click and hold Country column heading, note blue bar, drag till line between City and Region fields, release. Click a city, click Sort&Filter>Ascending Click a country, click Sort&Filter>Ascending Close Customers table (don't save changes) COMP1000 UNSW

Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 36 Filtering & Sorting Demo 4 Open Products table Sort&Filter>Advanced>Filter by Form Supplier row 1, Supplier arrow, scroll, select "Grandma Kelly's Homestead" Click Or (bottom of window), Category row 1, Category arrow, Confections Click Sort&Filter>Toggle Filter Close Products, don't save changes File>Compact&Repair Database File>Save&Publish>double-click Back Up Database>Save COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 37

When to use a Database Databases are ideal for maintaining data related to objects (e.g., student records, inventories, etc.) This data can be queried, sorted, etc. and the relationship between the objects and their attributes is maintained. While they can be used to perform numeric calculations, they are not usually optimised for this task. COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 38 Databases One important feature of a database is that the stored information is organised and structured in a particular way Usually databases try to allow for fast retrieval of

information (via queries) even when they contain vast amounts of data It is important to pay careful attention to the design so as to structure the storage of data appropriately COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 39 Differences Between Spreadsheets and Databases In a spreadsheet, changes to elements may affect other elements however the elements are not related in any other way. A database groups the attributes of an object together and treats them as belonging to that object. In a database, elements have a type (e.g., text,

number, date, yes/no, etc.) COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 40 Problems that Databases Try to Deal With Redundancy and inconsistency of data Different copies of information may exist (redundancy) and some attributes may differ (inconsistency) Access May need to allow/restrict access to different portions of data Multiple users accessing/modifying data at the same time Integrity Constraints on data that can be stored (e.g., only Gold status members entitled to a discount)

COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 41 When to Use a Database Rather than a Spreadsheet? Database stores information in a structured way as relations: attribute values of a single object Different attributes have a data type Spreadsheet doesnt maintain information in this way (only formula dependencies) and has only a few data types Use DBMS when it is important to maintain attributes of objects as single entities, and relationships between objects Spreadsheet is normally useful for calculations and visualisation of information COMP1000 UNSW

Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 42 Excel or Access? It is better to use Excel, if you: Are more comfortable with its ease of use Only need one worksheet to handle all of your data Have mostly numeric data Flat (non-relational) data Require subtotals and totals for a worksheet Want to use what if scenarios on data Need to create complex charts and/or graphs COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 43

Access or Excel? (continued) It is better to use Access when you: Need multiple related tables to store data Have a large amount of data Need to connect to and retrieve data from external databases (such as Microsoft SQL Server) Need to group, sort, and total data based on criteria Need multiple users to have access to application simultaneously Need built-in tools to help organize data Ability to create relationships between tables COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 44 Summary Access works best for large amounts of data stored in multiple related tables using relationships between tables

Access allows you to sort and filter data Access allows you to compact and repair and back up your data Practicing good database file management will improve the quality of your database COMP1000 UNSW Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 45

Recently Viewed Presentations

  • Increase in Life-Expectancy and Saving Behaviour

    Increase in Life-Expectancy and Saving Behaviour

    Les plaintes adressées par les PED le sont à 55 % contre des pays industrialisés. Logique mercantiliste de l'OMC = logique « donnant - donnant », volonté d'améliorer les termes de l'échange ; les coûts sur les autres pays d'une...
  • ICARE Project Module: ICARE Project Module: Post Approval ...

    ICARE Project Module: ICARE Project Module: Post Approval ...

    identifying monitoring strategies. Think individually about specific monitoring strategies that could be used to ensure a protocol is being conducted in accordance with the IACUC approval.
  • Cognitive Science 101A - cns.nyu.edu

    Cognitive Science 101A - cns.nyu.edu

    Role of Attention in FIT Attention moves within the location map Selects whatever features are linked to that location Features of other objects are excluded Attended features are then entered into the current temporary object representation Evidence for FIT Visual...
  • Chem 30BL - Lecture 1a

    Chem 30BL - Lecture 1a

    Isolation of Chlorophyll andCarotenoid (Pigments from Spinach) using Column Chromatography. Aldol condensation . Oxidation of Borneol to Camphor . Distillation and Gas Chromatography. Molecular Modeling . Infrared Spectroscopy. 13C NMR and DEPT Spectroscopy
  • Chapter 3 - Scientific Measurement

    Chapter 3 - Scientific Measurement

    12 inor 1 ft. 1 ft 12 in. Dimensional Analysis. Dimensional analysis is a way to analyze and solve problems using the units of the measurements. ... Convert 0.227 nm to centimeters. Convert 1.3 x 104km to decimeters. Convert 1325...
  • 4 Puertas a la sanidad interior - Weebly

    4 Puertas a la sanidad interior - Weebly

    rata con los espíritus familiares que sin saberlo heredamos y condicionan nuestra vida espiritual, los mandatos que desde niños inconscientemente cumplimos, los pactos y promesas que nuestros padres y abuelos hicieron y ataron nuestras vidas.
  • Deputy Principals and Administrators Briefing Years 7 to

    Deputy Principals and Administrators Briefing Years 7 to

    The teaching, learning and assessment process. Highlights the interconnection between the curriculum, developing teaching and learning activities, linking assessment to the curriculum and reflective teaching practice, and collecting evidence for reporting and assigning a grade using the evidence collected.
  • Market Structure - Jollibee Foods Corporation

    Market Structure - Jollibee Foods Corporation

    Population growth rate more than 4% per year. Consumer preference: quick, quality, clean, convenient, and affordable dining experience. Customization products (e.g. rice meals for Filipinos, potato fries for Americans)