Relationships - SQL Server 2008 Tutorial
Download
Report
Transcript Relationships - SQL Server 2008 Tutorial
SQL Server 2008 – Entity
Relationships in a Database
Learningcomputer.com
Some Terms to get us going
Entity relationship diagram (ERD) shows the db structure
An Entity or Table is any object that we store information on
An Attribute or a field is a specific value in a Table like SSN in
an Employee table
A Row or Record is one instance on an object in a Table
A Primary Key (PK) is a field that can server as a unique
identifier in a table
A Foreign Key (FK) is a field in a child table that stores
related info from a parent table e.g. Customer and Orders
Relationship is an association between tables
Customer and SalesOrderHeader
(AdventureWorks2008)
Types of Database Relationship
One to One
One to Many (Most common)
Many to Many
One - One Relationship
In this relationship, a row in table A can have no more than
one matching row in table B, and vice versa
Not very common
Divide a table with many columns into two for performance
Isolate part of a table for security reasons
Example Account and Account Ext table
One - Many Relationship
This is the most common type of relationship.
In this type of relationship, a row in table A can have one to
many matching rows in table B, but a row in table B can have
only one matching row in table A
Typically the PK of the primary (parent) table matches the
same data (FK) in the secondary (child) table.
Example is Customer and SalesOrderHeader table shown
earlier
Many to Many Relationship
In this type, a row in table A can have many matching rows in
table B, and vice versa
You create such a relationship by defining a third table, called
a junction table, whose primary key consists of the foreign
keys from both table A and table B
Example would be Students and Teachers tables
College database
I have created a new database called College
First I will create and populate student data using TSQL
Second I will create and populate teacher data using TSQL
Now remember this is a many – many relationship so what
do we need? We need a junction table
I will create the junction table called student_teacher which
will contain a field for studentid and one for teacherid
Demo
What in the world is a Join?
In order to understand relationships, you have to know Joins
Joins are an integral component of relational database design
and usage
Joins let you match data from multiple tables; based on
significant key information
A typical join condition specifies a foreign key from one table
and its associated primary key in the other table
Types of Joins are INNER and OUTER
Inner Join
A join that displays only the rows that have a match in both
joined tables
Most common type of join in practice
For example, you can join the Customers and
SalesOrderHeader tables to create a result set that shows the
Orders for each Customer
Null values do not match other null values so they are
ignored
You can have any number of joins in one statement as long as
there is a valid relationship between the tables
Syntax for Inner Join
Old Syntax is SQL-87:
SELECT * FROM SALES.CUSTOMER C,
SALES.SALESORDERHEADER S
WHERE C.CUSTOMERID=S.CUSTOMERID
New Syntax (ANSI SQL or SQL-92):
SELECT * FROM SALES.CUSTOMER C INNER JOIN
SALES.SALESORDERHEADER S
ON C.CUSTOMERID=S.CUSTOMERID
Demo
Outer Join
A join that includes rows even if they do not have related
rows in the joined table
Outer joins are typically used to find corrupt data or
unmatched data
After the results, you can run a delete query to remove the
problem records
Three variations of an outer join to specify the unmatched
rows to be included
Types of Outer Joins
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
Left Outer Join
All rows from the first-named table (the "left" table, which
appears leftmost in the JOIN clause) are included.
Unmatched rows in the right table do not appear.
We are going to use Customer and SalesOrderHeader tables
Basically we are interested in finding out Customers that have
yet to place an order
Demo
Right Outer Join
All rows in the second-named table (the "right" table, which
appears rightmost in the JOIN clause) are included.
Unmatched rows in the left table are not included.
Similar in concept to Left Outer Join except it is reversed
Full Outer Join
All rows in all joined tables are included, whether they are
matched or not.
For example, a full outer join between students and teachers
shows all students and all teachers, even those that have no
match in the other table.
This type of join is not very common
Referential integrity
Referential integrity is a system of rules that ensure
relationships between related tables are valid
You cannot enter a value in the foreign key column of the
child table (Orders) if that value does not exist in the
primary key of the parent table (Customers)
You cannot delete a row from a primary table (Customers) if
rows matching it exist in a related table (Orders)
In SQL Server you can enforce this via Cascade Updates and
Delete or through the use of Triggers