Database Design and Development
Database Design and Development | International Institute Of Computer Professionals | Francisco Lezama | Student ID: 00132002 |
March 2013
NCC Education - Level 5 Diploma in Business Information Technology
School of Accounting and Management, Trinidad, W.I.
Table of Contents
Task 1 1 Entity Relationship Diagram 1 Normalization 4 Data Dictionary 6
Task 2 10 2.1 Implementation and Data 10 2.2 Inserting data into tables for Members and Countries 16 2.3 Inserting Data for the Organizations; Assign Members to Organizations 18 2.4 Data on Qualification and Topic 20 2.5 Query – Select all the Organizations and Members 23 2.6 Query – Selects all Member’s Qualifications and Topics 24 2.7 Query – Selects all Interest Groups 26 2.8 Query – Show All Countries and The Members That Reside In Those Countries 26
Task 3 27 Derived Data 27
Task 4 29 Evaluation 29
Bibliography 30
Task 1
Entity Relationship Diagram
List of Entities 1. Member 2. Country 3. Qualification 4. Topic 5. Area 6. Interest Group 7. Organization
Qualification
Topic
Area
InterestGroup
Member
Country
Organization
1
1
1
1
1..*
1..*
1..*
1..*
1..*
1..*
1..*
1..*
1..*
1..*
STEP 1:
STEP 2 – Decomposition:
1
1
1..*
1
InterestGroup
Topic
Area
MemberQualification
MemberInterestGroup
Member
Country
MemberOrganization
1
1
Organization
Qualification
1
1
1
1
1..*
1..*
1..*
1..*
1..*
1..*
1..*
1..*
1..*
1
STEP 3 – Population:
Country
Country_ID (PK)
Country_name
Member
Member_ID (PK)
First_name
Last_name
Address
Phone
Email
Country_ID(FK)
MemberQualification
Member_ID (PK,FK)
Qual_ID (PK,FK)
MemberOrganization
Member_ID (PK,FK)
Org_ID (PK,FK)
Qualification
Qual_ID (PK)
Qual_name
Qual_type
Organization
Org_ID (PK)
Org_name
Start_date
End_date
Topic
Topic_ID (PK)
Topic_name
Qual_ID (FK)
Area_ID (FK)
March 2013
NCC Education - Level 5 Diploma in Business Information Technology
School of Accounting and Management, Trinidad, W.I.
Table of Contents
Task 1 1 Entity Relationship Diagram 1 Normalization 4 Data Dictionary 6
Task 2 10 2.1 Implementation and Data 10 2.2 Inserting data into tables for Members and Countries 16 2.3 Inserting Data for the Organizations; Assign Members to Organizations 18 2.4 Data on Qualification and Topic 20 2.5 Query – Select all the Organizations and Members 23 2.6 Query – Selects all Member’s Qualifications and Topics 24 2.7 Query – Selects all Interest Groups 26 2.8 Query – Show All Countries and The Members That Reside In Those Countries 26
Task 3 27 Derived Data 27
Task 4 29 Evaluation 29
Bibliography 30
Task 1
Entity Relationship Diagram
List of Entities 1. Member 2. Country 3. Qualification 4. Topic 5. Area 6. Interest Group 7. Organization
Qualification
Topic
Area
InterestGroup
Member
Country
Organization
1
1
1
1
1..*
1..*
1..*
1..*
1..*
1..*
1..*
1..*
1..*
1..*
STEP 1:
STEP 2 – Decomposition:
1
1
1..*
1
InterestGroup
Topic
Area
MemberQualification
MemberInterestGroup
Member
Country
MemberOrganization
1
1
Organization
Qualification
1
1
1
1
1..*
1..*
1..*
1..*
1..*
1..*
1..*
1..*
1..*
1
STEP 3 – Population:
Country
Country_ID (PK)
Country_name
Member
Member_ID (PK)
First_name
Last_name
Address
Phone
Country_ID(FK)
MemberQualification
Member_ID (PK,FK)
Qual_ID (PK,FK)
MemberOrganization
Member_ID (PK,FK)
Org_ID (PK,FK)
Qualification
Qual_ID (PK)
Qual_name
Qual_type
Organization
Org_ID (PK)
Org_name
Start_date
End_date
Topic
Topic_ID (PK)
Topic_name
Qual_ID (FK)
Area_ID (FK)