Databases

Database Management System

A Database management system (DBMS) is software that allows users to define, manipulate, retrieve, and manage data stored in a database. The following table describes the four most popular DBMSs. While they all function similarly, it's important that you have a basic understanding of each in order to choose the most appropriate DBMS to meet your needs. Microsoft SQL Server 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. Oracle Database Oracle Database is 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 MySQL is 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. Microsoft Access 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. Database management systems can be evaluated and compared based on a number of different factors, including: Number of concurrent users supported. Scalability as databases expand. Speed of access. Support for different data types. Programming language support.

Table

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

Form

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

Query

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

Database Developer

A person who designs and implements databases, usually by writing SQL code and conducting performance tests to ensure database optimization. Database developers also design and implement databases, but their work focuses more on writing SQL code and conducting performance tests to ensure that database systems are optimized for the needs of clients. In addition to understanding SQL, database developers typically have programming expertise in other languages, such as Java and Python.

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. The work of data scientists also focuses on data analysis, especially as it relates to data mining and Big Data analytics. However, these positions require even more advanced programming and analytical skills. Data scientists work with machine learning algorithms that help them build predictive models and develop strategies for business success. They're proficient in using the latest business intelligence tools, including dashboards, data mining and visualization tools, reporting, and predictive analytics.

Database Administrator

A person who is responsible for designing, maintaining, backing up, and securing an organization's databases. A database administrator (or DBA) is responsible for designing, maintaining, backing up, and securing an organization's databases, which include inventory tracking, customer relationship management, and personnel data. While larger companies usually have a dedicated DBA, systems administrators often assume the role in smaller companies. DBAs are in charge of managing enterprise database platforms such as Microsoft SQL Server and Oracle Database. They often work closely with data analysts to query databases and provide information to help businesses make informed decisions.

Data Analyst

A person who specializes in gathering and analyzing data to inform business decisions. A data analyst specializes in gathering and analyzing data to inform business decisions. They use their expertise in mathematics and statistics to find relevant trends and patterns in data. Data analysts often work with sales and marketing teams to analyze and interpret performance data, create reports, and recommend ways to improve company processes. Their work often focuses on extracting and mining Big Data, which is the vast amount of data that's collected and stored online.

Database

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

Data Modeling

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

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

ACID

Which command is used to add, change, or remove fields on an existing table?

ALTER TABLE

Analytical vs. Operational Data

All organizations can benefit from analyzing data. There are, however, different kinds of data and each can be used most productively in certain situations. In the broadest sense, the two main categories are operational data and analytical data. The type of data you use will depend on the problem you're trying to solve. Operational Data Operational data is simply data that's produced by an organization's everyday operations. It's data that's gathered in real time to provide a snapshot of what's happening in the present. What exactly this kind of data is depends on what a specific organization does on a day-to-day basis. For example, if you're running a hardware company, operational data might include how many customers you have at a given time, your inventory levels, or your profit margins. These are usually things that are easily observed and measured, especially with the help of added technology. This data type is most useful for quick updates and least useful for deep analysis. Operational data answers the "What?" questions, like, "What were our inventory levels today?" It doesn't deal with hypotheticals but instead in concrete, strictly observable things. Analytical Data Analytical data is historical data that's used to help people make decisions in the present. It's data that's meant to undergo deeper analysis. Being historical data means that it isn't real-time relevant. In other words, it's data that doesn't apply to the literal here-and-now. Data doesn't have to be months or years old to be considered historical, though. Depending on the situation, data could be historical even if it was retrieved a few hours before. This data type can easily be visualized using charts and graphs. It can show trends that help those in charge make mission-critical decisions for the future. Data like this answers the "What if?" questions, as in, "What if we sold this product instead of another one? What would that likely do to our sales?" You could analyze historical data to try to answer this question. There are various categories of data that are considered analytical data, including the following: Market data: 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. Transactional data: Use this data type to look at what customers have historically purchased in order to decide what products to make in the present. Sensor data: Use this data type to observe historical data from sensors, such as weather and climate information over time. Natural language: Use this data type to observe language, such as social media posts that can be used to look for trends in customer preferences. Machine data: Use this data type to look at data generated by computer hardware or software. You can better understand your technological landscape this way.

