Exam 3: The Relational Data Model

The primary key of a table is chosen among:

The ____________________ rule states that each table must have a column or combination of columns with unique values.

A relational algebra operator produces the following result on the table Department: Deptno Deptname Deptcity 100 Logistics Wichita 200 Finance Boise 300 Marketing Pierre That operator is:

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. -To retrieve the projects in which all employees participate,the following operator would be used:

The natural join operator provides the ability to include nonmatching rows in the result table as well as matching rows.

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} -The relationship connecting Employee to Department and expressing that an employee belongs to a department (foreign key Empdeptno)is:

The relational algebra operator that retrieves a subset of the rows of the input table satisfying a given condition is the _________________ operator.

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. -To retrieve the projects in which any employee participates,the following operator would be used:

A(n)_________________ set operator retrieves the rows that occur in the first input table but not in the second input table.

When the extended cross product operation is performed on a table of N rows and a table of M rows,the resulting table has the following number of rows:

Only a limited number of simple statistical functions can be used to summarize groups of rows in a query.

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

Unary relationships involve multiple tables and are common in database design.

To ensure that a database contains valid connections,the _____________________ rule states that the values of columns in one table must match the values of columns in other tables.

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. -Which of the following columns of the table Department is a foreign key?

The extended cross product operator can only combine two tables that are union compatible.

To perform a difference operation on two tables,the tables do not have to be union compatible.

The referential integrity rule prohibits foreign keys to have null values.

A row in a child table having a foreign key value identical to the primary key value of the parent table is known as a(n)___________________ row.

The tables Dept1 and Dept2 are as follows Table~Dept1\\ \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} Table~Dept2\\ \begin{array}{|l|l|l|l|} \hline \text { Deptno } & \text { Deptname } & \text { Deptcity } & \text { Deptmgr } \\ \hline 200 & \text { Finance } & \text { Boise } & 555 \\ \hline 300 & \text { Marketing } & \text { Pierre } & 666 \\ \hline 400 & \text { Sales } & \text { Reno } & 666 \\ \hline \end{array} -A relational algebra operator applied to tables Dept1 and Dept2 produces the following result: Deptno Deptname Deptcity Deptmgr 200 Finance Boise 555 300 Marketing Pierre 666 The operation is: