Comprehensive Guide to Database Systems and Data Modeling

What is data?

Raw facts that haven't been processed yet.

What is information?

Data that has been processed to become useful and meaningful.

What is knowledge?

A whole body of information and facts built up about one topic.

What is the relationship between data, information, knowledge, and wisdom?

Data > Information > Knowledge > Wisdom.

Why do we need databases?

To manage the vast volume and variety of data generated constantly.

What is a database?

A shared, organized computer structure that stores related data together.

What are end-user data and metadata?

End-user data are the actual raw facts; metadata describes each piece of data's name, type, and constraints.

What is a Database Management System (DBMS)?

Software that manages the database and controls access to it.

List one advantage of using a DBMS.

Improved data sharing, security, integration, or access.

What is a single-user database?

A database that only one person can use at a time.

What is a multiuser database?

A database that supports many users accessing it simultaneously.

What is a centralized database?

A database stored in a single physical location.

What is unstructured data?

Data in its original, raw form, such as voice recordings or handwritten essays.

What is structured data?

Data that has been formatted or organized for easy storage and retrieval.

What is database design?

The activity of planning the structure of a database to manage data properly.

What is data redundancy?

When the same piece of data is stored unnecessarily in multiple places.

What is a data anomaly?

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

What is an update anomaly?

Occurs when the same data is repeated in many records, requiring multiple updates.

What is an insertion anomaly?

Happens when you can't add new data without also adding unrelated 'dummy' data.

What is a deletion anomaly?

Occurs when deleting one record inadvertently deletes other necessary data.

What are the five components of a database environment?

Hardware, Software, People, Procedure, Data.

What is data dictionary management?

The DBMS function that keeps track of definitions of all data.

What is data storage management?

The DBMS function that organizes how and where data is physically stored.

Give an example of a discipline-specific database.

A hospital's database that only stores patient medical records.

What is operational data usage?

Data that supports daily, real-time operations.

What is analytical data usage?

Data that stores historical data and business metrics for decision-making.

What is a cloud database?

A database created and maintained using cloud services, not physically present.

What is a workgroup database?

A multiuser database used by a specific small group, usually under 50 users.

What is an enterprise database?

A database that represents an entire organization's data, built for current and future needs.

What does data transformation and presentation involve?

Converting raw data into a usable format for display.

What is the purpose of security management in databases?

Controlling who can view or edit data.

What is multiuser access control?

Allowing many people to use the database safely at once.

What does backup and recovery management protect against?

Protecting data from loss.

What is data integrity management?

Keeping data accurate and consistent.

What are database access languages and APIs?

Languages like SQL that allow programs and users to communicate with the database.

What is the difference between a database and a spreadsheet?

Databases have built-in metadata, enforce data types, define relationships, and maintain data consistency, unlike spreadsheets.

What are some disadvantages of a DBMS?

Increased costs, management complexity, need for constant updates, vendor dependence, and frequent upgrade cycles.

What is the role of a Database Developer?

Builds and maintains database-based applications.

What skills are needed for a Database Designer?

Systems design, database design, and SQL.

What does a Database Administrator do?

Manages and maintains the DBMS and databases on a day-to-day basis.

What is the function of a Database Analyst?

Builds databases for decision-support reporting.

What does a Database Architect design?

Designs and implements database environments (conceptual, logical, physical).

What is the responsibility of a Database Security Officer?

Implements security policies for data administration.

What is a Cloud Computing Data Architect responsible for?

Builds infrastructure for next-generation cloud databases.

What is metadata?

Data about data; it describes characteristics and relationships of data elements.

What are the four classifications of databases?

By number of users, location, type of data stored, and data usage.

What are data anomalies?

Issues that arise when changes to redundant data are not made consistently.

What are the five components of a database system?

Hardware, software, people, procedures, and data.

What is the first step in designing a database?

Data modeling, which involves creating a data model for a specific problem.

What is an entity in database terminology?

A person, place, thing, or event that data can be stored about.

What is an attribute in the context of databases?

A characteristic of an entity, which has a name and a data type.

What is a relationship in databases?

An association between entities that defines how they connect.

What is a one-to-many relationship?

One record in Entity A can relate to many records in Entity B, but each B relates back to only one A.

What is a many-to-many relationship?

