CompTIA ITF+ Database Fundamentals

Relational Database Management Systems (RDBMS)

Often, many users will be viewing the same record—a product in an online catalog, for example—at exactly the same time. But, as soon as a user begins the order process, that record is locked from access by other users. The RDBMS will actually provide a message to let the second user know that the record is locked because someone else is in the middle of an update.

In a situation where a PC-based database and an application are both hosted on the same computer, what is the application architecture called?

One-tier

Which would be the LEAST desirable architecture option for a database that requires a high degree of scalability?

One-tier

Data Persistence

Persistent data is data that is stored permanently in a database. A copy of the data may be loaded into memory at processing time and lost when processing is complete, but the original data is safely stored in the database. All data stored in the database is persistent, whether or not it is accessed frequently.

Who would typically use a query/report builder to access database data?

Power users and programmers

Query and Report Builders

Query and report builders are typically used by those who need to interact closely with a database, but might not want to learn SQL commands. However, programmers also find them useful for quickly extracting data in some situations.

Microsoft SQL Server is an example of what?

RDBMS

RDBMS

Relational Database Management Systems A relational database is created and managed by an RDBMS. A database server is the computer on which an RDBMS is installed and running, as it is used to create and manage databases. A query/report builder may be a feature of an RDBMS—or it may be an additional application software package that can connect to a database managed by the RDBMS.

In a relational database, data is organized into tables that are made up of rows and columns. The term "record" is a synonym for which of these terms?

Row The rows in a relational database are also called records. The term "record" has a logical meaning: each patient has a record. It is also a term that was originally used for a row in a flat file, and the term continued to be used after the advent of database technology.

Using a programmatic database access method to interact with a relational database would require what type of commands?

SQL

What term is defined as the ability of a database to increase in size as more information is added to it without increasing costs at the same rate?

Scalability

A manager needs to view last month's sales figures. Which of the following functionalities can be done with a query statement?

Specifying criteria to match values in one or more fields

In the event of an application failure, which application architecture model would be the most disruptive to users?

Standalone

Two-Tier Architecture

Storing the database and the application on two separate servers makes for a two-tier architecture

SQL

Structured Query Language

Which language is typically used by Relational Database Management Systems (RDBMS) to maintain data?

Structured Query Language (SQL)

Database dump

Term used for a copy of the database or table schema along with the data records--all expressed as SQL CREATE and INSERT statements. The dump is used to create an exact duplicate of the database structure and load an exact copy of the data from the original database.

In a database where each employee is assigned to one project, and each project can have multiple employees, which database design option would apply?

The Project table has a primary key of ProjectID. The Employee table has a primary key of EmployeeID and a foreign key of ProjectID. Each of these tables needs a primary key, to uniquely identify each row with the corresponding ID field: ProjectID for the Project table and EmployeeID for the Employee table. Since each employee is assigned to only one project, the Employee table needs the ProjectID field as well, to identify which project each employee is assigned to. This field becomes the foreign key that relates with the Project table, and each ProjectID can appear in several records of the Employee table.

Why is a client-server application considered a two-tier architecture?

The database runs on a database server, while the application is run on the client computer.

You've written an SQL query for an "Order" table: SELECT ProductName, Price from ProductMaster. Now your mentor asks you to add this line to your query: ORDER BY PRICE. How will that affect your query results?

The list of product names and prices will now be sorted by price.

In which order are the rows typically stored in a relational table?

The order in which they were entered into the database Each time a row is inserted into the database, it is added to the end of the appropriate table. Consequently, rows are stored in the order in which they were loaded into the table.

Which statement is NOT true regarding SQL permissions?

The owner of a table can assign only the permission to view (not update) data in the table to other users.

You have been placed on a project to update the addresses of customers in a "Customer" table. Who can issue the SQL commands to give you permission to update the data in that table?

The owner of the "Customer" table

In a relational database, what is the relationship between a primary key and a foreign key?

The primary key is in one table and it is matched to the foreign key in a secondary table.

Why is it important to consider the number and size of columns included in a table schema?

