2. Relationship Databases Flashcards
1
Q
What is a database model?
A
A conceptual framework for database systems, consisting of data structures, operations, and rules governing valid data.
2
Q
Who published the relational model and when?
A
E. F. Codd of IBM published the relational model in 1970.
3
Q
What is the primary data structure of the relational model?
A
Table.
4
Q
Which query language is standardized for relational databases?
A
SQL.
5
Q
Name two types of databases that were dominant before relational databases.
A
- Hierarchical databases
- Network databases
6
Q
What are relational databases optimized for?
A
Transactional data.
7
Q
What is big data characterized by?
A
Unprecedented data volumes and rapidly changing data structures.
8
Q
List three strengths of relational databases.
A
- Productivity
- Simplicity
- Transactional applications
9
Q
What type of data structure does a hierarchical database use?
A
Tree.
10
Q
What type of data structure does a network database use?
A
Linked list.
11
Q
What is the difference between a set and a tuple?
A
A set is unordered, while a tuple is ordered.
12
Q
What are the components of a table in a relational database?
A
- Name
- Fixed tuple of columns
- Varying set of rows
13
Q
In relational databases, what is a column?
A
A named data type.
14
Q
What is an unnamed tuple of values in a table called?
A
Row.
15
Q
Fill in the blank: A _______ is a named set of values from which column values are drawn.
A
Data type.
16
Q
What are the two main types of operations in relational algebra?
A
- Unary operations
- Binary operations
17
Q
Name three relational operations.
A
- Select
- Project
- Join
18
Q
What does the SELECT operation do?
A
Selects a subset of rows from a table.
19
Q
What does the JOIN operation do?
A
Combines two tables by comparing related columns.
20
Q
True or False: The result of a relational operation is always a table.
A
True.
21
Q
What is a unique primary key in relational rules?
A
A column or group of columns in which values may not repeat.
22
Q
True or False: Business rules are always the same as relational rules.
A
False.
23
Q
What is the purpose of SQL?
A
To store, manipulate, and retrieve data.
24
Q
What organization published the SQL standard?
A
American National Standards Institute (ANSI) and International Organization for Standardization (ISO).
25
True or False: SQL is only used in relational databases.
False.
26
What is an SQL statement composed of?
One or more clauses.
27
What is the preferred way to write SQL clauses?
Each clause on a separate line.
28
Fill in the blank: SQL commands can __________ databases and tables.
Create.
29
True or False: All database systems use identical SQL statements.
False.
30
What is an SQL statement?
A complete command composed of one or more clauses.
31
What are the three main clauses in a SELECT statement?
* SELECT clause
* FROM clause
* WHERE clause
32
What is the purpose of the SELECT clause?
Starts the statement and specifies the columns to retrieve.
33
What is the function of the FROM clause?
Follows the SELECT clause and specifies the table from which to retrieve data.
34
What is the role of the WHERE clause in an SQL statement?
Optional clause that specifies conditions for data retrieval.
35
True or False: SQL keywords are case sensitive.
False
36
What do SQL literals represent?
Explicit values that are string, numeric, or binary.
37
How must string literals be formatted in SQL?
Surrounded by single quotes or double quotes.
38
What are SQL keywords?
Words with special meaning, such as SELECT, FROM, WHERE.
39
What are identifiers in SQL?
Objects from the database like tables and columns.
40
What are comments in SQL?
Statements intended only for humans and ignored by the database.
41
What are the five sublanguages of SQL?
* Data Definition Language (DDL)
* Data Query Language (DQL)
* Data Manipulation Language (DML)
* Data Control Language (DCL)
* Data Transaction Language (DTL)
42
What does DDL stand for and what does it do?
Data Definition Language; defines the structure of the database.
43
What is the purpose of DQL?
Data Query Language; retrieves data from the database.
44
What does DML do?
Data Manipulation Language; manipulates data stored in a database.
45
What is the function of DCL?
Data Control Language; controls database user access.
46
What does DTL manage?
Data Transaction Language; manages database transactions.
47
What does the CREATE DATABASE statement do?
Creates a new database.
48
What happens when the DROP DATABASE statement is executed?
Deletes a database, including all tables in the database.
49
What is the purpose of the SHOW statement in SQL?
Provides information about databases and their contents.
50
What does SHOW DATABASES do?
Lists databases available in the database system.
51
What must precede a SHOW TABLES statement?
A USE statement to select a database.
52
What defines a table in a relational database?
A table has a name, fixed sequence of columns, and varying set of rows.
53
What is a cell in a database table?
A single column of a single row.
54
True or False: A table must have at least one column.
True
55
What are the rules governing tables in a relational database?
* Exactly one value per cell
* No duplicate column names
* No duplicate rows
* No row order
56
What does data independence refer to?
Ability to change data organization on storage devices without affecting query results.
57
What are the three columns in the Employee table?
ID, Name, Salary
58
What rule states that a table cannot have multiple values in the same cell?
A table cannot have multiple values in the same cell.
59
True or False: A table cannot have multiple columns with the same name.
True
60
What does the principle of data independence state?
The performance of a query is not related to the physical organization of data.
61
How can driver's license information be added to the Employee table?
By creating another column with a new name, such as ID2 or DriverLicense.
62
What SQL statement is used to create a new table?
CREATE TABLE
63
What SQL statement deletes a table along with all its rows?
DROP TABLE
64
Fill in the blank: The data type for integer values is _______.
INT or INTEGER
65
What is the syntax to create a table named Employee?
CREATE TABLE Employee (ID INT, Name VARCHAR(60), BirthDate DATE, Salary DECIMAL(7,2));
66
What is the purpose of the ALTER TABLE statement?
To add, delete, or modify columns on an existing table.
67
True or False: The Employee table is created with 4 columns.
False
68
What data type does the Salary column in the Employee table hold?
DECIMAL(7,2)
69
What does the ALTER TABLE statement do when using CHANGE?
Modifies a column
70
Fill in the blank: The _____ data type stores data as an exact copy of computer memory.
BLOB
71
What are common character data types?
* CHAR
* VARCHAR
72
What is an example of a spatial data type?
POLYGON, POINT, GEOMETRY
73
True or False: The CHAR data type represents a variable string of characters.
False
74
What is the storage requirement for the INT data type?
4 bytes
75
What is the signed range for the SMALLINT data type?
-32,768 to 32,767
76
What is the format for the DATE data type?
YYYY-MM-DD
77
Fill in the blank: The decimal data type DECIMAL(M,D) allows for _______ significant digits.
M
78
What SQL command is used to delete a column from a table?
DROP
79
What is the storage requirement for the DOUBLE data type?
8 bytes
80
Fill in the blank: The data type that represents numbers with fractional values is _______.
Decimal
81
What is the unsigned range for the BIGINT data type?
0 to 264 -1
82
True or False: Most databases allow integer and decimal numbers to be signed or unsigned.
True
83
What is the storage size of a DOUBLE data type?
8 bytes
## Footnote
Approximate decimal numbers with range: -1.8E+308 to 1.8E+308
84
What is the format for the DATE data type?
YYYY-MM-DD
## Footnote
Range: '1000-01-01' to '9999-12-31'
85
What is the storage size for the TIME data type?
3 bytes
## Footnote
Format: hh:mm:ss
86
What is the format for the DATETIME data type?
YYYY-MM-DD hh:mm:ss
## Footnote
Range: '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
87
Define CHAR(N) in MySQL.
Fixed-length string of length N; 0 ≤ N ≤ 255
88
What is the maximum length for a VARCHAR(N) data type?
65,535 characters
## Footnote
Length of characters + 1 bytes
89
What is the maximum length for a TEXT data type?
65,535 characters
## Footnote
Length of characters + 2 bytes
90
Which data type is best for storing a city's population ranging from a dozen to 24 million?
unsigned MEDIUMINT
91
For storing annual gain or loss in a city's population, which data type is appropriate?
signed MEDIUMINT
92
What data type should be used for storing the price of an item ranging from a few dollars to a few hundred dollars?
DECIMAL(5,2)
93
What data type is suitable for storing the date and time an item is purchased?
DATETIME
94
What data type is appropriate for storing a student's assigned letter grade, like A or D?
CHAR(1)
95
Which data type should be used for storing a student's email address?
VARCHAR(100)
96
What data type is appropriate for storing a yes or no value?
TINYINT
97
What does NULL represent in MySQL?
Unknown or inapplicable data
98
Is NULL the same as zero for numeric data types?
False
99
What does a NULL value in the BirthDate column signify?
Unknown
100
What does Lisa Ellison's NULL bonus indicate?
Inapplicable
101
What is the purpose of the NOT NULL constraint?
Prevents a column from having NULL values
102
In the CREATE TABLE statement, where is NOT NULL placed?
Follows the column name and data type
103
What happens when a user attempts to insert a new department without a Code value?
The database rejects the insert.
104
When do arithmetic or comparison operators return NULL?
When one or more operands are NULL
105
What is the result of using a WHERE clause that evaluates to NULL?
The row is not selected.
106
How can NULL values be selected in SQL?
Using IS NULL or IS NOT NULL operators
107
What does the statement SELECT * FROM Country WHERE IndepYear IS NULL return?
Rows where the country has not achieved independence
108
How many rows are returned by SELECT * FROM Country WHERE Population = NULL?
0
109
What is missing in the statement SELECT * FROM Country WHERE IndepYear ______ to select all rows except Aruba?
IS NOT NULL
110
How many rows are returned when selecting from Country where Population is NULL?
0, 1, or 3
111
What is missing to select all rows except Aruba?
IndepYear IS NOT NULL
112
What does the SELECT statement do in the context of NULL values?
Selects songs that have a NULL Artist value
113
What songs are selected by the query with Salary > 30000 OR Bonus > 1000?
Lisa Ellison, Sam Snead, and Maria Rodriguez
114
What songs are selected by the query with Salary > 30000 AND Bonus > 1000?
Sam Snead and Maria Rodriguez
115
What is the result of NOT(Salary > 30000 AND Bonus > 1000)?
Lisa Ellison
116
Which songs have a NULL Title or NULL Artist?
Songs with IDs 100, 300, 500, and 600
117
Which columns can contain NULL values in the Country table?
IndepDate, ISOCode2, Code
118
What does the INSERT statement do?
Adds rows to a table
119
What is the syntax for the INSERT statement?
INSERT [INTO] TableName (Column1, Column2, ...) VALUES (Value1, Value2, ...);
120
What happens if a column is omitted from an INSERT statement?
Assigned a NULL value unless NOT NULL constraint is specified
121
What is the default value for the BirthDate column in the Employee table?
'2000-01-01'
122
What happens when a column with a DEFAULT constraint is omitted from an INSERT?
Assigned the default value
123
What is the purpose of the UPDATE statement?
Modifies existing rows in a table
124
What is the syntax for the UPDATE statement?
UPDATE TableName SET Column1 = Value1, Column2 = Value2, ... WHERE condition;
125
What happens if the WHERE clause is omitted in an UPDATE statement?
All rows are updated
126
Which statement correctly inserts Engineering into the Department table?
INSERT INTO Department (Code, Name, ManagerID) VALUES (44, 'Engineering', 2538);
127
What columns are NULL after executing INSERT INTO Department (Code) VALUES (924);?
Name and ManagerID
128
What is the result of the UPDATE statement SET Name = 'Custodial' WHERE Code = 82?
Changes 'Sales' to 'Custodial'
129
What departments are changed by UPDATE Department SET Name = 'Administration' WHERE ManagerID IS NOT NULL?
All departments except Technical Support
130
What SQL command is used to change the name of a department from 'Sales' to 'Custodial'?
UPDATE Department SET Name = 'Custodial' WHERE Code = 82
131
What departments are affected by the command that sets Name to 'Administration' where ManagerID is NOT NULL?
All departments except Technical Support
132
What is needed to change Marketing's Code to 55 and Name to Administration?
WHERE ManagerID = 6381
133
What managers are changed with the command 'UPDATE Department SET ManagerID = 2538'?
Only the Engineering manager
134
What does the DELETE statement do?
Deletes existing rows in a table
135
What is the syntax for the DELETE statement?
DELETE FROM TableName WHERE condition
136
What does the command 'DELETE FROM Employee WHERE ID = 6381' do?
Deletes the row with ID 6381 from the Employee table
137
What rows are deleted by 'DELETE FROM Employee WHERE Salary > 40000 AND Salary < 80000'?
Rows with salaries 45000 and 72300
138
What does the command 'DELETE FROM Department WHERE ManagerID = 6381' delete?
Sales and Marketing
139
What is the result of 'DELETE FROM Department'?
All departments are deleted
140
What is missing to delete only the Sales department?
WHERE Code = 82
141
What is the purpose of the TRUNCATE statement?
Deletes all rows from a table
142
What is the difference between TRUNCATE and DELETE?
TRUNCATE is nearly identical to DELETE without WHERE clause but has minor differences
143
What is a primary key?
A column or group of columns used to identify a row
144
What are the requirements for a primary key?
* Unique
* Not NULL
145
What is a composite primary key?
A primary key consisting of multiple columns
146
What must composite primary keys be?
* Unique
* Not NULL
* Minimal
147
Can Name be a primary key in the Employee table?
False
148
Can a new employee be added without an ID value?
False
149
Can a new employee be added with ID 5384?
False
150
What is the PRIMARY KEY constraint?
Ensures a column or group of columns is always unique and non-null
151
What defines an auto-increment column?
Assigned an automatically incrementing value when a new row is inserted
152
What keyword defines an auto-increment column in SQL?
AUTO_INCREMENT
153
What common error occurs with auto-increment primary keys?
Inserting values for auto-increment primary keys
154
What SQL command is used to create a table with a primary key?
CREATE TABLE TableName (column definitions, PRIMARY KEY (column))
155
What is the primary key in the Employee table?
ID
156
What type of data is stored in the Salary column?
DECIMAL(7,2)
157
What error occurs when inserting values for auto-increment primary keys?
Inserting specific values for auto-increment primary keys is usually a mistake.
158
What happens if you omit values for primary keys that are not auto-increment columns?
It results in an error.
159
True or False: MySQL allows insertion of a specific value to an auto-increment column.
True
160
Fill in the blank: If ID is an _______ column, the ID should not be listed in the INSERT statement.
auto-increment
161
What must be specified if ID is not an auto-increment column?
An ID value must be specified.
162
What is the result of trying to insert a duplicate primary key value?
A unique ID must be chosen; the operation fails.
163
What happens when the INSERT statement uses an ID that already exists in Employee?
It results in an error due to duplicate primary key values.
164
What is the correct format for inserting values into the Employee table without specifying the ID?
INSERT INTO Employee (Name, Salary) VALUES ('Maria Rodriguez', 92300);
165
What is the label for the Salary column in the Employee table?
NOT NULL
166
True or False: Inserting values for a non-auto-increment ID requires specifying the ID in the INSERT statement.
True
167
What should you do if you want to insert a new employee without specifying the ID?
Omit the ID from the INSERT statement.
168
What will happen if you attempt to insert a row into Employee without specifying a non-auto-increment ID?
It will result in an error.
169
What are the three columns in the Employee table?
* ID
* Name
* Salary
170
What is the significance of the primary key in a database table?
It uniquely identifies each record in the table.
171
Fill in the blank: The ID column is labeled as _______ in the Employee table.
auto-increment
172
What SQL statement creates a Movie table?
CREATE TABLE Movie (ID INT AUTO_INCREMENT PRIMARY KEY, Title VARCHAR(255), Rating VARCHAR(10), ReleaseDate DATE)
173
What is the format for the ReleaseDate in the INSERT statement?
'YYYY-MM-DD'
174
Which movies are inserted into the Movie table?
* Raiders of the Lost Ark
* The Godfather
* The Pursuit of Happyness
175
What are the correct INSERT statements for the Engineering department?
INSERT INTO Department (Name, ManagerID) VALUES ('Engineering', 2538);
176
Which statement correctly inserts an unnamed department with no manager?
INSERT INTO Department (Name, ManagerID) VALUES ('', NULL);
177
What are the columns in the Country table?
* Code
* CountryName
* IndependenceYear
* Population
178
What does the SELECT statement return when querying CountryName for Code 558?
Nicaragua
179
Define an operator in SQL.
An operator is a symbol that computes a value from one or more operands.
180
What are the types of operators in SQL?
* Arithmetic
* Comparison
* Logical
181
What does the arithmetic operator + do?
Adds two numeric values
182
What does the comparison operator = do?
Compares two values for equality
183
What does the logical operator AND return?
TRUE only when both values are TRUE
184
What is an expression in SQL?
A string of operators, operands, and parentheses that evaluates to a single value.
185
What is the precedence of the unary operator -?
1
186
What is the highest precedence operator?
- (unary)
187
What does the WHERE clause do in a SELECT statement?
Specifies a condition for selecting rows.
188
What does the LIMIT clause do in a SELECT statement?
Limits the number of rows returned.
189
What is the result of the SELECT * FROM City statement?
Returns all rows and columns from the City table.
190
What is the result of the expression 7 + 3 * 2?
13
191
What is the result of the expression (7 + 3) * 2?
20
192
What does the expression (Age >= 13 AND Age <= 18) evaluate to when Age is 8?
FALSE
193
How do you select only the Name and District columns from the City table?
SELECT Name, District FROM City;
194
What is the result of SELECT Balance + Payment FROM Customer?
Returns the sum of Balance and Payment for each customer.
195
What does the expression SELECT * FROM CountryLanguage WHERE Percentage < 5.0 OR Percentage > 90.0 do?
Selects rows where Percentage is either less than 5.0 or greater than 90.0.
196
What is returned by the query SELECT CountryCode, Language FROM CountryLanguage WHERE Percentage > 0.0 AND Percentage < 10.0?
Rows with Percentage between 0.0 and 10.0.
197
What are the two columns in the Result table?
Country Code and Language
198
What does the SQL statement SELECT * FROM City WHERE Population < 150000 return?
Valencia
199
What districts are selected with the query SELECT District FROM City WHERE Name = 'Rio de Janeiro'?
Rio de Janeiro
200
What cities are selected with the query SELECT Name FROM City WHERE CountryCode != 'CHL'?
All cities except Santiago de Chile
201
What names are selected with the query SELECT Name FROM City WHERE NOT ID < 2000?
All names
202
What cities are selected with the query SELECT * FROM City WHERE ID <= 1000 AND ID >= 600?
Barcelona and Valencia
203
What cities are selected with the query SELECT * FROM City WHERE ID < 300 OR Population > 7500000?
Delhi and Rio de Janeiro
204
What cities are selected with the query SELECT * FROM City WHERE (Population >= 7000000 AND Population <= 8000000) OR ID < 500?
Rio de Janeiro, Manchester, and Delhi
205
What is a foreign key?
A column, or group of columns, that refer to a primary key
206
Can foreign key values be repeated?
Yes
207
True or False: Foreign keys must obey the same rules as primary keys.
False
208
What does referential integrity require?
Foreign key values must either be NULL or match a value of the referenced primary key
209
What is the result of replacing NULL in the Manager column with 5384?
Assigns Sam Snead as the manager of the Technical Support department
210
What must the data type of a foreign key and primary key be?
The same
211
True or False: Values in a foreign key must be unique.
False
212
What happens if a foreign key constraint is violated?
The database rejects insert, update, and delete statements
213
What is a composite foreign key?
A foreign key that refers to a composite primary key
214
What is required for a foreign key that refers to a composite primary key?
It must also be composite
215
Fill in the blank: A foreign key constraint is added to a CREATE TABLE statement with the _______ and REFERENCES keywords.
FOREIGN KEY
216
True or False: The FOREIGN KEY constraint must follow all column declarations in a CREATE TABLE statement.
True
217
In a FOREIGN KEY constraint, are parentheses required around the foreign key column name?
Yes
218
What happens when rows are added to the Department table with invalid ManagerID values?
They are rejected
219
Referential integrity violations must be corrected before data is stored in the database. True or False?
True
220
What is referential integrity?
A database concept ensuring that relationships between tables remain consistent.
221
What are the two tables mentioned in the content?
Department and Employee.
222
What are the columns in the Department table?
Code, Name, Manager.
223
What are the columns in the Employee table?
ID, Name, Salary.
224
What does the Manager column in the Department table represent?
A foreign key referencing the primary key ID in the Employee table.
225
What is the primary key of the Department table?
Code.
226
What is the primary key of the Employee table?
ID.
227
What is a composite foreign key?
A foreign key that consists of two or more columns.
228
What can violate referential integrity?
* A primary key is updated.
* A foreign key is updated.
* A row containing a primary key is deleted.
* A row containing a foreign key is inserted.
229
True or False: Primary key inserts can violate referential integrity.
False.
230
What happens when a primary key is deleted if CASCADE is set?
Rows containing matching foreign keys are also deleted.
231
What is the action of RESTRICT when a foreign key violates referential integrity?
Rejects an insert, update, or delete that violates referential integrity.
232
What does SET NULL do when a foreign key violates referential integrity?
Sets invalid foreign keys to NULL.
233
What does SET DEFAULT do when a foreign key violates referential integrity?
Sets invalid foreign keys to the foreign key default value.
234
What is the effect of ON DELETE CASCADE in a foreign key constraint?
Deletes rows in the child table when the corresponding row in the parent table is deleted.
235
What is the effect of ON UPDATE SET NULL in a foreign key constraint?
Sets the foreign key to NULL when the referenced primary key is updated.
236
Fill in the blank: A foreign key is a field in one table that uniquely identifies a row of another table. It is defined by the _______.
[primary key].
237
What does the NULL value in the Manager column indicate?
No manager is assigned to the department.
238
In the HealthPlan table, which foreign key value violates referential integrity?
(6381, 4) only.
239
In the Family table, what does the Relationship column represent?
The relationship of the dependent to the employee.
240
What happens if a foreign key is updated to a value that does not exist in the primary key?
Violates referential integrity.
241
What is the SQL command to create a foreign key constraint with ON DELETE and ON UPDATE actions?
FOREIGN KEY (column_name) REFERENCES other_table(column_name) ON DELETE action ON UPDATE action.
242
What happens when an employee's ID is updated if CASCADE is set?
Matching foreign keys in related tables are updated to the same value.
243
What does it mean if an operation is rejected due to RESTRICT?
The operation would violate referential integrity.
244
What does the composite primary key in the Family table consist of?
ID and Number.
245
True or False: An insert of a foreign key can be allowed if it does not match a primary key value.
False.
246
What happens when a foreign key is set to NULL?
It indicates that the relationship to the primary key is no longer valid.
247
What SQL action is performed when a foreign key is set to a default value?
SET DEFAULT.
248
What is the purpose of defining ON UPDATE and ON DELETE clauses in SQL?
To specify actions taken on foreign keys when the referenced primary key is updated or deleted.
249
What happens when Lisa Ellison is deleted?
Lisa Ellison is deleted.
250
What is the result of updating Lisa Ellison's ID to 1000?
The update is rejected.
251
What happens when the Engineering ManagerID is updated to 9999?
The update is rejected.
252
What is the outcome of deleting the Engineering department?
The delete is rejected.
253
What is a constraint in a database?
A rule that governs allowable values in a database.
254
What are the types of constraints mentioned?
* NOT NULL
* DEFAULT
* PRIMARY KEY
* FOREIGN KEY
255
What defines a column constraint?
It appears after the column name and data type in a CREATE TABLE statement.
256
What is a table constraint?
It appears in a separate clause of a CREATE TABLE statement and governs values in one or more columns.
257
What does the DEFAULT constraint do?
Specifies a value that is inserted when a column is omitted from an INSERT statement.
258
True or False: The DEFAULT constraint limits allowable values in a column.
False.
259
What does the UNIQUE constraint ensure?
Values in a column, or group of columns, are unique.
260
How does MySQL handle UNIQUE constraints?
MySQL creates an index for each UNIQUE constraint.
261
What does the CHECK constraint specify?
An expression on one or more columns of a table.
262
What happens if the CHECK constraint expression is FALSE?
The constraint is violated.
263
What is required for a CHECK constraint on multiple columns?
It must be a separate clause.
264
What is the syntax to name a constraint?
CONSTRAINT ConstraintName followed by the declaration.
265
How to add a named constraint?
Using the ADD clause.
266
What happens when you try to add a constraint that violates existing data?
The addition fails.
267
What is the effect of dropping a table with foreign key constraints?
The drop fails unless the foreign key constraint is removed.
268
Fill in the blank: A __________ constraint requires each combination of Name and Extension values to be different.
UNIQUE
269
What is the purpose of the PRIMARY KEY constraint?
To uniquely identify each row in a table.
270
What does the FOREIGN KEY constraint do?
Establishes a relationship between two tables.
271
What type of constraint is NOT NULL?
Column constraint only.
272
What type of constraint is PRIMARY KEY?
Either column or table constraint.
273
What happens to rows that violate UNIQUE constraints on insertion?
The insertion fails.
274
How can you check existing constraint names in MySQL?
Using the SELECT statement on Information_Schema.Key_Column_Usage.
275
What is a potential violation of the CHECK constraint?
When the expression evaluates to FALSE.
276
Fill in the blank: The __________ constraint ensures all rows have a HireDate between specified dates.
CHECK
277
What is the significance of naming constraints?
Constraint names appear in error messages when violated.
278
What is the syntax to define a CHECK constraint at the column level?
CHECK (expression) in the column declaration.
279
What happens when a constraint fails in a table?
Adding a constraint fails when the table contains data that violates the constraint.
280
How are named constraints dropped?
Named constraints are dropped with a DROP clause.
281
What command is used to drop a PRIMARY KEY?
DROP PRIMARY KEY
282
What command is used to drop a FOREIGN KEY?
DROP FOREIGN KEY ConstraintName
283
What command is used to drop a UNIQUE constraint?
DROP INDEX ConstraintName
284
What command is used to drop a CHECK constraint?
DROP CHECK ConstraintName
285
What command is used to drop any named constraint?
DROP CONSTRAINT ConstraintName
286
What happens when trying to drop a table with a foreign key constraint?
Dropping a table fails when a foreign key constraint refers to the table's primary key.
287
What must be done before dropping a table that has a foreign key constraint?
Either the foreign key constraint or the foreign key table must be dropped.
288
What is the purpose of the CONSTRAINT keyword?
The CONSTRAINT keyword gives the CHECK and UNIQUE constraints user-defined names.
289
Why are constraint names used in error messages?
The database uses constraint names in error messages.
290
What happens when a constraint check is violated?
An error message is generated indicating the violation.
291
What does the ALTER TABLE statement do regarding constraints?
The ALTER TABLE statement drops or adds constraints.
292
What is the effect of dropping the HireCheck constraint?
Any HireDate may now be added to Employee.
293
What does the new constraint added to the Employee table ensure?
Ensures the HireDate is before Feb 14, 2000.
294
Fill in the blank: To add a NOT NULL constraint to a column, use _______.
ALTER TABLE Department Salary INT NOT NULL
295
What is the command to add a constraint called MgrIDCheck?
ALTER TABLE Department (ManagerID >= 2000)
296
What command is used to drop the UNIQUE constraint called UniqueNameMgr?
ALTER TABLE Department
297
What is the structure of the Country table?
CREATE TABLE Country ( ContinentCode CHAR(2), Code SMALLINT, Name VARCHAR(15), ISOCode3 CHAR(3) UNIQUE, PRIMARY KEY (ContinentCode) );
298
True or False: A UNIQUE constraint allows duplicate values in its column.
False
299
What information is needed to determine if rows violate UNIQUE constraints?
Indicate whether each pair of rows violate the Country table's UNIQUE constraints.
