COP 4703 Final Exam Review

What is the difference between a DBMS and RDBMS?

A DBMS is a software for managing databases, while an RDBMS is a type of DBMS that implements the relational model. All RDBMSs are DBMSs, but not all DBMSs are RDBMSs. An RDBMS provides the ability to establish relationships between multiple databases and defines the related fields.

What is a database management system (DBMS)?

A Database Management System (DBMS) is a software system that uses a standard method to store and organize data. The data can be added, updated, deleted, or retrieved as per the user requirements.

What is the difference between a left join and a right join in SQL?

A LEFT JOIN returns all the rows from the left table and the matching rows from the right table. If there is no match, the result is NULL on the right side. In contrast, a RIGHT JOIN returns all the rows from the right table and the matching rows from the left table. If there is no match, the result is NULL on the left side.

What is a clustered index in SQL?

A clustered index determines the physical order of data in a table. There can only be one clustered index per table, which generally improves query speed by closely related data proximity.

What is a composite key?

A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table. Uniqueness is only guaranteed when the columns are combined; when taken individually, the columns do not guarantee uniqueness.

What is a compound statement in SQL?

A compound statement is a collection of individual SQL statements executed as a unit, reducing network congestion.

What is a cursor in SQL?

A cursor in SQL is a database object that retrieves data from result sets one row at a time. A cursor is used when the data needs to be updated row by row.

What is a data dictionary, and why is it important?

A data dictionary is a collection of descriptions of the data objects or items in a data model for the benefit of programmers and others who need to refer to them. It's important because it maintains consistency of definitions and understanding.

What is a data warehouse?

A data warehouse is a large store of data collected from a wide range of sources within a company and used to guide management decisions.

What is a data warehouse?

A data warehouse is a large, read-only database used to support decision-making in a business context. It contains historical data from multiple sources.

What is the difference between a database and a data warehouse?

A database is used for recording daily transactions, while a data warehouse is structured to make reporting easier. A database is application-oriented, whereas a data warehouse is subject-oriented.

What is a database model?

A database model is the conceptual blueprint of a database design. There are several types of models, including the Entity-Relationship (E-R) Model, Object-Relationship (O-R) Model, and Extended Relational Model.

What is a schema in a database?

A database schema is the structure of a database system, described in a formal language. It represents the logical view of the entire database.

What is a database trigger?

A database trigger is a program that automatically executes in response to some event on a table or view such as insert, update or delete of records.

What is a database view?

A database view is a searchable object in a database that is defined by a query. Though it is similar to a real table, it does not store the data physically.

What is a deadlock in database management?

A deadlock is a situation where two or more transactions are unable to proceed because each holds a lock that the other needs.

What is a foreign key in SQL?

A foreign key is a column or a set of columns in a relational database table that provides a link between data in two tables. It acts as a cross-reference between tables because it references the primary key of another table.

What is a foreign key, and how does it enforce referential integrity?

A foreign key is a column or a set of columns that is used to establish a link between the data in two tables. It enforces referential integrity by restricting the actions that can be performed on linked tables.

What is a join operation in SQL?

A join operation combines rows from two or more tables, based on a related column between them. There are several types of SQL join: INNER, LEFT, RIGHT, and FULL.

What is a non-clustered index in SQL?

A non-clustered index doesn't sort the physical data inside the table. Instead, it creates a separate object within a database that references the original table's data, improving the performance of certain queries.

What is a period definition in a table?

A period definition in a table identifies a pair of columns capturing the period start and end time.

What is a primary key in a database?

A primary key is a special relational database table column designated to uniquely identify all table records. A primary key cannot have NULL values, and each table can have only one primary key.

What is the difference between a primary key and a unique key?

A primary key is used to ensure data in the specific column is unique and not null. In contrast, a unique key ensures data within the column is unique, but one null value is allowed.

What is a self-join in SQL?

A self join is a regular join where a table is joined to itself.

What is SQL subquery?

A subquery is a query inside another query where a query is defined to retrieve data or information back from the database. They are often used in the WHERE and FROM clauses of the SQL statement.

What is a transaction in the context of databases?

A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.

What is a transaction in a database?

A transaction is a unit of work that is treated as "a whole". It has to either be fully completed or fully failed whereupon the system reverts back to the state before the transaction started.

What is a trigger in SQL, and what are some examples of its usage?

A trigger in SQL is a set of instructions that are executed or 'triggered' automatically when a specific event occurs. For example, a trigger can be created to update a log table when a record is updated in a monitored table.

