Introduction to Databases Principals by moti


									                                                                              Chapter One
           Introduction to Databases Principals

D         ata is generated in a company through various methods and operations. How we
          manage and use the data is the key. The transformation of data in to information
          is done through the formulation of queries.
Business operations, as well as management decisions rely on accurate data. The ability
to use data in an accurate and timely fashion is essential to for businesses to be successful
in today‟s competitive businesses arena. One of the key tools used in turning data into a
useful form, that being „information‟ is a database. The database not only stores data, but
also provides the following abilities: organization of data, insures data quality, secure
access of the data records and data retrieval in the form of queries and reports.

                                      How data is organized
                                      Databases are used to store and organize data. First, we
                                      would start by determining what pieces of data we
                                      wanted to store. Let's say we wanted to track the
                                      grades students receive in classes they are taking. Each
                                      piece of data is stored in the field (See Figure 1). Each
                                      field is used to store only one piece of data. For
                                      instance, one field could be used to store someone's
             Figure 1                 first name. This field would then only be used to store
                                      information about the first names of individuals.
Now that we have decided to store the first name, which is our individual pieces of data,
we then want to get these first names organized into tables. Each table is used to store
information about one person, place, thing, or concept. Tables are arranged into rows
(reading left to right) and columns (reading top to bottom). Each column in the student
table relates to one field, below First name Last name SSN Email are the columns.
Each row within a student table relates to one instance, and is referred to as a record. In
the example below, Student Table is used to store information about students. The first
record stores information about Sam Smith.
Database > consists of Tables > consists of Records > consists of Fields
Student Table

   First name             Last name            SSN                   Email
 Sam                    Smith             315-88-1111
 Jerry                  Adams             315-99-1111
 Shelly                 Hawkins           315-77-1111
                                              Table 1

Chapter One
Introduction to Databases Principals
Working with Fields
There are several important factors to consider when creating the fields used in the
database: Such as the fields „name‟, „data type‟, „size‟, and how this field may relate to
other fields within the same table. First, you need to choose the data type you want the
field to be, with access you cannot make up a data type. The common data types that are
available in most databases are listed in Table 2. The field sizes, in this case are of those
that are available in Microsoft Access. Within this lab manual you work with four main
data types: String, Numeric, Date, and Boolean.
String Fields
String fields are used to store numbers and letters. There are two main types of string
fields: fixed and variable length. In a fixed length field, you must specify the total
number of characters the field is to store. In a variable length field/memo field you do
not specify the length of the field, but there is an upper limit to how much the field can
store. In the case of Microsoft access this is up to 65,535 characters.
Numeric Fields
Numeric fields store values for any calculations being performed. However, numeric
values can be stored in text fields. At the same time calculations cannot be performed
against text values. Before jumping to the conclusion of storing a value in a numeric field
be is sure that it is a true numeric value. We become accustomed to thinking of a numeric
value as something that is composed of characters that are all numbers, but if the first
character in the numeric value is a zero, then this is not a numeric value. An example of
this is a Social Security Number where the first character is sometimes zero. If this were
stored as a numeric value the lead zero would be dropped. Because access is looking for
a number and zero has no value, so to speak. The next issue is thinking that a value is
numeric because we always see it composed of characters that are numeric. If you live in
the US the zip codes you see are composed of numbers, but in Canada they are composed
of numbers and letters.
There are different types of numeric fields, but they all fall into two types: exact and
approximate. Values stored in exact fields can be stored and retrieved as the same value.
Examples of exact field types are integer, long integer, and currency. The different exact
field type varies according to the precision of the type. The precision of an integer data
type can hold values from 32,768 to –32,768. Values that are greater than this cannot be
stored in the integer data type.
Approximate field types store values in exponential format (e.g. 1.48E24.) Examples of
approximate field type are: single and double. The range of values that a single field type
can store is 3.402823E38 to –1.401298E–45 for negative values, from 1.401298E–45 to
3.402823E38 for positive values, and 0. The precision of a single field type only allows
for six digits past the decimal point. Any time a value with more digits is stored into the
field, it is rounded to six digits.
The next set of data types to be examined is for storing date and time. This can be saved
as date only, time only or date and time together (commonly called a timestamp). Like
numeric values, date/time fields can have calculations performed against them. Examples

                                                                           Chapter One
           Introduction to Databases Principals
of calculations are computing the number of hours worked, the age of a person, the
number of day since an order has been placed, etc.
Logical Fields
Logical fields or Boolean fields are used to store true/false values. Care must be used
when accessing data using Boolean Fields. Although Boolean can store true and false
value‟s, there is third value that Boolean values can store, this being NULL. A Null
value is when no value has been assigned to that field.

The following table lists the some of different data types available in Access.
  Data Type                  Description                         Size
Text        (Default) Text or combinations of text and Up to 255.
Memo        Used to store large amounts of text.       Up to 65,535
                                                       characters. The size
                                                       of the field expands
                                                       as needed.
Number      Numeric data used in mathematical          1, 2, 4, or 8 bytes
            calculations. There are different types of depending on the
            numbers: Long Integer, Integer, and        numeric type
            Single. The default type is Long Integer. selected.
Date/Time   Date and time values for the years 100     8 bytes.
            through 9999.
Currency    Currency values and numeric data used in 8 bytes.
            mathematical calculations involving data
            with one to four decimal places.
AutoNumber A sequential (increased by 1) number or 4 bytes
            random number assigned by Access when
            a new record is added.
Yes/No      Used to indicate a True or False condition 1 bit.
            (Yes or No). Note a null value can also be
            stored in the field.
                                            Table 2