The size of each row affects the amount of storage used, and how quickly it can be processed. Storage is important, but processing speed is often a critical factor in database applications. Rows must be kept as succinct as possible by carefully choosing field sizes, because each row is read into memory during processing.

Flat File System

The structure of a flat file system is simpler than a database structure, since it has only rows and columns—much like a basic spreadsheet. Flat file systems are still supported by most programming languages and continue to have many relevant uses, such as storing simple collections of data and exporting and importing data between systems.

Your manager has asked you to display the customer database schema. What useful information will your schema provide?

The structure of the database

Varchar Data Type

The varchar data type provides for a variable-sized field type. If a book title is twenty-seven characters long, twenty-seven bytes are stored. If a title is only three characters long, only three bytes are stored, resulting in a significant savings in storage space. Varchar specifies a maximum number of bytes per column as a limit, but stores only the number of bytes used.

Structured databases work well, but why is it sometimes a better choice to store data in unstructured and semi-structured databases?

There are certain types of data (PowerPoint presentations, text files, Word documents, etc.) that are more easily stored without the rigid formatting a structured database requires.

Why might a table called "Products" in a relational database have twelve rows?

There are twelve products.

What is the SQL UPDATE command used for?

To change the value in one or more fields in a table The UPDATE command specifies one or more field values to be updated. If a WHERE clause is added, those fields are updated only for the rows that satisfy the selection criteria. For example, the command could be used to change the grade for one specific student from a B to a C if the studentID was specified in a WHERE clause.

What is the purpose of the SQL DROP command?

To delete a table or an entire database It is a DDL command, which is very powerful in that it can delete an entire table or entire database, which includes all of the data as well

N-Tier Architecture

Used to describe an even more complex structure where parts of the application may reside on different servers.

The use of a Graphical User Interface (GUI) to allow users to search and add records is an example of which type of database access method?

User interface/utility An ATM machine is an excellent example of a user interface that allows users to access a database. For example, withdrawing $100 from a savings account via an ATM updates the database to decrease your account balance by $100.

One-Tier Architecture

When the application, which includes reports, forms, queries, etc., is hosted on the same computer as the actual database, it's called a standalone or one-tier database application architecture

XML vs HTML

XML and HTML are both markup languages, but have different purposes. HTML is for web page development. XML is for data storage.

Why might a language like Extensible Markup Language (XML) be commonly used in a document database?

XML makes it easier to parse and sort through the data in a variety of types of documents. Since Extensible Markup Language (XML) provides an easy way to identify individual pieces of data in different types of documents, it's a good choice for a document database where all the documents may be in different formats—spreadsheets, text files, Word files, etc. XML allows each document to be different and still have identifiable data inside.

CREATE DATABASE

adds a new database

CREATE INDEX

adds an index to the tables, by specifying one or more columns which will be used for faster searching and sorting.

CREATE TABLE

adds new tables to the database

SQL Server, Oracle, MS Access, and MySQL

all examples of Relational Database Management Systems (RDBMS).

UPDATE

not a DDL command because it modifies data values inside a table, but doesn't modify the structure of a table

ORDER BY

query that causes the query output to be sorted in ascending order.

How many students would be represented in a "Student" table containing 150 rows, 17 columns, 1 primary key, and 1 foreign key?

150

A field in a relational table corresponds to which part of a spreadsheet?

A column

Colum

A column can also be called a field

Which of the following statements is true about a column in a table of a relational database?

A column with naturally occurring unique values is a good choice to be the primary key. The column, or combination of columns, that becomes the primary key for the table, must be unique. If no such column exists naturally (i.e., social security number or employee id) then an automatically generated sequentially numbered column can be added to the table.

Database Corruption

A database can become corrupted in a variety of ways. A user can enter bad data that corrupts a table, a hacker can introduce a virus that changes permissions, or a program can have a bug that alters the wrong table. Either way, the error could be in the data or the structure, and both are covered by the database dump.

Why is the process of generating backups such an important feature of an RDBMS?

A database can contain mission-critical data, which an organization may not be able to afford to lose.

