[go: up one dir, main page]
Scribd
Upload
14 views4 pages

EER Model MySQL Program

The document outlines the development of an Enhanced Entity-Relationship (EER) model using MySQL, focusing on generalization and normalization. It includes SQL code for creating base and specialized tables for employees, engineers, managers, and projects, along with sample data insertion and expected output. The normalization validation confirms compliance with the first three normal forms (1NF, 2NF, 3NF).

Uploaded by

shreenidhiofficial18
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
14 views4 pages

EER Model MySQL Program

The document outlines the development of an Enhanced Entity-Relationship (EER) model using MySQL, focusing on generalization and normalization. It includes SQL code for creating base and specialized tables for employees, engineers, managers, and projects, along with sample data insertion and expected output. The normalization validation confirms compliance with the first three normal forms (1NF, 2NF, 3NF).

Uploaded by

shreenidhiofficial18
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

MySQL Program - EER Model with Generalization

1. Problem Statement
Develop an EER concept using ERD tools by applying cardinalities for each relation using the bottom-up
approach (generalization). Validate it using normalization.

2. MySQL Code

-- Step 1: Create base table (generalized)


CREATE TABLE Employee (
emp_id INT PRIMARY KEY,
name VARCHAR(50)
);

-- Step 2: Specialized tables


CREATE TABLE Engineer (
emp_id INT PRIMARY KEY,
specialization VARCHAR(50),
salary DECIMAL(10,2),
FOREIGN KEY (emp_id) REFERENCES Employee(emp_id)
);

CREATE TABLE Manager (


emp_id INT PRIMARY KEY,
department VARCHAR(50),
bonus DECIMAL(10,2),
FOREIGN KEY (emp_id) REFERENCES Employee(emp_id)
);

-- Step 3: Project Table


CREATE TABLE Project (
proj_id INT PRIMARY KEY,
proj_name VARCHAR(50)
);

-- Step 4: Many-to-Many relationship resolver


CREATE TABLE Works_On (
emp_id INT,
proj_id INT,
hours INT,
PRIMARY KEY(emp_id, proj_id),
FOREIGN KEY (emp_id) REFERENCES Employee(emp_id),
FOREIGN KEY (proj_id) REFERENCES Project(proj_id)
);

3. Sample Data Insertion

INSERT INTO Employee VALUES


(1, 'Alice'),
(2, 'Bob');

INSERT INTO Engineer VALUES


(1, 'Software', 80000.00);

INSERT INTO Manager VALUES


(2, 'IT', 10000.00);

INSERT INTO Project VALUES


(101, 'ERP System'),
(102, 'Website Revamp');

INSERT INTO Works_On VALUES


(1, 101, 20),
(2, 102, 15);

4. Expected Output

Employee:
+--------+-------+
| emp_id | name |
+--------+-------+
|1 | Alice |
|2 | Bob |
+--------+-------+

Engineer:
+--------+---------------+----------+
| emp_id | specialization| salary |
+--------+---------------+----------+
|1 | Software | 80000.00 |
+--------+---------------+----------+

Manager:
+--------+------------+--------+
| emp_id | department | bonus |
+--------+------------+--------+
|2 | IT | 10000 |
+--------+------------+--------+

Project:
+---------+------------------+
| proj_id | proj_name |
+---------+------------------+
| 101 | ERP System |
| 102 | Website Revamp |
+---------+------------------+

Works_On:
+--------+---------+--------+
| emp_id | proj_id | hours |
+--------+---------+--------+
|1 | 101 | 20 |
|2 | 102 | 15 |
+--------+---------+--------+

5. Normalization Validation

- 1NF: All attributes are atomic.


- 2NF: No partial dependencies (non-prime attributes depend on whole key).
- 3NF: No transitive dependencies; all non-key attributes depend only on primary key.

You might also like