(1) Instructions: - Use draw.io to build your diagram (a hand-written version will not be allowed). - Please bring a printout to the class on the 26th of February (Wednesday). Ensure that your name is included in the printout. - Please take a screenshot of your diagram and submit the file on D2L (Under the Assignments section). The deadline for the submission will be the same (by 02/26 9:30am). - Use either Chens or Crow Foot notation (do not mix). - Identify primary keys for all the entities in your diagram (If a specific requirement is not given, make your own assumptions for identifying primary keys. State your assumptions in an appendix.). - Identify cardinalities (both the minimum and the maximum) for all the relationships in your diagram (If a specific requirement is not given, make your own assumptions for identifying cardinalities. State your assumptions in an appendix.). - There are two levels of business rules given: basic and advanced. If you feel confident about your diagram for the basic rules, you do not necessarily have to deal with the advanced ones. The advanced ones are for giving you an opportunity to make up. In other words, the total number of points for this assignment is 100. Even if you get 100 pts from the basic and 5 pts from the advanced, your score will be 100.

(2) Basic Business rules (100 pts):

A fast-growing retail company EMart recently realized that they can no longer rely on a traditional file system that has been used since the establishment of the company. They approached and asked you to build a database consistent to the companys day-to-day operations. You have talked to multiple employees from different departments and come up with the following business rules.

First of all, EMart wants to keep good track of their employees. When an employee gets hired from the company, s/he is given with an employee number which will be used for an identification purpose. In addition, email, phone number, address, emergency contact, gender, and date of birth will be recorded for each employee. Note that company wants their employees to provide as many emergency contacts as possible. Employees work for different parts of the company. For instance, there are salespersons who manage important customers such business customers. To facilitate large-quantity sales, EMart provides salespersons with special funds for lobbying. The company will track the total amount of funds provided as well as the amount of funds used for each salesperson. In addition, the amount of funds left for use will be calculated and stored. On the other hand, marketers deal with potential customers. Their job is to contact local businesses that have not yet built any relationships with the company and to transform into future customers. To provide performance-based bonuses to these employees, Emart tracks the total number of new customers each marketer has attracted. Note that there are still many employees who cannot be categorized into the above two subgroups. Also, a salesperson cannot be a marketer at the same time.

Second, EMart has a scholarship program, as a part of company welfare, for the children of their employees. To do so, they store information on each employees children. Since the scholarship is provided only to those between age from 9 to 17, the company wants to keep good track of the childrens age. Therefore, if an employee needs to get a scholarship for his/her a child (or children), s/he has to provide the birth certificate number to the company. Based on the number,

the company will calculate ones age and store it in the database. In addition, the company requires employees to enter bank account numbers so that the scholarship can be automatically transferred to those who qualify.

Third, customer information is essential to the company. The information that EMart needs to keep track of includes customer name, address, phone number, email, gender, and date of birth. While the company is interested in managing the current customers, they are also focusing on attracting potential customers (one who has not yet made a purchase but interested in the company). Especially, the company asks every individual, categorized as a potential customer, why one got interested in the company. There can be multiple reasons for this, and the company stores all of them in their database. As mentioned above, among the current customers, important ones are managed by salespersons. For each salesperson, there can be multiple customers to be managed. On the other hand, a customer can only be managed by one salesperson. Also, potential customers are target-marketed by marketers. A marketer can place target advertisement to many potential customers, but a potential customer can be targeted by only one marketer.

Customers place orders. A customer can place multiple orders, but an order can be placed by only one customer. Note that, since EMart keeps track of potential customers, they want their database system to allow customers to be in the system without placing any orders. For each order, the company stores information such as order number, date, and total price. Each order consists of a single or multiple product(s). For each product in an order, the company wants to know its quantity, unit price, and total price. Products have their own product number and category. Other information for products, such as the country of origin and description, is also stored.

(3) Advanced business rules (additional 10 pts)

Every product in EMart is delivered directly from factories around the world. To reduce the cost of the delivery, EMart is in contracts with multiple logistics companies. EMart wants to store data regarding this process. Factories are identified by factory names and locations (i.e., cities). Logistics companies are identified by their names. A factory can supply multiple types of products to EMart. A product manufactured from a factory can be delivered to EMart using only one carrier. However, for the delivery of different products produced within a factory, multiple carriers can be used. For instance, suppose product A and B are produced in factory F. Then, only one carrier is responsible for delivering A to EMart. However, for the delivery of B, F can choose another carrier. Some factories may produce the same product. EMart may procure the same product from multiple factories. For instance, product A can be bought from multiple sites such as F1, F2, and F3. Of the EMarts total purchase of A, 30% may come from F1, 20% from F2, and 50% from F3. A logistic company can deliver multiple products from different factories to EMart.

* Hint: Use the ternary relationship