Test Your PostgreSQL ERD (Data Model) Knowledge with this Quiz

Progress

True / False

In PostgreSQL, an ERD (Entity-Relationship Diagram) helps visualize the database schema.

True False

An ERD is a graphical representation of a database schema, showing entities, their attributes, and relationships.

True / False

In PostgreSQL, entities in an ERD correspond to tables in the database.

True False

Entities in an ERD are represented as tables in PostgreSQL, with attributes corresponding to columns.

True / False

In PostgreSQL, primary keys in an ERD are used to uniquely identify each row in a table.

True False

Primary keys are attributes in an ERD that uniquely identify each record in a table in PostgreSQL.

True / False

In PostgreSQL, relationships in an ERD are implemented using foreign keys.

True False

Relationships between entities in an ERD are implemented using foreign keys in PostgreSQL, which reference primary keys in other tables.

True / False

In PostgreSQL, an ERD cannot represent many-to-many relationships.

True False

Many-to-many relationships can be represented in an ERD and are implemented in PostgreSQL using junction tables.

True / False

In PostgreSQL, each attribute in an ERD must correspond to a unique column in a table.

True False

While each attribute in an ERD typically corresponds to a column, attributes can sometimes be derived or calculated fields that do not directly map to a single column.

True / False

In PostgreSQL, ERDs can include constraints such as unique, not null, and check constraints.

True False

ERDs can include various constraints that are implemented in PostgreSQL to enforce data integrity and rules.

True / False

In PostgreSQL, an ERD can represent hierarchical data structures using recursive relationships.

True False

Hierarchical data structures can be represented in an ERD using recursive relationships, and these can be implemented in PostgreSQL using self-referential foreign keys.

True / False

In PostgreSQL, an ERD cannot include views or materialized views.

True False

ERDs can include views and materialized views to represent derived or precomputed data structures in PostgreSQL.

Q10

True / False

In PostgreSQL, an ERD must be strictly adhered to and cannot be modified once the database is created.

True False

An ERD is a design tool and can be modified as needed to accommodate changes in the database schema or business requirements. The database schema in PostgreSQL can be updated to reflect these changes.

Q11

Single Choice

Which of the following best describes an ERD (Entity-Relationship Diagram)?

A tool to design database schemas by visually representing entities and their relationships. A tool to execute SQL queries in PostgreSQL. A method to back up PostgreSQL databases.A technique to monitor database performance.

An ERD is used to design database schemas by visually representing entities, their attributes, and the relationships between them.

Q12

Single Choice

In PostgreSQL, which of the following can be directly derived from an ERD?

Table structures and relationships SQL functions TriggersTriggers

An ERD helps in designing table structures and defining the relationships between them, which can then be implemented in PostgreSQL.

Q13

Single Choice

Which symbol in an ERD represents the relationship between entities in PostgreSQL?

Rectangle Oval DiamondCircle

In an ERD, a diamond is typically used to represent relationships between entities.

Q14

Single Choice

Consider the following PostgreSQL entities in an ERD: Entity Customer has attributes CustomerID and Name. Entity Order has attributes OrderID and OrderDate. What type of relationship is likely between Customer and Order?

One-to-One One-to-Many Many-to-ManyNo relationship

A Customer can have multiple Orders, establishing a One-to-Many relationship.

Q15

Single Choice

Given the ERD relationship between Product and Category where each product belongs to one category and each category can have multiple products, which of the following SQL statements correctly creates the Product table in PostgreSQL?

CREATE TABLE Product ( ProductID SERIAL PRIMARY KEY, ProductName VARCHAR(255), CategoryID INT REFERENCES Category(CategoryID) ); CREATE TABLE Product ( ProductID SERIAL PRIMARY KEY, ProductName VARCHAR(255), CategoryID INT ); CREATE TABLE Product ( ProductID SERIAL PRIMARY KEY, ProductName VARCHAR(255), CategoryID SERIAL REFERENCES Category(CategoryID) );CREATE TABLE Product ( ProductID SERIAL PRIMARY KEY, ProductName VARCHAR(255) );

Option A correctly references the CategoryID from the Category table, establishing the correct foreign key relationship as depicted in the ERD.

Q16

Single Choice

In PostgreSQL, if an ERD shows a Student entity related to a Class entity in a many-to-many relationship, which additional table is necessary?

A join table to store StudentID and ClassID A foreign key in the Student table A foreign key in the Class tableNo additional table is necessary

A join table (often called a junction table) is necessary to implement a many-to-many relationship, storing foreign keys from both Student and Class

Q17

Single Choice

In an ERD for PostgreSQL, how would you represent a weak entity OrderItem that depends on the Order entity, with OrderItemID and OrderID as attributes?

By adding a foreign key in OrderItem pointing to OrderID in Order By making OrderItemID the primary key and removing OrderID By creating a composite key with OrderItemID and OrderID in OrderItemBy creating a new table OrderDetails