What is a unique key constraint in a database?

A unique key is a set of one or more columns of a table that uniquely identify a record in a database table. The UNIQUE constraint maintains data integrity by ensuring that no two rows of data in a table have identical key values.

What is a database view?

A view is a virtual table based on the result-set of an SQL statement. They are used to restrict data access, simplify complex queries, and provide an additional level of table abstraction.

What is SQL view, and what are its advantages?

A view is a virtual table based on the result-set of an SQL statement. Views can simplify query writing, provide additional security, and insulate application code from changes in the database.

What is a wildcard character in SQL, and how is it used?

A wildcard character in SQL can be used to substitute for any other characters in a string. The most commonly used wildcards are the percent sign (%) which represents zero, one, or multiple characters and the underscore (_) which represents a single character.

What is an ER diagram, and how can it be used?

An Entity-Relationship (ER) diagram is a visual representation of major entities within a system, along with their attributes and relationships. ER diagrams are used to model and design relational databases, in terms of logic and business rules.

What is an index in a database and how does it affect performance?

An index is a database object that improves the speed of data retrieval operations. However, they require additional storage and can slow down data-write operations due to the overhead of maintaining the indexes.

What is the difference between inner join and outer join in SQL?

An inner join returns only the intersecting rows between two tables, while an outer join returns all the rows from one table and the matched rows from another table. If there is no match, the result is NULL on the side of the table that does not have a match.

What is the purpose of database backup and recovery?

Backup and Recovery are critical for database management systems. Backup provides a copy of data that can be used for protection against data loss. Recovery is the process of restoring data to a consistent state after a failure or loss.

What is database concurrency control?

Concurrency control ensures that transactions that are executed concurrently don't lead to inconsistencies. There are several techniques for managing concurrency, such as lock-based protocols, timestamp-based protocols, and validation-based protocols.

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

DELETE is a logged operation that removes rows one at a time and can use a where clause. TRUNCATE is a fast, minimally logged operation that removes all data from a table. DROP is a command that completely removes a table from the database.

What is the difference between DELETE and TRUNCATE commands?

DELETE is a logged operation that removes rows one at a time and can where clause. TRUNCATE is a logged operation that quickly removes all data from a table by deallocating the data pages used by the table.

What does data integrity involve?

Data integrity involves ensuring accuracy and consistency of data in a database. There are three types: Entity, Domain, and Referential.

What is the role of data mining in a database?

Data mining is the practice of automatically searching large stores of data to discover patterns and trends that go beyond simple analysis. It's utilized in databases to extract useful information and for decision making.

What is data mining?

Data mining is the process of discovering patterns in large data sets involving methods at the intersection of machine learning, statistics, and database systems.

What is data modeling in NoSQL databases?

Data modeling in NoSQL databases involves designing the schema according to the database type (document, key-value, wide-column, graph) and how data access is required by the application.

What is data redundancy in a database?

Data redundancy involves having the same data repeated in multiple places. While some redundancy is necessary for data recovery, too much leads to excessive storage costs and data inconsistencies.

What is data replication in database management systems?

Data replication involves creating and maintaining multiple copies of the same data. This process is used for data recovery, to improve data access speed, and to ensure data availability.

What is a database constraint?

Database constraints are predefined rules applied to database tables that are used to maintain the quality of data. Some of the constraints in SQL include primary key, foreign key, unique, check, and not null constraints.

What is database indexing?

Database indexing is a data structure technique to improve the speed of operations in a database table. Indexes are used to quickly locate data without having to search through every row in a database table every time a database table is accessed.

What is database replication?

Database replication is the process of creating and managing multiple copies of a database. Replication enhances the availability of the database and protects the database from the loss of a single server.

What is Database Seeding?

Database seeding pertains to test data creation. This data should be easily refreshable, cover all important border conditions, and be organized into independent sets for various testing purposes.

What is database sharding?

Database sharding is a type of database partitioning that separates large databases into smaller, faster, more manageable parts called data shards.

How can deadlocks be prevented in a database management system?

Deadlocks can be prevented by implementing techniques such as lock timeouts, deadlock detection algorithms, ordered locking, or the "Wait-Die" and "Wound-Wait" schemes.

What is denormalization and why is it used?

Denormalization is the process of adding redundancy to an optimized database to improve read performance. It's used when complex joins and queries are affecting performance.

What are distributed databases?

