Chapter 7 Database

4 ways of accessing information in relational databases

- Direct Access -Query Builder -User Interface Access -Programmatic Access

Query

A command sent to a database. Queries are most commonly used for CRUD operations (create, read, update, delete).

SQL

A computer language that makes relational databases work.

Field

A field describes a single attribute of each record in a table. For example, you may have a field for first names in a database. Every person's first name is stored in the first name field. Fields are comparable to columns on a spreadsheet.The schema of a database can prevent some fields from being left blank. This is called a NOT NULL constraint. It requires a value to be entered into the field whenever a new record is created.Databases allow for a constraint to be applied to a field in a table. Constraints are limitations on the value that can be entered into the field. For example, you can add a constraint to make sure the field is never empty, or to require the format of a telephone number.Constraints limit what is entered into a field. This means you can determine the type and form of information to be entered. This is used to avoid what is known as Garbage In, Garbage Out (GIGO).

Many-to-Many Relationships

A many-to-many relationship exists when the relationship between two entities is not unique on either side. Each student in a typical school has more than one teacher. Likewise, each teacher has multiple students. In a relational database, many-to-many relationships can't be represented by linking the two tables directly to each other. Since neither side of the relationship is unique, doing so would introduce redundancy into the database. Remember—in a well-designed database, each field must contain only one value.To get around this problem, we introduce a third table called an intermediary or junction table. This table sits between the teacher and student tables and keeps track of every teacher-student relationship.

Direct Access

A method of accessing a database directly using the database management system itself.

Query Builder

A method of accessing a database using a graphical user interface that allows users to select what actions they want to perform on a database.

Programmatic Access

A method of accessing a database using custom computer programs.

User Interface Access

A method of accessing a database using forms that allow users to interact with the database.

One-to-Many Relationships

A one-to-many relationship exists when only one side of the relationship is unique. For example, a single building may contain many classrooms, but each classroom is inside only one building.In a relational database, one-to-may relationships are usually represented by two tables linked together by a foreign key.

Data Scientist

A person who focuses on data analysis, especially as it relates to data mining and big data analytics. These positions require even more advanced programming and analytical skills.

Database Administrator

A person who is responsible for designing, maintaining, backing up, and securing an organization's databases.

Query Builder

A query builder provides a graphical user interface where people can select what actions they want to perform on a database. Then, the builder translates the user's choices into SQL commands and runs them. This is helpful when a person may need to interact with a database but doesn't know how to write SQL commands.

Record

A record is a single instance of data stored in a database table. For example, in a database of people, each individual person is a record. Generally, each record has one field designated as the primary key. Records are comparable to rows on a spreadsheet.

Report

A report is a formatted, easy-to-read listing of the results of a query. Without reports, query results would resemble a spreadsheet. When you search for an online video and are presented with a list of relevant options, the list is a report.

Which of the following acronyms describes the traditional way to make sure data stays consistent across the entire database? SQL DBA CRUD ACID

ACID

Which command is used to add, change, or remove fields on an existing table? ALTER TABLE INSERT INTO DROP TABLE CREATE TABLE

ALTER TABLE

Which data type uses historical data to make decisions in the present? -Conceptual -Logical -Analytical -Operational

Analytical

Historical data that is used to help people make decisions in the present. It's various categories include market data, transactional data, sensor data, natural language, and machine data.

Analytical Data

NoSQL

Because of the unstructured and complex nature of big data, we typically can't use SQL to query an organized collection of relational databases. For this reason, the nonrelational databases that arise from big data are often called NoSQL. NoSQL databases are sometimes called "Not only SQL," since the query languages that support them are often used in conjunction with certain relational database features that do support SQL.

Data Extraction

Before analyzing and interpreting big data, it needs to be gathered and organized. This is a challenge because big data is typically unstructured and distributed over several locations. And because it's so complex, combining various types of information, big data can't be processed quickly into a relational database. Very often, it includes images, documents, complex numbers, and video. And the data are constantly expanding.

The vast amount of data collected from Internet searches, social media posts, customer transactions, military surveillance, medical tests, weather sensors, and other data sources are collectively referred to as ________________. Metadata Omni Data Business Data Big Data

Big Data

Vast amounts of data are collected and stored online based on internet searches, social media posts, customer transactions, military surveillance, medical tests, and weather sensors. What is this known as? Big Data Data mining Data analytics Data extraction

Big Data

Big Data Analytics

Big data analytics is the process of asking specific questions to help interpret data. For example, a business might be interested in making an informed decision regarding product features. Medical researchers can use patient information and gene mappings to predict who might need specific treatments. Cities can take advantage of traffic sensor technology to better design roads and keep traffic moving smoothly.

