Member Directory:2011 Spring:Normalization Document v2.0

From Openitware
Revision as of 20:19, 7 November 2011 by Bjross (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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.

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. The COMMITTEE relation is created, which forms a one-to-many relationship with the MEMBER COMMITTEE relation. This new relation designates a "Committee ID" as its PK, and lists an additional non-key attribute called "Name." “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

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.

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. An Individual and an Organization characterize 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 referential integrity, the primary key in this new relation will have to become a foreign key in 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. This would result in the creation of the following relation:

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

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.

Conclusion – Relation List and Further Development

Relation List


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:

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

COMMITTEE: COMMITTEE ID (PK), NAME

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

Further Development


As the Member Directory database exists right now, there is a need to further enhance the functionality of the INDIVIDUAL MEMBER entity with additional attributes and/or relationships with other entities. NHHTC might stand to benefit from enabling individuals, e.g., aspiring writers, students conducting research or developing projects, or inventors, who could presumably buy a membership and receive NHHTC’s member services. This could theoretically strengthen the bonds between academia and the business world and forge mutually beneficial relationships between the two.

Relational Diagram

NHHTC 1.png