Exam 3: The Relational Data Model

When using a full outer join between two tables having some common columns and some unique columns,columns from the first table that do not have corresponding columns in the second table would contain null values and columns from the second table that do not have corresponding columns in the first table would contain null values.

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 or combination of columns may not be a candidate key of the table Projstaff?

M-N relationships cannot be directly represented in the Relational Model,but rather must be represented by two 1-M relationships and a linking or associative table.

In the case of a natural join,where one of the join columns is removed in the result table,it is important for future reference to be able to determine the particular column that will be removed.

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 _______ part of a table consists of the table name and the column names.The ______ body shows the rows of the table.

The _______________ operator produces a table in which the values of a column from one input table are associated with all the values from a column of a second input table.

A divide operator is used to retrieve rows in which the values of one column contain every value in another column.

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 100 Logistics Wichita 666 200 Finance Boise 555 300 Marketing Pierre 666 400 Sales Reno 666 The operation is:

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 is the manager of a department (foreign key: Deptmgr)is:

Each operator of relational algebra transforms one or more tables into a new table.

Regarding delete and update actions on referenced rows,the CREATE TABLE statement allows you to restrict or cascade the action,but does not allow to specify a new value for the foreign key of the related rows.

A disadvantage of using the extended cross product operator by itself is that it often generates excessive data consisting of many rows of meaningless combinations.

An advantage of using a constraint name in the CONSTRAINT clause of the CREATE TABLE statement is that the constraint will be easy to identify when a violation occurs.

Using the extended cross product operator on tables M and N,the numbers of rows in the result table can be computed as M + N,while the number of columns can be computed as M*N.

Because they are commonly occur,and contain columns that do not contribute to the uniqueness of a key,it is usually important to identify all possible minimal superkeys when designing a table.

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

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 relational algebra operator produces the following result on the table Department: Deptno Deptname Deptcity Deptmgr 100 Logistics Wichita 666 300 Marketing Pierre 666 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. -If the combination Prstno,Prstempno is chosen as the primary key for table Projstaff,setting Prstno to null in some row would be:

With regards to referential integrity constraints,a row is considered referenced if there are rows in a child table with foreign key values identical to the primary key value of the parent table.