MySQL Database Design, Queries, and Constraints: Key Concepts and Best Practices

0.0(0)
Studied by 0 people
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
GameKnowt Play
Card Sorting

1/122

encourage image

There's no tags or description

Looks like no tags are added yet.

123 Terms

1
New cards

How do you create a copy of an existing table in MySQL?

Use the CREATE TABLE AS statement with a SELECT statement.

2
New cards

What happens if you use CREATE TABLE AS with a name that already exists?

The existing table is not automatically copied over.

3
New cards

What is the requirement for string and date values in an INSERT statement?

They must be enclosed within quotes.

4
New cards

How do you insert multiple rows in a single INSERT statement?

Separate each list of values with a comma.

5
New cards

What is a subquery in SQL?

A SELECT statement coded within another SQL statement.

6
New cards

How do you use a subquery in an INSERT statement?

Use a SELECT statement instead of a VALUES clause, without parentheses around the SELECT.

7
New cards

When should you use parentheses around a subquery in an UPDATE or INSERT statement?

When the subquery is in the WHERE clause.

8
New cards

What is the purpose of Boyce-Codd Normal Form?

To eliminate transitive dependencies.

9
New cards

What is denormalization?

A deliberate deviation from normal forms, typically occurring beyond the third normal form.

10
New cards

What is a database view?

A SELECT statement stored in the database as a virtual table object.

11
New cards

What command is used to delete a view?

DROP VIEW.

12
New cards

What are the primary advantages of using views?

Hiding query complexity, limiting table exposure, allowing data updates, and restricting database access.

13
New cards

Why is it necessary to change the default delimiter in MySQL?

To allow a CREATE PROCEDURE statement to contain multiple SQL statements.

14
New cards

What statement executes the code within a stored procedure?

The CALL statement.

15
New cards

What is the difference between DROP DATABASE and DROP DATABASE IF EXISTS?

DROP DATABASE throws an error if the database is missing, while IF EXISTS prevents that error.

16
New cards

What is the difference between the NOT NULL and UNIQUE constraints?

NOT NULL prevents NULL values, while UNIQUE ensures distinct values but allows NULLs.

17
New cards

What is a composite key?

A primary key that spans multiple columns and cannot be defined at the column level.

18
New cards

What is the purpose of the FOREIGN KEY constraint?

To ensure referential integrity between tables.

19
New cards

What happens if you attempt to insert a foreign key value that does not exist in the parent table?

MySQL rejects the insert with Error Code 1452.

20
New cards

What is the difference between ON DELETE CASCADE and ON DELETE SET NULL?

CASCADE deletes related child rows, while SET NULL sets the foreign key in child rows to NULL.

21
New cards

What is the purpose of the AUTO_INCREMENT attribute?

To automatically generate unique integers for new rows in primary key columns.

22
New cards

What is the difference between CHAR and VARCHAR?

CHAR uses a fixed amount of storage for each value, while VARCHAR uses varying storage based on length.

23
New cards

What is the difference between scale and precision in a DECIMAL type?

Precision is the total number of digits, and scale is the number of digits to the right of the decimal point.

24
New cards

What is the purpose of an index in a database?

To speed up data retrieval by allowing quicker matching of rows.

25
New cards

What is the difference between CREATE INDEX and CREATE UNIQUE INDEX?

CREATE INDEX improves speed, while CREATE UNIQUE INDEX also enforces distinct values.

26
New cards

What is the requirement for updating rows in MySQL Workbench's Safe Update Mode?

The WHERE clause must refer to primary or foreign keys.

27
New cards

What are the three most widely used data type categories in MySQL?

Numeric, date and time, and character types.

28
New cards

What is the primary difference between CHAR and VARCHAR?

CHAR is for fixed-length strings, while VARCHAR is for variable-length strings.

29
New cards

How does storage differ between CHAR(10) and VARCHAR(10) when storing 'NE'?

CHAR(10) uses fixed space for 10 characters, while VARCHAR(10) uses space for the 2 characters plus 1 byte for length.

30
New cards

What is the maximum byte size for a character in the UTF8mb4 character set?

