Chap 7-1 ER- 관계사상에의한 관계데이터베이스설계 2015.06.15 조완섭충북대학교경영정보학과 wscho@cbnu.ac.kr 1
목차 ERP-to-Tables Mapping 실무예제 대학교 스포츠 은행 버스운행 2
ERD to Relation Mapping ERD 는실세계를모델링한결과이지만관계 DBMS 를이용하여구현되어야함 ERD 를직접구현한 DBMS 는없음 ERD 를관계데이타베이스의테이블들로변환하는방법 엔티티타입은릴레이션으로매핑하며, 엔티티의속성은릴레이션의필드로만든다. 엔티티타입의키중에서하나를릴레이션의기본키로지정한다. Fname Minit Lname Name Address Ssn Sex EMPLOYEE Salary Employee name ssn address salary sex bdate Bdate 3
ERD to Relation Mapping 1:N 이진관계는 N-side 릴레이션의외래키속성으로매핑하며, 1- side 의주키를참조하도록한다. 1:1 이진관계는관계에참여하는두릴레이션중어느한테이블의외래키속성으로매핑한다. Number EMPLOYEE N WORKS_FOR 1 Name Locations DEPATMENT Employee WORKS_FOR name ssn address salary sex bdate dno Department name dnumber location 외래키 (foreign key) 4
ERD to Relation Mapping 다중값속성은키와다중값속성으로구성되는별도의릴레이션으로매핑된다. Number Dept_Locations Dnumber 1 Dlocation Houston Name Locations DEPATMENT Name 4 5 5 5 Stafford Bellaire Sugarland Houston Department dno dname phone 1 총무과 4 자재과 5 인사과 {Huston} {Stafford} {Bellaire, Sugarland, Houston} 5
ERD to Relation Mapping 약한엔티티타입도릴레이션으로매핑하되소유릴레이션 (owner relation) 의키속성을포함시킨다. 기본키 : 소유릴레이션의키 + 약한엔티티타입의부분키 Fname Minit Lname Name Address Ssn Bdate Sex EMPLOYEE 1 Salary Employee name ssn address salary sex bdate DEPENDENTS_OF N DEPENDENT Dependent name essn sex birthdate relationship Name Sex BirthDate Relationship 6
ERD to Relation Mapping N:M 이진관계는별도의릴레이션 ( 이를관계릴레이션이라고부름 ) 으로생성하고, 관계에참여하는두릴레이션의기본키를각각참조하는외래키로애트리뷰트를구성한다. 이때두외래키가관계릴레이션의기본키를형성한다. SSN Employee N WORKS_ON M Project Location Hours Name Number Employee name ssn address salary sex bdate dno Project name number location WORKS_ON ssn number hours 7
ERD to Relation Mapping n 차관계는관계에참여하는 n 개의릴레이션의키들로구성되는관계릴레이션으로매핑된다. 관계릴레이션은참여릴레이션의주키를참조하는외래키들과관계속성 ( 들 ) 으로구성된다. quantity SUPPLIER SUPPLY PROJECT pname sname PART partno Supplier sname Part partno Project pname Supply sname pname partno quantity 8
Fname Minit Lname ERD - Company Number Name Address N 1 WORKS_FOR Name Locations Sex Salary Ssn EMPLOYEE Bdate supervisee supervisor 1 SUPERVISION N StartDate 1 NumberOfEmployees 1 DEPATMENT 1 MANAGES CONTROLS Hours N M N WORKS_ON PROJECT 1 Name DEPENDENTS_OF Location N Number DEPENDENT Name Sex BirthDate Relationship ER- 관계사상 : COMPANY 데이타베이스를위한 ER 스키마다이어그램 6 판 9 Fundamentals of Database Systems
Relational Tables - Company EMPLOYEE FNAME MINIT LNAME SSN BDATE ADDRESS SEX SALARY SUPERSSN DNO DEPARTMENT DNAME DNUMBER MGRSSN MGRSTARTDATE DNUMBER DEPT_LOCATION DLOCATION PROJECT PNAME PNUMBER PLOCATION DNUM WORKS_ON ESSN PNO HOURS DEPENDENT ESSN DEPARTMENT_NAME SEX BDATE RELATIONSHIP ER- 관계사상 : 변환결과생성된 relational schema 6 판 10 Fundamentals of Database Systems
ER- 관계사상을이용한관계데이타베이스설계 ER Model 과 relational Model 의비교 ER Model Entity Type 1:1,1:N relationship type N:M relationship type N-ary relationship type Simple attribute Composite attribute Multi-valued attribute Value set Key attribute Relational Model Entity relation Foreign Key (or relationship relation) Relationship relation and two foreign keys Relationship relation and n foreign keys Attribute Set of simple component attribute Relation and foreign key Domain Primary (or secondary) key 6 판 11 Fundamentals of Database Systems
ERD 를활용한 Database 설계 - 예제 2015.06 충북대학교경영정보학과 조완섭
대학교정보데이터베이스 Database Requirements : 학생, 학과, 코스, 강의, 강사, 수강, 성적등의정보로구성되는대학교정보데이터베이스에대하여 1. ERD로설계하시오. 2. Tables로변환하시오. 3. Sample 데이터를입력하고, 다음과같은질의를작성하시오. - 학생이수강하는강의의이름과성적은? - Database 강의를수강하는학생의이름과학과는? - 홍길동강사가강의하는과목들의관리학과는? - 기타유용한질문 1~2개 - 홍길동학생이수강신청한과목의개수는? (SQL을사용해야함 ) 6판 Fundamentals of 13 Database Systems
은행데이터베이스 DB 요구사항분석 : 각은행은다수의지점을가지며, 각지점은다수의대출계좌와예금계좌를가진다. 각계좌에대하여주인인고객정보도기록한다. 위의정보를 ERD 로작성한후, 테이블로변환하시오. 테이블을생성하고, 데이터를적절하게입력하시오. 다음의질문을 SQL 로표현하고, DBMS 를이용하여수행하시오. 질문 : - 각은행에대하여계좌를가지고있는사람들의이름과주소는? - 홍길동이가진계좌의관리지점과은행명은? - 국민은행에서개설한지점명들은? - 고객별평균대출금액과최고대출금액은? ( SQL 사용 ) 6판 Fundamentals of 14 Database Systems
스포츠데이터베이스 Database Requirement Specification : 1. 각팀은팀이름, 연고지, 감독, 코치, 연락처등의정보를가지며, 소속선수들정보를가지고있어야한다. 2. 각선수는이름, 전화번호, 주포지션, 주소, 사진, 성적, 연봉정보를가지며, 어느팀에소속되는지, 어느게임무슨포지션으로참가하여어떤성적을냈는지에관한정보를관리해야한다. 3. 각게임에대하여장소, 시간, 관중수, 참가팀, 성적등의정보를기록해야한다. ( 실습 ) 1. 위의정보를 ERD로작성한후, 테이블로변환하시오. 2. 테이블을생성하고, 데이터를적절하게입력하시오. 3. 다음의질문을 SQL로표현하고, DBMS를이용하여수행하시오. - 1998년 10월 1일대전구장에서개최된게임에참가한팀의이름은? - 1998년 10월 1일대전구장에서개최된게임에참가한선수들의이름과각선수의성적은? - 홍길동선수가참가한게임중에서대전에서개최된게임의일시를출력하시오. - Samsung 팀의선수별평균출전회수는? (SQL을사용해야함 ) 6판 Fundamentals of 15 Database Systems
다음의데이터베이스구조는책 (BOOK), 출판사 (PUBLISHER), 저자 (BOOK_AUTHORS), 책을소장한도서관정보 (BOOK_COPIES), 도서관 (LIBRARY_BRANCH), 대출자 (BORROWER), 책대여정보 (BOOK_LOANS) 를유지하는도서관데이터베이스이다. 화살표는참조무결성조건을나타낸다. 16
도서 Book_id Title Publisher n m 소장 Brahch_id Branch_name Address 도서관 대여 예약 대여일자수량 예약일자 학생 Name Department Phone 17
버스운행정보데이터베이스 1. 운행은속성운행번호로식별되며, 다음의속성들로구성된다. - 운행번호 - 요일 - 출발도시 - 도착도시 - 구간개수 2. 운행은일반운행과특별운행으로나뉘어진다. 특별운행은속성으로행사명과날짜를추가로가진다. 3. 일일운행은날짜와운행번호로식별된다. 이개체는다른속성을가지지않는다. 일일운행의각엔티티는운행에관련되는버스와관련이있으며, 운전사와도관련이있다. 각운전사에대하여출근여부를기록한다. 4. 버스에관한정보를기록하기위하여차량번호, 좌석수, 최종점검일등의속성을가지며, 버스에관한문제를기록하기위하여정비날짜, 정비사이름, { 교체부품 } 등의정보를저장한다. 5. 노선구간은운행번호와구간번호로식별되며, 구간번호는운행에포함된각구간마다단계적으로숫자가주어진다. 노선구간의속성으로는출발도시, 출발시간, 도착도시, 도착시간, 가격, 거리등의정보를포함한다. 6. 일일노선구간은운행번호, 구간번호, 날짜로식별된다. 또한, 예약정보를위하여남은좌석과예약좌석을가진다. 7. 고객은단골여행객, 여행사, 일반고객으로나누어지며, 여행사의경우매출액정보를추가로가지고, 단골고객의경우마일리지정보를추가로가진다. 고객은이름과전화번호를공통으로가진다. 8. 고객은일일노선구간과승차및예약의관계를가진다. 6판 Fundamentals of 18 Database Systems