Report

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

Which data type uses historical data to make decisions in the present?

Analytical

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

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

Big Data

Big Data refers to the vast amounts of data collected and stored online based on Internet searches, social media posts, customer transactions, military surveillance, medical tests, and weather sensors. The term can also refer to the tools and processes used by data engineers and analysts to make sense of this data. The table below describes key concepts associated with big data. 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. 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. 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. 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. 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. 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.

Which data model focuses on high-level concepts?

Conceptual

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?

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?

Create, Read, Update, Delete

What is the general term for software that creates and manages databases?

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?

DML (Data Manipulation Language)

Which of the following careers often requires expertise in mathematics and statistics to find relevant trends and patterns in data?

Data analyst

Which of the following careers often requires that one works with sales and marketing teams to analyze and interpret performance data, create reports, and recommend ways to improve company processes?

Data analyst

What is asking specific questions to interpret Big Data called?

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

Which of the following needs to be done before Big Data can be analyzed and interpreted?

Data extraction

Which of the following is a way for programmers and others to make sense of complex business flows?

Data modeling

Which of the following careers requires advanced programming and analytical skills related to data mining and big data analytics?

Data scientist

Operational Data

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.

What is the technology that aggregates structured data from one or more sources called?

Data warehousing

Data Modeling

Database Processes Since databases are used to securely store information and to create reports about this data, before getting too deep into creating your first database, you should consider the processes and servers tools that will be needed to create and use your databases. A few of the important processes to consider are: Creation - One of the first processes that should be considered, is to determine exactly what will be stored in the database, where the data will be stored, and how to make the data accessible to those who need. it. Input/import - The next process to consider is how the data will be entered into your database. In some cases much of the data can be entered manually. At other times, you may want to import the data from another source. Storage - Some applications can be used to generate and present data for visual consumption only. The calculator in Windows is a good example. When the calculator is closed, all the data it generated is lost. However, many other applications need some place for its data to be stored so it won't be lost when the application is terminated. This storage is often referred to as data persistence. Persistent data is typically stored on a hard disk. A database is one option for persistent data storage. Queries - The information stored in a database is useful only if it can be easily retrieved. One of the best ways to retrieve database information is to use queries. A database query is a request to access data from the database. Reports - Closely related to queries is the ability to take the data queried and to transform it or format it in a way that makes it easy to read and interpret. Databases vs. Flat File Systems As you consider using databases, you may be asking yourself why not just use a flat file system, such as an Excel spreadsheet or maybe even a simple comma separated value (CSV) file. While flat file systems are useful for such things as transferring information between systems or for tracking sales orders, they have several drawbacks. For example, consider the following advantages of a database over a flat file system: Processing speed - Databases can store thousands, millions, or even billions of data points. Imagine keeping track of a billion rows on a spreadsheet - it wouldn't be easy. Fortunately, a good database makes analyzing a million records just as easy as analyzing a few dozen. Backup and recover - Databases provide tools to backup information entered within seconds of it being committed. This means that through this backup process and through replication, lost data can be easily recovered. Forced Data types - A data type is an attribute attached to a value that tells the compiler or interpreter how the data should be used. Databases let you control or force data types and to validate the information entered. Concurrent use - Databases allow many individual users to access the data at the same time without having to wait or take turns. A database can support thousands or even millions of users at one time. This is difficult to do with a flat file system because of the high speeds required to track transaction. In most cases, when a single file is opened, a lock is placed on the file preventing it from being used by other users. Security - Databases have measures in place to make sure only authorized users can get to the data. Additionally, users can have different privilege levels. While a top-level administrator can view and edit all the data, lower-level users may be able to see records but not change or delete them. Database Modeling Creating databases for business purposes can be a complex process. Data modeling is a way to help programmers and other related parties make sense of complex business flow. These models help organize data and make sure that there's consistency in elements, such as naming conventions, default values, and the security apparatus within the database. It's like a blueprint that an architect creates to show how everything is supposed to fit together. These models can also help you easily identify missing or redundant data. There are various kinds of data models, all of which serve a different purpose and have varying levels of complexity. These include conceptual data models, logical data models, and physical data models. Database Keys and Other Vocabulary It is important to understand about database keys. A primary key is found in one or more columns of data and contains a unique identifier for the row (like a home address). Every entry must have a unique identifier in the primary key column. A foreign key is a column that refers to a primary key that exists in another table. These values can be duplicated, allowing an entry to reference a value from another table. In a database, an entity is a real-world element that's being used in a business. For example, each customer of a business is an entity. A product that a business sells is another example of a database entity. An attribute is a property or characteristic of an entity. For example, the customer's name and phone number are properties of an entity. The name and price of a product are attributes of that product. A relationship is an association or dependency between two entities. The sale of a product to a specific customer is a relationship between two entities. Conceptual Data Models A conceptual data model is the least complex type of data model as it focuses only on high-level concepts. This data model answers the question of what a system contains. It's an organized view of the data that you need to support the processes your business is running. It looks at data that's being used, but not at how it's processed or its physical attributes. The purpose of this kind of model is to organize and define business concepts and rules. It shows what all the entities are, their attributes, and their relationships. It's developed without looking at hardware specifications like storage capacity. This kind of model doesn't use a primary or foreign key. It can show that two entities are related, but it doesn't specify the attributes that make them so. These types of models are typically more straightforward and easier to read than other types. Logical Data Models A logical data model is a little more complex than a conceptual model because it expounds the details of the data. This data model answers the question of how a system should be structured. However, it does not show how to implement the database with a particular database management system (DBMS). It also doesn't take system hardware into account. The purpose of this model is to develop a technical map of rules and data structures. It provides the foundation on which a physical data model can be built. These kinds of data models are more technical and can appear very complex to the average user. It can include both primary keys and foreign keys in addition to entity names, entity relationships, and attributes. Logical data models typically include the following information: The objective and scope of the model. This communicates to the database developers what the end objective is. The names of the objects or entities in the model. This can include any technical jargon that relates to the project. Diagramming conventions. Business data points. Data abstractions. Physical Data Models A physical data model is the most complex type because it focuses on exactly how to implement a data model within a specific DBMS. It also includes technical and performance requirements for the specific hardware system the database will run on. A physical data model describes the data that's going to be needed for a single project and should provide enough detail so the database itself can be created.

