Database Systems Review

What is the difference between a procedural and a nonprocedural language? How would you classify the relational algebra and relational calculus

A procedural language is one that specifies the steps to be taken to solve a problem, while a nonprocedural language is one that specifies what the solution should look like without specifying the steps to get there. The relational algebra and calculus are nonprocedural languages that specify what data is required, without specifying how to retrieve it. The relational algebra is a nonprocedural language that operates on relations, using a set of operations to manipulate those relations. The relational calculus is also a nonprocedural language that specifies the conditions that must be satisfied by the desired result.

Describe the differences between a relation and a relation schema. What is a relational database schema?

A relation is a table in the relational data model, while a relation schema is the definition of a relation, including its name, attributes, and data types. A relational database schema is a collection of relation schemas that make up the structure of a relational database.

Explain how a relational calculus expression can be unsafe. Illustrate your answer with an example. Discuss how to ensure that a relational calculus expression is safe.

A relational calculus expression can be unsafe if it involves an existential quantifier (∃) in the predicate, which can result in an infinite number of tuples being generated. For example, the expression {x | ∃y R(x,y)} would return all attribute values from R for which there exists a corresponding value in the y attribute, which could potentially generate an infinite number of tuples. To ensure that a relational calculus expression is safe, the use of existential quantifiers should be minimized and restricted to cases where the number of generated tuples is known to be finite. Additionally, other restrictions can be imposed, such as limiting the number of tuples returned by the expression or restricting the types of values that can be used in the predicate.

What is the difference between a subquery and a join? Under what circumstances would you not be able to use a subquery?"

A subquery is a query that is nested within another query and is used to retrieve data for use in the outer query. A join combines two or more tables into a single result set based on common columns. Under certain circumstances, a subquery may not be able to be used, such as when the subquery returns too much data to be processed efficiently, when the subquery is too complex or uses too many nested queries, or when the subquery is not supported by the database software. In these cases, a join or other method may be used instead.

What is a view? Discuss the difference between a view and a base relation

A view is a virtual table created by applying a query to one or more existing tables in a database. A view does not store data itself but provides a way to access and present data from the underlying tables. The difference between a view and a base relation is that a view is based on a query and does not actually store data, while a base relation is a table that stores data. A view provides a way to present data from one or more base relations in a customized way, without modifying the underlying data.

What are the advantages and disadvantages of SQL?

Advantages of SQL include its ability to work with relational databases, its ability to handle large amounts of data, its simplicity and ease of use, and its ability to handle complex queries. Disadvantages include its lack of support for some advanced features, its complexity for certain tasks, and its potential for performance issues with complex queries.

What restrictions apply to the use of the aggregate functions within the SELECT statement? How do nulls affect the aggregate functions?

Aggregate functions, such as SUM, AVG, MAX, and MIN, can be used to perform calculations on groups of data within the SELECT statement. Restrictions on their use include the requirement that the data being aggregated must be of the same data type, and that any columns included in the SELECT statement that are not part of an aggregate function must also be included in the GROUP BY clause. Nulls can affect the aggregate functions by being excluded from the calculation, resulting in potentially inaccurate or incomplete results.

Discuss the differences between the candidate keys and the primary key of a relation. Explain what is meant by a foreign key. How do foreign keys of relations relate to candidate keys? Give examples to illustrate your answer.

Candidate keys are sets of attributes that can uniquely identify a tuple in a relation, while the primary key is the candidate key selected to be used as the primary means of identifying tuples in a relation. A foreign key is an attribute in one relation that refers to the primary key of another relation, allowing relationships to be established between tables. Foreign keys relate to candidate keys in that they refer to the primary key of another relation, which is a candidate key in that relation. For example, if a Booking relation has a foreign key hotelNo that refers to the primary key hotelNo in the Hotel relation, then the hotelNo attribute in the Hotel relation is a candidate key.

relationally complete

If it can express any query that can be expressed in the relational algebra.

Define the structure of a (well-formed) formula in both the tuple relational calculus and domain relational calculus.

In the tuple relational calculus, a well-formed formula is of the form {t | P(t)}, where t is a tuple variable and P(t) is a predicate that describes the conditions that the tuples in the result must satisfy. In the domain relational calculus, a well-formed formula is of the form {x | P(x)}, where x is a domain variable and P(x) is a predicate that describes the conditions that the attribute values in the result must satisfy.

attribute

Is a named column of a relation representing a property or characteristic of the entity being modeled.

tuple

Is a single row in a relation, representing a specific instance of the entity being modeled.

domain

Is the set of allowed values for an attribute.

extension

Refers to the actual data stored in a relation.

