Test Your Knowledge of Oracle Structured vs Unstructured Data

Progress

True / False

In ORACLE, an Entity-Relationship Diagram (ERD) is used to visually represent the structure of a database.

True False

An ERD is a visual representation of the entities in a database and the relationships between them, which helps in designing and understanding the database structure.

True / False

In an ORACLE ERD, entities are represented by rectangles.

True False

In an ERD, entities, which represent tables or objects in a database, are typically depicted as rectangles.

True / False

In ORACLE, a relationship in an ERD can be many-to-many.

True False

A many-to-many relationship in an ERD indicates that multiple records in one entity are related to multiple records in another entity, usually requiring a junction table in the database schema.

True / False

In ORACLE, attributes in an ERD are depicted using ovals.

True False

Attributes, which describe properties of entities, are often depicted as ovals connected to their respective entities.

True / False

In an ORACLE ERD, primary keys are indicated by underlining the attribute name.

True False

Primary keys, which uniquely identify records in an entity, are typically indicated by underlining the attribute name in an ERD.

True / False

In ORACLE, foreign keys are used to establish relationships between entities in an ERD.

True False

Foreign keys are attributes in one entity that reference primary keys in another entity, establishing relationships between the entities.

True / False

In ORACLE ERD, a weak entity does not have its own primary key.

True False

A weak entity has a primary key, but it relies on a strong entity's primary key to form a composite key. It cannot exist without the strong entity.

True / False

In ORACLE ERD, a derived attribute is an attribute that can be calculated from other attributes.

True False

Derived attributes are not stored in the database; they are calculated based on other stored attributes.

True / False

In ORACLE, an ERD can include both physical and logical data models.

True False

An ERD can represent both logical data models (focusing on business requirements and relationships) and physical data models (focusing on the actual implementation in the database).

Q10

True / False

In ORACLE, identifying relationships in an ERD imply that the child entity is dependent on the parent entity for its identity.

True False

In an identifying relationship, the child entity's primary key includes the parent entity's primary key, signifying dependency and a strong association.

Q11

Multiple Choice

Which SQL code snippet demonstrates creating a table based on an ERD where the primary key is a composite key?

SQL Code

CREATE TABLE enrollment (
student_id NUMBER,
course_id NUMBER,
enrollment_date DATE,
PRIMARY KEY (student_id, course_id)
);

CREATE TABLE enrollment ( student_id NUMBER, course_id NUMBER, enrollment_date DATE, PRIMARY KEY (student_id, course_id) ); CREATE TABLE enrollment ( student_id NUMBER, course_id NUMBER, enrollment_date DATE, PRIMARY KEY (student_id, course_id) ); CREATE TABLE enrollment ( student_id NUMBER, course_id NUMBER, enrollment_date DATE, PRIMARY KEY (student_id, course_id) );CREATE TABLE enrollment ( student_id NUMBER PRIMARY KEY, course_id NUMBER, enrollment_date DATE );

Options A, B, and C correctly demonstrate the creation of a table with a composite primary key based on the ERD. Option D is incorrect because it defines a single-column primary key.

Q12

Multiple Choice

Which SQL code snippet demonstrates adding a foreign key constraint based on an ERD model?

SQL Code

ALTER TABLE orders
ADD CONSTRAINT fk_customer_id
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id);

ALTER TABLE orders ADD CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customers (customer_id); ALTER TABLE orders ADD CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customers (customer_id); ALTER TABLE orders ADD CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customers (customer_id);ALTER TABLE orders ADD CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES employees (employee_id);

Options A, B, and C correctly demonstrate adding a foreign key constraint to the `orders` table based on the ERD model. Option D is incorrect because it does not reference the correct table or column.

Q13

Multiple Choice

Which SQL code snippet demonstrates creating a many-to-many relationship in a database based on an ERD?

SQL Code

CREATE TABLE student_course (
student_id NUMBER,
course_id NUMBER,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

CREATE TABLE student_course ( student_id NUMBER, course_id NUMBER, PRIMARY KEY (student_id, course_id), FOREIGN KEY (student_id) REFERENCES students(student_id), FOREIGN KEY (course_id) REFERENCES courses(course_id) ); CREATE TABLE student_course ( student_id NUMBER, course_id NUMBER, PRIMARY KEY (student_id, course_id), FOREIGN KEY (student_id) REFERENCES students(student_id), FOREIGN KEY (course_id) REFERENCES courses(course_id) ); CREATE TABLE student_course ( student_id NUMBER, course_id NUMBER, PRIMARY KEY (student_id, course_id), FOREIGN KEY (student_id) REFERENCES students(student_id), FOREIGN KEY (course_id) REFERENCES courses(course_id) );CREATE TABLE student_course ( student_id NUMBER, course_id NUMBER, PRIMARY KEY (student_id, course_id) );

Options A, B, and C correctly demonstrate creating a many-to-many relationship between students and courses based on the ERD model. Option D is incorrect because it lacks the foreign key constraints.

Q14

Multiple Choice

Which SQL code snippet demonstrates creating an ERD-based table with a self-referencing foreign key?

SQL Code

CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
manager_id NUMBER,
FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, manager_id NUMBER, FOREIGN KEY (manager_id) REFERENCES employees(employee_id) ); CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, manager_id NUMBER, FOREIGN KEY (manager_id) REFERENCES employees(employee_id) ); CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, manager_id NUMBER, FOREIGN KEY (manager_id) REFERENCES employees(employee_id) );CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, manager_id NUMBER );

Options A, B, and C correctly demonstrate creating a table with a self-referencing foreign key based on an ERD model. Option D is incorrect because it does not define the foreign key constraint.

Q15

Multiple Choice

Which SQL code snippet demonstrates the creation of a hierarchical relationship between tables based on an ERD model?

SQL Code

CREATE TABLE departments (
department_id NUMBER PRIMARY KEY,
parent_department_id NUMBER,
FOREIGN KEY (parent_department_id) REFERENCES departments(department_id)
);

CREATE TABLE departments ( department_id NUMBER PRIMARY KEY, parent_department_id NUMBER, FOREIGN KEY (parent_department_id) REFERENCES departments(department_id) ); CREATE TABLE departments ( department_id NUMBER PRIMARY KEY, parent_department_id NUMBER, FOREIGN KEY (parent_department_id) REFERENCES departments(department_id) ); CREATE TABLE departments ( department_id NUMBER PRIMARY KEY, parent_department_id NUMBER, FOREIGN KEY (parent_department_id) REFERENCES departments(department_id) );CREATE TABLE departments ( department_id NUMBER PRIMARY KEY, parent_department_id NUMBER );

Options A, B, and C correctly demonstrate creating a hierarchical relationship between departments based on an ERD model. Option D is incorrect because it does not define the foreign key constraint.

Q16

Multiple Choice

Which SQL code snippet demonstrates advanced creation of an ERD-based table with multiple foreign keys?

SQL Code

CREATE TABLE project_assignment (
assignment_id NUMBER PRIMARY KEY,
employee_id NUMBER,
project_id NUMBER,
FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
FOREIGN KEY (project_id) REFERENCES projects(project_id)
);

CREATE TABLE project_assignment ( assignment_id NUMBER PRIMARY KEY, employee_id NUMBER, project_id NUMBER, FOREIGN KEY (employee_id) REFERENCES employees(employee_id), FOREIGN KEY (project_id) REFERENCES projects(project_id) ); CREATE TABLE project_assignment ( assignment_id NUMBER PRIMARY KEY, employee_id NUMBER, project_id NUMBER, FOREIGN KEY (employee_id) REFERENCES employees(employee_id), FOREIGN KEY (project_id) REFERENCES projects(project_id) ); CREATE TABLE project_assignment ( assignment_id NUMBER PRIMARY KEY, employee_id NUMBER, project_id NUMBER, FOREIGN KEY (employee_id) REFERENCES employees(employee_id), FOREIGN KEY (project_id) REFERENCES projects(project_id) );CREATE TABLE project_assignment ( assignment_id NUMBER PRIMARY KEY, employee_id NUMBER, project_id NUMBER, FOREIGN KEY (employee_id) REFERENCES employees(employee_id) );

