GROUP ASSIGNMENT TITLE: DATABASE DESIGN FOR PINKTON HOSPITAL LECTURER NAME: Liz Sokolowoski GROUP NUMBER: 43 GROUP MEMBERS: Mrisho Abeid Omary Nisha Patel Younis Ali Charles Asante ISSUED DATE: 24th – Jan – 2017 SUBMITION DATE: 10th – Mar – 2017 WORD COUNT: 2000 Words INFORMATION SYSTEMS & DATABASES DATABASE DESIGN FOR PINKTON HOSPITALISDB-Semester 02 INFORMATION SYSYTEMS & DATABASES GROUP Degree University of West London Page i TABLE OF CONTENTS: 0.1 TABLE OF FIGURES: ............................................................................................................................ ii 0.2 LIST OF TABLES:................................................................................................................................. iii 1.0 INTRODUCTION:................................................................................................................................. 1 2.0 ASSUMPTIONS: .................................................................................................................................. 2 3.0 STAKEHOLDERS AND FUNCTIONAL REQUIREMENTS: ......................................................................... 3 4.0 ENTITY RELATIONSHIP DIAGRAM: ...................................................................................................... 5 5.0 ENTITY SPECIFICATION FORMS:.......................................................................................................... 6 6.0 PROJECT MANAGEMENT:................................................................................................................. 11ISDB-Semester 02 INFORMATION SYSYTEMS & DATABASES GROUP Degree University of West London Page ii 0.1 TABLE OF FIGURES: Figure 1: ENTITIY RELATIONSHIP DIAGRAM .................................................................................................. 5 Figure 2: 2nd MEETING MINUTES ............................................................................................................... 11 Figure 3: 3rd MEETING MINUTES ................................................................................................................ 12ISDB-Semester 02 INFORMATION SYSYTEMS & DATABASES GROUP Degree University of West London Page iii 0.2 LIST OF TABLES: Table 1: ENTITY SPECIFICATION FORM – TWIN ............................................................................................. 7 Table 2: ENTITY SPECIFICATION FORM - TWIN_SET ...................................................................................... 8 Table 3: ENTITY SPECIFICATION FORM – ILLNESS.......................................................................................... 8 Table 4: ENTITY RELATIONSHIP FORM - TWIN_TEST ..................................................................................... 9 Table 5: ENTITY SPECIFICATION FORM - TEST ............................................................................................. 10ISDB-Semester 02 INFORMATION SYSYTEMS & DATABASES GROUP Degree University of West London Page 1 1.0 INTRODUCTION:ISDB-Semester 02 INFORMATION SYSYTEMS & DATABASES GROUP Degree University of West London Page 2 2.0 ASSUMPTIONS: As part of the database development procedure, we had to make a few assumptions that would eventually affect the design and development of the database. The following below are the assumptions that we made while putting our database together.  All twins in every pair agreed to take part in the research together: As one of our assumptions, we assumed that all pairs of twins that were approached by the researchers to take part in the projects agreed to do so together. This eliminated the option of a single twin accepting and the other denying to take part in the research. Eventually, we created an entity that contained collective records of each pair of twins from which the researchers could obtain some samples for their researches.  Each pair of twins was born on the same day: Since there is a possibility of twins to be born in different days in situations where the mother gave birth to one moments before midnight and then there other was born after that, we decided to assume that such a situation never occurs so that each twin and their co-twin was born on the same day but could be a few hours apart. This eliminated the issues of twins being of different ages and not sharing the same birthdate.  The budget is managed by an accountant: In order to specify the division of work and responsibilities between all stakeholders related to the database, we assumed that the an accountant would be a role that managed the budgets for the projects that are carried out by the researchers.ISDB-Semester 02 INFORMATION SYSYTEMS & DATABASES GROUP Degree University of West London Page 3 3.0 STAKEHOLDERS AND FUNCTIONAL REQUIREMENTS: The following below are the list of 4 stakeholders of the database together with their functional requirements that they expect the databases should meet. Researcher: 1. The database should be able to list the medical records for each twin and co-twin if applicable showing the name, contact, address, type and illness. 2. The database should be able to show the details of a particular project and all twins involved in that project listing their names and contact details. 3. The database should be able to show the records of the number of research studies that the twins have been involved in. 4. The database should be able to show the results of the research studies that were conducted on the twins. Manager: 1. The database should be able to produce a list of all projects and their budget totals. 2. The database should be able to show the journals in which the research projects were published. 3. The database should be able to show the details of the everyone involved in the projects such as the twins and researchers. 4. The database should be able to indicate the number of hours spent and the remaining hours for each project. Accountant: 1. The database should be able to produce a list of records containing the total amount of money in each budget of each project. 2. The database should be able to show the expenses in each project as well as the person who incurred that expense.ISDB-Semester 02 INFORMATION SYSYTEMS & DATABASES GROUP Degree University of West London Page 4 Twin: 1. The database should be able to show my medical history from results of the research studies that I went through. 2. The database should be able to list the names and contact details of the researchers who will be involved in the research studies that I am involved in.ISDB-Semester 02 INFORMATION SYSYTEMS & DATABASES GROUP Degree University of West London Page 5 4.0 ENTITY RELATIONSHIP DIAGRAM: FIGURE 1: ENTITIY RELATIONSHIP DIAGRAMISDB-Semester 02 INFORMATION SYSYTEMS & DATABASES GROUP Degree University of West London Page 6 5.0 ENTITY SPECIFICATION FORMS: The following are the specification forms of each entity in the database showing the data types of each attribute, relationships they have with other entities, data they contain and examples of the data to be entered in each attribute. ENTITY NAME: TWIN RELATIONSHIPS: One to many with TWIN_ILLNESS One to many with TWIN_SET One to many with TWIN_TEST Entity description/purpose and metrics: Contains individual details on all twins related to the research projects conducted by the Pinkton Hospital. (Synonym: Patient) Approximately 14000 current twins (patients) Attribute Name Attribute Data Type and width where applicable Status (nn, PK, FK, I ) Validation Example of input and any other relevant information, eg coding structure twin_id VARCHAR2(7) PK Made up of first letters of the first and last names (CAPITALS), followed by a 5 digit number. Must be unique. EG: MO00001 first_name VARCHAR2(15) Must be in uppercase Made up of full first name. EG: MRISHO. last_name VARCHAR2(15) I Must be in uppercase Made up of full last name. EG: OMARY. sex CHAR(1) Must be in uppercase Made up of the first letter of their sex. EG: M for Male or F – Female. blood_type VARCHAR2(3) Limit values to O, O+, O-, A, A+, A-, B, B+, B-, AB, AB+, AB-. (CAPITALS) Indicates their blood group. EG: O, A, AB+, O-, B+.ISDB-Semester 02 INFORMATION SYSYTEMS & DATABASES GROUP Degree University of West London Page 7 address VARCHAR2(30) Must be in uppercase Made of the full address. EG: 1 ACACIA AVE. city VARCHAR2(15) Must be in uppercase Made up of the full city name in CAPITALS. EG: LONDON. postcode VARCHAR2(8) Must be in uppercase Made up of alphanumeric characters. EG: TW89RT area CHAR(2) Limit values to one of: NE, NW, SE, SW (CAPITALS) Refers to the geographical location of the twin's/patient's residential area. EG: NW. mobile VARCHAR2(15) Must contain numbers only Made up of the twin's/patient's mobile phone number. EG: 07405391035. twin_set_id NUMBER(5) FK Must contain numbers only Made of 5 digits. EG: 00001. TABLE 1: ENTITY SPECIFICATION FORM – TWIN ENTITY NAME: TWIN_SET RELATIONSHIPS: One to many with TWIN One to many with TWIN_PROJECT Entity description/purpose and metrics: Contains collective details that each pair of twins share. (Synonyms: Group of twins) Attribute Name Attribute Data Type and width where applicable Status (nn, PK, FK, I ) Validation Example of input and any other relevant information, eg coding structure twin_set_id NUMBER(5) PK Must be unique and made up of 5 digits. EG: 00001. date_of_birth DATE Must be in uppercase Must be the System default date format. EG: 15-MAR-95 name_of_mother VARCHAR2(15) Must be in uppercase Made up of full last name and first letter of first name. EG: PRATT S.ISDB-Semester 02 INFORMATION SYSYTEMS & DATABASES GROUP Degree University of West London Page 8 age NUMBER(3) Must be a number Must be a NUMBER to indicate the age of the TWIN. twin_type VARCHAR2(4) I Limit values to OST, SSIT, SSNT, TRP Made up of the initials of the types of twins in CAPITALS. EG: Opposite Sex Twins – OST, Same Sex Identical Twins – SST, Same Sex NonIdentical Twins - SSNT or Tripplets - TRP TABLE 2: ENTITY SPECIFICATION FORM - TWIN_SET ENTITY NAME: ILLNESS RELATIONSHIPS: One to many with TWIN_ILLNESS Entity description/purpose and metrics: Contains the names of all diseases that have been found on the twins during the research projects. Attribute Name Attribute Data Type and width where applicable Status (nn, PK, FK, I ) Validation Example of input and any other relevant information, eg coding structure illness_id VARCHAR2(6) PK Must be unique and made up of first 3 letters of the illness name followed by 3 digits in CAPITALS. EG: CAN001 for Cancer illness_name VARCHAR2(20) I Must be in uppercase Made up of the full name of the illness or disease and must be in CAPITALS. EG: CANCER. TABLE 3: ENTITY SPECIFICATION FORM – ILLNESSISDB-Semester 02 INFORMATION SYSYTEMS & DATABASES GROUP Degree University of West London Page 9 ENTITY NAME: TWIN_TEST RELATIONSHIPS: One to many with TWIN One to many with TEST Entity description/purpose and metrics: Contains details of the test conducted on the twins during the research projects. Attribute Name Attribute Data Type and width where applicable Status (nn, PK, FK, I ) Validation Example of input and any other relevant information, eg coding structure twin_id VARCHAR2(7) FK, CK Made up of first letters of the first and last names (CAPITALS), followed by a 5 digit number. Must be unique. EG: MO00001 test_id VARCHAR2(6) FK, CK Made up of the letter T in CAPITALS followed by 5 numbers. EG: T00001 – Test 00001 result VARCHAR2(15) Limit the values to POS or NEG Made up of the full names of the result in CAPITALS. EG: POSITIVE – POS or NEGATIVE – NEG. date_conducted DATE Must be in uppercase Must be the System default date format. EG: 15-MAR-95 TABLE 4: ENTITY RELATIONSHIP FORM - TWIN_TESTISDB-Semester 02 INFORMATION SYSYTEMS & DATABASES GROUP Degree University of West London Page 10 ENTITY NAME: TEST RELATIONSHIPS: One to many with TWIN_TEST Entity description/purpose and metrics: Contains the names of the tests conducted on the twins during the research projects. Attribute Name Attribute Data Type and width where applicable Status (nn, PK, FK, I ) Validation Example of input and any other relevant information, eg coding structure test_id VARCHAR2(6) PK Made up of the letter T in CAPITALS followed by 5 numbers. EG: T00001 – Test 00001 test_name VARCHAR2(30) Must be in uppercase Made up of the full name or type of test conducted in CAPITALS. EG: CONE BIOPSY. TABLE 5: ENTITY SPECIFICATION FORM - TESTISDB-Semester 02 INFORMATION SYSYTEMS & DATABASES GROUP Degree University of West London Page 11 6.0 PROJECT MANAGEMENT: The following are the meeting minutes that we prepared during every meeting that we had in order to help with managing the project. FIGURE 2: 2ND MEETING MINUTESISDB-Semester 02 INFORMATION SYSYTEMS & DATABASES GROUP Degree University of West London Page 12 FIGURE 3: 3RD MEETING MINUTESISDB-Semester 02 INFORMATION SYSYTEMS & DATABASES GROUP Degree University of West London Page 13