Module 1

Chapter 2
Conceptual Modeling in Database Design
•What is a Database Application?
•A database application consists of a database and the programs that manage data (queries and updates).
•Example: A BANK database tracks customer accounts, including deposits and withdrawals.

•Role of Application Programs

•Programs provide user-friendly interfaces (e.g., forms, menus).
•These programs help users (like bank tellers) interact with the database.
•Database Design vs. Software Engineering
•Database design focuses on data structures and constraints.
•Software engineering focuses on designing and testing application programs.
•Both are related, but database design primarily deals with organizing data.

•Conceptual Database Design

•Uses high-level models to structure data before implementation.
•The Entity-Relationship (ER) model is a popular conceptual modeling approach.
•Entity-Relationship (ER) Model Helps visualize database structures before building them.
•Uses ER diagrams to represent entities (objects), attributes (properties), and relationships.

•UML is used forUML (Unified Modeling Language) and Database Design

both software and database design.

•Class diagrams in UML are similar to ER diagrams but also include operations (functions).

Beyond ER Model: Enhanced-ER (EER) Model

•Extends the ER model with additional concepts:
• Specialization & Generalization (e.g., a "Vehicle" can be a "Car" or "Truck").
• Inheritance (attributes passed down from a parent entity).
• Union types (categories) to group different entities.
Database Design Process
Step 1: Requirements Collection and Analysis
• What happens?
• Database designers interview users to understand what data the system needs to store
and how it will be used.
• The result is a written document listing the users’ data requirements.
• Example:
• A University Database must store details about students, courses, professors, and grades.
• Functional requirements include:
• Students should be able to register for courses.
• Professors should be able to assign grades.
• Admins should be able to view student records.
• Why is this important?
• Ensures that all necessary data is considered.
• Avoids missing any important details before database design begins.
Step 2: Conceptual Design (Creating a Conceptual Schema)
• What happens?
• A conceptual schema (high-level design) is created using a conceptual data model like the Entity-
Relationship (ER) model.
• It includes:
• Entities (objects) like Student, Course, Professor.
• Relationships (how entities are related) like "enrolled in" (Student → Course).
• Constraints (rules) like “A student can enroll in up to 5 courses”.
• Example:
• Entities:
• Student (ID, Name, Email, Major)
• Course (CourseID, Name, Credits)
• Professor (ProfID, Name, Department)
• Relationships:
• A Student enrolls in a Course.
• A Professor teaches a Course.
• Constraints:
• A student cannot enroll in more than 5 courses at a time.
• Why is this important?
• It creates a clear, structured plan before implementing the database.
• Easy to understand and modify before actual development.
Step 3: Verifying Functional Requirements
• What happens?
• Designers check if the conceptual schema meets all functional requirements
(retrieval & updates).
• They simulate queries and operations users will perform.
• If something is missing, they modify the schema.
• Example:
• A student should be able to see a list of all courses they are enrolled in.
• If this cannot be easily retrieved, the schema may need to be adjusted (e.g.,
adding an enrollment date).
• Why is this important?
• Ensures the database structure supports all user needs.
• Reduces the need for major changes later.
Step 4: Logical Design (Transforming to an Implementation Model)
• What happens?
• The conceptual schema (high-level ER model) is converted into a format
suitable for Database Management Systems (DBMS).
• Most DBMS use a Relational Model (tables with rows and columns).
• Why is this important?
• Converts a visual conceptual model into a real database structure.
• Ensures compatibility with the chosen DBMS (e.g., MySQL, Oracle).
Step 5: Physical Design (Optimizing Storage & Performance)
• What happens?
• Designers decide on file storage, indexing, and access methods to improve performance.
• Factors include:
• How data is stored (e.g., disk vs. cloud).
• Indexes to speed up searches.
• Security measures (who can access what).
• Example:
• Adding an index on the StudentID column in the Enrollment Table allows faster lookups.
• Using partitioning (storing old student records separately) to improve query speed.
• Why is this important?
• Ensures the database runs efficiently (fast queries, minimal storage issues).
• Helps prevent slowdowns when handling large amounts of data.
• Step 6: Application Development (Building Database Programs)
• What happens?
• Developers create interfaces and programs that interact with the database.
• Includes:
• Web applications (e.g., Student Portal).
• Mobile apps (e.g., Banking App).
• Backend APIs for communication with the database.
• Example:
• A university student portal where:
• Students log in and view their enrolled courses.
• Professors enter grades for students.
• Admins generate reports on student performance.
• Why is this important?
• Provides end-users with tools to interact with the database.
• Ensures data is accessible, user-friendly, and secure.
• Designing the COMPANY Database Using the ER Model
• The COMPANY database will store information about a company's
employees, departments, projects, and dependents. Below, we break
down the step-by-step design process using Entity-Relationship (ER)
modeling concepts.
• Step 1: Understanding the Data Requirements
• The database will store the following:
• Departments
• Each department has a unique name and unique number.
• Each department has one manager and a start date when they started managing.
• A department may have multiple locations.
• Projects
• Each project has a unique name, unique number, and a single location.
• Each project is controlled by one department.
• Employees
• Each employee has:
• Name
• Social Security Number (SSN) (Unique ID)
• Address
• Salary
• Gender
• Birth date
•Each employee is assigned to one department.
•An employee can work on multiple projects (not necessarily in the same department).
•We track the number of hours an employee works on each project.
•Each employee has one direct supervisor (who is another employee).
•Dependents (for insurance purposes)
•Each dependent has:
•First name
•Gender
•Birth date
•Relationship to the employee.
• Step 2: Identifying Entities and Their Attributes
• In ER modeling, an entity represents a real-world object. Each entity has
attributes (properties).
1. Department Entity
• Attributes:
• Department Name (Unique)
• Department Number (Unique, Primary Key)
• Manager (Employee ID)
• Manager Start Date
• Locations (Multi-valued Attribute)
2. Project Entity
• Attributes:
• Project Name (Unique)
• Project Number (Unique, Primary Key)
• Location
• Controlling Department (Department ID - Foreign Key)
3. Employee Entity
• Attributes:
• SSN (Primary Key, Unique ID)
• Name
• Address
• Salary
• Gender
• Birth date
• Assigned Department (Foreign Key - Department Number)
• Supervisor (Foreign Key - Employee SSN)
4. Works_On Relationship (Employee ↔ Project)
• Attributes:
• Employee SSN (Foreign Key - Employee)
• Project Number (Foreign Key - Project)
• Hours per Week
5. Dependent Entity
• Attributes:
• Dependent Name
• Gender
• Birth Date
• Relationship
• Employee SSN (Foreign Key - Employee)
• Step 3: Identifying Relationships Between Entities
1. Employee → Department (Works For Relationship)
• Each Employee works for one Department.
• Each Department has multiple Employees.
• One-to-Many Relationship (1:N).
2. Employee → Employee (Supervises Relationship)
• Each Employee has one Supervisor (except top management).
• Each Supervisor can have multiple Employees under them.
• One-to-Many Relationship (1:N).
3. Department → Project (Controls Relationship)
• Each Department controls multiple Projects.
• Each Project belongs to only one Department.
• One-to-Many Relationship (1:N).
4. Employee → Project (Works On Relationship)
• Each Employee can work on multiple Projects.
• Each Project can have multiple Employees working on it.
• Many-to-Many Relationship (M:N).
• Works_On table is used to track Hours per Week.
5. Employee → Dependent (Has Relationship)
• Each Employee can have multiple Dependents.
• Each Dependent belongs to only one Employee.
• One-to-Many Relationship (1:N).
Step 4: ER Diagram Representation
• Entities: Employee, Department, Project, Dependent.
• Relationships: Works For, Supervises, Controls, Works On, Has.
• Attributes: Each entity has a unique identifier (Primary Key).
Step 5: Keys in the ER Model
• Primary Key (Uniquely identifies an entity):
• Employee: SSN
• Department: Department Number
• Project: Project Number
• Dependent: (Employee SSN + Dependent Name)
• Foreign Key (Links one entity to another):
• Employee → Department (Department Number)
• Project → Department (Department Number)
• Works_On Table → Employee & Project (SSN, Project Number)
Step 6: Implementing Constraints
• Each department must have a manager → Use a foreign key in the Department table linking
to an Employee's SSN.
• A project must belong to one department → Foreign key in Project table linking to
Department Number.
• An employee must belong to one department → Foreign key in Employee table linking to
Department Number.
• An employee can work on multiple projects → Many-to-Many Relationship using Works_On
table.
• An employee may have dependents → One-to-Many Relationship using Dependent table.
1. Entities and Attributes
• Entities: Objects or things in the real world that have a distinct existence.
• Example: An Employee, a Department, a Project.
• Attributes: Characteristics or properties of an entity.
• Example: An Employee has attributes like Name, Age, Address, Salary.
• Types of Attributes
• Simple vs. Composite Attributes
• Simple: Cannot be divided further.
• Example: Age, Salary, Gender
• Composite: Can be divided into smaller attributes.
• Example: Address → Street, City, State, Zip Code.
• Single-Valued vs. Multivalued Attributes
• Single-Valued: Holds only one value for an entity.
• Example: Age of an Employee
• Multivalued: Can have multiple values for an entity.
• Example: Phone numbers of an Employee (work phone & personal phone).
• Stored vs. Derived Attributes
• Stored: Values that are actually stored in the database.
• Example: Birth Date
• Derived: Values calculated from stored attributes.
• Example: Age (derived from Birth Date).
• NULL Values
• When an attribute doesn’t have a value.
• Example: If an Employee does not have a phone number, the value is
NULL.
2. Entity Types and Entity Sets
• Entity Type: A collection of similar entities.
• Example: The EMPLOYEE entity type consists of all employees in the company.
• Entity Set: The actual instances of an entity type in a database.
• Example: All employees currently working in the company.
• 3. Keys in an ER Model
• Key Attribute: An attribute whose value is unique for each entity.
• Example:
• SSN (Social Security Number) is a key for EMPLOYEE.
• Department Number is a key for DEPARTMENT.
• Composite Key: A key made up of multiple attributes.
• Example: (State, Registration Number) for a CAR entity.
• 4. COMPANY Database Example
• Entities and Their Attributes
• DEPARTMENT
• Attributes:
• Name (Key)
• Number (Key)
• Locations (Multivalued)
• Manager
• Manager_start_date
•PROJECT
•Attributes:
•Name (Key)
•Number (Key)
•Location
•Controlling_department
•EMPLOYEE
•Attributes:
•Name (Composite: First Name, Middle Initial, Last Name)
•Ssn (Key)
•Sex
•Address (Composite: Street, City, State, Zip Code)
•Salary
•Birth Date
•Department
•Supervisor
• DEPENDENT
• Attributes:
• Employee (Foreign Key)
• Dependent Name
• Sex
• Birth Date
• Relationship to Employee
• Representing Employee-Project Relationship
• Option 1: EMPLOYEE entity has a multivalued composite attribute
called Works_on (Project, Hours).
• Option 2: PROJECT entity has a multivalued composite attribute called
Workers (Employee, Hours).
• Chosen Approach: Works_on in EMPLOYEE (Each employee keeps track
of projects and hours worked).
1. Entity Types and Entity SetsEntity Type: A collection of similar entities that share the same attributes.
•Example: An EMPLOYEE entity type may have attributes like Name, Ssn, Salary, etc.
•Entity Set: A collection of entities of a particular entity type at a given time.
•Example: All employees in a company form the EMPLOYEE entity set.

