BUS4 112 V1

Front

Back

What is a DBMS?

Database Management System — software that enables users to define, create, maintain, and control access to a database. Acts as an interface between applications/users and the data. Key functions: data integrity, security, concurrent access.

What is a Data Dictionary?

A DBMS component that stores metadata — data about data. Contains data definitions, data characteristics, and relationships. (Showed up on Exam 1!)

What is a Schema?

A logical grouping of database objects such as tables, indexes, views, and queries that are related to each other. (Exam 1)

What is a Data Anomaly?

An inconsistency between one part of the data and another part — a design flaw caused by data redundancy. (Exam 1)

What is Data Integrity?

Condition in which data in the database complies with all entity and referential integrity constraints. (Exam 1)

What is Data Inconsistency?

The state where different versions of the same data exist in different places within a database.

What is Data Redundancy?

The repetition of data in a database, often leading to inconsistencies.

What is a Data Warehouse?

A large store of data accumulated from a wide range of sources within a company, used to support management decision-making.

What is Metadata?

Data that describes other data — provides information about content, format, and characteristics.

What is a Query?

A request for data or information from a database.

What is SQL?

Structured Query Language — the de facto standard query language and data access standard supported by the majority of DBMS vendors. (Exam 1)

What is NoSQL?

A class of non-relational database management systems, often used for big data and real-time web applications. NOT the same as SQL.

What is OLTP?

Online Transaction Processing — handles many concurrent, relatively simple transactions (e.g., ATM withdrawals, online orders). Day-to-day operations.

What is OLAP?

Online Analytical Processing — analyzes data from multiple database systems simultaneously. Used for decision-making, analytics, and reporting.

What is the difference between OLTP and OLAP?

OLTP: daily transactions, simple/fast queries, current data, operational DB. OLAP: analytics/reporting, complex aggregated queries, historical data, data warehouse.

What is a Data Mart?

A subject-oriented SUBSET of a data warehouse, typically designed for a specific department or business function.

What is Structured Data?

Data that resides in a fixed field within a record or file and is organized in a highly formatted way (e.g., relational tables).

What is Semi-Structured Data?

Data that does not conform to a fixed schema but contains tags or other markers to separate semantic elements (e.g., XML, JSON).

What is Unstructured Data?

Data that has no predefined format or organization (e.g., text documents, images).

What is a Distributed Database?

A database where parts of it are stored on multiple computers located in various physical locations.

What is a Centralized Database?

A database stored at a single location that multiple users can access.

Tricky fact: When designing from business rules, what do nouns and verbs become?

NOUNS → ENTITIES, VERBS → RELATIONSHIPS. (NOT the other way around — common trap on Quiz 1!)

What is XML?

Extensible Markup Language — a markup language that defines rules for encoding documents in a format that is both human-readable and machine-readable.

What are the 5 Vs of Big Data?

1. Volume — sheer size 2. Velocity — speed of generation 3. Variety — different formats 4. Veracity — accuracy/trustworthiness 5. Value — usefulness of the data

Is Veracity a characteristic of Big Data?

YES — Veracity IS one of the 5 Vs. Quiz 5 said it was NOT — that statement was FALSE.

What is Big Data?

Extremely large and complex datasets too large to be managed by traditional data-processing software. Includes structured, semi-structured, and unstructured data from social media, business transactions, IoT devices, etc.

What is a Conceptual Model?

A high-level description of data that is independent of any specific database technology.

What is a Conceptual Schema?

The overall logical structure of a database, independent of storage details.

What happens during the Conceptual Design phase?

A database team documents entities and relations — it is INDEPENDENT of any specific database system. (Quiz 3, Quiz 1 trap)

What is Logical Design?

The process of translating the conceptual data model into a relational database schema.

What is a Physical Model?

A description of how data is physically stored in the database, including table names, column names, data types, and constraints.

What is Logical Independence?

The ability to change the conceptual schema without having to change external schemas or application programs.

