Database Project

Database design is my life’s work. I have been designing Relation and Dimensional models for over 20 years. My primary DMBS are DB2 and SQL Server (Relational Transactional Databases) and Oracle (Dimensional Analytics Databases). While I have worked in Access as a means for analyzing data from flat files that were over 64,000 rows and that I did not want to seat in my Oracle Databases, I have never worked with the forms or reporting aspects of Access. It was fun to see that you could create a small application without programming skills, and the reporting features may prove valuable for data research and validation that needs to go to my business users

There are a few things I would do to enhance this database. I would pull the address information out of the members table and create a members address table. This would allow the business to store more then one address for a member (home, office, or for students school address and permanent address) I would create a members phone number table. This would allow us to track multiple telephone numbers for a member (Home, Cell, Work, etc.). I would have a reference table for address type and I would encrypt the Birthdate Column I would create a State table that stores the industry standard state abbreviations and would make this column a pull down list to insure data integrity. Without business requirements, that is about as much as I would be willing to suggest.

Tutorial 3: Creating A Relational Data Model

For my third tutorial, I opted to take the Database assignment an create an actual small third normal form database based on this one table. I used Erwin to create an Logical Model also know as an Entity Relationship Diagram. (ERD)

The steps needed to normalize and expand a database from flat file data begin as follows

1. Profile the existing data. Evaluate what was truly dependent on the True Primary Key of the members table. It is my opinion that the true primary key was a compound key of the First Name + Last Name + Birth date

2. I looked to see if there were ways to make the data entry easier while also creating better data integrity. That led me to create a state table that could be a pull down menu from the entry forms.

3. I decide that individuals could have more then one address, so I created a Physical address table that would record all street addresses. I created an associative table that would allow an address to belong to multiple members (Family Membership) and allow each member to have more then one address (Home, Office, Dorm)

4. I thought that it might be important to store the member e-mail address. I decided that individuals could have more then one email address, so I created a email address table that would record all email addresses. I created an associative table that would allow an e-mail address to belong to multiple members (Family Membership) and allow each member to have more then one email address (Home, Office, Junk)

5. I created a Membership Transaction Table. I did this so that we could tract all of the different interaction that we have with a member. The table we have only tracked joined and left. I created a transaction type reference table that will store Joined and Left but also Renewed, Rejoined, Suspended, or any other type of transaction that a member might do.

6. The transaction table it associated to the member through the email and address table so that we can run reports based on the address and e-mail address the member used for the transaction.



This ER Diagram is an original work of Theresa Fortnash.

It is always best to have a business requirements document and access to a Subject Matter Expert (SME) when designing or modifying a database as there may be many hidden business rules that may need to be accommodated in the database or explained in the metadata. Metadata is the information about the tables columns entities, attributes and the database itself. There is both technical and business metadata. The ERD should contain both.

This is a very basics lesson in the thought process of a relational data modeler. If anyone interested in learning about creating a dimensional (Star Schema) Data warehouse database from this model, send me an e-mail or a comment and I will be happy to help.