Database

Database

a structured set of data held in a computer

Where do databases exist?

Locally, on a server, or in the cloud

What do databases resemble?

Spreadsheets, but contain tables instead

Flat files

spreadsheet software that is two-dimensional and contains rows and columns

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

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

What are examples of relational databases?

SQL, Oracle, Sybase, Informix, MySQL

Relational Database Management Systems (RDBMS)

software used to manage a database

What can an RDBMS do?

add, remove, or update records (rows), retrieve data, and cross reference data in different tables

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

Attributes

the columns in a table

Tables

looks like a spreadsheet and contains data

Fields

columns containing a single data type for all records

Forms

how data is entered

Queries

how data is found

Macros and modules

increases database functionality

Reports

generated to answer specific questions

Primary and foreign keys

unique identifier that helps you define a record

How many keys can exist per record?

One primary key

Foreign Key

a key or field in a table that is used to link two tables together

Non-relational databases / NoSQL databases

does not use the tabular schema of rows and columns

What type of storage model do non-relational databases use?

optimized for the specific requirements of the data being solved

What are example of unstructured data?

pictures, videos, webpages, emails, documents

What are examples of semi-structured data?

unstructured data tagged with meta data

Structured data

texts and numbers, which can be organized or searched through

Key / Value Databases

represent data as a collection of key/value pairs

Blob Storage

stores unstructured data like words or binary

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

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

Select

allows you to select, specify, or reach for intended data

Insert

allows the ability to add contents into table

Delete

Allows the ability to remove contents

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

Database dumps

One time logical backup of a database that dumps data into an empty database already set up with a schema

Database backup

Done through a program and creates a physical backup, allowing it to be rescheduled, repeated, and used to restore a database without needing to create a new one