Which data model focuses on high-level concepts? -Operational -Logical -Physical -Conceptual

Conceptual

Conventional Database Tools

Conventional database tools work well with structured data that are organized into relational databases and data warehouses. These tools are designed for relational database management systems (RDBMS) and use Structured Query Language (SQL) for querying and maintaining the databases. Traditional tools do not work well with big data, which requires query languages and other tools that are more flexible and dynamic.

Patricia, a database system manager for a large hotel chain, is setting up a relational database to track customer hotel registrations for each hotel. The table in the database currently includes fields (columns) for First Name, Last Name, Phone, Email, License Plate #, and Check-in Date. What can Patricia do to make sure that each record (row) in the database is unique? -Combine the Email field and the Phone field to use as the primary key. -Designate the Phone field as the primary key. -Designate the License Plate # field as the primary key -Create an additional Check-in ID field.

Create an additional Check-in ID field.

Which of the following are the four general tasks a user needs to perform for a database to function properly? Configure, Read, Update, Deny Create, Reuse, Unleash, Delete Create, Read, Update, Delete Create, Read, Unleash, Deny

Create, Read, Update, Delete

What is the general term for software that creates and manages databases? MySQL DBMS Tables Microsoft Access

DBMS

April is a database manager who wants to insert some new records (rows) into the company's customer contact database. Instead of using a database client to insert the records, she wants to use a command. Which of the following languages provides a command for inserting records into a database? HTML (HyperText Markup Language) DCL (Data Control Language) DDL (Data Definition Language) DML (Data Manipulation Language)

DML (Data Manipulation Language)

A way to help programmers and other related parties make sense of database information.

Data Modeling

What is asking specific questions to interpret Big Data called? Data extraction Data analytics Data ethics Data mining

Data analytics

The concern that Big Data is often obtained without the knowledge or consent of users falls under which Big Data concept? Data ethics Data extraction Data analytics Data mining

Data ethics

Which of the following needs to be done before Big Data can be analyzed and interpreted? Data analytics Data extraction Data mining Securing data

Data extraction

Data Mining

Data mining is a broad and open-ended analysis of extracted data. Data mining experts use automated tools and algorithms to discover trends or patterns in the data. These patterns can be descriptive, helping us understand what's going on in a business operation. Or the patterns might be predictive, putting data scientists in a better position to identify potential threats or forecast future trends.

Which of the following is a way for programmers and others to make sense of complex business flows? -Microsoft Access -Primary keys -Database management system -Data modeling

Data modeling

What is the technology that aggregates structured data from one or more sources called? Relational Non-relational Big Data Data warehousing

Data warehousing

A structured set of data held in a computer, usually in a way that is accessible by multiple concurrent users.

Database

Software that allows users to define, manipulate, retrieve, and manage data stored in a database. Examples are Microsoft SQL Server, Oracle Database, MySQL, and Microsoft Access.

Database Management System (DBMS)

Data Definition Language (DDL)

Database commands that change the structure, or schema, of the database.

Data Manipulation Language (DML)

Database commands that modify the records within the database and not the schema itself.

Which process exports a database into a format that can be read by another program like a spreadsheet? CSV Database dump Database backup CRUD

Database dump

Direct Access

Database management systems give administrators direct access to their databases. From here, they can write SQL commands to create new databases and interact with existing databases.

In a relational database table, columns are better known as ________________. Fields Categories Records Instances

Fields

Which of the following should not be used as a primary key in a table? First name Social Security number Username Employee ID

First name

Marty, a database administrator, has added a foreign key to a table in a database. Which of the following describes the purpose of a foreign key? To allow the storage of unstructured data. For one database to reference another database. To provide a way to distinguish records within a table. For one table to reference records in another table.

For one table to reference records in another table.

User Interface Access

For some databases, most users won't ever need to interact with the data directly. Instead, they fill out information on a form. A SQL query gets the information from the form and uses it to send a command to the database. Then, the results of the query are formatted as an easy-to-read report and sent back to the user. When you log onto your bank account or search for a library book, you're accessing a database through a user interface.

A value that's used to identify a record from a linked table is called a ________________. Foreign Key Borrowed Key Primary Key Linked Key

Foreign Key

A graphical interface that allows users to query a database by filling in information on the form.

Form

User-friendly interface for adding to or retrieving information from a database.

Form

Which of the following makes it easier for users to enter data into their tables? Forms Reports Queries Tables

Forms

Which SQL command is used to give a user permission to a specific database object? GRANT VALUES UPDATE REVOKE

GRANT

Programmatic Access

