Question

You are tasked to design the ER diagram for the database for the 3S clothing store. Data requirements are as given below.
• Information about 3S employees includes a unique employee ID, and his/her name and position.
• Each customer of 3S is identified by a customer ID, and has a name.
• Information about the suppliers of 3S include a unique name, and a contact phone number.
• Clothes stocked by 3S are described by a product code, size, colour, set price and stock quantity.
• Details of clothing brands need to be kept, including a unique brand name and any designer information.
• A piece of clothing must have at least one supplier and each supplier can supply many clothes. When a supplier supplies a piece of clothing, data includes a supply price, and the quantity and date of the supply. If a supplier provides several supplies for a piece of clothing, only data for the most recent supply is kept.
• Customers order clothes. Details of an order include date of the order and quantity of the clothes s/he orders. A customer can place several orders on a day and the details of each order need to be kept.
• Some customers are VIP customers who are issued a membership number. While a VIP customer is serviced by one service specialist (who is an employee). A service specialist cares for several VIP customers.
• VIP events about brands are organised regularly, including details such event date and format. A brand can be included in several events and an event can promote several brands. A service specialist promotes events to his/her VIP customers.
Examples of queries required by 3S:
• Look up the details of suppliers, service specialists, customers and clothes for daily operation.
• Check the stock quantity of clothes and contact suppliers for a new supply if needed.
• Find out details of events for different brands.
According to the data requirements and query requirements above, give the ER diagram for the database using the UML class diagram symbols (as used in the lecture notes and tutorials), making appropriate assumptions where necessary. You must represent entity types, relationships and their attributes, and all applicable constraints in your diagram. Explain any constraints that are not be expressed in the diagram. Note that your ER diagram would be mapped to a relational database schema and implemented as tables using a DBMS. It may be helpful that you do this mapping and see if the queries can be answered using the mapped relations to help you refine the ER diagram.

Answer 1

COMMENT FOR ANY QUERIES

PLEASE GIVE AN UPVOTE