The Department of Health in New York has four offices for processing immunizatio

Question

The Department of Health in New York has four offices for processing immunization records of children. Records about immunization of children from the Bronx. Manhattan. and Queens are processed in offices in each of these boroughs; records of children from Brooklyn and Staten Island are processed in the Brooklyn office. In addition to the basic information about the children, the database includes the data for all immunizations and immunization events, including the immunization code, when it was given, and to which child. The relational model of the database is the following: Child (childLD, firstName, lastName, dateofBirth, street, city, ZIP, phone, borough) Immunization (immunizationCode, description) Immunization Event (childLD inmmunizationCode, date) Each office supports the data for local children and their immunization events. In addition to all the necessary data modifications, the office daily processes several thousand requests for immunization events of a child (including immunization code and description, and date of the immunization even), given the child's lDor name and date of birth. The office in Brooklyn, in addition to supporting the local immunization information, produces weekly reports about the immunization events of a particular type for all children in New York. This office also maintains (updates, deletes, inserts) the data about mmunizations, The database users in local offices can retrieve and modify all local data, but cannot access data of other offices. A group of database users in Brooklyn can retrieve data about all children and their immunization events.

Explanation / Answer

1. Suppose a child name Jason Smith born on Jan 31st of 1994, living in Manhattan, New York, needs to be given an immunization 'Y' on '01-01-1996'. Assuming that the immunization details are not present in the database, so inserting it too.

INSERT INTO Child (childID, firstName, lastName, dateOfBirth, street, city, ZIP, phone, borough) VALUES (1, 'Jason', 'Smith', 31-01-1994', 'Vassar Street', 'New York', 40081,'404-222-987','Manhattan');

INSERT INTO Immunization(immunizationCode, description) VALUES('Y', 'vaccination for polio' );

INSERT INTO Immunization_Event (childID, immunizationCode, date) VALUES (1, 'Y', '01-01-1995');

2. Child X immunization on date '01-01-1996', was incorretly entered as Y instead of Z. Need to change it.
UPDATE Immunization_Event SET immunizationCode = 'Z' WHERE childID='1' and date='01-01-1996' and immunizationCode = 'Y';

3. Select names of the children who were born between X and Y
SELECT firstName, lastName FROM Child WHERE dateOfBirth>='01-01-1992' and dateOfBirth <='01-01-1994';

4. Select childIDs and number of immunizations she/he has received
SELECT childID, COUNT(*) FROM Immunization_Event GROUP BY childID;

5. For childID = X, select his/her name, codes and descriptions of the immunizations she has received.

SELECT
ImmEvent.childID,
PersInfo.firstName,
PersInfo.lastName,
ImmInfo.immunizationCode,
ImmInfo.description
FROM
       Child AS PersInfo
              JOIN
       Immunization_Event AS ImmEvent ON PersInfo.childID = ImmEvent.childID
              JOIN
       Immunization_Event AS ImmEvent ON ImmEvent.immunizationCode = ImmInfo.immunizationCode
       WHERE
              ImmEvent.childID = 'X';

6. Select childID and name for the child who had more than X immunization in a given month, say January

SELECT
PersInfo.childID,
firstName,
lastName
FROM
       (SELECT DISTINCT childID
         FROM
              Immunization_Event
       WHERE
              date >= '01-01-2016' AND date<= '31-01-2016'
         GROUP BY childID
         HAVING COUNT(*) > X) AS temporary
              JOIN
       Child AS PersInfo ON temporary.childID = PersInfo.childID;

1. Select list of all immunizations and number of times they were given

SELECT ImmunizationCode, COUNT(*) as totalUsage
FROM
       Immunization_Event
WHERE
       date >= '01-01-2016' AND date<= '31-01-2016'
GROUP BY ImmunizationCode;

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.