Options A, B, and C correctly demonstrate creating a table with multiple foreign keys based on an ERD model. Option D is incorrect because it lacks one of the foreign key constraints.

Q17

Multiple Choice

Which SQL code snippet demonstrates creating an advanced ERD-based table with a unique constraint on a composite key?

SQL Code

CREATE TABLE employee_contact (
employee_id NUMBER,
contact_type VARCHAR2(50),
contact_value VARCHAR2(100),
PRIMARY KEY (employee_id, contact_type),
UNIQUE (employee_id, contact_value)
);

CREATE TABLE employee_contact ( employee_id NUMBER, contact_type VARCHAR2(50), contact_value VARCHAR2(100), PRIMARY KEY (employee_id, contact_type), UNIQUE (employee_id, contact_value) ); CREATE TABLE employee_contact ( employee_id NUMBER, contact_type VARCHAR2(50), contact_value VARCHAR2(100), PRIMARY KEY (employee_id, contact_type), UNIQUE (employee_id, contact_value) ); CREATE TABLE employee_contact ( employee_id NUMBER, contact_type VARCHAR2(50), contact_value VARCHAR2(100), PRIMARY KEY (employee_id, contact_type), UNIQUE (employee_id, contact_value) );CREATE TABLE employee_contact ( employee_id NUMBER, contact_type VARCHAR2(50), contact_value VARCHAR2(100), PRIMARY KEY (employee_id, contact_type) );

Options A, B, and C correctly demonstrate creating a table with a unique constraint on a composite key based on an ERD model. Option D is incorrect because it does not define the unique constraint.

Q18

Multiple Choice

Which SQL code snippet demonstrates certification-level creation of an ERD-based table with multiple composite keys and foreign key constraints?

SQL Code

CREATE TABLE task_assignment (
task_id NUMBER,
employee_id NUMBER,
project_id NUMBER,
PRIMARY KEY (task_id, employee_id),
FOREIGN KEY (employee_id, project_id) REFERENCES project_assignment(employee_id, project_id)
);

CREATE TABLE task_assignment ( task_id NUMBER, employee_id NUMBER, project_id NUMBER, PRIMARY KEY (task_id, employee_id), FOREIGN KEY (employee_id, project_id) REFERENCES project_assignment(employee_id, project_id) ); CREATE TABLE task_assignment ( task_id NUMBER, employee_id NUMBER, project_id NUMBER, PRIMARY KEY (task_id, employee_id), FOREIGN KEY (employee_id, project_id) REFERENCES project_assignment(employee_id, project_id) ); CREATE TABLE task_assignment ( task_id NUMBER, employee_id NUMBER, project_id NUMBER, PRIMARY KEY (task_id, employee_id), FOREIGN KEY (employee_id, project_id) REFERENCES project_assignment(employee_id, project_id) );CREATE TABLE task_assignment ( task_id NUMBER, employee_id NUMBER, PRIMARY KEY (task_id, employee_id) );

Options A, B, and C correctly demonstrate certification-level creation of a table with multiple composite keys and foreign key constraints based on an ERD model. Option D is incorrect because it does not define the foreign key constraints correctly.

Q19

Multiple Choice

Which SQL code snippet demonstrates certification-level creation of a table with an ERD-based complex foreign key relationship?

SQL Code

CREATE TABLE order_items (
order_id NUMBER,
item_id NUMBER,
quantity NUMBER,
PRIMARY KEY (order_id, item_id),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (item_id) REFERENCES items(item_id)
);

CREATE TABLE order_items ( order_id NUMBER, item_id NUMBER, quantity NUMBER, PRIMARY KEY (order_id, item_id), FOREIGN KEY (order_id) REFERENCES orders(order_id), FOREIGN KEY (item_id) REFERENCES items(item_id) ); CREATE TABLE order_items ( order_id NUMBER, item_id NUMBER, quantity NUMBER, PRIMARY KEY (order_id, item_id), FOREIGN KEY (order_id) REFERENCES orders(order_id), FOREIGN KEY (item_id) REFERENCES items(item_id) ); CREATE TABLE order_items ( order_id NUMBER, item_id NUMBER, quantity NUMBER, PRIMARY KEY (order_id, item_id), FOREIGN KEY (order_id) REFERENCES orders(order_id), FOREIGN KEY (item_id) REFERENCES items(item_id) );CREATE TABLE order_items ( order_id NUMBER, item_id NUMBER, quantity NUMBER, PRIMARY KEY (order_id, item_id) );

Options A, B, and C correctly demonstrate certification-level creation of a table with a complex foreign key relationship based on an ERD model. Option D is incorrect because it does not define the foreign key constraints correctly.

Q20

Multiple Choice

Which SQL code snippet demonstrates certification-level use of ERD concepts to create a normalized table structure with foreign key constraints?

SQL Code

CREATE TABLE product_category (
category_id NUMBER PRIMARY KEY,
parent_category_id NUMBER,
category_name VARCHAR2(50),
FOREIGN KEY (parent_category_id) REFERENCES product_category(category_id)
);

CREATE TABLE product_category ( category_id NUMBER PRIMARY KEY, parent_category_id NUMBER, category_name VARCHAR2(50), FOREIGN KEY (parent_category_id) REFERENCES product_category(category_id) ); CREATE TABLE product_category ( category_id NUMBER PRIMARY KEY, parent_category_id NUMBER, category_name VARCHAR2(50), FOREIGN KEY (parent_category_id) REFERENCES product_category(category_id) ); CREATE TABLE product_category ( category_id NUMBER PRIMARY KEY, parent_category_id NUMBER, category_name VARCHAR2(50), FOREIGN KEY (parent_category_id) REFERENCES product_category(category_id) );CREATE TABLE product_category ( category_id NUMBER PRIMARY KEY, parent_category_id NUMBER, category_name VARCHAR2(50) );

Options A, B, and C correctly demonstrate certification-level creation of a normalized table structure with foreign key constraints based on ERD concepts. Option D is incorrect because it does not define the foreign key constraint.

Q21

Single Choice

Which SQL statement is used to create a table that represents an entity in an ERD?

SQL Code

CREATE TABLE department (
 department_id NUMBER PRIMARY KEY,
 department_name VARCHAR2(50)
);

CREATE ENTITY CREATE TABLE CREATE SCHEMACREATE DATABASE

The CREATE TABLE statement is used to define a table that corresponds to an entity in an ERD, with attributes represented as columns.

Q22

Single Choice

How would you define a one-to-many relationship between two entities in Oracle?

SQL Code

CREATE TABLE employee (
 employee_id NUMBER PRIMARY KEY,
 department_id NUMBER,
 CONSTRAINT fk_department FOREIGN KEY (department_id)
 REFERENCES department(department_id)
);

Use a PRIMARY KEY constraint Use a FOREIGN KEY constraint Use a UNIQUE constraintUse a CHECK constraint

A one-to-many relationship is defined by creating a foreign key constraint on the child table (employee) that references the primary key of the parent table (department).

Q23

Single Choice

Which constraint would you apply to ensure that an attribute in an entity is unique across all records?

SQL Code

CREATE TABLE project (
 project_id NUMBER PRIMARY KEY,
 project_code VARCHAR2(10) UNIQUE,
 project_name VARCHAR2(100)
);

PRIMARY KEY FOREIGN KEY UNIQUENOT NULL

A UNIQUE constraint is used to ensure that values in a specified column are unique across all records in the table.

Q24

Single Choice

How would you represent a many-to-many relationship between two entities in Oracle?

SQL Code