Why is the level of data integrity greater in a database than in a flat file?

A database can enforce data types for each column in a table, and can validate data as it is entered into those columns.

Data Integrity

A database does enforce data types for each field/column in a table; therefore, it can validate that the correct type of data is being entered and stored in each field.

Why might a database dump be a good option for backup/restore?

A database dump includes both the database data and the structure, so it could be restored whether the offending error was in the data or in the structure.

What is a database?

A database is a collection of related data organized in a structured manner for easier access.

Data Validation

A feature that helps users by reducing the possibility of errors during data entry.

Field

A field is an individual piece of data in a record. For example, a patient's last name would be a field in the patient record in a hospital database.

Why would a flat file system not be the optimum choice for data storage for an online shopping cart application?

A flat file system doesn't support multiple concurrent users. A successful online shopping application would be highly likely to have hundreds or thousands of users hitting the database at any given time. A flat file system is not able to support multiple concurrent users. Rather, it only provides access to one user at a time.

CREATE

A new database, table, or index is added with the CREATE command

Why is a query an important tool in database processing?

A query allows a user to extract only the data needed from a database by selecting specific rows and columns from a table.

Why do a report and a query work well together?

A query can be used to extract only the data based on specified criteria, while a report can format and summarize the data making it easier to read and interpret.

What is the relationship between queries and reports?

A query selects and retrieves the correct data, whereas a report formats and summarizes the output.

An attorney uses a standard form to capture information about new clients. Which type of database would best suit those needs?

A relational database

Which statement is NOT true regarding the relationship between a table and its components?

A table always has the same number of columns as it has rows.

Relational Table

A table contains rows and columns of data, much like a spreadsheet. The columns are the same for all rows. For example, if a "Customer" table includes columns like customerID, lastName, and firstName, then each row in this table includes these columns. A relational table is very structured with specific fields/columns; consequently, fields are never randomly placed. All rows in a relational table include the same columns. There are no rows with a unique collection of columns. All columns in a relational table apply to the same rows. There are no columns with a unique collection of rows.

Table

A table contains rows of data, which are also called records. For example, in a table containing data about a classroom, there would be one row/record in the table for each student.

How is a relational table structured?

A table is made up of rows and columns, where all of the rows include the same columns.

You've been tasked with changing the primary key in the Customer table from the CustID field to the AcctNum field. Which Data Definition Language (DDL) command can do that?

ALTER TABLE

You've been tasled with changing the primary key in the Customer table from the CustID field to the AcctNum field. Which Data Definition Lnaguage (DDL) command can do that?

ALTER TABLE

Primary Key

Added to a table for the purpose of making sure that each row in the table can be uniquely identified

You submitted the following SQL command for a "Customer" table: DELETE FROM CUSTOMER. What happened next?

All of the records from the "Customer" table were deleted.

You submitted the following SQL command for a "Customer" table: DELETE FROM CUSTOMER. What happened next?

All of the records from the "Customer" table were deleted. Without a WHERE clause to specify which rows should be deleted, all of the rows would be deleted. Adding a clause lik e WHERE state = MN" would cause only the rows with Minnesota customers to be deleted.

Index

An index on the table is created for the purpose of making sorting and searching easier and faster.

SQL ALTER command

An individual column can be deleted only by using the ALTER command

In a database application, what is the difference between a form and a report?

Both display data, but a form can be used to update the contents of the database as well.

Adding a new relational database, table, or index is a function of which Data Definition Language (DDL) command?

CREATE

Which is the correct sequence in the process of database creation?

CREATE DATABASE CREATE TABLE INSERT INTO In the database creation process, the database must be created first. It's the all-around container for all of the tables and data.

Columns

Columns can be in any order in a table. Some columns may be grouped together to make up a logical sequence (i.e., Last Name, Middle Initial, and First Name, or Address, City, State, and Zip Code), but even a random order of columns will work the same way. Each value for a given column has the same data type. The specifications of the table assign a data type to each column (i.e., string, number, date, etc.)

What type of flat file contains plain text with delimiters to set apart the columns?

Comma Separated Values (CSV)

