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?
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?
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?
Signup and view all the answers
The unary relational operations are?
Signup and view all the answers
The relational algebra expression R-S includes all tuples in?
Signup and view all the answers
Compared to an INTERSECTION, a RIGHT OUTER JOIN may?
Signup and view all the answers
A natural join?
Signup and view all the answers
What does the expression 'Dno F average(Salary)(Employee)' mean?
Signup and view all the answers
Choose a statement in relational calculus that is equivalent to NOT(∃x)(P(x))?
Signup and view all the answers
Choose the relational algebra expression that is equivalent to {t.A, t.B | R(t)}?
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?
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?
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?
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?
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?
Signup and view all the answers
Which operation produces all combinations of tuples from two relations that satisfy the join condition (>, < =>, etc.)?
Signup and view all the answers
Which operation produces all the combinations of tuples from two relations that satisfy only equality conditions?
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)}?
Signup and view all the answers
Which of the following relational algebra operations from set theory are commutative?
Signup and view all the answers
Which of the following can be included in an SQL CREATE TABLE statement?
Signup and view all the answers
Which WHERE clause would you use to match birth years in the 1950s?
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?
Signup and view all the answers
Which statement is true regarding SQL's aggregation operations?
Signup and view all the answers
The three schema DB architecture consists of?
Signup and view all the answers
Which of these statements about the formal and informal terms is true?
Signup and view all the answers
Which of these statements about keys is true?
Signup and view all the answers
Suppose a relation EMPLOYEE(EMPNO, NAME, DNO, MGR, SAL). Which of these would violate the entity integrity constraint?
Signup and view all the answers
Restriction, cascade, and nullification are methods to resolve violations of what?
Signup and view all the answers
A weak entity type?
Signup and view all the answers
What does a dashed oval mean?
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?
Signup and view all the answers
Which is true of ERDs and class diagrams in UML?
Signup and view all the answers
All relationships in the ER model are required to have?
Signup and view all the answers
Domain constraints mean?
Signup and view all the answers
What indexing would you use for the SQL query 'SELECT * FROM EMP WHERE Name='John Doe'?
Signup and view all the answers
What indexing would you use for the SQL query 'SELECT * FROM EMP WHERE Name='Smith' AND Salary < 10000'?
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.
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.
Signup and view all the answers
A relation where all attributes are atomic is always in 1st normal form.
Signup and view all the answers
A prime attribute must be a member of some candidate key.
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.
Signup and view all the answers
If a relation R is decomposed into {R1, R2,..., Rn} and the decomposition is lossless, then?
Signup and view all the answers
The functional dependency {A} -> {B} for the relation schema R(A,B,C,D) implies that?
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:
Signup and view all the answers
A table that displays data redundancies yields the following anomalies:
Signup and view all the answers
A table where every attribute is fully functionally dependent on the key, is said to be in?
Signup and view all the answers
What is ACID?
Signup and view all the answers
What is atomic?
Signup and view all the answers
What is consistent?
Signup and view all the answers
What is isolated?
Signup and view all the answers
What is durable?
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
, andUPDATE
.
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
, andRENAME
. - The expression
R-S
results in tuples from R that are not in S. -
RIGHT OUTER JOIN
can yield more tuples thanINTERSECTION
. - 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 aGROUP BY
clause for aggregation operations.
Referential Integrity
- Referential triggered actions with
FOREIGN KEY
includeSET NULL
,CASCADE
, andSET 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 theEMPLOYEE
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 onName
for queries with multiple conditions.
Functional Dependencies
- Functional dependency
{A} -> {B}
implies tuples sharing the same value forA
must have the same value forB
. - 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.