Difference between revisions of "Member Directory:2011 Spring:Normalization Document v2.0"

From Openitware
Jump to: navigation, search
(Member Directory Normalization: - major formatting)
Line 1: Line 1:
==Member Directory Normalization==
+
==Member Directory Normalization - P2==
  
 
===Introduction:===
 
===Introduction:===
Line 21: Line 21:
 
The conversion process resulted in the creation of the entity MEMBER COMMITTEE.  Following first normal conversion rules, the database designer copied the primary key of the original relation and moved the multivalued attribute to the new relation.  The composite key unique identifier was formed by designating “Member ID” as a foreign key to the original relation, and as a primary key in MEMBER COMMITTEE in addition to the “Committee” attribute.
 
The conversion process resulted in the creation of the entity MEMBER COMMITTEE.  Following first normal conversion rules, the database designer copied the primary key of the original relation and moved the multivalued attribute to the new relation.  The composite key unique identifier was formed by designating “Member ID” as a foreign key to the original relation, and as a primary key in MEMBER COMMITTEE in addition to the “Committee” attribute.
  
Recognizing the need to plan for potential additions to the nine currently listed committee selection options, creation of a relation which could easily store additional committee names is required.  This relation is named COMMITTEE, and it identifies (?) the MEMBER COMMITTEE relation -- first by replacing and then designating the “Committee” attribute with the “Committee ID” primary key from COMMITTEE.  “Committee ID” in the MEMBER COMMITTEE relation is then designated both a primary key and foreign key (to its parent).
+
Recognizing the need to plan for potential additions to the nine currently listed committee selection options, creation of a relation which could easily store additional committee names is required.  This relation is named COMMITTEE, and it identifies (?) the MEMBER COMMITTEE relation -- first by replacing and then designating the “Committee” attribute with the “Committee ID” primary key from COMMITTEE.  “Committee ID” in the '''MEMBER COMMITTEE''' relation is then designated both a primary key and foreign key (to its parent).
  
MEMBER COMMITTEE :
+
'''MEMBER COMMITTEE:'''
 
MEMBER ID (PK, FK), COMMITTEE ID (PK, FK)
 
MEMBER ID (PK, FK), COMMITTEE ID (PK, FK)
  
COMMITTEE:
+
'''COMMITTEE:'''
 
COMMITTEE ID (PK), NAME
 
COMMITTEE ID (PK), NAME
  
Part B: Second Normal Form
+
===Part B: Second Normal Form===
  
 
Since there is only one relation with a composite key, and that relation includes no nonkey attributes, all relations are currently in second normal form.
 
Since there is only one relation with a composite key, and that relation includes no nonkey attributes, all relations are currently in second normal form.
  
Part C: Third Normal Form and Beyond
+
===Part C: Third Normal Form and Beyond===
  
 
NHHTC’s database requires functionality which will enable detailed analysis of member data.  Looking closely at the fields listed on the Membership Sign Up webpage - https://nhhtc.org/index.php/membership-sign-up.html - the database design team realizes that two categories of members need to be represented: an individual, whom represents himself, and an organization, representing a nonprofit or for profit institution. Both and Individual and an Organization represent the subtypes of the Member supertype.
 
NHHTC’s database requires functionality which will enable detailed analysis of member data.  Looking closely at the fields listed on the Membership Sign Up webpage - https://nhhtc.org/index.php/membership-sign-up.html - the database design team realizes that two categories of members need to be represented: an individual, whom represents himself, and an organization, representing a nonprofit or for profit institution. Both and Individual and an Organization represent the subtypes of the Member supertype.
Line 39: Line 39:
 