ALTER TABLE

Command that can be used to add, remove, and change table columns, primary keys, and foreign keys

SQL DELETE command

Command used to delete one or more rows of data from an existing table

A database administrator is considering adding a primary key to enforce unique rows, defining a format for a phone number, and adding a rule so that a user must enter a date prior to today. What is the administrator adding to the database?

Constraints

You are designing a "Customer" table in a relational database. Which of the following fields would be the best candidate for a primary key?

CustomerNumber The primary key must be able to uniquely identify each customer from all other customers. In past decades, social security numbers were used for that purpose. Now, a good database designer would create a CustomerNumber field to use as the primary key and generate unique numbers for each customer.

What is the difference between the DELETE and DROP commands in SQL?

DELETE deletes rows from a table DROP eliminates the entire table

DDL

Data Definition Language

Which of the following statements best describes data persistence?

Data in memory is lost once the processing is completed. Data in a database is stored persistently and securely so that it can be used again in the future.

Which is the BEST example of importing data to populate a database?

Data records that are already stored in a digital format can be loaded directly into a database. The database expects to receive data in a digital format. Importing data is the process of copying existing digital data that is already stored on another device and pasting it into the database.

What is the term used for the file that includes a copy of the database along with the schema?

Database dump

When processing large numbers of transactions, how does processing speed compare between databases and flat file systems?

Database processing speeds are much faster than flat file speeds. A database is organized by relating data together, so its processing speed is much faster than it can be with a flat file. The data stored in a database always processes more quickly because only the parts of the data that are needed are loaded into memory.

What are the steps in the process of creating a database?

Determine how the database will be used by clients, where it will be hosted, and what information it will store.

Flat file processing speeds

Flat file processing speeds are slower than database processing because each entire row must be read into memory whether or not all of the fields in the row are needed.

Which type of constraint is added to a table for the purpose of establishing a relationship with another table?

Foreign key A relationship between two tables requires a common column between the two tables. A foreign key is added to a table for the purpose of being that common column that matches back to a primary key in another table.

INSERT

INSERT command only inserts data into an existing table and is not a DDL command

Key/Value Pair Database

In a key/value pair database, objects and their properties are stored, but each object doesn't have to have all of the properties.

Relational Database

In a relational database, all attributes of a record are defined up front, and each record has all of the same attributes as the other records.

How is a key/value pair database different from a relational database?

In a relational database, all attributes of a record are defined up front, and each record has all of the same attributes as the other records. In a key/value pair database, objects and their properties are stored, but each object doesn't have to have all of the properties.

Queries often retrieve data from multiple tables. If the same column name is used in two tables, how does SQL know which column to use?

In the SELECT statement, the column name is prefixed by the table name.

CREATE TABLE

Is a DDL command used to create a new table, and to establish a primary key at creation time. It cannot, however, be used to modify the primary key after it has been created.

What is the purpose of the SQL INSERT command?

It adds a new row to a table in a database

In a relational database table, what is the significance of creating a field with a varchar data type?

It allows a variable number of characters to be stored for a particular column in each row.

What is a true statement about Data Definition Language (DDL)?

It comprises SQL commands that can add to or modify the structure of a database.

Which is the best way to issue SQL commands to manipulate data in a relational database?

It depends on the user's level of SQL experience, overall purpose, and access permissions.

What is the purpose of a schema?

It describes the structure of a database.

Which statement is NOT true regarding the SQL INSERT statement?

It is a data definition language statement.

Unstructured/Semi-Structured

It is more difficult to store documents and files in fields in a database table. Unstructured and semi-structured databases are made specifically to handle that kind of data.

Which of the following is the best example of a direct/manual database access method?

Log into phpMyAdmin and run SQL statements at the command prompt.

Which software package is a Relational Database Management System (RDBMS)?

Microsoft SQL Server

How do Relational Database Management Systems (RDBMS) handle multiple users accessing the same database concurrently?

Multiple users can view the same record at the same time. If more than one user attempts to update the same record at the same time, the RDBMS locks that record so that only one user at a time can update.