Introduction to Databases using MS Access by oneforseven


									Introduction to Databases using
            MS Access
    Concepts, Creating tables and
• By the end of today you will be able to
  – Create a table in MS Access
  – Establish relationships between tables
             What is a database?
• An organized and structured collection of data
• Types of databases
   – Relational databases
   – Object-oriented databases
• When to use
   – Multiple users
   – Large quantities
• Benefits over spreadsheet
   – Larger than 65,000 records
   – Reduced redundancy
               Relational Database
• A database is a collection of tables
   – also called a database schema
   – Tables are also called a relations
• All tables should have a primary key
• Tables have
   – Records – “A record contains information about a single item in
     your database.”
   – Fields – “Each record is made up of a series of fields which store
     individual bits of information inside a record.”
• Schema captures relationships between tables
                              Data Types
• Each field in a table has a data type
• Vendors (eg Microsoft, Oracle, IBM) call data types slightly different names
  in their different databases
• Frequently used data types (use MS Access names) are
   – Text/Character
         • Use these for data that can be alpha numeric
         • Eg a persons first and last names, a book title etc
    – Numeric
         • use if you want to make calculations on the attribute (eg shoe size)
         • A special numeric data type is autonumber
              – good choice for a primary key
    – date & time
         • Typically stored as one field ie BOTH date and time
    – Boolean
         • yes/no
    Attributes for all data types
• Required or Not
  – "NULL": empty
Creating tables
Creating a new table
Editing an existing table
Double click
the table to
get the Data
Demo – Creating a new Database
The Customer Table
1) Unique Identifier – Primary key and autonumber
2) Last Name – text required, field size =20
3) First Name – text required field size =20
4) Phone – set input mask · phone: (xxx) xxx-xxxx
5) Birthday – date data type
6) Number of children
                    Class Exercise
Product table
1) 4 fields: product_id, product_name, company, price
2) data type
·    product_id: AutoNumber
·    product_name, company: Text
·    price: Currency (decimal places: 2)
3) Set product_id as a primary key
4) Type four records in the table (change to datasheet view)
5) Save the table as 'Product'
                       Class Exercise
Order table
1) 5 fields: order_id, customer_id, product_id, date, time
2) set order_id as a primary key
3) data type
·    order_id: AutoNumber
·    customer_id, product_id (related fields): Number/Long Integer
·    date: Date/Time ('Short Date' format, in the general tab at the
·    time: Date/Time ('Medium Time' format, in the general tab at the
4) enter 5 records
5) save the table as “Orders”
Creating relationships
                Referential integrity
•       Ensure that a database is always in a valid state
•       Keeps relationships between tables
    –     Eg that orders are only placed for current products
    –     Eg that orders exist for customers that are in the
•       To enforce domain constraints
    –     Eg that a state name really exists

•       You enforce referential integrity by creating a
        foreign key from one table to the primary key of
        another table
Creating a relationship in MS Access
         Creating a relationship
• Goal: Create a foreign key from ORDER to
  PRODUCT because many orders can refer to the
  same product
• Process
• Step1 - Create an attribute that will become a
  foreign key – in this case a new attribute product_id
  in ORDER
• Step 2 - Drag your new foreign key to the table
  where the primary key is stored – in this case drag
  ORDER.product_id to PRODUCT.product_id
MS Access Relationship
                     Drag to here

                     Drag from
    Dialog box to create foreign key

Primary Key

                            Foreign Key
Required                     Nature of
Editing a Relationship
                         Right mouse
                           click on
           Cascading update and delete
•       Enforce Referential Integrity
    –      rule 1: can't enter a value in the foreign key field of the related table if
           the value does not exist in the primary key field of the primary table
    –      rule 2: can't update a record from a primary table if there are matching
           records in a related table
    –      rule 3: can't delete a record from a primary table if there are matching
           records in a related table
•       Cascade Update
    –      if you change the primary key, the foreign key will also change
    –      Ie if checked, rule 2 will not be enforced
•       Cascade Delete
    –      If you delete a record in the primary table, the record will be deleted
           from the referring table
    –      Ie if checked, rule 3 will not be enforced

To top