Basics of Database Concepts

What is a database?

•Collection of organized data

•The data must be categorical and, logical in manner

Commonality of databases

All databases have columns and rows or, fields and records

Field

• Column of data
• category of data

Record

•Row of data

•one record is equal to a paper file on that record

Index Vs Primary key

•A unique value in the column or field, i.e. Social Security Number, etc…

•creates more efficiency in maintaining records

Table

•The file where the records are maintained

Database types

•Flat file databases

•Relational databases

Flat file databases

•Maintain all records on one table

•tend to create duplicate records

•can be extremely problematic

•not recommended

Relational database

•Multiple tables that are categorical

•linked together via indexing

•better organization of the files (tables)

•faster through-put

•easier to maintain

Relationship

Suggests that multiple table have fields that share commonalties between them

Differing Relationships

_One to one or 1:1

•one to many or 1:M

•many to many or M:N

One to One relationship, 1:1

Suggests that a table is indexed with another table whose links are unique to there record

One to Many Relationship, 1:M

•Suggests that the indexed field in table B is not unique

Many to Many Relationship, M:N

•Suggests that many records in one table can have a relationship with many records in another table

Simple relationship question

•Can table A have more than one match in table B?

•Can Table B have more than one match in table A?

Simple Relationship response

•No, no suggests a 1:1 relationship

•yes, no suggests a 1:M relationship

•yes, yes suggests a M:N relationship

Brand Name Databases

•Oracle •Microsoft Fox Pro •Microsoft Access •Microsoft Works •Paradox

Database or Spreadsheet

•Spreadsheets offer more flexibility when crunching numbers and creating charts

•databases offer more flexibility and reliability when managing multiple tables

•spreadsheets and databases can work together

Microsoft Excel Spreadsheet

•Flexible solutions

Microsoft Access Database

Relational Database Management System RDBMS

Access Components

•Table

•Form

•Report

•Query

•Data Access Page

•Macro

•Module

Table

•The object where the data is stored

•Looks a lot like a spreadsheet

Form

•Template used make data entry more graphical, readable and, understandable; a more user interface for your table(s) or query(s)

Report

•Printing of data stored in the Table or Query

Data Access Page

•Internet Database utility

Query

•Questions data from table(s) or other query’s based on criteria defined by the user