Database Management System Review ICS 100

Database Management System Review ICS 100

What is a Database? Collection of data associated with a topic – Smallest unit of information is a field – Group of related fields is a record An organized collection of related information

Flat vs. Relational Small number of records: Flat DB Excel spreadsheet Large number of records: Relational Like the Web Contains more than one table Banner Student names, addresses, phone number Courses Fees Owed Transcripts

DBMS Database Management System – Provides tools to enter, edit and retrieve data Users – Policemen, librarians, students, retail establishments

Commonly Used Software Microsoft Access* Paradox Lotus Approach Alpha 5 FileMaker Pro *Access is not available for Macs Structured Query Language SQL http://databases.about.com/od/sql/a/sqlfundamentals.htm

Basic Features Locate information Display information – Sort information Watch demo Stop

How is it Organized? Tables are the foundation of a DB A primary key* is selected What is a primary key? *needed to link two or more tables

Examples of Primary Keys SSN Badge Numbers Student ID Number Vehicle ID Number Employee Number

Fields Table Last Name First Name Job Title SSN Brown Alice Accountant 123-33-1234 Akiona John Sales Manager 334-65-9876 Joaquin Pablo Receptionist 789-76-0987 Thomas Sandy Marketing Manager 234-43-4321 Records

Fields and Records Fields are like columns in a spreadsheet Records are like rows in a spreadsheet

Two Common Views Data Sheet View – Looks like a table Design View – Shows fields in a vertical arrangement – Allows formatting of fields

Data Types Numbers – only numbers Text everything (0 to 255 characters) AutoNumber Access will number each record Yes/No Currency Date/Time Hyperlink Memo more text (0 to 6536 characters) OLE

Data Properties Format each data type Example – Size of a field – Masks for a field

Objects Allow you to use the database Types of objects – – – – – – – Table Queries Forms Reports Modules Macros Pages

Integrity—most important Keep data accurate Keep data up to date Eliminate redundancy

Ways to Eliminate Redundancy Input Mask Use of a form Use of a primary key Other?

Input Mask Analogy to painting Masking tape “masks” the areas you don’t want to paint Guides the user in entering data eg. 234-45-4560

Data is Stored on Tables Printed Information Reports Forms Data Queries Tables

Opening a Database Use the Explorer or My Computer to copy the database You cannot use the Save as command

Closing Databases may require a lot of memory. If you plan to move a database, invest in a flash drive.