Entity Relationship Diagram

Constraint conflicts occur when different views impose different constraints on certain aspects of the application domain. For example, one view may identify a concept by a particular property (which will influence the choice of primary key); yet another view may identify the same concept using a different property, or even a group of properties. The second step in view integration is to modify the views by resolving the current conflicts. Regarding the synonym conflict, a common and more representative name (product) is given to describe the same concept in the two views.

On the other hand, sale order, and purchase order are utilized to distinguish between orders exist in the two views. The type conflict is eliminated by removing the attribute from the Order entity. Finally, the attribute types are reviewed in order to achieve the consistency and solve the domain conflicts. N-ary Integration is an integration Strategy that is employed in order to combine the views. The three views (sale, stock and purchase) have been integrated in a single global view. A single process is used to combine the similar entities.

For example, the two separate shipment entities in stock and shipment views have been combined into one single entity. This saves storage and increase the accuracy as well as consistency between different entities. Additionally, relationships are created between the entities to eliminate the redundancy in data. The following diagram shows the global schema after the three views (Sales, Purchase and Stock) integration. According to the diagram, the process starts when the Customer places an Order in the sale department. This Order is received by the Sales person.

The Order consists of Order Line which specifies the Products. The Products are stored in the Stock. If the Customer purchases some Products the inventory will automatically be updated to show the new stock level. When the current product available reaches its minimum level, the Stock issues Purchase Order. Depend on the Purchase Order issued by the Stock; the Supplier will send the Shipment to be shipped to the Stock. Upon arrival to the Stock, its reception is recorded and stored in the appropriate place (special dark storage, normal storage).

There are nine entities in the WineZone database which are customers, orders, Sales Person, Order Line, Product, Stock, Purchase Order, suppliers and shipment. Order line is the associative entity. The underlined attributes are the identifying attributes (primary key) that will be used to uniquely identify each entity. The following section will present the relationship between the entities along with the attributes, primary and foreign key: 1. Customer entity is a table in the database. It contains information about the customers.

Many attributes are associated with the Customer entity. Each customer will be given a customer I. D. (primary key)that will never change, and will be used to uniquely identify that customer in the database. The other attributes of the customer entity are name, address, email address, Zip_Code and Tel which introduced to keep trac of the customer. Moreover, the date of birth attribute is important to obtain because of drinking age regulations. The Order entity is another table in the database which used to keep track of the order. This table records a unique Order_ID as a primary key together with Order_Date, Customer_ID and Staff_ID. In order to connect theses two entities, Customer_ID is placed in the Order table as a foreign key.

This will help allocating each order to the customer easily. According to the drawing above, there is one to many relationships between the Customer and Order entities. Each customer may place one or more than one order, while each Order placed by one and only one Customer. It also shows an optional relation occurs between the two entities. For instance; The Customer may or may not place an order. 2. Sales Person entity contains information about the Sales Staff and it consists of attributes which are First_Name, Sir_Name, Address and date_of_Birth.

The primary key is Staff_ID and it used as a foreign key in the Order table to connect the two entities. This will show each staff with the sales order he has served, thus facilitate the process of employee promotion and incentive in terms of performance. It is clear that, there is many to one relationship between the two entities. Each Order is processed by one and only one Sales Person, while each Sales Person may receive one or more than one Order. An optional relation indicates the Sales Person may or may not receive an Order 3. When a customer makes a purchase the quantity is kept in the Order Line table.

Oreder_Line_ID, Order_ID, Quantity and Bar_Code are attributes associated with this entity. Each order line will be given an Order_Line_ID that will never change and will be used to uniquely identify that Order. It is an associative entity to breakdown many to many relationship Order and Product entities. These two tables linked together using a foreign key called Order_ID in the Order Line table. There is one to many relationships between the two entities. Each Order may consist of one or more than one Order Line, whereas each Order Line is part of one and only one Order.