IS300 Final Exam: Database Concepts, ER Modeling, Normalization, SQL

What is the primary purpose of databases?

To solve fundamental problems such as data redundancy and data anomalies.

Define 'data'.

Raw facts of interest to the end user.

What does 'information' refer to in database terminology?

The result of processing raw data to reveal its meaning.

What is 'knowledge' in the context of databases?

The familiarity, awareness, and understanding of information as it applies to an environment.

What is 'metadata'?

Data about data, including descriptions of data characteristics like types and sizes.

What is a 'Database Management System' (DBMS)?

A collection of programs that manages the database structure and controls access to the data.

Why is database design important?

It directly impacts an organization's ability to function and supports effective decision-making.

What is data redundancy?

The unnecessary storage of the same data in different places, leading to wasted space and data inconsistency.

What are the consequences of data redundancy?

Wasted space, data inconsistency, data-entry errors, and data integrity problems.

What is an insertion anomaly?

When it is impossible to add a new record due to missing data.

Explain a deletion anomaly.

When deleting a record unintentionally removes other essential data from the database.

What is an update anomaly?

When changes to data are not consistently applied across all occurrences in the database.

Who developed the relational model?

E.F. Codd.

What is a relational table?

A two-dimensional structure composed of rows and columns representing entities and their attributes.

What does each row in a relational table represent?

A single, unique entity occurrence.

What is a primary key?

An attribute or combination of attributes that uniquely identifies every row in a table.

Define a composite key.

A key composed of more than one attribute.

What is a superkey?

Any attribute or combination of attributes that uniquely identifies each row in a table.

What is a candidate key?

A minimal superkey that cannot lose its unique identification property without removing attributes.

What is a foreign key?

An attribute in one table that must match the primary key in another table or be null.

What does functional dependency mean?

The value of one or more attributes determines the value of one or more other attributes.

Give an example of functional dependency.

A student's classification is determined by the number of credit hours they have completed.

What is the significance of the order of rows and columns in a relational table?

The order is immaterial to the DBMS.

What is the attribute domain in a relational table?

The specific range of values that each column can hold.

How does a well-designed database impact decision-making?

It facilitates efficient data management and generates accurate, valuable information.

What can result from a poorly designed database?

Difficult-to-trace errors leading to faulty information and poor decisions.

What is the relationship between data integrity and database design?

Good database design is essential for maintaining data integrity and consistency.

What are 'islands of information'?

Scattered data caused by uncontrolled data redundancy in unnormalized databases.

What is a Foreign Key (FK)?

An attribute in one table that must match a primary key in another table or be null, establishing a link between the two tables.

What is a Secondary Key?

An attribute used strictly for data retrieval purposes, enabling efficient lookups.

What does Entity Integrity ensure?

All primary key entries must be unique, and no part of a primary key may be null.

What is Referential Integrity?

A foreign key must either be null or match a primary key value in the related table, preventing orphan records.

What is an Entity in an ERD?

A person, place, thing, concept, or event about which data is collected, represented as a rectangle.

What is an Attribute in an ERD?

A characteristic of an entity, equivalent to a field in a table.

What is a Relationship in an ERD?

An association among entities that operates in both directions.

How are business rules translated into data model components?

Nouns correspond to entities, and verbs correspond to relationships.

What does Connectivity refer to in ER modeling?

The classification of a relationship, such as one-to-one (1:1), one-to-many (1:M), or many-to-many (M:N).

What does Cardinality express?

The specific minimum and maximum number of entity occurrences associated with one occurrence of the related entity.

What does Crow's Foot notation represent?

Cardinality using symbols on the relationship line to indicate minimum and maximum occurrences.

What is a Weak Relationship?

Exists when the primary key of the child entity does not contain a primary key component of the parent entity, depicted with a dashed line.

What is a Strong Relationship?

Exists when the primary key of the child entity contains a primary key component of the parent entity, depicted with a solid line.

What defines a Weak Entity?

An entity that cannot exist without a parent entity and has a strong relationship with it.

What are Associative Entities used for?

To resolve many-to-many (M:N) relationships by creating a linking table.

What does the Extended Entity Relationship Model (EERM) add?

Semantic constructs like supertypes and subtypes to the original ER model.

What is an Entity Supertype?

A generic entity type that contains common characteristics shared by its subtypes.

What is an Entity Subtype?

Contains unique characteristics of a specific subgroup of the supertype.

What is a Subtype Discriminator?

An attribute in the supertype that determines to which subtype an occurrence belongs.

What is the Disjoint Constraint?

Subtypes are mutually exclusive, meaning an occurrence can only belong to one subtype.

What is the Overlapping Constraint?

Subtypes are not mutually exclusive, allowing an occurrence to belong to multiple subtypes.

What is Partial Completeness in supertype/subtype relationships?

A supertype has optional subtypes; the subtype discriminator can be null.

What is Total Completeness in supertype/subtype relationships?

Every supertype occurrence must be a member of at least one subtype; the subtype discriminator cannot be null.

What is Normalization in database design?

A process for evaluating and correcting table structures to minimize data redundancy and reduce data anomalies.

What is the goal of Normalization?

To create well-structured relations in a database.

What is a partial dependency?

