Database Management Systems
Mapping E-R to Physical Database
Pınar Yıldırım
Database design
1-Conceptual Design(E-R)
2-Logical Design
3-Physical Design
STEPS IN TABLE CREATION
• Identify data types for attributes
• Identify columns(null or not null)
• Identify columns that must be unique
• Identify primary and foreign keys
• Identify constraints on columns
• Create tables
Transforming E-R into Logical Model
1:N Relationship
Logical Model
Foreign Key
Personnel{SSN, P_Name}
Department{Dep_No, Dep_Name, SSN}
Personnel_Phone{SSN,Phone}
Transforming into Physical Model
1:N Relationship
CREATE TABLE PERSONNEL (
SSN INTEGER,
P-NAME CHAR(30),
PRIMARY KEY (SSN),
CREATE TABLE DEPARTMENT(
DEP_NO INTEGER,
DEP_NAME CHAR(20),
SSN INTEGER,
PRIMARY KEY(DEP_NO));
FOREIGN KEY(SSN) REFERENCES PERSONNEL(SSN));
CREATE TABLE PERSONNEL-PHONE (
SSN INTEGER,
PHONE INTEGER,
PRIMARY KEY(SSN),
FOREIGN KEY(SSN) REFERENCES PERSONNEL(SSN)
ON DELETE CASCADE);
N:M Relationship
Logical Model
Customer{C_No, C_Name, C_Address}
Product{P_No, P_Name, Price}
Customer-Product{C_No,P_No} OR
{ID, C_No, P_No}
Physical Model
CREATE TABLE CUSTOMER (
C_NO INTEGER,
C-NAME CHAR(30),
C_ADDRESS CHAR(40),
PRIMARY KEY (C_NO),
CREATE TABLE PRODUCT( SECOND OPTION
CREATE TABLE CUSTOMER-PRODUCT(
P_NO INTEGER,
ID INTEGER
P_NAME CHAR(20),
C_NO INTEGER,
P_PRICE FLOAT, P_NO INTEGER,
PRIMARY KEY(P_NO)); PRIMARY KEY(ID),
FOREIGN KEY(C_NO) REFERENCES CUSTOMER(C_NO),
FIRST OPTION FOREIGN KEY(P_NO) REFERENCES PRODUCT(P_NO));
CREATE TABLE CUSTOMER-PRODUCT(
C_NO INTEGER,
P_NO INTEGER,
PRIMARY KEY(C_NO, P_NO),
FOREIGN KEY(C_NO) REFERENCES CUSTOMER(C_NO),
FOREIGN KEY(P_NO) REFERENCES PRODUCT(P_NO));
1:1 Relationship
Logical Model
A- Employee{SSN, Name, ID}
Parking Place{ID, Location}
B- Employee{SSN, Name}
Parking Place{ID, Location, SSN}
Physical Model(1. OPTION)
CREATE TABLE EMPLOYEE (
SSN INTEGER,
NAME CHAR(30),
ID INTEGER,
PRIMARY KEY (SSN)
FOREIGN KEY ID REFERENCES PARKING PLACE(ID)
ON UPDATE CASCADE);
CREATE TABLE PARKING PLACE(
ID INTEGER,
LOCATION CHAR(20),
PRIMARY KEY(ID));
Physical Model(2. OPTION)
CREATE TABLE EMPLOYEE (
SSN INTEGER,
NAME CHAR(30),
PRIMARY KEY (SSN));
CREATE TABLE PARKING PLACE(
ID INTEGER,
LOCATION CHAR(20),
SSN INTEGER,
PRIMARY KEY(ID));
FOREIGN KEY (SSN)REFERENCES EMPLOYEE(SSN));
Composite Attribute
Logical Model:
Customer{Customer_ID, C_Name, C_Street,C_City}
Physical Model
CREATE TABLE CUSTOMER(
CUSTOMER_ID INTEGER,
C_NAME CHAR(30),
C_STREET CHAR(30);
C_CITY CHAR(25);
PRIMARY KEY (CUSTOMER_ID));
Multivalued Attribute
Logical Model:
Employee{E_ID, E-name}
Employee-Languages{E_ID, E-languages}
Physical Model
CREATE TABLE EMPLOYEE(
E_ID INTEGER,
E_NAME CHAR(30),
PRIMARY KEY (E_ID));
CREATE TABLE EMPLOYEE-LANGUAGES(
E_ID INTEGER,
E_LANGUAGES CHAR(30),
PRIMARY KEY (E_ID));
FOREIGN KEY(E_ID) REFERENCES EMPLOYEE(E_ID)
ON DELETE NO ACTION);
Weak Entity
Logical Model:
Employee{E_ID, E_name}
Dependent{D_ID,E_ID, F_name, L_name}
Physical Model
CREATE TABLE EMPLOYEE(
E_ID INTEGER,
E_NAME CHAR(30),
PRIMARY KEY (E_ID));
CREATE TABLE DEPENDENT(
D_ID INTEGER,
E_ID INTEGER,
F_NAME CHAR(20),
L_NAME CHAR(25),
PRIMARY KEY (D_ID, E_ID));
FOREIGN KEY(E_ID) REFERENCES EMPLOYEE(E_ID));
Ternary relationship
Logical Model:
Patient{Patient_ID, Patient_name}
Physician{P_ID,P_name}
Treatment{Treatment_code, Treatment_description}
Patient-Physician-Treatment{Patient_ID, P_ID, Treatment_code}
Physical Model
CREATE TABLE PATIENT(
PATIENT_ID INTEGER,
P_NAME CHAR(30),
PRIMARY KEY (P_ID));
CREATE TABLE PHYSICIAN(
P_ID INTEGER,
P_NAME CHAR(25),
PRIMARY KEY (P_ID));
CREATE TABLE TREATMENT(
TREATMENT_CODE INTEGER,
TREATMENT_DEESCRIPTION CHAR(25),
PRIMARY KEY (TREATMENT_CODE));
CREATE TABLE PATIENT-PHYSICIAN-TREATMENT(
PATIENT_ID INTEGER,
P_ID INTEGER(30),
TREATMENT_CODE INTEGER,
PRIMARY KEY (PATIENT_ID,P_ID, TREATMENT_CODE ),
FOREIGN KEY(PATIENT_ID) REFERENCES PATIENT(PATIENT_ID),
FOREIGN KEY(P_ID) REFERENCES PHYSICIAN (P_ID),
FOREIGN KEY(TREATMENT_CODE) REFERENCES TREATMENT (TREATMENT_CODE));
Supertype/subtype relationships
Logical Model
Physical Model
CREATE TABLE EMPLOYEE(
EMPLOYEENUMBER INTEGER,
EMPLOYEENAME CHAR(30),
EMPLOYEEADDRESS CHAR(40),
EMPLOYEEDATEHIRED DATE,
EMPLOYEETYPE CHAR(20),
PRIMARY KEY (EMPLOYEENUMBER));
CREATE TABLE HOURLY_EMPLOYEE(
HEMPLOYEENUMBER INTEGER,
HOURLYRATE INTEGER
PRIMARY KEY (HEMPLOYEENUMBER),
FOREIGN KEY (HEMPLOYEENUMBER) REFERENCES EMPLOYEE(EMPLOYEENUMBER));
CREATE TABLE CONSULTANT(
CEMPLOYEENUMBER INTEGER,
CONTRACTNUMBER INTEGER,
BILLINGRATE INTEGER,
PRIMARY KEY (CEMPLOYEENUMBER),
FOREIGN KEY (CEMPLOYEENUMBER) REFERENCES EMPLOYEE(EMPLOYEENUMBER));
CREATE TABLE SALARIED EMPLOYEE(
SEMPLOYEENUMBER INTEGER,
ANNUALSALARY INTEGER,
STOCKOPTION INTEGER,
PRIMARY KEY (SEMPLOYEENUMBER),
FOREIGN KEY (SEMPLOYEENUMBER) REFERENCES EMPLOYEE(EMPLOYEENUMBER));
Example
Conceptual Design(E-R)
Logical Design
Student{Student_No, S_Name, S_Address, Dep_No, D_No}
Department{Dep_No, Dep_Name}
Dormitory{D_No, D_Name}
Physical Design
CREATE TABLE STUDENT(
STUDENT_NO INTEGER,
S_NAME CHAR(20),
S_ADDRESS CHAR(30),
DEP_NO INTEGER,
D_NO INTEGER,
PRIMARY KEY (STUDENT_NO),
FOREIGN KEY(DEP_NO) REFERENCES DEPARTMENT(DEP_NO),
FOREIGN KEY(D_NO) REFERENCES DORMITORY(D_NO));
Physical Design
CREATE TABLE DEPARTMENT(
DEP_NO INTEGER,
DEP_NAME CHAR(20),
PRIMARY KEY(DEP_NO));
CREATE TABLE DORMITORY(
D_NO INTEGER,
D_NAME CHAR(20),
PRIMARY KEY(D_NO));
EXAMPLE DATA
Student
Student_No S_Name S_Address Dep_No D_No
2 Ayse Tuzla 77 22
3 Ali Levent 99 44
4 Hakan Tuzla 77 22
5 Pelin Kadıköy 88 22
6 Yılmaz Sarıyer 99 33
Department
Department
Dep_No
77
Dep_Name
Computer Eng.
D_No D_Name Dormitory
22 A
88 Industrial Eng. 33 B
99 Electronics Eng. 44 C
Example
Conceptual Design(E-R)
Logical Design
Employee {SSN, E_Name, E_Address, Dep_No, Pr_No}
Department {Dep_No, Dep_Name}
Project {Pr_No, Pr_Name}
Dependent{SSN,D_Name, D_Gender}
Physical Design
CREATE TABLE EMPLOYEE(
SSN INTEGER,
E_NAME CHAR(20),
E_ADDRESS CHAR(30),
DEP_NO INTEGER,
PR_NO INTEGER,
PRIMARY KEY(SSN),
FOREIGN KEY(DEP_NO) REFERENCES DEPARTMENT (DEP_NO)
FOREIGN KEY(PR_NO) REFERENCES PROJECT(PR-NO));
Physical Design
CREATE TABLE DEPARTMENT(
DEP_NO INTEGER,
DEP_NAME CHAR(20),
PRIMARY KEY(DEP_NO);
CREATE TABLE PROJECT(
PR_NO INTEGER,
PR_NAME CHAR(20),
PRIMARY KEY(PR_NO);
Physical Design
CREATE TABLE DEPENDENT(
SSN INTEGER,
D_NAME CHAR(20),
D_GENDER CHAR(1),
PRIMARY KEY(SSN,D_NAME),
FOREIGN KEY(SSN) REFERENCES EMPLOYEE(SSN));
Enhanced E-R Example
Conceptual Design
Logical Design
Employee {SSN, E_Name, E_Birthdate}
Engineer {SSN, Expertise}
Technician {SSN, Education}
Physical Design
CREATE TABLE EMPLOYEE(
SSN INTEGER,
E_NAME CHAR(20),
BIRTHDATE DATE,
PRIMARY KEY(SSN));
CREATE TABLE ENGINEER(
SSN INTEGER,
EXPERTISE CHAR(20),
PRIMARY KEY(SSN));
FOREIGN KEY (SSN) REFERENCES EMPLOYEE(SSN));
CREATE TABLE TECHNICIAN(
SSN INTEGER,
EDUCATION CHAR(20),
PRIMARY KEY(SSN)
FOREIGN KEY (SSN) REFERENCES EMPLOYEE(SSN));
References
• Modern Database Management 11th Edition, Jeffrey A. Hoffer, V. Ramesh, Heikki Topi ©
2013 Pearson Education, Inc. Publishing as Prentice Hall.
