4 THE RELATIONAL MODEL The Relational Model Relation

บทท 4 รปแบบฐานขอมลเช งสมพนธ THE RELATIONAL MODEL The Relational Model

Relation name Attribute Student Std_Fname Std_Lname Std_Address Std_Major Std_GPA Fac_ID 1234567801 Bob Norbert USA IT 3. 00 Sc 1234567821 Wiliam Pilgrim AUS CS 2. 50 Sc 1234567822 Luke Brazzi USA PHY 3. 50 Sc 1234567840 Homer Wells AUS CS 2. 50 Sc 2345678910 Candy Kendall NZ FIN 2. 75 Msc 2345678920 Roberto Morales AUS FIN 3. 25 Msc 2345789015 Tess Dodge CAN ECON 3. 75 Msc 4567890122 Joe Estrada CAN Art 3. 25 En 4567890133 Cristopher Colan USA MUS 2. 75 En 5678901235 Wally Kendall NZ GSc 3. 50 Ed 5678901240 Mariah Dodge CAN Gsc 2. 50 Ed Degree Cardinality Tuple Std_ID

การสร างร เลชน CREATE TABLE ( Std_ID Std_FName Std_LName Std_Address Std_Major Std_GPA Fac_ID ); Student CHAR(11), VARCHAR(20), VARCHAR(30), VARCHAR(50), VARCHAR(15), DECIMAL(3, 2), CHAR(5)

ตวอยาง Relation Employee. Number First. Name Last. Name Department Email Phone 100 Jerry Johnson Accounting JJ@somewhere. com 236 -9987 200 Mary Abernathy Finance MA@somewhere. com 444 -8898 300 Liz Smarthers Finance LS@somewhere. com 777 -0098 400 Tom Caruthers Accounting TC@somewhere. com 236 -9987 500 Tom Jackson Production TJ@somewhere. com 444 -9980 600 Eleanore Caldera Legal EC@somewhere. com 767 -09000 700 Richard Bandalone Legal RB@somewhere. com 767 -0900

ตวอยาง Table แตไมใช Relation Employee. Number First. Name Last. Name Department Email Phone 100 Jerry Johnson Accounting JJ@somewhere. com 236 -9987 200 Mary Abernathy Finance MA@somewhere. com 444 -8898 300 Liz Smarthers Finance LS@somewhere. com 777 -0098 400 Tom Caruthers Accounting TC@somewhere. com 236 -9987 , 266 -9987 , 555 -7171 500 Tom Jackson Production TJ@somewhere. com 444 -9980 600 Eleanore Caldera Legal EC@somewhere. com 767 -09000 700 Richard Bandalone Legal RB@somewhere. com 767 -0900 , 767 -0011

ตวอยาง Table แตไมใช Relation Employee. Number First. Name Last. Name Department Email Phone 100 Jerry Johnson Accounting JJ@somewhere. com 236 -9987 200 Mary Abernathy Finance MA@somewhere. com 444 -8898 300 Liz Smarthers Finance LS@somewhere. com 777 -0098 400 Tom Caruthers Accounting TC@somewhere. com 236 -9987 Fax : 266 -9987 Home : 555 -7171 500 Tom Jackson Production TJ@somewhere. com 444 -9980 600 Eleanore Caldera Legal EC@somewhere. com 767 -09000 Fax : 236 -9987 Home : 555 -7171 700 Richard Bandalone Legal RB@somewhere. com 767 -0900

ประเภทของค ย (Keys( Candidate Key Primary Key Composite Key Alternate key Foreign Key

Foreign Key Primary key Faculty Fac_ID Foreign key Fac_Name Phone Sc Science 201 Msc Management Science 145 En Engineering 512 Ed Education 301 Student Std_ID Std_Fname Std_Lname Std_Address Std_Major Std_GPA Fac_ID 1234567801 Bob Norbert USA IT 3. 00 Sc 1234567821 Wiliam Pilgrim AUS CS 2. 50 Sc 1234567822 Luke Brazzi USA PHY 3. 50 Sc 1234567840 Homer Wells AUS CS 2. 50 Sc 2345678910 Candy Kendall NZ FIN 2. 75 Msc 2345678920 Roberto Morales AUS FIN 3. 25 Msc 2345789015 Tess Dodge CAN ECON 3. 75 Msc 4567890122 Joe Estrada CAN Art 3. 25 En 4567890133 Cristopher Colan USA MUS 2. 75 En 5678901235 Wally Kendall NZ GSc 3. 50 Ed 5678901240 Mariah Dodge CAN Gsc 2. 50 Ed Parent relation Child relation

คณสมบต ของ Foreign Key 7. ร เลชนใดๆ สามารถเปนได ท ง Referenced Relation และ Referencing Relation 8. ร เลชนใดๆ สามารถม ความสมพนธด วย FK กบตวมนเองได (Self-Referencing) Emp. ID PK E 001 E 002 E 003 Ename Peter Nicole Willy Esurname EMgr Corp E 002 Terio Mac E 002 FK FK

THE END