Humans aren't the only ones who need access to databases; many computer programs need it too. Developers can write code into their software that reads, writes, updates, and deletes database records programmatically.

Use this data type to look at data generated by computer hardware or software. You can better understand your technological landscape this way.

Machine data

Which type of analytical data can help you make investment decisions? Market Transactional Machine Sensor

Market

Use this data type to gauge markets trends in order to decide what to do in the present, such as which companies to invest in.

Market data

Form

Most database users don't write queries in query language directly. Instead, they enter information into a form that builds the query automatically.A good example of a form is the search bar of a search engine. When you enter search terms and press enter, the information from the form is entered into a query that retrieves the information.Forms are used in a relational database to build the objects in the fields through manual input. For example, when you fill out a soccer registration form online, the information you type in the form fields is entered into the database.

Primary key

Most records in a database require a way to distinguish them from other records. A primary key is a unique identifier that distinguishes records. Primary keys are usually numbers, but they can be any unique value. For example, a phone number, it is a primary key. Even though other people may share your same first and last name, nobody else shares your phone number.

Which of the following DBMS programs is open source? Oracle Database SQL Server Microsoft Access MySQL

MySQL

: Use this data type to observe language, such as social media posts that can be used to look for trends in customer preferences.

Natural language

What are non-relational databases that deal with Big Data often referred to as? RDBMS NoSQL CRUD SQL

NoSQL

Non-structured (unstructured) data

Non-structured data is data that doesn't follow a clear pattern. Imagine an archive of primary sources about the American Civil War that includes journals, newspapers, photographs, telegrams, and poems.A good way to keep track of all these different resources would be a document database. Document databases can store lots of different kinds of files. In our example, a user could enter a search term such as newspaper and get a list of the newspaper articles in the database. The file system on your computer is an example of a document database.Extensible Markup Language (XML) is a language like html, except that instead of the tags indicating how to display information, XML creates tags and codes to provide structure for the documents or other objects in a non-structured database. This allows for a variety of data files in the database.

Foreign key

Often, a table in a database references records in another table. It does this by using the primary key value of the record it' wants to reference. A foreign key is a primary key from another table..

Data that is produced by an organization's everyday operations. It's gathered in real time to provide a snapshot of what's happening in the present.

Operational Data

A database administrator is setting up a new table that will contain customer information. The fields include name, address, company, phone, email, and customer ID. The administrator is using the customer ID to uniquely identify each customer record. Which of the following BEST describes the customer ID field? Public key Primary key Foreign key Private key

Primary key

Which of the following is found in one or more columns of data and contains a unique identifier for the row? -Attribute -Foreign key -Relationship -Primary key

Primary key

A developer is working on an application that needs to be able to read, write, update, and delete database records. Which of the following database access methods should the developer use? User interface access Query builder Direct access Programmatic access

Programmatic access

Which database object is essentially questions that we can ask the database? Reports Forms Tables Queries

Queries

Query

Queries are questions used to retrieve data stored in database. Generally, it's not useful to look at an entire data table. Instead, database users build queries that retrieve the relevant information. For example, if you've ever searched for a single TV show from a streaming service, you've queried a database.

A method of asking questions about the data stored in a database.

Query

Edward, a database user, would like to gather some specific information from a database and then manipulate the data in a spreadsheet. Edward then wants to save that information in a file format that will be used by another application. Which of the following database features would work BEST to accomplish this? Query Report Form Table

Query

Retrieves specific information from a database. Can also be used to update, edit, and delete information.

Query

Gina is a database user and her supervisor has asked her to generate a report from the structured database. Gina does not know how to write SQL commands and no forms have been created. Which of the following would be her BEST option to generate the report? Query builder Direct access User interface access Programmatic access

Query builder

An easy-to-read listing of the results of a query.

Report

Formatted summary of information from a database.

Report

Which of the following database features would provide a formatted, easy-to-read listing of the results of a query? Record Form SQL command Report

Report

Which of the following database objects provides a summary of data? Reports Queries Forms Tables

Reports

Mary needs to view all records from the Clients table. Which command should she use? SELECT ^ FROM Clients SELECT * FROM Clients WHERE ^ FROM Clients WHERE * FROM Clients

SELECT * FROM Clients

Mary needs to view the records from the Clients table that pertain to Travis. Which command should she use? SELECT ^ FROM Clients WHERE name = Travis UPDATE * FROM Clients WHERE name = Travis SELECT Travis FROM Clients SELECT * FROM Clients WHERE name = Travis

SELECT * FROM Clients WHERE name = Travis

What is the computer language that makes relational databases work? Java Python C++ SQL

SQL

SQL (Structured Query Language)

