Canadian Pacific Railway is planning to computerize the inventory management operations of its' train engine repair center.
Question:
Canadian Pacific Railway is planning to computerize the inventory management operations of its' train engine repair center. Following details were captured by the system analysts who performed the requirement gathering process.
• Each engine is identified by a unique ID and has a name, country of origin, date purchased, and the mileage. • The information of the mechanics who repair the engines are also stored in the system. Their name, employee ID, SIN, date of birth, age, address, home telephone number, Employment start date, current position, and specialization should be stored in the database.
• The information about each repair performed on the trains should be recorded. The information includes the mechanic who performed the repair, the parts used for the repair, and the start time and end time of the repair. • Parts are identified by the unique ID numbers and details such as part type, part name, and the quantity of parts available in the store should be kept in the database.
• Each mechanic (except the most senior mechanic) is managed by another mechanic. The manager of a mechanic may change from time to time. The repair work completed by the mechanic is verified by his/her manager. The history of this verification must be kept for all repair works. The most senior mechanic does not perform repairs.
• The system should automatically list the parts that need to be reordered at the end of each day. A part will be reordered if the quantity in hand falls below the reorder quantity for that part. The quantity reordered is stored in the database for each part. • There will be several suppliers for each part and the supplier will be selected based on the availability of parts with the supplier and the price.
• The details such as company name, company phone number(s), main contact person (MCP), MCP phone number, MCP mobile number should be stored about each supplier. However, the details about the supply orders provided by the suppliers will not be stored in this initial version of the system.
• The suppliers may have affiliations with sub-contractors, who may deliver parts to the CGR on the supplier's behalf. One sub-contractor is affiliated only with a single supplier and a supplier may have many sub-contractors. The details such as company name, company phone number(s), main contact person (MCP), MCP phone number, MCP mobile number should be stored about each sub-contractor as well.
1) Generate the EER model of the database for above requirements. State any assumptions made. Provide the following information with your EER diagram.
• Indicate the primary key of each entity and the cardinality of each relationship.
• Mark any weak entities present in your design.
• List candidate keys for each entity in the EER diagram.
• Identify any constraints not captured by the EER diagram.
• Clearly indicate derived, composite and multi-valued attributes present in your design.
2) Perform the logical database design process to convert your ER model not a relational model.
3) Convert the relational model obtained into 3rd normal form (if it is not in 3rd normal form).
Income Tax Fundamentals 2013
ISBN: 9781285586618
31st Edition
Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill