BMGT402 Database Systems - Comprehensive Study Guide

255 practice questions covering every section of the study guide in lecture order: course intro, database overview, data models, Visio, relational model, database analysis, ER modeling, design considerations, normalization, and worked problems.

What is a zettabyte, and when did we enter the Zettabyte Era (per Cisco)?

10^21 bytes; 2016. The yottabyte (10^24 bytes) is expected in the 2030s.

Data vs. information?

Data = raw facts (phone number, birth date). Information = the result of processing raw data to reveal meaning; it requires context.

What is the hierarchy from data upward?

Data → information → knowledge.

What three qualities make information useful for decision making?

Accurate, relevant, and timely.

Who proposed the relational model, and when?

E.F. Codd (IBM), 1969–1970.

What three weaknesses of file-based systems do databases fix?

Redundancy, anomalies, and structural/data dependence.

Define field, record, and file.

Field: characters with a specific meaning. Record: logically connected set of fields describing a person/place/thing. File: a collection of related records.

List the 5 limitations of file-based systems.

1) Lengthy development times 2) Difficulty getting quick (ad hoc) answers 3) Lack of security and limited data sharing 4) Duplication of data / islands of information 5) Data dependence.

What are 'islands of information'?

The same data held separately by different programs/departments, wasting space and possibly holding different values or formats.

Structural dependence vs. structural independence?

Dependence: access to a file depends on its structure, so every program must change when the structure changes. Independence: structure can change without affecting the application's access.

Data dependence vs. data independence?

Dependence: data access changes when storage characteristics (e.g., data type or size) change. Independence: storage characteristics can change without affecting program access.

Define data redundancy and its consequences.

The same data stored unnecessarily in different places. Causes poor security, inconsistency, data-entry errors, and integrity problems.

What is a data anomaly?

An error that occurs when not all required changes to redundant data are made successfully.

Agent Leah's name is changed in one record but not others. Which anomaly?

Update anomaly.

Leah is fired and her row is deleted, and customer Alfred (stored only in that row) is lost. Which anomaly?

Deletion anomaly.

Adding a new agent requires creating a 'dummy' customer. Which anomaly?

Insertion anomaly.

What is a database?

A shared, integrated computer structure that stores end-user data (raw facts) and metadata (data about data).

What is metadata, and what does it allow?

Data about data (the system catalog); describes data characteristics and relationships and enables program-data independence.

Ubiquitous vs. pervasive data?

Ubiquitous: abundant, global, everywhere. Pervasive: inescapable, prevalent, persistent.

Classify databases by number of users.

Single-user (one at a time) vs. multiuser: workgroup (small group/one department) or enterprise (many users across departments).

Classify databases by location.

Centralized (one site), distributed (across sites), cloud (built on cloud data services).

Classify databases by time sensitivity.

Operational (transactional/production; daily operations) vs. data warehouse (optimized for decision support).

Classify data by structure.

Unstructured (raw original state), structured (formatted for processing), semistructured (processed to some extent).

What do OLAP tools do?

Online analytical processing tools retrieve, process, and model data in a data warehouse.

What is business intelligence?

Capturing and processing business data to generate information for decision making.

What does NoSQL stand for, and what is it for?

'Not only SQL'; databases that handle large amounts of structured and unstructured data.

What is a DBMS?

A collection of programs that manages the database structure and controls access to data; the intermediary between users and the database.

Name the 5 components of the database system environment.

Hardware, software, people, procedures, data.

List the 9 DBMS functions.

Data dictionary mgmt; data storage mgmt; data transformation & presentation; security mgmt; multiuser access control; backup & recovery mgmt; data integrity mgmt; database access languages & APIs; database communication interfaces.

Which DBMS function stores definitions of data elements and their relationships?

Data dictionary management.

Which DBMS function minimizes redundancy and maximizes consistency?

Data integrity management.

Which DBMS function lets many users access data concurrently without compromising integrity?

Multiuser access control.

