You are given a table with sample data Your task is to norma

You are given a table with sample data. Your task is to normalize the tables. Your solution should not show tables with data. Instead, represent each table in this format: TABLENAME (column, column, column … , column) Underline the primary key for each table you create. If the table has a foreign key, it should be italicized.

A salesperson can be involved in many orders. Usually a given order will be handled by only one salesperson. But complex orders may involve several salespersons. If you know the Salesperson number, you can figure out the salesperson’s last name and the department in which they work. While a department might have many salespersons it has just one manager. If you know an Order number, you will know the order date and order total. Knowing just an order date reveals nothing important.

Salesperson Number

Salesperson Lname

Dept ID

Dept Manager LName

Order Number

Order Date

Order Total

134

Katz

003

Davis

0112

10/15/11

$403.00

137

Smith

004

Johnson

0117

10/16/11

$135.00

134

Katz

003

Davis

0121

10/16/11

$338.50

c.   Specify the referential integrity constraints that must be maintained by the database now that you have broken the original table into smaller tables.

Solution

When to Enforce Business Rules with Integrity Constraints

You can enforce rules by defining integrity constraints more reliably than by adding logic to your application. Oracle can check that all the data in a table obeys an integrity constraint faster than an application can.

Example of an Integrity Constraint for a Business Rule

To ensure that each employee works for a valid department, first create a rule that all values in the department table are unique :

ALTER TABLE Dept_tab
    ADD PRIMARY KEY (Deptno);

Then, create a rule that every department listed in the employee table must match one of the values in the department table:

ALTER TABLE Emp_tab
    ADD FOREIGN KEY (Deptno) REFERENCES Dept_tab(Deptno);

When you add a new employee record to the table, Oracle automatically checks that its department number appears in the department table.

To enforce this rule without integrity constraints, you can use a trigger to query the department table and test that each new employee\'s department is valid. But this method is less reliable than the integrity constrain, because SELECT in Oracle uses \"consistent read\" and so the query might miss uncommitted changes from other transactions.

When to Enforce Business Rules in Applications

You might enforce business rules through application logic as well as through integrity constraints, if you can filter out bad data before attempting an insert or update. This might let you provide instant feedback to the user, and reduce the load on the database. This technique is appropriate when you can determine that data values are wrong or out of range, without checking against any data already in the table.

Creating Indexes for Use with Constraints

All enabled unique and primary keys require corresponding indexes. You should create these indexes by hand, rather than letting the database create them for you. Note that:

Constraints use existing indexes where possible, rather than creating new ones.
Unique and primary keys can use non-unique as well as unique indexes. They can even use just the first few columns of non-unique indexes.
At most one unique or primary key can use each non-unique index.
The column orders in the index and the constraint do not need to match.
If you need to check whether an index is used by a constraint, for example when you want to drop the index, the object number of the index used by a unique or primary key constraint is stored in CDEF$.ENABLED for that constraint. It is not shown in any catalog view.
You should almost always index foreign keys, and the database does not do this for you.