Close
Register
Close Window

Database

Chapter 2 The ER-Data Model and Mapping to Relational Schema

Show Source |    | About   «  2.6. Binary Vs Ternary Relationships   ::   Contents   ::   3.1. Introduction To SQL  »

2.7. Comprehensive example

How to Draw ER Diagrams ? Follow the below points to know how to go about creating an ER.

Sounds simple right? In a complex system, it can be a nightmare to identify relationships. This is something you’ll perfect only with practice.

Try solving the given example following the above steps for ERD creation. Try to find the corresponding relational schema by appling ERD/RDD mapping rules. Finally compare your answer with the below solution to know its pros and cons.

Tip: Don’t try to see the given solution before trying to solve the problem alone.

Company Databaes Example: Requirements are oversimplified for illustrative purposes

The company is organized into DEPARTMENTs. Each department has a unique name, phone (each department may has more than one phone number), and an employee who manages the department. We keep track of the start date of the department manager. Each department controls a number of PROJECTs. Each project has a name, Duedate to be finished. Projects may have same names but in different locations. There are suppliers that supply specific parts for each project. The price of each part supplied for specific project by specific supplier should be stored.

Each PART has name and identified by its no.. SUPPLIERs have Ids, names and speciallity.

We store each EMPLOYEE’s social security number, name, address, salary, and birthdate. Employee address should specify city, street & building. Each employee works for one department but may work on several projects. We keep track of the number of hours per week that an employee currently works on each project. We also keep track of the direct supervisor of each employee. Each employee may have a number of children. For each child, we keep track of their name, sex, age, and relationship to employee.

Company Database ERD:

_images/CompanyERDEXample.png

Company’s Relational Schema Diagram:

Hint: relational schema diagram is supported by some comments & analysis below for better understanding.

_images/CompanyRDDExample.png
_images/RDDExampleComments.png

   «  2.6. Binary Vs Ternary Relationships   ::   Contents   ::   3.1. Introduction To SQL  »

nsf
Close Window