Lab 2: Company Database ER Diagram

Design and Implementation Assignment

Lab Assignment

Objective: Design a complete Entity-Relationship (ER) diagram for a company database using MySQL Workbench based on the specifications provided below.

Submission Required: Upload your ERD (PDF) and MySQL Workbench model (.mwb file) during lab hours

Problem Statement

Design a comprehensive database system for a company that manages employees, departments, projects, and their relationships. The system should track employee information, departmental structure, project assignments, work hours, dependents, and supervisory relationships.

Entity Specifications

1. DEPARTMENT Entity

Attribute Type Constraints Description
Dnumber INT Primary Key Unique department number
Dname VARCHAR(50) NOT NULL, Unique Department name
Mgr_ssn CHAR(9) Foreign Key Manager's SSN (references EMPLOYEE)
Mgr_start_date DATE Date when manager started
Locations Multi-valued Separate table needed Department office locations

2. EMPLOYEE Entity

Attribute Type Constraints Description
SSN CHAR(9) Primary Key Social Security Number
Fname VARCHAR(30) NOT NULL First name
Lname VARCHAR(30) NOT NULL Last name
Address VARCHAR(100) Employee address
Salary DECIMAL(10,2) Employee salary
Sex CHAR(1) CHECK (Sex IN ('M', 'F')) Gender
Bdate DATE Birth date
Dno INT Foreign Key, NOT NULL Department number (references DEPARTMENT)
Super_ssn CHAR(9) Foreign Key Supervisor's SSN (self-referencing)

3. PROJECT Entity

Attribute Type Constraints Description
Pnumber INT Primary Key Unique project number
Pname VARCHAR(50) NOT NULL, Unique Project name
Plocation VARCHAR(50) Project location
Dnum INT Foreign Key, NOT NULL Controlling department number

4. DEPENDENT Entity

Attribute Type Constraints Description
Essn CHAR(9) Primary Key, Foreign Key Employee SSN (references EMPLOYEE)
Dependent_name VARCHAR(30) Primary Key Name of the dependent
Sex CHAR(1) CHECK (Sex IN ('M', 'F')) Gender of dependent
Bdate DATE Birth date of dependent
Relationship VARCHAR(20) Relationship to employee

Relationship Specifications

WORKS_FOR (Employee → Department)

Cardinality: N:1

Description: Each employee works for exactly one department, but each department can have many employees.

Implementation: Foreign key Dno in EMPLOYEE table

MANAGES (Employee → Department)

Cardinality: 1:1

Description: Each department is managed by exactly one employee, and each employee can manage at most one department.

Implementation: Foreign key Mgr_ssn in DEPARTMENT table

CONTROLS (Department → Project)

Cardinality: 1:N

Description: Each department controls many projects, but each project is controlled by exactly one department.

Implementation: Foreign key Dnum in PROJECT table

WORKS_ON (Employee ↔ Project)

Cardinality: M:N

Description: Employees can work on multiple projects, and projects can have multiple employees.

Attribute: Hours (number of hours worked)

Implementation: Separate WORKS_ON table with composite key (Essn, Pno)

DEPENDENTS_OF (Employee → Dependent)

Cardinality: 1:N

Description: Each employee can have multiple dependents, but each dependent belongs to exactly one employee.

Implementation: Foreign key Essn in DEPENDENT table

SUPERVISION (Employee → Employee)

Cardinality: 1:N (Recursive)

Description: Each employee can supervise multiple employees, but each employee has at most one supervisor.

Implementation: Foreign key Super_ssn in EMPLOYEE table

Design Requirements

Entity Design Guidelines:

Relationship Implementation:

Integrity Constraints:

Assignment Instructions

Submission Requirements

  1. MySQL Workbench Model (.mwb file):
    • Create the complete ER diagram in MySQL Workbench
    • Include all entities with proper attributes and data types
    • Implement all relationships with correct cardinalities
    • Save as "Exp-2.mwb"
  2. ER Diagram PDF:
    • Export your ER diagram from MySQL Workbench as PDF
    • Ensure all entities, attributes, and relationships are clearly visible
    • Include your name and student ID on the diagram
    • Save as "Exp-2.pdf"

Step-by-Step Process:

  1. Analysis Phase:
    • Carefully read all entity and relationship specifications
    • Identify primary keys, foreign keys, and composite keys
    • Note special cases (multi-valued attributes, recursive relationships)
  2. Design Phase:
    • Open MySQL Workbench and create a new EER Model
    • Create all entities with their attributes
    • Set appropriate data types and constraints
    • Create all relationships with proper cardinalities
  3. Validation Phase:
    • Verify all entities have primary keys
    • Check that all foreign key relationships are correct
    • Ensure multi-valued attributes are handled properly
    • Validate recursive relationship implementation
  4. Submission Phase:
    • Export ER diagram as PDF
    • Save MySQL Workbench model as .mwb file
    • Submit both files during lab hours

Evaluation Criteria

Criteria Points Description
Entity Design 25 Correct entities with all specified attributes and data types
Primary Keys 15 Proper identification and implementation of primary keys
Relationships 25 Correct implementation of all relationships with proper cardinalities
Foreign Keys 15 Proper implementation of foreign key constraints
Special Cases 10 Multi-valued attributes, recursive relationships, associative entities
Presentation 10 Clear diagram layout, proper naming conventions, documentation

Tips and Best Practices

Do's

  • Use meaningful and consistent naming conventions
  • Clearly mark primary keys with key symbols
  • Use appropriate data types for each attribute
  • Document any assumptions you make
  • Test your design logic before submission

Don'ts

  • Don't forget to handle multi-valued attributes properly
  • Don't create redundant relationships
  • Don't ignore referential integrity constraints
  • Don't use ambiguous attribute names
  • Don't submit without thorough validation

Learning Outcomes

Upon completion of this lab assignment, students will be able to: