Database Modeling, Normalization, and SQL Concepts for ACCT/ITSS 4301

What is a primary key (PK)?

A candidate key that is chosen to uniquely identify each row in a relation.

What is a candidate key?

A determinant that can functionally determine all other attributes in a row.

What is a domain/key normal form (DK/NF)?

A form that focuses on the relationship between domains and keys in a database.

What is a composite key?

A key that contains two or more attributes to create uniqueness.

What is the significance of the candidate key in normalization?

It helps identify unique identifiers for rows and supports the normalization process.

What is the relationship between ORDER and PRODUCT in a database?

It is a many-to-many relationship requiring an intersection table.

What does it mean for a primary key to be a candidate key?

It must determine all other attributes in the row and be unique.

What are the phases of database design?

Logical design and physical design.

What is the difference between a logical and physical database model?

Logical models focus on structure and relationships, while physical models focus on implementation specifics.

What is the significance of normalization in database design?

Normalization helps eliminate redundancy and ensures data integrity by organizing data into appropriate tables.

What is the difference between OLTP and OLAP databases?

OLTP databases are used for transaction processing, while OLAP databases are used for data analysis and reporting.

What happens when a candidate key cannot serve as a primary key?

The entity attributes may need further refinement to determine a suitable primary key.

What is referential integrity?

The requirement that every value of a foreign key must match a value of an existing primary key.

What is the significance of candidate keys in a database?

They provide options for selecting a primary key based on their ability to uniquely identify rows.

What is the role of a foreign key (FK)?

To create a link between two tables by referencing the primary key of another table.

What is the purpose of a database design?

To create a set of database specifications that can be implemented in a DBMS.

What is the significance of relationship cardinalities?

To define the number of instances of one entity that can or must be associated with each instance of another entity.

What is the purpose of Fifth Normal Form (5NF)?

To ensure that every join dependency is a consequence of the candidate keys.

What is the role of functional dependencies in normalization?

To establish how attributes relate to each other and to identify candidate keys.

What is the main focus of normalization?

To remove anomalies and redundancies in database design.

What is the role of a primary key (PK)?

To uniquely identify each record in a table.

What is the purpose of creating an ERD (Entity-Relationship Diagram)?

To visually represent the relationships between entities in a database.

What is cloud computing in the context of databases?

Using another company's hardware to conduct business, accessed via an Internet connection.

What is a transitive dependency?

When a non-key attribute depends on another non-key attribute rather than the primary key.

What is the difference between a relation and a relationship?

A relation is a table in a database, while a relationship describes how tables are linked.

What is a recursive relationship?

A relationship where an entity has a relationship to itself.

What is a functional dependency?

A relationship where one attribute uniquely determines another attribute.

What is the third normal form (3NF)?

A rule that requires all attributes to be dependent only on the primary key and not on any other non-key attributes.

What is the second normal form (2NF)?

A rule that requires all non-key attributes to be fully functionally dependent on the primary key.

What is the first normal form (1NF)?

A rule that requires avoiding multi-valued fields and repeating groups, focusing on the column's content.

What is a field in a database?

A single column in a table that contains all rows.

What is a record in a database?

A single entry in a table that contains all columns.

What is a table in a database?

A structured collection of data organized into rows and columns.

What is a business intelligence (BI) system?

A system consisting of tools used to analyze and report on company data.

What is an intersection table?

A table created to handle many-to-many relationships between entities.

What is Boyce Codd Normal Form (BCNF)?

A table is in BCNF if it is in 3NF and all determinants are candidate keys.

What is a surrogate key?

A unique, DBMS-assigned identifier added to a table to serve as the primary key.

What is a weak entity?

An entity that cannot exist without the existence of another entity.

What is Structured Query Language (SQL)?

An international standard for creating, processing, and querying databases and their tables.

What is a database?

An organized collection of tables that have relationships with each other.

What is metadata in a database?

Data that describes other data, such as column names and table names.

What does physical design involve?

Data types specific to the database, domain constraints, and allowed table/column names.

What is the first step in designing a database?

Determine the entities.

What does Fourth Normal Form (4NF) require?

Each table must be in BCNF and all multivalued dependencies must be moved to their own table.

What does logical design focus on?

Entities and relationships, normalization, primary keys (PK), foreign keys (FK), and relationship cardinalities.

What does it mean for an attribute to further define an entity?

Every attribute must provide additional information about the entity it belongs to.

What is a strong entity?

Any entity that is not a weak entity.

What is the importance of determining attributes in database design?

Attributes define the properties of entities and are essential for creating relations.

What are determinants in database terms?

Attributes that determine the value of another attribute in a functional dependency.

What is data in the context of databases?

Specific values stored in a cell, such as 'Delilah' being 7 characters long.