Chapter 7 Introduction to Databases

SQL

A computer language that makes relational databases work.

Physical data model

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.

Primary key

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

one-to-one relationship

where two entities or attributes share a unique connection with each other.

Data Modeling

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

Data Mining

A broad and open-ended analysis of extracted big data.

Table

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

Relational Database

A database that holds data on multiple related tables.

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.

Foreign Key

A field that corresponds to the primary key field in another table.

Form

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

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.

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.

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.

Data Analyst

A person who specializes in gathering and analyzing data to inform business decisions.

Field

A single attribute of each record in a table.

Record

A single instance of an entity stored in a database table.

Database

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

Report

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

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.

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.

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.

Big Data

Extremely large sets of data, usually automatically recorded, that can be analyzed to show insights into human behavior trends.

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.

NoSQL

Nonrelational databases that arise from big data.

SQL (Structured Query Language)

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

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.

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.

Big Data Analytics

The process of asking specific questions to help interpret big data.

Data Extraction

The process of gathering and organizing big data.

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.

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.

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.

Table Relationships

We often want to associate data in one table with data in another table. We do this by adding foreign key fields 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 that even though a phone number can belong to only one person, each person can have many phone numbers.

Microsoft Access

a DBMS developed by Microsoft that works effectively to create and manage small relational databases with a few users. 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.

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

Attribute

a property or characteristic of an entity.

Microsoft SQL Server

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

Entity

a real-world element that's being used in a business.

Relationship

an association or dependency between two entities. The sale of a product to a specific customer is a relationship between two entities.

MySQL

an open-source DBMS distributed by Oracle since 1995. 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.

Query

essentially questions that we can ask the database

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.

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.

Analytical data

historical data that's used to help people make decisions in the present. It's data that's meant to undergo deeper analysis.

Logical data model

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 DBMS. It also doesn't take system hardware into account.

Operational data

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.

Conceptual data model

the least complex type of data model as it focuses only on high-level concepts It. 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.