DBMS Flashcards

Questions and Answers

When selecting from a table, which of the following is true about the SQL SELECT statement?

WHERE is optional, but FROM is required (correct)

WHERE and FROM are both optional

SELECT is optional, but WHERE is required

FROM is optional, but WHERE is required

Which statement can select all of the attributes and all of the tuples from relation R?

SELECT * FROM R

Three SQL commands used to change the database are?

INSERT, DELETE, UPDATE

What does the query 'SELECT S.Name, S.Major FROM STUDENT S WHERE NOT EXISTS ( SELECT * FROM GRADE_REPORT G WHERE G.StudentNumber=S.StudentNumber AND G.Grade='E')' do?

Retrieve the names and majors of all students who do not have any grade of E in any of their courses Signup and view all the answers

Which of the queries retrieves the names of employees who make at least $10K more than the employee who is paid the least in the company?

SELECT LNAME FROM EMPLOYEE WHERE SALARY >= 10000+(SELECT MIN(SALARY) FROM EMPLOYEE) Signup and view all the answers

The unary relational operations are?

SELECT, PROJECT, RENAME Signup and view all the answers

The relational algebra expression R-S includes all tuples in?

R but not in S Signup and view all the answers

Compared to an INTERSECTION, a RIGHT OUTER JOIN may?

Include more tuples Signup and view all the answers

A natural join?

Uses a * notation, combines all attributes of both relations, and joins attributes with the same name Signup and view all the answers

What does the expression 'Dno F average(Salary)(Employee)' mean?

Average salary of employees by department Signup and view all the answers

Choose a statement in relational calculus that is equivalent to NOT(∃x)(P(x))?

∀x(NOT P(x)) Signup and view all the answers

Choose the relational algebra expression that is equivalent to {t.A, t.B | R(t)}?

Project (R(A,B,C)) Signup and view all the answers

Which operation produces a relation that has all of the attributes and all of the tuples in all possible combinations of two relations, even if they are not union compatible?

Cartesian product Signup and view all the answers

Which operation produces a relation that includes all of the tuples in one relation, which are not also in another relation, if they are union compatible?

Difference Signup and view all the answers

Which operation produces a relation that includes all of the tuples in two relations if they are union compatible?

Union Signup and view all the answers

Which operation produces a relation that includes all of the tuples that are in both of two relations, if they are union compatible?

Intersection Signup and view all the answers

Which operation produces a relation with all of the tuples in one relation, and also the matching tuples of another relation?

Outer join Signup and view all the answers

Which operation produces all combinations of tuples from two relations that satisfy the join condition (>, < =>, etc.)?

Theta join Signup and view all the answers

Which operation produces all the combinations of tuples from two relations that satisfy only equality conditions?

Equijoin Signup and view all the answers

Which is true regarding the tuple variables in this relational calculus expression? {e.Lname, e.Fname | Employee(e) AND((Ep)(Ew)(PROJECT(p) and WORKS_ON(w) AND p.Dum=5 AND w.Essn=e.SSN AND P.Pnumber=w.Pno)}?

P, W are bound. E is free Signup and view all the answers

Which of the following relational algebra operations from set theory are commutative?

All of the above Signup and view all the answers

Which of the following can be included in an SQL CREATE TABLE statement?

Attribute names Signup and view all the answers

Which WHERE clause would you use to match birth years in the 1950s?

WHERE b-year LIKE '195_' Signup and view all the answers

What are the referential triggered actions that may be used with the FOREIGN KEY clause to a SQL CREATE statement?

CASCADE Signup and view all the answers

Which statement is true regarding SQL's aggregation operations?

The HAVING clause requires a GROUP BY Signup and view all the answers

The three schema DB architecture consists of?

Internal, conceptual, and external schemata Signup and view all the answers

Which of these statements about the formal and informal terms is true?

A domain in formal terms is like a table in informal terms Signup and view all the answers

Which of these statements about keys is true?

A superkey is a subset of the relation attributes where all tuple values must be distinct, a key is a minimal superkey, any key is a superkey, but not vice versa Signup and view all the answers

Suppose a relation EMPLOYEE(EMPNO, NAME, DNO, MGR, SAL). Which of these would violate the entity integrity constraint?

Duplicate values in the EMPNO attribute Signup and view all the answers

Restriction, cascade, and nullification are methods to resolve violations of what?

Referential integrity Signup and view all the answers

A weak entity type?

Uses a foreign key as part of its primary key and has total participation in an identifying relationship Signup and view all the answers

What does a dashed oval mean?

Derived attribute Signup and view all the answers

What does a diagram where it is a 1-to-n relationship and the n relationship has double lines indicate?

A person must work on at least one project and may have many projects Signup and view all the answers

Which is true of ERDs and class diagrams in UML?

Both show types of things remembered by the system Signup and view all the answers

All relationships in the ER model are required to have?

Cardinality Signup and view all the answers

Domain constraints mean?

Each attribute value in the tuple must be an element from the domain Signup and view all the answers

What indexing would you use for the SQL query 'SELECT * FROM EMP WHERE Name='John Doe'?

Hash-index on Name Signup and view all the answers

What indexing would you use for the SQL query 'SELECT * FROM EMP WHERE Name='Smith' AND Salary < 10000'?

Tree index on Salary, hash index on Name Signup and view all the answers

A relation schema R is in Second Normal Form (2NF) if every non-prime attribute A in R is functionally dependent (FD) on the primary key.

False Signup and view all the answers

Full functional dependency means a FD Y->Z where removal of any attribute from Z means the FD does not hold anymore.

False Signup and view all the answers

A relation where all attributes are atomic is always in 1st normal form.

True Signup and view all the answers

A prime attribute must be a member of some candidate key.

True Signup and view all the answers

X->Y holds if whenever two tuples have the same value for X, they must have the same value for Y.

True Signup and view all the answers

If a relation R is decomposed into {R1, R2,..., Rn} and the decomposition is lossless, then?

The natural join of R1, R2,...,Rn will have the same number of tuples as the original relation R Signup and view all the answers

The functional dependency {A} -> {B} for the relation schema R(A,B,C,D) implies that?

Any two tuples in R that have the same value for A must have the same value for B Signup and view all the answers

Give the set of functional dependencies, ({A,B} ->{C,D,E} and {A}->{E}), for relation schema R=(A,B,C,D,E) we can infer the following:

{A,B} is a key for R Signup and view all the answers

A table that displays data redundancies yields the following anomalies:

Update, insertion, deletion anomalies Signup and view all the answers

A table where every attribute is fully functionally dependent on the key, is said to be in?

2NF Signup and view all the answers

What is ACID?

Atomic, Consistent, Isolated, Durable Signup and view all the answers

What is atomic?

The transaction needs to either be entirely performed or not performed at all -- no half-done transactions are allowed Signup and view all the answers

What is consistent?

Transactions that start from a database in a consistent state should end with the database in a consistent state Signup and view all the answers

What is isolated?

Transactions should always function as if they were executing in isolation from other transactions Signup and view all the answers

What is durable?

Database changes made by a completed transaction must persist -- even if the database fails Signup and view all the answers

Study Notes

SQL Basics

WHERE clause is optional; FROM clause is mandatory in SELECT statements.
To select all attributes and tuples from a relation R, use SELECT * FROM R.
SQL commands to modify a database include INSERT, DELETE, and UPDATE.

Subqueries and Joins

The query SELECT S.Name, S.Major FROM STUDENT S WHERE NOT EXISTS (SELECT * FROM GRADE_REPORT G WHERE G.StudentNumber=S.StudentNumber AND G.Grade='E') retrieves names and majors of students without an 'E' grade.
Employees earning at least $10,000 more than the lowest paid can be selected using a subquery: SELECT LNAME FROM EMPLOYEE WHERE SALARY >= 10000 + (SELECT MIN(SALARY) FROM EMPLOYEE).

Relational Operations

Unary relational operations include SELECT, PROJECT, and RENAME.
The expression R-S results in tuples from R that are not in S.
RIGHT OUTER JOIN can yield more tuples than INTERSECTION.
A NATURAL JOIN uses a * notation and combines attributes with identical names from both relations.

Division and Aggregation

The expression Dno F average(Salary)(Employee) provides the average salary for employees categorized by department.
HAVING clause necessitates a GROUP BY clause for aggregation operations.

Referential Integrity

Referential triggered actions with FOREIGN KEY include SET NULL, CASCADE, and SET DEFAULT.
Methods to resolve referential integrity violations are restriction, cascade, and nullification.

Keys and Constraints

Superkeys consist of relation attributes with distinct tuple values; keys are minimal superkeys.
Duplicate values in the EMPNO attribute violate the entity integrity constraint in the EMPLOYEE relation.
A table where each attribute value is from its domain adheres to domain constraints.

Normal Forms

A relation achieves the First Normal Form (1NF) if all attributes are atomic.
A relation is in Second Normal Form (2NF) when every non-prime attribute is functionally dependent on the primary key.

ACID Properties

ACID stands for Atomicity, Consistency, Isolation, and Durability.
Atomicity ensures transactions are all or nothing; consistency guarantees a stable database state; isolation maintains operation separation; durability confirms persistent changes post-transaction.

Indexing

Use a hash index on Name for queries filtering by a specific employee name.
Implement a tree index on Salary and a hash index on Name for queries with multiple conditions.

Functional Dependencies

Functional dependency {A} -> {B} implies tuples sharing the same value for A must have the same value for B.
The functional dependencies {A, B} -> {C, D, E} and {A} -> {E} suggest {A, B} is a key for the relation schema R(A, B, C, D, E).

Entity-Relationship Models

A weak entity type relies on a foreign key for its primary key and participates fully in an identifying relationship.
Cardinality is required for all relationships in the Entity-Relationship (ER) model.
A dashed oval in a diagram signifies a derived attribute.

Additional Concepts

Trees and hash indexes enhance query performance based on search patterns.
Anomalies arising from data redundancies in a table include update, insertion, and deletion anomalies.

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Description

Test your knowledge with these flashcards on Database Management Systems (DBMS). Each card includes key concepts and SQL commands essential for understanding database operations. Perfect for students and professionals looking to reinforce their understanding of SQL.

DBMS Flashcards

Questions and Answers

When selecting from a table, which of the following is true about the SQL SELECT statement?

Which statement can select all of the attributes and all of the tuples from relation R?

Three SQL commands used to change the database are?

What does the query 'SELECT S.Name, S.Major FROM STUDENT S WHERE NOT EXISTS ( SELECT * FROM GRADE_REPORT G WHERE G.StudentNumber=S.StudentNumber AND G.Grade='E')' do?

Which of the queries retrieves the names of employees who make at least $10K more than the employee who is paid the least in the company?

The unary relational operations are?

The relational algebra expression R-S includes all tuples in?

Compared to an INTERSECTION, a RIGHT OUTER JOIN may?

A natural join?

What does the expression 'Dno F average(Salary)(Employee)' mean?

Choose a statement in relational calculus that is equivalent to NOT(∃x)(P(x))?

Choose the relational algebra expression that is equivalent to {t.A, t.B | R(t)}?

Which operation produces a relation that has all of the attributes and all of the tuples in all possible combinations of two relations, even if they are not union compatible?

Which operation produces a relation that includes all of the tuples in one relation, which are not also in another relation, if they are union compatible?

Which operation produces a relation that includes all of the tuples in two relations if they are union compatible?

Which operation produces a relation that includes all of the tuples that are in both of two relations, if they are union compatible?

Which operation produces a relation with all of the tuples in one relation, and also the matching tuples of another relation?

Which operation produces all combinations of tuples from two relations that satisfy the join condition (>, < =>, etc.)?

Which operation produces all the combinations of tuples from two relations that satisfy only equality conditions?

Which is true regarding the tuple variables in this relational calculus expression? {e.Lname, e.Fname | Employee(e) AND((Ep)(Ew)(PROJECT(p) and WORKS_ON(w) AND p.Dum=5 AND w.Essn=e.SSN AND P.Pnumber=w.Pno)}?

Which of the following relational algebra operations from set theory are commutative?

Which of the following can be included in an SQL CREATE TABLE statement?

Which WHERE clause would you use to match birth years in the 1950s?

What are the referential triggered actions that may be used with the FOREIGN KEY clause to a SQL CREATE statement?

Which statement is true regarding SQL's aggregation operations?

The three schema DB architecture consists of?

Which of these statements about the formal and informal terms is true?

Which of these statements about keys is true?

Suppose a relation EMPLOYEE(EMPNO, NAME, DNO, MGR, SAL). Which of these would violate the entity integrity constraint?

Restriction, cascade, and nullification are methods to resolve violations of what?

A weak entity type?

What does a dashed oval mean?

What does a diagram where it is a 1-to-n relationship and the n relationship has double lines indicate?

Which is true of ERDs and class diagrams in UML?

All relationships in the ER model are required to have?

Domain constraints mean?

What indexing would you use for the SQL query 'SELECT * FROM EMP WHERE Name='John Doe'?

What indexing would you use for the SQL query 'SELECT * FROM EMP WHERE Name='Smith' AND Salary < 10000'?

A relation schema R is in Second Normal Form (2NF) if every non-prime attribute A in R is functionally dependent (FD) on the primary key.

Full functional dependency means a FD Y->Z where removal of any attribute from Z means the FD does not hold anymore.

A relation where all attributes are atomic is always in 1st normal form.

A prime attribute must be a member of some candidate key.

X->Y holds if whenever two tuples have the same value for X, they must have the same value for Y.

If a relation R is decomposed into {R1, R2,..., Rn} and the decomposition is lossless, then?

The functional dependency {A} -> {B} for the relation schema R(A,B,C,D) implies that?

Give the set of functional dependencies, ({A,B} ->{C,D,E} and {A}->{E}), for relation schema R=(A,B,C,D,E) we can infer the following:

A table that displays data redundancies yields the following anomalies:

A table where every attribute is fully functionally dependent on the key, is said to be in?

What is ACID?

What is atomic?

What is consistent?

What is isolated?

What is durable?

Study Notes

SQL Basics

Subqueries and Joins

Relational Operations

Division and Aggregation

Referential Integrity

Keys and Constraints

Normal Forms

ACID Properties

Indexing

Functional Dependencies

Entity-Relationship Models

Additional Concepts

Studying That Suits You

Description

More Quizzes Like This

SQL Commands Quiz

SQL Database Management

نظم إدارة قواعد البيانات وDDL

Bases de Datos: Instalación y Migración