CREATE TABLE enrollment (
 student_id NUMBER,
 course_id NUMBER,
 PRIMARY KEY (student_id, course_id),
 FOREIGN KEY (student_id) REFERENCES student(student_id),
 FOREIGN KEY (course_id) REFERENCES course(course_id)
);

Use a foreign key in one table Use a junction table with two foreign keys Use a single table with composite keysUse a CHECK constraint

A many-to-many relationship is represented by creating a junction table that includes foreign keys referencing the primary keys of the related entities.

Q25

Single Choice

Which SQL clause is used to enforce referential integrity between related entities?

SQL Code

CREATE TABLE order_details (
 order_id NUMBER,
 product_id NUMBER,
 CONSTRAINT fk_order FOREIGN KEY (order_id)
 REFERENCES orders(order_id)
);

PRIMARY KEY FOREIGN KEY UNIQUECHECK

The FOREIGN KEY clause is used to enforce referential integrity by ensuring that a value in one table corresponds to a valid value in another table.

Q26

Single Choice

How would you model an entity with optional attributes in an ERD using Oracle SQL?

SQL Code

CREATE TABLE contact (
 contact_id NUMBER PRIMARY KEY,
 phone_number VARCHAR2(15),
 email_address VARCHAR2(50) NULL
);

Use a NOT NULL constraint Use a NULL constraint Allow NULL values in the columnUse a DEFAULT value

Optional attributes in an entity are modeled by allowing NULL values in the corresponding columns in the Oracle table.

Q27

Single Choice

What is the best way to represent an inheritance relationship in an ERD using Oracle SQL?

SQL Code

CREATE TABLE person (
 person_id NUMBER PRIMARY KEY,
 name VARCHAR2(100)
);
CREATE TABLE employee (
 employee_id NUMBER PRIMARY KEY,
 person_id NUMBER,
 CONSTRAINT fk_person FOREIGN KEY (person_id)
 REFERENCES person(person_id)
);

Use a composite key Use a foreign key to the base entity Use a CHECK constraintUse a UNIQUE constraint

Inheritance relationships in an ERD are typically represented by creating a parent table for the base entity and child tables for the derived entities, with foreign keys linking them.

Q28

Single Choice

Which key constraint ensures that each entity instance in a table is uniquely identifiable?

SQL Code

CREATE TABLE customer (
 customer_id NUMBER PRIMARY KEY,
 customer_name VARCHAR2(100)
);

UNIQUE FOREIGN KEY PRIMARY KEYCHECK

The PRIMARY KEY constraint ensures that each entity instance in a table is uniquely identifiable by enforcing uniqueness and non-nullability.

Q29

Single Choice

How would you represent a weak entity in an ERD using Oracle SQL?

SQL Code

CREATE TABLE dependent (
 dependent_id NUMBER,
 employee_id NUMBER,
 PRIMARY KEY (dependent_id, employee_id),
 CONSTRAINT fk_employee FOREIGN KEY (employee_id)
 REFERENCES employee(employee_id)
);

Use a composite key with a foreign key Use a primary key Use a unique constraintUse a check constraint

A weak entity is represented by creating a table with a composite primary key that includes a foreign key to the related strong entity.

Q30

Single Choice

How would you define a relationship between two entities with a mandatory participation constraint?

SQL Code

CREATE TABLE enrollment (
 student_id NUMBER,
 course_id NUMBER,
 PRIMARY KEY (student_id, course_id),
 FOREIGN KEY (student_id) REFERENCES student(student_id) ON DELETE CASCADE,
 FOREIGN KEY (course_id) REFERENCES course(course_id) ON DELETE CASCADE
);

Use a NOT NULL constraint Use an ON DELETE CASCADE constraint Use both NOT NULL and ON DELETE CASCADEUse a UNIQUE constraint

A mandatory participation constraint in a relationship is enforced by using NOT NULL constraints or ON DELETE CASCADE clauses to ensure that related records cannot exist without the other.

Oracle Database Quiz Questions