ERD example

ERD example

Problem 1 A company database needs to store information about employees (identified by ssn, with salary and phone as attributes), departments (identified by dno, with dname and budget as attributes), and children of employees (with name and age as attributes).

Problem Employees work in departments; each department is managed by an employee; a child must be identified uniquely by name when the parent (who is an employee; assume that only one parent works for the company) is known. We are not interested in information about a child once the parent leaves the company. Draw an ER diagram that captures this information.

Solution First, we shall design the entities and relationships. – “Employees work in departments ” – “ each department is managed by an employee ” – “ a child must be identified uniquely by name when the parent (who is an employee; assume that only one parent works for the company) is known.”

Solution dname salary ssn phone Employees Manages Dependent Works In Child name dno age budget Departments

Exercise 2 Consider a university database for the scheduling of classrooms for ‑final exams. This database could be modeled as the single entity set exam, with attributes course-name, section-number, roomnumber, and time. Alternatively, one or more additional entity sets could be defined, along with relationship sets to replace some of the attributes of the exam entity set, as [1] course with attributes name, department, and c-number [2] section with attributes s-number and enrollment, and dependent as a weak entity set on course [3] room with attributes r-number, capacity, and building Show an E-R diagram illustrating the use of all three additional entity sets listed.

Problem: 2 The craft trading website Itsy! Bitsy! is setting up a database to record sellers and their products. This requires recording the following information: For each seller, their name, contact email, and postal address. For each product, its name, price, and number available. Which product is from which seller. A unique id number for each product. Draw an entity-relationship (ER) diagram that represents this information. Make sure to capture the constraints on the relationships involved, and designate appropriate primary keysfor the entities.

Exercise 3 Construct an ER Diagram for Company having following details : Company organized into DEPARTMENT. Each department has unique name and a particular employee who manages the department. Start date for the manager is recorded. Department may have several locations. A department controls a number of PROJECT. Projects have a unique name, number and a single location. Company's EMPLOYEE name, ssno, address, salary, and birth date are recorded. An employee is assigned to one department, but may work for several projects (not necessarily controlled by her dept). Number of hours/week an employee works on each project is recorded; The immediate supervisor for the employee. Employee's DEPENDENT are tracked for health insurance purposes (dependent name, birthdate, relationship to employee).