Relational Databases, SQL & Application Design – Review

100 Q&A flashcards covering SQL commands, NULL logic, relational design, normalization, Visio notation, MS Access features, MSF methodology, ADO/JDBC programming, and general database concepts.

What does the SQL SELECT statement do?

Retrieves rows (records) from a database table.

Which SQL command is used to insert new rows into a table?

INSERT.

Which SQL command removes rows from a table?

DELETE.

Which SQL command modifies existing rows in a table?

UPDATE.

What is the main purpose of an index in a relational database?

To speed up record retrieval (search).

Which SQL statement permanently saves the changes of a transaction?

COMMIT.

Which SQL statement undoes the changes of the current transaction?

ROLLBACK.

What privilege-related SQL command grants rights to a user?

GRANT.

Which SQL command removes previously granted privileges?

REVOKE.

What does the SQL statement ALTER TABLE primarily change?

The schema/structure of an existing table.

In three-valued logic, what is the result of Null = Null?

Null.

What is the result of TRUE OR Null?

TRUE.

What is the result of FALSE OR Null?

Null.

What is the result of FALSE AND Null?

FALSE.

What is the result of TRUE AND Null?

Null.

What is the result of NOT Null?

Null.

Why does the predicate EmployeeID = NULL return no rows?

Because comparisons with NULL are unknown; you must use IS NULL.

Which SQL keyword counts rows irrespective of NULLs?

COUNT(*).

What is the maximum COUNT(*) result of SELECT … WHERE a < a?

Always 0.

In a relational database, what object corresponds to an entity?

A table.

Which relational object represents a single-valued attribute?

A column in a table.

What database object usually represents a one-to-many relationship?

A foreign key in the many-side table.

What database object represents a many-to-many relationship?

An additional associative (junction) table.

Which key enforces entity integrity and uniqueness of each row?

Primary key.

What does referential integrity guarantee?

A foreign key is either NULL or matches a primary key value in the parent table.

What is the informal rule of Third Normal Form (3NF)?

Non-key attributes must depend only on the whole key and nothing transitively on the key.

What is the Boyce-Codd Normal Form (BCNF) rule?

Every non-trivial functional dependency has a superkey on its left side.

Which relational model properties are true? (rows / columns)

Rows are unique; column order is irrelevant.

Can there be multiple candidate (unique) keys in one table?

Yes, many candidate keys are allowed; only one is chosen as primary.

Does every foreign key automatically receive an index?

No; primary and unique keys get indexes automatically, foreign keys may not.

Who proposed the relational model?

Edgar F. Codd.

Which company built the first relational DBMS prototype?

IBM.

Which problems appear if a schema is not in 3NF?

Redundancy and insert, delete, update anomalies.

What concept does a functional dependency relate?

Attributes (columns).

Moving to an object table gives what to each row?

Object identity; the row becomes an object of some class and can have methods.

In an ORDBMS, an attribute value can be which complex types?

Atomic value, collection (list/set), or reference to another object.

In ODL, a class is declared with which keyword?

interface.

Which ODL features allow bidirectional navigation and hierarchy?

Inverse relationships and inheritance.

In Visio, how is a non-identifying relationship shown?

Arrow pointing to the ‘one’ side; foreign key not part of child’s primary key.

How is an identifying relationship shown in Visio?

Arrow to the ‘one’ side; foreign key included in child’s primary key.

What do Visio referential actions define?

How INSERT, DELETE, UPDATE propagate between related rows.

In Visio, Cardinality property sets what?

Number of child rows per parent row.

Transforming a binary many-to-many relationship creates how many tables?

One associative table.

Transforming a binary many-to-many creates how many relationships?

Two one-to-many relationships.

What does a cycle in an ERD potentially indicate?

May represent a recursive relationship; not necessarily an error.

In Chen’s notation, what symbol represents a relationship?

A diamond (rhombus).

In Chen’s notation, how is an entity represented?

A rectangle.

In Chen’s notation, how is an attribute shown?

An oval (circle).

Which SQL query correctly finds employees in DALLAS or CHICAGO with salary >1200?

SELECT name, dept, sal FROM emp WHERE (dept='DALLAS' OR dept='CHICAGO') AND sal>1200;

Which clause filters grouped rows after GROUP BY?

HAVING.

Which clause lists the grouping columns?

GROUP BY.

Where is a pass-through query executed?

Directly on the remote DBMS via ODBC, not by Access.

