Test Your MySQL ERD (Data Model) Knowledge with Our SQL Quiz

Progress

True / False

In MySQL, an ERD (Entity-Relationship Diagram) is used to visually represent the relationships between different tables in a database.

True False

An ERD helps to understand the structure of a MySQL database by illustrating tables and the relationships between them.

True / False

In MySQL, an ERD can be used to generate SQL statements to create database tables and relationships.

True False

Many database design tools allow you to generate SQL scripts from an ERD to create tables and relationships in MySQL.

True / False

In MySQL, an ERD cannot include attributes that are not directly stored in the database tables.

True False

ERDs can include derived attributes that are calculated from other attributes, though they are not directly stored in MySQL tables.

True / False

In MySQL, a foreign key constraint in an ERD ensures that the values in a column or group of columns match the values in a primary key column in another table.

True False

Foreign key constraints enforce referential integrity between tables in MySQL.

True / False

In MySQL, a primary key in an ERD can have multiple columns.

In MySQL, a primary key in an ERD can have multiple columns. False

A primary key in MySQL can be a composite key, which consists of multiple columns.

True / False

In MySQL, a unique key constraint in an ERD indicates that the values in the constrained columns must be distinct across all rows in the table.

True False

Unique key constraints ensure that all values in the specified columns are unique in MySQL.

True / False

In MySQL, an ERD can be directly modified using DML (Data Manipulation Language) statements.

True False

ERDs are not directly modified using DML statements; they are conceptual models. DML is used to manipulate data in the database tables, not the ERD itself.

True / False

In MySQL, a one-to-many relationship in an ERD is implemented using a join table.

True False

A one-to-many relationship in MySQL is typically implemented using a foreign key in the table on the "many" side that references the primary key on the "one" side. Join tables are used for many-to-many relationships.

True / False

In MySQL, an ERD can include supertype and subtype relationships to represent inheritance in database tables.

True False

ERDs can model supertype and subtype relationships, where a supertype can have one or more subtypes, and this can be implemented in MySQL using separate tables for the supertype and subtypes.

Q10

True / False

In MySQL, an ERD must always represent all possible relationships and constraints present in the database schema.

True False

While an ERD aims to represent the database schema accurately, it may not always capture every detail or constraint, especially in complex schemas or during the early stages of design.

Q11

Single Choice

Which of the following is a primary purpose of an Entity-Relationship Diagram (ERD) in MySQL?

To visualize data processing algorithms To map out the structure of a database To create user interfacesTo manage user permissions

ERDs are used to visually represent the structure of a database, including entities, attributes, and the relationships between entities.

Q12

Single Choice

In an ERD, what does a rectangle symbolize?

An attribute relationship An entityprimary key

In ERDs, a rectangle is used to represent an entity, which is a thing or object in the real world with an independent existence that can be identified in the database.

Q13

Single Choice

What is the significance of a foreign key in a MySQL ERD?

It uniquely identifies a row in the same table It is used to link tables together It serves as a primary keyIt represents a user-defined function

A foreign key in a MySQL ERD is used to establish a link between two tables, ensuring referential integrity.

Q14

Single Choice

In MySQL, what does a diamond shape in an ERD represent?

An entity relationship An attributeprimary key

In ERDs, a diamond shape represents a relationship between entities.

Q15

Single Choice

Which type of relationship in an ERD indicates that one entity can be associated with multiple instances of another entity in MySQL?

One-to-One One-to-Many Many-to-ManySelf-Referencing

In a one-to-many relationship, a single instance of one entity is associated with multiple instances of another entity.

Q16

Single Choice

What is a composite key in the context of a MySQL ERD?

single attribute key foreign key primary key composed of multiple attributesunique key

composite key consists of two or more attributes that together uniquely identify an entity instance.

Q17

Single Choice

In a MySQL ERD, which notation is typically used to represent cardinality constraints?

Crow’s Foot Notation Unified Modeling Language (UML) Chen NotationObject-Role Modeling (ORM)

Crow’s Foot Notation is widely used in ERDs to depict cardinality constraints, showing the relationships between entities.

Q18

Single Choice

Which of the following best describes a weak entity in a MySQL ERD?

An entity without a primary key An entity that depends on another entity for its identification An entity that has no attributesAn entity that exists independently

weak entity cannot be uniquely identified by its attributes alone and relies on a foreign key relationship with another entity.

Q19

Single Choice

In MySQL, what is the purpose of a bridge table in an ERD?

To link two entities with a one-to-many relationship To resolve a many-to-many relationship between two entities To define primary keys for entitiesTo represent recursive relationships

