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.

Presentation Project

The best and most lasting growth comes when we move from our comfort zones. This presentation project was miles from mine. It made me understand how my mother felt when I gave her her first computer. I started the assignment excited because the project I chose was one that I wanted to do for a long time. The discomfort came from using tools that were very unfamiliar to me.

It my professional life, I spend all day long staring at a computer screen, but the time is spent writing queries or ddl or in ERwin designing databases. Even though I had designed hundreds of powerpoint presentations, I never used any of the fun frills like narration and slide timing or custom animation. With this assignment I used all of those and also stretched my creativity by learning a wireframe tool called MockFlow. The final step of the project, loading the presentation to the internet was a complete mystery to me. I am eternally grateful to the blog posters as I would never have figured it out without reading the posts.

I was reluctant to post my presentation for critique as it is a venture capital idea that I am actively pursuing (and you cannot copyright or patent an “idea”) however, self critiquing my presentation, I felt it had a very logical flow from idea to idea but that my transitions could have been smoother (both with my script and my narration tool). I shared information at a “30,000 foot” level as we say in IT. I felt that getting too technical would not hold the interest of venture capitalists.

As for anticipating questions, I did a high level idea presentation. It is my hope that there were unanswered process questions as I would hope to get to another meeting. The Purpose of the initial presentation was to spark interest in the idea. I also deliberately used minimal text and relied on narration. I felt that the photographs were the best way to get my point across about the diversity of choices and the need to be able to pare them down in a methodical manner.

While there are many things I will do differently in version 2 of this presentation, I feel I did a decent job of getting to the heart of my goal quickly and concisely. I am looking forward to refining this presentation and one day very soon presenting it to actual venture capitalists in a personal setting. I have a great deal of faith in this idea and I am fortunate to work with very talented developers and designers. I am going to bring this idea and this presentation to them and see if we can form the team to make this project a reality.

To view my Florida Staycation presentation, use the link :

http://www.authorstream.com/Presentation/tfortnash-874632-weekend-staycation/