SQL Server 2008 – Entity Relationships in a Database

1. SQL Server 2008 – Entity Relationships in a Database Learningcomputer.com

2. 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

3. Customer and SalesOrderHeader (AdventureWorks2008)

4. Types of Database Relationship • One to One • One to Many (Most common) • Many to Many

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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

11. 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

12. 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

13. Types of Outer Joins • LEFT OUTER JOIN • RIGHT OUTER JOIN • FULL OUTER JOIN

14. 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

15. 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

16. 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

17. 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