What is used to create relationships between tables in a relational database?
Relational database was proposed by Edgar Codd (of IBM Research) around 1969. It has since become the dominant database model for commercial applications (in comparison with other database models such as hierarchical, network and object models). Today, there are many commercial Relational Database Management System (RDBMS), such as Oracle, IBM DB2 and Microsoft SQL Server. There are also many free and open-source RDBMS, such as MySQL, mSQL (mini-SQL) and the embedded JavaDB (Apache Derby). Show
A relational database organizes data in tables (or relations). A table is made up of rows and columns. A row is also called a record (or tuple). A column is also called a field (or attribute). A database table is similar to a spreadsheet. However, the relationships that can be created among the tables enable a relational database to efficiently store huge amount of data, and effectively retrieve selected data. A language called SQL (Structured Query Language) was developed to work with relational databases. Database Design ObjectiveA well-designed database shall:
Relational Database Design ProcessDatabase design is more art than science, as you have to make many decisions. Databases are usually customized to suit a particular application. No two customized applications are alike, and hence, no two database are alike. Guidelines (usually in terms of what not to do instead of what to do) are provided in making these design decision, but the choices ultimately rest on the you - the designer. Step 1: Define the Purpose of the Database (Requirement Analysis)Gather the requirements and define the objective of your database, e.g. ... Drafting out the sample input forms, queries and reports, often helps. Step 2: Gather Data, Organize in tables and Specify the Primary KeysOnce you have decided on the purpose of the database, gather the data that are 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 identify the each of the rows. Primary KeyIn the relational model, a table cannot contain duplicate rows, because that would create ambiguities in retrieval. To ensure uniqueness, each table should have a column (or a set of columns), called primary key, that uniquely identifies every records of the table. For example, an unique number Most RDBMSs build an index on the primary key to facilitate fast search and retrieval. The primary key is also used to reference other tables (to be elaborated later). You have to decide which column(s) is to be used for primary key. The decision may not be straight forward but the primary key shall have these properties:
Consider the followings in choose the primary key:
Let's illustrate with an example: a table Step 3: Create Relationships among TablesA database consisting of independent and unrelated tables serves little purpose (you may consider to use a spreadsheet instead). The power of relational database lies in the relationship that can be defined between tables. The most crucial aspect in designing a relational database is to identify the relationships among tables. The types of relationship include:
One-to-ManyIn a "class roster" database, a teacher may teach zero or more classes, while a class is taught by one (and only one) teacher. In a "company" database, a manager manages zero or more employees, while an employee is managed by one (and only one) manager. In a "product sales" database, a customer may place many orders; while an order is placed by one particular customer. This kind of relationship is known as one-to-many. One-to-many relationship cannot be represented in a single table. For example, in a "class roster" database, we may begin with a table called To support a one-to-many relationship, we need to design two tables: a table The column Take note that for every value in the parent table, there could be zero, one, or more rows in the child table. For every value in the child table, there is one and only one row in the parent table. Many-to-ManyIn a "product sales" database, a customer's order may contain one or more products; and a product can appear in many orders. In a "bookstore" database, a book is written by one or more authors; while an author may write zero or more books. This kind of relationship is known as many-to-many. Let's illustrate with a "product sales" database. We begin with two tables: To support many-to-many relationship, we need to create a third table (known as a junction table), say The many-to-many relationship is, in fact, implemented as two one-to-many relationships, with the introduction of the junction table.
One-to-OneIn a "product sales" database, a product may have optional supplementary information such as Instead, we can create another table (say Some databases limit the number of columns that can be created inside a table. You could use a one-to-one relationship to split the data into two tables. One-to-one relationship is also useful for storing certain sensitive data in a secure table, while the non-sensitive ones in the main table. Column Data TypesYou need to choose an appropriate data type for each column. Commonly data types include: integers, floating-point numbers, string (or text), date/time, binary, collection (such as enumeration and set). Step 4: Refine & Normalize the DesignFor example,
NormalizationApply the so-called normalization rules to check whether your database is structurally correct and optimal. First Normal Form (1NF): A table is 1NF if every cell contains a single value, not a list of values. This properties is known as atomic. 1NF also prohibits repeating group of columns such as Second Normal Form (2NF): A table is 2NF, if it is 1NF and every non-key column is fully dependent on the primary key. Furthermore, if the primary key is made up of several columns, every non-key column shall depend on the entire set and not part of it. For example, the primary key of the Third Normal Form (3NF): A table is 3NF, if it is 2NF and the non-key columns are independent of each others. In other words, the non-key columns are dependent on primary key, only on the primary key and nothing else. For example, suppose that we have a Higher Normal Form: 3NF has its inadequacies, which leads to higher Normal form, such as Boyce/Codd Normal form, Fourth Normal Form (4NF) and Fifth Normal Form (5NF), which is beyond the scope of this tutorial. At times, you may decide to break some of the normalization rules, for performance reason (e.g., create a column called Integrity RulesYou should also apply the integrity rules to check the integrity of your design: Entity Integrity Rule: The primary key cannot contain NULL. Otherwise, it cannot uniquely identify the row. For composite key made up of several columns, none of the column can contain NULL. Most of the RDBMS check and enforce this rule. Referential Integrity Rule: Each foreign key value must be matched to a primary key value in the table referenced (or parent table).
Most RDBMS can be setup to perform the check and ensure the referential integrity, in the specified manner. Business logic Integrity: Beside the above two general integrity rules, there could be integrity (validation) pertaining to the business logic, e.g., zip code shall be 5-digit within a certain ranges, delivery date and time shall fall in the business hours; quantity ordered shall be equal or less than quantity in stock, etc. These could be carried out in validation rule (for the specific column) or programming logic. Column IndexingYou could create index on selected column(s) to facilitate data searching and retrieval. An index is a structured file that speeds up data access for Index can be defined on a single column, a set of columns (called concatenated index), or part of a column (e.g., first 10 characters of a How are relationships created in a relational database?In a relational database, a relationship is formed by correlating rows belonging to different tables. A table relationship is established when a child table defines a Foreign Key column that references the Primary Key column of its parent table.
Which type is used to create a relationship between tables?There are three types of relationships in Access between tables: One-to-one. One-to-many. Many-to-many.
How are tables related in a relational database?All tables in a relational database have an attribute known as the primary key, which is a unique identifier of a row, and each row can be used to create a relationship between different tables using a foreign key—a reference to a primary key of another existing table.
What is a type of relationship that can be created between tables in a relational database?One of the most common relationships between tables in well-designed databases is the one-to-many relationship. Relationships between tables normally rely on the primary key in one of the tables. Recall that the primary key is a unique identifier (often numeric) for each record.
|