TO CREATE RELATIONAL DATABASE FEW STEPS TO FOLLOW

Step 1: Define the Purpose of the Database:

The major purpose of a database is to provide the information system in its broadest sense that utilizes it with the information the system needs according to its own requirements. A certain broad set of requirements refines this general goal

Step 2: Gather Data, Organize in tables and Specify the first Key

Once you have decided on the purpose of the database, gather the data that is needed to be stored in the database. Divide the data into subject-based tables.

Choose one column (or a few columns) as the so-called primary key, which uniquely identifies each of the rows.

Primary Key

In the relational model, a table cannot contain duplicate rows, because that might create ambiguities in retrieval. To ensure uniqueness, each table should have a column (or a set of columns), called the primary key, that uniquely identifies every record of the table. For example, an unique number customerID are often used because the primary key for the purchasers table; productCode for Products table; isbn for Books table. A primary key's called an easy key if it's one column; it's called a composite key if it's made from several columns.

Most RDBMSs build an index on the first key to facilitate fast search and retrieval.

The primary key's also wont to reference other tables (to be elaborated later).

You have to decide which column(s) is to be used for the primary key. The decision might not be simple but the first key shall have these properties:

The values of primary key shall be unique (i.e., no duplicate value). For example, customerName might not be appropriate to be used because the primary key for the purchasers table, as there might be two customers with an equivalent name.

The primary key shall always have a value. In other words, it shall not contain NULL.

Consider the followings in choose the primary key:

The primary key shall be simple and familiar, e.g., employeeID for workers table and isbn for books table.

The value of the first key shouldn't change. Primary key is used to reference other tables. If you modify its value, you've got to vary all its references; otherwise, the references are going to be lost. For example, phoneNumber may not be appropriate to be used as primary keys for table Customers, because it might change.

Primary key often uses integer (or number) type. But it could even be other types, like texts. However, it is best to use a numeric column as primary key for efficiency.

Primary key could take an arbitrary number. Most RDBMSs support so-called auto-increment (or AutoNumber type) for integer primary key, where (current maximum value + 1) is assigned to the new record. This arbitrary number is fact-less, because it contains no factual information. Unlike factual information such as phone numbers, fact-less numbers are ideal for primary key, as it does not change.

Primary key's usually one column (e.g., customerID or productCode). But it could also make up several columns. You should use as few columns as possible.

Step 3: Create Relationships among Tables

A database consisting of independent and unrelated tables serves little purpose (you may concede to use a spreadsheet instead). The power of electronic database lies within the relationship which will be defined between tables. The most crucial aspect in designing a electronic database is to spot the relationships among tables. The types of relationship include:

1. one-to-many

2. many-to-many

3. one-to-one

one-to-many: In a relational database, a one-to-many relationship exists when one row in table A may be linked with many rows in table B, but one row in table B is linked to just one row in table A. It is important to notice that a one-to-many relationship isn't a property of the info , but rather of the connection itself.

many-to-many: Many-to-many relationships. A many-to-many relationship occurs when multiple records during a table are related to multiple records in another table. ... electronic database systems usually don't allow you to implement an immediate many-to-many relationship between two tables.

one-to-one: In a relational database, a one-to-one relationship exists when one row in a table may be linked with only one row in another table and vice versa. It is important to notice that a one-to-one relationship isn't a property of the info , but rather of the connection itself.

Step 4: Refine & Normalize the Design

For example,

adding more columns,

create a replacement table for optional data using one-to-one relationship,

split a large table into two smaller tables,

others.

Normalization is that the process of minimizing redundancy from a relation or set of relations. Redundancy in relation may cause insertion, deletion and updation anomalies. ... Normal forms are wont to eliminate or reduce redundancy in database tables.

First normal form (1NF) : As per the rule of first normal form, an attribute (column) of a table cannot hold multiple values. It should hold only atomic values.

Second normal form (2NF): A table is claimed to be in 2NF if both the subsequent conditions hold:

Table is in 1NF (First normal form)

No non-prime attribute depends on the right subset of any candidate key of table.

An attribute that's not a part of any candidate key's referred to as non-prime attribute.

Third Normal form (3NF): A table design is claimed to be in 3NF if both the subsequent conditions hold:

Table must be in 2NF

Transitive functional dependency of non-prime attributes on any super key should be removed.

An attribute that is not part of any candidate key is known as a non-prime attribute.

In other words 3NF are often explained like this: A table is in 3NF if it's in 2NF and for every functional dependency X-> Y a minimum of one among the following conditions hold:

X is a super key of table

Y is a prime attribute of table

An attribute that is a part of one of the candidate keys is known as a prime attribute.

Referential Integrity Rule: Each foreign key value must be matched to a primary key value within the table referenced (or parent table).

You can insert a row with a far off key within the child table as long as the worth exists within the parent table.

If the worth of the key changes within the parent table (e.g., the row updated or deleted), all rows with this foreign key within the child table(s) must be handled accordingly. You could either (a) disallow the changes; (b) cascade the change (or delete the records) within the child tables accordingly; (c) set the key value within the child tables to NULL.

Most RDBMS can be set up to perform the check and ensure the referential integrity, in the specified manner.

Business logic Integrity: Beside the above two general integrity rules, there might be integrity (validation) concerning the business logic, e.g., postcode shall be 5-digit within a particular ranges, delivery date and time shall fall within the business hours; quantity ordered shall be equal or but quantity available , etc. These could be carried out in validation rules (for the specific column) or programming logic.