Document Sample
CSI480AccessPrimer Powered By Docstoc
					CSI480 Access – Primer/Refresher

               Spring Semester 2009
               Professor Suydam
Tables, Relationships & Queries

   E-R Diagrams
   Relationships
   The Relational Model
   Normalization
   Stages of the Database Application Cycle
   Queries
E-R Diagram

   Food Store Database
Establishing Relationships Between Tables

   Download FIPS text File and convert to MS Access Table (import text
      1. The US Counties table has State column in which state name
          codes are repeated. This is inefficient
      2. What we would like to accomplish is to reorganize this table as
          two and establish a relationship between them.
      3. You can accomplish the “splitting” process by “Make Table” query.
      4. Make Table Query
      5. Start a new query. Add Counties table. Save it as
Establishing Relationships Between Tables

7. The two tables after split are US Counties – Modified and States

8. Select the SQL View of the query
Establishing Relationships Between Tables

 9. Type Distinct as shown.

10. Select Query and then Run. The Table States is now created.
11. in the Tables view, make a copy of US Counties and rename it as US
    Counties – Modified
12. In the design view of US Counties – Modified, delete the State Name field
13. Now, the table US Counties – Modified has only STATE_FIPS attribute that
    identifies the State in which the county is located.
14. The States table has both SateName and State_FIPS attributes that would
    help us reconstruct the data as we need.
15. You will be able to display information from one table in the other, if there is a
    relationship is established between them.
Establishing Relationships Between Tables

16. To establish the relationship between two tables, select Tools and then
17. Then pick the tables (two in this case) you need to relate with each other.
18. Follow the directions in the next screen captures.
Establishing Relationships Between Tables
Establishing Relationships Between Tables
Establishing Relationships Between Tables
Establishing Relationships Between Tables
What you learned from the exercise

   Efficiently organizing data in tables
   Establishing relationships in Access environment
   Retrieving (showing) relational data though covert queries
What you learned from the exercise

If you were to establish relationships for a number of tables in the database,
    here is how it should look like -- an example database, Northwind, is
    shipped with Access. Notice the multiplicity appearing on the relationship.
 The Relational Model

1.   Rows contain data about an entity occurrence
2.   Columns contain data about attributes of an entity occurrence
3.   Cells of the table hold a single value
4.   All entries in a column are of the same kind
5.   Each column has a unique name
6.   The order of the column is unimportant
7.   The order of the rows is unimportant
8.   No two rows may be identical
9.   All tables must be related to at least one other table
The Relational Model – PK and FK

To represent a relationship between two tables, we place the primary
key (PK) of the first table in the second table. Then this attribute in the
second table is called foreign key (FK).

• The process of identifying a suitable set of relations.
• Technically speaking:
     • Normalization is a technique for producing a set of relations
       with desirable properties, given the data requirements of an
     • The real key is –primary key!!
     • Functional dependencies play a significant role in
     • Unnormalized form (UNF):A table that contains one or more
       repeating groups

From Requirements to Data Entry

1st Normal Form (1NF)
• Definition: A relation in which the intersection of
  each row and column contains one and only one
• UNF has to be transformed to 1NF
• Remove repeating groups by entering
  appropriate data in to empty columns of rows
  containing repeating data –i.e. fill-in-the blanks!
• This approach is called flattening the table
• The resulting table, now satisfies the above
  definition –hence the 1NF

UNF to 1NF

2d Normal Form (2NF)

3d Normal Form (3NF)

Integrity Constraints

• Required data
    • Some attributes must always contain a valid value –no
    • Example: every employee has a staff number and a
      position title!
    • If missing –do not enter it into the database, period!
• Attribute domain constraints - What do you mean you
  want to enter a “N/A” code for the attribute “Gender”?
• Entity integrity - NO, repeat, NO records should have a
  null in the primary key

   Fan Trap

   Chasm Trap
Data Redundancy & Anomalies

   Inefficient organization leads to anomalies
   Insertion anomalies–Inserting new staff in to StaffBranch table–
    Inserting a new branch that currently has no staff
   Deletion anomalies–When deleting some data, you loose other
    data for good
   Modification anomalies–Each time you modify one data element,
    you have to do it in several places
Addressing Anomalies
Stages of the Database Application Cycle
Introduction to Structured Query Language

• Standard English like language
• Differs from other programming languages
   • Performs many tasks in one simple
   • Easy to learn because of the simple syntax
   • Heart and soul of any dB management task
   • All application programs are built on SQL
     statements and the data they retrieve.
Structured Query Language (SQL)


• Functional
   • Create database and relation structure
   • Perform basic data management tasks (remember the 4-basic ops)
   • Perform simple and complex queries
• Perform most of the above with minimal user effort
• Command structure must be relatively easy to use
• Two main components of SQL
   • DDL
   • DML
Structured Query Language (SQL)


• A non-procedural language- - i.e. you specify what
  you need and how to get it
• Essentially free-format -- Not like FORTRAN where
  you start at line 9
• English like
• Can be used by a range of users -- DBA to DumbA
• NOT case sensitive
• Has several reserved words
• Does not use formal dB terms like tuples etc.

Shared By: