How to complete the ERdiagram by adding all missing constrai

How to complete the ER-diagram by adding all missing constrains and transform it into a relational schema which doesn\'t contain redundant fields and tables?

Category

The ID and the name of a category are both unique.

All categories are identified by their ID.

Manufacturer

The ID of a manufacturer is unique and all manufacturers are identified by their ID.

Some manufacturers may share the same name, phone number or address.

Part

The ID of a part is unique and all parts are identified by their ID.

Some parts may have the same name, price, available quantity, warranty, manufacturer ID or category ID.

Salesperson

The ID of a salesperson is unique and all salespersons can be identified by their ID only.

Some salespersons may have the same name, address, phone number or years of experience.

Transaction Records

The ID of a transaction is unique and all transactions can be identified by their ID only.

Each transaction corresponds to the sale of one part.

Some salesperson may never sell any part and some parts may have never been sold.

A part can be sold only if it is still available (i.e. available quantity > 0).

A salesperson can sell the same part more than once by having more than one transaction.

There may be more than one transaction in one day.

After a salesperson sells a part, the system should reduce the available quantity of that part by one and add a transaction record accordingly.

ManufacturerManufacturer Name Address CategoyIDCategory Name Manufacturer ne Number Manufacturer Category Part Name Part Price nufacturi PartID Salesperson Name art Manufacture ID having Salesperson Address Salesperson Part Category ID Salesperson one Number Part Warranty Part Salesperson Experience Part Available QuantityEntity ype maintains ansactionRecor ds Salesperson ID PartiD TransactionDate

Solution

Converting an E-R diagram to a relational schema

1. (Non-weak) Entity sets: Create a relational schema with same name, and same attributes. The unique key of the entity set is used as the primary key of the relation

Category (Category ID, Category Name)

Manufacturer (Manufacturer ID, Manufacturer Name, Manufacturer Address, Manufacturer Phone Number)

Part (Part ID, Part Price, Part Manufacturer ID, Part Category ID, Part Warranty, Part Available Quantity)

Salesperson (Salesperson ID, Salesperson Name, Salesperson Address, Salesperson Phone Number, Salesperson Experience)

Transaction Records (Transaction Records ID, Part ID, Salesperson ID, Transaction Date)

2. Relationships: Relationship between weak entity-set and strong entity-set doesn’t need to be represented separately. But here all the entities are strong entites.

If there is many-to-many mapping, union of all entity-set primary keys becomes primary key of relationship-set.

If there is one-to-one mapping, either of the entity-set’s primary key is acceptable.

For many-to-one or one-to-many mappings primary key of entity-set on “many” side is primary key of relationship

manufacturing (Manufacturer ID, Part ID)

having (Category ID, Part ID )

maintains (Salesperson ID, Transaction Records ID )

type (Part ID, Transaction Records ID, Salesperson ID )