The Relational model is today the foremost data model for commercial data processing applications. This model enables development of a very efficient and simple representation of the data. A relational database has a set of tables, each with a unique name. For example the Student table will consist of the following columns: Student name, Student Identity, and additional columns such as enrolment date, current status etc. These column headers are known as attributes. Each attribute ahs a set of permitted values, known as its Domain.
For example, a Class attribute would have the set of all classes as its domain. Each Table has a set of keys (a set of attributes of a table), which are used to identify a column or a row of a table. A Primary key uniquely identifies all the rows of a table. For example, the student table could have Roll Number or Registration Number as the primary key, since that number would uniquely identify any student. For efficiency and programming ease, other values may be used as primary keys, though careful analysis is required before assigning any set of attributes as the primary key.
A Foreign key is that attribute (column) which contains values from the primary key column of another table. Thus it is used to link tables among themselves. The Student table can be linked with the Class table in this way. To properly develop a Relational Database, adhering to the Database Application Life Cycle is vital. The development of the Relational Model involves the following major steps: Development of an ER Diagram (Entity Relationship Diagram), which will accurately represent the real world system, that is to be automated.
In this case, the Class Registration System needs to be represented in this ER diagram form. This will involve Creation of Tables, identification of columns for each table, granularity of data to be included etc. Improper analysis will result in poor and inefficient design. As the programmer gains additional knowledge about the system or there are changes in requirements or enhancements required, modifications need to be made to the ER diagram. All the Primary and Foreign key relationships need to be identified at this stage.
This step is the most important step in database design. Proper analysis is required at this step to devise the most efficient Primary and Foreign key combinations. Queries performed on the database would give proper results, and quickly and efficiently only if correct Primary and Foreign keys are devised. Normalization Redundancy of data results in undesirable increase in volume of data required being stored and inefficient SQL query results. Conversion of the Database in one of the following Normal Forms is essential for efficient performance of the Database.
• First Normal Form • BCNF: Boyce-Codd Normal Form • Third normal Form • Fourth normal Form • PJNF: Project-join-normal form • DKNF: Domain Key Normal Form There may be penalty of not keeping the Database in Normalized Form, and that may result in inconsistent data across the Database resulting in incorrect query results. After the completion of these steps the Database is created. However, the design may have to be changed during any stage of the process. Hence the developer may need to go to an earlier design stage to incorporate these modifications and redo the analyses.
Thorough Testing is required before the Database can be said to go Live. Prior to going live, a Test database needs to be created with demo data to check for inconsistencies and errors. In case any inconsistencies are discovered at this stage, the design process needs to be redone and changes made.
References: 1. Database System Concepts by Silberschatz, Korth & Sudarshan, McGraw Hill Publication 2. Web link: en. wikipedia. org/wiki/Life_cycle_of_a_relational_database 3. DB1. doc provided by Project Creator.