Wk 2 Lec 2: Basic Database Concepts and Design

Basic Database Concepts

• A database is a collection of data that can be accessed and updated.
• A table in a database contains rows and columns.
• A row is a record, and each row can hold one or more data values.
• A field in a table is a column, and each field holds a specific data value.
• A key uniquely identifies a record in a table and is typically called the primary key.

Relational Database

• A relational database is a collection of tables that can be accessed in multiple ways without reorganizing the database.
• Relationships between tables help to link data across tables and improve data access.

Designing Databases

• Databases are persistent, meaning the structure is fixed.
• It is important to define the database structure before adding data.
• Databases should be reliable and structured for easy data manipulation and additions.

Data Types

• Databases use data types to classify data values.
• Data types determine the amount of memory required to store the data and the permissible operations for that data.
• Some common data types include integers, floating point numbers, characters, character strings, and Boolean values.

The Four Basic Operations in a Database

• Design: Create the database by defining the tables and specifying the fields.
• Data Entry: Add data to the database by hand, automatically, or by importing from other files.
• Queries: Ask questions about the data stored in the database.
• Reports: Generate documents containing data from the database.

Database Options

• Several database management systems are available, including freeware/shareware, Microsoft Excel, Microsoft Access, Oracle/SQL, or through hiring a programmer.
• The options differ in their complexity, number of users supported, ability to handle multiples datasets, and security features.

Basic Design Rules

• Use descriptive field names to avoid confusion and ensure clarity.
• Ensure that field names are unique across different tables to avoid ambiguity.
• Avoid using calculated fields in the database, as calculations should be performed later using the raw data.
• Maintain unique records to avoid confusion and ensure accuracy in data retrieval.

Primary Keys

• A primary key is a field that uniquely identifies each record in a table and cannot contain duplicates or blank values.
• If a table does not have a unique field, an AutoNumber field can be used to generate a unique identifier for each record.

Unique Records

• Use unique primary keys to link data between tables and ensure data integrity.

Database Creation Example

• Creating a database involves defining the tables and specifying the fields for each.

Database Management System (DBMS)

• A collection of software designed for managing and organizing data within a database.

• It enables efficient data retrieval through queries and the generation of reports.

• Examples of DBMS applications include sales management, banking systems, and online shopping platforms.

Four Basic Operations in a Database

• Design: Involves creating the database structure by defining tables and specifying the fields within them.

• Data Entry: The process of entering data into the database, which can be done manually, automatically, or through importing data from external files.

• Queries: Involve asking questions about the stored data using specific query languages.

• Reports: Used to produce documents that summarize and present data from the database.

Field Naming Practices

• Use descriptive names that reflect the data they contain.
• Avoid abbreviations and jargon that may be unclear.
• Keep field names concise and consistent with naming conventions.

Pulling Fields Apart

• Pulling fields apart refers to separating a larger field into multiple smaller fields. This can improve the organization and clarity of data.

Long Field Names

• Using long field names can make the database schema difficult to read and navigate.

Avoiding in Field Names

• Avoid using spaces, special characters, and reserved keywords when naming fields.

Essential Database Field Design Factor

• An essential factor is data type, which determines the kind of data that can be stored in a field.

Primary Key Purpose

• A primary key uniquely identifies each record in a table.

Relational Databases and Data Access

• Relational databases use relationships between tables to enable efficient data access.

Database Persistence

• Databases are persistent, meaning that the stored data remains available even after the database application is closed.

Uncommon Database Table

• A typical database table would not contain information on the physical layout of the data itself.

Data Addition Prerequisites

• Before adding data to a database, you need to understand the structure of the tables, including the fields and data types.

Table Relationship in a Database

• Two tables in a relational database are connected by shared fields, forming a relationship.

Key Definition

• A key in a database refers to a field or combination of fields used to identify or link records.

Database Structure Design

• Designing the database structure before data entry ensures data consistency, integrity, and efficient retrieval.

Data Typing Benefit

• Data typing ensures that data is stored appropriately, maintaining data integrity and improving search efficiency.

Boolean Values

• Boolean values represent true or false states, typically used for logical conditions.

Database Structure Change Challenges

• Modifying the structure of a database after it has been populated can be complex and require careful planning to avoid data loss.

Film Duration Data Type

• The appropriate data type for storing film duration is "number" or "decimal", allowing for both whole numbers and fractional values.

Document Generation from Database

• Reports are primarily generated from database information.

Character String Data

• Character strings, also known as textual data, include letters, numbers, and special characters.

Simplest Database Data Addition

• The INSERT statement is the simplest database operation for adding data.

Multi-User Database Option

• Client-server databases are designed for multiple users to concurrently access and modify data.

Last Names Presentation

• Last Names from two different tables should be presented consistently, using the same formatting and capitalization.

Access Data Table Purpose

• Creating data tables in Access allows organized storage and management of information.

Importance of Unique Records

• Unique records ensure that each entry in a database is distinct and verifiable, preventing data duplication.

Access Calculated Expressions

• Calculated expressions can be created in Access by using the Expression Builder.

Access Data with Calculations

• Data in Access should be organized in tables to perform calculations later, making the process more efficient.

Example of Calculated Field

• "Total Cost" calculated by multiplying quantity and price is an example of a calculated field in Access.

Employee Pay Calculation Format

• Employee pay can be calculated by multiplying the hourly rate by the hours worked.

Unique Record Check Scenario

• If identical entries exist in a database with the same information, it indicates that unique records are not maintained.

Best Primary Key Field

• The "Employee ID" field is a suitable option for a Primary Key as it is unique and consistent for each employee.

Primary Key Field Characteristic

• A Primary Key field must be unique for each record in the table, enabling easy identification and retrieval.

Record Uniqueness Principle

• Each record in a database should be distinct, meaning that every entry should be unique and identifiable.

Unsuitable Primary Key

• A field that allows duplicate values would not be a suitable Primary Key because it wouldn't guarantee unique identification of records.

Alternate Primary Key

• If no unique field exists, a new field can be created and assigned as the Primary Key.

Primary Key Usage

• Using a Primary Key allows efficient data retrieval, linking between tables and ensuring consistent data entry.

Unique Records Implication

• Unique records are essential for avoiding data duplication and ensuring data integrity.

Primary Keys Section Format

• The Primary Keys section should list each Primary Key and its associated data type.

DBMS Definition

• Database Management System (DBMS) is software that manages databases by providing tools for creating, accessing, and maintaining data.

Database Structure Creation

• The process of creating a database structure involves defining tables, fields, and their relationships.

DBMS Function

• A DBMS allows for data management, including data storage, retrieval, and security.

Database Data Entry Methods

• Data can be entered directly into a database using forms, imported from external files, or generated automatically by other applications.

Database Data Inquiry

• Queries are used to ask questions about the data within a database, retrieving information based on specific criteria.

Default Access Database File Extension

• The default file extension for Access databases is .accdb.

Logical Value Data Type

• Boolean is the data type used to store logical values, represented as True or False, in a database.

Older Access File Extension

• The file extension .mdb was commonly used in earlier versions of Access.

Boolean Data Type Description

• A Boolean data type is used to store logical values, representing true or false states.

Boolean Field Values

• Fields defined as Boolean can hold only two values: True or False.