Term Project Week 5: Translation Method and Relational Schema

The assignment content discusses the translation method used for ERD to relational schema conversion. The mapped translation method is used, where each entity is transformed into a table with attributes and primary keys. Relationships are also mapped using foreign keys. The advantages of this method include reducing redundancy in the ER diagram, enforcing constraints, and reducing physical storage requirements. Disadvantages include requiring expertise and increasing the number of attributes.

term project week 5
Student
09 OCTOBER, 2016

Contents
TRANSLATION METHOD........................................................................................................................2
DRAFT ERD.............................................................................................................................................3
RELATIONAL SCHEMA............................................................................................................................3
DATA DICTIONARY.................................................................................................................................4
1 | P a g e

TRANSLATION METHOD
The translation method used on ERD was ‘mapped translation method’. In this method, a
table is created for each entity. Although the entities may be weak or strong but a table is
needed to define and declare the attributes of the entities. Then, the primary keys are added to
each table to identify each record and as shown in the ER diagram. Further we, map the
relationship between entities into the tables using foreign keys. These foreign keys act as a
bridge between the entities that have transformed into the table. Along with this we also add
all the attributes that we had defined in the ERD into the table as columns. This entire process
is called mapped translation because one by one each part is mapped and then translated into
another form for use in the database.
Its advantages are:
1. The multi-values attributes in the ERD can be transformed into atomic attributes in
the relational table.
2. Translation is easy rather than directly converting to the SQL.
3. Redundancy present in the ER diagram can be removed easily via it.
4. Constraints can be enforced after translation, which is not possible in the ER diagram
5. The actual physical storage requirements are reduced by applying this method.
6. Multiple tables can be easily joined without any hassles
Its disadvantages are:
1. Requires cost and expertise
2. Increased number of attributes while making the ERD atomic.
2 | P a g e

3 | P a g e

DRAFT ERD
RELATIONAL SCHEMA
A. EMPLOYEE (EMP_NO , FNAME, LNAME, SALARY, PHONE, DEP_NO*)
B. DEPARTMENT(DEP_NO , DEPT_NAME, EMP_NO*, OFFICE_ID*)
C. OFFICE (OFFICE_ID, OFFICE_NAME, ADDRESS, PHONE)
D. ISSUES (ISSUE_ID, ISSUE_DATE, STATUS, DETAILS, REMARKS, EMP_NO*)
4 | P a g e

DATA DICTIONARY
TABLE NAME: EMPLOYEE
TABLE DESCRIPTION: It will contain data about the employees.
ATTRIBUTE
NAME
DESCRIPTION DATA
TYPE
SIZE CONSTRAINTS
EMP_NO Unique no. allotted
to each employee
Integer Auto_increment PRIMARY KEY
FNAME First name of the
employee
Text 20
LNAME Last name of the
employee
Text 20
SALARY Salary of the
employee
Decimal (10,2) Not null
PHONE Phone number of
the employee
Numeric 10 Unique
DEP_NO Department number
in which the
employee works.
Varchar 4 Not null, foreign key
department(dep_no)
TABLE NAME: department
TABLE DESCRIPTION: It will hold the data about all the departments.
ATTRIBUTE
NAME
DESCRIPTION DATA TYPE SIZE CONSTRAINTS
DEP_NO Unique department
number allotted to
each department
Varchar 4 PRIMARY KEY
DEPT_NAME Name of each
department
Text 30 Not null
EMP_NO Employee id of
employee who
handles the
department
Integer Not null, foreign key
employee(emp_no)
OFFICE_ID ID of the office
which has this
department.
Varchar 4 Primary key, foreign
key office(office_ID)
TABLE NAME: office
TABLE DESCRIPTION: It will contain information about all the offices.
5 | P a g e

ATTRIBUTE
NAME
DESCRIPTION DATA TYPE SIZE CONSTRAINTS
OFFICE_ID Unique id allotted to
each office.
Varchar 4 PRIMARY KEY
OFFICE_NAME Name of each office Text 30
ADDRESS Address of each
office.
Text 50 Not null
PHONE Phone number of
each office
Numeric 10 Unique
TABLE NAME:
TABLE DESCRIPTION:
ATTRIBUTE
NAME
DESCRIPTION DATA
TYPE
SIZE CONSTRAINTS
ISSUE_ID Unique id allotted to
each issue.
Integer Auto_increment PRIMARY KEY
ISSUE_DATE Begin date of the
issue
Date Format (‘DD-
MM-YYYY’
OR ‘YYYY-
MM-DD’)
Not null
STATUS Current status of the
issue
Text 50 Values should be IN
(‘open’, ‘closed’,
‘pending’)
DETAILS Details related to
the issue
Text 100 Not null
REMARKS Ay remarks about
the issue
Text 100
EMP_NO Employee id of the
employee who
handles the issue.
Integer Not null, foreign key
employee(emp_no)
6 | P a g e