Answer to Question #253596 in Databases | SQL | Oracle | MS Access for Sonu

Question

Entities. Attributes. Customer-

Customer Number, Name, Address, Telephone Number, Payment Number, Installation Number.  

Facility-

Facility Number, Employee Number, Equipment Number, Type.

Payment-

Amount, Payment Date, Customer Number, Facility Number, Payment Method.

Installation-

Address, Type, Name, Installation Number, Customer Number, Employee Number.

Equipment-

Inventory Number, Category, Type, Facility Number, Staff Number.

Staff-

Staff Number, Type, Name, Period, Telephone Number, Equipment Number.

Employee-

Employee ID, Designation, Address, Name, Salary, Facility Number, Telephone Number.

1.1.Check whether the provided logical design is normalized. If not, normalize the database by removing the anomalies.

Answer 1

This database plan was not normalized, and it is normalized now.

Many-to-many and one-to-many relationship bugs have been fixed

For example, there were columns Payment Number, Installation Number in the table Customer. But one customer can have many installations and payments, but one payment or installation depends only on one customer. So, the tables Payment and Installation have the columns Customer, but the customer is not bound to the one payment or installation.And facility do not depend on equipment or employee, so it should not have these columns, but the equipment should have the column of facility as unequivocally relation to the facility, facility-to-equipment as one-to-many. And equipment-staff is many-to-many, so it should have pivot table and they should not have the ids of each other in own tables. One staff person can work with different equipment, and one equipment could be used by different staff. Employee is independent table of the one working person and has its contact information. Staff is a pivot table between the employee and facility, it shows affiliation of employees to facilities, creating the pivot entity - staff as certain employee of certain facility, many-to-many. It characterizes the employee as a staff of facility, has the attributes which this semployee has only for the certain designation of the certain facility. Staff Number identifies the certain position of the certain staff in the certain facility as unique, so we need that primary key. By such methods we have reduced the redundancy deleting the repeated columns of name and telefone number of employee being a staff. Reducing of such redundancy reduces the anomaly of editing and removing. And also we reduced the insert anomaly when we need to insert the equipment and must point the staff, but it can be not belonging to certain staff, so we deleted the staff column. And we deleted the payment and installation column of customer, because at the moment of inserting the customer may have not done the payment or installation.

Customer

Customer Number, Name, Address, Telephone Number.  

Facility

Facility Number, Type.

Payment

Payment Number, Amount, Payment Date, Customer Number, Facility Number, Payment Method.

Installation-

Installation Number, Name, Address, Type, Customer Number, Staff Number.

Equipment

Inventory Number, Category, Type, Facility Number.

Staff

Staff Number, Employee ID, Type, Designation, Salary, Period, Facility Number.

Equipment-Staff

Equipment-Staff ID, Inventory Number, Staff Number.

Employee

Employee ID, Address, Name, Telephone Number.