Before resolving the issue of generalization vs. specialization, third normal form requires that all nonkeys be dependent only on the primary key.  In the starting relation MEMBER SIGNUP FORM, the “business type” attribute is not dependent on the “Member ID” primary key; therefore, third normal form rules require the creation of a new relation.  ORGANIZATION TYPE is the name chosen, with a surrogate key called “Type ID” created as the unique identifier, and the “Business Type” attribute removed from the original relation changed to “Name,” and moved to ORGANIZATION TYPE.  In order to enforce relationships, the primary key in this new relation will have to become a foreign key to the MEMBER SIGNUP FORM relation (which will eventually become the MEMBER relation).
 
Before resolving the issue of generalization vs. specialization, third normal form requires that all nonkeys be dependent only on the primary key.  In the starting relation MEMBER SIGNUP FORM, the “business type” attribute is not dependent on the “Member ID” primary key; therefore, third normal form rules require the creation of a new relation.  ORGANIZATION TYPE is the name chosen, with a surrogate key called “Type ID” created as the unique identifier, and the “Business Type” attribute removed from the original relation changed to “Name,” and moved to ORGANIZATION TYPE.  In order to enforce relationships, the primary key in this new relation will have to become a foreign key to the MEMBER SIGNUP FORM relation (which will eventually become the MEMBER relation).
  
ORGANIZATION TYPE:
+
'''ORGANIZATION TYPE:'''
 
TYPE ID (PK), NAME
 
TYPE ID (PK), NAME
  
The ORGANIZATION TYPE relation lists attributes TypeID as a surrogate primary key and Name as a nonkey.  The Name nonkey holds values such as biotechnology, telecommunications, information technology, etc..  Setting up this table in NHHTC’s database will provide for easy scalability – for instance, if NHHTC needs to drop, add, or modify text stored in any given Name field, they will be easily able to do so.
+
The '''ORGANIZATION TYPE''' relation lists attributes TypeID as a surrogate primary key and Name as a nonkey.  The Name nonkey holds values such as biotechnology, telecommunications, information technology, etc..  Setting up this table in NHHTC’s database will provide for easy scalability – for instance, if NHHTC needs to drop, add, or modify text stored in any given Name field, they will be easily able to do so.
  
The “Company Sales” attribute in the MEMBER SIGNUP FORM represents another third normal form violation, as it would designate a nonkey as its unique identifier in the current relation.  Therefore, it was necessary to create the SALES LEVEL entity, designating the surrogate key “Sales Level ID” as its primary key which is the determinant of the attribute which will eventually be named “Level Description.”  A non identifying relationship is maintained by mandating that the newly formed surrogate is a foreign key in the original entity.
+
The “Company Sales” attribute in the '''MEMBER SIGNUP FORM''' represents another third normal form violation, as it would designate a nonkey as its unique identifier in the current relation.  Therefore, it was necessary to create the SALES LEVEL entity, designating the surrogate key “Sales Level ID” as its primary key which is the determinant of the attribute which will eventually be named “Level Description.”  A non identifying relationship is maintained by mandating that the newly formed surrogate is a foreign key in the original entity.
 
The Issue of Specialization:
 
The Issue of Specialization:
  
Line 58: Line 58:
 
MEMBER ID (PK, FK), TYPE ID (FK), EMPLOYMENT STATUS, DESCRIPTION, SALES, NUMBER OF EMPLOYEES
 
MEMBER ID (PK, FK), TYPE ID (FK), EMPLOYMENT STATUS, DESCRIPTION, SALES, NUMBER OF EMPLOYEES
  
The attribute“Sales” is drawn from a user selection on a drop down list on the member sign up website form.  It would be prudent to adjust for granularity in these levels, and was done with the creation of the SALES LEVEL relation.  The “Level Description” attribute can easily adjust for granularity, either narrowing the scope of the value or widening it.  The LevelDescription attribute, as it exists now, would hold a value of either less than a million, less than 5 million, less than 10 million, more than 10 million, or non profit.  The primary key in SALES LEVEL then points to the “Sales” attribute in ORGANIZATION MEMBER; therefore, we change “Sales” to “Sales Level ID” and declare it as a foreign key in the original relation (which is ORGANIZATION MEMBER).  
+
The attribute “Sales” is drawn from a user selection on a drop down list on the member sign up website form.  It would be prudent to adjust for granularity in these levels, and was done with the creation of the SALES LEVEL relation.  The “Level Description” attribute can easily adjust for granularity, either narrowing the scope of the value or widening it.  The LevelDescription attribute, as it exists now, would hold a value of either less than a million, less than 5 million, less than 10 million, more than 10 million, or non profit.  The primary key in SALES LEVEL then points to the “Sales” attribute in ORGANIZATION MEMBER; therefore, we change “Sales” to “Sales Level ID” and declare it as a foreign key in the original relation (which is ORGANIZATION MEMBER).  
  
 
SALES LEVEL:
 
SALES LEVEL:
Line 65: Line 65:
 
ORGANIZATION MEMBER:
 
ORGANIZATION MEMBER:
 
MEMBER ID (PK, FK), TYPE ID (FK), SALES LEVEL ID (FK), EMPLOYMENT STATUS, DESCRIPTION, NUMBER OF EMPLOYEES
 
MEMBER ID (PK, FK), TYPE ID (FK), SALES LEVEL ID (FK), EMPLOYMENT STATUS, DESCRIPTION, NUMBER OF EMPLOYEES
Contact Information:
+
 
 +
====Contact Information:====
  
 
Contact details must be maintained as accurately and timely as possible for NHHTC’s member directory.  For any NHHTC member, a unique relationship to its contact relation is mandatory.  Additionally, there exists a need for secondary contact information.  Although not explicitly required for a new user signup on NHHTC’s sign-up webpage, the secondary contact would be practical to implement.  For instance, the need to get in contact with an additional person whom represents the specific institution could lead to more efficient communication, reducing frustrating back and forth exchanges to unchecked voicemails.  To accomplish these tasks, the creation of two relations was necessary:
 
Contact details must be maintained as accurately and timely as possible for NHHTC’s member directory.  For any NHHTC member, a unique relationship to its contact relation is mandatory.  Additionally, there exists a need for secondary contact information.  Although not explicitly required for a new user signup on NHHTC’s sign-up webpage, the secondary contact would be practical to implement.  For instance, the need to get in contact with an additional person whom represents the specific institution could lead to more efficient communication, reducing frustrating back and forth exchanges to unchecked voicemails.  To accomplish these tasks, the creation of two relations was necessary:

Revision as of 16:17, 27 April 2011

Contents

Member Directory Normalization - P2

Introduction:

Our team has been tasked with upgrading what the client has described as a “broken” database. This normalization draft will attempt to show and explain the normalization process. Once the logical design of New Hampshire High Technology Council’s member sign up database complies to all three normalization forms, our team can progress to physical design using the Structured Query Language (SQL) and the MySQL DBMS to present the company with a functional database. Part A: First Normal Form

The process of normalization requires that for each relation or user view, there is a designated primary key chosen. Beginning with MEMBER SIGNUP FORM as the starting user view, the database designer has elected to use a surrogate key called ‘Member ID’. Designating “Member ID” as the primary key of the user view, it helps to list the rest of the attributes in order to spot multivalued attributes, which violate first normal form.

MEMBER SIGNUP FORM: MEMBER ID (PK), FIRST NAME, LAST NAME, COMPANY, JOB TITLE, ADDRESS 1, ADDRESS 2, PO BOX, CITY, STATE, ZIP, PHONE, EXT, EMAIL, WEBSITE, BUSINESS TYPE, NUMBER EMPLOYEES, BUSINESS DESCRIPTION, COMPANY SALES, COMMITTEE, REFERRAL INFO, JOIN REASON

The “committee” attribute can hold multiple values for the same instance, based on the ability of a member sign-up to select from up to nine committee options on the NHHTC member signup webpage. This represents a first normal form violation. Remediation of this violation results in the following two relations:

