Suppose that, after the requirements collection and analysis phase, the database designers stated the following de******ion of the "miniworld"- to be represented in the Company Supplier-Part database:
1. The company is organized into departments. Each department has a unique name, a unique number. A department may have several locations, and may have number of employees work in it.
2. Each employee is identified by unique number (EMP#), and we store the employee's name, address, salary, gender, and birth-date. An employee is assigned to one department but may work on several project. We keep track of the number of hours per week that an employee works on each project.
3. We want to keep track of the dependents of each employee for insurance purposes. We keep each dependent's name, sex, birthdate, and relationship to the employee.
4. Each project has a unique name, a unique number (PRJ#), and a single location, and a particular employee who manages the project. We keep track of the start date when that employee began managing the project.
5. We keep track of the suppliers (more accurately, suppliers under contract), who supply parts (more accurately, kinds of parts required by a project) to the projects. Each supplier has a unique supplier number (SPL#), a supplier name; rating or status value (STATUS); and a location (CITY). We assume that each supplier is located in exactly one city.
6. Each kind of part has a part number (PRT#), which is unique; a part name, a color, weight, and a location where parts of that kind are stored (CITY). We assume where it makes any difference that part weights are given in kilograms. We also assume that each kind of part comes in exactly one color and is stored in a warehouse in exactly one city.
7. We keep track of each shipment of parts by the supplier to a project, and we store the quantity of each part in a particular shipment.
8. The supplier may supply many parts and a part may supplied by several suppliers.