Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

C175 Data Management Foundations > Lesson 2.8 To 3 > Flashcards

Lesson 2.8 To 3 Flashcards

1
Q

What is the UPDATE syntax

A 
UPDATE TableName
SET Column1 = Value1, Column2 = Value2, ...
WHERE condition;
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is the UPDATE syntax

A 
UPDATE Employee
SET Name = 'Tom Snead', 
    BirthDate = '2000-03-15' 
WHERE ID = 5384
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What would the result be?

UPDATE Employee
SET Salary = 42000;
A

The UPDATE statement has no WHERE clause, so the Salary in all rows is changed.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Refer to the Department table.

What is missing to change “Sales” to “Custodial”?

UPDATE Department
SET \_\_\_
WHERE Code = 82;
A

The SET clause names the column to change and the new value.

UPDATE Department
SET Name = 'Custodial'
WHERE Code = 82;
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Refer to the Department table.

What departments are changed?

UPDATE Department
SET Name = 'Administration'
WHERE ManagerID IS NOT NULL;
A
  1. The WHERE clause changes only rows that do not have a NULL manager.
  2. Only Technical Support has a NULL manager, so all other departments are renamed Administration.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What is the DELETE syntax

A 
DELETE FROM TableName 
WHERE condition;
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Refer to the table

What syntax would be used for this?

A 
DELETE FROM Employee
WHERE ID = 6381;
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What are the results?

DELETE FROM Employee
WHERE Salary > 40000 AND 
      Salary < 80000;
A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Refer to the table

What is missing to delete only Sales?

DELETE FROM Department
WHERE $ ;
A

Code = 82

Only the Sales row has Code 82, so only the Sales row is deleted.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Refer to the table

What departments are deleted

DELETE FROM Department
WHERE ManagerID = 6381;
A

Sales and Marketing

Two rows have ManagerID 6381, so both rows are deleted.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What is the TRUNCATE syntax

A 
TRUNCATE TABLE TableName;
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Refer to the Table | List

  1. Unique Keys
  2. Not Unique Keys
  3. Composite Primary
  4. Not Minimal
A
  1. ID and Number together is unique
  2. each column on its own wouldn’t work as a primary key in this table because neither is unique
  3. ID & Number are unique, so (ID, Number) is a composite primary key
  4. ID, Number, Relationship is minimal beacuse relationship is Unnecessary
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Why can’t (ID, Relationship) be the primary key of Family?

A

(ID, Relationship) cannot be the primary key because (6381, Daughter) is repeated. Composite primary keys must be unique.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q
  1. The primary key of the PhoneNumber table is not indicated with a solid circle.
  2. Explain how (AreaCode, Exchange, Number) is the primary Key?
A
  1. (AreaCode, Exchange, Number) is unique, non-null, and minimal; which makes it a composite primary key.
  2. Occasionally, a primary key includes all table columns.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Composite keys can also be referred to as composite primary keys, why is this?

A
  • While all composite primary keys are primary keys, not all primary keys are composite.
  • Some primary keys can be simple, consisting of just one column.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

The CREATE TABLE statement uses the keywords PRIMARY KEY; to indicate the ID column is the table’s primary key this would be done how?

CREATE TABLE TableName (
    Column1 DataType [Constraints],
    Column2 DataType [Constraints],
    ...
    ColumnN DataType [Constraints],
    \$\$$ (ColumnName) -- Specify primary key if necessary
);
A 
CREATE TABLE Employee (
   ID        SMALLINT UNSIGNED,
   Name      VARCHAR(60),
   Salary    DECIMAL(7,2),
   PRIMARY KEY (ID)
);
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

The CREATE TABLE statement uses the keywords PRIMARY KEY ; to indicate the ID column is the table’s primary key this would be done how?

CREATE TABLE TableName (
    Column1 DataType [Constraints],
    Column2 DataType [Constraints],
    ...
    ColumnN DataType [Constraints],
    \$\$$ (ColumnName) -- Specify primary key if necessary
);
A 
CREATE TABLE Family (
   ID           SMALLINT UNSIGNED,
   Number       SMALLINT UNSIGNED,
   Relationship VARCHAR(20),
   Name         VARCHAR(60),
   PRIMARY KEY(ID)
);
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

