database.sarang.net
UserID
Passwd
Database
ㆍDBMS
MySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
DBMS Columns 579 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 579
Datamodeling and Relational Database Design (4)
작성자
정재익(advance)
작성일
2002-09-29 10:06
조회수
4,708
첨부파일: refining-pic.zip (18,139bytes)

Refining the E-R

 

Refining The Entity-Relationship Diagram

 

This section discusses four basic rules for modeling relationships

 

Entities Must Participate In Relationships

 

Entities cannot be modeled unrelated to any other entity. Otherwise, when the model was transformed to the relational model, there would be no way to navigate to that table. The exception to this rule is a database with a single table.

 

Resolve Many-To-Many Relationships

 

Many-to-many relationships cannot be used in the data model because they cannot be represented by the relational model. Therefore, many-to-many relationships must be resolved early in the modeling process. The strategy for resolving many-to-many relationship is to replace the relationship with an association entity and then relate the two original entities to the association entity. This strategy is demonstrated below Figure 6.1 (a) shows the many-to-many relationship:

 

Employees may be assigned to many projects.

Each project must have assigned to it more than one employee.

 

In addition to the implementation problem, this relationship presents other problems. Suppose we wanted to record information about employee assignments such as who assigned them, the start date of the assignment, and the finish date for the assignment. Given the present relationship, these attributes could not be represented in either EMPLOYEE or PROJECT without repeating information. The first step is to convert the relationship assigned to to a new entity we will call ASSIGNMENT. Then the original entities, EMPLOYEE and PROJECT, are related to this new entity preserving the cardinality and optionality of the original relationships. The solution is shown in Figure 1B.

 

Figure 1: Resolution of a Many-To-Many Relationship

 

 

 

Notice that the schema changes the semantics of the original relation to

 

 

employees may be given assignments to projects

and projects must be done by more than one employee assignment.

 

A many to many recursive relationship is resolved in similar fashion.

 

Transform Complex Relationships into Binary Relationships

 

Complex relationships are classified as ternary, an association among three entities, or n-ary, an association among more than three, where n is the number of entities involved. For example, Figure 2A shows the relationship

 

Employees can use different skills on any one or more projects.

Each project uses many employees with various skills.

 

Complex relationships cannot be directly implemented in the relational model so they should be resolved early in the modeling process. The strategy for resolving complex relationships is similar to resolving many-to-many relationships. The complex relationship replaced by an association entity and the original entities are related to this new entity. entity related through binary relationships to each of the original entities. The solution is shown in Figure 2 below.

 

Figure 2: Transforming a Complex Relationship

 

[img2]

 

Eliminate redundant relationships

 

A redundant relationship is a relationship between two entities that is equivalent in meaning to another relationship between those same two entities that may pass through an intermediate entity. For example, Figure 3A shows a redundant relationship between DEPARTMENT and WORKSTATION. This relationship provides the same information as the relationships DEPARTMENT has EMPLOYEES and EMPLOYEEs assigned WORKSTATION. Figure 3B shows the solution which is to remove the redundant relationship DEPARTMENT assigned WORKSTATIONS.

 

Figure 3: Removing A Redundant Relationship

 

[img3]

 

Summary

 

The last two sections have provided a brief overview of the basic constructs in the ER diagram. The next section discusses Primary and Foreign Keys.

[Top]
No.
제목
작성자
작성일
조회
582Datamodeling and Relational Database Design (7)
정재익
2002-09-29
5851
581Datamodeling and Relational Database Design (6)
정재익
2002-09-29
5377
580Datamodeling and Relational Database Design (5)
정재익
2002-09-29
4531
579Datamodeling and Relational Database Design (4)
정재익
2002-09-29
4708
578Datamodeling and Relational Database Design (3)
정재익
2002-09-29
5569
577Datamodeling and Relational Database Design (2)
정재익
2002-09-29
4595
576Datamodeling and Relational Database Design (1)
정재익
2002-09-29
5680
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.047초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다