Chapter 7 Flashcards by Jerry Brinson

Where can Databases be stored?

local workstations

servers

the cloud

How well did you know this?

Not at all

Perfectly

Flat file:

has two dimensions, rows and columns

How well did you know this?

Not at all

Perfectly

Database servers:

allow access to hundreds or tens of thousands of users at a time

How well did you know this?

Not at all

Perfectly

What types of data can a database store?

numbers

text

virtually any type of data

How well did you know this?

Not at all

Perfectly

Data Persistence:

users expect their data to be stored indefinitely

How well did you know this?

Not at all

Perfectly

Relational databases:

is predictable and organized, with tables containing columns and rows of text or numerical data

How well did you know this?

Not at all

Perfectly

Database management system (DBMS):

manages all databases

How well did you know this?

Not at all

Perfectly

Relational database management system (RDBMS):

manages relational databases

How well did you know this?

Not at all

Perfectly

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

How well did you know this?

Not at all

Perfectly

Null value:

means the absence of data, not a value of zero

not considered equal to another null value

How well did you know this?

Not at all

Perfectly

Schema:

the rules and structure of the database

How well did you know this?

Not at all

Perfectly

What are the two types of schemas in relational databases?

Logical schema

Physical schema

How well did you know this?

Not at all

Perfectly

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

How well did you know this?

Not at all

Perfectly

What are databases usually modular?

to make it easier to allow or restrict access to specific data

How well did you know this?

Not at all

Perfectly

Physical Schema:

the actual tables, columns, and relationships created

How well did you know this?

Not at all

Perfectly

Each row in a database table is called a:

a record

How well did you know this?

Not at all

Perfectly

Each column in a database table is called a:

a field

How well did you know this?

Not at all

Perfectly

A field:

contains a single data type for all records

How well did you know this?

Not at all

Perfectly

Primary Key:

is one or more fields whose data is used to identigy a record uniquely

How well did you know this?

Not at all

Perfectly

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

How well did you know this?

Not at all

Perfectly

Foreign Key:

is one or more columns in a table that refers to the primary key in another table

are not required

How well did you know this?

Not at all

Perfectly

Constraints:

basically limitations that prevent someone from takkng an action that would cause major problems within the database

How well did you know this?

Not at all

Perfectly

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

How well did you know this?

Not at all

Perfectly

Primary and Foreign Keys:

Required

Yes

How well did you know this?

Not at all

Perfectly

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