When would the PRIMARY KEY constraint include multiple columns?

A
  1. The PRIMARY KEY constraint includes multiple columns when the primary key is composite.
  2. Making this not null, minimal, & unique
both the combination of (ID, Number) is necessary
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

If ID is an auto-increment column, why would this be incorrect?

INSERT INTO Employee (ID, Name, Salary)
VALUES (3, 'Maria Rodriguez', 92300);
A
  1. If ID is an auto-increment column, the ID should not be listed in the INSERT statement.
  2. The database assigns the ID automatically.
INSERT INTO Employee (Name, Salary)
VALUES ('Maria Rodriguez', 92300);
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Refer to the table

SELECT CountryName
FROM Country
WHERE ISOCode3 = 'GEO';

A

Georgia is the CountryName for which the value of the primary key ISOCode3 is GEO.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Refer to the table

  1. Which properties is satisfied by the composite column (ContinentPopRank, Area) ?
  2. Can (ContinentPopRank, Area) be a primary key of the Country table?
A
  1. (ContinentPopRank, Area) is unique. Although neither ContinentPopRank nor Area is unique, the combination (ContinentPopRank, Area) uniquely identifies each row.
  2. (ContinentPopRank, Area) is minimal, since both ContinentPopRank and Area are necessary for uniqueness.
  3. Primary keys must be unique and not NULL. Composite primary keys must be minimal.
    (ContinentPopRank, Area) has NULL values and therefore cannot be a primary key.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What is needed to complete the code?

A 
   Primary Key (TLD)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What is a foreign key?

A

A foreign key is a column (or set of columns) in one table that references the primary key in another table.

It establishes a relationship between the two tables.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

What is the purpose of a foreign key?

A