Which of the following careers is responsible for backing up and securing an organization's databases?

Database administrator

Which of the following careers typically requires programming expertise in languages such as Java and Python, in addition to skills in optimizing database systems?

Database developer

Which process exports a database into a format that can be read by another program like a spreadsheet?

Database dump

Database Access

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

In a relational database table, columns are better known as ________________.

Fields

Which of the following should not be used as a primary key in a table?

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?

For one table to reference records in another table.

A value that's used to identify a record from a linked table is called a ________________.

Foreign Key

Match each database object on the left to its description on the right.

Formatted summary of information from a database. correct answer: Report User-friendly interface for adding to or retrieving information from a database. Form correct answer: Stores raw data in a relational database. Table correct answer: Retrieves specific information from a database. Can also be used to update, edit, and delete information. correct answer: Query

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

Forms

Which SQL command is used to give a user permission to a specific database object?

GRANT

Analytical Data

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.

Non-relational Database Challenges

Many traditional databases are what are known as relational databases. Non-relational databases (NoSQL databases) don't follow the traditional relational model. This is done to help overcome some of the challenges faced with traditional database management. There are, however, additional challenges presented by working with a non-relational database. Learning Curve One of the biggest challenges when dealing with non-relational databases is that there's a steep learning curve associated with them. A talent gap often exists because companies can't find enough knowledgeable recruits that have experience with them. Also, many IT professionals aren't yet familiar with big data technology like specific design principles and report generation. This problem is further compounded by the fact that there's no standard non-relational database program. Each database interface is separate from all others, meaning that they all vary much more than typical relational databases do. Overhead Costs Data mining can be an extremely powerful tool in today's environment, but an effective system for doing so comes at a steep cost. There are many things that need to be invested in to build this system, including personnel recruiting and establishing productive hardware and software environments. Validating and Securing Data A relational database makes it easy to clean and validate the data stored there. This becomes more complicated in a non-relational database. Traditional databases use ACID (atomicity, consistency, isolation, and durability) transactions to prevent problems with inconsistent data. Most non-relational database don't perform these transactions, which can lead to these data-consistency issues. Another challenge is making sure that data is kept secure. Compared to relational databases, non-relational ones have more security issues, such as weak password storage, the inability to use external encryption tools, vulnerability to attacks, and other problems. Being a newer technology, many of these issues will likely be addressed in time. But currently, security is a major problem to consider. Combining Approaches What kind of database you use depends largely and on what sort of project you're tackling. Non-relational databases are better if your focus is on simplicity, scalability, and availably. If you want to focus on accuracy and security, go for a relational database. Sometimes the solution is to combine the two approaches using data warehousing. Data warehousing is a technology that aggregates data structures from one or more sources. This allows you to use data from different kinds of databases at the same time.

