Initial Conceptual Design of the COMPANY Database

 

Initial Conceptual Design of the COMPANY Database

After analyzing the data requirements of the COMPANY miniworld, the first step in conceptual database design is to identify the major entity types and their attributes. At this stage, the focus is on what data must be stored, not yet on how entities are related. Relationships are refined later.

Based on the given requirements, four main entity types are identified.

1. DEPARTMENT Entity Type

The DEPARTMENT entity represents the organizational units of the company.

Attributes

  • Name (key)

  • Number (key)

  • Locations (multivalued)

  • Manager

  • Manager_start_date

Design Notes

  • Both Name and Number are unique, so each can serve as a key.

  • A department may operate at multiple locations, hence Locations is multivalued.

  • Manager-related attributes are included initially but will later be modeled as relationships.

2. PROJECT Entity Type

The PROJECT entity represents projects controlled by departments.

Attributes

  • Name (key)

  • Number (key)

  • Location

  • Controlling_department

Design Notes

  • Each project has a unique name and number.

  • A project is associated with one department, initially represented as an attribute.

  • This association will later be refined into a relationship.

3. EMPLOYEE Entity Type

The EMPLOYEE entity represents people working in the company.

Attributes

  • Ssn (key)

  • Name (composite: Fname, Minit, Lname)

  • Sex

  • Address

  • Salary

  • Birth_date

  • Department

  • Supervisor

Design Notes

  • SSN uniquely identifies employees.

  • Name is modeled as a composite attribute to allow access to individual components.

  • Address is kept as a simple attribute after user consultation.

  • Department and Supervisor attributes represent associations that will later be converted into relationships.

4. DEPENDENT Entity Type

The DEPENDENT entity stores information about employees’ dependents for insurance purposes.

Attributes

  • Dependent_name

  • Sex

  • Birth_date

  • Relationship

  • Employee

Design Notes

  • Dependents do not exist independently of employees.

  • The Employee attribute indicates association with an EMPLOYEE entity.

  • This entity will later be identified as a weak entity type.

5. Employee–Project Work Information

One key requirement is to store:

  • Which projects an employee works on

  • The number of hours per week spent on each project

Initial Representation

This is initially modeled as a multivalued composite attribute:

  • Works_on (Project, Hours) — attached to EMPLOYEE

Design Decision

  • This approach captures the requirement but is temporary.

  • In the next design step, this will be refined into a many-to-many relationship between EMPLOYEE and PROJECT with Hours as a relationship attribute.

6. Nature of This Design Stage

This design is preliminary and conceptual:

  • Focuses on entity identification

  • Some attributes represent implicit relationships

  • Refinement occurs when relationship concepts are formally introduced




Key Takeaways

✔ Four core entity types: EMPLOYEE, DEPARTMENT, PROJECT, DEPENDENT
✔ Keys and multivalued attributes identified early
✔ Composite attributes used where user requirements demand
✔ Complex associations postponed for refinement into relationships

Pedagogical Insight

This stage demonstrates that conceptual design is iterative. Initial modeling captures requirements clearly, while later refinements improve correctness and expressiveness using relationships and constraints.

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