Working with Tables
Tables are used to store information about specific topic or field, such as, grades, orders,
products, and so forth. To reduce errors and to clarify the information being stored each
table is used to store information about only one field. Each table then stores one
instance or record of the topic in the table. For instance, if this table were used to store
information about students, then each record would be one student‟s information. Each
record is comprised of a collection of fields, which describe a specific student. Such as
the students first name, last name, address, city, and so forth.

Chapter One
Introduction to Databases Principals
Exploring Objects in Access
                               Your TA will show you how do open North Wind database
                               in Access.
                               Access allows the user to create database applications. To
                               do this, it stores information about the different objects that
                               make up an application: these objects are tables, queries,
                               forms, reports, pages, macros, and modules. The screen
                               pictured here shows the different objects within Access
                               and allows the user to add, modify, delete, and run those
                               Table - the table stores data in records and also data about
                               the primary key and indexes for the table.
Query - a virtual view of selected data from one or more tables. The query is created by
selecting the desired fields out of the different tables, calculated values between fields,
sorting the records, and calculated values from groups of rows of data.
Form - a customizable window in Access where information can be entered, modified,
deleted, viewed, or where actions can performed on the data.
Report - a collection of data formatted for printing, or viewing on the window. A report
does not allow data to be modified, just viewed. A report converts data into a useful form
called information.
Page - data that is formatted to be viewed or modified by a web browser.
Macro - a collection of actions to be processed together. Macros can be composed of
keystrokes, menu clicks, other macros, and code modules. Macros can be used modify
records, open forms and reports, and other actions.
Module - contains Visual Basic for Application (VBA) code. VBA code can perform
many of the same action as macros but has the advantage of being able to be debugged.
Debugging is a process of testing code to find errors or verifying code for proper
functionality. Access has the ability to convert macros to VBA code.
Viewing Tables
From the database window select the table objects. Using the left mouse button to double
click on the Shippers table. This will open the table in datasheet view. The shippers table
has three fields: ShipperID, CompanyName, and Phone. The table also contains three
instances or records.

                                                                          Chapter One
           Introduction to Databases Principals

Next, examine the structure of the table in design view. Change to the design view by
clicking on the title bar using the right mouse button. Select Table Design from the popup
menu. This window allows you to view the design of the fields and change the design if

                 Each field has properties that describe it, and how data is stored within
                 that field. For example, the ShipperID field is first named; ShipperID is
                 of Data Type Number and can have a description. In the field size, click
                 on the      button to show the different way a numeric value can be
                 stored. Next, click on the CompanyName field and notice that the field
                 type is text, and field size now shows 40. There are nine different field
                 types that can be stored in Access. Click in the Data Type for
                 CompanyName where it says “Text”, next click on the dropdown            to
                 show the different field types.
Viewing Indexes
Next, view the indexes for this table. Click on View on the menu bar, and from the menu
select Indexes. This will display the Indexes dialog box. For the Shippers table, there is
only one index, which is also the primary key.

Chapter One
Introduction to Databases Principals

Close the dialog box, and close the design view by clicking on the close button      in the
upper right hand corner of the window.
Viewing Relationships
Access maintains information about the relationships between tables. This information
can be viewed by clicking on Tools on the menu bar and then Relationships on the popup
menu. (If the relationships item does not appear, leave the mouse over the popup menu
for a few seconds and the menu will expand showing additional items.) The next window
will show the relationships that have been established between tables.

Note: The tables in your database will not be arranged as pictured above.
Access uses relationships to insure that the data entered into tables has the correct values
when that data is being pulled from another table. For example, when a customer places
an order, the value entered into the orders table for the CustomerID is only for a customer
that already exists. Find the relationship between the Customers and Orders table. A line
that is drawn between the two tables represents this
relationship. Right click on the line. From the popup                            menu select
Edit Relationships. The next dialog will show the details                        of the
relationship. Notice that it shows the two tables involved in the relationship, and that they
are related through the CustomerID field. Close the dialog box and the relationship

                                                                         Chapter One
           Introduction to Databases Principals
Creating a New Table
To add functionality to the NorthWind database, two new tables need to be added. These
tables will allow us to track Purchase Orders. In the database window, double click on the
Create Table in Design View.

                                          Figure 2

Next add the following Field Names and Data Types.
 Field Name       Data Type
 PoNumber         AutoNumber
 DateOrdered      Date/Time
 DateReceived     Date/Time

 AuthorizedBy     Number

 SupplierID       Number
 Freight          Currency
 ShipVia          Number

Chapter One
Introduction to Databases Principals

                                        Figure 3

Next click on the PONumber field and then click on the Primary Key          button on the
tool bar at the top of the window. Notice that the primary key icon now appears next to
the PONumber field. Close the window by clicking on the        icon in the upper right
hand corner of the window. Save the table as PO.

Chapter One
Introduction to Databases Principals
Lab Exercises
  1. How many tables are there in the NorthWind database?
  2. How many fields are in the Customers table?
  3. What field/s are in the primary key for the Customers table?
  4. What is the data type and field size of the Phone field in the Customers table?
  5. What are the names of the indexes in the Employees table?
  6. What is the data type of the HireDate field in the Employees table?
  7. What is the data type of the Salary field in the Employees table?
  8. What is the field size of the Notes field in the Employees table?
  9. What are the field/s in the Primary Key in the Order Details table?
  10. How many relationships does the Orders table share with other tables?
  11. How many records are in the Orders table?
  12. What is the name of the contact for the “Tokyo Traders” in the Suppliers table?
  13. What is the title of the person in the Employees table who does not report to
  14. How many fields are in the primary key in the PODetails table?
  15. What is the title of the supplier who is in region “OR”?

                                 End of Chapter one


To top