Up to 4 bytes.

31
New cards

What is the purpose of a primary key?

To uniquely identify each row in a table.

32
New cards

How are many-to-many relationships implemented in a database?

By creating a linking table that contains primary key columns from both related tables.

33
New cards

What is the primary benefit of database normalization?

It reduces data redundancy and related storage or maintenance issues.

34
New cards

What are the first three normal forms commonly applied in database design?

First, second, and third normal forms.

35
New cards

What is the function of an index in a database?

It allows for direct row location, preventing the need to search the entire table.

36
New cards

Which types of keys does MySQL index automatically?

Primary keys and foreign keys.

37
New cards

What criteria should be used when deciding to create an index on a column?

Columns that are frequently searched, have distinct values, are infrequently updated, or are used in joins.

38
New cards

Why is it not advisable to index every column in a table?

Because it can create unnecessary technical debt and overhead despite potential performance gains.

39
New cards

What is a composite index?

An index consisting of two or more columns.

40
New cards

What is the difference between TRUNCATE TABLE and DROP TABLE?

TRUNCATE deletes all rows but keeps the table structure; DROP removes the table entirely.

41
New cards

Why might a DROP TABLE command fail?

It fails if there are foreign key references to the table (Error Code 3730).

42
New cards

What is the purpose of the ALTER TABLE... MODIFY command?

To change a column's data type, length, or attributes without truncating existing data.

43
New cards

Why should telephone numbers be stored as character types rather than numeric?

Character types are preferred for values that do not require mathematical operations.

44
New cards

What attribute is used with integers to prevent negative values?

The UNSIGNED attribute.

45
New cards

What is the difference between scale and precision in decimal numbers?

Precision refers to the total number of digits, while scale refers to the number of digits after the decimal point.

46
New cards

What is the default format for a DATE type in MySQL?

yyyy-mm-dd.

47
New cards

What is the role of a composite key?

It establishes a unique primary identity using two or more columns.

48
New cards

What happens when a user attempts to insert an invalid foreign key value?

MySQL systematically blocks the attempt.

49
New cards

What is the standard practice for defining strings in SQL?

Using single quotes.

50
New cards

What is the consequence of deleting a parent row in a table with foreign key constraints?

It may require prior child row clean-up or depend on cascading behavior settings.

51
New cards

What are the six steps for designing a data structure?

1) Identify data elements, 2) Subdivide components, 3) Identify tables and columns, 4) Identify keys, 5) Review normalization, 6) Identify indexes.

52
New cards

What statement is used to create a copy of a table?

CREATE TABLE AS followed by a SELECT statement.

53
New cards

What does MySQL copy when using CREATE TABLE AS?

Column definitions and the data.

54
New cards

How can you create a partial copy of a table?

By using a search condition in the WHERE clause.

55
New cards

What happens if you use CREATE TABLE AS with a name that already exists?

The existing table is not automatically overwritten.

56
New cards

What clause is used with INSERT to specify values?

The VALUES clause.

57
New cards

What is the syntax rule for inserting multiple rows?

Separate each list of values with a comma.

58
New cards

What is a subquery?

A SELECT statement coded within another SQL statement.

59
New cards

How do you code a subquery in an INSERT statement?

Use a SELECT statement instead of a VALUES clause without parentheses.

60
New cards

When coding a subquery in a WHERE clause, are parentheses required?

Yes.

61
New cards

What is the Boyce-Codd normal form used to eliminate?

Transitive dependencies.

62
New cards

What is denormalization?

The deliberate deviation from normal forms, usually beyond the third normal form.

63
New cards

What is an EER model?

An enhanced entity-relationship model that represents database structure.

64
New cards

What is a database view?

A SELECT statement stored in the database as an object.

65
New cards

Do views store data themselves?

No, they refer back to the base tables.

66
New cards

How do you delete a view?

Use the DROP VIEW command.

67
New cards

What keywords allow you to automatically replace an existing view?

OR REPLACE.

68
New cards

What is the purpose of the DELIMITER statement in a stored procedure?

To change the default semicolon to another character to identify the end of the procedure.

69
New cards

