Need help with Checkpoint 2, SQL create table statements
Overview A new hospital is being constructed to service the needs of a rapidly growing retirement community. You have been contracted to develop the database system that will manage the primary care operation of the hospital, but not the financial operation. The database system must have an easy to use interface that supports all the data entry and information gathering needs of the hospital. Deliverables There are four checkpoints for this project before the final project is due. Checkpoint 1 is the ER diagram for this project. This will be an image file you submit through Canvas. You may draw this diagram on paper and scan the paper or use a piece of software and save your diagram as an image. Checkpoint 2 is the text file containing all the CREATE TABLE statements for the database. You are to translate your ER diagram from checkpoint 1 into the tables necessary for your application to provide everything detailed in the specifications. Specifications The hospital is staffed by many types of workers. Employees include doctors, nurses, technicians, staff, and administrators. When a patient is admitted to the hospital by a doctor, he or she is given a unique patient identification number and assigned to a room by an administrative employee. Each patient must provide an emergency contact and insurance policy information at the time of admission. The hospital rooms are all private rooms; that is, each room accommodates no more than one patient. Our hospital has 20 rooms, the room numbers will be 1-20. The doctor who admits a patient is considered that patient's primary doctor. At the discretion of the primary doctor, multiple doctors on staff can be assigned to a given patient. Any doctor assigned to a patient can order and perform treatments for that patient. Treatments include both procedures and medication. All treatments must be ordered by a doctor and administered by an appropriate hospital employee. Treatments may be ordered for patients. A timestamp is associated with the order and all administrations. . Treatments are administered to patients by one or more employees who are either doctors, nurses, or technicians. At the time of admission, a patient's primary doctor provides an initial diagnosis. When an admitted patient's primary doctor decides that the necessary course of treatment has ended, that patient is discharged from the hospital by an administrative employee. For the sake of this project (to make it a bit less complex), we will have two non-real world constraints: O Assume that all people (employees and patients) have a different last name, i.e. last names in this system will be unique. O Employees will never be patients. Remember that your database must account for (potentially) many admissions by the same person at different times. E.g. Kay Smith could be admitted on Jan 10th, Apr 21st, and Sept 2nd. Each one of those admissions could have a different diagnosis. Overview A new hospital is being constructed to service the needs of a rapidly growing retirement community. You have been contracted to develop the database system that will manage the primary care operation of the hospital, but not the financial operation. The database system must have an easy to use interface that supports all the data entry and information gathering needs of the hospital. Deliverables There are four checkpoints for this project before the final project is due. Checkpoint 1 is the ER diagram for this project. This will be an image file you submit through Canvas. You may draw this diagram on paper and scan the paper or use a piece of software and save your diagram as an image. Checkpoint 2 is the text file containing all the CREATE TABLE statements for the database. You are to translate your ER diagram from checkpoint 1 into the tables necessary for your application to provide everything detailed in the specifications. Specifications The hospital is staffed by many types of workers. Employees include doctors, nurses, technicians, staff, and administrators. When a patient is admitted to the hospital by a doctor, he or she is given a unique patient identification number and assigned to a room by an administrative employee. Each patient must provide an emergency contact and insurance policy information at the time of admission. The hospital rooms are all private rooms; that is, each room accommodates no more than one patient. Our hospital has 20 rooms, the room numbers will be 1-20. The doctor who admits a patient is considered that patient's primary doctor. At the discretion of the primary doctor, multiple doctors on staff can be assigned to a given patient. Any doctor assigned to a patient can order and perform treatments for that patient. Treatments include both procedures and medication. All treatments must be ordered by a doctor and administered by an appropriate hospital employee. Treatments may be ordered for patients. A timestamp is associated with the order and all administrations. . Treatments are administered to patients by one or more employees who are either doctors, nurses, or technicians. At the time of admission, a patient's primary doctor provides an initial diagnosis. When an admitted patient's primary doctor decides that the necessary course of treatment has ended, that patient is discharged from the hospital by an administrative employee. For the sake of this project (to make it a bit less complex), we will have two non-real world constraints: O Assume that all people (employees and patients) have a different last name, i.e. last names in this system will be unique. O Employees will never be patients. Remember that your database must account for (potentially) many admissions by the same person at different times. E.g. Kay Smith could be admitted on Jan 10th, Apr 21st, and Sept 2nd. Each one of those admissions could have a different diagnosis