Distributed databases are databases in which storage devices are not all attached to a common processor. It may be stored in multiple computers located in the same physical location, or spread out over a network of interconnected computers.

What are some limitations of ER diagrams?

ER diagrams can't capture the dynamic aspects of a system, i.e., how the system behaves in response to external events. They also don't show temporal data or enforce data integrity constraints.

What are Entity Relationship Diagrams (ERD)?

ERDs, including the Chen model, Information Engineering (IE), and Unified Modeling Language (UML), represent entities and their relationships visually.

What is ETL?

ETL stands for Extract, Transform, Load. It's a process that extracts data from source systems, transforms the information into a consistent data type, then loads the data into a single depository.

What are SQL events and how can you limit their duration?

Events in SQL are tasks scheduled to run at a specific time or intervals. To limit an event's duration, use the ENDS keyword.

What is eventual consistency in the context of NoSQL databases?

Eventual consistency means that if no new updates are made to a given item, eventually all accesses to that item will return the last updated value. It's a model used in many large distributed systems.

What is horizontal scaling, and how does it differ from vertical scaling?

Horizontal scaling means adding more servers to the system to improve performance, also known as scale-out. Vertical scaling, also known as scale-up, means adding more resources such as CPU or memory to a single server.

What is a staging area in a data warehouse?

In a data warehouse, a staging area is often used for cleaning and processing operational data before it's inserted into the warehouse.

What are some of the key SQL queries one should be comfortable with for database management?

Key SQL queries include SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, JOIN, and subqueries.

What are modification anomalies and how can they be prevented?

Modification anomalies can occur when a database isn't structured correctly. Normalizing a database helps prevent these.

What is NoSQL?

NoSQL (Not Only SQL) is a category of database management systems that do not conform to the traditional relational database management system rules but provide mechanisms for storage and retrieval of data that can be scaled horizontally and are schema-free.

What are NoSQL databases?

NoSQL databases are non-tabular databases that store data differently than relational tables. They are used when dealing with a huge quantity of data where the data's nature does not require a relational model.

What are some advantages of using NoSQL databases?

NoSQL databases handle a lot of data and provide flexibility as the data structure can be altered over time. They are particularly good with unstructured data and provide a higher scalability level compared to relational databases.

What are the normal forms in a database?

Normal forms are rules that define certain properties of the database. They are divided into several degrees, including First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), Boyce-Codd Normal Form (BCNF), Fourth Normal Form (4NF), and Fifth Normal Form (5NF).

What is SQL normalization?

Normalization in SQL is the process of organizing data in a database to reduce redundancy and improve data integrity.

What is normalization in the context of databases?

Normalization is the process of designing a database in a way that reduces redundancy and ensures data integrity. The main aim of normalization is to add, delete, or modify fields that can be made in a single table.

What is the difference between OLTP and OLAP in the context of data warehousing?

OLTP (Online Transaction Processing) is characterized by a large number of short on-line transactions (INSERT, UPDATE, DELETE). OLAP (Online Analytical Processing) is characterized by a relatively low volume of transactions. Queries are often complex and involve data aggregation.

What is Object-Relational Mapping (ORM)?

ORM is a technique that lets you interact with your database, like you would with SQL. It 'maps' Object code to relational databases. It provides a way to bridge data and objects in a language you're familiar with and in a much easier manner.

What are the limitations of Pure Object-Oriented DBMSs?

Object-oriented DBMSs require complete overhauls of existing systems. They're best suited to systems requiring complex data manipulation, such as CAD and AI, but are less efficient for simple, straightforward data storage and retrieval.

How can primary keys be designated in Application-Time Period Tables?

Primary keys uniquely identify each record in a table and can be defined when creating the table or later using ALTER TABLE.

What are relational operators in SQL?

Relational operators, such as UNION, INTERSECTION, EXCEPT, and JOIN, allow you to combine, compare, or extract data from multiple related tables.

What are Service Level Objectives (SLOs)?

SLOs are commitments made by architects and operators to guide the design and operations of the system to meet specific performance characteristics, often defined in a Service Level Agreement (SLA).

What is an SQL injection attack?

SQL Injection is a code injection technique where an attacker can insert malicious SQL statements into an entry field for execution. This can lead to unauthorized viewing of data, data manipulation, and even data deletion.

What is SQL aliasing?

SQL aliasing is the process of giving a table, or a column in a table, a temporary name to make the query more readable.