What keywords define the code block within a procedure?

BEGIN and END.

70
New cards

What statement executes a stored procedure?

The CALL statement.

71
New cards

What is the purpose of IF NOT EXISTS in CREATE DATABASE?

It prevents an error if the database already exists.

72
New cards

What is the difference between DROP DATABASE and DROP DATABASE IF EXISTS?

DROP DATABASE throws an error if the database is missing, while IF EXISTS makes it safe to run.

73
New cards

What is a NOT NULL constraint?

A constraint that prevents a column from storing NULL values.

74
New cards

What is a UNIQUE constraint?

A constraint ensuring no two rows have the same value in that column.

75
New cards

Can a UNIQUE column store NULL values?

Yes, unless it is also marked NOT NULL.

76
New cards

What is the primary function of a UNIQUE constraint?

It ensures no two rows can have the same value in that column.

77
New cards

How does a UNIQUE constraint differ from a PRIMARY KEY regarding NULL values?

A UNIQUE column can store NULL unless marked NOT NULL, whereas a primary key cannot.

78
New cards

What is the difference between column-level and table-level constraints?

Column-level is defined inline after the column; table-level is defined separately after all columns and is required for multi-column constraints.

79
New cards

When is a table-level PRIMARY KEY constraint required?

When the primary key is a composite key spanning multiple columns.

80
New cards

Which SQL statements are used to add new rows and modify existing rows?

INSERT for new rows and UPDATE for modifying existing rows.

81
New cards

What are the roles of the UPDATE, SET, and WHERE clauses?

UPDATE names the table, SET defines columns and values, and WHERE specifies the condition for the rows to update.

82
New cards

What is the requirement for an UPDATE statement in MySQL Workbench's safe update mode?

The WHERE clause must refer to a primary or foreign key.

83
New cards

What is the recommended practice before executing an UPDATE statement?

Execute a SELECT statement with the same WHERE clause first to verify the correct rows are affected.

84
New cards

What happens if you disable safe update mode and omit the WHERE clause in an UPDATE statement?

All rows in the table will be updated.

85
New cards

What statement is used to remove rows from a table?

The DELETE statement.

86
New cards

How does CHAR storage differ from VARCHAR storage?

CHAR uses a fixed amount of storage for every value, while VARCHAR uses a varying amount based on string length.

87
New cards

Why are data types like telephone numbers and zip codes stored as character types instead of numeric?

They are identifiers rather than values used for mathematical calculations.

88
New cards

How do you prevent negative numbers from being stored in an integer column?

Include the UNSIGNED attribute.

89
New cards

What are fixed-point numbers?

Numbers that have a fixed number of digits to the right of the decimal point.

90
New cards

In a DECIMAL type, what is the difference between scale and precision?

Scale is the number of digits to the right of the decimal; precision is the total number of digits.

91
New cards

What is the default display format for the DATE type?

yyyy-mm-dd

92
New cards

In a data structure design, what do tables, columns, and rows represent?

Tables represent entities, columns represent attributes, and rows represent instances.

93
New cards

What is the purpose of a FOREIGN KEY constraint?

It enforces referential integrity by ensuring a column value matches an existing value in a parent table's primary key.

94
New cards

What error occurs if you insert a foreign key value that does not exist in the parent table?

Error Code 1452 (foreign key constraint violation).

95
New cards

What is the effect of the ON DELETE CASCADE action?

When a parent row is deleted, all related child rows are automatically deleted.

96
New cards

What is the effect of the ON DELETE SET NULL action?

When a parent row is deleted, the foreign key column in related child rows is set to NULL.

97
New cards

What is the purpose of the AUTO_INCREMENT attribute?

It automatically generates a unique numeric value for a column whenever a new row is inserted.

98
New cards

Can a subquery be used in the WHERE clause of an UPDATE or DELETE statement?

Yes, to identify the specific rows to be processed.

99
New cards

Which quote style is more typically used for strings in SQL?

Single quotes.

100
New cards

What is the purpose of an auto-incrementing integer in a database?

To automatically generate a unique ID for each new row, commonly used for primary key columns.