Member Directory:2011 Spring:Normalization Document v1.0

From Openitware
Jump to: navigation, search

Member Directory Normalization

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 our 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:

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 rules, the database designer copied the primary key of the original relation to the new relation and moved the multivalued attribute. 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.

Part B - Second Normal Form

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

Part C - Third Normal Form and Beyond

NHHTC’s database requirements require functionality which will enable detailed analysis of member data. Looking closely at the fields listed on the Membership Sign Up webpage the database design team realizes that two categories of members need to be represented. An individual, whom represents himself, and an organization, a nonprofit or for profit institution that has employees, are the subtypes which we have included.

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 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 I list the supertype MEMBER with its subtypes, and specify the attributes associated with them.

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

“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; this can be done with a new relation called SALES LEVEL. This entity specifies SalesLevelID as its primary key, along with the nonkey attribute LevelDescription. The latter attribute is where the database administrator can 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

Since any MEMBER could have more than one contact information, it was necessary to form two new relations – OTHER CONTACT and CONTACT.

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

OTHER CONTACT: MEMBER ID (PK, FK), CONTACT ID (PK, FK)

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:

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), 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

OTHER CONTACT: MEMBER ID (PK, FK), CONTACT ID (PK, FK)