Problem: design a er diagram for a database to keep track of building inspections required by a department of Damaged Buildings (DB). DB building inspections are requested by builders every month. Each building inspection is either passed or not. Inspections have a type code (3 characters, e.g. PLU, FRM, ELE, etc.) and possibly sequencing requirements. Some inspections cannot be performed before other inspections, e.g. final plumbing inspection cannot be performed until the framing inspection is passed. Each inspection has a numeric score, with 75 or more out of 100 being sufficient for a pass status. Each inspection data contains the date of inspection, inspector identification, inspection score, and textual information about the inspection. The textual information can be updated later, but the score can never be changed. FODB maintains a pool of inspectors. Each inspector has a unique 5 digit employee ID, name, and date they were hired. They can conduct any type of inspection but can only perform at most 5 inspections per month. Any failed inspection can be repeated until passed. Particular information maintained about builders includes: Name (30 byte character string), address (40 byte character string), license# (5 digit number). A builder’s license# is unique. A builder and location must exist prior to requesting an inspection. A request for an inspection may be assigned to any available inspector assuming the prerequisite inspections have a pass status.
Problem:
design a er diagram for a
- Construct an ER Diagram with attributes, being precise in your notation, including cardinality constraints. The ER diagram you create must support all requirements stated above. If you add any restrictions or information not stated above, please specify.
- Given your ER diagram, provide an initial description of the tables you plan to create, identifying keys and foreign keys (i.e. an initial relational schema)
- Write the code for a trigger that ensures an inspection is assigned to an inspector hired before the date of the inspection.
Trending now
This is a popular solution!
Step by step
Solved in 4 steps with 1 images