A partial dependency occurs when a non-key attribute is functionally dependent on only part of a composite primary key.

What is a transitive dependency?

A transitive dependency exists when a non-key attribute depends on another non-key attribute instead of directly on the primary key.

What is a dependency diagram?

A dependency diagram is a visual representation of all functional dependencies within a table structure, used to identify undesirable dependencies.

What are desirable dependencies in a dependency diagram?

Desirable dependencies are those where an attribute is dependent on the full primary key, represented with arrows above the attributes.

What are undesirable dependencies in a dependency diagram?

Undesirable dependencies, such as partial and transitive dependencies, are represented with arrows below the attributes.

What is the First Normal Form (1NF)?

A table is in 1NF when it has a defined primary key, is in a table format, and contains no repeating groups.

What conditions must be met for a table to be in Second Normal Form (2NF)?

A table must be in 1NF and have no partial dependencies to be in 2NF.

What is the process to achieve 2NF?

To achieve 2NF, create new tables to eliminate partial dependencies, making each component of the primary key a primary key in its new table.

What conditions must be met for a table to be in Third Normal Form (3NF)?

A table must be in 2NF and have no transitive dependencies to be in 3NF.

What is the process to achieve 3NF?

To achieve 3NF, move any non-key attribute that is a determinant to a new table, making it the primary key, and leave a foreign key in the original table.

What is SQL?

SQL (Structured Query Language) is the standard language for managing and querying relational databases.

What is the purpose of Data Definition Language (DDL)?

DDL is used to define, modify, and manage the structure of the database, including tables, columns, and constraints.

What does the CREATE TABLE command do?

The CREATE TABLE command is used to create a new table, specifying its columns and their data types.

What does the ALTER TABLE command do?

The ALTER TABLE command modifies the structure of an existing table by adding, dropping, or modifying columns and constraints.

What does the DROP TABLE command do?

The DROP TABLE command permanently deletes a table and all its data, an irreversible action.

What is the INTEGER data type used for?

The INTEGER data type is used for whole numbers.

What is the DECIMAL(M,D) data type used for?

The DECIMAL(M,D) data type is used for fixed-point numbers where precision is important, such as currency.

What is the DATE data type used for?

The DATE data type is used for storing date values.

What is the TIMESTAMP data type used for?

The TIMESTAMP data type is used for storing a point in time, including both date and time.

What is the CHAR(size) data type used for?

The CHAR(size) data type is used for fixed-length strings, padded with spaces if shorter than the specified size.

What is the VARCHAR(size) data type used for?

The VARCHAR(size) data type is used for variable-length strings, using storage only for the characters entered.

What does the NOT NULL constraint do?

The NOT NULL constraint ensures that a column cannot have a NULL (empty) value.

What does the UNIQUE constraint do?

The UNIQUE constraint ensures that all values in a column are different from one another.

What does the DEFAULT constraint do?

The DEFAULT constraint provides a default value for a column when no value is specified during an INSERT.

What does the CHECK constraint do?

The CHECK constraint ensures that all values in a column satisfy a specific condition.

What is the purpose of Data Manipulation Language (DML)?

DML is used to perform operations on the actual data stored within the tables, such as retrieving, adding, modifying, and deleting records.

What does the SELECT command do?

The SELECT command is used to retrieve data from one or more tables.

What does the INSERT INTO command do?

The INSERT INTO command is used to add new rows of data into a table.

What does the UPDATE command do?

The UPDATE command modifies existing data in a table, with the WHERE clause specifying which record(s) to change.

What is the purpose of the WHERE clause in SQL?

To specify which record(s) to change or delete.

What SQL command is used to remove one or more rows from a table?

DELETE FROM

What is the function of the JOIN clause in SQL?

To combine rows from two or more tables based on a related column.

What does an INNER JOIN return?

Only the rows that have matching values in both tables.

What is the difference between LEFT JOIN and RIGHT JOIN?

LEFT JOIN returns all rows from the left table and matched rows from the right; RIGHT JOIN does the opposite.

What is a CROSS JOIN?

It creates a Cartesian product of two tables, pairing every row of the first table with every row of the second.

What is a NATURAL JOIN?

It automatically links tables based on columns that have the same name and data type.

What is the purpose of table aliases in SQL?

To assign a short, temporary name to a table within a query for easier reference.

What are aggregate functions in SQL?

Functions that perform a calculation on a set of rows and return a single summary value.

What does the COUNT() function do?

Returns the number of rows that match a specified criterion.

What is the purpose of the GROUP BY clause?

To group rows that have the same values in specified columns into summary rows.

What is the HAVING clause used for?

To filter the results of a GROUP BY operation based on a condition involving an aggregate function.

What is a subquery in SQL?

A SELECT statement nested inside another SQL statement.

How does a subquery in the WHERE clause function?

It filters results based on a list of values returned by the inner query.

What is the Database Life Cycle (DBLC)?

A structured process used to manage a database from its initial concept to its retirement.

What is the first phase of the DBLC?

Database Initial Study.

What does the Database Design phase involve?

Creating conceptual, logical, and physical models to meet project requirements.

What is the focus of the Physical Design stage?

Translating the logical model into a specific physical implementation for the chosen DBMS.