Question

You are to design a database for the department of the Smallville town environment control agency. This agency controls and maintains the water meters installed in the Smallville buildings. All the water meters are linked to the consumers. For all the Smallville consumers, the agency records a unique consumer ID, first and last names, billing address (Street, Zip). Every consumer pays for the water consumption according to the rate assigned to this consumer. The company has several types of rates. Every rate type has a rate ID, description, and price (cents per gallon of water). Each consumer is assign one rate type. Every meter is linked to the consumer responsible for this meter. A consumer can be responsible for several meters. But every meter is assigned to only one consumer. For every meter, the Smallville’s environment control agency records the information about this meter’s unique serial number, date installed. In addition, for each meter, the agency keeps the information about its model. For each water meter model we know its ID, producer, date the meter was produced, and the price associated with the meter of this model. The environment control agency stores the information about all the water meter models available on the market, even if no meter of this model is currently installed in Smallville. For every employee of the environment control agency for which you create the database we store the unique employee ID, first and last names and employee phone. There are employees of three types: namagers, controllers, and maintenance workers. Every controller is certified by the Smallville’s environment control agency and has a certificate ID as well as the certificate expiration date. Every maintenance worker can work on any meter model. To keep their qualifications up to date the maintenance workers must attend the agency workshops. After attending a workshop a maintenance worker is issued a certificate stating that the maintenance worker's qualifications are up-to-date. Each certificate has a unique certificate ID, and the date the certificate was issued. For every workshop, the environment control agency keeps information about the workshop ID, date, and time. The first thing that anyone who is hired as a maintenance worker does is attending a workshop to obtain the agency maintenance worker certificate. Over the tenure in the agency, a maintenance worker attends workshops every six months. Every meter is checked every month by controllers and maintenance workers. Controllers and maintenance workers visit the meters in pairs: while a controller records the meter reading, a maintenance worker examines the meter for any technical problems. For every check-up, the agency stores a unique check-up record number, the reading (certified by the controller) and the comments regarding the meter state (done by the maintenance worker). If no comments are recorded for the reading it means that the meter functions normally, and no further maintenance work it required.

Answer 1

1. CONSUMER (consumerID, firstname, lastname, street, zipcode)
2. RATE (rateID, description, price)
3. METER (serialNo, date_installed)
4. MODEL ( modelID, producer, date_produced, price)
5. EMPLOYEE (employeeID, firstname ,lastname, phone)
   a. CONTROLLER ( certificateID, certificate_expiry_date)
   b. MAINTENANCE (qualifications)
6. WORKSHOP (workshopID, date, time)
7. CERTIFICATE (certificateID, issued_date)
8. check-up (recordNo, reading, comment)

consumer1D X firstName X lastName Street Xzipcode) rateld description price) CONSUMER assigned RATE responsible modell serialNo H producer METER associated MODEL date_installed date_produced price reading K check-up comment record No qualification workshopID certificateD CONTROLLER MAINTENANCE attends WORKSHOP date certificate_expiration_date ] EMPLOYEE certificatel CERTIFICATE issued_date employeelDXfirstName X lastName X phone