Module 3 Unit 4: Using Databases

Question 1

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.

Answer 1

Database

Question 2

A database typically consists of tables of information that are organized into what?

Answer 2

Columns and Rows

Question 3

Each row represents a separate _________ in the database, while each column represents a single __________within a record.

Answer 3

Row - Record
Column - Field

Question 4

Database usage involves the following processes and tools:

Answer 4

Creation
Import/Input
Storage (data persistence)
Queries
Reports

Question 5

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.

Answer 5

Creation

Question 6

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.

Answer 6

Import/Input

Question 7

This step in the database processes/tools that represents a way for an application to store data persistently.

Answer 7

Storage

Question 8

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.

Answer 8

Query

Question 9

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.

Answer 9

Reports

Question 10

Two examples of a flat file systems

Answer 10

Spreadsheets (Excel)
Plain text file with delimiters for each column (CSV)

Question 11

A type of file that uses commas to identify the end of a column and a line feed for each row

Answer 11

Comma Separated Values (CSV)

Question 12

What are the advantages of databases over flat file systems?

Answer 12

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

Question 13

A highly structured type of database. Information is organized in tables (known as relations).

Answer 13

Relational Database

Question 14

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 ________.

Answer 14

Fields
Record

Question 15

What does RDBMS stand for?

Answer 15

Relational Database Management Systems

Question 16

The type of language that RDBMS use to maintain and query data in the database. (Microsoft SQL Server, Oracle Database, MySQL, Microsoft Office Access).

Answer 16

Structured Query Language (SQL)

Question 17

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

Answer 17

Primary key

Question 18

When a primary key in one table is referenced in another table, then in the secondary table, that column is referred to as what?

Answer 18

Foreign key

Question 19

Refers to the structure of the database in terms of the fields defined in each table and the relations between primary and foreign keys

Answer 19

Schema

Question 20

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.

Answer 20

Garbage In, Garbage Out (GIGO)

Question 21

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.

Answer 21

Constraints

Question 22

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)

Answer 22

Unstructured Database

Question 23

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.

Answer 23

Semi-structured database

Question 24

Associated information that helps identify the data in a semi-structured data.

Answer 24

Metadata

Question 25

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.

Answer 25

Document database

Question 26

Markup language that documents in a document database would very commonly use

Answer 26

XML (eXtensible Markup Language)

Question 27

A means of storing the properties of object without predetermining the fields used to define an object.

Answer 27

Key/Value pair database

Question 28

Why are document databases and key/value pair databases are non-relational?

Answer 28

There are no formal structures to link the different data objects and files

Question 29

A widely used key/value format

Answer 29

JavaScript Object Notation (JSON)

Question 30

Database engines dealing with a mixture of structured, unstructured, and semi-structured data

Answer 30

NoSQL databases (No SQL/Not only SQL)

Question 31

The processes used to add/update information to and extract (or view) information from the database.

Answer 31

Database interfaces

Question 32

Refers to SQL commands that add to or modify the structure of the database. (CREATE, ALTER TABLE/DATABASE, DROP, CREATE INDEX)

Answer 32

Data Definition Language (DDL)

Question 33

A DDL command that can be used to add a new database on the RDBMS server

Answer 33

CREATE

Question 34

A DDL command that allows you to add, remove (drop), and modify table columns (fields), change a primary key and/or foreign key.

Answer 34

ALTER TABLE

Question 35

A DDL command used to delete a table or database

Answer 35

DROP (DROP TABLE/DROP DATABASE)

Question 36

A DDL command that speeds up queries. The tradeoff is that updates are slowed down

Answer 36

CREATE INDEX

Question 37

Refers to SQL commands that allow you to insert or update records and extract information from records for viewing (a query)

Answer 37

Data Manipulation Methods

Question 38

A DML command that adds a new row in a table

Answer 38

INSERT INTO TableName

Question 39

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

Answer 39

UPDATE TableName

Question 40

A DML command that deletes records from the table.

Answer 40

DELETE FROM TableName

Question 41

A DML command that enables you to define a query to retrieve data from a table

Answer 41

SELECT