School of CS Western Illinois University

IS 342 AMARAVADI

THEORY REVIEW QUESTIONS

PLEASE NOTE THAT THESE ARE ONLY EXAMPLES OF QUESTIONS THAT MIGHT BE ASKED ON THE MIDTERM. THERE IS NO IMPLICATION THAT THESE WILL BE THE QUESTIONS THAT WILL APPEAR ON THE MIDTERM.

1.  Is functional dependency concerned with tableships among entity classes?

No, its concerned with tableships among attributes

2.  Would a table that is in 2nd NF have repeating groups?

No, in the 2nd NF, we will probably have transitive dependencies, a table with repeating groups will be unnormalized.

3.  Can a candidate key be a primary key?

Yes, that’s the definition of a candidate key, a key that can serve as a primary key.

4.  What are the inputs to database logical design?

The outputs of database analysis will be inputs to the logical design. These will include ER charts and descriptions of various types of constraints.

5.  What are the outputs of database logical design?

The outputs are a set of normalized tables.

6.  Why do anomalies occur?

Anomalies are a result of poorly designed tables i.e. if FDs are not properly considered or table is ill-structured or does not have a proper primary key.

7.  If there is a FD: A à B, then can B be associated with many values of A?

Yes, A à B implies that for each instance of A there is one occurrence of B. But typically, each instance of B will be associated with multiple values of A (The secondary key effect).

8.  Why are repeating groups undesirable?

Repeating groups lead to anomalies in search, update and deletion.

9.  What do atomic values at row column intersection mean?

Single values e.g. JD-550 in each cell.

10.  What information do we need in order to be able to retrieve data from a table?

We need information identifying the table, row and column.

11.  What language does “data sub-language” refer to?

DML, since the context in which this occurs is one of CODD’s rules for tableal databases which states that “it should not be possible to bypass the integrity constraints using the data sub language”.

12.  Who is CODD?

Codd was a research fellow at IBM who introduced database principles via the System R.

13.  What are examples of DDL?

Create database, table, index, view; Drop table, index, view; Alter table;

14.  What is DCL?

DCL is concerned with commands to give authorizations or privileges for users to view particular tables or the results of queries.

15.  What does non-subversion mean?

Non –subversion means that we should not be able to use the data sub language to bypass integrity constraints.

16.  What does an online catalog refer to? Why should it be dynamic?

Online catalog refers to data dictionary. It should be dynamic so that the correctness of queries can be checked before execution.

17.  What SQL command is used to change attributes in a table?

Alter Table add emp_salary decimal, …..;

18.  What SQL command is used to create file organizations?

Create index.

19.  What is the “GROUP BY” clause in SQL used for?

To group data into groups and perform summary operations on them.

20.  What is the “LIKE” logical operator in SQL used for?

Used for string comparision as in “Where city like “San%”” to identify all cities beginning with “San”.

21.  Can you include arithmetic in the “SELECT” part of an SQL?

Yes, We can have a statement such as “SELECT Avg(Salary) to find out the average Salary.

22.  What are examples of arithmetic functions in SQL?

Sum, Avg, Max etc.

23.  What is the reflexive rule of functional dependency? Union rule?

n  Attribute determines itself

n  Two FDs having same LHS should be combined.

24.  What level of the 3-schema architecture is addressed by logical design?

logical database design addresses the conceptual level of the three schema architecture.

25.  What does “satisfy requirements” mean in the context of database design?

This means the design should satisfy user needs for queries, reports and forms.

26.  What are some examples of redundancies that can occur in normalized tables?

Duplication of primary key in one table as a cross-reference key in another.

27.  What does a composite key consisting of three attributes (in a database design) indicate?

A three way relationship.

28. What does this diagram mean a à> b ?

For each value of a, there are multiple values of b.

29. What type of functional dependency is indicated here if:

R1(a, b, c) if a -> b -> c?

Transitive dependency.

30. What type of problem is indicated in this tableship if

R1(a, b, c) and a -> b -> c? (a and c have no tableship)

Multi-valued dependency.