degree and cardinality

Refers to the number of attributes in a relation, while cardinality refers to the number of tuples.

closure of relational operations

Refers to the property that any sequence of relational operations can be expressed as a single operation in the same language.

Explain how the GROUP BY clause works. What is the difference between the WHERE and HAVING clauses?

The GROUP BY clause works by grouping the rows in the result set by one or more columns, allowing aggregate functions to be performed on the groups rather than on the entire result set. The WHERE clause is used to filter rows based on specific conditions, while the HAVING clause is used to filter groups based on specific conditions. The main difference is that the WHERE clause filters individual rows, while the HAVING clause filters groups.

Explain the function of each of the clauses in the SELECT statement. What restrictions are imposed on these clauses?

The clauses in the SELECT statement are as follows: - SELECT: specifies the columns to be included in the result set - FROM: specifies the table or tables to be queried - WHERE: specifies the conditions that the data must meet to be included in the result set - GROUP BY: specifies the grouping of data by one or more columns - HAVING: specifies the conditions that the grouped data must meet to be included in the result set - ORDER BY: specifies the ordering of the data in the result set Restrictions imposed on these clauses include limits on the number of tables that can be included in a query, limits on the number of columns that can be selected, and restrictions on the use of certain keywords and operators.

intension

The definition of a relation, including its name, attributes, and data types.

Define the five basic relational algebra operations. Define the Join, Intersection, and Division operations in terms of these five basic operations.

The five basic relational algebra operations are: - Selection (σ): selects tuples from a relation that satisfy a certain condition Projection (π): selects a subset of attributes from a relation - Union (⋃): combines two relations into a single relation, with no duplicates - Set difference (-): returns a relation containing tuples that appear in the first relation but not the second - Cartesian product (×): combines two relations into a single relation by pairing each tuple in the first relation with each tuple in the second relation. Join (⋈) is a binary operation that combines two relations based on a common attribute, returning a relation that includes all the attributes from both relations. Intersection (∩) is a binary operation that returns a relation containing tuples that appear in both relations. Division (÷) is a binary operation that returns a relation containing tuples from one relation that have a matching tuple in another relation for every tuple in that relation.

Discuss the differences between the five Join operations: Theta join, Equijoin, Natural join, Outer join, and Semijoin. Give examples to illustrate your answer.

The five join operations are: - Theta join: combines two relations based on a condition involving the attributes from both relations - Equijoin: a special case of theta join where the condition involves equality between the attributes - Natural join: combines two relations based on the attributes with the same name and discards duplicates - Outer join: combines two relations and includes all tuples from one relation, with null values for any unmatched tuples in the other relation - Semijoin: returns only the attributes from one relation that match the attributes in another relation For example, suppose we have two relations R and S with attributes A and B in R and attributes B and C in S. An equijoin on R and S would be R ⋈ A=B S, while a natural join would be R ⋈ S.

Discuss the properties of a relation.

The properties of a relation include: - Each attribute has a unique name - Each attribute has a domain - Each tuple is unique - The order of the tuples and attributes is not significant - Each attribute has a single value

Describe the relationship between mathematical relations and relations in the relational data model.

The relational data model is based on mathematical relations. Specifically, a relation in the data model corresponds to a mathematical relation. The mathematical concept of a relation involves a set of ordered pairs, while a relation in the data model involves a table with columns and rows.

Compare and contrast the tuple relational calculus with domain relational calculus. In particular, discuss the distinction between tuple and domain variables.

The tuple relational calculus and domain relational calculus are both formal languages used to express queries in the relational model. The tuple relational calculus operates on tuples, while the domain relational calculus operates on individual attribute values. Tuple variables refer to tuples in relations, while domain variables refer to individual attribute values.

What are the two major components of SQL and what function do they serve?

The two major components of SQL are Data Definition Language (DDL) and Data Manipulation Language (DML). DDL is used to define and modify the structure of the database, including creating tables, altering tables, and defining constraints. DML is used to manipulate the data within the database, including inserting, updating, deleting, and querying data.

Define the two principal integrity rules for the relational model. Discuss why it is desirable to enforce these rules.

The two principal integrity rules for the relational model are entity integrity and referential integrity. Entity integrity ensures that each tuple in a relation is uniquely identifiable, while referential integrity ensures that any foreign key values in a relation refer to valid primary key values in another relation. It is desirable to enforce these rules because they ensure data consistency and prevent data anomalies, such as duplications, deletions, and inconsistencies.

relation

This in the relational data model, is a table with columns and rows representing entities and their relationships. Each table is given a name and is composed of a set of attributes, with each attribute representing a characteristic of the entity being modeled.