A weak entity like OrderItem would use a composite key comprising its primary key and the foreign key from the parent entity Order.

Q18

Single Choice

Which of the following SQL statements correctly represents the creation of a table from an ERD with a one-to-one relationship in PostgreSQL?

CREATE TABLE Passport ( PassportID SERIAL PRIMARY KEY, IssueDate DATE, PersonID INT UNIQUE REFERENCES Person(PersonID) ); CREATE TABLE Passport ( PassportID SERIAL PRIMARY KEY, IssueDate DATE, PersonID INT REFERENCES Person(PersonID) ); CREATE TABLE Passport ( PassportID SERIAL PRIMARY KEY, IssueDate DATE, PersonID INT );CREATE TABLE Passport ( PassportID SERIAL PRIMARY KEY, IssueDate DATE, PersonID INT FOREIGN KEY REFERENCES Person(PersonID) );

The correct implementation of a one-to-one relationship in PostgreSQL uses a unique constraint on the foreign key, ensuring the relationship is enforced.

Q19

Single Choice

When translating an ERD into a PostgreSQL schema, which of the following best describes how to represent a ternary relationship between three entities: Employee, Project, and Task?

By creating a new associative table that includes foreign keys from all three entities By adding foreign keys in one of the entities By creating two separate binary relationshipsBy using a composite key in one of the entities

A ternary relationship should be represented by creating an associative table that includes foreign keys from all three involved entities.

Q20

Single Choice

In a PostgreSQL ERD, which of the following best represents a scenario where an entity has a recursive relationship?

A table that has a foreign key referencing its primary key A table that has no foreign keys A join table with foreign keys from two different tablesA table with a composite key including two foreign keys

A recursive relationship in an ERD is represented by a table where a foreign key references its own primary key, allowing entities to relate to themselves (e.g., an employee supervising another employee).

Q21

Multiple Choice

What is the purpose of an Entity-Relationship Diagram (ERD) in database design?

SQL Code

You have a simple ERD with two entities: 'customers' and 'orders'. Write a SQL script to create the corresponding tables in PostgreSQL, ensuring the 'orders' table references the 'customers' table.

```sql
CREATE TABLE customers (
 customer_id SERIAL PRIMARY KEY,
 customer_name VARCHAR(100)
);

CREATE TABLE orders (
 order_id SERIAL PRIMARY KEY,
 order_date DATE,
 customer_id INT REFERENCES customers(customer_id)
);
```

To visualize relationships between entities To design the logical structure of a database To automatically generate database codeTo define the physical storage details

An ERD helps in visualizing the relationships between different entities in a database, aiding in the logical design of the database schema.

Q22

Multiple Choice

What type of relationship is represented between two entities when a foreign key is used?

SQL Code

CREATE TABLE students (
 student_id SERIAL PRIMARY KEY,
 student_name VARCHAR(100)
);

CREATE TABLE courses (
 course_id SERIAL PRIMARY KEY,
 course_name VARCHAR(100)
);

CREATE TABLE enrollments (
 enrollment_id SERIAL PRIMARY KEY,
 student_id INT REFERENCES students(student_id),
 course_id INT REFERENCES courses(course_id)
);

One-to-one One-to-many Many-to-oneMany-to-many

A foreign key is used to establish a relationship between two entities, typically representing a one-to-many or many-to-one relationship.

Q23

Multiple Choice

How would you represent a many-to-many relationship in an ERD?

SQL Code

Given an ERD with 'authors' and 'books' where each author can write multiple books and each book can have multiple authors, write a SQL script to implement this relationship in PostgreSQL.

```sql
CREATE TABLE authors (
 author_id SERIAL PRIMARY KEY,
 author_name VARCHAR(100)
);

CREATE TABLE books (
 book_id SERIAL PRIMARY KEY,
 book_title VARCHAR(100)
);

CREATE TABLE author_books (
 author_id INT REFERENCES authors(author_id),
 book_id INT REFERENCES books(book_id),
 PRIMARY KEY (author_id, book_id)
);
```

Use a junction table with foreign keys Directly link the entities with a foreign key Use a composite primary keyCreate two one-to-many relationships

A many-to-many relationship is represented in an ERD by using a junction table that contains foreign keys referencing the related entities.

Q24

Multiple Choice

What is the significance of primary keys in an ERD?

SQL Code

Create a table 'departments' with a primary key 'department_id' and a unique constraint on the 'department_name'. Write a SQL script to create this table in PostgreSQL.

```sql
CREATE TABLE departments (
 department_id SERIAL PRIMARY KEY,
 department_name VARCHAR(100) UNIQUE
);
```

To uniquely identify records To enforce uniqueness within a table To establish relationships with other tablesTo store large amounts of text data

Primary keys uniquely identify each record in a table and are essential in establishing relationships between entities in an ERD.