What is Chen Notation?

A graphical representation style for ERDs where entities = rectangles and relationships = diamonds.

What is Crow's Foot Notation?

A graphical representation style in ERDs that uses crow's foot symbols to show the 'many' side of a relationship.

What is an ERD?

Entity Relationship Diagram — a graphical representation of a complex real-world data structure. Used during the database design phase of the Database Life Cycle. (Exam 1)

In an ER diagram, what does a named oval represent?

An ATTRIBUTE — e.g., 'AlbumTitle' shown as an oval attached to an entity. (Quiz 1)

What are the 4 steps to develop an ERD? (ALL are necessary — Exam 1)

1. Identify business rules from the description of operations 2. Identify main entities and relationships from business rules 3. Identify attributes and primary keys 4. Develop the initial ERD, review and revise as needed

In SQL, what does an entity typically become?

A TABLE. (Quiz 3)

What is Cardinality?

Expresses the specific number of entity occurrences associated with an occurrence of a related entity. (Exam 1)

What is a 1:1 relationship? Give an example.

One-to-One — one instance of an entity relates to only one instance of another. Example: A department is chaired by one professor; a professor may chair only one department.

What is a 1:M relationship? Give an example.

One-to-Many — one instance can relate to multiple instances of another. Example: One teacher can teach many classes (NOT many teachers teach one class).

What is an M:N relationship? Give an example.

Many-to-Many — each student can take many classes AND each class can have many students. Requires a composite/bridge entity to implement.

What is a Unary (Recursive) Relationship? Give an example.

One entity relates to itself. Example: An employee manages other employees. (Exam 1)

What is a Binary Relationship?

A relationship between TWO distinct entities. The most common type. (Exam 1)

What is a Ternary Relationship? Give an example.

THREE entities take part in a relationship. Example: A doctor writes prescriptions; a patient receives prescriptions. (Exam 1)

What is a Business Rule?

A brief, precise, and unambiguous description of a policy, procedure, or principle within a specific organization. (Exam 1)

What is an Entity?

A person, place, thing, or event about which data is collected and stored.

What does the term 'Attribute' refer to in a table?

A COLUMN of a table — a characteristic or property of an entity. (Exam 1)

What is a Tuple?

A row in a relational database table — also called a record.

What is DDL?

Data Definition Language — SQL commands used to define and modify the database schema: CREATE, ALTER, DROP.

What is DML?

Data Manipulation Language — SQL commands used for managing data: SELECT, INSERT, UPDATE, DELETE.

What is a Super Key?

A set of attributes that uniquely identifies a tuple in a relation.

What is a Candidate Key?

A minimal superkey — a column or set of columns that can uniquely identify any row without including extraneous columns.

What is a Primary Key (PK)?

The candidate key chosen to uniquely identify each row in a table. CANNOT be null, must be unique. (Quiz 2, Exam 1)

What is a Composite Key?

A candidate key composed of two or more attributes. Example: (RoomNumber, DateOfStay) for reservations. (Exam 1)

What is a Foreign Key (FK)?

A field in one table that references the PK of another table, establishing a link between them. (Exam 1)

Can a table have no foreign key?

YES — a table may or may not have a foreign key. (Quiz 2)

Can a foreign key be NULL?

YES — a FK can be NULL. It just cannot reference a non-existent PK value.

What is Entity Integrity?

The rule that a primary key CANNOT contain null values. (Exam 1)

What is Referential Integrity?

A dependent table's FK must have either a null entry OR a matching entry in the related table. Example: each JOB_CODE in EMPLOYEE must point to an existing JOB_CODE in JOB. (Exam 1)

What is a Composite Entity (Bridge Table)?

Used to break down an M:N relationship into two 1:M relationships. Holds the FKs of both related entities as a composite PK. Example: BENEFIT table with EMP_CODE and PLAN_CODE. (Exam 1)

What does NULL mean?

MISSING DATA — NOT zero, NOT empty string. Use IS NULL / IS NOT NULL to check for it. (Quiz 2, Quiz 3)

