Logical Database Design (1)

Database development

1. Conceptual database design
2. Logical database design
3. Physical database design
4. Database implementation
5. Database maintenance

---

Logical Database Design

• Conceptual data modeling: er모델을 사용해서 user requirement를 어떻게 나타내는가.

  • 얼마나 정확하게 er 모델에 user requirement를 나타내는가

    ===> Logical database design을 해야 conceptual data modeling -> logical data modeling

• Logical data modeling: 데이터베이스 기술을 사용하여 어떻게 데이터를 조직하느냐

• Relational data model: 압도적인 logical data modeling tool

Relational data model

• logical design과 database 개발에 사용되는 가장 일반적으로 사용되어 지는 모델

• logical database 디자인의 목표는 ERD와 같은 conceptual database design을 가지고 데이터베이스 기술을 사용해서 logical design으로 변환한다.

• 초기 데이터베이스 시스템은 relational data model에 기반되어짐 System R(IBM), Igres(USB)

• 현재 데이터베이스 시스템은 relational data model에 기반되어짐

  • Oracle, Mysql,,,

Relation?

• Definition: 이름이 있고 2차원의 테이블
• 이름을 가지고 있는 여러 개의 컬럼과 이름이 없고 개수가 정해지지않은 row들로 이루어진 관계
• erd에서 entity type, relationship type들의 attribute가 relation의 column이 된다.
• erd에서 entity type, relationship type의 instance가 relation의 row가 된다.
• relational data model의 관점으로 보면, 각각의 데이터베이스들의 관계로 나타내어진다.

---

Components of Relational Data Model

1. Data Structure: 데이터는 관계 또는 테이블로서 조직화된다. 각각은 row, column를 갖는다. --> 어떻게 데이터가 저장

2. Data integrity: data accuracy와 consistency를 보장하는데 사용되는 규칙과 메커니즘을 지원. 그리고 비즈니스 룰에 맞게 데이터를 저장하고 조작 --> 비즈니스룰에 기반해서 어떻게 데이터의 무결성이 지켜지는가

3. Data manipulation: 다른 관계들에서 저장된 데이터들을 직관적이고 강력한 SQL문으로 읽고 조작함 --> 데이터를 어떻게 읽고 쓰는 등의 조작하는가

보통 Data Structure, Data integrity를 주로 생각해서 만든다

1. Data Structure: Notation

• Short text statements
  • BOOK(BID, Title, Price, Stock)
• Graphical representation
• 같은 정보를 담고 있으면 같은 table이다.
  • 행이 비어도 같은 table이다.
  • stucture가 같으면 row, column 순서도 상관없다

• Properties of relation
  • 각각의 relation은 유니크한 이름을 가져야 한다.
  • 각각의 attribute들은 원자처럼 쪼갤 수 없어야 한다.(multivalued, composite은 허용이 안됨)
  • 각각의 row는 반드시 유니크해야 한다.(2개의 row들은 그들의 fields에서 완전히 같으면 안됨, PK만 다르면 괜찮음)
  • 하나의 relation에 있는 2개 이상의 column 이름은 같을 수 없다.
  • row, column의 순서는 무관하다.

• Key Fields
• Key들은 2개의 주된 목적들을 위한 특별한 column들이다.
  • Primary Key들은 relation에서 unique identifier이다.
  • Foreign Key들은 dependent relation이 parent relation을 참조하는 걸 가능하게 하는 identifier이다.
    • dependent relation: 부모를 참조하는 relation
    • parent relation: 부모의 역할을 해주는 relation
    • (주문테이블) 주문 번호, 주문 날짜, 고객ID // (고객테이블) 고객ID, 전화번호가 있을 때 고객ID를 통해 고객의 정보를 얻을 수 있으므로 주문이 dependent, 고객이 parent.
    • one to many일 때 many 쪽이 foreign key를 가지고 있고 one이 primary key이다. 이럴 때 foreign쪽이 dependent, primary쪽이 parent
• Key들은 1개 또는 2개 이상의 column일 수 있다.
• Key들은 보통 유저의 질문들에 반응하는 속도를 높이기 위한 인덱스에 사용되어진다.
  • db에서 primary Key를 선언하는 순간 그 column에는 자동으로 인덱싱이 걸린다.

2. Data integrity

데이터베이스에서 정확성과 무결성을 유지하는 것을 돕는 것이 data integrity이다. 크게 3가지가 있다.

1. Domain Constraints
2. Entity Integrity
3. Referential Integrity

• Domain Constraints
  • 어떤 특정 값들은 미리 정해진 domain에서 와야한다. 즉, 하나의 컬럼에서 오는 모든 값들은 같은 domain에서 와야한다. domain은 domain의 이름, 의미, 데이터 타입, 데이터 크기에 올 수 있는 값들을 이야기한다.
  • 가령 학생이라는 테이블이 있고 여러 컬럼이 있을 때, 학생의 이름 컬럼이 있다면 이것은 이미 정해진 domain이고, 데이터 타입, 이름 길이 등이 정의되어진 것을 말한다.

