Databases Flashcards
Databases
an organized collection of data that can be accessed, managed, and updated
relational database
a particular type of database built upon the relational model of data
Data in a relational database can be accessed and reassembled in many different ways without having to reorganize the data.
Each entity is stored in a table.
Columns are called attributes
Rows represent individual records.
Rows represent individual records and consist of many attributes organized using columns.
(R)DBMS
Relational Database Management System
Relational Database Management System
is a software application designed to manage a database. It has four basic functions
four basic functions Relational Database Management System
Data Definition
Data Storage
Data Retrieval
Administration
RDBMSs include databases like
Oracle, Microsoft Sql Server, PostgreSQL, MySql, are relational, and are commonly called SQL Databases.
NoSQL Databases are
do not use a relational structure, instead they structure data specific to the problem they are designed to solve.
NoSQL databases include
MongoDB, Cassandra, Google BigTable, HBase, DynamoDB, and Firebase
entity
a set of data being stored a table
Table
defines a set of data elements and the structure to store them. Tables are structured into columns and rows.
Columns
attributes of a table and define the name and data type. A table has a set number of defined columns
Rows
the data being stored. A table has an unlimited number or rows.
Cell
the location where a column and row intersect, and is used to refer to a specific value or row of data (entity).
SQL
Structured Query Language
Structured Query Language
a language that lets you access and manipulate databases
ANSI-SQL
A standard that databases must follow to be considered a SQL Database.
All SQL databases support the ANSI-SQL language, however, most databases extend it with their own proprietary additions.
Character Data Types
character, varying character, text based data
char(#)
character. # defined the length of the data.
varchar(#)
varying character. # defined the length of the data.
text
text based data that is not limited by a predefined size
Numeric Data Types
int, bigint, decimal(p, s)
int
integer - similar to Java’s int
bigint
Big Integer, similar to Java’s long
decimal(p, s)
floating point numbers, similar to Java’s double or BigDecimal
p - precision
the total number of digits being stored
1234.567 has a precision of 7
s - scale
number of digits to the right of the decimal point
1234.567 has a scale of 3
boolean
true/false
Not the same in all SQL databases
Mysql - tinyint(1)
PostgreSQL / Oracle - boolean
MS Sql - bit
DATE
yyyy-mm-dd
TIME
hh:mm:ss
TIMESTAMP / DATETIME
yyyy-mm-dd hh:mm:ss
Structured Query Language (SQL)
A declarative programming language used to manage a database and its data. A declarative programming language specifics what actions should be performed rather than how to perform those actions.
DDL
Data Definition Language
Data Definition Language
defines the structure of the data
DML == SQL
Data Manipulation Language
Data Manipulation Language
query and modify the data
DCL
Data Control Language
Data Control Language
used to administer the database
SELECT
clause indicates what columns that you want to get from a database table
FROM
clauses indicates which table(s) to retrieve the data from.
SELECT name, population FROM country;
WHERE
clause is used to filter the rows returned in the results using conditional clauses.
SELECT name, population
FROM country
WHERE population > 100000000;
