Database Summary A2 chap 5

Importance of Database

  • Online stores and applications use databases to store data like product names, prices, categories, and descriptions.

Introduction to Database

  • Database: A collection of structured data stored in a computer system for efficient storage and retrieval.

Database Management System (DBMS) Features:

  • Creation of new databases.
  • Changing the database schema (design).
  • Insertion and update of records.
  • Data retrieval.
  • Providing an interface for other programs to access data.
  • Creation of forms.
  • Generating reports.

Access Objects

  • Common types: Table, Form, Report, Query.

Table

  • Most important Access object; stores data in columns (fields) and rows (records).
  • Data source for forms, reports, and queries.
  • Two views: datasheet view and design view.

Datasheet View

  • Columns are fields and rows are records.

Design View

  • Shows every field in a table and allows management of the table structure.
  • Manages field properties like “Primary Key”, “Field Size”, “Format”, “Input Mask”.

Form

  • User-friendly interface to manage data in a table, showing one record at a time.

Report

  • Displays and summarizes data from tables in a customizable way for printing.
  • Data can be filtered or sorted.

Query

  • Filters, sorts, summarizes, updates, or deletes data in a table using Structured Query Language (SQL).

Database Structure

  • Stricter structure compared to a spreadsheet table:
    • No empty columns or rows.
    • Each record has the same fields.
    • Every field in the same column shares the same properties.

Data Type

  • Defined before data entry; changing it may alter the stored value.

Primary Key

  • Stores unique data for every record to identify it from others.
  • Cannot be empty (NULL).
  • Composite Key: A primary key consisting of more than one field.

Field Size

  • Determines the storage size of a field; a text field of size N can store N characters.

Input Mask

  • Restricts user input to ensure data validity.
  • Example: Phone number format "0000-0000".

Format

  • Affects how a field's value is displayed but does not impact the actual stored value.

Database Design

  • Elements to consider when designing a database table:
    • List the Fields
    • Name the Fields
    • Define the Fields

Data Manipulation

  • Methods to meet needs: Filtering, Sorting.

Filtering

  • Showing only the records matching the criteria.

Sorting

  • Arranging records in a specific order.

Basic Query

  • Used to perform: Calculation, Filtering, Sorting.

Calculation

  • Specific fields:
    • SELECTCLASS,CNO,CNAMEFROMSTUDENTSELECT CLASS, CNO, CNAME FROM STUDENT
  • All information:
    • Asterisk (*) means all fields in the table.
  • Arithmetic Operators: +, -, *, /
    • "+" can function as a text concatenation operator.

Filtering

  • Using a WHERE clause.
  • Text strings are enclosed by single quotation marks.
  • Dates are enclosed by hash symbols (#) in Access.
  • DISTINCT command: To filter out duplicate records.

Comparison Operators

  • =, <>,

Logical Operators

  • AND, OR, NOT

IN

Specifies more than one value in a WHERE clause

BETWEEN … AND

Selects values within a specific range.

LIKE

Selects text strings that follow a specific pattern; uses wildcards.

Wildcards

  • * (asterisk): represents zero or more characters
  • ? (question mark): represents a single character

NULL Values

  • Use IS NULL or IS NOT NULL to select NULL or non-NULL values.

Sorting

  • Using ORDER BY clause.

Access Function

Mathematical Function

  • INT(): Rounds a number down to the nearest integer.
  • ABS(): Returns the absolute value of a number.

Text Function

  • LEN(): Counts characters in text.
  • MID(): Returns characters from the middle of text.
  • LOWER()/LCASE(): Converts to lowercase.
  • UPPER()/UCASE(): Converts to uppercase.
  • TRIM(): Removes spaces from the beginning and end of text.
  • SPACE(): Returns a string containing whitespaces.

Statistical Function

  • Group By function
  • MAX(): Returns the largest values of a field.
  • MIN(): Returns the smallest values of a field.
  • COUNT(): Counts the number of the given records.
  • HAVING clause: Filter summarized records.
  • SUM(): Adds all the values of a field.
  • AVG(): Returns the average values of a field.