Introduction to Computing

1
Databases(Microsoft Access)
Ghulam Murtaza CSCS100 Forman Christian College
Slides Created by Numan Sheikh, these slides
have been adapted and modified from slides by
Amjad Hussain Zahid
2
What is a database?

• A database is a storage space for content/
  information (data)
• MS Access 2003 is a database software used to
  mange data that can be organized

3
But what is data? And where is it now?

• Data is factual information about objects and
  concepts, such as
• measurements
• statistics
• You can find it in
• filing cabinets
• spreadsheets
• folders
• ledgers
• lists
• colleagues memories
• piles of papers on your desk

4
Different parts of a database

• The smallest unit of data organization is called
  a field. A field consists of a specific category
  of data such as a customer name, city, state, or
  phone number.
• A group of related fields that describe a person
  place, or thing is called a record.
• Table contains all of the raw data within the
  database in a spreadsheet-like view.
• Query allows the user to select a subset of
  fields or records from one or more tables.
• Reports

5
Learning Database Terminology

• Access is structured in terms of tables that have
  rows and columns and look a lot like an Excel
  worksheet.
• Columns in the table, which are called fields,
  identify the types of data that are stored in the
  table Last Name, First Name, Middle Initial,
  Street, City, and State in the example shown
  below.
• Each row in the table, which is called a record,
  is a set of information about a particular item
  in the database a particular person in the
  example shown below.

6
Learning Database Terminology
7
Learning Database Terminology

• A key field is a field that contains unique
  information for each record, such as roll number,
  national ID card number etc.
• A collection of records for a single subject,
  such as all of the customer records, is called a
  table.
• A collection of tables associated with a general
  topic is called a database.

8
Learning Database Terminology
Student Roll Number Student Name CGPA
ABC XYZ 3.87
EFG HIJ 2.54
9
Access Objects and their purpose

• Table contains all of the raw data within the
  database in a spreadsheet-like view
• Query provides a spreadsheet-like view of the
  data similar to tables, but allows the user to
  select a subset of fields or records from one or
  more tables.
• Form Provides an easy to use data entry screen
  which often shows only one record at a time.
• Report Provides a professional printout of data
  that may contain enhancement such as headers,
  footers, graphics, and calculations on groups of
  records.

10
Identify the Data Fields
11
Specify Key Fields

• Each table has a field that uniquely identifies a
  record
• Student ID,
• Employee ID,
• and Customer ID.

12
Microsoft Access
13
Starting Microsoft Access

• When Access starts you will see a screen that
  looks something like

14
Starting New Database

• First Create Tables (using Design View)
• Access Window Elements

Database toolbar
Database title bar
Database window
Database window toolbar
15
Create Table

• You then will see the Design View for the new
  table.
• The Design View allows you to enter names of the
  fields in the table and to specify their data
  types. A data type indicates the kind of data to
  be storedfor example, numbers, text, or dates.
• It is best to select the type that most closely
  matches the kinds of values you are storing in a
  field.

16
Create Table

• When you design a table, the first thing you do
  is to specify the Field Names and the Data Types
  the type of data that is allowable for each
  field. A few common data types are listed below

17
Create Table
18
Create Table

• The first Field Name we will enter is ID45, the
  numbers we will use as primary keys to uniquely
  identify each record. Enter ID45 as the first
  field name and change its Data Type to
  AutoNumber.

19
Create Table

• Setting up Key Field
• We need to specify that this field holds the
  primary key for the table.
• Right-click on the Field Name. In the menu that
  pops up, select
• Primary Key. When you do so, you should see a
  small key icon in
• the column to the left of the Field Name.

20

• Enter Label as the second field name. The
  default Data Type is Text, which is appropriate
  for this field.
• You will see a list of Field Properties at the
  bottom of the Design View window. The default
  Field Size for a Text field is 50, which is much
  bigger than a record label name is ever going to
  be. Change the Field Size to 20.
• When you have entered them, click on the Save
  icon to save the table.

21

• To view the Database window again you can click
  on the Database Window icon in the main toolbar.
• You also can minimize the Table window by
  clicking on the Minimize icon in its upper right
  corner.

22

• The Database window will look the same as before
  except that a new table is now included in the
  list of tables in the database.

23
Adding Data to a Table

• Now that the tables have been created we need to
  populate them with data. We will start with
  tblIssue. Double-click on the table name in the
  list of tables.

24
Entering Records
25
Editing Records
Ascending and Descending Order
Delete Record
New Record
26
Querying the Database

• First we must specify which table contains the
  information we need.
• In the Show Table window that appears, we see a
  list of tables in the database under the Tables
  tab.
• Select the table from which the information is
  required.
• Click on the Add button.
• Then click on the Close button on show table
  window.

27
Querying the Database

• Add the fields (which you want to display) to the
  Design Grid.
• For example, select StId from the list of field
  names.
• Hold the mouse key down on the arrow and drag it
  down to the first Field in the Design grid.