MEMBER SIGNUP FORM: MEMBER ID (PK), FIRST NAME, LAST NAME, COMPANY, JOB TITLE, ADDRESS 1, ADDRESS 2, PO BOX, CITY, STATE, ZIP, PHONE, EXT, EMAIL, WEBSITE, BUSINESS TYPE, NUMBER EMPLOYEES, BUSINESS DESCRIPTION, COMPANY SALES, REFERRAL INFO, JOIN REASON

MEMBER COMMITTEE : MEMBER ID (PK, FK), COMMITTEE (PK)

The conversion process resulted in the creation of the entity MEMBER COMMITTEE. Following first normal conversion rules, the database designer copied the primary key of the original relation and moved the multivalued attribute to the new relation. The composite key unique identifier was formed by designating “Member ID” as a foreign key to the original relation, and as a primary key in MEMBER COMMITTEE in addition to the “Committee” attribute.

Recognizing the need to plan for potential additions to the nine currently listed committee selection options, creation of a relation which could easily store additional committee names is required. This relation is named COMMITTEE, and it identifies (?) the MEMBER COMMITTEE relation -- first by replacing and then designating the “Committee” attribute with the “Committee ID” primary key from COMMITTEE. “Committee ID” in the MEMBER COMMITTEE relation is then designated both a primary key and foreign key (to its parent).

MEMBER COMMITTEE: MEMBER ID (PK, FK), COMMITTEE ID (PK, FK)

COMMITTEE: COMMITTEE ID (PK), NAME

Part B: Second Normal Form

Since there is only one relation with a composite key, and that relation includes no nonkey attributes, all relations are currently in second normal form.

Part C: Third Normal Form and Beyond

NHHTC’s database requires functionality which will enable detailed analysis of member data. Looking closely at the fields listed on the Membership Sign Up webpage - https://nhhtc.org/index.php/membership-sign-up.html - the database design team realizes that two categories of members need to be represented: an individual, whom represents himself, and an organization, representing a nonprofit or for profit institution. Both and Individual and an Organization represent the subtypes of the Member supertype.

Before resolving the issue of generalization vs. specialization, third normal form requires that all nonkeys be dependent only on the primary key. In the starting relation MEMBER SIGNUP FORM, the “business type” attribute is not dependent on the “Member ID” primary key; therefore, third normal form rules require the creation of a new relation. ORGANIZATION TYPE is the name chosen, with a surrogate key called “Type ID” created as the unique identifier, and the “Business Type” attribute removed from the original relation changed to “Name,” and moved to ORGANIZATION TYPE. In order to enforce relationships, the primary key in this new relation will have to become a foreign key to the MEMBER SIGNUP FORM relation (which will eventually become the MEMBER relation).

ORGANIZATION TYPE: TYPE ID (PK), NAME

The ORGANIZATION TYPE relation lists attributes TypeID as a surrogate primary key and Name as a nonkey. The Name nonkey holds values such as biotechnology, telecommunications, information technology, etc.. Setting up this table in NHHTC’s database will provide for easy scalability – for instance, if NHHTC needs to drop, add, or modify text stored in any given Name field, they will be easily able to do so.

The “Company Sales” attribute in the MEMBER SIGNUP FORM represents another third normal form violation, as it would designate a nonkey as its unique identifier in the current relation. Therefore, it was necessary to create the SALES LEVEL entity, designating the surrogate key “Sales Level ID” as its primary key which is the determinant of the attribute which will eventually be named “Level Description.” A non identifying relationship is maintained by mandating that the newly formed surrogate is a foreign key in the original entity. The Issue of Specialization:

The MEMBER relation is a supertype to the INDIVIDUAL MEMBER and ORGANIZATION MEMBER entities. The two subtypes designate MemberID (PK) in the MEMBER entity as their unique identifier, as all attributes of the supertype are inherited by the subtypes. Here are listed the supertype MEMBER with its subtypes, and their associated attributes.

