Chapter 7 Flashcards
What is a Join?
A relational operation that causes two or more tables with a common domain to be combined into a single table or view.
What is Equi-join?
A join in which the joining condition is based on equality between values in the common columns; common columns appear redundantly in the result table
What is Natural join?
An equi-join in which one of the duplicate columns is eliminated in the result table.
What are usually the common columns in joined tables?
Usually the primary key of the dominant table and the foreign key of the dependent table in 1:M relationships.
What is Outer join?
A join in which rows that do not have matching values in common columns are nonetheless included in the result table (as opposed to inner join, in which rows must have matching values in order to appear in the result table)
What is Union join?
Includes all data from each table that was joined
Example of different join types
What is a subquery?
Placing an inner query (SELECT statement) inside an outer query
What are the options for using a subquery?
- In a conditon of the WHERE clause
- As a “table” of the FROM clause
- Within the HAVING clause
What are the two options for a subquerie?
Noncorrelated
Correlated
What is a noncorrelated subquerie?
Executed once for the entire outer query
What is a correlated subquerie?
Executed once for each row returned by the outer query
What are the traits of a noncorrelated subquerie?
- Do not depend on data from the outer query
- Execute once for the entire outer query
What are the traits of a correlated subquerie?
- Make use of data from the outer query
- Execute once for each row of the outer query
- Can use the EXISTS operator
What is a union querie?
Combine the output (union of multiple queries) together into a single result table
Why would it be useful to combine queries, through the use of Views?
Because production databases contain hundreds or even thousands of tables, and tables could include hundreds of columns.
What can you do if you use a view (which is a query)?
You could have another query that uses the view as if it were a table.
What are some tips for developing queries?
- Be familiar with the data model (entities and relationships)
- Understand the desired results
- Know the attributes desired in the results
- Identify the entities that contain desired attributes
- Review ERD
- Construct a WHERE equality for each link
- Fine tune with GROUP BY and HAVING clauses if needed
- Consider the effect on unusual data
What are some considerations for Query efficiency?
- Instead of SELECT *, identify the specific attributes in the SELECT clause; this helps reduce network traffic of result set
- Limit the number of subqueries; try to make everything done in a single query if possible
- If data is to be used many times, make a separate query and store it as a view
What are some guidelines for better query design?
- Understand how indexes are used in query processing
- Keep optimizer statistics up-to-date
- Use compatible data types for fields and literals
- Write simple queries
- Break complex queries into multiple simple parts
- Don’t nest one query inside another query
- Don’t combine a query with itself (if possible avoid self-joins)
What are some more guidelines for better query design?
- Create temporary tables for groups of queries
- Combine update operations
- Retrieve only the data you need
- Don’t have the DBMS sort without an index
- Learn!
- Consider the total query processing time for ad hoc queries
What is transaction?
A discrete unit of work that must be completely processed or not processed at all
- May involve multiple updates
- If any update fails, then all other updates must be cancelled
What are the SQL commands for transactions?
- BEGIN TRANSACTION/END TRANSACTION (Marks boundaries of a transaction)
- COMMIT (Makes all updates permanent)
- ROLLBACK (Cancels updates since the last COMMIT)
What are data dictionary facilities?
- System tables that store metadata
- Users usually can view some of these tables
- Users are restricted from updating them
Examples of data dictionary facilities in Oracle 12c
DBA_TABLES - descriptions of tables
DBA_CONSTRAINTS - description of constraints
DBA_USERS - information abou tthe users of the system
Examples of data dictionary facilities in Microsoft SQL Server 2014
- sys.columns - table and column definitions
- sys.indexes - table index information
- sys.foreign_key_columns - details about columns in foreign key constraints
What are user-defined data types (UDT)?
Subclasses of standard types or an object type
What are the analytical functions (for OLAP)?
CEILING, FLOOR, SQRT, RANK, DENSE_RANK, ROLLUP, CUBE, SAMPLE
WINDOW -improve numerical analysis capabilities
What are new data types in SQL:2008?
BIGINT, MULTISET (collection), XML
What does CREATE TABLE LIKE do?
Creates a new table similar to an existing one
What are routines?
Program modules that execute on demand
What are functions?
Routines that return values and take input parameters
What are procedures?
Routines that do not return values and can take input or output parameters.
What are triggers?
Routines that execute in response to a database event (INSERT, UPDATE, or DELETE)
What is Embedded SQL?
Including hard-coded SQL statements in a program written in another language such as C or Java
What is Dynamic SQL?
Ability for an application program to generate SQL code on the fly, as the application is running.
What are the reasons to embed SQL in 3GL?
- Can create a more flexible, accessible interface for the user
- Possible performance improvement
- Database security improvement; grant access only to the application instead of users
