Database

Database: a structured set of data held in a computer

• Stored locally, on a server, or in the cloud

• Resemble spreadsheets but contain tables instead

• Flat files are two-dimensional, containing rows and columns

  • Flat files = spreadsheet software

When to use databases?

• Multiple concurrent users: multiple users attempting to access or manipulate data

• Scalability: increased users and data to a flat file will slow down performance

• Speed: databases are faster at processing large amounts of data

• Variety of data: databases contain text, numbers, images, and some cases web pages

• Security

Relational databases

• Structured to recognize relationships among stored items of information known as datasets

• Predictable and organized with tables containing columns and rows of text or numerical data

• SQL, Oracle, Sybase, Informix, MySQL= examples of relational databases

• Designed to support numbers and text (structured)

Relational Database Management Systems (RDBMS): software used to manage a database

• add, remove, or update records

  • records. =rows (horizontal)

• Retrieve data

• Cross-reference data in different tables

Relational DB Structure

• Schema: rules and structure of a database

  • Constraints: rules limiting types of data that can be entered into a data table

• Records: the row in a table (horizontal)

• Attributes: the column in a table (vertical)

• Tables: looks like a spreadsheet and contains data

• Fields: columns: contain a single data type for all records (phone numbers, names, etc)

• Forms: how data is entered

  • Referred for nontechnical users

• Queries: how data. is found

  • Best way to retrieve data: Select Command

• Macros and Modules: increases database functionality (run reports at regular intervals)

• Reports: generated to answer specific questions

Primary and Foreign Keys: unique identifier that helps you define a record (or content in a row)

• Can only be one primary key per record

• Foreign Key: a key or field in a table that is used to link two tables together

  • relationship/logical links

Non-relational databases: does not use the tabular schema of rows and columns

• Uses a storage model that. is optimized for the specific requirements. of the type of data being stored

• Also known as “NoSQL” databases

• Unstructured data: pictures, videos, webpages, emails, documents

• Semi-structured data: unstructured data tagged with meta data (facts about data)

• Structured data: texts and numbers- can be organized or searched through

Types of non-relational database

• Key/Value Databases: represent data as a collection of key/value pairs

  • Blob storage: stores unstructured data like words or binary

  • Examples: Amazon, Microsoft Azure, MangoDB

Database Access Methods

• Manual Access: going directly into tables to view or change data, suitable method for working with a database that is stored locally

• Direct Access: accessing via a direct connection across a network suitable for LANS

  • • Direct and Manual Access are often paired together

• Programming Access: accessing a database through a programming language like SQL

• User Interface and Utility Access: using a graphical interface (GUI) such as a form or webpage

• Query/Report Builders: creating reports and visual aids such as pie charts

Data Manipulation: enables the ability to adjust the contents/data in your table

• Use on the rows/records/tuples of a relational database

  • The following is data manipulation

    • Select: Allows you to select, specify, or reach for intended data

    • Insert: allows the ability to add contents into table (new data)

    • Delete: allows the ability to remove contents (remove data)

    • Update: allows the changing or editing of contents in table (edit data/row)

    • Data definition: defines the structure of your database and its tables on columns/attributes/fields BUT cannot manipulate rows/records/tuples

      • Create: Allows you to create a new structure, such as a new Database or new table

      • Alter: usually used to alter DB object such as column name

      • Drop: allows the removal of.a database or table(s)

      • Permissions: identifies who has the ability to edit table structure

  • DDL: CADP

Data Backups

• Database Dumps:

  • one time logical backup of a database

  • Data is dumped into an empty database already set up with a schema

• Database Backup: done through a program and creates a physical backup

  • Can be scheduled and repeated

  • Used to restore a database without needing to create a new one