1. Designing a database and populating data

Multiple sclerosis, also known as MS, is a chronic inflammatory disease that attacks the central nervous system (the brain, spinal cord and optic nerves), and causes a variety of signs and symptoms such as numbness in the limbs, paralysis or blindness.

ABC Clinic, is a clinic specialized in treating and researching on inflammatory diseases, and accepts patients who suffer from the following five diseases.

1. Multiple Sclerosis
2. Alzheimers
3. Parkinsons
4. Rheumatoid Arthritis
5. Lupus Erythematous

Patients who are accepted by the clinic must sign a consent form allowing the clinic to include their information in the scientific researches that the clinic conducts. The clinic conducts 5 research studies (one for each of the diseases listed above). Each study is led by one of the clinics physician researchers.

Clinical studies around the world suggest that Multiple Sclerosis is 3 times more common in women than in men. Dr. Schneider, who leads the MS research in ABC Clinic, believes the number of male and female MS patients in ABC aligns with the statistics mentioned above (i.e. he has about 3 times more women in the clinic), but since ABC does not have an EHR yet, confirming that statistics is not easy.

For this part of the assignment you are going to create a database for ABC and fill it with imaginary (fake) data. Follow the instructions below to complete this part:

1. The following information should be stored in your database:
   1. Information about the abovementioned 5 studies:
      1. Name of the disease
      2. Number of patients enrolled in the study
      3. Name of the researcher leading the study
      4. Date when the study started
   2. Information about patients:
      1. Name
      2. Healthcare card number
      3. Province issuing the healthcare card
      4. Sex
      5. Date of birth
      6. Phone number
      7. Address
   3. Information to indicate which patient is enrolled in which study (note that some patients may have more than one disease and therefore be enrolled in more than one study)

1. Create the following three tables to store the information mentioned above, respectively:
   1. STUDIES
   2. PATIENTS
   3. PATIENT_ENROLLMENTS

1. Make sure both STUDIES and PATIENTS tables have a field called ID, which is the primary key (PK) for that table.

1. PATIENT_ENROLLMENTS table should have 3 fields (the first two, together would be the PK):
   1. Patient_ID, which is a foreign key (FK) to ID field in PATIENTS table
   2. Study_ID, which is a FK to ID field in STUDIES table
   3. Enroll_Date, which is the date patient got enrolled in that study

1. Populate the tables that you have created with made-up data:
   1. 5 records in STUDIES table (because there are 5 studies in ABC Clinic)
   2. 8 records in PATIENTS table. Feel free to use patient names and demographics that you found in HINF 4 Assignment (or you can make up your own data). Your patients should be 20 years old or older.
   3. 10 records in PATIENT_ENROLLMENTS (assume all 8 patients have MS; 2 of them have another disease as well). Note: Women suffering from MS in ABC Clinic are indeed 3 times more than men. Your made-up data should show this.

1. You can create your database in MS Access, or MS Excel. If you choose to use Excel, each table will be a worksheet. You are only required to submit your database; you dont have to write any SQL queries.

1. You are NOT required to implement PK-FK relationships. Those have been mentioned in order to help you understand the structure of tables and their relationship better.

1. Creating a data dictionary

ABC Clinic has decided to use the database that you created to store their data, and has hired a team of computer programmers to develop a user interface. Their plan is to create your database in a RDBMS which runs on a server that is not directly accessible to users. The user interface that will be developed, will act as a liaison between users and the database. Users (5 researchers and 2 data entry clerks) will access the user interface (which provides the needed forms on the screen, in a user-friendly manner), to enter data into the database or retrieve data from it.

In order to design the data entry forms correctly and develop the right data integrity checks, the computer programmers will need to know what the data type of each field is, what the maximum allowable number of characters is, and so on. To help them understand the database structure and data storage requirements better, you are going to create a data dictionary for your database.

For this part of the assignment, you are required to create a data dictionary as explained above. This data dictionary will actually be a table added to your existing database, making it a database with 4 tables. Follow the instructions below to complete this task:

1. The first 2 fields of this table should be TABLE_NAME and FIELD_NAME, which together are the PK of the table. TABLE_NAME and FIELD_NAME hold the name of the table and field within it that you are providing meta-data for.

1. In addition, this table should have fields to store the following:
   1. Data type of the field
   2. Length of the field
   3. Description of the field

1. Populate data for this table (since this is a data dictionary table, it stores meta-data about the other three tables).

1. Similar to what was mentioned in the instructions of the first part, you can create this table in MS Access, or MS Excel. You can just add this table (or worksheet, if you are using Excel) to the database that you created before. You dont have to write any SQL queries.

To help you with this part, below you can see the structure of this table as well as 2 records populated (these 2 records describe Disease_Name in table STUDIES and First_Name in table PATIENTS)

TABLE_NAME	FIELD_NAME	Data_Type	Length	Description
STUDIES	Disease_Name	character	20	This field stores the name of the disease which is being researched in this study.
PATIENTS	First_Name	character	35	This field stores first (given) name of the patient.

Hints:

• Each field in the 3 tables that you created before, should have one record in this table.
• The field names that you had assigned for the three tables that you created in part 1, may be different than what you see in the example table above. That is totally fine. For example, in part 1, you may have named the first name field of PATIENTS table, F_Name, so dont worry if you had other names instead of Disease_Name and First_Name. Just use your own names.
• With respect to length of the field, you only have to specify the length for character fields. Use your own judgement to decide what the right length should be. For example, first name and last name could be 35 characters long (or a little shorter or longer this is not an exact science so just use your judgement), but the address field should probably be longer (perhaps 80 or 100 characters).