Database Design Project: Translation Method, ERD, Relational Schema

This project provides a comprehensive solution for a database design assignment. It begins with an explanation of the translation method used to convert an Entity-Relationship Diagram (ERD) into a relational schema, emphasizing the creation of tables for each entity and the use of foreign keys to represent relationships. The project includes a draft ERD, followed by the relational schema, detailing the tables for EMPLOYEE, DEPARTMENT, OFFICE, and ISSUES, along with their attributes and relationships. Finally, a data dictionary is provided, offering detailed descriptions, data types, sizes, and constraints for each attribute within each table, ensuring data integrity and consistency. This project is designed to help students understand the process of database design from conceptual modeling to implementation.

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