Many records in Entity A can relate to many records in Entity B, and vice versa.

What is a many-to-many relationship in databases?

A relationship where many records in one table can relate to many records in another table, such as STUDENTs enrolling in multiple CLASSes.

What defines a one-to-one relationship?

A relationship where one record in table A relates to exactly one record in table B, like an EMPLOYEE managing one STORE.

What is a constraint in a database?

A restriction placed on data, such as limiting a class to 35 students.

What is a business rule?

A description of a policy or principle within an organization that defines the entities and relationships needed in a database.

How do nouns and verbs function in business rules?

Nouns typically become entities, while verbs represent relationships between those entities.

What is the relational model?

A data model developed by E. F. Codd that represents data as independent tables (relations) connected by common columns.

What is a table in the context of a relational database?

A two-dimensional structure of rows (entities) and columns (attributes) representing a set of data.

What is a tuple?

A single row in a table within a relational database.

What does RDBMS stand for?

Relational Database Management System, software that manages relational databases.

How do tables link together in a relational database?

Tables share a common column (attribute) to connect their data.

What is an Entity Relationship (ER) Model?

A data model that describes relationships between entities at a conceptual level using ER diagrams.

What is an Entity Relationship Diagram (ERD)?

A diagram that visually represents an ER model's entities, attributes, and relationships.

How is an entity represented in an ERD?

As a rectangle with the entity's name in capital letters, singular form.

What are the three common notations for ER diagrams?

Chen notation, Crow's Foot notation, and UML class diagram notation.

What does the Crow's Foot notation indicate?

Entities are shown in rectangles connected by lines, with 'many' represented by a crow's-foot symbol.

What are the four degrees of abstraction in database design?

External, Conceptual, Internal, and Physical.

What is the external model/schema?

The programmer's view of a subset of data focused on specific business needs.

What is the conceptual model/schema?

The global view of the database that combines all external views into one model.

What is the internal model/schema?

The conceptual model adapted to fit a specific DBMS.

What is the physical model in database design?

The lowest level that describes how and where data is stored, including location and format.

What does software independence mean in database design?

A model that does not depend on the software used to implement it.

What is logical independence?

The ability to change the internal model without affecting the conceptual model.

What is physical independence?

The ability to change the physical model without affecting the internal model.

What is the significance of business rules in ER design?

They define the entities and relationships necessary for the database structure.

What is the purpose of data modeling?

To plan a database's structure before building it, defining entities, attributes, and relationships.

What is a data model?

A relatively simple representation, usually graphical, of real world complexity.

Define 'entity' in data modeling.

An entity is a person, place, thing, or event about which data can be stored.

What are attributes?

Characteristics of an entity that have a name and data type.

What is a relationship in data modeling?

An association between entities that shows the connection/relevance of entity and attribute with each other.

List the types of relationships in data modeling.

One to one, many to many, and one to many.

What is a constraint in data modeling?

A restriction placed on data, expressed in the form of rules.

Define a business rule.

A description of a policy, procedure, or principle within an organization.

How does a noun in a business rule translate in data modeling?

A noun translates into an entity in the model.

What is a tuple in the context of a relational database?

A table row that represents one single occurrence within the entity set.

What is the purpose of a relational diagram?

To represent a relational database's entities, attributes, and relationships.

What does the entity relationship model describe?

It describes relationships among entities.

What is the external model in data abstraction?

The programmer's view of the data environment, working with a data subset of the global database schema.

What is the conceptual model?

A global view of the entire database that integrates all external views.

Define a primary key.

The candidate key chosen to uniquely identify each row in a table, which cannot contain nulls.

What is functional dependence?

Attribute B is functionally dependent on A if one value of A always points to exactly one value of B.

What is a foreign key?

An attribute in one table whose values must match a primary key in another table or be null.

What are integrity rules in a relational database?

Rules that ensure the accuracy and consistency of data within a relational database.

What is entity integrity?

Guarantees that every row has a unique primary key, and that key has no null values.

What is referential integrity?

A foreign key must either be null or match an existing primary key value in the related table.

What is the difference between a superkey and a candidate key?

A superkey is any attribute or combination that uniquely identifies each row, while a candidate key is a minimal superkey.

What is a composite key?

A key made up of more than one attribute.