To maintain referential integrity within the relational database structure.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
What must match between foreign keys and primary keys?
The data types must be the same.
26
Can foreign key values be different from primary key values in terms of naming
Yes, foreign key values can have different names from primary key values, as *they are independent identifiers* used to establish relationships.
27
What are the possible values for foreign keys?
* Repeated * NULL
28
What does referential integrity ensure?
Foreign key values must either be `NULL` or *match a value* of the primary key from the referenced table.
29
In the Department and Employee example, which column in the Department table is a foreign key?
Manager.
30
What does a self-referencing foreign key do?
It points to its own primary key.
31
Fill in the blank: Foreign keys can be ______, meaning they consist of multiple columns.
composite.
32
How are foreign key constraints specified?
Within `CREATE TABLE` statements.
33
What happens if an operation leaves a foreign key *without a corresponding* primary key?
The database system rejects the operation.
34
1. *Different foreign keys in a table refer to the same primary key in another table.* 2. What is this an example of?
1. This is an example is simply a table with **multiple foreign keys**, *pointing to a single table*. 2. The scenario could be confused with being called "bidirectional". ## Footnote Bidirectional relationships involve two tables with foreign *keys referencing each other*, creating a two-way link.
35
What is a key characteristic of composite foreign keys?
They *must match* a composite primary key in another table.
36
How can **Foreign key** values be NULL.
1. If the foreign key is `NULL`, it simply means that **no relationship** has *been established for that particular record*. 2. A foreign key establishes a link between two tables based on a relationship between the data in the tables.
37
What are the referential integrity violations?
1. `4407` does not match any value in `ID` and violates referential integrity. 2. Since `NULL` doesn't correspond to any actual ID in the Employee table, this constitutes a referential integrity violation.
38
What is the referential integrity violation?
1. `(6381, 4)` does not match any value in` (ID, Number)` and violates referential integrity. 2. Referential integrity can be violated if *a primary key is changed*, a foreign key is updated, a row containing a primary key is deleted, or if a row containing a foreign key is inserted.
39
What is the referential integrity violation?
1. `(NULL, 1)` is partially `NULL` and violates referential integrity. 2. Referential integrity can be violated if a primary key is changed, a foreign key is updated, a row containing a primary key is deleted, or if a row containing a foreign key is inserted.
40
What is the referential integrity violation?
Updating the `Employee` primary key to `8888` violates referential integrity because the foreign key `2538` no longer exists in `Department`.
41
What is the referential integrity violation?
Updating the foreign key to `3333` violates referential integrity because `3333` does not match a primary key value
42
What is the referential integrity violation?
Deleting `Employee` primary key `6381` violates referential integrity because the foreign key `6381` _no longer exists_ in `Employee`.
43
What is the referential integrity violation?
Inserting foreign key `0202` violates referential integrity because `0202` does not match a primary key value.
44
What is the the syntax for a `CREATE TABLE` statement with a `FOREIGN KEY` constraint
``` CREATE TABLE TableName ( column1 datatype, column2 datatype, ... PRIMARY KEY (column), FOREIGN KEY (foreign_key_column) REFERENCES parent_table(parent_table_column) ); ```
45
From this image and code we can aknowledge that what data is shared? `CREATE TABLE Department ( Code TINYINT UNSIGNED, Name VARCHAR(20), ManagerID SMALLINT UNSIGNED, PRIMARY KEY (Code), FOREIGN KEY (ManagerID) REFERENCES Employee(ID) );`
The `Department` table is created with a `FOREIGN KEY` constraint that `REFERENCES` the `Employee ID` column.
46
What is the order of FOREIGN KEY constraint and column declarations
The order is not significant however, column declarations normally appear first, followed by the PRIMARY KEY constraint and the FOREIGN KEY constraint.
47
In a FOREIGN KEY constraint, where are parentheses required? ``` CREATE TABLE TableName ( column1 datatype, column2 datatype, ... PRIMARY KEY (column), FOREIGN KEY (foreign_key_column) REFERENCES parent_table(parent_table_column) ); ```
Parentheses are required around both the foreign key and primary key column names.
48
How does adding a `FOREIGN KEY` **constraint** to a table effect rows and columns?
1. **Ensuring Data Integrity**: Validates foreign key values against primary key values, which may limit valid rows. 2. **Enforcing Cascade Effects**: Automatic changes from actions like `ON DELETE CASCADE` can indirectly affect row counts. 3. **Restricting Insertions**: Limits rows if corresponding *referenced rows do not exist*. 4. **Establishing Relationships**: Links tables for better data organization.
49
In the `Department table`, first three columns; which foreign key value violates referential integrity?
1. Since no employee has `ID 3829`; it violates referential integrity. 2. Referential integrity can be violated if a primary key is changed, a foreign key is updated, a row containing a primary key is deleted, or if a row containing a foreign key is inserted.
50
Why do both `(6381, 4)` & `(6381, NULL)` foreign key values violate referential integrity?
* (6381, 4) does not match any (ID, Number) in a Family row and thus violates referential integrity. * (6381, NULL) is partially null and thus violates referential integrity.
51
Is it true that if a column is partially `NULL` that this should violate referential integrity?
`Manager` is a single-column, or simple, foreign key. A NULL in a simple foreign key does not violate referential integrity.
52
# The row containing primary key `2538` is deleted. How would `RESTRICT` access this situation?
`RESTRICT` rejects the delete, since employee `2538` manages `Engineering`. ## Footnote Prevents the deletion or update of a parent record
53
# The row containing primary key `2538` is deleted. How would `SET NULL` access this situation?
`SET NULL` sets matching foreign keys to `NULL`. ## Footnote allows child records to exist but removes their link to the deleted parent
54
# The row containing primary key `2538` is deleted. How would `SET DEFAULT` access this situation?
`SET DEFAULT` sets matching foreign keys to the foreign key default value, `6381`.
55
# The row containing primary key `2538` is deleted. How would `CASCADE` access this situation?
`CASCADE` deletes all rows with matching foreign key values. ## Footnote ensures all dependent records are cleaned up
56
What is the syntax for *referntial integrity actions* such as ```RESTRICT, SET NULL, SET DEFAULT, CASCADE``` ## Footnote Referential integrity actions on *primary key DELETE*.
``` CREATE TABLE child_table ( id INT, parent_id INT, FOREIGN KEY (parent_id) REFERENCES parent_table(id) ON DELETE CASCADE -- or SET NULL, SET DEFAULT, RESTRICT ); ``` The syntax for referential integrity actions in SQL starts with ON DELETE or ON UPDATE.
57
# Refer to the Employee and Department tables. * What are the results of the actions below? * `RESTRICT`, when the row containing `Maria Rodriguez` is deleted.
1. `RESTRICT` rejects referential integrity violations. 2. If `Maria Rodriguez` is deleted, the managers of `Sales` and `Marketing` would not match any ID, so the delete is rejected.
58
# Refer to the Employee and Department tables. * What are the results of the actions below? * `SET NULL`, when `Lisa Ellison's` ID is changed to `1001`.
1. `SET NULL` nullifies matching foreign keys. 2. Since the `Lisa Ellison` manages `Engineering`, changing Lisa's ID sets the `Engineering` manager to `NULL`.
59
# Refer to the Employee and Department tables. * What are the results of the actions below? * SET DEFAULT, when Lisa Ellison's ID is changed to 1001.
* `SET DEFAULT` sets matching foreign keys to a default value. * Since `Lisa Ellison` manages `Engineering`, the `Engineering` manager is set to a default value, which must be a valid primary key in the `Employee` table. * Any defaults value relies on whatever default value was *specifically set during the table's definition*.
60
# Refer to the Employee and Department tables. * What are the results of the actions below? * `CASCADE`, when `Maria Rodriguez`' ID is changed to `2022`.
1. `CASCADE` propagates primary **key changes** & deletions accordingly to matching foreign keys. 2. Maria Rodriguez manages Sales and Marketing, so `Sales` and `Marketing` managers are set to `2022`.
61
# Refer to the Employee and Department tables. * What are the results of the actions below? * `CASCADE`, when `Maria Rodriguez` is deleted.
1. `CASCADE` propagates primary key changes & **deletions** accordingly to matching foreign keys. 2. Maria Rodriguez manages Sales and Marketing, so the `Sales` and `Marketing` rows are deleted.
62
What is the create & alter syntax for creating foreign key constraints with the `ON UPDATE` and `ON DELETE` clauses in SQL.
``` CREATE TABLE child_table ( column1 datatype, column2 datatype, -- other columns CONSTRAINT fk_name FOREIGN KEY (child_column) REFERENCES parent_table (parent_column) ON DELETE action ON UPDATE action ); ```
63
# Foreign key constraints with ON UPDATE and ON DELETE clauses. Explain how `DELETE CASCADE` creates this result
`ON DELETE CASCADE` causes the database to delete the row with ManagerID `7343` when the employee with ID `7343` is deleted.
64
# Foreign key constraints with ON UPDATE and ON DELETE clauses. Explain how `UPDATE SET NULL` creates this result
`ON UPDATE SET NULL `causes the database to set `ManagerID 2538` to `NULL` when the `Employee ID 2538` is changed to `8754`.
65
What is referential integrity?
A relational database requires that foreign key values must either be fully NULL or match a primary key value.
66
What does the `CASCADE` action do in referential integrity?
Deletes or updates the corresponding child records automatically *when a parent record is deleted or updated*.
67
What happens when SET NULL is applied in referential integrity?
Sets the foreign key value in the child records to `NULL` when the *parent record is deleted or updated*.
68
What is the effect of the SET DEFAULT action in referential integrity?
Sets the **foreign key value** in the child records *to its default value* when the parent record is deleted or updated.
69
What does the RESTRICT action do in referential integrity?
**Prevents** the *deletion or update of a _parent_ record* if there are any corresponding child records.
70
What are common causes of referential integrity violations?
Violations can occur through: * Updating a primary key. * Updating a foreign key. * Deleting a row with a primary key. * Inserting a row with a foreign key that does not match any primary key.
71
How can referential integrity violations be _identified_?
Check **foreign key values** in tables to find violations and `NULL` values.
72
What does a fully NULL foreign key consist of?
All columns being NULL.
73
True or False: A NULL in a simple foreign key violates referential integrity.
False.
74
What happens when a **primary key** is updated to a value that doesn't exist?
It violates referential integrity.
75
What is the default action in MySQL if no specific action is defined in the ON UPDATE or ON DELETE clause?
RESTRICT.
76
What limitation does `SET NULL` have in MySQL?
It is *not allowed* if the foreign key column is defined as `NOT NULL`.
77
What is the role of foreign keys in maintaining referential integrity?
Foreign keys maintain referential integrity between tables by ensuring a match with primary keys or being NULL.
78
The _______ action sets the foreign key to a predefined default value if the referenced primary key is deleted or updated.
SET DEFAULT
79
What does the ON DELETE CASCADE action do?
Deletes the row with foreign key when the referenced primary key is deleted.
80
What does the ON UPDATE SET NULL action do?
Sets the foreign key to NULL when the referenced primary key is updated.
81
What is a composite foreign key?
A foreign key that consists of more than one column.
82
What happens if an insert or update would result in *a foreign key that does not match* an existing primary key in MySQL?
The operation is rejected automatically.
83
What SQL statement is used to insert a new row into an *existing table*?
``` INSERT INTO TableName (Column1, Column2, ...) VALUES (Value1, Value2, ...); ```
84
What SQL statement is used to update existing rows in a table?
``` UPDATE TableName SET Column1 = NewValue1, Column2 = NewValue2, ... WHERE Condition; ``` ## Footnote [📖](https://share.evernote.com/note/64474482-e9a6-6af7-c75a-2e2687c9eb33)
85
What SQL statement is used to delete rows from an existing table?
``` DELETE FROM TableName WHERE Condition; ```
86
What happens if a `NULL` value is inserted into a column with a `NOT NULL` constraint?
The database system will raise an error, and the insertion will fail.
87
What occurs if a duplicate value is inserted into a column with a `UNIQUE` constraint?
The insertion will be rejected, and an error will be raised.
88
What is the effect of violating a `CHECK` constraint during an insertion?
The database will raise an error, and the insertion will fail.
89
List the column constraints in SQL.
* NOT NULL * DEFAULT * P̶r̶i̶m̶a̶r̶y̶ K̶e̶y̶ * U̶n̶i̶q̶u̶e̶ ## Footnote [📖](https://app.milanote.com/1TG7Rm1JAOKG0G?p=N4oXWlErVzz)
90
List the table constraints in SQL.
* PRIMARY KEY * FOREIGN KEY * 𝗨𝗡𝗜𝗤𝗨𝗘 * 𝗖𝗛𝗘𝗔𝗞
91
``` CHANGE CurrentColumnName NewColumnName NewDataType [ConstraintDeclaration] ``` If this is adding an unnamed constraint like `NOT NULL` to an existing/non existing table?
The provided syntax is for modifying an existing table (using `ALTER TABLE`). ## Footnote [📖](https://share.evernote.com/note/b084bd5d-f49f-2fd6-cdc9-9fad8de04268)
92
What is the syntax to add a named PRIMARY KEY constraint?
ADD [CONSTRAINT ConstraintName] PRIMARY KEY (Column1, Column2 ...)
93
What is the syntax to drop a FOREIGN KEY constraint?
DROP FOREIGN KEY ConstraintName
94
What happens when trying to add a constraint to a table with existing data violating that constraint?
Adding a constraint fails when the table *contains data that violates* the constraint.
95
What must be done before dropping a table that has a foreign key *constraint referring to its primary key*?
Either the foreign key **constraint** or the foreign key **table** must be dropped.
96
What is the syntax to drop any named constraint?
`DROP CONSTRAINT` ConstraintName
97
Constraints are *added and dropped* with the `ALTER TABLE TableName` followed by an _______ clause.
[ADD, DROP, or CHANGE]

C175 Data Management Foundations (30 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions