Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

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 TableName
SET Column1 = Value1, Column2 = Value2, ...
WHERE condition;
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, Number)
);
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
Q

What must match between foreign keys and primary keys?

A

The data types must be the same.

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

Can foreign key values be different from primary key values in terms of naming

A

Yes, foreign key values can have different names from primary key values, as they are independent identifiers used to establish relationships.

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

What are the possible values for foreign keys?

A
  • Repeated
  • NULL
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

What does referential integrity ensure?

A

Foreign key values must either be NULL or match a value of the primary key from the referenced table.

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

In the Department and Employee example, which column in the Department table is a foreign key?

A

Manager.

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

What does a self-referencing foreign key do?

A

It points to its own primary key.

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

Fill in the blank: Foreign keys can be ______, meaning they consist of multiple columns.

A

composite.

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

How are foreign key constraints specified?

A

Within CREATE TABLE statements.

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

What happens if an operation leaves a foreign key without a corresponding primary key?

A

The database system rejects the operation.

34
Q
  1. Different foreign keys in a table refer to the same primary key in another table.
  2. What is this an example of?
A

This is an example of a scenario involving multiple foreign keys

35
Q

What is a key characteristic of composite foreign keys?

A

They must match a composite primary key in another table.

36
Q

How can Foreign key values be NULL.

A
  1. A foreign key establishes a link between two tables based on a relationship between the data in the tables.
  2. If the foreign key is NULL, it simply means that no relationship has been established for that particular record.
37
Q

What is the referential integrity violation?

A
  1. 4407 does not match any value in ID 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.
38
Q

What is the referential integrity violation?

A
  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
Q

What is the referential integrity violation?

A
  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
Q

What is the referential integrity violation?

A

Updating the Employee primary key to 8888 violates referential integrity because the foreign key 2538 no longer exists in Employee.

41
Q

What is the referential integrity violation?

A

Updating the foreign key to 3333 violates referential integrity because 3333 does not match a primary key value

42
Q

What is the referential integrity violation?

A

Deleting Employee primary key 6381 violates referential integrity because the foreign key 6381 no longer exists in Employee.

43
Q

What is the referential integrity violation?

A

Inserting foreign key 0202 violates referential integrity because 0202 does not match a primary key value.

44
Q

What is the the syntax for a CREATE TABLE statement with a FOREIGN KEY constraint

A 
CREATE TABLE TableName (
    column1 datatype,
    column2 datatype,
    ...
    PRIMARY KEY (column),
    FOREIGN KEY (foreign_key_column)
      REFERENCES   parent_table(parent_table_column)
);
45
Q

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) );

A

The Department table is created with a FOREIGN KEY constraint that REFERENCES the Employee ID column.

46
Q

What is the order of FOREIGN KEY constraint and column declarations

A

The order is not significant however, column declarations normally appear first, followed by the PRIMARY KEY constraint and the FOREIGN KEY constraint.

47
Q

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)
);
A

Parentheses are required around both the foreign key and primary key column names.

48
Q

How does adding a FOREIGN KEY constraint to a table effect rows and columns?

A
  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
Q

In the Department table, first three columns; which foreign key value violates referential integrity?

A
  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
Q

Why do both (6381, 4) & (6381, NULL) foreign key values violate referential integrity?

A
  • (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
Q

Is it true that if a column is partially NULL that this should violate referential integrity?

A

Manager is a single-column, or simple, foreign key. A NULL in a simple foreign key does not violate referential integrity.

52
Q

The row containing primary key 2538 is deleted.

How would RESTRICT access this situation?

A

RESTRICT rejects the delete, since employee 2538 manages Engineering.

Prevents the deletion or update of a parent record

53
Q

The row containing primary key 2538 is deleted.

How would SET NULL access this situation?

A

SET NULL sets matching foreign keys to NULL.

allows child records to exist but removes their link to the deleted parent

54
Q

The row containing primary key 2538 is deleted.

How would SET DEFAULT access this situation?

A

SET DEFAULT sets matching foreign keys to the foreign key default value, 6381.

55
Q

The row containing primary key 2538 is deleted.

How would CASCADE access this situation?

A

CASCADE deletes all rows with matching foreign key values.

ensures all dependent records are cleaned up

56
Q

What is the syntax for referntial integrity actions such as

RESTRICT, SET NULL,  SET DEFAULT, CASCADE

Referential integrity actions on primary key DELETE.

A 
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
Q

Refer to the Employee and Department tables.

  • What are the results of the actions below?
  • RESTRICT, when the row containing Maria Rodriguez is deleted.
A
  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
Q

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.
A
  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
Q

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.
A
  • 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
Q

Refer to the Employee and Department tables.

  • What are the results of the actions below?
  • CASCADE, when Maria Rodriguez’ ID is changed to 2022.
A
  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
Q

Refer to the Employee and Department tables.

  • What are the results of the actions below?
  • CASCADE, when Maria Rodriguez is deleted.
A
  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
Q

What is the syntax for creating foreign key constraints with the ON UPDATE and ON DELETE clauses in SQL

A 
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
Q

Foreign key constraints with ON UPDATE and ON DELETE clauses.

Explain how DELETE CASCADE creates this result

A

ON DELETE CASCADE causes the database to delete the row with ManagerID 7343 when the employee with ID 7343 is deleted.

64
Q

Foreign key constraints with ON UPDATE and ON DELETE clauses.

Explain how UPDATE SET NULL creates this result

A

ON UPDATE SET NULL causes the database to set ManagerID 2538 to NULL when the Employee ID 2538 is changed to 8754.

65
Q

What is referential integrity?

A

A relational database requires that foreign key values must either be fully NULL or match a primary key value.

66
Q

What does the CASCADE action do in referential integrity?

A

Deletes or updates the corresponding child records automatically when a parent record is deleted or updated.

67
Q

What happens when SET NULL is applied in referential integrity?

A

Sets the foreign key value in the child records to NULL when the parent record is deleted or updated.

68
Q

What is the effect of the SET DEFAULT action in referential integrity?

A

Sets the foreign key value in the child records to its default value when the parent record is deleted or updated.

69
Q

What does the RESTRICT action do in referential integrity?

A

Prevents the deletion or update of a parent record if there are any corresponding child records.

70
Q

What are common causes of referential integrity violations?

A

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
Q

How can referential integrity violations be identified?

A

Check foreign key values in tables to find violations and NULL values.

72
Q

What does a fully NULL foreign key consist of?

A

All columns being NULL.

73
Q

True or False: A NULL in a simple foreign key violates referential integrity.

A

False.

74
Q

What happens when a primary key is updated to a value that doesn’t exist?

A

It violates referential integrity.

75
Q

What is the default action in MySQL if no specific action is defined in the ON UPDATE or ON DELETE clause?

A

RESTRICT.

76
Q

What limitation does SET NULL have in MySQL?

A

It is not allowed if the foreign key column is defined as NOT NULL.

77
Q

What is the role of foreign keys in maintaining referential integrity?

A

Foreign keys maintain referential integrity between tables by ensuring a match with primary keys or being NULL.

78
Q

The _______ action sets the foreign key to a predefined default value if the referenced primary key is deleted or updated.

A

SET DEFAULT

79
Q

What does the ON DELETE CASCADE action do?

A

Deletes the row with foreign key when the referenced primary key is deleted.

80
Q

What does the ON UPDATE SET NULL action do?

A

Sets the foreign key to NULL when the referenced primary key is updated.

81
Q

What is a composite foreign key?

A

A foreign key that consists of more than one column.

82
Q

What happens if an insert or update would result in a foreign key that does not match an existing primary key in MySQL?

A

The operation is rejected automatically.

C175 Data Management Foundations (19 decks)

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