Introduction to Databases

Introduction to Databases

• Databases are organized collections of related data stored in a computer
• They allow easier storage, access, and retrieval of data
• Databases provide a mechanism for creating, querying, modifying, and deleting data
• Data is a collection of raw facts
• Information is processed data or data represented in a meaningful form
• Organizations require information for decision-making

Examples of Databases

• Databases are prevalent, with examples including:
  • Schools keeping data on students
  • Governments tracking earnings and taxes
  • Credit information storage
  • Grocery stores use for tracking purchases
  • Banks use it to keep track of account data
  • Cookies used on website collect user information
• Databases drive the information age by facilitating quality decision-making

Types of Database Models

• Relational databases
  • Collection of relations or two-dimensional tables
  • Connects data in different files (tables) using common data elements or a key field
• Hierarchical databases
  • Can be visualized as a parent-child relationship
  • Child may have one parent, but a parent can have multiple children
  • Can also be imagined as an inverted tree, where the single table is the root and other tables act as branches
  • Data access requires passing through the root table
• Network databases
  • Developed to address problems within the hierarchical model
  • Allows a child to have multiple parents, similar to the parent-child model
  • Data can be accessed from any table, unlike in hierarchical models where access begins from the root table
  • Replaced by relational database model

Database Management System (DBMS)

• Set of programs to manage databases
• A DBMS must perform the following functions:
  • Create, modify, and delete data structures, such as tables
  • Add, modify, and delete data
  • Selectively retrieve data
  • Generate reports based on the data
• Examples of Relational Database Management Systems include:
  • Microsoft Access
  • Oracle
  • SQL Server
  • MYSQL

Microsoft Access Database Objects

• Tables
  • Used to store data
• Forms
  • Permit users to enter, view, and modify data stored in tables
• Queries
  • Pose questions about information stored in tables
  • Search and compile data from one or more tables
  • Define specific conditions for finding data
• Reports
  • Allow you to print data based on queries/tables
  • Used to extract information and present it in an organized, formatted manner (e.g., invoices, transcripts)
• Macros
  • Easy-to-use commands to automate tasks in Access
  • Example: Jumping to a new record every time database opens

Tables

• Two-dimensional structures storing data
• Contain information about one subject
  • Examples: Customers, students, books
• Made up of records (rows)
  • Each record represents a single item
  • Student, customer
• Each record is subdivided into fields (columns)
  • Fields define attributes for each record
  • Employee salary, student grade

Creating Tables

• Usually the first objects created in a database
• Table is a collection of data about a specific topic
  • Employee information, products, customers
• The first step is entering field names and data types
  • Add field names, their data types, descriptions, properties
  • Field name is the identifier for a field (e.g. Surname)
  • Field descriptions are more descriptive than field names, they clarify what each field is for
  • Example: “Current physical address"
• Data Types specify
  • Types of values to allow in the field
  • Storage space to use
  • Whether Access can index or sort data in the field

Understanding Views

• Design View
  • Used to set data types
  • Insert or delete fields
  • Set the Primary Key
• Datasheet View
  • Used to enter and view the data for the records

Data Types

• Short Text
  • Alphanumeric data (names, titles, etc.)
  • Up to 255 characters
• Long Text
  • Large amounts of alphanumeric data: sentences and paragraphs
  • Maximum is 64,000 characters
• Number
  • Numeric data
• Date/Time
  • Dates and times
• Currency
  • Monetary values
• AutoNumber
  • Unique value generated by Access for each new record
• Yes/No
  • Yes or no values
  • Fields contain only one of two values
• OLE Object
  • Pictures, graphs, or other ActiveX objects from another Windows-based application
• Hyperlink
  • A link address to a document or file on the Internet
• Attachment
  • You can attach files
  • Such as pictures, documents, spreadsheets, or charts
  • Limited by the storage limit of the size of a database file
• Calculated
  • Create an expression that uses data from one or more fields
  • Designate different result data types from the expression
• Lookup Wizard
  • Displays a list of values from a table or query, or a set of specified values
  • Creates a lookup field
  • Data type is either text or number, depending on choices made in the wizard

Field Properties

• Control appearance of data
  • Specifying the format of date
• Prevent improper entries
  • Specify default values
  • Sort of values to enter
• Control size of field

Field Properties (Continued)

• Field Size
  • Maximum number of characters allowed
• Format
  • Layout to use when displaying data (e.g., Date)
• Default Value
  • Value inserted automatically when new record is created
• Input Mask
  • Pattern to use when entering data (e.g. //____)
• Validation Rule
  • Rules for field data (e.g. “
• Validation Text
  • Rule error message (e.g. "CA can't be greater than 50")
• Required
  • Field cannot be blank
• Index (Required)
  • Set field as indexed, use to order records
  • Speeds up searches
• Caption
  • Specifies a label other than the field name to be used in forms and reports

Forms

• Database object to enter or display data
  • Data entry, editing, display for review
• Clear viewing because you can look at one record at a time
• Navigation buttons at the bottom-left are used to move between records and create new ones
• To Create a Form
  • Open the table or query on which you based the form
  • Click “Create” tab
  • Click “Form” in the “Forms” group

Views

• Form View
  • Allows to view, create, and edit records
• Layout View
  • Similar to Design View
  • More visually-oriented so that each control displays real data
  • Useful for setting the size of controls, or performing many other tasks that affect the visual appearance and usability of the form
• Design View
  • Provides a more detailed view of the form's structure
  • View the header, detail, and footer sections
  • Unable to see the underlying data while making design changes