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;
