History , Evolution , Flat Files , RDBMS Vs DBMS , ERD , Normalization
If we open the chapters of the past regarding History , Evolution , Flat Files , RDBMS Vs DBMS , ERD , Normalization,then we will find that the practice of information collection was several thousands years old (Ancient Records)
The historians used to record the events,incidents,changes in the form of records.Those records were placed/written in papers (important to mention Ancient Egyptian Papyrus).To specify record keeping by means of proper definition,I would like to refer to this link:
It states that
Record keeping refers to the retention of records deemed important to a person, company or any other establishment. This goes hand in hand with records management, which are the creation, retention and scheduled destruction of an organization’s paper and film documents.
Here are three links mentioned for you to understand the entire history of current Database Management Systems
Old ways of storage before the arrival of databases in computer world
I want to point out the fact that a record represents single collection of attributes of something of an interest.
Let’s say I want to keep my record.The self suggested attributes of my record are
This is a record of only one person.How about the collection of records of several other persons ?
The collection of records of interest of same attribute(s) is referred to as an entity or in terms of computer science; a table.It is compulsory in a table to store the records having same attributes. A table consists of Columns or Attributes and Rows or Records.
The tables are joined together by means of Relations.These relations may be
I always like to use ERD in order to display the relationships.
Things to look for
Following are some definitions which we have to look first,then we will carry on further:
Super Key – Attribute(s) of the table;whether single or in combination,used to identify the table separately called Super Key
In Employee table;We can have the following Super Keys:
Candidate Key Further shortened Super Key.The single or combo of the keys enough to specify the table in a unique way
Example: ID,ID and SSN are enough to be eligible for the criteria to become the Candidate Key(s)
The most relevant and the shortest form of Candidate Key is the Primary Key.
Example:If the old ID’s of those employees who left are not reassigned to the newer ones and they are unique in any aspect,they can be termed as PK such as ID is enough to address the Employee
The name clearly suggests that Foreign Key is the Primary Key of one table which appears as a field in another table in order to establish the relationship is called the Foreign Key to that table.
Example: ID in the Employee table, which is its PK;appears in Defaulters table as a field to find those Employees who are in Defaulters list.
The combination of keys which are used to make the Primary Key are called Composite,Concatenated or Compound Keys.
Alternate Keys Alternate Key is any Candidate Key(s) which are not taken as Primary Key
Those attributes which are not in Super Key(s) but they can be used to address the table(s) are called Secondary Key(s).Suppose the scenario in which we are required to see the employees who live in Chicago
Now the stage is set and we will discuss those relationships after reviewing the ERD (Entity Relationship Diagram)
Don’t bother with the typical definitions.In our own words and ERD is self explanatory in its meaning. ERD is a template or diagrammatic representation of the whole conceptual data which has to be implemented in any organization supporting the Information System.
The need for the pictorial representation of the data in an organized format is necessary for the proper implementation of the application to operate to it in future.
ERD has some symbols which are required to be highlighted.So what are these symbols?
Symbols like those found in Egyptian tombs :)……No they are quite easy to understand and implement if you go along with me and throw away the negative thoughts like (I can’t do it).we will make you do it and we will show you so many cases here which involve ER modelling so that you will emerge as a champion.I will teach you the methodology which I adopt when I teach my students.We will follow Crow’s Foot Model.Click to Zoom the diagrams and see what these symbols denote.I will design the real world data model later so you won’t forget this technique
Edraw Max enables students, teachers and business professionals to reliably create and publish kinds of diagrams to represent any ideas. It’s an all-in-one graphics software that makes it simple to create professional-looking flowcharts, organizational charts, network diagrams, business presentations, building plans, mind maps, fashion designs, UML diagrams, workflows, program structures, web design diagrams, electrical engineering diagrams, directional maps, database diagrams and more.
With large pre-drawn libraries and more than 4600 vector symbols, drawing couldn’t be easier! Edraw Max lets you create a wide range of diagrams using templates, shapes, and drawing tools while working in an intuitive and familiar Office-style environment.
Normalization in Databases
What is Normalization?
Normalization is the technique applied in the science of database to divide or breakup the tables to remove redundancy(duplication of keys) as much as possible and to isolate the table(s) and when the change is applied to a single table,all the related tables might get that change if it is desired.
There are three types of Normalization techniques generally applied when designing the database(s).These are
Description of Normalization
Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency.
Redundant data wastes disk space and creates maintenance problems. If data that exists in more than one place must be changed, the data must be changed in exactly the same way in all locations. A customer address change is much easier to implement if that data is stored only in the Customers table and nowhere else in the database.
What is an “inconsistent dependency”? While it is intuitive for a user to look in the Customers table for the address of a particular customer, it may not make sense to look there for the salary of the employee who calls on that customer. The employee’s salary is related to, or dependent on, the employee and thus should be moved to the Employees table. Inconsistent dependencies can make data difficult to access because the path to find the data may be missing or broken.
There are a few rules for database normalization. Each rule is called a “normal form.” If the first rule is observed, the database is said to be in “first normal form.” If the first three rules are observed, the database is considered to be in “third normal form.” Although other levels of normalization are possible, third normal form is considered the highest level necessary for most applications.
As with many formal rules and specifications, real world scenarios do not always allow for perfect compliance. In general, normalization requires additional tables and some customers find this cumbersome. If you decide to violate one of the first three rules of normalization, make sure that your application anticipates any problems that could occur, such as redundant data and inconsistent dependencies.
The following descriptions include examples.
First Normal Form
Eliminate repeating groups in individual tables.
Create a separate table for each set of related data.
Identify each set of related data with a primary key.
Do not use multiple fields in a single table to store similar data. For example, to track an inventory item that may come from two possible sources, an inventory record may contain fields for Vendor Code 1 and Vendor Code 2.
What happens when you add a third vendor? Adding a field is not the answer; it requires program and table modifications and does not smoothly accommodate a dynamic number of vendors. Instead, place all vendor information in a separate table called Vendors, then link inventory to vendors with an item number key, or vendors to inventory with a vendor code key.
Second Normal Form
Create separate tables for sets of values that apply to multiple records.
Relate these tables with a foreign key.
Records should not depend on anything other than a table’s primary key (a compound key, if necessary). For example, consider a customer’s address in an accounting system. The address is needed by the Customers table, but also by the Orders, Shipping, Invoices, Accounts Receivable, and Collections tables. Instead of storing the customer’s address as a separate entry in each of these tables, store it in one place, either in the Customers table or in a separate Addresses table.
Third Normal Form
Eliminate fields that do not depend on the key.
Values in a record that are not part of that record’s key do not belong in the table. In general, any time the contents of a group of fields may apply to more than a single record in the table, consider placing those fields in a separate table.
For example, in an Employee Recruitment table, a candidate’s university name and address may be included. But you need a complete list of universities for group mailings. If university information is stored in the Candidates table, there is no way to list universities with no current candidates. Create a separate Universities table and link it to the Candidates table with a university code key.
EXCEPTION: Adhering to the third normal form, while theoretically desirable, is not always practical. If you have a Customers table and you want to eliminate all possible interfield dependencies, you must create separate tables for cities, ZIP codes, sales representatives, customer classes, and any other factor that may be duplicated in multiple records. In theory, normalization is worth pursing. However, many small tables may degrade performance or exceed open file and memory capacities.
It may be more feasible to apply third normal form only to data that changes frequently. If some dependent fields remain, design your application to require the user to verify all related fields when any one is changed.
Other Normalization Forms
Fourth normal form, also called Boyce Codd Normal Form (BCNF), and fifth normal form do exist, but are rarely considered in practical design. Disregarding these rules may result in less than perfect database design, but should not affect functionality.
Normalizing an Example Table
These steps demonstrate the process of normalizing a fictitious student table.
Student# Advisor Adv-Room Class1 Class2 Class3
1022 Jones 412 101-07 143-01 159-02
4123 Smith 216 201-01 211-02 214-01
First Normal Form: No Repeating Groups
Tables should have only two dimensions. Since one student has several classes, these classes should be listed in a separate table. Fields Class1, Class2, and Class3 in the above records are indications of design trouble.
Spreadsheets often use the third dimension, but tables should not. Another way to look at this problem is with a one-to-many relationship, do not put the one side and the many side in the same table. Instead, create another table in first normal form by eliminating the repeating group (Class#), as shown below:
Student# Advisor Adv-Room Class#
1022 Jones 412 101-07
1022 Jones 412 143-01
1022 Jones 412 159-02
4123 Smith 216 201-01
4123 Smith 216 211-02
4123 Smith 216 214-01
Second Normal Form: Eliminate Redundant Data
Note the multiple Class# values for each Student# value in the above table. Class# is not functionally dependent on Student# (primary key), so this relationship is not in second normal form.
The following two tables demonstrate second normal form:
Student# Advisor Adv-Room
1022 Jones 412
4123 Smith 216
Third Normal Form: Eliminate Data Not Dependent On Key
In the last example, Adv-Room (the advisor’s office number) is functionally dependent on the Advisor attribute. The solution is to move that attribute from the Students table to the Faculty table, as shown below:
Name Room Dept
Jones 412 42
Smith 216 42
complete credit to http://support.microsoft.com/kb/283878/en-us for normalization topic