A department of the university has a small car, which members of staff may use for work purposes. They need a database to keep track of who uses the car and any related expenses Design a database for recording the information on the staff member who books the departmental car and the date booked and returned, the car mileage and any claims for the fuel, parking and similar related expenses 1. Create an Entity Relation Diagram showing the tables needed and the relationships between tables 2. Create suitable fields for each table and select an appropriate primary key for each table select an appropriate foreign keys to connect the tables. If there is no adequate candidate key create necessary surrogate keys for the appropriate tables. For the tables in figure P3.24 you want to see data on charters flown by either Robert Williams (employee number 105) or Elizabeth Travis (employee number 109) as pilot or copilot, but not charters flown by both of them. Complete Items 3-5 to find this information. 3. Create the table that would result from applying the SELECT and PROJECT relational operators to the CHARTER table to return only the CHAR_TRIP, CHAR_PILOT, and CHAR_COPILOT attributes for charters flown by either employee 105 or employee 109. 4. Create the table that would result from applying the SELECT and PROJECT relational operators to the CHARTER table to return only the CHAR_TRIP, CHAR_PILOT, and CHAR_COPILOT attributes for charters flown by both employee 105 and employee 109. 5. Create the table that would result from applying a DIFFERENCE relational operator of your result from Item 1 to your result from Item 2. A department of the university has a small car, which members of staff may use for work purposes. They need a database to keep track of who uses the car and any related expenses Design a database for recording the information on the staff member who books the departmental car and the date booked and returned, the car mileage and any claims for the fuel, parking and similar related expenses 1. Create an Entity Relation Diagram showing the tables needed and the relationships between tables 2. Create suitable fields for each table and select an appropriate primary key for each table select an appropriate foreign keys to connect the tables. If there is no adequate candidate key create necessary surrogate keys for the appropriate tables. For the tables in figure P3.24 you want to see data on charters flown by either Robert Williams (employee number 105) or Elizabeth Travis (employee number 109) as pilot or copilot, but not charters flown by both of them. Complete Items 3-5 to find this information. 3. Create the table that would result from applying the SELECT and PROJECT relational operators to the CHARTER table to return only the CHAR_TRIP, CHAR_PILOT, and CHAR_COPILOT attributes for charters flown by either employee 105 or employee 109. 4. Create the table that would result from applying the SELECT and PROJECT relational operators to the CHARTER table to return only the CHAR_TRIP, CHAR_PILOT, and CHAR_COPILOT attributes for charters flown by both employee 105 and employee 109. 5. Create the table that would result from applying a DIFFERENCE relational operator of your result from Item 1 to your result from Item 2
