Module 3 Unit 4: Using Databases Flashcards
An organized collection of information. The information is stored in a structured manner for easier access.
It is used both to store information securely and to report on the information it contains.
Database
A database typically consists of tables of information that are organized into what?
Columns and Rows
Each row represents a separate _________ in the database, while each column represents a single __________within a record.
Row - Record
Column - Field
Database usage involves the following processes and tools:
Creation
Import/Input
Storage (data persistence)
Queries
Reports
This step in the database processes/tools that involves defining what information the database will store, where it will be hosted, and how it will be accessed by clients.
Creation
This step in the database processes/tools that involves populating the database with data records, which can be updated manually using some type of form or can also be from another source, or both.
Import/Input
This step in the database processes/tools that represents a way for an application to store data persistently.
Storage
This step in the database processes/tools that is used to extract information more efficiently and allows the user to specify criteria to match values in one or more fields and choose which fields to display in the results so that only information of interest is selected.
Query
This step in the database processes/tools that functions a means of formatting and summarizing the records returned by a query so that the information is easy to read and interpret.
Reports
Two examples of a flat file systems
Spreadsheets (Excel)
Plain text file with delimiters for each column (CSV)
A type of file that uses commas to identify the end of a column and a line feed for each row
Comma Separated Values (CSV)
What are the advantages of databases over flat file systems?
Databases can enforce data types for each column and validate information entered as fields and records.
Databases can manage multiple tables and link the fields in different tables to create complex connections
Databases can support tens, hundreds or thousands, or even millions of users
Databases are also more scalable
Databases provide access controls and backup/replication tools
A highly structured type of database. Information is organized in tables (known as relations).
Relational Database
A table in a relational database is defined with a number of ________ represented by table columns and can be a particular data type.
Each row entered into the table represents a data ________.
Fields
Record
What does RDBMS stand for?
Relational Database Management Systems
The type of language that RDBMS use to maintain and query data in the database. (Microsoft SQL Server, Oracle Database, MySQL, Microsoft Office Access).
Structured Query Language (SQL)
Is used to define the relationship between one table and another table in the database. Each record in this field should have a unique value
Primary key
When a primary key in one table is referenced in another table, then in the secondary table, that column is referred to as what?
Foreign key
Refers to the structure of the database in terms of the fields defined in each table and the relations between primary and foreign keys
Schema
One of the functions of an RDBMS is to address this concept.
It is very important that the values entered into fields conform are consistent with what information the field is supposed to store.
Garbage In, Garbage Out (GIGO)
When defining the properties of each field, as well as enforcing a data type, you can impose this on the values that can be input into each field. (Ex: Primary key)
This can be applied at different levels. As well as applying rules to fields, they can be used at the table and schema levels too.
Constraints
Database that provides no rigid formatting of the data. The data in this database is typically much easier to create than structured data. (Ex: Images and text files, Word documents and PowerPoint presentations)
Unstructured Database
Sits somewhere between a structured and nonstructured database.
The data lacks the structure of formal database architecture. But in addition to the raw unstructured data, there is associated information that helps identify the data.
Semi-structured database
Associated information that helps identify the data in a semi-structured data.
Metadata
An example of a semi-structured database.
Rather than define tables and fields, the database grows by adding documents to it. The documents can use the same structure or be of different types.
Document database
Markup language that documents in a document database would very commonly use
XML (eXtensible Markup Language)
A means of storing the properties of object without predetermining the fields used to define an object.
Key/Value pair database
Why are document databases and key/value pair databases are non-relational?
There are no formal structures to link the different data objects and files
A widely used key/value format
JavaScript Object Notation (JSON)
Database engines dealing with a mixture of structured, unstructured, and semi-structured data
NoSQL databases (No SQL/Not only SQL)
The processes used to add/update information to and extract (or view) information from the database.
Database interfaces
Refers to SQL commands that add to or modify the structure of the database. (CREATE, ALTER TABLE/DATABASE, DROP, CREATE INDEX)
Data Definition Language (DDL)
A DDL command that can be used to add a new database on the RDBMS server
CREATE
A DDL command that allows you to add, remove (drop), and modify table columns (fields), change a primary key and/or foreign key.
ALTER TABLE
A DDL command used to delete a table or database
DROP (DROP TABLE/DROP DATABASE)
A DDL command that speeds up queries. The tradeoff is that updates are slowed down
CREATE INDEX
Refers to SQL commands that allow you to insert or update records and extract information from records for viewing (a query)
Data Manipulation Methods
A DML command that adds a new row in a table
INSERT INTO TableName
A DML command that changes the value of one or more table.
This can be used with a WHERE statement to filter the records that will be updated
UPDATE TableName
A DML command that deletes records from the table.
DELETE FROM TableName
A DML command that enables you to define a query to retrieve data from a table
SELECT