bridge table (or junction table) is used to handle many-to-many relationships by breaking them down into two one-to-many relationships.

Q20

Single Choice

Which MySQL feature can automatically create an ERD for an existing database schema?

MySQL Workbench MySQL Server MySQL ClientMySQL Cluster

MySQL Workbench is a visual tool that can generate ERDs from existing database schemas, allowing users to visualize and design database structures.

Q21

Multiple Choice

Identify the correct SQL statement to create a table for an entity in an ERD representing customers.

SQL Code

CREATE TABLE customers (
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(100) NOT NULL,
 email VARCHAR(100) UNIQUE,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE customers ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE customers ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(100), created_at TIMESTAMP ); CREATE TABLE customers ( id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );CREATE TABLE customers ( id INT AUTO_INCREMENT, name VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

This SQL statement creates a table for the customers entity, including an auto-incrementing primary key, a unique email field, and a timestamp for when each record was created.

Q22

Multiple Choice

Choose the correct SQL statement to establish a foreign key relationship in an ERD between orders and customers.

SQL Code

CREATE TABLE orders (
 id INT AUTO_INCREMENT PRIMARY KEY,
 customer_id INT,
 order_date DATE,
 FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, order_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(id) ); CREATE TABLE orders ( id INT PRIMARY KEY, customer_id INT, order_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(id) ); CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, order_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE );CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, order_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );

This SQL statement creates an orders table with a foreign key that references the id column in the customers table, establishing a relationship between the two entities.

Q23

Multiple Choice

Determine the correct SQL statement to represent a many-to-many relationship in an ERD using a junction table.

SQL Code

CREATE TABLE order_products (
 order_id INT,
 product_id INT,
 quantity INT,
 PRIMARY KEY (order_id, product_id),
 FOREIGN KEY (order_id) REFERENCES orders(id),
 FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE order_products ( order_id INT, product_id INT, quantity INT, PRIMARY KEY (order_id, product_id), FOREIGN KEY (order_id) REFERENCES orders(id), FOREIGN KEY (product_id) REFERENCES products(id) ); CREATE TABLE order_products ( order_id INT, product_id INT, quantity INT, PRIMARY KEY (order_id, product_id), FOREIGN KEY (order_id) REFERENCES orders(id) ); CREATE TABLE order_products ( order_id INT, product_id INT, PRIMARY KEY (order_id, product_id), FOREIGN KEY (order_id) REFERENCES orders(id), FOREIGN KEY (product_id) REFERENCES products(id) );CREATE TABLE order_products ( order_id INT, product_id INT, quantity INT, PRIMARY KEY (order_id, product_id) );

This SQL statement creates a junction table named order_products to represent the many-to-many relationship between orders and products. It includes foreign keys that reference the orders and products tables.

Q24

Multiple Choice

Which SQL statement correctly creates an entity with a composite primary key in an ERD?

SQL Code

CREATE TABLE student_courses (
 student_id INT,
 course_id INT,
 enrollment_date DATE,
 PRIMARY KEY (student_id, course_id)
);

CREATE TABLE student_courses ( student_id INT, course_id INT, enrollment_date DATE, PRIMARY KEY (student_id, course_id) ); CREATE TABLE student_courses ( student_id INT, course_id INT, enrollment_date DATE, PRIMARY KEY (student_id) ); CREATE TABLE student_courses ( student_id INT, course_id INT, enrollment_date DATE, PRIMARY KEY (course_id) );CREATE TABLE student_courses ( student_id INT, course_id INT, enrollment_date DATE, PRIMARY KEY (student_id, enrollment_date) );

This SQL statement creates a student_courses table with a composite primary key consisting of student_id and course_id, representing a many-to-many relationship between students and courses.

Q25

Multiple Choice

Select the correct SQL statement to add a new column to an existing table that represents an attribute in an ERD.

SQL Code

ALTER TABLE customers
ADD COLUMN phone_number VARCHAR(15);

ALTER TABLE customers ADD COLUMN phone_number VARCHAR(15); ALTER TABLE customers ADD phone_number VARCHAR(15); ALTER TABLE customers ADD COLUMN phone_number CHAR(15);ALTER TABLE customers ADD phone_number CHAR(15);

This SQL statement adds a new column named phone_number to the existing customers table, representing a new attribute in the entity's ERD.

Q26

Multiple Choice

Determine the correct SQL statement to create an entity with a self-referencing foreign key in an ERD.

SQL Code

CREATE TABLE employees (
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(100) NOT NULL,
 manager_id INT,
 FOREIGN KEY (manager_id) REFERENCES employees(id)
);

CREATE TABLE employees ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, manager_id INT, FOREIGN KEY (manager_id) REFERENCES employees(id) ); CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, manager_id INT, FOREIGN KEY (manager_id) REFERENCES employees(id) ); CREATE TABLE employees ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, manager_id INT, FOREIGN KEY (manager_id) REFERENCES managers(id) );CREATE TABLE employees ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, manager_id INT REFERENCES employees(id) );

