Database Note

What is database?

• An organized collection of information.
• Telephone books .
• Collection of possessions.
• Customers,employees, and orders 
• Tools to analyze your data 
• Sorting using tables 
• Extracting what you want to see using Forms and Queries 
• Summarizing it using Reports 

Tables

• Table is a collection of information, organized into 
• Fields: the columns in the table (name,address,phone,number)
• Records: the rows in the table
• Data values: the actual information itself

Excel (Flat files) Vs. Access (Relational Databases)

• A phone book is a "flat file"(just a list of info) 
• That's what differentiates Access and Excel.

• A" relational database" prevents duplicate information.
• It's efficient and less prone to errors.
• In flat files, repeated information are kept in the same place.

 Relational Databases

• Example it works with 3 different types

Primary Keys and Foreign Keys 

• Need to add some techinique to the table structure 
• Primary Key is a unique identifier for each record in the table 
• These primary keys become Foreign Keys (AKA common fields)
• Linking related information between 2 or more tables 
• It's the field that both tables have in common and it;s allowed user to gather data from both when the user taking order,or run report.

Relationships

• The connection between Primary Keys and Foreign Keys 
• One-to-many 

•  1 record in the first table has multiple occurrences in the table .
• There are more types but one-to-many is common.

Data Types

• Every field needs a Data Type so that Access knows how to manage it
• Auto Number - Sequentially increments each record  
• Commonly used as a Primary Key Field 
• Text = for alphanumeric characters (letters and numbers) 
• Number - numbers that you calculate 
• Currency - for financial information ($)
• Date/Time - for date and times 
• Yes/No - for either / or scenarios 
• Attachments - allow you to associate files from other programs such as Word or Excel

• Hyperlinks - for emails and websites URLs 
• Memo - for long commentary (for taking notes)
• Calculations - do math based on data in your other fields
• Lookups - get data from other tables 

Normalization

• Design your database according to accepted standards
• Use smallest meaningful values possible 
• (split "Name" into Last Name and First Name for easier sorting )
• (split "Address" into Street,City ,State,Zip)

•  Separate repeated groups of data 
• If you see the same information more than once ,those fields should be moved into a second table, and linked through a conmmon ID field 

•  Plan Ahead!