Database Development Systems Flashcards

Question 1

What is a database management system, and what does it do?

Accepted Answer

A database management system, or DBMS, is software that lets users and applications create, store, retrieve, update, and control access to data. It handles low-level storage details, enforces data rules, manages many simultaneous users, and provides tools for backup and recovery, so developers can focus on the application rather than file handling.

Question 2

What is the difference between data and information?

Accepted Answer

Data is raw, unprocessed facts or values, such as a list of order amounts with no context. Information is data that has been organized, interpreted, or summarized so it answers a question, such as total regional sales. Databases store data, and applications turn that data into useful information for people to act on.

Question 3

What is data independence, and why is it valuable?

Accepted Answer

Data independence is the ability to change how data is stored or organized without rewriting the applications that use it. Physical independence lets administrators change storage structures or file placement, while logical independence lets designers change the schema without breaking existing programs. It reduces maintenance cost and makes systems easier to evolve over time.

Question 4

What are the main components of a database system?

Accepted Answer

A database system typically includes the data itself, the DBMS software that manages it, the metadata or data dictionary that describes its structure, the applications and users that access it, and the hardware on which it runs. Together these parts let people store and retrieve information reliably.

Question 5

What is a data dictionary, and what information does it hold?

Accepted Answer

A data dictionary, also called a system catalog or metadata repository, is a central store of information about the database itself. It records table and column names, data types, constraints, relationships, index definitions, and permissions. The DBMS consults it when processing queries, so it is essential for consistency.

Question 6

What is the difference between a database and a database management system?

Accepted Answer

A database is the organized collection of related data itself, while a database management system is the software that creates, controls, and accesses that collection. People often use the word database loosely to mean both, but the DBMS is the program, and the database is the stored content it manages.

Question 7

Why did database systems replace earlier file-based approaches to storing data?

Accepted Answer

File-based systems stored data in separate files for each application, which caused duplicated data, inconsistent updates, and difficulty sharing information. Database systems centralize data, control redundancy, enforce integrity rules, and allow many users to work with shared data safely. This shift made applications simpler to build and the data more trustworthy.

Question 8

What is a database schema, and how does it differ from the data?

Accepted Answer

A schema is the formal description of a database's structure, including its tables, fields, data types, keys, and relationships. It is relatively stable and defined by design, while the data is the content that changes constantly as records are added, edited, and deleted. Think of the schema as a blueprint.

Question 9

What is data modeling, and why do developers perform it?

Accepted Answer

Data modeling is the process of describing the information a system needs, the entities involved, the attributes they carry, and the relationships among them. Developers perform it before building tables so they can agree on requirements, spot gaps early, and reduce costly redesign later. The model is a shared map for everyone involved.

Question 10

What is an entity in an entity-relationship model?

Accepted Answer

An entity is a distinct object or concept about which the system stores information, such as a customer, a product, or an order. Each entity is typically represented as a table in a relational database, and each individual occurrence of it is called an instance, such as one specific customer record.

Question 11

What is an attribute, and how does it relate to an entity?

Accepted Answer

An attribute is a named property that describes an entity, such as a customer's name, email address, or birth month. Attributes become columns when the entity is implemented as a table, and each instance supplies a value for each attribute. Attributes can be simple, composite, derived, or multivalued, depending on their structure.

Question 12

What is a relationship in an entity-relationship diagram?

Accepted Answer

A relationship is an association between two or more entities, such as a customer placing an order or an employee managing a department. Relationships are often implemented with foreign keys that point to a related row. They describe how entities depend on or connect to one another.

Question 13

What is cardinality, and what do one-to-one, one-to-many, and many-to-many mean?

Accepted Answer

Cardinality states how many instances of one entity can be associated with instances of another. A one-to-one relationship links a single instance on each side. A one-to-many relationship links one instance to several, such as a customer and many orders. A many-to-many relationship allows several on both sides, such as students and courses.

Question 14

What is a primary key, and what properties must it have?

Accepted Answer

A primary key is the column or set of columns whose values uniquely identify each row in a table. It must never be null, and its value must not repeat across rows. Good primary keys are also stable, meaning they rarely or never change, which keeps references from other tables reliable over time.

Question 15