MEMBER: MEMBER ID (PK), LAST NAME, FIRST NAME, EMAIL, PHONE, MEMBER TYPE, JOIN REASON, REFERRAL INFO

INDIVIDUAL MEMBER: MEMBER ID (PK, FK), EMPLOYMENT STATUS

ORGANIZATION MEMBER: MEMBER ID (PK, FK), TYPE ID (FK), EMPLOYMENT STATUS, DESCRIPTION, SALES, NUMBER OF EMPLOYEES

The attribute “Sales” is drawn from a user selection on a drop down list on the member sign up website form. It would be prudent to adjust for granularity in these levels, and was done with the creation of the SALES LEVEL relation. The “Level Description” attribute can easily adjust for granularity, either narrowing the scope of the value or widening it. The LevelDescription attribute, as it exists now, would hold a value of either less than a million, less than 5 million, less than 10 million, more than 10 million, or non profit. The primary key in SALES LEVEL then points to the “Sales” attribute in ORGANIZATION MEMBER; therefore, we change “Sales” to “Sales Level ID” and declare it as a foreign key in the original relation (which is ORGANIZATION MEMBER).

SALES LEVEL: SALES LEVEL ID (PK), LEVEL DESCRIPTION

ORGANIZATION MEMBER: MEMBER ID (PK, FK), TYPE ID (FK), SALES LEVEL ID (FK), EMPLOYMENT STATUS, DESCRIPTION, NUMBER OF EMPLOYEES

Contact Information:

Contact details must be maintained as accurately and timely as possible for NHHTC’s member directory. For any NHHTC member, a unique relationship to its contact relation is mandatory. Additionally, there exists a need for secondary contact information. Although not explicitly required for a new user signup on NHHTC’s sign-up webpage, the secondary contact would be practical to implement. For instance, the need to get in contact with an additional person whom represents the specific institution could lead to more efficient communication, reducing frustrating back and forth exchanges to unchecked voicemails. To accomplish these tasks, the creation of two relations was necessary:

CONTACT: CONTACT ID (PK), LAST NAME, FIRST NAME, ADDRESS 1, ADDRESS 2, STATE, ZIP, EMAIL, PHONE, EXT, WEBSITE

… this relation has a unique one-to-one relationship with MEMBER, designating its primary key (Contact ID) as a foreign key. The related contact attributes of MEMBER have been removed.

MEMBER: MEMBER ID (PK), CONTACT ID (FK), JOIN REASON, REFERRAL INFO

SECONDARY CONTACT MEMBER ID (PK, FK), CONTACT ID (PK, FK), PHONE The SECONDARY CONTACT relation thus becomes an intersection table, splitting the many-to-many relationship between MEMBER and CONTACT into two one-to-many relationships.

Part D: Conclusion – Relation List and UML Diagram

Since the MEMBER entity shares so many attributes with MEMBER SIGNUP FORM, the two have been combined into MEMBER. The following is the list of all relations for NHHTC’s member signup database: (also, I should think about defining each entity as it would exist in our relational model.))

MEMBER: MEMBER ID (PK), CONTACT ID (FK), JOIN REASON, REFERRAL INFO

INDIVIDUAL MEMBER: MEMBER ID (PK, FK), EMPLOYMENT STATUS

ORGANIZATION MEMBER: MEMBER ID (PK, FK), TYPE ID (FK), SALES LEVEL ID (FK), EMPLOYMENT STATUS, DESCRIPTION, NUMBER OF EMPLOYEES

SALES LEVEL: SALES LEVEL ID (PK), LEVEL DESCRIPTION

ORGANIZATION TYPE: TYPE ID (PK), NAME

CONTACT: CONTACT ID (PK), LAST NAME, FIRST NAME, ADDRESS 1, ADDRESS 2, STATE, ZIP, EMAIL, PHONE, EXT, WEBSITE

SECONDARY CONTACT MEMBER ID (PK, FK), CONTACT ID (PK, FK), PHONE