DBMS Flashcards
53 Questions
101 Views

Choose a study mode

Play Quiz
Study Flashcards
Spaced Repetition
Chat to lesson

Podcast

Play an AI-generated podcast conversation about this lesson

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?

    <p>Retrieve the names and majors of all students who do not have any grade of E in any of their courses</p> 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?

    <p>SELECT LNAME FROM EMPLOYEE WHERE SALARY &gt;= 10000+(SELECT MIN(SALARY) FROM EMPLOYEE)</p> Signup and view all the answers

    The unary relational operations are?

    <p>SELECT, PROJECT, RENAME</p> Signup and view all the answers

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

    <p>R but not in S</p> Signup and view all the answers

    Compared to an INTERSECTION, a RIGHT OUTER JOIN may?

    <p>Include more tuples</p> Signup and view all the answers

    A natural join?

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

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

    <p>Average salary of employees by department</p> Signup and view all the answers

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

    <p>∀x(NOT P(x))</p> Signup and view all the answers

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

    <p>Project (R(A,B,C))</p> 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?

    <p>Cartesian product</p> 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?

    <p>Difference</p> 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?

    <p>Union</p> 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?

    <p>Intersection</p> 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?

    <p>Outer join</p> Signup and view all the answers

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

    <p>Theta join</p> Signup and view all the answers

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

    <p>Equijoin</p> 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>P, W are bound. E is free</p> Signup and view all the answers

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

    <p>All of the above</p> Signup and view all the answers

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

    <p>Attribute names</p> Signup and view all the answers

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

    <p>WHERE b-year LIKE '195_'</p> 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?

    <p>CASCADE</p> Signup and view all the answers

    Which statement is true regarding SQL's aggregation operations?

    <p>The HAVING clause requires a GROUP BY</p> Signup and view all the answers

    The three schema DB architecture consists of?

    <p>Internal, conceptual, and external schemata</p> Signup and view all the answers

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

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

    Which of these statements about keys is true?

    <p>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</p> Signup and view all the answers

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

    <p>Duplicate values in the EMPNO attribute</p> Signup and view all the answers

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

    <p>Referential integrity</p> Signup and view all the answers

    A weak entity type?

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

    What does a dashed oval mean?

    <p>Derived attribute</p> 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?

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

    Which is true of ERDs and class diagrams in UML?

    <p>Both show types of things remembered by the system</p> Signup and view all the answers

    All relationships in the ER model are required to have?

    <p>Cardinality</p> Signup and view all the answers

    Domain constraints mean?

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

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

    <p>Hash-index on Name</p> Signup and view all the answers

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

    <p>Tree index on Salary, hash index on Name</p> 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.

    <p>False</p> 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.

    <p>False</p> Signup and view all the answers

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

    <p>True</p> Signup and view all the answers

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

    <p>True</p> 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.

    <p>True</p> Signup and view all the answers

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

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

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

    <p>Any two tuples in R that have the same value for A must have the same value for B</p> 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:

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

    A table that displays data redundancies yields the following anomalies:

    <p>Update, insertion, deletion anomalies</p> Signup and view all the answers

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

    <p>2NF</p> Signup and view all the answers

    What is ACID?

    <p>Atomic, Consistent, Isolated, Durable</p> Signup and view all the answers

    What is atomic?

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

    What is consistent?

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

    What is isolated?

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

    What is durable?

    <p>Database changes made by a completed transaction must persist -- even if the database fails</p> 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.

    Quiz Team

    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.

    More Like This

    Use Quizgecko on...
    Browser
    Open
    Browser
    Browser