Q25

Multiple Choice

In an ERD, what does a 'crow's foot' notation represent?

SQL Code

Given an ERD that shows a one-to-many relationship between 'categories' and 'products', write a SQL script to create these tables with appropriate foreign key constraints in PostgreSQL.

```sql
CREATE TABLE categories (
 category_id SERIAL PRIMARY KEY,
 category_name VARCHAR(100)
);

CREATE TABLE products (
 product_id SERIAL PRIMARY KEY,
 product_name VARCHAR(100),
 category_id INT REFERENCES categories(category_id)
);
```

One-to-many relationship Many-to-many relationship One-to-one relationshipMany-to-one relationship

The 'crow's foot' notation in an ERD indicates a one-to-many relationship between two entities.

Q26

Multiple Choice

How would you model a self-referencing relationship in an ERD?

SQL Code

Create a 'categories' table where each category can have a parent category. Write a SQL script to implement this self-referencing relationship in PostgreSQL.

```sql
CREATE TABLE categories (
 category_id SERIAL PRIMARY KEY,
 category_name VARCHAR(100),
 parent_category_id INT REFERENCES categories(category_id)
);
```

Use a foreign key referencing the same table Create a separate table to store relationships Use a composite key for hierarchyDefine a one-to-many relationship with itself

A self-referencing relationship in an ERD is modeled by having a foreign key in a table that references the primary key of the same table, representing hierarchical data.

Q27

Multiple Choice

What is the role of composite keys in an ERD?

SQL Code

Given an ERD with 'employees' and 'projects' where each employee can work on multiple projects and each project can have multiple employees, write a SQL script to implement this many-to-many relationship using a composite key in PostgreSQL.

```sql
CREATE TABLE employees (
 employee_id SERIAL PRIMARY KEY,
 employee_name VARCHAR(100)
);

CREATE TABLE projects (
 project_id SERIAL PRIMARY KEY,
 project_name VARCHAR(100)
);

CREATE TABLE employee_projects (
 employee_id INT REFERENCES employees(employee_id),
 project_id INT REFERENCES projects(project_id),
 PRIMARY KEY (employee_id, project_id)
);
```

To uniquely identify records in junction tables To establish one-to-one relationships To enforce uniqueness across multiple columnsTo simplify ERD complexity

Composite keys in an ERD are used to uniquely identify records in a junction table, representing many-to-many relationships between entities.

Q28

Multiple Choice

How does normalization relate to ERD in database design?

SQL Code

You have an ERD with entities 'students' and 'courses'. To avoid redundancy, write a SQL script to normalize the design by creating a 'enrollments' table that stores the relationships between students and courses.

```sql
CREATE TABLE students (
 student_id SERIAL PRIMARY KEY,
 student_name VARCHAR(100)
);

CREATE TABLE courses (
 course_id SERIAL PRIMARY KEY,
 course_name VARCHAR(100)
);

CREATE TABLE enrollments (
 enrollment_id SERIAL PRIMARY KEY,
 student_id INT REFERENCES students(student_id),
 course_id INT REFERENCES courses(course_id)
);
```

Reduces data redundancy Ensures data integrity Simplifies ERD designIntroduces data anomalies

Normalization in ERD design reduces data redundancy by organizing data into related tables, ensuring data integrity and reducing anomalies.

Q29

Multiple Choice

What are the key components of an ERD?

SQL Code

Create an ERD for a 'library' database with entities: 'books', 'authors', and 'borrowers'. Write a SQL script to create these tables with primary and foreign key constraints in PostgreSQL.

```sql
CREATE TABLE books (
 book_id SERIAL PRIMARY KEY,
 book_title VARCHAR(100),
 author_id INT REFERENCES authors(author_id)
);

CREATE TABLE authors (
 author_id SERIAL PRIMARY KEY,
 author_name VARCHAR(100)
);

CREATE TABLE borrowers (
 borrower_id SERIAL PRIMARY KEY,
 borrower_name VARCHAR(100)
);
```

Entities Attributes RelationshipsData types

The key components of an ERD include entities, attributes, and relationships, which together represent the structure and relationships in a database.

Q30

Multiple Choice

How do you represent optional relationships in an ERD?

SQL Code

You have an ERD with 'employees' and 'managers' where each employee may or may not have a manager. Write a SQL script to implement this optional relationship in PostgreSQL.

```sql
CREATE TABLE employees (
 employee_id SERIAL PRIMARY KEY,
 employee_name VARCHAR(100),
 manager_id INT REFERENCES employees(employee_id) ON DELETE SET NULL
);
```

Using nullable foreign keys Using mandatory foreign keys Creating a separate table for relationshipsUsing composite keys

Optional relationships in an ERD are represented using optional (nullable) foreign keys, allowing the relationship to be optional for some entities.

PostgreSQL Database Quiz Questions