Download
1 / 38
380 likes | 610 Views
Entity Relationship Diagram (ERD) Creation. Alan Schneider. Overview. Relationships One-to-one One-to-Many Many-to-Many Relationship Characteristics Deletion Type Degree Business Rules A Physical ERD Relationship “Language” Final Note. Establishing Relationships.
E N D
Entity Relationship Diagram (ERD) Creation Alan Schneider
Overview • Relationships • One-to-one • One-to-Many • Many-to-Many • Relationship Characteristics • Deletion • Type • Degree • Business Rules • A Physical ERD • Relationship “Language” • Final Note
Establishing Relationships • A relationship is a crucial part of the database because it establishes a connection between a pair of tables that are logically related to each other in some form or manner • The logical relationship exists between the data contained in the tables
Establishing Relationships • It helps to further refine table structures and minimize redundant data. These benefits come about as a result of the manner in which the tables are connected • The process used to establish the relationship modifies the table structures in a manner that makes them more efficient • It is the mechanism that allows data from multiple tables to be drawn together simultaneously
Establishing Relationships • Relationship-Level integrity • Established when a relationship is properly defined. • Guarantees that the relationships are reliable and sound.
Establishing Relationships • In order to take advantage of the many benefits provided by a relational database, you must make certain that you establish each relationship carefully and properly • Failure to do so can make: • Working with multiple tables at the same time difficult • Inserting, updating, and deleting records in related tables difficult
Entity Relationship Elements • Entity Classes = Entire Table • Entity Instance = Single Row in a Table • Attributes = Columns • Identifiers = Primary Keys • Attributes that uniquely identify Entity Instances • Can consists of one or more attributes
Cross Reference, Union, Connection, Linking, Bridging, Junction, Transition, etc., table Establishing RelationshipsMany-To-Many: Data Example
Entity Relationship Exercise • Define the relationship for: • Sales Representative to a Sale • Items to an Order • Corporate Headquarters to Divisions to Locations • Favorite Movies to a Person
Establishing Relationship Characteristics • The final step in this procedure is to establish the characteristics of each relationship. • These characteristics indicate • What will occur when a record is deleted • The type of participation each table bears within the relationship • What degree each table participates in the relationship.
Deletion Rule • This rule defines what will happen if a user places a request to delete a record in the main table of a one-to-one relationship or in the "one" side of a one-to-many relationship. • Defining a deletion rule for a relationship helps to guard against "orphaned" records, which are: • Records that exist in a subordinate table of a one-to-one relationship but have no related records in a main table • Records that exist in the "many" side of a one-to-many relationship that have no related records in the "one" side.
Deletion Rule Options • Restrict • The requested record cannot be deleted if there are related records in the subordinate table of a one-to-one relationship or the "many" side of a one-to-many relationship. Related records must be deleted before the requested record can be deleted. • Cascade • The requested record will be deleted as well as all related records in the subordinate table of a one-to-one relationship or the "many" side of a one-to-many relationship.
Deletion Rule Options • Use a restrict deletion rule as a matter of course • Use a cascade deletion rule very judiciously • The best way to determine whether a cascade deletion rule is appropriate for a set of related tables is to study the relationship diagram for those tables
Table Type Participation • Each table participates within a relationship in a particular manner • A table's type of participation determines whether a record must exist in that table before a record can be entered into the other table
Table Type Participation • Mandatory • There must be at least one record in this table before you can enter any records into the other table. • Optional • There is no requirement for any records to exist in this table before you can enter any records into the other table.
Table Type Participation • The type of participation for most tables is usually determined later when you're defining Business Rules • However, it's common to establish the type of participation for tables in relationships where the type of participation for each table is: • Obvious • A result of common sense • Conformance to some set of standards
Table Degree Participation • Identify the total number of records in one table that can be related to a single record in the other table • The factors used in determining the degree of participation are the same as those used to determine the type of participation • Obvious • A result of common sense • Conformance to some set of standards
Relationship - Level Integrity • A direct result of properly establishing a table relationship and defining its characteristics in the proper manner • You have accomplished this by using: • A Primary key and a Foreign key to establish a one-to-one and one-to-many relationship, • A linking table to establish a many-to-many relationship.
Entity Attribute Exercise • Assign the following Attributes to an Entity Class, identify its Unique Identifier and if applicable, its relationship, to include the Type and Degree of Participation
What Are Business Rules? • A statement that imposes some form of constraint on elements within a field specification for a particular field or on characteristics of a relationship between a specific pair of tables. • Based on the way the organization perceives and uses its data; this perception is derived from the manner in which the organization functions or conducts its business.
What Are Business Rules? • Example: • "A Ship Date cannot be prior to an Order Date for any given order.” • Imposes a constraint on the Range of Values element of the field specifications for a Ship Date field • Imposed in order to ensure that the value of Ship Date is meaningful within the context of a "Sales Order." • Without this constraint, any date—including one prior to the Order Date—could be entered into the field, thus rendering the value in the Ship Date field meaningless
What Are Business Rules? • Because Business Rules depend on the manner in which an organization perceives and uses its data, several organizations might use Business Rules cast in the same words, yet apply them for completely different reasons.
Entity Attributes A Physical ERDThat is, the kind you will create for yourindividual and group assignments
A case can have one and only one Patient Patient Patient ID PI N R Last Name C20 R First Name C20 R Age N Gender C1 Case Case ID PI C25 R Begin Date D R End Date D R 1 1 One and only one Patient can be assigned to a case A patient can have one or more addresses. Such as Home and Billing Address Address ID PI N R Address 1 C30 R Address 2 C30 City C20 R State C2 R Patient Patient ID PI N R Last Name C20 R First Name C20 R Age N Gender C1 1 M An address can only be assigned to one person A lab can have one or more software applications installed Software Software ID PI N R Name C30 R Version C10 R Vendor C30 R Laboratory Lab ID PI N R Name C20 R Building C20 R Room C12 R M M A software application can be installed in one or more labs Relationship “Language”
Final Note • A Logical ERD, or “real” ERD, does not have Foreign Keys or Cross-Reference, Linking etc. tables
