Database Design and Development

Only available on StudyMode
  • Download(s) : 40
  • Published : May 29, 2013
Open Document
Text Preview
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 11
Entity Relationship Diagram1
Normalization4
Data Dictionary6
Task 210
2.1Implementation and Data10
2.2Inserting data into tables for Members and Countries16
2.3Inserting Data for the Organizations; Assign Members to Organizations18
2.4Data on Qualification and Topic20
2.5Query – Select all the Organizations and Members23
2.6Query – Selects all Member’s Qualifications and Topics24
2.7Query – Selects all Interest Groups26
2.8Query – Show All Countries and The Members That Reside In Those Countries26 Task 327
Derived Data27
Task 429
Evaluation29
Bibliography30

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)
IntGroup_ID (FK)

MemberInterestGroup
Member_ID (PK,FK)
IntGroup_ID (PK,FK)
InterestGroup
IntGroup_ID (PK)
IntGroup_name

Area
Area_ID (PK)
Area_name
1
1..*
1
1
1
1
1
1
1
1
1
1..*
1..*
1..*
1..*
1..*
1..*
1..*
1..*
1..*

Normalization
UNF:
Member_ID, First_name, Last_name, Address, Phone, Email, Country, Org_ID, Org_name, Start_date, End_date, Qual_ID, Qual_name, Qual_type, IntGroup_ID, IntGroup_name, Topic, Area

1NF:
Member
Member_ID, First_name, Last_name, Address, Phone, Email, Country

MemberOrganization
Org_ID, Org_name, Start_date, End_date, Member_ID

MemberQualification
Qual_ID, Qual_name, Qual_type, Topic, Area, Member_ID

MemberInterestGroup
IntGroup_ID, IntGroup_name, Member_ID

2NF:
Member
Member_ID, First_name, Last_name, Address, Phone, Email, Country

MemberOrganization
Member_ID, Org_ID

Organization
Org_ID, Org_name, Start_date, End_date

MemberQualification
Member_ID, Qual_ID

Qualification
Qual_ID, Qual_name, Qual_type, Topic, Area

MemberInterestGroup
Member_ID, IntGroup_ID

InterestGroup
IntGroup_ID, IntGroup_name

3NF:
Member
Member_ID, First_name, Last_name, Address, Phone, Email, Country_ID*

Country
Country_ID, Country_name

MemberOrganization
Member_ID*, Org_ID*

Organization
Org_ID, Org_name, Start_date, End_date

MemberQualification
Member_ID*, Qual_ID*

Qualification
Qual_ID, Qual_name, Qual_type

Topic
Topic_ID, Topic_name, Qual_ID*, Area_ID*, IntGroup_ID*

Area
Area_ID, Area_name
MemberInterestGroup
Member_ID*, IntGroup_ID*

InterestGroup
IntGroup_ID, IntGroup_name

Data Dictionary
Representing Entities
Entity| Description| Identifying Attribute| Other Attribute| 1. Member| Someone who has joined the International Institute of Computer Professionals| Member_ID| First_name, Last_name, Address, Phone, Email| 2. Country| Member’s country of residence| Country_ID| Country_name| 3. Organization| Member’s place of...
tracking img