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.