Fundamental Database Concepts Flashcards

Which of the following BEST describes the primary purpose of the relational model in database design?

Answer: To define relationships between data points using tables, rows, and columns, ensuring data consistency.

The relational model's core purpose is to organize data into tables (relations) with rows (tuples) and columns (attributes). This structure allows for the explicit definition of relationships between different data points, which helps maintain data consistency and integrity.

A database administrator is designing a table to store employee information, including their skills. To avoid data redundancy, they decide to create a separate 'Skills' table and a 'Employee_Skills' table to link employees to their skills. This process is an example of what fundamental database concept?

Answer: Normalization

Normalization is the process of organizing columns and tables in a relational database to minimize data redundancy. By creating separate tables for employees and skills and a third table to link them, the administrator is eliminating redundant data (not repeating skill names for each employee) and improving data integrity, which is the primary goal of normalization.

In an Oracle database, which of the following are considered schema objects?

Answer: Tables, Views, Indexes, and Sequences

Schema objects are logical structures of data owned by a database user. Common examples include tables, views, indexes, and sequences. Users, roles, and tablespaces are considered non-schema objects, and the SGA/PGA are memory structures.

What is the key difference between Data Definition Language (DDL) and Data Manipulation Language (DML)?

Answer: DDL defines and manages the structure of database objects, while DML is used to add, modify, or delete the data within those objects.

Data Definition Language (DDL) includes commands like CREATE, ALTER, and DROP, which are used to define the database schema and its objects. Data Manipulation Language (DML) includes commands like INSERT, UPDATE, and DELETE, which are used to manipulate the actual data stored within those objects.

A developer needs to create a table to store customer orders. Each order must have a unique identifier. Which of the following constraints is MOST appropriate for the 'OrderID' column to ensure each row can be uniquely identified?

Answer: PRIMARY KEY

A PRIMARY KEY constraint uniquely identifies each record in a table. It must contain unique values and cannot contain NULL values. This is the most suitable constraint for an OrderID column to ensure every order has a unique identifier.

An Entity-Relationship Diagram (ERD) is used during the database design phase. What does a rectangle typically represent in an ERD?

Answer: An entity, such as a person, place, or object, about which data is stored

In an Entity-Relationship Diagram (ERD), a rectangle represents an entity. An entity is a real-world object, concept, or event about which data is collected, such as 'Customer', 'Product', or 'Order'.