Example: COMPANY Database Application

 

Example: COMPANY Database Application

The COMPANY database is a classic example used to demonstrate conceptual database design using the ER model. It models a miniworld that includes employees, departments, projects, and dependents, along with their relationships and constraints.

1. Purpose of the COMPANY Database

The COMPANY database is designed to:

  • Maintain employee information

  • Track departmental organization

  • Manage projects and employee participation

  • Store employee dependents for insurance purposes

This database supports common business operations such as employee management, project allocation, and departmental control.

2. Identified Entity Types

From the requirements, we identify the following entity types:

  1. EMPLOYEE

  2. DEPARTMENT

  3. PROJECT

  4. DEPENDENT (weak entity)

3. Entity Descriptions with Sample Data

3.1 EMPLOYEE Entity

Stores personal and job-related information about employees.

Attributes

  • Ssn (Primary Key)

  • Name (Fname, Minit, Lname)

  • Address

  • Salary

  • Sex

  • Birth_date

Sample EMPLOYEE Data

SsnNameAddress    Salary    Sex    Birth_date
123456789    John A Smith    2311 Kirby, Houston TX    60000    M    1969-03-15
987654321    Alicia J Brown    45 Main St, Dallas TX    75000    F    1985-08-21

3.2 DEPARTMENT Entity

Represents organizational units within the company.

Attributes

  • Dnumber (Primary Key)

  • Dname (Unique)

  • Locations (Multivalued)

  • Manager (FK → EMPLOYEE)

  • Mgr_start_date

Sample DEPARTMENT Data

DnumberDnameLocations    Manager_Ssn    Mgr_start_date
5Research    Houston, Dallas    123456789    2018-01-01
4Accounting    Chicago    987654321    2020-06-15

3.3 PROJECT Entity

Represents projects controlled by departments.

Attributes

  • Pnumber (Primary Key)

  • Pname (Unique)

  • Location

  • Controlled_by (FK → DEPARTMENT)

Sample PROJECT Data

PnumberPnameLocation    Dnumber
1    ProductX    Houston        5
2    ProductY    Dallas        5
3    PayrollSystem    Chicago        4

3.4 DEPENDENT Entity (Weak Entity)

Stores dependents of employees for insurance purposes.

Attributes

  • Dependent_name (Partial Key)

  • Sex

  • Birth_date

  • Relationship

  • Employee_Ssn (FK → EMPLOYEE)

📌 A DEPENDENT cannot exist without an EMPLOYEE.

Sample DEPENDENT Data

Employee_Ssn    Dependent_nameSex    Birth_date    Relationship
123456789    AnnaF    2012-05-10    Daughter
123456789    MarkM    2015-11-02    Son

4. Identified Relationships

4.1 WORKS_FOR

  • Between EMPLOYEE and DEPARTMENT

  • Many employees work for one department

  • Each employee belongs to exactly one department

4.2 MANAGES

  • Between EMPLOYEE and DEPARTMENT

  • One employee manages one department

  • Includes attribute Mgr_start_date

4.3 CONTROLS

  • Between DEPARTMENT and PROJECT

  • One department controls many projects

4.4 WORKS_ON

  • Between EMPLOYEE and PROJECT

  • Many-to-many relationship

  • Attribute: Hours_per_week

4.5 DEPENDENTS_OF

  • Identifying relationship between EMPLOYEE and DEPENDENT

  • One employee may have multiple dependents

5. How This Maps to an ER Diagram

  • Rectangles → Entity types (EMPLOYEE, DEPARTMENT, PROJECT, DEPENDENT)

  • Diamonds → Relationships (WORKS_FOR, MANAGES, WORKS_ON, CONTROLS)

  • Ovals → Attributes

  • Double rectangles → Weak entity (DEPENDENT)

  • Double diamonds → Identifying relationship (DEPENDENTS_OF)

Refer Figure 3.2 




6. Why This Example Is Important

  • Demonstrates real-world complexity

  • Illustrates keys, weak entities, multivalued attributes, and relationship attributes

  • Commonly used in exams, labs, and ER modeling practice

  • Serves as a foundation for:

    • ER → Relational mapping

    • SQL schema design

    • Normalization



Comments

Post a Comment

Popular posts from this blog

Database Management Systems DBMS PCCST402 Semester 4 KTU CS 2024 Scheme

 About Me Database Management Systems DBMS PCCST402 Scheme and Syllabus Module-1 Introduction to DBMS Introduction to DBMS Example  Database Database System Vs Traditional File Processing Database System Concepts and Architecture Data Models, Schemas and Instances   Three schema architecture and Data Independence Database Languages and Interfaces Centralized and Client Server Architectures in DBMS Classification of Database Management Systems Conceptual Data Modeling and Database Design Conceptual Modeling and the Role of the ER Model in Database Design Entities and Attributes  Company Database Application - Example Entity Types, Entity Sets, Keys, and Value Sets in the ER Model Initial Conceptual Design of the COMPANY Database Relationship Types in the ER Model Relationship Degree, Role Names, and Recursive Relationships Constraints on Binary Relationship Types Attributes of Relationship Types Weak Entity Types Summary of the Notations Used in ER Diagram , Naming Co...
Read more

Database Management Systems DBMS PCCST402 Scheme and Syllabus

  DATABASE MANAGEMENT  SYSTEMS (Common to  CS/CD/CA/CR/AD/AI/CB/CN/CC/CU/CI/CG)   Course  Code PCCST402 CIE Marks 40 Teaching  Hours/Week (L:T:P: R) 3:1:0:0 ESE Marks 60 Credits 4 Exam Hours 2Hrs.30 Min. Prerequisites(if  any) PCCST303 Course Type Theory Course Objectives:   1.        Equip the students with a comprehensive understanding of fundamental DBMS concepts as well as the principles and applications of NoSQL databases 2.        Enable students to design,implement,and manage both relational and NoSQL databases SYLLABUS   Module No. Syllabus  Description Contact Hours         1 Introduction to Databases...
Read more

Introduction to Database Management System -DBMS

Image
In today’s digital world, almost every application—from mobile banking and e-commerce to university portals and hospital systems—depends on the efficient storage, organization, and retrieval of data. As the amount of information generated by individuals and organizations continues to grow, managing this data in a structured and meaningful way becomes essential. This is where database systems play a central role. A database can be thought of as a well-organized collection of related data, designed so that it can be easily accessed, managed, and updated. But the database itself is only one component. To manage it effectively, we rely on a specialized software package known as a Database Management System (DBMS) . A DBMS provides a systematic and automated way to store large volumes of data, enforce rules for consistency and correctness, protect data from unauthorized access, and allow multiple users to work with the data simultaneously without conflicts. A database typically has three...
Read more