How do a foreign key and a primary key work together?

Accepted Answer

A foreign key is a column in one table whose values must match primary key values in another table, or be null where allowed. This link enforces referential integrity, so a row cannot point to a customer that does not exist. The DBMS records and checks these relationships.

Question 16

How should a developer check an entity-relationship design before building tables?

Accepted Answer

A developer should review entity names, attribute lists, key choices, and cardinality with domain experts, checking that each real business rule appears in the diagram. Walking through sample scenarios reveals missing entities or wrong relationships. Fixing errors at this stage is far cheaper than changing tables after data already exists.

Question 17

What is the relational model, and what are its basic building blocks?

Accepted Answer

The relational model organizes data into relations, which are tables made of rows and named columns. Each column has a domain of allowed values, each row represents one tuple of related facts, and table order carries no meaning. Relations are linked through shared values such as keys rather than physical pointers.

Question 18

What is a functional dependency, and why does it matter?

Accepted Answer

A functional dependency is a constraint stating that the value of one attribute or set of attributes determines the value of another within a table. For example, if each employee ID has exactly one department name, then employee ID determines department name. Recognizing these dependencies reveals which columns belong together and guides normalization.

Question 19

What is a candidate key, and how does it relate to a primary key?

Accepted Answer

A candidate key is any minimal set of attributes that uniquely identifies each row in a table. A table can have several candidate keys, and the designer chooses one of them to serve as the primary key. The others become alternate keys, still enforcing uniqueness.

Question 20

What is first normal form, and what rule does it impose?

Accepted Answer

First normal form requires that every column hold atomic values, meaning single indivisible values rather than lists or repeating groups. It also requires each row to be unique, usually through a key. A table with a column listing several phone numbers for one person must be restructured to satisfy this form.

Question 21

What is a partial dependency, and which normal form removes it?

Accepted Answer

A partial dependency exists when a non-key attribute depends on only part of a composite primary key rather than on the whole key. Second normal form removes these by moving the dependent attributes into their own table, keyed by the part they depend on. For example, a course title belongs in a course table.

Question 22

What is third normal form, and what kind of dependency does it eliminate?

Accepted Answer

Third normal form requires that every non-key attribute depend directly on the primary key, with no non-key attribute depending on another non-key attribute. This eliminates transitive dependencies, where a value depends on the key only through a second non-key column. Splitting such columns into a separate table prevents update anomalies.

Question 23

What are modification anomalies, and why are they a problem?

Accepted Answer

Modification anomalies are problems that arise when redundant data is inserted, changed, or deleted inconsistently. Insertion anomalies block recording facts without unrelated data, deletion anomalies remove facts by accident, and update anomalies leave conflicting copies of one fact. Normalization reduces these risks by storing each fact once.

Question 24

Why might a designer deliberately denormalize a table?

Accepted Answer

A designer may denormalize to reduce the number of joins needed for frequent reads, which can speed up reporting or read-heavy applications. The trade-off is extra redundancy and a greater risk of inconsistent updates, so the decision should be based on measured performance needs and documented with the rules that keep copies synchronized.

Question 25

What is SQL, and what are its main sublanguages?

Accepted Answer

SQL, or Structured Query Language, is the standard language for defining, manipulating, and controlling data in relational databases. Its main parts are the data definition language for creating and altering structures, the data manipulation language for inserting, updating, deleting, and querying rows, and the data control language for granting or revoking permissions.

Question 26

How does a basic SELECT statement retrieve data?

Accepted Answer

A basic SELECT statement names the columns to return with the SELECT clause and the source table with the FROM clause. An optional WHERE clause filters rows by a condition, and the DBMS returns a result set. For example, SELECT name FROM customers WHERE city = 'Austin'; returns names of customers in that city.

Question 27

What is the difference between WHERE and HAVING in a query?

Accepted Answer

WHERE filters individual rows before any grouping or aggregation takes place, based on column values. HAVING filters groups after aggregation, so it can test results such as a count or sum. For example, WHERE can exclude canceled orders, while HAVING can keep only customers whose order total exceeds a set amount.

Question 28

What is a join, and what are the common types of joins?

Accepted Answer

A join combines rows from two or more tables based on a matching condition. An inner join returns only rows with matches on both sides, a left outer join keeps all rows from the left table, and a right outer join does the reverse. A full outer join keeps unmatched rows from both sides.

Question 29

What does GROUP BY do, and which aggregate functions are commonly used with it?

Accepted Answer

GROUP BY arranges rows that share the same values in specified columns into groups, so that aggregate calculations can be performed for each group. Common aggregate functions include COUNT, SUM, AVG, MIN, and MAX. For example, grouping orders by customer and applying SUM gives each customer's total spending.

Question 30

What is a subquery, and when might you use one?

Accepted Answer

A subquery is a query nested inside another query, used to supply a value, a list, or a temporary table for the outer statement. You might use one to find rows whose values exceed an average, or to check membership in a set returned by another query.

Question 31

What is the difference between DELETE, TRUNCATE, and DROP?

Accepted Answer

DELETE removes selected rows, can use a WHERE condition, and can be rolled back within a transaction in most systems. TRUNCATE removes all rows from a table quickly, usually with less logging and without row-by-row conditions. DROP removes the table's structure and data entirely, so the table no longer exists.

Question 32

What is the purpose of a view in SQL?

Accepted Answer

A view is a named query whose results can be referenced like a table, without storing a separate copy of the data. Views simplify complex queries, hide sensitive columns from certain users, and provide a stable interface when underlying tables change. Ordinary views reflect current data each time they are queried.

Question 33

What does the acronym ACID stand for in transaction processing?

Accepted Answer

ACID stands for atomicity, consistency, isolation, and durability, the four properties that reliable transactions should preserve. Atomicity means all steps succeed or none do, consistency means rules are kept intact, isolation means concurrent transactions do not interfere unexpectedly, and durability means committed changes survive failures.

Question 34

What is a transaction, and how do COMMIT and ROLLBACK relate to it?

Accepted Answer

A transaction is a sequence of database operations treated as a single logical unit of work. COMMIT makes all of its changes permanent and visible, while ROLLBACK undoes every change made since the transaction began. Applications use transactions so that multi-step operations either fully happen or leave no partial effects.

Question 35

What is an index, and what trade-offs does it introduce?

Accepted Answer

An index is a separate data structure, often a B-tree, that lets the DBMS locate rows matching a condition without scanning the entire table. Indexes speed up searches, joins, and sorting on indexed columns. The trade-off is extra storage and slower inserts, updates, and deletes, because each index must also be maintained.

Question 36

What are constraints, and which common types help protect data integrity?

Accepted Answer

Constraints are rules the DBMS enforces on data, rejecting any change that would violate them. Common types include NOT NULL, which requires a value; UNIQUE, which prevents duplicates; CHECK, which tests a condition; and foreign key constraints, which require matching references. Enforcing rules in the database protects data even when application code has bugs.

Question 37

How does role-based access control limit what users can do in a database?

Accepted Answer

Role-based access control assigns privileges to roles, such as reporting or data entry, and then grants roles to users. Each role receives only the permissions its duties require, such as reading certain tables but not changing them. This principle of least privilege reduces the damage from mistakes and compromised accounts.

Question 38

What is a backup and recovery plan, and what should it cover?

Accepted Answer

A backup and recovery plan describes how data is copied, where copies are stored, and how the database is restored after failure. It should cover full and incremental backups, off-site or separate storage, and documented restore procedures tested before they are needed. An untested backup may fail exactly when it is required.

Question 39

What is a deadlock in a database, and how is it usually resolved?

Accepted Answer

A deadlock occurs when two or more transactions each hold a lock that the other needs, so none can proceed. Many DBMS products detect deadlocks automatically and resolve them by rolling back one transaction, which the application can then retry. Keeping transactions short and accessing tables in a consistent order helps prevent them.

Question 40

Why do developers use database migration scripts for schema changes?

Accepted Answer

Migration scripts record each schema change as versioned, repeatable steps, so every environment can be brought to the same structure. They let teams review changes in version control, test them before release, and apply them in the same order across development, testing, and production systems. Writing changes as code reduces manual errors.

Database Development Systems Flashcards

1Core database concepts

2Data modeling and ER design

3Relational theory and normalization

4SQL querying fundamentals

5Applied development practices

Learning Resources