In the EMPLOYEE/JOB/BENEFIT/PLAN model, which table has a FK?

EMPLOYEE has JOB_CODE as a FK. BENEFIT has EMP_CODE and PLAN_CODE as FKs. JOB and PLAN are parent tables with NO foreign keys. (Exam 1 trap!)

What is Relational Algebra?

The theoretical basis for relational databases — a procedural query language that takes instances of relations as input and yields relations as output.

What does SELECT (σ) do in relational algebra?

Filters ROWS based on a condition.

What does PROJECT (π) do in relational algebra?

Selects a subset of COLUMNS from a relation.

What is the PRODUCT (Cartesian Product) in relational algebra?

Combines every row of one relation with every row of another. Table A (5 rows) × Table B (6 rows) = 30 rows. (Quiz 2)

What is UNION in relational algebra?

Combines result sets of two SELECT statements, removing duplicate rows.

What is INTERSECT in relational algebra?

Returns only the common rows found in both tables.

What is DIFFERENCE in relational algebra?

Returns rows in the first relation but not the second.

What is DIVIDE in relational algebra?

Finds tuples in one relation associated with ALL tuples in another relation.

What is an Inner Join?

Returns only the rows where there is a match in BOTH tables.

What is an Equijoin?

A join based on equality between corresponding columns.

What is a Natural Join?

An equijoin using columns with the same name and data type; duplicate columns are removed.

What is a Left Outer Join?

Returns all rows from the LEFT table plus matching rows from the right (NULLs for right where no match exists).

What is a Right Outer Join?

Returns all rows from the RIGHT table plus matching rows from the left (NULLs for left where no match exists).

What is a Self-Join?

A join where a table is joined to itself to compare rows within the same table.

What is a Cross-Join?

Produces the Cartesian product of two tables — every row from table 1 × every row from table 2.

What is a Theta Join?

A join based on a condition other than equality (>,

What is Functional Dependence?

A → B means the value of A determines the value of B.

What is an Attribute Domain?

The set of all possible values an attribute can take.

What is the correct SQL clause order?

SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY

What does SELECT do, and does it alter data?

SELECT specifies the columns to retrieve. It does NOT alter any database data. (Quiz 1, Exam 1)

What does WHERE do in SQL?

Filters records based on specified conditions (applies to rows).

What does GROUP BY do in SQL?

Groups rows that have the same values into summary rows — used with aggregate functions.

What does HAVING do in SQL?

Filters GROUPS — like WHERE but applied AFTER GROUP BY.

What does ORDER BY do in SQL?

Sorts the result set in ascending (ASC) or descending (DESC) order.

What does DISTINCT do in SQL?

Returns only unique values in the result set.

What does BETWEEN do in SQL?

Selects values within a given range (INCLUSIVE on both ends).

What does LIKE do in SQL?

Searches for a specified pattern in a column. Use % as a wildcard.

What does IS NULL do in SQL?

Checks for null values in a column — the correct way to test for NULL.

What does EXISTS do in SQL?

Returns true if a subquery returns any rows.

What SQL data type stores whole numbers?

INT (or SMALLINT for small integers)

What SQL data type stores fractional/decimal values like prices?

DECIMAL(x, y) — NOT INT. (Quiz 1)

What does VARCHAR(n) store, and what does n mean?

Variable-length string. The (n) is the MAXIMUM number of characters allowed. (Quiz 1)

What is CHAR(n)?

Fixed-length string data type.

What does AUTO_INCREMENT do?

Automatically generates a unique, sequential integer for the primary key. (Exam 1, Quiz 3)

Correct syntax for NOT NULL on ProductName VARCHAR(50)?

VARCHAR(50) NOT NULL — constraint comes AFTER the data type. (Exam 1)

What are the aggregate functions in SQL?

SUM (total), AVG (average), COUNT (number of rows), MAX (maximum), MIN (minimum)