Database Systems Management

Define what a database and a database management system (DBMS) are and explain their purpose.

Distinguish between file-based systems and database systems, identifying the advantages of the database approach.

Describe the relational data model and explain how data is organized into tables (relations), rows (tuples), and columns (attributes).

Define what an entity is and identify entities from a given business scenario.

Identify and select appropriate attributes for a given entity from a business description.

Define what a primary key (PK) is and explain its role in uniquely identifying entity instances.

Define a composite primary key and identify when it is necessary.

Evaluate candidate keys and select an appropriate primary key for a given entity.

Define what a foreign key (FK) is and explain its role in linking related tables.

Identify foreign keys in a given set of related tables.

Define what a relationship is between entities in a database context.

Identify relationships between entities from a business scenario description.

Distinguish between one-to-one (1:1), one-to-many (1:M), and many-to-many (M:N) relationships.

Determine the cardinality and participation (mandatory vs. optional) of a relationship from a given business rule.

Identify the standard symbols used in ER diagrams for entities, attributes, and relationships.

Draw an ERD for a simple scenario involving two entities with a 1:M relationship.

Draw an ERD for a scenario involving a 1:1 relationship.

Correctly place primary and foreign key indicators on an ERD.

Explain why M:N relationships cannot be directly implemented in a relational database.

Define what a composite (bridge/associative) entity is and explain its purpose.

Identify the primary key of a composite entity, which is typically a composite key formed from the foreign keys of the parent entities.

Convert an M:N relationship into two 1:M relationships using a composite entity.

Draw an ERD that includes a composite entity to resolve an M:N relationship.

Extract entities, attributes, and relationships from a multi-paragraph business scenario.

Determine appropriate cardinalities and participation constraints from business rules.

Synthesize and draw a complete ERD for a scenario with multiple entities, incorporating 1:1, 1:M, and M:N relationships.

Verify an ERD against the original business requirements for completeness and correctness.

Explain the purpose of normalization and the problems caused by data redundancy (update, insert, and delete anomalies).

Define functional dependency and identify functional dependencies in a given table.

State the requirements for a table to be in First Normal Form (1NF).

Convert an unnormalized table into 1NF by removing repeating groups.

Define partial dependency and identify partial dependencies in a table with a composite primary key.

State the requirements for a table to be in Second Normal Form (2NF).

Identify violations of 2NF in a given table.

Convert a 1NF table into 2NF by removing partial dependencies into separate tables.

Define transitive dependency and identify transitive dependencies in a given table.

State the requirements for a table to be in Third Normal Form (3NF).

Identify violations of 3NF in a given table.

Convert a 2NF table into 3NF by removing transitive dependencies into separate tables.

Given an unnormalized table and its functional dependencies, perform normalization to achieve a set of 3NF tables.

Identify the primary and foreign keys required to link the resulting set of 3NF tables.

Analyze a set of tables to determine their level of normalization (1NF, 2NF, or 3NF) and justify the assessment.

Construct a complete ERD that represents a database schema after normalization to 3NF.