This SQL statement creates an employees table where the manager_id column is a self-referencing foreign key, establishing a relationship within the same entity to represent hierarchical data.

Q27

Multiple Choice

Which SQL statement correctly modifies an existing table to enforce a NOT NULL constraint on a column?

SQL Code

ALTER TABLE orders
MODIFY COLUMN order_date DATE NOT NULL;

ALTER TABLE orders MODIFY COLUMN order_date DATE NOT NULL; ALTER TABLE orders ALTER COLUMN order_date SET NOT NULL; ALTER TABLE orders CHANGE order_date DATE NOT NULL;ALTER TABLE orders MODIFY order_date DATE NOT NULL;

This SQL statement alters the orders table to enforce a NOT NULL constraint on the order_date column, ensuring that every order must have a date.

Q28

Multiple Choice

Identify the correct SQL statement to create a table with a CHECK constraint in an ERD to validate an attribute.

SQL Code

CREATE TABLE products (
 id INT AUTO_INCREMENT PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 price DECIMAL(10,2) CHECK (price > 0)
);

CREATE TABLE products ( id INT AUTO_INCREMENT PRIMARY KEY, product_name VARCHAR(100) NOT NULL, price DECIMAL(10,2) CHECK (price > 0) ); CREATE TABLE products ( id INT AUTO_INCREMENT PRIMARY KEY, product_name VARCHAR(100) NOT NULL, price DECIMAL(10,2) CHECK price > 0 ); CREATE TABLE products ( id INT AUTO_INCREMENT PRIMARY KEY, product_name VARCHAR(100) NOT NULL, price DECIMAL(10,2) CONSTRAINT price_check CHECK (price > 0) );CREATE TABLE products ( id INT AUTO_INCREMENT PRIMARY KEY, product_name VARCHAR(100) NOT NULL, price DECIMAL(10,2) CONSTRAINT CHECK (price > 0) );

This SQL statement creates a products table with a CHECK constraint on the price column, ensuring that the price is always greater than zero.

Q29

Multiple Choice

Determine the correct SQL statement to create a table with a default value for an attribute in an ERD.

SQL Code

CREATE TABLE accounts (
 id INT AUTO_INCREMENT PRIMARY KEY,
 account_name VARCHAR(100) NOT NULL,
 balance DECIMAL(10,2) DEFAULT 0.00
);

CREATE TABLE accounts ( id INT AUTO_INCREMENT PRIMARY KEY, account_name VARCHAR(100) NOT NULL, balance DECIMAL(10,2) DEFAULT 0.00 ); CREATE TABLE accounts ( id INT AUTO_INCREMENT PRIMARY KEY, account_name VARCHAR(100) NOT NULL, balance DECIMAL(10,2) DEFAULT = 0.00 ); CREATE TABLE accounts ( id INT AUTO_INCREMENT PRIMARY KEY, account_name VARCHAR(100) NOT NULL, balance DECIMAL(10,2) DEFAULT '0.00' );CREATE TABLE accounts ( id INT AUTO_INCREMENT PRIMARY KEY, account_name VARCHAR(100) NOT NULL, balance DECIMAL(10,2) DEFAULT 0 );

This SQL statement creates an accounts table where the balance column has a default value of 0.00, ensuring that new accounts start with a zero balance unless otherwise specified.

Q30

Multiple Choice

Which SQL statement correctly creates a table with a UNIQUE constraint to enforce an attribute's uniqueness in an ERD?

SQL Code

CREATE TABLE users (
 id INT AUTO_INCREMENT PRIMARY KEY,
 username VARCHAR(50) NOT NULL,
 email VARCHAR(100) UNIQUE
);

CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE ); CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE KEY ); CREATE TABLE users ( id INT PRIMARY KEY, username VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE );CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL, email VARCHAR(100) CONSTRAINT UNIQUE );

This SQL statement creates a users table with a UNIQUE constraint on the email column, ensuring that no two users can have the same email address.

MySQL Database Quiz Questions