1. Type of Relationship
It is a relationship that occurs in a table with another table, which functions to regulate the operation of a database. The relationship that can be formed can include 3 (three) types of relationships, namely.
a. One-To-One (1 -- 1)
Has the meaning "Each row of data in the first table is connected to only one row of data in the second table".
b. One-To-Many (1 -- )
Has the meaning "Each row of data from the first table can be connected to one or more rows of data in the second table".
c. Many-To-Many ( -- )
It means "One or more rows of data in the first table can be linked to one or more rows of data in the second table".
2. Building Relationships
- Open the SALES.MDB database, which you saved in your respective working folder.
- Click the Tools menu bar, click Relationship or click the Relationship icon on the database toolbar.
- Next, the Relationship window and Show Table window appear as below:
Figure 7.1. Relationships Window and Show Table Window
1]. Display the tables one by one by clicking the Add button.
2]. If all tables are in the Relationships window, click the Close button, it will look like the image below.
Figure 7.2. Tables to be connected
1. Next, connect each table and determine its Referential Integrity.
- Item Table with SalesData Table
- Sales Table with DataSales Table
3. Referential Integrity
Referential Integrity is a rule for relations between tables to ensure the validation of the relationship between records in related tables. and will automatically ensure the relationship whether or not there is a record on the Many side (the table connected to the Main table) whose foreign key does not have a partner in the One side table (Main Table).
Figure 7.3. Edit Relationships Window
3.1. Referential Integrity Relationship Model
There are two models of relations to Referential Integrity, by choosing one or both options, namely:
- Cascade Update Related Fields
- Cascade Delete Related Records
Cascade Update Related Fields
Every change to the Primary Key in the Main table automatically changes the value in the corresponding records in the tables that have a relationship with the Main table.
Cascade Delete Related Records
Every deletion of a Primary Key record in the Main table will result in the deletion of corresponding records in the tables that have a relationship with the Main table.
3.2. Referential Integrity Rules
- The field linked from the main table must be a Primary Key.
- Both related fields must have the same data type and data width.
- It is not permitted to delete records in the main table that have been linked to the related table.
- It is not allowed to add records to a relational table if the records contain data or facts that do not yet exist in the main table.
How to Display Relationships Between Tables
Hi guys, sorry, is there anyone who would like to help me? I'm stuck trying to figure out the SQL command to display related tables in the database. In this learning process, I am still using HeidiSQL, besides being helped by its UI, it is also easier to observe SQL commands, coincidentally my case study uses the northwind database.
Please enlighten me, or at least give me some guidance, clues, or something like that so that I can know the direction and purpose of studying this, and not get lost when browsing later.
Thank You,
Oh yes, as a note, the masters don't need to worry about explaining, because I have studied the basic concepts of SQL commands, to convince them I will try to summarize several things as a general overview.
Introduction
SQL is one of the 4th generation languages (4th GL) originally developed by IBM at the San Jose Research Laboratory. 4th generation SQL is request oriented and non-procedural so it is easy to learn. SQL consists of:
- DDL (Data Definition Language), which is a language that has the ability to define data related to the creation and deletion of objects. Such as CREATE, DROP and ALTER.
- DML (Data Manipulation Langauge), which is a language related to the data manipulation process. Such as INSERT, SELECT, UPDATE and DELETE.
Data Type
The data types supported by SQL Server 2000 are:
- Numeric to store numeric data consists of bigint, int, smallint, tinyint, bit, decimal and numeric.
- Money to store currency value data consists of money and smallmoney.
- Numeric Precision to accommodate high precision numeric data, namely float and real.
- Date Time to store date time data consists of datetime and smalldatetime.
- Strings to store character data consist of char, varchar and text.
- Unicode Character Strings to store character data of a certain size consist of nchar, nvarchar and ntext.
- Binary Strings to hold binary data consist of binary, varbinary and image.
- As well as other data types such as cursor, timestamp and uniqueidentifier.
Operator
Almost all operators in programming languages can be used in SQL Server 2000. The following is the order of operators based on the order of evaluation:
- + (positive), - (negative) and ~ (bitwise NOT).
- * (multiplication), / (division) and % (modulus).
- + (addition), + (combination and -- (subtraction).
- = > < >= <= <> != !> !<
- ALL, ANY, BETWEEN, IN LIKE, OR and SOME
- = (assignment)
Commands that are very good at displaying data relationships are the SELECTION, PROJECTION and JOIN operations in a single SQL command.
1. SELECTION
The SELECTION operation works on a single relation R and defines a relation that contains only R pairs that satisfy a certain condition (predicate). For example, finding employee salaries that are more than 10000. Predicates can be generated from logical operations AND, OR and NOT.
2. PROJECTION
The PROJECTION operation works on a single relation R and defines a relation that contains a vertical subset of R, taking values from specified attributes and eliminating duplicates.
3. JOIN
The JOIN operation is the same as the cross-product operation which searches for the same data in related columns between 2 tables in the query. In meeting certain query conditions, the use of the JOIN operation is better than the CROSS-PRODUCT operation in terms of time efficiency and searches performed. The JOIN operation will combine two relations into a new relational form, which is a basic operation in relational algebra.
4. Optimize JOIN Command
Here are the ways how Optimizer analyzes the JOIN command that will be executed in the query. To execute a JOIN command, Optimizer must identify several things, namely:
- How to Access JOIN Commands. For simple commands, the Optimizer must determine the most optimal way to access data from each JOINed table.
- JOIN Method. Every time there is a JOIN command, the Optimizer will determine which JOIN method is most appropriate to use, be it Nested Loop, Sort Merge, Cartesian or Hash Joins.
- Join Order. To execute more than 2 tables, the Optimizer will JOIN the two tables first. The result of joining the 2 tables will be JOINed again to the next table until all tables are finished being JOINED.