2. ER Diagram Representation
•Rectangular Box → Represents the entity type.
•Ovals → Represent attributes.
•Double Ovals → Represent multivalued attributes (e.g., Locations for a DEPARTMENT).
•Lines → Connect attributes to their entity type.
➡️ER diagrams visually represent the structure of a database.

3. Key Attributes
•Key Attribute: An attribute (or set of attributes) that uniquely identifies each entity in the entity set.
• Example:
• For EMPLOYEE, Ssn (Social Security Number) is a unique identifier.
• For DEPARTMENT, both Name and Number are unique keys.
•Composite Key: A combination of multiple attributes used as a key.
• Example: In the CAR entity type, Registration (which includes State and Number) is a
composite key.
➡️A key ensures no two entities have identical values for that attribute.
4. Weak Entity Types
•Weak Entity: An entity type without a key attribute.
•Requires a strong (or owner) entity and a partial key to ensure uniqueness.
➡️Example: A DEPENDENT entity may rely on EMPLOYEE for identification (e.g., employee's children).

5. Value Sets (Domains)

•Each attribute is assigned a value set (also called a domain) that defines the possible values it
can hold.
•Example:
•Age can have a value set of integers between 16 and 70.
•Name can have a value set of strings with alphabetic characters.
➡️Value sets define the boundaries for data values.

6. Example Entity Types in the COMPANY Database

•DEPARTMENT
• Attributes: Name, Number, Locations, Manager, Manager_start_date
• Keys: Name, Number (both unique)
•PROJECT
• Attributes: Name, Number, Location, Controlling_department
• Keys: Name, Number (both unique)
•EMPLOYEE
•Attributes: Name, Ssn, Sex, Address, Salary, Birth_date, Department, Supervisor
•Key: Ssn
•DEPENDENT
•Attributes: Employee, Dependent_name, Sex, Birth_date, Relationship
•Key: Combination of Employee and Dependent_name

7. Multivalued and Composite Attributes

•Multivalued Attribute: Can hold multiple values for a single entity.
•Example: Locations in DEPARTMENT may include multiple office locations.
•Composite Attribute: Comprises multiple simple attributes.
•Example: Name in EMPLOYEE may include First_name, Middle_initial, and
Last_name.

8. Special Cases:
•In the EMPLOYEE entity, the Works_on attribute is a multivalued composite attribute with two
components: Project and Hours.
•In the PROJECT entity, the Workers attribute could also track the employee and hours worked but is defined
differently for clarity.
• 1. Implicit Relationships in Entity Types
• Attributes that refer to other entities imply relationships.
• Example:
• Manager in DEPARTMENT refers to an EMPLOYEE who manages the department.
• Controlling_department in PROJECT refers to the department controlling the project.
• These attributes are better represented as explicit relationships in the ER
model.
• 2. Relationship Types, Sets, and Instances
• Relationship Type: Defines associations between entity types.
• Relationship Set: A set of specific relationship instances.
• Relationship Instance: An individual association between entities.
• Example:
• WORKS_FOR links EMPLOYEE with DEPARTMENT.
• Each instance shows one employee working for one department.
• Relationship Degree
• Degree refers to the number of entity types in the relationship:
• Binary (Degree 2): Two entity types (e.g., WORKS_FOR).
• Ternary (Degree 3): Three entity types (e.g., SUPPLY).
• Higher-degree relationships are rare and more complex.
• 4. Relationships as Attributes
• A binary relationship can be modeled as an attribute:
• Department in EMPLOYEE = The department the employee works for.
• Employee in DEPARTMENT = Employees working for the department.
• When both exist, they are inverse attributes.
• 5. Role Names and Recursive Relationships
• Role Names identify the role an entity plays in a relationship.
• Recursive Relationship: When the same entity type participates in different roles.
• Example:
• SUPERVISION links EMPLOYEEs as:
• Supervisor role → Manager
• Supervisee role → Subordinate
• 6. Constraints on Binary Relationships
• Cardinality Ratio: Limits the number of entities in a relationship.
• 1:1 - Each entity can relate to only one other.
• 1:N - One entity relates to many entities.
• M:N - Many-to-many relationship.
• Example:
• WORKS_FOR (1:N) → One department can employ many employees, but each employee
belongs to one department.
• MANAGES (1:1) → One manager controls one department.
• 7. Participation Constraints (Existence Dependency)
• Total Participation: Every entity must be linked to another entity in the relationship.
• Example: Each EMPLOYEE must belong to a DEPARTMENT.
• Partial Participation: Only some entities participate in the relationship.
• Example: Only some EMPLOYEEs are MANAGERs.
• Diagram Tip:
• Total Participation → Double line
• Partial Participation → Single line
• 8. Attributes of Relationship Types
• Relationship Attributes store extra information about the relationship itself.
• Example:
• Hours in WORKS_ON → Tracks how many hours an EMPLOYEE works on a PROJECT.
• Start_date in MANAGES → Tracks when an EMPLOYEE started managing a DEPARTMENT.
• Placement of Attributes:
• 1:1 Relationship → Can be added to either entity.
• 1:N Relationship → Best added to the entity on the N-side.
• M:N Relationship → Must be stored in the relationship itself.
• 9. Weak Entity Types
• Weak Entity: An entity that cannot exist without an owner entity.
• Identifying Relationship: Links the weak entity to its owner entity.
• Partial Key: Identifies the weak entity uniquely within its owner's context.
• Example:
• DEPENDENT entity depends on the EMPLOYEE entity.
• DEPENDENT’s Name can act as a partial key if no two dependents of the same employee
share the same name.
• Diagram Tip:
• Weak Entity → Double rectangle
• Identifying Relationship → Double diamond
• Partial Key → Dashed underline
• 10. Choosing Representation
• Represent weak entities as complex attributes if:
• The weak entity has many attributes.
• The weak entity participates in relationships other than its identifying relationship.