Relational Database Design: Keys, Relationships, and Normalization

What is the difference between a base table and a view?

A base table is the original table, while a view is a derived subset of that table.

What is a crow's foot in database design?

A crow's foot is a symbol used to represent the nature of relationships between entities.

What is a foreign key?

A foreign key is a primary key from one table that appears in another table to create a relationship.

What are the types of databases mentioned?

Hierarchical, network, relational, and object-oriented databases.

What is the role of an associative entity in a many-to-many relationship?

It allows for the representation of relationships where multiple instances of one entity relate to multiple instances of another.

What does it mean for a primary key to be 'minimal'?

It means that the primary key contains no unnecessary attributes; it is the smallest set of attributes that can uniquely identify a record.

What does it mean when a primary key is described as 'not null'?

It means that the primary key must have a value and cannot be left empty.

What does the crow's foot notation indicate?

It visually represents relationships between entities in an entity-relationship diagram.

What is the purpose of normalization in databases?

Normalization organizes data to reduce redundancy and improve data integrity.

What does a one-to-many relationship signify?

One entity can be associated with many instances of another entity.

What is the primary key for the LINEITEM associative table?

The primary key is formed from both ORDER$id and PRODUCT$id.

What is the basis of the relational model?

The relational model is based upon tables with rows and columns.

What is the advantage of relational databases?

They rely on logical links between records and allow a high degree of data independence.

List the characteristics of a primary key.

Unique, minimal, not null, nonupdateable.

What is a primary key?

A primary key uniquely identifies each record in a table.

What is first normal form (1NF)?

A table is in 1NF when each field contains single values only.

What is a view in a database?

A view is a subset derived from base tables, restricting the portion of the database visible to users.

What is a weak entity?

A weak entity is a child table that requires a parent table for identification.

What is entity integrity?

Entity integrity ensures that every table has a valid primary key.

What is the significance of the naming convention for foreign keys?

Foreign keys follow the naming convention of PARENT_TABLENAME$parent_fieldname.

What is an associative table?

An associative table is a child of two parent tables in a many-to-many relationship.

How is a many-to-many relationship represented?

By creating an associative entity.

What is the purpose of attributes in a database?

Attributes define the properties or characteristics of an entity.

How are computer-generated primary keys formed?

By adding one to the last number assigned or establishing a single-field primary key.

What is referential integrity?

Referential integrity requires that foreign key values match existing primary key values.

What are tables in the relational model called?

Tables are called relations.