Create ERDs for the following case studies using LucidChart (available as a third-party add-on to Google Drive).

1. A hospital has a large number of registered physicians. Attributes of physicians include physician ID and specialty. Patients are admitted to the hospital by physicians. Attributes of patients include patient ID, name, and DOB (date of birth). An admission must have one patient, one admitting physician, and the date of admission. A patient may be admitted more than once and a physician may admit any number of patients. Once admitted, a given patient must be treated by at least one physician. A particular physician may treat any number of patients, or may not treat any patients. Whenever a patient is seen by a physician, the hospital wishes to record the details of the visit, which include date, time, observed symptoms, and prescribed treatment.
2. Consider a real estate firm that lists property for sale. The firm has a number of sales offices in several states. Attributes of sales offices include office number (identifier) and address. Each sales office is assigned one or more employees. Attributes of employees include employee ID (identifier) and employee name. An employee must be assigned to only one sales office. For each sales office, there is always one employee assigned to manage that office. An employee may manage only the sales office to which they are assigned. The firm lists properties for sale. Attributes of property include property ID (identifier), list price, street address, city, state, zip code. Each unit of property must be listed with one (and only one) of the sales offices. A sales office may have any number of properties lists or may have no properties listed. Each unit of property has one owner. Attributes of owners include owner ID (identifier) and owner name. An owner may own one or more units of property.

Tips and Guidelines

I’ve created a short list of links to Lucidchart tutorials and some tips for dealing with ERD export and import: https://sites.temple.edu/spatialdb/2019/09/27/erds-with-lucidchart/.

Please follow these guidelines:

1. Use course conventions for table and column names (lower case with underscores, no embedded spaces, etc.)
2. Make sure your tables have sensible primary keys. Indicate primary keys with “PK” in Lucidchart.
3. Use connectors with correct crow’s foot endings to indicate relationships between tables (entities).
4. Use “FK” to indicate foreign keys in Lucidchart. Note, however, that Lucidchart will not generate the required REFERENCES lines when you export the SQL. Edit the SQL manually to add the necessary REFERENCES.

Deliverables

Use LucidChart to put a database name (e.g. “Hospital Database”) and your name on the ERD. (In current versions of LucidChart, if you start from an ERD template, the name associated with your Google account should appear automatically.) To submit the assignment, download the diagrams as PDF. Also export the ERD to SQL. Make sure to use the PostgreSQL option when you export. Clean up the SQL, including removing the unnecessary CREATE INDEX statements and adding the REFERENCES lines. Upload the PDFs and SQL scripts to Canvas, and bring printouts of the PDFs to class.