HomeCalendarFAQSearchRegisterMemberlistUsergroupsLog in

Share | 

 Concepts of Database Design and Management

Go down 

Number of posts : 1850
Age : 55
Registration date : 2008-03-08

PostSubject: Concepts of Database Design and Management   Sun 6 Apr - 17:21

It's not difficult to find an online tutorial on creating a database. It's not hard to find a tutorial on how to create queries. In fact, it's not hard to find tutorials on syntax for SQL, the differences of data types, and even which database is best for what purposes. It is, however, difficult to find good tutorials on the theories of designing, developing, and maintaining a quality database.

Database design and management isn't very difficult. People much wiser than we have designed some very orderly and sound rules to follow and developed these rules into what is called the Normalization Process.

Using this process, you can create brand new, fully functional, finely tuned databases or take current database tables, run them through these steps, and come out with well-oiled tables ready to fly. However you use these steps, they are the fundamentals of quality database design.

Functional Dependence
Before we jump into the Normalization Process, I should take a step back and clear a few things up. First, this is not specific to any one type of database. These are rules that should be followed when using any database system, whether it is Oracle, MySQL, PostgreSQL, SQL Server, etc.

Let us first discuss Functional Dependence, which is crucial in understanding the Normalization Process. This is merely a big term for a relatively simple idea. To illustrate it, lets take a look at a small sample table.

This relatively simple table is a good example of functional dependence, it can also be used to illustrate a point.

Definition: A column is functionally dependent on another column if a value 'A' determines a single value for 'B' at any one time.

Sound confusing? Let me explain. The field 'Rate' is functionally dependent on the field 'Pay Class'. In other words, Pay Class determines Rate.

To determine functional dependency, you can think of it like this: Given a value for Field A, can you determine the single value for B? If B relies on A, then A is said to functionally determine B.

Taking the same table as above, lets add to it.

Now, lets look at this table and find some more Functional Dependencies. We already know that Pay Class determines Rate. We can also say that Sales Rep Number determines Last Name. Only one Sales Rep Number for each Last Name. This fits the definition of a Functional Dependency.

But does Last Name functionally determine anything? At first glance, some people might say yes, however, this is not true. Currently, you can say that Ward will only give you one Sales Rep Number, however, what if we hired another person with the name Ward? Then you would have two values for your Sales Rep Number, and then Last Name would no longer functionally determine anything.

On Keys
Now that we know what functional dependence is, we can clarify keys. Now, if you are working in databases, you probably already know what Primary Keys are. But, can you define them?

Definition: Column A is the primary key for table T if:
Property 1. All columns in T are functionally dependent on A
Property 2. No sub collections of the columns in table T also have Property 1.

This makes perfect sense. If all your fields in a database are dependent on one and only one field, then that field is the key. Now, occasionally Property 2 is broken, and two fields are candidates for the Primary Key. These keys are then called candidate keys. From these candidate keys, one key is chosen and the others are called alternate keys.
Back to top Go down
View user profile
Concepts of Database Design and Management
Back to top 
Page 1 of 1
 Similar topics
» CSE to Introduce Progressive Risk Management Model
» Please share your views on a habbit I have [Dummy portfolio management]
» SEC management & Government will be killing over investors
» Property Management
» Interest Rate Risk (IRR) and Asset Liability Management (ALM)

Permissions in this forum:You cannot reply to topics in this forum
Job98456 :: Databases-
Jump to: