Spreadsheet in Access Database by bhb11449


More Info
									INLS102 Week 12:
Spreadsheet Functions
Database Concepts

      Date: 11/15/05
      Instructor: Leo Cao
      SILS, UNC-Chapel Hill
Learning objectives of the day

   Master use of excel functions
   Understanding the fundamental
    concepts of databases
   Intro to the use of MS Access

   Spreadsheet application by MS
   Fairly simple to use, got good depth of
    functions if you explore it
   One of the “killer apps” for business to
    adopt computing
Excel cont’d
   The one key thing to remember about excel is that
    every cell is an object
       This allows you to manipulate the elements in the sheet
           (Cell_A + Cell_B) / Cell_C = result
           That formula above, in various forms, speaks for the versatility
            of an spreadsheet app
   In a sense, you have a functional document
       Spreadsheets can perform complex statistical analysis as
        well, I’ll leave that to your leisure pursuits
   Often good for text as well
       If you get a massive batch of text, try importing into excel,
        use the eliminators, often times it’ll save you a load of time
What is a database?
   An organized and structured collection
    of data, and relationships between
   Types of databases
       Flat files
            Simple list style storage, direct input/output/edit, such as
             excel, manageable when data is limited
       Relational databases
            Using tables with fields to store data
            Establish relationships between tables to effectively
             manage data
Picturing a database?
Database functions
   Database examples?
   Functions of database
       Data storage
       Data management
       Data retrieval
   Why Use relational databases?
       Reduce data redundancy
       Ease of backup (save another copy)
       Performing complex queries
       Managing multi-user access
Database structure

   Table
       Records – “A database file is a group of records that are identical
        in structure. A record contains information about a single item in your

       Fields – “Each record is made up of a series of fields which store
        individual bits of information inside a record.”

   Relationships – How each entity relate
    to each other
    Table structure
   Fields
       Primary key
            Unique to each record, composed of 1 or a set of attributes
             (id #, or name + phone #)
       Foreign key
            The field being connected to by the primary key in the other
   Primary and foreign keys are the same attribute,
    just in different tables, they must have matching
    data, that’s how referential integrity is enforced.
Table structure
   Field type --- data type
       character: alphabetic (name), numeric (age), alpha-
        numeric (address)
       number: numeric information to be used in calculations
       dates & time: mm/dd/yy or dd-mm-yy
       yes/no: true/false, male/female, etc.
       memo: free text of variable length, comments, description
       "NULL": empty
       Field length --- keep to the possible maximal length
Entity Relations
   ER diagrams are used to abstractly represent
    real world entities in systems terms
       An essential concept in databases
       The first entity is always the primary entity

Types of relationships
   One-To-Many: diagram http://ils.unc.edu/inls102/Fall2004/notes/w11_db/one-to-many.jpg
        the most common type of relationship
        a record in the primary table corresponds to more than one record
         in the related table
        i.e.) Customer-to-Order: customer_id
   One-To-One: diagram http://ils.unc.edu/inls102/Fall2004/notes/w11_db/one-to-one.jpg
        matching fields on both sides are primary keys
        i.e.) AuthorName-to-AuthorDates: author_id
   Many-To-Many: diagram http://ils.unc.edu/inls102/Fall2004/notes/w11_db/many-to-many.jpg
        more realistic relationship
        i.e.) Name-to-Address: name_id to address_id
        represented as two one-to-many relationships through a junction
        junction table contains pointers to the primary keys of each table
Database: Access Intro

   One of most popular relational
    database software around
   Can be a bit frustrating to learn, but
    not too difficult
   Very frequent and annoying error msg
    pop-ups, just don’t panic, read it and
    make corrections, it’s mostly there to
    forcibly prevent errors by the user
Intermission – 15sec pause

   Any questions on what we just went

   Probably not enough time to go through the exercise today ?
Access exercise
     Read and complete the following,
1.    Create a new database file (File --- new), save the file as Shopping.mdb
2.     Create Customer Table
1)     6 fields: customer_id, last_name, first_name, phone, birthday, children
2)     make customer_id as the primary key (right-click the field, and select primary key)
3)     make last_name and first_name as required fields (in the general tab at the bottom, “required” --- choose
4)     set data type
· Number: customer_id
· text: last_name (field size:20), first_name (20), phone (15)
· date/time: birthday (format: short date)
· number: children (field size: Byte)
5)     set input mask · phone: (xxx) xxx-xxxx --- click on the “…” next to input mask to select
6)     add 5 records
7)     save the table as Customer

3.   Create the 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'
Access exercise cont’d
4.   Create the 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 bottom)
·    time: Date/Time ('Medium Time' format, in the general tab at the bottom)
4)   enter 5 records
5)   save the table as “Orders”

5.   Generate the relationships
a.   Tools --- Relationships
b.   Select all three tables to show
c.   Drag the customer_id in the Customer table to the customer_id in the Order_table
d.   Drag the product_id in the Product table to the product_id in the Order_table.
e.      Remember to check the “referential intergrity” on the pop-up window.

f.      http://www.unc.edu/~lcao/inls102test/shopping.mdb (full db)
For Thursday

   Search engine assignment
   Make it available on your webpage,
    email to notify me

To top