• Entity Integrity
  • 어떠한 primary key의 attribute도 null이 되면 안된다. 0도 아니고 blank도 아니고 그냥 값이 없는 것이 null이다.
  • 각각의 relation은 반드시 primary key가 있어야 한다.
  • conceptual design의 ER에서 weak entity type, EER에서 subtype은 identify attribute가 없었는데, logical design의 relational model에서는 모든 relation은 primary key를 가져야 한다.

• Referential Integrity
  • 각각의 foreign key 값은 그것이 참조하는 parent relation의 primary key와 매치되어야 하고, 그렇지 않으면 foreign key가 null이 되어야 한다.
    • ex) deptId가 fk로 dept를 참조하고 있을 때, employee 테이블에는 있지만, 부서배치를 받지 못한 사람의 deptId 값이 null이면 dept 테이블과 referential integrity 위반이 발생하지 않는다.
  • Delete Rule
    • Restrict: dependent쪽에 관련된 row가 존재할 때, parent쪽을 delete를 허용하지 않는다. referential constraint가 지켜져야만 데이터의 일관성이 지켜지기 때문이다.
    • Cascade: 연쇄적으로 관계있는 것들을 delete 한다.
    • Set to Null: null로 설정한다

---

ER Model -> Relational Data Model

conceptual design에서 ER Model이 나온다. 이것을 logical design에서 relational model로 바꾼다.

1. 각각의 entity type을 relation으로 바꾼다.
   • simple attribute일 경우 포함
   • composite attribute일 경우 뻗어나가는 것만 포함
   • identifier attribute를 primary key로 바꿈
   • multi-valued는 relation에 포함되지 않음

2. weak entity type도 relation이 된다.
   • primary key가 있어야 하는데, partial key + idetifying owner의 primary key가 primary key가 됨. --> composite primary key가 됨
   • idetifying owner의 primary key는 foreign key가 됨
   • relational model에서 foreign key를 반드시 명시해줘야 하는데, 밑에 바로 씀
     • ex) Foreign Key(Employee_ID) references EMPLOYEE(Employee_ID)
     • Employee의 Employee_ID를 참조하기 위한 foreign key이다. employee의 가족들에 대한 테이블인데, 어떤 employee인지 알기 위해 참조.

3. Cardinality Constraints에 따라 달라진다.
   • one-to-many
     • many쪽의 foreign key는 one쪽의 primary key로 씀
     • relationship type의 attribute도 many쪽으로 감
   • many-to many
     • 또하나의 relation으로 나눈다. 또하나의 relation은 원래 있었던 애들의 primary key가 들어와서 composite primary key가 됨
     • 만들어진 relation의 foreign key는 반드시 명시
     • relationship type의 attribute도 새로 만들어진 쪽으로 감
     • ex) Foreign Key(Employee_ID) references EMPLOYEE(Empoloyee_ID), Foreign Key(Course_ID) references COURSE(Course_ID) 이렇게 2개를 다 써야함
   • one-to-one
     • many가 될 가능성이 커보이는 쪽에 foreign key를 포함시킴
     • relation type의 attribute를 foreign key가 있는 쪽에 포함 시킴
     • Foriegn Key 명시 필수, Participation Constraint 고려해서 판단
4. Degree(Unary, Binary, Ternary) 고려해서 적용
   • Unary일때, one-to-one, one-to-many, many-to-many 적용
     • Entity type이 1개인데(recursive) one-to-many를 적용하려면, 자기 자신에게 foreign key를 지정
     • many-to-many는 테이블 하나 더 만들기 등등
   • Binary는 여태까지 했던 방식으로 one-to-one, one-to-many, many-to-many 적용
   • Ternary 이상일 때 one-to-one, one-to-many, many-to-many 적용
     • 가운데 있는 relationship type이 새로운 relation이 되고, 모든 primary key가 composite primary key로 포함, foreign key 역할
     • Foreign key도 명시
5. Supertype, Subtype 고려
   • Supertype의 attribute들은 supertype의 relation으로 들어감
   • Subtype의 attribute들은 subtype의 relation으로 들어감
   • Supertype의 primary key는 subtype의 primary key로 들어가고, subtype의 foreign key역할로 supertype을 참조함
   • Foreign key 명시
6. Multi-valued attribute를 가지고 있을때
   • multi-valued를 가지고 있던 entity type의 identifier attribute를 primary key로 갖는 테이블을 생성
   • 새로 만들어진 테이블의 primary key가 foreign key로도 작용
   • multi-valued인 attribute는 새로운 relation에 primary key로 포함, 따라서 composite primary key로 작용
   • Foreign key 명시
     • Foreign Key(EID) references EMPLOYEE(Employee_ID)