- 1 Create An Entity Relationship E R Model For A New Company That Recently Started A Business Where They Buy Highly Di 1 (66.58 KiB) Viewed 119 times
1) Create an Entity-Relationship (E-R) model for a new company that recently started a business where they buy highly discounted items in bulk from different companies who are going out of business or need to get rid of additional items to save warehouse space. This new company has retail stores in big cities across the country where they sell the items to individuals. They also do business as wholesalers where they sell items in bulk to local retailers in small cities. Therefore, they have two types of customers: individuals and small retailers. The company has four departments: purchase, warehouse, sales (retail and wholesale) and accounting. They employ five different types of employees: managers, accounting staffs, warehouse staffs, retail associates, and purchase agents. Each staff, agent and associate works under a manager of the respective department. Purchase agents hunt for discount items, and they get a commission based on the items they are successfully able to buy. You have freedom to make necessary assumptions about relations and attributes to create a realistic model. However, you must incorporate at least one composite and one multivalued attribute in the model. 2) Draw your model in LucidChart or any other similar software. However, you must follow the notations and conventions that have been primarily demonstrated in the textbook and the lecture slides. You will not receive any marks if you fail to follow this. In the design, identify all primary keys and mapping cardinalities correctly. 3) Convert all entity and relationship sets into relation schemas. You do not have to draw the schema. Just write the schema names followed by attribute names. E.g, schemalatti, att2,att3). You do not need to identify foreign keys. 4) If any of the relation schema representing a relationship set is redundant, remove that. Mention your justifications 5) After removing redundancies, for each relation schema list a reasonable set of functional dependencies that should exist. 6) For each functional dependency in each schema, determine whether it fulfills the one of the three rules of the Third Normal Form (3NF). Page 2 of 2 Submit one pdf document that will contain: a. E-R model drawn using Lucidchart b. Initial set of relation schemas c Reduced set of relation schemas (with explanations) d. Functional dependencies for each schema in the reduced set e. Comments on whether each functional dependency fulfils one of the 3NF for the given schema