Exam 3: The Relational Data Model

Count,min,max,average and sum are all ___________________ functions that can be used to summarize groups of rows of an input table.

A relationship in which a foreign key refers to the same table and represents an association among the members of the same set is known as a(n)__________________ relationship.

Candidate keys that are not primary keys are declared with the DISTINCT keyword in the CONSTRAINT clause of the CREATE TABLE statement.

One specialized use of the union,intersection and difference operators is to combine tables distributed over multiple locations.

For each column in a table,the ___________________ defines a set of values and permissible operations on those values.

The following data are in the tables Employee (primary key: Empno) and Department (primary key: Deptno). The questions refer to row numbers of this table. The first row of each table is row #1. Empdeptno in table Employee is a foreign key that refers to Deptno in the table Department. Deptmgr in the table Department is a foreign key that refers to Empno in the table Employee. Table~Employee\\ \begin{array}{|l|l|l|l|l|} \hline \text { Empno } & \text { Empname } & \text { Empdeptro } & \text { Empsalary } & \text { Emptitle } \\ \hline 555 & \text { Keamy } & 200 & 7000 & \text { manager } \\ \hline 666 & \text { Williams } & 100 & 8000 & \text { manager } \\ \hline 777 & \text { Haberle } & 200 & 5000 & \text { clerk } \\ \hline 888 & \text { Haberle } & 200 & 5000 & \text { clerk } \\ \hline & \text { Johnson } & 300 & 4000 & \text { analyst } \\ \hline 889 & \text { Dupont } & 400 & & \\ \hline & \text { McCabe } & 400 & 6000 & \text { programmer } \\ \hline 999 & \text { Lopez } & & 9000 & \text { manager } \\ \hline \end{array} Table~Department\\ \begin{array}{|l|l|l|l|} \hline \text { Deptno } & \text { Deptname } & \text { Deptcity } & \text { Deptmgr } \\ \hline 100 & \text { Logistics } & \text { Wichita } & 666 \\ \hline 200 & \text { Finance } & \text { Boise } & 555 \\ \hline 300 & \text { Marketing } & \text { Pierre } & 666 \\ \hline \end{array} -Which of the following statements is true?

A(n)__________________ is a connection between rows in two tables,which is shown by column values in one table that match column values in another table.

A(n)_________________ set operator retrieves all rows in either input table.

The Intersection of a table of N rows and of a table of M rows (M<=N)has the following number of rows:

A(n)_________________ is an operator that includes in the result table the matching rows of the input tables and the nonmatching rows from both input tables.

The ________________ operator compresses a table by replacing groups of rows with individual rows containing calculated values.

In the project operation of relational algebra,when a subset of columns is retrieved and the resulting table contains duplicate rows,the project operator will automatically remove the duplicate rows.

An unknown value,or a value that does not apply to a given row of a table,is indicated by:

Table~Employee\\ \begin{array}{|l|l|l|l|l|} \hline \text { Empno } & \text { Empname } & \text { Empdeptno } & \text { Empsalary } & \text { Emptitle } \\ \hline \end{array} Each row of the table Department describes a department. Deptno is the primary key. Deptmgr (Department manager) refers to Empno in table Employee. Table~Department\\ \begin{array}{|l|l|l|l|} \hline \text { Deptno } & \text { Deptname } & \text { Deptcity } & \text { Deptmgr } \\ \hline \end{array} Each row of the table Project describes a project. The primary key is Projno. Projmgr (project manager. refers to Empno in the table Employee. Table~Project\\ \begin{array}{|l|l|l|} \hline \text { Projno } & \text { Projname } & \text { Projmgr } \\ \hline \end{array} The table Projstaff indicates the staff assigned to various Projects. Table~Project\\ \begin{array}{|l|l|l|l|l|} \hline \text { Prstno } & \text { Prstempno } & \text { Prsthours } & \text { Prstdate } & \text { Prstasgtno } \\ \hline \end{array} The combination of columns Prstno, Prstempno is a candidate key. There could be other candidate keys. Prstempno is the Empno in table Employee of the employee working on project number Prstno, which refers to Projno in the table Project. The meaning of Prstasgtno is left undefined. -The relationship connecting Employee to Project and expressing that an employee works on a project (Link table Projstaff)is:

In a relational database,each table has a heading or definition part and a body or content part.

A candidate key is indicated in SQL with the use of the following keywords in a CREATE statement:

In relational algebra,the _________________ operator shows everything possible from two tables.

The following data are in the tables Employee (primary key: Empno) and Department (primary key: Deptno). The questions refer to row numbers of this table. The first row of each table is row #1. Empdeptno in table Employee is a foreign key that refers to Deptno in the table Department. Deptmgr in the table Department is a foreign key that refers to Empno in the table Employee. Table~Employee\\ \begin{array}{|l|l|l|l|l|} \hline \text { Empno } & \text { Empname } & \text { Empdeptro } & \text { Empsalary } & \text { Emptitle } \\ \hline 555 & \text { Keamy } & 200 & 7000 & \text { manager } \\ \hline 666 & \text { Williams } & 100 & 8000 & \text { manager } \\ \hline 777 & \text { Haberle } & 200 & 5000 & \text { clerk } \\ \hline 888 & \text { Haberle } & 200 & 5000 & \text { clerk } \\ \hline & \text { Johnson } & 300 & 4000 & \text { analyst } \\ \hline 889 & \text { Dupont } & 400 & & \\ \hline & \text { McCabe } & 400 & 6000 & \text { programmer } \\ \hline 999 & \text { Lopez } & & 9000 & \text { manager } \\ \hline \end{array} Table~Department\\ \begin{array}{|l|l|l|l|} \hline \text { Deptno } & \text { Deptname } & \text { Deptcity } & \text { Deptmgr } \\ \hline 100 & \text { Logistics } & \text { Wichita } & 666 \\ \hline 200 & \text { Finance } & \text { Boise } & 555 \\ \hline 300 & \text { Marketing } & \text { Pierre } & 666 \\ \hline \end{array} -Which of the following statements is true?

The referential integrity rule ensures that people,things and events are uniquely identified in a database.

A(n)_________________ set operator retrieves just the common rows of both input tables.