WDIM331 - Database Mgmt Assignment - Manhattan Physicians

Project: Manhattan Physicians Centers

This project comes from a real-world project I recently worked on, and thought it would make a good project for you!  So, the client is having us design & build a site for them.  As part of the site, they are going to have us develop some custom modules.  The client is a medical group, with several medical centers in and around Manhattan. 

The modules are areas of their site that will allow people to see the locations of their centers, details of their locations, the physicians who work there, details about the physicians, their specialties, and what insurance they accept.  They will need to be able to administer (add, edit, and delete) all the content for these pages.

As part of our development process, we have created a Technical Spec document, here is part of that document that relates to these modules (there is more, but it's related to the Drupal CMS, this is all you need to worry about).

Here are screen mock-ups we produced in preparation for the programming.  This is something we always do for clients, mocking up each front-end screen:

Please note, these pages are HTML mock-ups only, they are not functional.  No programming has been done yet.  The next step - which is what you get to do - is to design the database.  You will not be programming anything.  I just want to see the database design for these screens.  

Create a design schema (ERD) document noting the table names, the primary keys, the field types, and the relationships.    

Create the tables in mySQL, and populate them with some sample data.  Write 7-8 queries to demonstrate that the right information could be pulled from your DB structure to be able to show on these pages once they are built.

A helpful tip:  It may take 2-3 SQL statements to get all the data that appears on one page. But if a page shows a list, or a table of data, then it should be created with 1 SQL statement.