What are the differences between SQL and NoSQL databases?

SQL databases are relational, NoSQL are non-relational. SQL databases use structured query language and have a predefined schema. NoSQL databases have dynamic schemas for unstructured data. SQL databases are vertically scalable, NoSQL databases are horizontally scalable. SQL databases are table-based, while NoSQL databases are document, key-value, graph or wide-column stores.

What are some examples of NoSQL databases?

Some examples of NoSQL databases are MongoDB, Cassandra, Redis, Couchbase, and HBase.

What are some examples of SQL-based databases?

Some examples of SQL-based databases include MySQL, PostgreSQL, SQLite, and Oracle Database.

What are the differences between a spreadsheet and a database?

Spreadsheets are designed for one-time, individual analysis of data. Databases, on the other hand, are designed for multiple users and large volumes of data. Databases can manage relationships between different data elements, enforce data integrity, and handle transactions.

How do Stored Functions differ from Stored Procedures in SQL?

Stored Functions, like Stored Procedures, allow code reuse, but they're invoked within SQL expressions instead of using the CALL statement.

What are Stored Procedures in SQL and how are they invoked?

Stored Procedures are SQL statements stored and executed on the database server rather than the client, enhancing performance and security. They are invoked using the CALL statement.

What is a stored procedure, and what are some benefits of using them?

Stored procedures are prepared SQL codes that you can save and reuse. The benefits include better performance, higher productivity, ease of use, security, and integrity.

What are system-versioned tables?

System-versioned tables maintain an auditable record of when data was added, changed, or deleted.

What is temporal data?

Temporal data refers to any data associated with a specific time period during which the data is effective or valid.

What are the ACID properties in a database?

The ACID properties of a database system are Atomicity, Consistency, Isolation, and Durability. These properties are essential for any system that maintains data over time.

What does the ATOMIC keyword do in a SQL compound statement?

The ATOMIC keyword ensures that the entire compound statement either executes in its entirety or fails as a whole. If execution fails, it rolls back to the original state, maintaining data integrity.

What is the CAP theorem in distributed systems?

The CAP theorem states that it's impossible for a distributed system to simultaneously provide Consistency, Availability, and Partition tolerance.

What is the DROP command in SQL?

The DROP command in SQL is used to delete a whole database or just a table.The DROP command destroys the objects like an existing database, table, index, or view.

What is the Extended Relational Model (ERM)?

The ERM incorporates elements of both the relational and object-oriented models. It retains the simplicity and robustness of the relational model while adding features like user-defined data types, inheritance, and polymorphism.

Explain the IF-THEN-ELSE-END IF construct in SQL.

The IF-THEN-ELSE-END IF construct allows for conditional execution of code blocks. If a condition is true, the corresponding block runs, else another block runs.

What is the SQL GROUP BY statement used for?

The SQL GROUP BY statement is used with the aggregate functions to group the result-set by one or more columns.

What is the SQL ORDER BY keyword used for?

The SQL ORDER BY keyword is used to sort the result-set in ascending or descending order by one or more columns.

What is the database locking mechanism?

The database locking mechanism is a system used to synchronize access to a database item by multiple transactions. Locking can prevent multiple transactions from modifying the same data at the same time.

What are the different transaction properties?

The properties of a transaction can be remembered using the acronym ACID: Atomicity, Consistency, Isolation, and Durability.

What are the different types of SQL normalization?

The types of SQL normalization include 1NF (First Normal Form), 2NF (Second Normal Form), 3NF (Third Normal Form), BCNF (Boyce-Codd Normal Form), 4NF (Fourth Normal Form), 5NF (Fifth Normal Form or Project-Join Normal Form), and 6NF (Sixth Normal Form).

What are triggers in SQL?

Triggers are automated actions that execute, or 'trigger', in response to certain database events like INSERT, UPDATE, or DELETE.

What is the difference between UNION and UNION ALL in SQL?

UNION combines the results of two or more SELECT statements and removes duplicate rows. UNION ALL does the same but does not remove duplicates.

What is a UNION operation in SQL?

UNION operator combines the result sets of two or more SELECT statements. However, it eliminates duplicate rows from the results. Each SELECT statement within the UNION must have the same number of columns, and the columns must also have similar data types.

How do you declare and use a variable in SQL?

Variables are declared using the DECLARE statement, and they can be assigned a value using the SET statement. For example: BEGIN DECLARE @temp INT; SET @temp = 10; SELECT @temp; END;