4.4 Suppose you receive the following two tables. DEPARTMENT (DepartmentName. Bu

Question

4.4 Suppose you receive the following two tables.
DEPARTMENT (DepartmentName. Budget Code)
Employee (EmployeeNumber, EmployeeLastName, EmployeeFirstName, Email, DepartmentName)
and you conclude that EMPLOYEE.DepartmentName is a foreign key to
DEPARTMENT.DepartmentName. Show SQL for determining whether the following referential
integrity constraint has been enforced:
DepartmentName in EMPLOYEE must exist in DepartmentName in DEPARTMENT

4.10 Suppose you are given a table:
EMPLOYEE_DEPARTMENT (EmployeeNumber, EmployeeLastName, EmployeeFirstName, Email, DepartmentName, BudgetCode)
and you wish to transform this table into the two tables:
DEPARTMENT (DepartmentName, BudgetCode)
EMPLOYEE (EmployeeNumber, EmployeeLastName, EmployeeFirstName, Email, DepartmentName)
Write the SQL statements needed for filling the EMPLOYEE ND DEPARTMENT tables with data from EMPLOYEE_Department.

Explanation / Answer

To determine an employee’s department name, you compare the value in the
DEPARTMENT_ID column in the EMPLOYEES table with the DEPARTMENT_ID values
in the DEPARTMENTS table. The relationship between the EMPLOYEES and
DEPARTMENTS tables is an equijoin—that is, values in the DEPARTMENT_ID column
on both tables must be equal. Frequently, this type of join involves primary and foreign key
complements Use a decision matrix for simplifying writing joins. For example, if you want to display the
name and department number of all the employees who are in the same department as
SELECT employees.employee_id, employees.last_name,
employees.department_id, departments.department_id,
departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;
The SELECT clause specifies the column names to retrieve:
employee last name, employee number, and department number, which are columns in
the EMPLOYEES table department number, department name, and location ID, which are
columns in the DEPARTMENTS table.
The FROM clause specifies the two tables that the database must access:
EMPLOYEES table
DEPARTMENTS table
The WHERE clause specifies how the tables are to be joined:
EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID Because the DEPARTMENT_ID column is common to both tables, it must be prefixed by
the table name to avoid ambiguity.
Using the AND Operator
In addition to the join, you may have criteria for your WHERE clause to restrict the rows
under consideration for one or more tables in the join. For example, to display employee
Matos' department number and department name, you need an additional condition in the
WHERE clause.
SELECT last_name, employees.department_id,
department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id
AND last_name = 'Matos';
All data stored in a database must adhere to certain business rules.
For example, there may be a business rule specifying a minimum hourly wage for any employee or another rule stating that the discount for sale items cannot be more than 100%.
In either case if an INSERT or UPDATE statement attempts to violate the integrity rule, Oracle must roll back the statement and return an error.

Integrity Constraints

An integrity constraint defines a business rule for a table column. When enabled, the rule will be enforced by oracle (and so will always be true.) To create an integrity constraint all existing table data must satisfy the constraint.

Default values are also subject to integrity constraint checking (defaults are included as part of an INSERT statement before the statement is parsed.)
If the results of an INSERT or UPDATE statement violate an integrity constraint, the statement will be rolled back.

Integrity constraints are stored as part of the table definition, (in the data dictionary.)
If multiple applications access the same table they will all adhere to the same rule.

The following integrity constraints are supported by Oracle:

NOT NULL
UNIQUE
CHECK constraints for complex integrity rules
PRIMARY KEY
FOREIGN KEY integrity constraints - referential integrity actions: – On Update – On Delete – Delete CASCADE – Delete SET NULL

Related Questions

Navigate

• Browse (All)
• Browse 4
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.