Based on this case above, see my database design below. Here are my Questions: 1) do we need these tables: mechanics, department, ans checkout? And also a table called manager? 2) or may we have a table called employee which include faculty, mechanics, and manager? 3) do we need a checkout table? 4) in reservation table, may I use reservation as FK and also PK?
The administrators of Tiny College are so pleased with your design and implemen- tation of their student registration and tracking system that they want you to expand the design to include the database for their motor vehicle pool. A brief description of operations follows: Faculty members may use the vehicles owned by Tiny College for officially sanc- tioned travel. For example, the vehicles may be used by faculty members to travel to off-campus learning centers, to travel to locations at which research papers are presented, to transport students to officially sanctioned locations, and to travel for public service purposes. The vehicles used for such purposes are managed by . Tiny College's Travel Far But Slowly (TFBS) Center Using reservation forms, each department can reserve vehicles for its faculty, who are responsible for filling out the appropriate trip completion form at the end of a trip. The reservation form includes the expected departure date, vehicle type required, destination, and name of the authorized faculty member. The faculty member who picks up a vehicle must sign a checkout form to log out the vehicle and pick up a trip completion form. (The TFBS employee who releases the vehi- cle for use also signs the checkout form.) The faculty member's trip completion form includes the faculty member's identification code, the vehicle's identifica- tion, the odometer readings at the start and end of the trip, maintenance com- plaints (if any), gallons of fuel purchased (if any), and the Tiny College credit card number used to pay for the fuel. If fuel is purchased, the credit card receipt must be stapled to the trip completion form. Upon receipt of the trip completion form, the faculty member's department is billed at a mileage rate based on the vehicle type used: sedan, station wagon, panel truck, minivan, or minibus. (Hint: Do not use more entities than are necessary. Remember the difference between attributes and entities!) 7 aculty heck Out aculty ID Researvation ID Depture Date Vehicle Type Destination Faculty ID F Last Name Reservation ID F Last Name k Out Date dometer End uel Gallons redit Card Number redit Card Receipt First Name Pick-up Signaure TFBS Emp Signature Vehicle ID Phone Number Dept ID aintenance Complain aculty ID ehicle ID ehicle Type-FK) First Name Dept i Dept Descript Credit Card Receipt (FK) 2 vehicle Vehicle ID Vehicle Type Vehicle Descript (yr, Modell arts Usage Maintenance Log enance Details M Last Name Maintenance Descript Part Userd Item M First Name Log Entry Date enance arts Inventory art Type (Ps Part Type Descript Manager Signature ompletion Date M Signaure Vehicle Type [ O8 ID Mechanics ID ( ID chanics ID Last Name First Name Part Type Left Quantity hanics IC nspection Authorization