Which type of analytical data can help you make investment decisions?

Market

Which of the following DBMS programs is open source?

MySQL

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

NoSQL

Types of Relationships

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. 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-many relationships are usually represented by two tables linked together by a foreign key. 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. Now, instead of having a many-to-many relationship between the two tables, each table has a one-to-many relationship with the junction table. Each record on this table signifies one teacher/student relationship.

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?

Primary key

Which of the following is found in one or more columns of data and contains a unique identifier for the row?

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?

Programmatic access

Which database object is essentially questions that we can ask the database?

Queries

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

Querying Databases

Query A command sent to a database. Queries are most commonly used for CRUD operations (create, read, update, delete). 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. User Interface Access A method of accessing a database using forms that allow users to interact with the database. Programmatic Access A method of accessing a database using custom computer programs. SQL A computer language that makes relational databases work. 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.

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

Relational Databases

Relational Database A database that holds data on multiple related tables. Table A data structure in a relational database that is comprised of fields (columns) and records (rows). Field A single attribute of each record in a table. Record A single instance of an entity stored in a database table. Primary Key A field in a table that uniquely identifies a record. Foreign Key A field that corresponds to the primary key field in another table. Query A method of asking questions about the data stored in a database. Form A graphical interface that allows users to query a database by filling in information on the form. Report An easy-to-read listing of the results of a query. Normalization A design technique that organizes data in a database in an optimal way. This results in it being easier to add, remove, and update data without introducing errors.

Non-relational Database Facts

Relational databases are powerful tools for storing data that share the same structure. However, not all data can be stored using rigid fields and records. These kinds of data are usually stored in a non-relational database. The following table looks at three different kinds of data: structured, non-structured (often called unstructured), and semi-structured. 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. 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. 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.

Relational Database Concepts

Relational databases work by linking fields from different tables to create complex and easily-searchable databases. Relational databases have many advantages over flat file databases, which typically store data in a single file in plain text format. Advantages include: Support for concurrent users. Scalability. Speed of access. Support for a variety of data types. Security through access control. Resiliency through tools for back up and replication. Flexibility in allowing user-defined actions. Ability to manage the information put in the database. Support for a variety of data, such as strings, numbers, and data fields. 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. 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. 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. 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. 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. 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. 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. 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). 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. 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. 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.

Which of the following database features would provide a formatted, easy-to-read listing of the results of a query?

Report

Which of the following database objects provides a summary of data?

Reports

Mary needs to view all records from the Clients table. Which command should she use?

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

What is the computer language that makes relational databases work?

SQL

Which type of data have elements in common but do not fit into rigid rows and columns in a table?

Semi-structured

Database Management System (DBMS)

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.

Non-relational Databases

Structured Query Language (SQL) A computer language used by most relational databases for creating queries. Structured Data Data that can be divided into discrete categories. Unstructured Data Data that doesn't follow a clear pattern. Semi-Structured Data Data that has some discrete categories but also other information that doesn't follow a clear pattern. Big Data Extremely large sets of data, usually automatically recorded, that can be analyzed to show insights into human behavior trends. Data Extraction The process of gathering and organizing big data. Data Mining A broad and open-ended analysis of extracted big data. Big Data Analytics The process of asking specific questions to help interpret big data. NoSQL Nonrelational databases that arise from big data.

Basic SQL Commands

