$3.(35$ pts$)$ Create an E$-$R model for Case Study $5($attached to homework$).$ This should include any assumptions you

make and anything else that you think helps explain, clarify, and validate your model. You do not have to

complete the queries section. The queries section can be used to make decisions on how to structure your E$-$R

model. Case study $5$: Marble factory: Problem Description

Your team is assigned to build a database system for a small factory in Venezuela, called

"Marbles of Venezuela". The factory produces different type of marbles that are sold in the Venezuelan or USA market. This is a small sized company that uses a simple database management $($DBM$)$ system. The factory keeps the information about final products, raw materials, and prices in simple Excel spreadsheets. In the process of extending the business, the management realized the need for re$-$organizing the database.

The factory produces different kinds of products that distinguish from each$-$other by their color, size, and shape. The two main products are Gems and Marbles. There are two different sizes of Gems and six different sizes of Marbles. Both products are offered in $25$ different colors. Each colored Gem is classified as matte or luster Gem. Finished products are delivered in boxes. Each box contains products packed either in nets of different sizes, big plastic bags, or hard plastic holders.

The factory does not have a proper inventory management system. The inventories are handled in the following way: the manager knows roughly the amount of inventory for each final product. Based on previous experiences, the manager decides how much and when to produce. As the business is growing, it is getting difficult to keep track of all the inventories and decide wisely about the production schedule. The company is facing the problem of not being able to deliver the products on the due date. Their first step toward re$-$organizing themselves is building a proper database management system. The new system will enable them to keep track of the inventory level of final products and raw materials and help with production scheduling.

The new database management system keeps track of the inventory level, the backorder level $($amount of final product orders not yet satisfied$),$ and the number of outstanding orders $($raw materials orders that are not received yet$).$ Whenever an order for final products arrives, the inventory of final products should be reduced by the amount in the order. If the final product inventory is not enough to satisfy the order, then production should start. In this case, the raw material inventory should be checked. If there are not enough raw materials in stock, then an order for these raw materials should be placed.

Database Design

The following are the main entity types of the database of this company. For each entity type, we provide some of the corresponding attributes. Use this information in order to: $($a$)$

Build an Enhanced E$-$R diagram;

$1.$ Employee: The main attributes are SSN$,$ name, address, profession, etc.

$2.$ Customer: The main attributes are identification number, name, address, etc.

$3.$ Supplier. The main attributes are identification number, name, address, etc.

$4.$ Raw material: The main attributes are identification number, name, description, quantity on hand, unit price, etc.

$[7$:$56$ PM$,9/11/2024].$: $5.$ Final product: The main attributes are identification number, name, product characteristics $($color$,$ size, etc.$),$ quantity on hand, unit price, etc.

Note the following:

$1.$ For each customer order, the company keeps information about quantity ordered order date, due data, delivery date, and total amount of money charged.

$2.$ For each $($raw materials$)$ order set, the company keeps information about quantity ordered, order date, delivery date, and amount to be paid.