m2u4 comptia - formatives 5.1-5.3

explain creation

WHAT info the database stores, WHERE it's hosted, and HOW it's accessed by clients

explain import/input

brings data into the database, rows (records) can be INPUT (updated manually) or IMPORTED (taken from another source)

ex. CSV, Delimited text files

explain queries

requests specific data from database for efficiency, uses SQL to return results

what are the characteristics of a flat file system?

uniform format (no structure for indexing), useful for export, import, and for closely related info

what are the characteristics of a database?

enforces data types for columns, validates info, supports different formats of data, manages multiple tables and links their fields to make schemas, supports millions of users concurrently, scalable, access control

RDBMS

relational database management system

ex. Microsoft SQL server, Oracle database, MySQL, Microsoft Office Access

what is a primary key?

unique identifier for each row, define relationships between tables

what is a database?

an organized collection of information

what do databases consist of?

tables

what are tables made of?

columns (fields) and rows (records)

what are databases used for?

to store info securely and report on that info

what are the processes and tools used in databases?

creation, import/input, queries, reports

explain reports

formats data to make it easy to read and interpret

ex. grouping, sorting, hiding details, performing operations (total count, average), exporting

what is a delimited text file

has a header row to name fields and characters (like , :) in between fields

CSV

comma separated values

what is a flat file system?

when data is stored in a SINGLE table

ex, spreadsheets, CSV

what is a relational database?

a highly structured database

what does monolithic mean?

composed all in one piece

what is the condition for RDBMS?

each row (record) must be unique in atleast one way

how is this condition accomplished?

through a primary key

what is a foregin key?

identifier across multiple tables, when a primary key is mentioned in another table

what is a schema?

a term used for a structure or layout defining a set of data

what are constraints?

a constraint on a value

ex. keys, preventing fields from being blank, making sure a delivery date is not in the past

what is GIGO?

garbage in garbage out, the quality of your input is the quality of your output, function of RDBMS

what are three types of data?

structured, semi-structured, unstructured

what type of data is in RDBMS?

structured

explain unstructured data

unorganized data

things like word docs, slides, media, also typically text heavy

explain semi-structured data

uses metadata to help identify data

ex. emails, posts organized by hashtags, folders organized by topic

types : XML, JSON

explain structured data

tables, rows, columns

ex. spreadsheets, star ratings

what is a non-relational database?

a database with no formal structure to link different data object/files

what is a document database? how does its query engine work?

a non-relational, semi-structured, database that grows by adding documents

its query engine is designed to parse documents and extract info from them

what is a key/value pair database?

a non-relational database that stores properties of objects without predetermining fields that define an object

what is a NoSQL database?

when structured, semi-structured, and unstructured data is all on one database

what are database interfaces?

processes used to add/update info to extract info from database

what are the two relational methods?

defining database structure & manipulating info in database

DDL

data definition language - SQL commands that add/modify the structure of a databases

what are the DDL commands?

CREATE, ALTER, DROP

CREATE commands

CREATE DATABASE - adds a new database

CREATE TABLE - adds a new table

CREATE INDEX- specifies index speeds of column

ALTER commands

ALTER TABLE - config tables columns, keys, & constraints

ALTER DATABASE - modifies properties of database

DROP commands

DROP TABLE - deletes table

DROP DATABASE - deletes database (n everything in it)

DML

data manipulation language - inserts/updates rows (records) and extracts info from them

what are the DML commands?

INSERT, UPDATE, DELETE, SELECT

INSERT command

INSERT INTO tablename - adds new row to table

UPDATE command

UPDATE tablename - changes value of table columns

*use WHERE command to specify or command will apply to all

DELETE command

DELETE FROM tablename - deletes rows from table

*use WHERE command to specify or command will apply to all

SELECT command

enables you to determine a query to retrieve data

SQL does not support access control: true/false

false: it does

Accounts that create an object automatically gain ownership: true/false

true

ALTER AUTHORIZATION

changes owner of an object

GRANT permission TO user

gives rights to an object to another account

CONTROL

user has all rights by defaults but can be derived of them

what are database access methods?

the process of which a user might run SQL commands

explain direct/manual access

uses an admin tool to connect into RDBMS database, then runs SQL commands to interact with data

list the database access methods

direct/manual access

query/report builder

programmatic access

user interface/utility access

backup & data export

explain query/report builder

uses queries (SELECT statements) to find data

explain programmatic access

a software app interacts with the database, uses programming language (writes script to interact with data)

explain user interface/utility access

GUI to interact with database

ex. Microsoft Access

explain backup & data export

BACKUP, RESTORE commands

what does dump mean?

a copy of a database/schema with records in the form of SQL statements

what is application architecture?

the way in which the functions of the application layer software are spread among the clients and servers in the network

CRM

customer relationship management

one tier / standalone database architecture

application, presentation, data hosted on ONE computer

two-tier architecture

data separated from application and presentation

three-tier architecture

application, presentation, data all SEPERATE

n-tier architecture

two or three tier architecture with complexities

ex. separate access control, monitoring services

what is a parent table?

The table that has the Primary Key in the relationship is the Parent table and the table that has the Foreign Key is the Child table

what is data persistence?

when you delete something but it leaves a trace of itself, still there

what are data stores?

a temporary or permanent repository of data

For which of the following is a relational database management system MOST commonly used?

storing info

Which of the following database concepts would MOST likely use a CSV file?

data importing