Striking up a Relationship with Your Data A

Striking up a Relationship with Your Data A relationship is a connection between tables in the database. As you might expect, many types of relationships can exist in a database. The one-to-one relationship, for example, means that for each row in one table, there is at most one associated row in another table. If you want to get concrete, I can tell you that for each husband, there is at most one (legal) wife, and a wife has at most one (legal) husband at least in this country. You can also have many-to-one relationships, indicating that for a given row in a table, there can be multiple related rows in another table. A woman, for example, can have zero or more children. And then there are many-to-many relationships, in which there are no restrictions on the number of rows related to the row of a specific table. As I discuss in the preceding section, a foreign key in a database table is a key from another table that is contained within the table in question. The foreign key in a specific table is most often the primary key of the related table. Foreign keys complete an association or relationship between two tables by identifying the parent entity. Foreign keys provide a method for navigating between different instances of an entity (sometimes referred to as referential integrity). Every relationship in a database must be supported by a foreign key. The rule for referential integrity is that if a relational table has a foreign key, every value of the foreign key must either be null or match the values in the relational table in which that foreign key is a primary key. As you might expect, a relational database is in fact a set of tables with relationships. The job of a DBA is to select the appropriate relationships for the database being modeled. When the DBA completes a model for a database, he or she publishes it in the form of an entity-relationship (ER) model. An ER diagram is the document that communicates how all the tables in a database are related. It is also the roadmap for how to query a database for reporting purposes. You can see an example of an entity-relationship diagram in Figure 2-1. (The sample AdventureWorks database depicted here comes with SQL Server, so it s going to serve as my handy sample database of choice in this and many other chapters of this book.) The relationships between sales order, sales detail, territory, salesperson, and customer are clearly shown in this ER diagram, so it s easy to see how tables link up. Note that the scope of this diagram is not the entire database but merely the Sales Order table relationships in the database. This diagram was created as a database diagram with the SQL Server 2005 Management Studio, which I talk more about in Chapter 12. Chapter 2: Retrieving Data from a Database 31

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

Bookmark the permalink.

Comments are closed.