Which DBMS function formats data to conform to logical expectations?

Data transformation and presentation.

Which DBMS function accepts end-user requests over different network environments?

Database communication interfaces.

What does it mean that a query language specifies 'what, not how'? Which is the standard?

You state what data you want, not how to retrieve it; SQL is the de facto standard.

Name 5 disadvantages of database systems.

Increased costs, management complexity, maintaining currency (updates/patches/training), vendor dependence, frequent upgrade/replacement cycles.

What does poor database design produce?

Redundancy and anomalies.

Generative AI vs. machine learning?

Generative AI is trained on data and generates new data. Machine learning uses statistical techniques to find patterns/trends to forecast the future.

Name 3 challenges of AI.

Ethics and data privacy, inherited bias, resource utilization.

Which database job manages and maintains the DBMS and databases?

Database Administrator (DBA).

Which database job designs environments at the conceptual, logical, and physical levels?

Database Architect.

Which database job develops databases for decision-support reporting?

Database Analyst.

Which database job analyzes large, varied data for insights and predictions?

Data Scientist.

What is a data model?

A simple, often graphical representation of complex real-world data structures (an abstraction). Data modeling is iterative and progressive.

What 3 things does an implementation-ready data model contain?

1) A description of the data structure 2) Enforceable rules to guarantee integrity 3) A data manipulation methodology.

What are the four building blocks of data models?

Entity, attribute, relationship, constraint.

Define an entity.

A person, place, thing, idea, or event about which data is collected and stored; should represent one 'theme'.

Define a constraint.

A restriction placed on data to ensure data integrity.

A customer may have many cars; each car belongs to one customer. Relationship type?

CUSTOMER 1:M CAR.

A student takes many classes; each class has many students. Relationship type?

STUDENT M:N CLASS.

Each department is managed by one employee; each manager manages only one department. Relationship type?

EMPLOYEE 1:1 DEPARTMENT.

What is a business rule?

A brief, precise, unambiguous description of a policy, procedure, or principle, written in non-technical business language.

Name sources of business rules.

Executives, managers, policy makers, written documentation, end-user interviews.

When translating business rules, nouns become ___ and verbs become ___.

Nouns → entities; verbs → relationships.

How do you determine a relationship's type from business rules?

Ask both directions: how many B per one A, and how many A per one B (e.g., many invoices per customer + one customer per invoice = 1:M).

What are the naming conventions for entities and attributes?

Entities: descriptive, familiar, singular nouns. Attributes: descriptive (CUSTOMER_NAME not C_NAME), atomic parts, every record has a unique identifier.

Attribute name length limits in SQL Server and MySQL?

SQL Server 128 characters; MySQL 64.

PAINTER exhibits at many GALLERYs; a gallery shows many painters. Relationship?

M:N.

Order the data model generations (1st–6th).

1) File system 2) Hierarchical & network 3) Relational 4) Object-oriented / object-relational 5) XML hybrid 6) NoSQL.

Which was the first data model, and what structure did it use?

Hierarchical (1960s) — an upside-down tree where each child has one parent.

Disadvantages of the hierarchical model?

Complex to implement, hard to manage, lacks structural independence, no standards.

How does the network model differ from hierarchical, and what concepts did it give us?

A record can have more than one parent. Gave us schema/subschema, DML, and DDL.

Disadvantages of the network model?

Cumbersome, structural dependence, no ad hoc queries (programmers had to code reports).

What is the relational model based on?

The set-theory concept of a relation (table): a matrix of row/column intersections.

What is the RDBMS's biggest advantage?

It hides the model's complexity — users see a collection of tables.

What are the three parts of a SQL-based application?

End-user interface, collection of tables, and the SQL engine.

What does the ER model add to the relational model?

Better modeling capability through the ERD (graphical entities, attributes, relationships). It's the accepted standard for data modeling.

Name the three ERD notations and where each is used.

Chen (original), Crow's Foot (most used in industry), UML (object-oriented / software engineering).

Which data model is called 'semantic'?

Object-oriented — data and relationships live in one self-contained object.

What is the extended relational data model (ERDM)?

Relational model plus OO features like inheritance; also called O/R DBMS; geared to business applications.

What does XML handle?

Structured, semistructured, and unstructured data; a standard for data exchange among systems and Internet services.

What is the Internet of Things (IoT)?

The web of Internet-connected devices that exchange and collect data.

What are the 3 Vs of Big Data?

Volume (amount), Velocity (speed of growth/processing), Variety (multiple formats).

What did Apache Hadoop introduce?

HDFS (distributed storage) and MapReduce (parallel processing).

Name the 3 NoSQL types with an example each.

Key-value (shopping cart sessions — Redis, DynamoDB); Document (customer profile — MongoDB); Graph (social networks — Neo4j).

A social network's friend recommendations would best use which NoSQL type?

Graph database (e.g., Neo4j).

Order the cloud data pipeline stages.

Storage (S3, Azure Data Lake) → distributed processing (Spark, Databricks) → data lakes/lakehouses (Iceberg) → AI analytics (TensorFlow, PyTorch, Azure ML).

What are the four degrees of data abstraction (from most abstract)?

External, conceptual, internal, physical (ANSI/SPARC defined the first three).

Which abstraction level is the end users' view of one business unit?

External.

Which abstraction level is the global, organization-wide view?

Conceptual — hardware and software independent.

Which abstraction level is software dependent but hardware independent?

Internal (the DBMS's view; written in SQL for relational).

Which abstraction level is both hardware and software dependent?

Physical — how data is saved on storage media.

Logical independence vs. physical independence?

Logical: changing the internal model doesn't affect the conceptual model. Physical: changing the physical model (e.g., new storage device) doesn't affect the internal model.

Which Visio edition is required for database diagrams?

Visio Professional (not Standard).

What are the four diagram types drawn in Visio for this course?

Conceptual model, relational diagram, Crow's Foot ERD, dependency diagram.

In a Visio conceptual model, how are relationships labeled?

With a verb read from the 'one' side to the 'many' side.

In Visio, what are the Begin and End symbols?

Begin = the 'many' side; End = the 'one' side. Drag from the 'many' entity to the 'one' entity.

Which arrow types are used for a Visio conceptual model?

Always one of 25, 28, 29, or 30 (arrow size Jumbo).

How do you set a foreign key in Visio?

Click the entity edge, click the attribute, right-click → Set Foreign Key ('FK' appears).

In Visio, what does checking 'Set Identifying' do?

Makes the relationship strong (identifying) — solid line. Unchecked = weak (non-identifying), dashed line.

In a dependency diagram, where are good vs. bad dependencies drawn?

Full (good) dependencies above the attributes; partial and transitive (bad) dependencies below.

CUSTOMER 1:M INVOICE: which table gets the foreign key?

INVOICE (the 'many' side) gets CUSTOMER_ID as a FK.

Define relation, tuple, attribute, and domain.

Relation: table of rows/columns. Tuple: a row. Attribute: a named column. Domain: set of allowable values for an attribute.

Degree vs. cardinality of a relation?

Degree = number of attributes (columns). Cardinality = number of tuples (rows).

A relation has 7 columns and 120 rows. Degree? Cardinality?

Degree 7; cardinality 120.

Give the alternative terms for relation, tuple, and attribute.

Relation = table = file; tuple = row = record; attribute = column = field.

List the 8 characteristics of a relational table.

2-D rows/columns; each row = one entity occurrence; distinct column names; single value per intersection; same data type per column; attribute domain per column; row/column order immaterial; a unique identifier for each row.

Define functional dependence.

B is functionally dependent on A if each value of A determines exactly one value of B (A → B). A = determinant, B = dependent.

Hours Completed → Classification: which is the determinant?

Hours Completed is the determinant; Classification is the dependent.

Define superkey.

Any attribute(s) that uniquely identifies each row.