Programming Foundations: Databases

What should come instead of the ??? placeholder for this query to return all fields and records in the table? SELECT ??? FROM mytable;

*

In the ACID model, which term represents the fact that a transaction can't be divided into smaller parts?

Atomic

You can narrow down the results that a query returns by only asking for results where a _____ matches a given value.

Field; In order to reduce the number of records you get back from a query, you can filter the results based on the contents of a field. For example, you might ask the database to show you records for customers who have their 'State' field set to 'California'.

Which database is often used in a big data context?

Hadoop

What is true regarding composite keys?

When a primary key does not exist, a composite key can be used to uniquely identify and relate a record to other data.

A relationship connects two pieces of data in different _____ in the same _____.

tables; database

Which other table in your database will likely have a one-to-one relationship with the employees table?

the badges table

SQL

the language you use to communicate with a database

When is an associative table useful?

when records need to be related in a many-to-many relationship

When modeling a many-to-many relationship, how should you name the linking table?

with a combination of the names of the tables it's linking

If you don't use a number type to store numeric data, _____.

you need to take additional steps to process the data as a number whenever you use it

Defining relationships helps you to do what?

-reduce the repetition of data cross tables -model real-world scenarios and requirements -understand how your data should be stored

Before you create a database, what do you need to know?

-the names and data types of fields you'll use -how entries in different tables will be used together -what tables you'll need for your data

How many SQL clauses are in this query? SELECT width,height FROM Shapes

2, SELECT and FROM

When you use SQL statements to create or modify the structure of a database, what is SQL being used as?

A Data Definition Language, SQL can be used to create and modify the structure of database tables.

When using an aggregate function, how many results do you expect?

Aggregate functions return one value that describes a set of data.

The definition of how data in a database will be organized is called the...

Schema

When talking about data types, what do you call the group of types that represent text?

String types

In a database where you keep track of records for a school, what tables should you expect to find?

Students, Classes, and Grades

Relational databases can store___ but not___

Text, files or binary data and images but not graph data

When you move data from an unstructured form to a structured form, what benefit do you gain?

The ability to: -use data programmatically -easily locate data -easily sort data

In a one-to-many relationship, the value representing the 'many' side is what?

The foreign key, as it points to the primary key for the 'one' side of the relationship.

Denormalization

The process of intentionally duplicating information in a table in violation of normalization rules *gaining speed may reduce consistency

When first defining a table, what should you specify?

The table's name, the fields and type of data they contain, and the primary key and any referential constraints

If you can figure out the value of one non-key field in a row by looking at another non-key field in that same row, what do you violate?

Third Normal Form

T/F: If you store certain kinds of information, your database may be subject to certain compliance regulations.

True

T/F: fields should be named singularly.

True

Data Type

Type of information stored in each column: -Strings -Dates and Times -Numbers

What are two best practices for naming tables?

Use upper camel case and a plural form (i.e. CheckDeposits)

First Normal Form

Values in each cell should be atomic and tables should have no repeating groups

Third Normal Form

Values should not be stored if they can be calculated from another non-key field

Which WHERE condition can you use to find all records containing a first name starting with the letter "A"?

WHERE FirstName LIKE "A%";

A SQL statement that returns requested records from the database is called:

a SQL query

What is it called if you delete a record and the database goes on and deletes other records associated with that record?

a cascading delete

What would you use a relationship to connect?

a customer with their favorite table in the restaurant

What kind of condition represents a NULL value?

a date cell containing no data

If you reference a key from Table A in Table B, what is that value in Table B?

a foreign key

What is a database transaction?

a group of statements that runs or fails as a whole

When you need to create a many-to-many relationship, what do you need to generate?

a linking table that has a one-to-many relationship with two or more tables

A stored procedure is _____.

a predefined query or statement

In a database, what is a relation?

a set of attributes (columns) that describe information about specific instances (rows) of an entity

What does the term 'transaction' mean?

all of the steps for an action must be completed

A one-to-one relationship...

allows only one record to be connected to only one other record

In order to put a database into Third Normal Form, it must...

also be in First and Second Normal Form

When planning a database, what do you start with?

an Entity Relationship (ER) diagram

Which is a good example of a candidate key?

an employee ID number

When modifying a record, it's a good idea to specify the record _____.

as precisely as possible, ideally using the primary key

Which scenario represents a one-to-many relationship?

bank customers linked to their bank accounts

You can write SQL in:

database management software an app's source code a command-line console

Microsoft Access is generally considered a_____ database platform.

desktop

An index _____.

helps to increase the speed of lookups using a particular column at the cost of speed while modifying records

What is it called when a malicious user tries to change the way a SQL statement works by entering their own SQL?

injection

Which tasks can you accomplish using SQL as a DML?

inserting a record into a table

When telling the database that a certain field must not contain an empty value, you say that it is:

not null

3 Types of Relationshpis

one-to-many -->one favorite dish to many customers many-to-many -->many orders with many dishes to customers one-to-one -->customers to their PersonalInfo

Which is an example of referential integrity?

preventing the user from entering a record that refers to nonexistent data

First Normal Form tells you to do what?

remove repeating groups

When storing the text Mozambique in a column with a data type of VARCHAR(8), what would be saved in the database?

Mozambiq

Number Types

Integers Double Precision Floating point Decimals

What is the possible issue with this query? UPDATE mytable SET price=5;

It may update undesired records.

Strings

Alphanumeric characters and text -CHAR - fixed number of characters -VARCHAR - variable number of characters -other types for longer text

Which SQL command will you use to create a new database called "mydb"?

CREATE DATABASE mydb;

A unique value occurs only once in a given: -row? -column? -table?

Column

What is the name of a key that consists of different fields taken together to act as a unique identifier?

Composite key -combines tow or more fields to act as a unique identifier

What does CRUD stand for?

Create Read Update Delete

What would be a good name for a table containing customer details and contact information?

Customers

Database Needs (examples)

Customers Dishes Events Orders Restaurants Birthdays

To store the value 4:32PM, December 27, 2019, which data type would you use?

DATETIME

Referential Integrity

Databases are aware of relationships and won't allow users to modify data in a way that violates that relationship

Which ACID step requires that the database is updated when the transaction completes successfully?

Durable

Entity Relationship Diagram

ER Diagrams that use tables, fields, and relationships to plan a database

T/F: If a table is set to auto-increment the primary key, you'll need to know the next value and set it manually when you enter a record.

False

T/F: In any given query, you can only join together a maximum of two tables.

False

T/F: In order to sort results based on a field, that field needs to appear in the final output.

False

T/F: A database must include one or more relationship.

False, You can use tables in a database without defining any relationships.

In order to use records from more than one table in a query, you need to _____ the tables based on some matching criteria.

Join, Joining tables allows you to match rows from one table with rows on another table.

What is one advantage of using a spreadsheet to store data?

It can help you see gaps in the data

Second Normal Form

No value in a table should depend on only part of a key that can be used to uniquely identify a row

Null

Represents the absence of a value, indicates a value is missing It is NOT a data type, but rather a condition

When might you choose to denormalize a table?

Retrieving the data upon request would be slow or burdensome, and you are able to pre-calculate or store a copy of the data somewhere it can be retrieved faster.

When you store data in a database, what is one advantage you get over a plain spreadsheet?

Rows of data can be associated with each other across tables.

What is the correct SQL syntax to use when joining tables A and B on their "ID" field?

SELECT * FROM A JOIN B ON A.ID=B.ID

For a table that holds the purchase amounts in a grocery store over time, which query will likely return the highest value?

SELECT SUM(amount) FROM purchases;