Name two uses of a pass-through query.

Create remote objects (tables, views) and retrieve data from a remote DB.

Which Access option enforces cascading deletes?

‘Cascade Delete Related Records’ in the relationship properties window.

How many join types are offered in Access relationship dialog?

Three.

Give two action query types in Access.

Update query and Delete (or Append/Make-Table) query.

List three default form views in Access.

Single Form, Continuous Form, Datasheet.

Access form controls can be of which binding types?

Bound, unbound, or calculated.

What does the expression If(IsNull([Salary]),0,[Salary]) return?

0 when Salary is Null, otherwise the Salary value.

What structure is the natural way to organize a report in Access?

Groups.

Cross-tab reports rely on which queries?

Crosstab queries and report totals.

What is an Access lookup field?

A list control in a table column storing only allowed values (often foreign keys).

What does ‘Filter by Form’ do?

Allows a user to enter criteria into blank form fields to filter records.

Name two common form events you can program in Access.

On Close and After Update.

Which objects can appear in a WHERE clause reference Forms![FormName]![Control]?

A table or query compared with a form control.

Where can you use Sum([Cost]) in Access?

SELECT list, form footer, detail section, or query design grid field row.

What does the DLookup function do?

Returns a value from another table/query into the current form/report.

What is required to link a subform to a main form?

Matching parent and child fields (usually PK↔FK) in their record sources.

What is the purpose of a TOR (Terms Of Reference) document?

Provides a baseline for further project work.

Name four repository operations supported by CASE tools.

Transformation, versioning, forward engineering, reverse engineering.

List the MSF risk model phases.

Analysis, Planning, Tracking, Control.

Which two main phases exist in the MSF process model?

Planning Phase and Building (Creating) Phase.

Who in MSF is accountable for delivery within project constraints?

The Program Manager.

Which four aspects make up MSF Enterprise Architecture?

Business, Information, Applications, Technology.

Which three design perspectives are defined in MSF?

Conceptual, Logical, Physical.

Name the three-tier layers in MSF application model.

Presentation layer, Business layer, Data layer.

Which Access VBA elements help error handling?

On Error statement, CancelEvent action, Form events like On Error or NotInList.

What is an ADO Recordset?

An object (Recordset) representing a set of records returned by a query.

Which object is the root of the ADO hierarchy?

Connection.

What does the DoCmd object provide in Access VBA?

Methods that execute database commands (OpenForm, ApplyFilter, Requery…).

Name two methods that synchronise data between forms.

DoCmd.ApplyFilter and DoCmd.Requery (or DoCmd.OpenForm with criteria).

Which actions can speed up an Access application?

Database Performance Analyzer, adding/removing indexes, splitting/merging tables, converting to .mde, removing unbound images.

Which Java package gives JDBC classes?

java.sql.

What is DriverManager responsible for in JDBC?

Managing the list of available database drivers.

Give two methods of java.sql.Statement.

executeQuery and executeUpdate.

Give two methods of java.sql.ResultSet.

next and getString.

Which class owns the commit method in JDBC?

java.sql.Connection.

How do you model a many-to-many relationship in Visio?

Create an associative entity and connect it to both original entities with two one-to-many relationships (associative entity on the ‘many’ side).

What type of relationship exists between COUNTRY and RIVER (flows through)?

Many-to-many; requires an associative entity.

How many tables are needed to model floors and rooms with 1-to-many?

Two (Floors and Rooms).

Which SQL operator should you use to test for NULL?

IS NULL (or IS NOT NULL).

Write an Oracle SQL query to return departments with more than 3 employees and their average salary.

SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING COUNT(*) > 3;

What is an example of pessimistic concurrency control?

Locking (exclusive locks until transaction completes).

What two mechanisms protect data from unauthorized access?

User accounts/passwords and granting object privileges.

Where are Access system tables (metadata) stored?

In special hidden tables inside the same database file.

Define client-server architecture.

Division of application tasks between a service provider (server) and a requester (client).

Can a single machine host multiple clients and servers?

Yes; many server and client processes can coexist on one computer.

Which SQL clause may still appear inside a subquery when GROUP BY is used?

GROUP BY is allowed, and SELECT list must contain grouped columns or aggregates.

Which Access control refreshes data in a list or combo box?

The Requery method.

What is a collection in VBA/ADO terms?

An object representing an ordered set of elements accessed by index or key.