Chapter 7 Flashcards
Where can Databases be stored?
local workstations
servers
the cloud
Flat file:
has two dimensions, rows and columns
Database servers:
allow access to hundreds or tens of thousands of users at a time
What types of data can a database store?
numbers
text
virtually any type of data
Data Persistence:
users expect their data to be stored indefinitely
Relational databases:
is predictable and organized, with tables containing columns and rows of text or numerical data
Database management system (DBMS):
manages all databases
Relational database management system (RDBMS):
manages relational databases
Database management system (DBMS) and Relational database management system (RDBMS) enforces the following principles:
No duplicate rows are allowed
Column values must not be arrays or repeating groups of data
Where data is not present, null values are used
Null value:
means the absence of data, not a value of zero
not considered equal to another null value
Schema:
the rules and structure of the database
What are the two types of schemas in relational databases?
Logical schema
Physical schema
Logical schema:
outlines the structure of the data, such as the tables and their fields, and relationships between tables
can be created by almost any visual diagramming software package or even in a spreadsheet
What are databases usually modular?
to make it easier to allow or restrict access to specific data
Physical Schema:
the actual tables, columns, and relationships created
Each row in a database table is called a:
a record
Each column in a database table is called a:
a field
A field:
contains a single data type for all records
Primary Key:
is one or more fields whose data is used to identigy a record uniquely
What two things must be true for primary keys:
the data in the combination of columns must be unique
no values in the column can be blank or null
Foreign Key:
is one or more columns in a table that refers to the primary key in another table
are not required
Constraints:
basically limitations that prevent someone from takkng an action that would cause major problems within the database
Constraints can prevent someone from doing the following:
Entering a value that isn’t found in the linked table’s primary key
Changing the foreign key value to a value that doesn’t exist in the linked table’s primary key
Deleting rows from the primary key table, which would create an orphan record
Primary and Foreign Keys:
Required
Yes
No
Primary and Foreign Keys:
Number allowed per table
1
No limit
Primary and Foreign Keys:
Automatically indexed
Yes
No
Primary and Foreign Keys:
Duplicate values allowed
No
Yes
Primary and Foreign Keys:
Function
Uniquely identify records in a table
Refer to records in primary key table
Forms:
are how data is entered into and often viewed from your database
Queries:
mine your data to find the specific information that you need
Reports:
are generated to answer specific questions
Macros and modules:
let you add functionality to your database
The three types of databases are:
Relational databases
Nonstructured databases
Semi-Structured databases
Nonstructured and Semi-Structured can be categorized as:
Non-relational databases
Semi-Structured data:
is generally thought of as unstructured data that has been tagged with metadata
Metadata::
data about data
What is a popular type of non-relational database?
Document Database
In a Document Database:
each record and its associated data is considered a document
What does a document database need?
the ability to index all of the available metadata
robust search engines to mine the data available
Key/value database:
data is represented as a collection of key/value pairs
manage associative arrays of data, which is a collection of objects or records
Key:
is an arbitrary string of characters
must be unique
What are some popular key/value databases?
Redis
Amazon DynamoDB
Memcached
Microsoft Azue Cosmmos DB
Oracle NoSQL
What are the ways to access a database?
Manual Access
Direct Access
Programmatic Access
User Interface and Utility Access
Manual Access:
going directly into the tables of a database to view or change data
Direct Access:
accessing the database tables directly across a network connection
Programmatic Access:
accessing the data through a program or a programming method
User Interface and Utility Access:
use a gr`aphical interface or utility to access database
What are the three different classifications of permissions for databases?
Server permissions
Database permissions
Object permissions
Server permissions:
affect the entire server
typically granted to an adminstrator account
Database permissions:
apply to one database and all objects within the database
Object permissions:
will affect specific database objects
Predefined database roles:
db_owner
Full access (read, write, delete, back up)
Predefined database roles:
db_datareader
Read data
Predefined database roles:
db_datawriter
Add, delete, or modify data
Predefined database roles:
db_bckupoperator
Back up the database
Predefined database roles:
db_denydatareader
Can’t view data
Predefined database roles:
db_denydatawriter
Can’t add, delete, or modify data
SQL syntax for creating a database:
CREATE DATABASE database_name ;
SQL syntax for creating tables in database:
CREATE TABLE table_name
ALTER command:
add, delete, and modify columns
What are two ways to provide data for database?
Input data
Import data
Dropping:
delete tables or databases
What are the four commands that are frequently used to manipulate data in a relational database?
SELECT
INSERT
UPDATE
DELETE
INSERT Command:
used to insert records into an existing table
UPDATE Command:
used to update existing data in the database
DELETE Command:
deletes records
SELECT Command:
identify and display the data the user needs
What are the two pimary methods that exist to extract data from a database?
Queries
Reports
Report:
is essentially a query that’s been formatted to make them look like a finished product
can also produce different visuals, such as a pie chart
What are the two recommended ways to back up data from a database?
Database dump
Backup
Database dump:
a one-tme logical backup of a database
exports the schema, tables, views, and the data
Backup:
a physical backup of everything
can be scheduled and repeating
If data is lost how does a database dump differ from a backup?
For a database dump a new database would need to be created, followed by restoring the data from the dump
A backup would restore the entire database without needing to create a new database
