K204 Chapter 4

relational database

stores + organizes data in tables (consists of rows + columns)

Tables

collections of related data

Rows

represents a single record

Columns

represents a specific attribute of the data

in a relational database, tables can be related to e/o using key fields:

• Primary Key

• Foreign Key

Primary Key

unique identifier for each record in a table

Foreign Key

field in one table that links to a primary key in a nother table, establishing a relationship b/tw the 2 tables

relational databases use a language called _______ to manage and manipulate the data

SQL - Structured Query Language

Why are data stored in separate tables in relational databases?

Normalization

1. Avoid Data Redundancy

2. Maintain Data Consistency

Avoid Data Redundancy

data redundancy is minimized by splitting data into separate tables based on related entities

Maintain Data Consistency

changes to data only need to be made in one place

What benefits are associated with relational databases?

• improve data integrity + accuracy

Enforce Data Integrity

relational databases use constraints like PKs, FKs, data types

Reduce Anomalies

by organizing data into related tables, update, insert, + delete anomalies are minimized, ensure consistent + accurate data

query

request for info. from a database

• crucial for answering important questions

  • EX: revenue-generating products, evaluating employee performance)

optimized queries

SQL allows complex queries to be performed across multiple tables using joins, providing flexible + efficient data retrieval

• enables users to retrieve specific data sets w/o unneeded duplication or processing

faster access

indexing on separate tables can improve query performance, making data retrieval more fast + efficient

Modular Design

separating data into different tables makes it easier to modify + scale

Reusability

data stored in separate tables can be reused across different applications + queries, providing greater flexibility in how data is utilized

easier updates + management

managing data in smaller related tables simplifies updates + maintenance tasks

better organization

logical separation of data into tables makes for more organization + better navigation

What is a relational database management system (RDBMS)?

software system designed to create, manage, store, + retrieve data organized in a relational data model

Some of most commonly used RDBMS

• MYSQL

  • used in web apps

  • reliable, ease of use

• Oracle

  • powerful, enterprise-grade RDBMS known for scalability + extensive feature set

• Microsoft SQL Server

  • comprehensive RDBMS developed by Microsoft

  • used in many biz apps like e-commerce, data wave housing

• IBM DB2 - used in large-scale enterprises, fin. institutions…requiring robust data processing capabilities

Microsoft Access

desktop RDBMS developed by Microsoft

• combines relational Microsoft Jet Database Engine with graphical user interface + software-developmental tools

• commonly used for S-to-Medium database solutions, personal projected, + small biz apps.

Benefits of Microsoft Access

• ease of use

• cost-effective

• simple setup + maintenance

• integrated with microsoft office

• rapid development

• no server requirement

• adequate for small data volumes

Benefits of Microsoft Access

• ease of use

  • user-friendly interface

  • templates + wizards: built-in to simplify database creation

• cost-effective

  • lower cost

• simple setup + maintenance

  • quick installation

  • minimal maintenance

• integrated with microsoft office

  • seamless integration

  • VBA Automation: Users can automate tasks and create custom solutions using Visual Basic for Applications

• rapid development

  • prototyping - quickly create + adapt solns to needs

  • Forms + Reports

• no server requirement

  • standalone app.

• adequate for small data volumes

  • adequate performance

Precautions when using Access

• save work often !

• padlock icon, .laccdb extension

  • prevent unwanted changes or deletion by another person

• once done w/ work, make sure to SAVE file + CLOSE file

  • for most up-to-date version

Table

stores data

Query

retrieve data from one or more related tables that meet specific criteria

Form

allows user to view, add, + update data in tables

Report

presents summarized data