question 4

(a) A hospital will have multiple specialties. Each speciality has a unique name (e.g. cardiology, neurology, etc.), a motto (e-g, some fun tagline for the specialty), a priority level, and one or more rooms in the hospital assigned to it (b) Each room is uniquely identified by a room id and has a location and a maximum capacity (number of people who can be in the room). (c) Two specialities cannot use the same room. (d) A room need not belong to any specialty (e) Doctors are identified by their SSN, name, age, and sex. (1) Two doctors can have the same name but must have different SSNs (8) Teams consists of doctors and are defined by their name and their specialty's names. For example, "Cardiology Team 5" is the team with name 5 that belongs to specialty "Cardiology (h) Each specialty may have several teams, which can be spread across various rooms. (i) A room can have more than one team from the same specialty. G) Doctors must belong to one specialty but may belong to multiple teams within the specialty. (k) A specialty may or may not have any doctors. 4. Car Insurance Database (20 points): Map the ER diagram for a car insurance comp shown in Figure 1 into a relational database. Use the smallest possible number of relations Attributes in the resulting relations must not have NULL values Use the entity names in the ER-diagram as the name of the relation Use the attribute names in the ER-diagram for attribute names in the relational model . If you augment an existing relation with an attribute z to represent a relationship R, . If you define a new relation to represent a relationship, use the name of the relationship . Underline the primary key in every relation give the attribute the names R.r for the name of the relation Draw an arrow from each foreign key to its corresponding primary key Acoident Figure 1: ER diagram for a car insurance company