Terms To Remember Flashcards
(233 cards)
1
Q
What is a row called in a database?
A
A Record
2
Q
What is a column called in a database?
A
Field
3
Q
Databases: what function do they provide?
A
They organize the information we receive so that we can use it
4
Q
Tables
A
A collection of rows and columns. A database can contain more than one table.
5
Q
The structure of tables in the database?
A
Schema. The database’s schema includes the information about the layout of tables and other information about the database itself.
6
Q
Relational Database
A
Tables are made up of rows and columns. Columns are the relation.
7
Q
What are common column data types?
A
Number, text, date, true/false value, binary data
8
Q
Composite Key
A
Two or more fields taken together to act as a unique identifier
9
Q
Foreign Key
A
Primary key from one table when referencing another table
10
Q
What are the three types of Database Relationships?
A
One-to-many
Many-to-many
One-to-one
11
Q
Transaction
A
A set of operations that all must be completed
12
Q
What does ACID stand for in relation to transactions?
A
Atomic
Consistent
Isolated
Durable
13
Q
What does Atomic mean?
A
Means that the transaction is indivisible, that pieces of it can’t be separated out.
14
Q
What does consistent mean?
A
Whatever the transaction does, it needs to leave the database in a valid or consistent state. Can’t violate integrity.
15
Q
What does Isolated mean?
A
While the activities in the transaction are being completed, nothing else can make changes to the data involved
16
Q
What does Durable mean?
A
Information actually gets written to the database.
17
Q
SQL
A
Structured Query Language
18
Q
What does SQL do?
A
Allows statements to be written for DBMS to interpret how to interact with data (DML)
Offers feature to manage the database itself, such as creating or modifying tables and controlling access to tables. (DDL and DCL)
19
Q
What does CRUD stand for?
A
Create
Read
Update
Delete
20
Q
A relationship connects two pieces of data in different _____ in the same _____.
A
tables; database
21
Q
Which is a good example of a candidate key?
A
an employee ID number. Any piece of data that uniquely represents a row is a candidate key, and if you have a value that occurs in the data naturally, that’s a natural key.
22
Q
How many SQL clauses are in this query?
SELECT Width,Height FROM Shapes;
A
two. Each keyword, SELECT and FROM, defines a single clause.
23
Q
In a database, what is a relation?
A
a set of attributes (columns) that describe information about specific instances (rows) of an entity
This was the correct answer
24
Q
When is an associative table useful?
A
when records need to be related in a many-to-many relationship. An associative (or linking) table relates foreign keys from different tables to associate their records.
25
What does the term transaction mean?
A transaction is a collection of steps that must all be completed in order for a change to be made to the database.
26
If you reference a key from Table A in Table B, what is that value in Table B?
a foreign key. Because it refers to a key in another table, in this context, the value is called a foreign key.
27
In a database where you keep track of records for a school, what tables should you expect to find?
Each of these tables holds a different kind of information, and because the tables can store more than one record, the table names are pluralized.
28
When talking about data types, what do you call the group of types that represent text?
string types. There are various kinds of string types to accommodate text of different lengths.
29
To store the value 4:32PM, December 27, 2019, which data type would you use?
DATETIME. This is the correct type, because it includes both a date component and a time component. The other options here do not.
30
Which condition represents a NULL value?
a date cell containing no data. A cell, regardless of its data type, is NULL when it has no value.
31
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. Storing numeric data in numeric data types gives you the ability to work with numbers directly, as you might do when you use mathematical operations in queries.
32
When planning a database, what do you start with?
an Entity Relationship (ER) diagram. Using an ER diagram, you can plan out what fields will appear on which tables, and how they're related.
33
Three Relationships?
one to one
one to many
many to many
34
Referential Integrity
Databases are aware of relationships and won't allow a user to modify data in a way that violates those relationships.
35
Which is an example of referential integrity?
preventing the user from entering a record that refers to nonexistent data
36
Defining relationships helps you to do what?
reduce the repetition of data across tables
model real-world scenarios and requirements
understand how your data should be stored
37
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
38
In a one-to-many relationship, the value representing the 'many' side is what?
foreign key
39
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
40
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
41
Which scenario represents a one-to-many relationship?
bank customers linked to their bank accounts. A certain customer can have multiple accounts, but a certain account can belong to only one customer.
42
A one-to-one relationship _____.
allows only one record to be connected to only one other record
43
First Normal Form (1NF) Normalization Rule?
Values in each cell should be atomic and tables should have no repeating groups.
44
Second Normal Form (2NF) Normalization Rule?
No value in a table should depend on only part of a key that can be used to unique identify a row
45
Third Normal Form (3NF) Normalization Rule?
Values should not be stored if they can be calculated from another non-key field
46
Denormalization
The process of intentionally duplicating information in a table, in violation of normalization rules
47
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
48
In order to put a database into Third Normal Form, _____.
it must also be in First and Second Normal Form
49
First Normal Form tells you to do what?
remove repeating groups
50
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.
51
A table has two rows with the same values in all columns. Which step can you take to have this table meet the first normal form (1NF) requirements?
Add a primary key to the table. The primary key will add a unique value for each row, and thus eliminate the repeating duplicate rows issue.
52
What is the foreign key in the table created after this command?
CREATE TABLE Models (
ModelID INT(6) NOT NULL AUTO_INCREMENT,
Color INT(6) REFERENCES Colors(ColorID),
PRIMARY KEY(ModelID)
);
Color
53
Which SQL command will you use to create a new database called "mydb"?
CREATE DATABASE mydb;
54
Which of these is not an example of a time when you would use an aggregate function?
looking up customers with the name Rafael Montresso. Aggregate functions are used to tell you about certain characteristics of multiple records. Requesting a record by specific field values is not an aggregate operation.
55
Which WHERE condition can you use to find all records containing a first name starting with the letter "A"?
WHERE FirstName LIKE "A%";
56
When using an aggregate function, how many results do you expect?
Aggregate functions return one value that describes a set of data.
57
What should come instead of the ??? placeholder for this query to return all fields and records in the table?
SELECT ??? FROM mytable;
*
The asterisk is used as a wildcard to retrieve everything from the given table.
58
True or False? In any given query, you can only join together a maximum of two tables.
FALSE. You can join together many tables as long as you tell the database which pairs of values on the tables are intended to match.
59
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.
60
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. True or false?
FALSE.
When the database automatically increments a key field, you don't need to worry about setting the value. The database will provide the next value in the sequence automatically.
61
When modifying a record, it's a good idea to specify the record _____.
as precisely as possible, ideally using the primary key
62
True or false? In order to sort results based on a field, that field needs to appear in the final output.
FALSE
A field used for sorting doesn't need to appear in the 'SELECT' clause of the query.
63
A SQL statement that returns requested records from the database is called:
a SQL query
64
Where can you write SQL?
in database management software
in an app's source code
at a command-line console
65
You can narrow down the results that a query returns by only asking for results where a _____ matches a given value.
field
66
Which of these tasks can you accomplish using SQL as a DML?
inserting a record into a table
67
When first defining a table, what should you specify?
A. the table's name
C. the fields and type of data they contain
E. the primary key and any referential constraints
68
Which sorting option shows dates from latest to earliest?
ORDER BY `Date` DESC
69
When you use SQL statements to create or modify the structure of a database, what is SQL being used as?
a Data Definition Language (DDL)
As a Data Definition Language, SQL can be used to create and modify the structure of database tables.
70
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
71
When telling the database that a certain field must not contain an empty value, you say that it is:
not null
72
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;
73
What is the possible issue with this query?
UPDATE mytable SET price=5;
It may update undesired records.
Without a WHERE condition, this query will update all records including possibly undesired ones.
74
Which database is often used in a big data context?
Hadoop and Spark are often used for big data applications.
75
Microsoft Access is generally considered a(n) _____ database platform.
desktop
Desktop databases are typically hosted on a workstation rather than a dedicated server, and they're designed to support a few to a few hundred users.
76
Relational databases can store all of these except what?
graph data
Some NoSQL databases are designed to store data arranged as graphs rather than in relations.
77
A stored procedure is _____.
a predefined query or statement
Stored procedures are queries that are stored on the server, and they can be called by developers or users in their queries.
78
An index _____.
helps to increase the speed of lookups using a particular column at the cost of speed while modifying records
As with denormalization, indexes offer a trade off.
79
What is it called when a malicious user tries to change the way a SQL statement works by entering their own SQL?
injection
This kind of attack involves someone injecting their own code into an application.
80
If you store certain kinds of information, your database may be subject to certain compliance regulations. True or false?
TRUE
If you store personally identifiable information (PII), health information, or some other kinds of information, your database may be subject to various regulations. Be sure to do your research and stay in compliance!
81
What is a database transaction?
a group of statements that runs or fails as a whole
Transactions combine multiple statements into a single logical block, where one failing statement rolls back the entire action.
82
What is a table?
A table stores and displays data in a structured format consisting of columns and rows that are similar to those seen in Excel spreadsheets.
83
What does SQL do?
SQL is used to access and manipulate a database.
It can be used to select & filter, add and remove data and tables, and much more.
84
The SELECT statement
used to select data from a table.
85
*
selects all columns of the table.
86
in order to select everything from the Customers table, we use the following:
SELECT * FROM Customers
87
Instead of *, we can specify a column that we want to select.
For example, let's select only the City column:
SELECT City FROM Customers
88
We can specify multiple columns in the SELECT statement by separating them with commas.
For example:
SELECT firstname, lastname FROM Customers
89
You can run multiple SQL queries, by separating them with semicolons.
For example:
SELECT firstname, lastname
FROM Customers;
SELECT firstname, city, age
FROM Customers;
90
SQL is case insensitive.
The following statements are equivalent and will produce the same result:
select city from Customers;
SELECT City FROM Customers;
sElEct ciTY From Customers;
Common practice is all upper case
91
The ORDER BY command
used to sort the data in a SELECT statement.
92
By default, ORDER BY sorts the results in
ascending order. (A to Z)
93
We can specify the ordering using the
ASC and DESC keywords.
SELECT * FROM Customers
ORDER BY firstname DESC
94
lets sort our Customers data using the city column, followed by the lastname column:
SELECT * FROM Customers
ORDER BY city ASC, lastname DESC
95
The SELECT statement returns all the rows in the table.
In case we only need a specific number of rows from the table, we can use
the LIMIT keyword.
96
let's select only the top 3 rows from our table:
SELECT * FROM Customers
LIMIT 3
97
We can combine the LIMIT with ORDER BY.
For example:
SELECT * FROM Customers
ORDER BY city DESC
LIMIT 3
Remember, the LIMIT keyword has to come after the ORDER BY in the SELECT statement.
98
We can also select rows starting from an offset.
For example, let's select 3 rows starting from the 3rd:
SELECT * FROM Customers
ORDER BY city DESC
LIMIT 3 OFFSET 2
The first row has the offset 0, which is why OFFSET 2 will start from the 3rd row in the result.
99
We can remove the duplicates using the
DISTINCT keyword.
100
To select only specific rows that satisfy a condition
WHERE command can be used.
101
For example, let's select the rows that have the age column greater than 30:
SELECT * FROM Customers
WHERE age > 30
102
The following comparison operators can be used in the WHERE condition:
> greater than
< less than
>= greater or equal
<= less or equal
103
o check for equality, the
equal to operator = can be used:
WHERE age = 42
104
To compare with a text value, put the
text value in single quotes.
WHERE firstname = 'James'
105
The not equal operator is
<>
106
The BETWEEN operator can be used to
Select rows that are in a range of values.
SELECT * FROM Customers
WHERE age
BETWEEN 30 AND 50
107
The AND operator
combines two conditions and is satisfied if both conditions are satisfied.
SELECT * FROM Customers
WHERE age >= 42 AND age <= 60
108
the OR operator
satisfied if any one of its conditions holds.
SELECT * FROM Customers
WHERE city = 'New York' OR city = 'Chicago'
109
the IN operator
Instead of multiple OR conditions for a single column, the IN operator can be used.
SELECT * FROM Customers
WHERE city IN ('New York', 'Chicago')
110
The NOT IN operator
allows you to exclude a list of specific values from the result set.
SELECT * FROM Customers
WHERE city NOT IN ('New York', 'Chicago')
111
When combining conditions, it is important to
use parentheses, so that the order to evaluate each condition is known.
112
Any text values in SQL statements need to be enclosed in
single quotes.
113
SQL supports filtering text based on a search condition.
This can be done using
the LIKE keyword, by specifying a pattern for the text.
SELECT * FROM Customers
WHERE lastname LIKE '%s'
For example, let's select all Customers where the lastname column value ends with an 's':
114
To match only a single character
the _ underscore symbol can be used.
SELECT * FROM Customers
WHERE firstname LIKE '_ames'
This will match all the Customer firstnames that start with any letter, then end with 'ames'.
115
NULL is
the absence of value.
This means that any field in our table with no value with have the value NULL.
116
To check for NULL values
the IS NULL operator can be used.
SELECT * FROM Customers
WHERE age IS NULL
117
Similarly, we can check for non-null values using
IS NOT NULL
SELECT * FROM Customers
WHERE age IS NOT NULL
118
the COUNT() function
is used to return the number of records of a select statement
SELECT COUNT(*)
FROM Customers
This will return the number of rows in the table.
119
SUM()
It returns the sum of the values in a column.
SELECT SUM(salary)
FROM Customers
120
The AVG() function
returns the average value of a numeric column.
SELECT AVG(salary)
FROM Customers
121
MIN() and MAX() are used to
return the minimum and maximum value of a column
SELECT MIN(salary)
FROM Customers;
SELECT MAX(salary)
FROM Customers;
122
the UPPER() function
can be used to convert text to uppercase.
SELECT UPPER(firstname)
FROM Customers
123
We can provide a new name for the column with the
AS keyword
SELECT UPPER(firstname) AS Name
FROM Customers
124
SQL allows to group records using the
GROUP BY clause
125
SELECT city, COUNT(*)
FROM Customers
GROUP BY city
This query will return the number of customers in each city, as well as the city name.
126
We can filter groups using the
HAVING clause
SELECT city, COUNT(*) AS c
FROM Customers
GROUP BY city
HAVING COUNT(*) > 1
ORDER BY c DESC
127
Creating a basic table involves
naming the table and defining its columns and each column's data type.
128
INT
whole number
129
FLOAT
floating point number
130
DOUBLE
A double precision floating-point number
131
DATE
A date in YYYY-MM-DD format
132
DATETIME
A date and time combination in YYYY-MM-DD HH:MM:SS format
133
TIMESTAMP
A timestamp, calculated from midnight, January 1, 1970
134
TIME
Stores the time in HH:MM:SS format
135
VARCHAR(M)
Variable-length character string. Max size is specified in parenthesis
136
TEXT
Large amount of text data
137
The CREATE TABLE command
defined the table name and the columns list in parentheses.
138
A column can also have a default value, which is applied to the rows when no value is provided for it. It is defined using the
DEFAULT keyword
CREATE TABLE Customers (
id INT,
firstname VARCHAR(128),
lastname VARCHAR(128),
salary INT DEFAULT 0,
city VARCHAR(128)
);
139
We can also specify if a column can or cannot have NULL values.
For example
CREATE TABLE Customers (
id INT,
firstname VARCHAR(128) NOT NULL,
lastname VARCHAR(128),
salary INT DEFAULT 0,
city VARCHAR(128)
);
Now, when inserting data into the table, the firstname column has to have a value.
140
When the table is created, it is empty.
We can insert data using the
INSERT command
INSERT INTO Customers VALUES
(1, 'John', 'Smith', 'New York', 5000)
141
The UPDATE statement allows you to
change values in a table.
UPDATE Customers
SET salary = 9900
WHERE id = 2
142
It is also possible to UPDATE multiple columns at the same time by comma-separating them:
For example:
UPDATE Customers
SET salary = 9900,
city = 'New York'
WHERE ID = 2
143
Similar to UPDATE, the DELETE command is used to
delete rows from a table
DELETE FROM Customers
WHERE ID = 4
144
The ALTER TABLE command is used to
add, delete, or modify columns in an existing table
For example, let's add a new column to our Customers table:
ALTER TABLE Customers
ADD age INT
145
we can delete an entire column with
DROP COLUMN
146
The ALTER TABLE command can also be used to rename a column
ALTER TABLE Customers
RENAME city TO location
147
We can rename an existing table using the following
ALTER TABLE Customers
RENAME TO People
148
To delete an entire table, the following command is used
DROP TABLE Customers
149
What is a Relational Database?
Stores data in tables
150
Normalization
The process of building the best database design
151
Keys
How to make everything unique in a table
152
Record, Rows, Tuple
All talking about the rows in a table
153
Columns, Field, Attributes
All talking about the columns in a table
154
Frontend
What the user sees
155
Backend
What's going on behind the scenes
156
Joins
Connects data from multiple tables
157
Atomic Value
The value stores one thing
158
Entity
This would be like a column header "name"
159
Attributes
This is the stuff that is under the heading, like the actual names themselves; this is the actual information
160
Foreign Key
The child in the parent to child relationship. It is a reference to a primary key.
161
Explain the Child and Parent relationship concept in Database Design
Parent would be something like the user name
Child would be the comments that this user makes.
Children inherit the values from their parents
162
What rules are followed with Keys?
Unique, never changing, never null
163
Superkey
Any number of columns that create unique rows
164
Candidate Key
The least number of columns needed to force every row to be unique
165
Natural Key
Something that you naturally want to store (username and password, for example)
166
Surrogate Key
Giving an ID to every row in every table
167
Simple Key
Key consists of one column
168
Composite Key
Key consists of two or more columns. Most common with natural keys.
169
Compound Key
Key that has multiple columns, and they're all keys.
170
Entity Relationship Modeling
It's when you draw out your database before you make it
171
Cardinality
Relationship type between rows (ex: Cardholder and card)
172
Modality
Whether or not the relationship is required. (ex: not null)
173
Normalization
When we go through our database and start correcting things that may case problems (integrity, repeating, etc.)
174
What are the three steps in normalization?
1st Normal Form, 2nd Normal Form, 3rd Normal Form. Can't do the next one until you've done the one before it.
175
What is second normal form?
Deals with a partial dependency. That's when a column only depends on part of the primary key. In order for that to happen, you need to have a compound or composite key.
176
What is third normal form?
Deals with what is called a transitive dependency. Which is when a column depends on a column that depends upon the primary key.
177
What is an index?
It's a list of where certain data points are.
178
What is a nonclustered index?
Just a separate reference that points to the data
179
What is a clustered index?
organizes the actual data. You can only have one of these. (like a phonebook)
180
Three main categories of Data Types?
Date, Numeric, and String
181
What is a string?
it is a combination of characters
182
What is numeric in data types?
Numbers, no quotes
183
What is date in data types?
stores a date. Think DATE, TIME, or DATETIME. Usually, colons separate.
184
Join?
Taking something complex in a database and presenting it in a user friendly way.
185
What is an inner join?
Going to take rows that are the same from one table and rows that are the same from another table and join them together to make a new table
186
What is an outer join?
Returning all values; left outer join, right outer join and full outer join
187
What is an alias?
It's when you rename something to make it easier to understand using the "AS" command
188
What are the four components of a database system?
Users
Database Applications
Database Management System (DBMS)
Database
189
What is a database?
A self-describing collection of related records
190
What is metadata?
Data describing the structure of the data in a database
191
What is a DBMS?
Serves as an intermediary between database applications and the database
192
What is normalization?
It's a process where we go through our table and try to eliminate the null values
193
What is a functional dependency?
ex: box price and cookie price; they are dependent upon one another
194
What is first normal form?
each cell within the table has one value (atomic)
195
What is second normal form?
Beyond all requirements of the first normal form, it must be fully functionally dependent on the entire primary key. No partial dependencies.
196
What is the most important concept to remember when it comes to normalization?
All your tables should have to do with one business concept.
197
Who originally developed SQL?
IBM
198
SQL is comprised of three components. What are they?
data definition language (DDL), data manipulation language (DML), and data control language (DCL)
199
What is DDL?
used to define and manage database structures
200
What is DML?
data definition and updating, and data retrieval (queries)
201
What is DCL?
used for creating user accounts, managing permissions, etc.
202
what do you end all SQL statements with?
semicolon
203
What does the INSERT INTO statement do?
Will add a new row to a table
204
What does the UPDATE statement do?
Will update the rows in a table which match the specified criteria
205
What does the DELETE FROM statement do?
Will delete the rows in a table which match the specified criteria
206
What does the SELECT statement do?
Extracts information you specify from the database.
207
The DISTINCT keyword may be added to the SELECT statement to
Suppress the display of duplicate rows
208
The WHERE clause
specifies the matching or filtering criteria for the records (rows) that are to be displayed
209
The multiple character wildcard character is a
percent sign
210
The single character wildcard character is
an underscore
211
Queried data can be categorized by what clause?
GROUP BY
212
HAVING
filters the categorized results that you get from the select statement
213
OUTER JOIN
Can be used to obtain data that exists in one table without matching data in the other table
214
What are the three stages of database development?
Requirements analysis stage, component design stage, implementation stage
215
Entity Class
this is like the recipe that someone follows for a cake, while the entity instance is actual cake that comes about as a result
216
What is an attribute?
they describe a specific entity. example are projectName, startDate, projectType, projectDescription
217
What is an identifier or key?
It identifies a specific instance in the entity class, for example, socialSecurityNumver, studentID, employeeID, emailAddress, departmentId
218
Unary Relationship
an entity is related to itself (Marriage)
219
Binary Relationship
one entity is related to another entity (most common) (employee and parking space)
220
ternary relationship
three entities involved in the relationship (doctor, patient, drug; intersection of those would be a prescription)
221
Relationships are named and classified by their cardinalities, which is a word that means
Count (as in the number of items in a set)
222
Minimum cardinality
minimum number of entities that need to participate in a relationship
223
What is concurrency?
People or applications may try to update the same information at the same time
224
What is interdependency?
Changes requested by one user may impact others
225
Atomic Transaction
all of the database actions occur or none of them does. Each step must be successful for the transaction to be saved
226
Dynamic Cursor
All changes made to the rows in the result set are visible when scrolling through the cursor
227
What do processing rights define?
Who is permitted to perform certain actions
When certain actions are allowed to be performed
228
Index
a data structure that contains a copy of some of the data from one or more existing data tables
229
Indexes are created on one or more ______ in a table
columns
230
B-Tree Index
Use pointers and several layers of nodes in order to quickly locate desired data
231
Bitmap Index
A table is created with the values of one attribute along the horizontal axis and the values of another attribute along the vertical axis. Can be really fast.
232
Hashed Index
a hashing algorithm is used in order to convert an input value into a location within an index (such as a B-tree index), which in turn contains or points to the actual data row.
233
What are the two categories of Business Intelligence systems?
Reporting systems
data mining applications
