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 559 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 559
데이터베이스 모델링과 디자인의 기초
작성자
정재익(advance)
작성일
2002-09-17 15:14
조회수
6,682

이글은 다음 URL 의 글을 번역한 글입니다.

 

http://www.sqlteam.com/item.asp?ItemID=122

 

Spyder on 6/30/2000 in Table Design

 

이 아티클은 데이테베이스 디자인의 기본인 normalize, relationship 그리고 cardinality 등에 관한 얘기이다. 데이테베이스에 대한 전반적인 튜토리얼이다.

 

--------------------------------------------------------------------------------

 

데이테베이스 디자인과 Entity-relationship diagram (ERD) 의 생성은 어플리케이션 작성 과정에서 여전히 중요한 부분인데도 불고 하고 가끔은 어플리케이션 개발 lifecycle 동안에 간과 되기도 한다. 정확하고 가장 최신으로 유지된 데이타 모델은 DBA 와 개발자 그리고 JAD (joint application development) 팀의 다른 멤버들에는 중요한 개발 참조 자료로서 활용될 수 있다. 데이타모델의 생성 과정은 말단 사용자들이 하는 추가적인 의문사항을 없앨수 있다.

효율적인 데이테베이스 디자인은 팀이 처음 부터 퍼포먼스가 좋은 어플리케이션을 개발할 수 있도록 해 준다. 프로젝트의 질적인 측면에서 보면, 팀은 프로젝트를 완성하는데 걸리는 시간을 단축할 수 있다. 이것은 당연히 개발 비용의 절감으로 이어진다. 데이테베이스 디자인에서 중요한 점은 "두번 재어보고, 한번 잘라라 (measure twice, cut onece)" 이다.

 

효율적인 데이터베이스 디자이너는 데이터베이스를 디자인하는 동안에 정규화 (normalize) 를 항상 유념하고 있다. 정규화라고 하는 것은 다음의 네가지 목적을 이루기 위한 데이터베이스 디자인 접근방법이다.

 

1. minimization of data redundancy

2. minimization of data restructuring

3. minimization of I/O by reduction of transaction sizes

4. enforcement of referential integrity

 

효율적인 데이테베이스 디자인을 하고자 한다면 다음 기술과 개념은 반드시 명심하고 있어야 한다:

1. entity 라고 하는 것은 여러분들의 데이터베이스에서 상대적인 무엇인 가를 논리적으로 모아 놓은 것이다. entity 의 반대되는 물리적인 것은 데이터베이스 테이블이다. 여러분들의 entity 의 이름은 단일형태이며, 모두 대문자로 표기된다 (ALL CAPS). 예를 들면, 여러분들 회사 고용자들에 대한 자료를 포함하고 있는 EMPLOYEE 라는 이름의 entity 를 생각해 보자.

 

attribute (속성)은 entity 의 기술적 (descriptive) 또는 정량적 (quantitative) 인 성격을 가지게 될것이다. 그리고 attribute 의 물리적인 대응은 database 의 column 이 될 것이다.

 

An attribute is a descriptive or quantitative characteristic of an entity. The physical counterpart of an attribute is a database column (or field). Name your attributes in singular form with either Initial Capital Letters or in all lower case. For example, some attribute names for your EMPLOYEE entity might be: EmployeeId (or employee_id) and BirthDate (or birthdate).

 

 

A primary key is an attribute (or combination of attributes) that uniquely identify each instance of an entity. A primary key cannot be null and the value assigned to a primary key should not change over time. A primary key also needs to be efficient. For example, a primary key that is associated with an INTEGER datatype will be more efficient than one that is associated with a CHAR datatype. Primary keys should also be non-intelligent; that is, their values should be assigned arbitrarily without any hidden meaning. Sometimes none of the attributes of an entity are sufficient to meet the criteria of an effective primary key. In this case the database designer is best served by creating an "artificial" primary key.

 

 

A relationship is a logical link between two entities. A relationship represents a business rule and can be expressed as a verb phrase. Most relationships between entities are of the "one-to-many" type in which one instance of the parent entity relates to many instances of the child entity. For example, the relationship between EMPLOYEE and STORE_LOCATION would be represented as: one STORE_LOCATION (parent entity) employs many EMPLOYEEs (child entity).

 

 

The second type of relationship is the "many-to-many" relationship. In a "many-to-many" relationship, many instances of one entity relate to many instances of the other entity. "Many-to-many" relationships need to be resolved in order to avoid data redundancy. "Many-to-many" relationships may be resolved by creating an intermediate entity known as a cross-reference (or XREF) entity. The XREF entity is made up of the primary keys from both of the two original entities. Both of the two original entities become parent entities of the XREF entity. Thus, the "many-to-many" relationship becomes resolved as two "one-to-many" relationships. For example, the "many-to-many" relationship of (many) EMPLOYEEs are assigned (many) TASKs can be resolved by creating a new entity named EMPLOYEE_TASK. This resolves the "many-to-many" relationship by creating two separate "one-to-many" relationships. The two "one-to-many" relationships are EMPLOYEE (parent entity) is assigned EMPLOYEE_TASK (child entity) and TASK (parent entity) is assigned to EMPLOYEE_TASK (child entity).

 

 

A "foreign key" exists when the primary key of a parent entity exists in a child entity. A foreign key requires that values must be present in the parent entity before like values may be inserted in the child entity. The concept of maintaining foreign keys is known as "referential integrity".

 

 

Relationships between two entities may be classified as being either "identifying" or "non-identifying". Identifying relationships exist when the primary key of the parent entity is included in the primary key of the child entity. On the other hand, a non-identifying relationship exists when the primary key of the parent entity is included in the child entity but not as part of the child entity's primary key. In addition, non-identifying relationships may be further classified as being either "mandatory" or "non-mandatory". A mandatory non-identifying relationship exists when the value in the child table cannot be null. On the other hand, a non-mandatory non-identifying relationship exists when the value in the child table can be null.

 

 

Cardinality helps us further understand the nature of the relationship between the child entity and the parent entity. The cardinality of a relationship may be determined by asking the following question: "How many instances of the child entity relate to each instance of the parent entity?". There are four types of cardinality: (1.) One to zero or more (common cardinality), (2.) One to one or more (P cardinality), (3.) One to zero or one (Z cardinality), and (4.) One to exactly N (N cardinality).

 

In conclusion, effective database design can help the development team reduce overall development time and costs. Undertaking the process of database design and creating a data model helps the team better understand the user's requirements and thus enables them to build a system that is more reflective of the user's requirements and business rules. The act of performing database design is platform-independent so persons who use database systems other than SQL Server should also be able to benefit from these concepts.

[Top]
No.
제목
작성자
작성일
조회
568정보처리기술의 페러다임 - Data WareHousing 과 OLAP (3)
정재익
2002-09-22
5995
567정보처리기술의 페러다임 - Data WareHousing 과 OLAP (2)
정재익
2002-09-22
5242
566정보처리기술의 페러다임 - Data WareHousing 과 OLAP (1)
정재익
2002-09-22
4922
559데이터베이스 모델링과 디자인의 기초
정재익
2002-09-17
6682
528시스템 성능 최적화는 DB 튜닝에서 출발
정재익
2002-08-29
5670
527분석 목적과 대상 설정에 따른 튜닝 방법론
정재익
2002-08-29
4599
526데이터베이스 연결문자열을 웹에서 분리하자
정재익
2002-08-29
4058
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.047초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다