Structured Query Language (SQL) is a computer language that makes relational databases work. A query language is a programming language created to add to, change, or search the datasets within a database. Any time you've searched for something online using search engines, video streaming services, or store catalogs, the computer almost certainly used SQL to find what you were looking for. For a database to function correctly, users need to be able to do four general tasks: add new information, access existing information, modify information, and delete information. An easy way to remember these is the acronym CRUD (create, read, update, and delete). Data Definition Language Commands CREATE TABLE The CREATE TABLE command can add a new table to an existing database. Example: CREATE TABLE books (id int,title text);Creates a new table with two fields—an integer field for the primary key and a text field for the title. ALTER TABLE ALTER TABLE is used to add, change, or remove fields on an existing table. It also lets you define primary and foreign keys. Example: ALTER TABLE booksADD author text;Adds a new text field to the books table to store the author of each book. DROP TABLE The DROP TABLE command deletes an entire table, including all the data stored in it. Example:DROP TABLE books;Deletes the books table and all its records from the database. CREATE DATABASE The CREATE DATABASE command allows you begin a brand-new database. Example:CREATE DATABASE library;Creates a new database using only one parameter. CREATE VIEW The CREATE VIEW command creates a virtual table populated from an SQL statement result-set. Example:CREATE VIEW book_authors AS SELECT column1, column2, ... FROM books WHERE junior_fiction;Displays a table with book authors from the junior fiction category. CREATE INDEX The CREATE INDEX command creates an index within a table which can be used to accelerate the speed of query retrieval time. Example:CREATE INDEX book_copyrightdate ON books (column1, column2, ...); Creates an index for copyright dates from the books table. Data Manipulation Language Commands INSERT INTO and VALUES These commands work together to add new records to an existing table. The INSERT INTO command tells the database which table should store the new record. VALUES tells the database what to write in each field. Example:INSERT INTO books (id, title, author)VALUES (12345, "The Cat in the Hat", "Dr. Seuss")Adds The Cat in the Hat to the books table. SELECT The SELECT command lets you view the specific information you want from the database. It's commonly used with the asterisk (*) operator, which tells the computer to include everything. Example:SELECT * FROM booksReturns all records from the books table WHERE The WHERE command lets you narrow your query to only the results you want. Example:SELECT * FROM books WHERE author = "Dr. Seuss"Returns all records from the books table where the value of the author field is Dr. Seuss. UPDATE and SET The UPDATE command makes changes to existing records in a table. It's usually used with the SET command, which tells the database the changes to make. Example:UPDATE booksSET title = "Green Eggs and Ham"WHERE id = 2345Finds all records with an id of 2345 (probably just one book) and changes the value of the title field to Green Eggs and Ham. DELETE The DELETE command deletes records from a table. Example:DELETE * FROM books WHERE id = 2468Deletes all records from the books table where the value of the id field is 2468. Using the primary key field is a good way to make sure you don't accidentally delete a wrong record. Data Control Language and Other Common SQL Commands This following table describes other common SQL commands that deal with backing up and restoring databases, as well as managing permissions. It's important to note that the SQL permissions are how the objects in the database are kept secure. When an object is created, the creator of the object becomes the owner of the object. The owner has the ability (through permissions) to grant rights to specific objects, tables, or columns to other users. Even if other users are granted rights, the owner retains complete control of the object. BACKUP DATABASE The BACKUP DATABASE command creates a backup copy of the database.Example:BACKUP DATABASE libraryTO DISK = "C:\Users\student\Desktop"Backs up the entire database to the desktop. (Many relational database management systems also provide stored procedures to backup individual tables.) GRANT The GRANT command gives a user specified permissions to a specific database object and is part of the data control language within SQL.Example:GRANT SELECT ON books TO marian;Gives the user marian permission to run select queries on the books table. REVOKE The REVOKE command removes the specified permissions to specific database objects and is also part of the data control language within SQL. Example:REVOKE DROP ON books FROM marian;Makes it so the user marian can't drop the books table. Database Dump Another way to back up a database is to export the data into a format that can be read by another program like a spreadsheet. This is called a database dump. It typically stores the database schema and data in a CSV file. The command syntax for exporting and importing a database dump depends on the database platform you are using.

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?

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.

A NOT NULL __________ is a rule that prevents certain fields in a database from being left blank.

constraint