Introduction to Database

INTRODUCTION TO DATABASE with MS Access 2003

Databases are used every day, sometimes without us realizing. They are everywhere. The most basic example is a telephone book or a library card index. They can store all sorts of information, from phone numbers to map grids and references, score cards from sports games, report cards, etc. They allow quick searching and are great for keeping historical data, for example weather history. Most websites run using a combination of a database and a CMS (content management system).

At the most basic level, databases contain tables. Database tables are used to hold information, for example you may have a database table about ‘authors’ or ‘news’.
Inside a table, you have fields. Table fields tells us what information is being kept in the database. In an authors table, for example, you may keep the person’s name and email address.
Microsoft Access is a computer application used to create and work with databases. that means it’s a Database Management System or DBMS.

A Microsoft Access database is a single file with the file extension *.mdb and is often referred to as a database application.

A database is basically a collection of data or pieces of information. Whether you know it or not, you use databases all of the time. Some examples of commonly used databases might be:
• Address book
• Library catalogue
• Telephone directory
• Stock list

A database isn’t necessarily contained on a computer. A telephone directory is still a database even if it’s in the form of a huge book sitting next to your phone.

Databases are intended for storing and maintaining large amounts of information. The following are examples of the sort of information that can be kept in a database:

• Inventory control
• Payroll systems
• Personnel records
• Music collection catalogue
• Phone and address lists

Database is a collection of related information or data.

DATA VS. INFORMATION

Data – a collection of facts made up of text, numbers and dates:
          Murray       35000       7/18/86
Information - the meaning given to data in the way it is interpreted:
        Mr. Murray is a sales person whose annual salary is $35,000 and whose hire date is July 18, 1986.

•    Data are facts about people, places, things or events. – unprocessed facts
•    Information is a processed data.

BASIC DATABASE CONCEPTS

Table - A set of related records (a set of one or more records)
Record - A collection of data about an individual item
Field - A single item of data common to all records

ACCESS 2003 STRUCTURE
What is Microsoft Access?
•    Powerful Relational Database Management System (RDBMS) design to run in Microsoft Windows. Data can be organized as a set of related tables
•    Integration with other Office applications allows seamless exchange of data with centralized database
What is an Access Database?
•    Collection of data objects stored with filename extension .mdb (Microsoft database)
•    Main Access data objects ( Tables, Queries, Forms, Reports, Macros, Modules, Pages)

DATABASE WINDOW   
•    Main database design/management window
•    Displayed when creating or opening an Access database
•    You can use the Objects toolbar to access the different objects that make up a database

TABLES
What is table
•    Basic container for data, arranged as a grid of rows and columns
•    Each row contains a single record
•    Each column represents a field within the record
Access tables
•    Fundamental data objects in Access
–    Forms, queries and reports are all based on tables
•    Table Wizard provides automated table creation
•    Tables can also be created manually for more precise specification
FORMS
What is form
•    Electronic version of paper form
•    Used to simplify entry of data into an Access database

QUERIES
What is a Query?
•    A question asked of the database
•    Used to extract specific information from database
•    Used to extract specific information from database
–    Example:What is the three top-selling products in our company’s product line?
•    Queries are composed of structured query language(SQL) statements
–    Example:
                SELECT Products.[Product #], Products.[Product Name], Products.Price    FROM Products
                WHERE (((Products.Price)<4.75));
•    Access allows queries to be created graphically
•    Hides complexity of SQL language

REPORT
What is report?
–    Formatted template used to print reports of database or query results
–    Allows user to specify fields, grouping levels, arrangement of printed data
MACROS
What is access macro?
–    User-defined sequence of actions to be performed by Access 2000
–    Macros will not be covered
MODULES
What is a module?
–    User-created sections of code which provide sophisticated automation of Access functions
–    Written in Visual Basic for Application(VBA)

CREATING A NEW TABLE IN DESIGN VIEW
For each field in new DATABASE, specify the following items
–    FIELD NAME
•    Descriptive name of field to be used in table
–    64-character maximum
–    Prohibited characters:period(.), accent grave(‘), square brackets([]), exclamation point(!)
–    DATA TYPE
•    Drop-down list displays available data types
–    Number, Date/Time, Currency,Auto number, Yes/No, OLE Object, Hyperlink, Lookup Wizard
–    DESCRIPTION
•    Comment describing details of field.  Appears on the status bar in Datasheet view when you click a row in the field's column

SETTING A PRIMARY KEY
What is a primary key?
–    Main index for table
–    Must be unique for each record in table
•    Example: Product number, Employee number, etc.
–    If no such field exist, create a new field with the data type “Autonumber” and specify it as the primary key
•    Access will automatically create unique numbers for this field
Assigning a field as the primary key
–    Select field
–    Click on Primary Key button on toolbar (or use “Edit /Primary Key”)
•    A key symbol will appear next to selected field