Databases Flashcards
What are the three key steps of Data Mining?
Pre-processing, Extraction, Evaluation & Presentation
The ETL process is part of which of the following steps?
Data Cleaning
Data Integration
Data Selection
Data Transformation
Data Integration
ETL process (Extract/Transform/Load) gets transactional data into a format that is optimal for reporting/queries.
What are 4 kinds of UMLs?
Which is often used to create physical schema and DDL/SQL code?
UML = Unified Modeling Language Toolsets for describing databases
Class diagram - describes OO classes. Shows classes as boxes and inheritance with solid arrow pointing to parent class
Activity diagram - process flowchart; decision steps are represented as diamonds
Use Case Diagram - actors (users; stick figures), goals, dependencies
Entity-Relationship Diagram (E-R Diagram) - objects and their relationships. Uses special symbols to show cardinality. Often used to create physical schema and DDL/SQL code.
Which of the following describes the ACID Test property of Consistency?
A. Database transactions must be indivisible.
B. Simultaneous transactions must be processed sequentially.
C. Transactions must meet all constraint rules.
D. The system must be tolerant to failure.
ACID Test describes reliable databases.
Answer: C
Atomicity = transactions must be indivisible (all or none; no partial transactions)
Consistency = transactions must meet all constraint rules (this is the answer to this question)
Isolation = DBMS must be able to sequence simultaneous transactions
Durability = system must be tolerant to failure
Name some features of relational databases
Defines associations within and between Relations ~ Table
Attributes ~ Columns (domains in the relation). Can have constraints
Tuple ~ Row (list of elements)
Element ~ Cell
Primary Key (PK)
Foreign Key (FK) - refers to PK in another table
Data Definition Language (DDL) is used to describe metadata about the relation.
Data Dictionary provides definitions and intentions for each field.
What is the purpose of a SQL JOIN?
To retrieve data from two or more tables based on a related column (attribute; Primary Key in one which is a Foreign Key in another).
Used in relational database management systems
A/an ________ returns all records from both tables even if there is no match.
A. INNER JOIN
B. CROSS JOIN
C. FULL OUTER JOIN
D. None of the above
Answer: FULL OUTER JOIN
An INNER JOIN only returns rows that are present in both columns.
A CROSS JOIN (aka Cartesian Join) gives the cross product of both tables. Ex two tables with 5 rows each would return with 25 rows
A FULL OUTER JOIN will return all cells from both tables.
Fill in the blank: A _______ JOIN returns all records from the left table and the matched records from the right table.
LEFT
Which type of JOIN would you use to get all records from both tables, regardless of whether they match?
FULL OUTER JOIN
What is the primary difference between INNER JOIN and RIGHT OUTER JOIN?
INNER JOIN returns only the rows with matching data in both tables, while RIGHT OUTER JOIN returns all rows from the right table and matched rows from the left table.
