Ch 3. Relational Data Modeling

What is a data model?

A particular representation of data that specifies a subject and its attributes, grouping them together based on their relationship to that subject.

What is abstraction?

A process of simplification that removes details and replaces them with concepts to make describing data easier.

What is an abstract data model?

A representation even simpler than a standard data model, used specifically to simplify the understanding of data.

What does UML stand for, and what is its purpose?

Unified Modeling Language. A standardized modeling language used to visually represent, design, and implement complex software systems by dividing them into components and sub-components.

In a UML class diagram, what are items that have parentheses () called?

Methods.

What is an Object Diagram?

A snapshot of the objects (instances of classes) in a system at a given point in time. It changes as the program runs and is excellent for building prototypes to see data flow.

What are the design guidelines for building an Object Diagram?

It must be named clearly, objects must be connected to show associations, values should be included where necessary, and it must be documented to ensure understanding.

What is a Class Diagram?

A diagram that shows classes, their attributes, methods, constraints, and relationships. It acts as the blueprint for the final product.

What is an Association in a UML class diagram?

A relationship between classes, drawn simply as a line.

What is Aggregation, and how is it drawn?

A parent/child relationship where the child survives if the parent class is destroyed. It is drawn as a line with an open diamond pointing to the parent.

What is Composition?

An association where the child cannot survive on its own if the parent class is destroyed.

What is Inheritance?

A relationship where typically two or more classes act as sub-classes of a top class.

What is a database schema?

The conceptual blueprint of a relational database containing the design, list of attributes, and instructions that tell the database engine how data is organized and related.

What is Schema Theory and what are Schemata?

Schema Theory states that knowledge is organized into units. These individual units of knowledge that store information are called schemata.

What limitations can field attributes and rules enforce in a table?

They can limit data types, enforce specific formats, require specific masks, or restrict the data to a specific number of characters.

What is a sort key?

A key that allows the database engine to quickly sort a table into a specific order.

What is a linked key?

A key used to connect entries in one table with associated entries in another. Good linked keys are unique.

What is the difference between a Physical Schema and a Logical Schema?

A Physical Schema displays the hardware, servers, and connections needed to install the database. A Logical Schema describes the structure of the data itself.

What visual style is used for Primary Keys when graphically mapping out a schema before data is entered?

They are written in bold.

What causes database anomalies, and what do they impact?

Poor table relations and erroneous data cause anomalies, which negatively impact information quality.

What is Normalization?

The process of removing existing or possible anomalies, data redundancies, and data inaccuracies. It limits tables to a single particular purpose or entity.

What is a Normal Form?

The specific stage at which a table is organized.

What is a Transitive Dependency?

A condition where a non-primary attribute depends on another non-primary attribute, rather than directly on the primary key (facilitated through a column located between them).

Who developed SQL, when, and who first made it publicly available?

Developed in the early 1970s by IBM; it became publicly available from Oracle in 1979.

What is the action of the SQL SELECT command?

Retrieve information in a database.

What is the action of the SQL INSERT command?

Add information to a database.

What is the action of the SQL DELETE command?

Remove information from a database.

What is the action of the SQL UPDATE command?

Change information in a database.

What is the action of the SQL CREATE command?

Generate a new object in a database.

Does a DBMS automatically normalize your database?

No, normalization is not automatic. The database designer must do it.

What are the core requirements to satisfy First Normal Form (1NF)?

1. Must have a primary key. 2. Columns must have unique names and single values. 3. Attributes must share the same data type down a column. 4. No two rows can be completely identical. 5. Data cannot be repeated across fields (no redundant/combined fields).

What does 'granular' mean in 1NF?

It means each individual data element is broken down so far that it cannot be split into any smaller chunks.

What are the core requirements to satisfy Second Normal Form (2NF)?

The table must already be in 1NF, and all partial dependencies on the primary key are removed (meaning fields must rely entirely on the primary key).

What are the core requirements to satisfy Third Normal Form (3NF)?

The table must already be in 2NF, and it must contain zero columns or fields that have a transitive dependence. All non-primary fields must depend directly on the primary field.