Question

4.   Review Exercise 2 on Page 154 in your textbook. Construct a relational database, including tables, to handle the entities, attributes, and relationships included in the exercise. For each relation (table) in your relational database, clearly specify primary key, alternate key(s), and foreign keys. (9pts)

The State of New York certifies firefighters throughout the state and must keep track of all of them, as well as of the state’s fire departments. Each fire department has a unique department number, a name that also identifies its locale (city, county, etc.), the year it was established, and its main telephone number. Each certified firefighter has a unique firefighter number, a name, year of certification, home telephone number, and a rank (firefighter, fire lieutenant, fire captain, etc.) The state wants to record the fire department for which each firefighter currently works and each firefighter’s supervisor. Supervisors are always higher-ranking certified firefighters. Construct a relational database for New York’s fire departments and firefighters.

Answer 1

Hi,

Please find the database tables below:

Department Table:

Sample Attributes of the table:

DeptNo
DeptName
EstablishmentYear
TelephoneNumber.

Primary key of the table: DeptNo

-----------------------------

Firefighter Table:

Sample Attributes of the table:

FirefighterNo
FirefighterName
YearOfCertification
HomeTelephoneNumber
Rank
DeptNumber
Supervisor

Primary key of the table: FirefighterNo

Foreign key for this table is DeptNumber that references the DeptNo primary key of the Department table.

----------------------------

Let me know if you need more information.

Hope this helps,
Thanks.