MIS 3376 Final Exam Review

Sub-form

- AKA, hierarchical form, master/detail form, parent/child form

Why UML class diagrams are preferred over ER diagrams?

- They provide an introduction to object-oriented design, so students will be better prepared for future development issues - They are standardized, so students learn only one set of notations - They are "cleaner" in the sense that they are easier to read without the bubbles and cryptic notations of traditional ERDs - With the rapid adoption of UML as a standard design methodology, students are better prepared to move into future jobs

What is a subquery and in what situations is it useful?

- a query within another SQL query, most commonly embedded in WHERE clause - used to return data from a table, and this data will be used in the main query as a condition to further restrict the data to be retrieved

query by example (QBE)

- database query language for relational databases - user interface that simplifies SQL procedures

What is the SQL UNION command and when is it useful?

- used to combine data from the result of 2 or more SELECT command queries into a single distinct result set

What are the three basic tasks of a query language?

1. Define the database 2. Change the data 3. Retrieve the data

What are the four questions used to create a query?

1. SELECT - What output (columns and calculations) do you want to see? 2. FROM - What do you know or what constraints are given? 3. JOIN - What tables are involved? 4. WHERE - How are the tables joined? What needs to be met?

What are the basic SQL data definition commands?

CREATE, ALTER, DROP, RENAME, TRUNCATE

In running queries that produce duplicate rows, the keyword used to eliminate the duplicates from the output is

DISTINCT

To sort a list totals from high to low, use the SQL phrase

ORDER BY totals DESC

Missing data in a database is referenced as _____ in a query.

Is Null

The most important characteristic of a primary key in a table is that

It must be unique in all cases

To handle simple pattern matching tasks SQL provides the _______ command.

LIKE

Copying attributes from a form led to an initial design of: MeetingSchedule(PersonID, Name, Title, (Date/Time, Contact, Phone, Reason)) Parentheses indicate a repeating section. This design can be converted to 1NF as:

MeetingSchedule(PersonID, Name, Title) + Reason(PersonID, Date/Time, Contact, Phone, Reason)

What are the primary sections of reports?

REPORT HEADER, PAGE HEADER, DETAIL, PAGE FOOTER and REPORT FOOTER

_________ integrity exists when a value for a foreign key can be entered only if the corresponding value already exists in the originating table. (10 points)

Referential

An association where a column in one table connects to another column in the same table is known as:

Reflexive

What is the outline of the report layout?

Report Header Page Header Group Header Detail Group Footer Page Footer Report Footer

Using the basic SQL commands, the desired output columns are listed after which phrase/keyword:

SELECT

SQL Mnemonic

SELECT FROM INNER JOIN WHERE GROUP BY HAVING ORDER BY (Someone From Ireland Will Grow Horseradish and Onions)

What is the basic structure of the SQL SELECT command

SELECT columns or SELECT Table.Column

What is the SQL syntax for joining two tables?

SELECT, FROM, INNER JOIN, ON

FROM

SQL command used to specify the tables used in the query - what tables are involved?

Using common assumptions, which of the following columns would NOT depend on CustomerID?

Sale date

When designing a database table, computed values?

Should not be stored.

SQL

Structured Query Language - retrieves and updates data in tables and views (manipulates RDBs)

What is the purpose of the DISTINCT operator?

Tells the DBMS to display only rows that are unique

IN

allows you to easily test if an expression matches any value in a list of values

What are the main controls you can use on forms?

bound, unbound, calculated

HAVING

comes right after GROUP BY to query the database based on a specified condition

Tabular Form

enables users to update multiple rows in a table at once from a single page

What are the uses for forms and reports

forms for getting data in, reports for presenting data out

JOIN

how are the tables joined? - conditions

Which of the following types of relationships between two tables is not directly supported by relational database?

many-to-one

Page footer

printed at the bottom of every page--page totals or page numbers & notes

Report footer

printed one time at the end of the report. summary notes, overall totals, and graphs for the entire data set

What are the main report types?

query report, data entry report, column report, parameter query, data entry, chart design

INNER JOIN

selects all rows from both tables as long as there is a match between the columns creates a new table by combining rows that have matching values in 2 or more tables

Page header

title lines or page notes that are printed at the top of every page

Report header

title pages that are printed one time for entire report

Which of the following is not one of the basic questions that need to be addressed to write a query?

How many rows will be returned?

What are the primary form types?

1NF, 2NF, 3NF, and BCNF

Given the table list below and common assumptions, in which normal form is this list? Customer(CustomerID, FirstName, LastName, Address, City, State, ZIPCode) Sale(SaleID, SaleDate, CustomerID) SaleItems(SaleID, ItemID, Quantity) Item(ItemID, Description, ListPrice, QuantityOnHand)

3NF

Constraint

AND

When writing SQL WHERE conditions, combining conditions with which operator will tend to reduce the number of matching rows returned?

AND

What needs to be done for converting one-to-many relationships?

Add primary key from the one-side as a foreign key to the many-side table.

Third normal form is achieved when the design is in second normal form and

Each non-key column depends on nothing but the key.

Second normal form (2NF) is achieved when the design is in first normal form and (10 points)

Each non-key column depends on the whole key.

Switchboard

a form that appears when you open a database and that provides controlled access to the database's forms, reports and queries - a form that allows you to navigate around your Access database

How do you compute subtotals using SQL?

use ROLLUP extension of the GROUP BY statement - generate hierarchical subtotal rows according to its input columns and also adds a grand total row to the result set

UNION

used to combine the data from the result of 2 or more SELECT command queries into a single distinct result set

GROUP BY

used to group any rows of a column with the same value stored in them, based on a function specified in the statement

ORDER BY

used to sort the result set in ascending/descending order

DISTINCT

used with SELECT to eliminate all duplicate records and fetching only unique records

WHERE

what do you already know (or what constraints are given) - criteria

SELECT

what do you want to see? - columns

Using the basic SQL commands, a condition of the form SaleDate > 'May 1, 2014'

will return rows where the sale took place after May 1, 2014.