SQL is a computer language used by most relational databases for creating queries. Using SQL, a database user can view, edit, delete, and create database records.

Which type of data have elements in common but do not fit into rigid rows and columns in a table? Structured Semi-structured Non-structured Non-relational

Semi-structured

: Use this data type to observe historical data from sensors, such as weather and climate information over time.

Sensor data

Big Data Ethics

Since big data is often obtained without the knowledge or consent of users, information can be sold to other organizations in a way that violates user privacy. Another potential problem is that big data analytics can uncover misleading or harmful information on innocent people. For example, we might falsely associate a person with terrorism. This same kind of analysis could also warn health insurance companies when an applicant has a higher risk for genetic disease. These examples illustrate why big data needs to be used with caution and ethical responsibility.

Semi-structured data

Somewhere between structured and non-structured data are semi-structured data. These data don't fit into the rigid rows and columns of a table but have some things in common. For example, think about classifying different kinds of animals. Every animal species has a name, average size, and life expectancy. However, a dog doesn't need a field to store its wingspan, and a butterfly doesn't need a field for its favorite prey.These kinds of data can be stored in a key/value pair database. A key is a category of information, kind of like a field. Using a key/value database, we could store information about a variety of objects. Some keys would be common to all the objects, but others would be different.Key/value pair databases do not have a query language. The pairs can't be queried; only the key can be queried. A direct request, such as the commands get, put, or delete, are used to retrieve specific data. The key must be known because there is no search option.

Structured data

Structured data is data that can be divided into discrete categories. For example, all the books in a library catalog have a title, author, publication year, etc.This kind of data is easy to store in a structured table, where every book has a value for every field. It is perfect for a relational database. Relational databases store data that can be retrieved and manipulated through a predefined schema in a query language.

A data structure in a relational database that is comprised of fields (columns) and records (rows).

Table

Stores raw data in a relational database.

Table

Table

Tables store the information in a relational database. Each table is comprised of fields (columns) and records (rows). There is no limit to the number of fields and records a table can contain, as long as there's enough storage space on the drive.

An application developer needs to store configuration data that can be retrieved each time the application is launched. The data must be in a format that could be easily viewed outside of the application and edited using any editor. Which of the following would be the BEST choice? Database Management System Binary flat file Relational database Text-based flat file

Text-based flat file

A business wants to set up a database to store customer and order information. They have decided to use a relational database. Which of the following is the main benefit of using a relational database? -The data can be organized into separate tables that are related to each other. -The data is stored as a single file for quick retrieval. -The data can be stored as unstructured data. -The data can be saved as a flat file.

The data can be organized into separate tables that are related to each other.

Schema

The schema is the general structure of a database. The schema defines the logical groupings and relationships between database objects such as tables and fields.

One-to-One Relationships

The simplest kind of relationship is a one-to-one relationship, where two entities or attributes share a unique connection with each other. ID numbers are a good example of this. Each student at a school has only one student ID number, and each ID number represents only one student. In a relational database, one-to-one relationships are generally represented by storing both items as fields in the same table.

Use this data type to look at what customers have historically purchased in order to decide what products to make in the present.

Transactional data:

Table relationships

You may want to associate data in one table with data in another table. To do this, add a foreign key field to one of the tables. Bringing data from multiple tables together is called a table relationship.The most common type of relationship between tables is a one-to-many relationship. An example of this is a phone number. A phone number can belong to only one person, but each person can have multiple phone numbers.

Oracle Database

a highly-scalable, robust, industry-standard DBMS. It was released in 1980 by Oracle Corporation. It is commonly trusted by larger companies to manage and secure enterprise databases.Oracle continually provides software updates to maintain a high quality product. Oracle DB supports more programming languages than Microsoft SQL Server and MySQL.

MySQL

an open-source DBMS that was originally released in 1995. Oracle acquired the technology and now owns and maintains MySQL. Many people who manage their own websites and databases prefer to use MySQL because it provides SQL database implementation that works well for small to medium websites.

A NOT NULL __________ is a rule that prevents certain fields in a database from being left blank. query builder schema scalability constraint

constraint

Microsoft Access

is a DBMS developed by Microsoft that works effectively to create and manage small relational databases with a few users. It was released in 1992 as part of Microsoft's Office suite.Access databases are composed of four kinds of database objects: tables, queries, forms, and reports. An advantage to using Access is that it is easy to set up and can be a great introduction to relational databases for inexperienced users.

Microsoft SQL Server

is a proprietary relational database management system developed by Microsoft. It was released in 1989.It is a database server that supports transaction processing, business intelligence, disaster recovery, and analytic applications. As an enterprise software solution, it provides robust security and protection for a company's databases.