Scribd
Upload
28 views11 pages

Employee Database Creation and Queries

The document describes creating tables and inserting values into databases and tables to store employee, company, and works information, including creating relationships between the tables. Various queries are also demonstrated to select, group, and aggregate data from the tables. The document shows how to create a relational database schema and populate it with sample data.

Uploaded by

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

Employee Database Creation and Queries

The document describes creating tables and inserting values into databases and tables to store employee, company, and works information, including creating relationships between the tables. Various queries are also demonstrated to select, group, and aggregate data from the tables. The document shows how to create a relational database schema and populate it with sample data.

Uploaded by

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

DBMS ASSIGNMENT-4

CREATING TABLES AND INSERTING VALUES:

mysql> create database empinfo;

Query OK, 1 row affected (0.04 sec)

mysql> use empinfo;

Database changed

mysql> create table employee( person_name varchar(30) not null primary key, street
varchar(40),city char(20)

-> );

Query OK, 0 rows affected (0.11 sec)

mysql> create table works( person_name varchar(30) not null primary key, company_name
varchar(40), salary int(5)

-> );

Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> create table company( company_name varchar(40), city char(20)

-> );

Query OK, 0 rows affected (0.08 sec)

mysql> create table manages( person_name varchar(30), manager_name varchar(30)

-> );

Query OK, 0 rows affected (0.04 sec)

mysql>desc employee;

+-------------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------------+-------------+------+-----+---------+-------+

| person_name | varchar(30) | NO | PRI | NULL | |

| street | varchar(40) | YES | | NULL | |


| city | char(20) | YES | | NULL | |

+-------------+-------------+------+-----+---------+-------+

3 rows in set (0.02 sec)

mysql>desc works;

+--------------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------------+-------------+------+-----+---------+-------+

| person_name | varchar(30) | NO | PRI | NULL | |

| company_name | varchar(40) | YES | | NULL | |

| salary | int | YES | | NULL | |

+--------------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql>desc company;

+--------------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------------+-------------+------+-----+---------+-------+

| company_name | varchar(40) | YES | | NULL | |

| city | char(20) | YES | | NULL | |

+--------------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql>desc manages;

+--------------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------------+-------------+------+-----+---------+-------+

| person_name | varchar(30) | YES | | NULL | |

| manager_name | varchar(30) | YES | | NULL | |

+--------------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)


QUERY QUESTION 1:

mysql> alter table works drop primary key;

Query OK, 0 rows affected (0.11 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table company add primary key (company_name);

Query OK, 0 rows affected (0.11 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table works add foreign key(company_name) references company(company_name);

Query OK, 0 rows affected (0.09 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table works add foreign key(person_name) references employee(person_name);

Query OK, 0 rows affected (0.11 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql>desc works;

+--------------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------------+-------------+------+-----+---------+-------+

| person_name | varchar(30) | NO | MUL | NULL | |

| company_name | varchar(40) | YES | MUL | NULL | |

| salary | int | YES | | NULL | |

+--------------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql> alter table manages add foreign key(person_name) referEnces employee(person_name);

Query OK, 0 rows affected (0.08 sec)


Records: 0 Duplicates: 0 Warnings: 0

QUERY QUESTION 2:

mysql> alter table employee add email varchar(20);

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into employee values(

-> 'john','a-101','delhi');

mysql> insert into employee values(

-> 'john','a-101','delhi','johnchad@[Link]');

Query OK, 1 row affected (0.02 sec)

mysql> insert into employee values(

-> 'johnny','b-202','mumbai','johnnydept@[Link]');

Query OK, 1 row affected (0.02 sec)

mysql> insert into employee values(

-> 'janardan','c-303','goa','janawar@[Link]');

Query OK, 1 row affected (0.01 sec)

mysql> insert into employee values(

-> 'tara','d-404','bombay','betatara@[Link]');

Query OK, 1 row affected (0.02 sec)

mysql> insert into employee values(

-> 'rumpam','e-505','delhi','sigmarum@[Link]');

Query OK, 1 row affected (0.02 sec)

mysql> insert into company values('ncbbank','delhi');


Query OK, 1 row affected (0.03 sec)

mysql> insert into company values('sambabank','mumbai');

Query OK, 1 row affected (0.03 sec)

mysql> insert into company values('rakshabank','goa');

Query OK, 1 row affected (0.01 sec)

mysql> insert into company values('kotabank','bombay');

Query OK, 1 row affected (0.02 sec)

mysql> insert into company values('hiteshibank','delhi');

Query OK, 1 row affected (0.02 sec)

mysql> insert into works values('john','ncbbank','30000');

Query OK, 1 row affected (0.02 sec)

mysql> insert into works values('johnny','sambabank','40000');

Query OK, 1 row affected (0.02 sec)

mysql> insert into works values('janardan','rakshabank','28000');

Query OK, 1 row affected (0.02 sec)

mysql> insert into works values('tara','kotabank','43000');

Query OK, 1 row affected (0.02 sec)

mysql> insert into works values('rumpam','hiteshibank','35000');

Query OK, 1 row affected (0.02 sec)

mysql> insert into manages values('john','jin')


-> ;

Query OK, 1 row affected (0.02 sec)

mysql> insert into manages values('johnny','june')

-> ;

Query OK, 1 row affected (0.02 sec)

mysql> insert into manages values('johnny','jay')

-> ;

Query OK, 1 row affected (0.03 sec)

mysql> insert into manages values('janardan','jay')

-> ;

Query OK, 1 row affected (0.01 sec)

mysql> insert into manages values('tara','jack')

-> ;

Query OK, 1 row affected (0.02 sec)

mysql> insert into manages values('rumpam','jake')

-> ;

Query OK, 1 row affected (0.02 sec)

mysql> select * from manages;

+-------------+--------------+

| person_name | manager_name |

+-------------+--------------+

| john | jin |

| johnny | june |

| janardan | jay |
| tara | jack |

| rumpam | jake |

+-------------+--------------+

5 rows in set (0.00 sec)

mysql> select * from company;

+--------------+--------+

| company_name | city |

+--------------+--------+

| hiteshibank | delhi |

| kotabank | bombay |

| ncbbank | delhi |

| rakshabank | goa |

| sambabank | mumbai |

+--------------+--------+

5 rows in set (0.00 sec)

mysql> select * from works;

+-------------+--------------+--------+

| person_name | company_name | salary |

+-------------+--------------+--------+

| john | ncbbank | 30000 |

| johnny | sambabank | 40000 |

| janardan | rakshabank | 28000 |

| tara | kotabank | 43000 |

| rumpam | hiteshibank | 35000 |

+-------------+--------------+--------+

5 rows in set (0.01 sec)


QUERY QUESTION 3:

mysql> select manager_name from manages where company_name in('sambabank', 'ncbbank');

+-------------+

| manager_name |

+-------------+

| jin |

| june |

+-------------+

2 rows in set (0.01 sec)

mysql> select * from employee;

+-------------+--------+--------+----------------------+

| person_name | street | city | email |

+-------------+--------+--------+----------------------+

| janardan | c-303 | goa | janawar@[Link] |

| john | a-101 | delhi | johnchad@[Link] |

| johnny | b-202 | mumbai | johnnydept@[Link] |

| rumpam | e-505 | delhi | sigmarum@[Link] |

| tara | d-404 | bombay | betatara@[Link] |

+-------------+--------+--------+----------------------+

5 rows in set (0.00 sec)

QUERY QUESTION 4:

mysql> select employee.person_name from employee, company,works where


employee.person_name =works.person_name and
company.company_name=works.company_name and [Link]=[Link];

+-------------+

| person_name |

+-------------+

| janardan |
| john |

| johnny |

| rumpam |

| tara |

+-------------+

5 rows in set (0.00 sec)

QUERY QUESTION 5:

mysql> select company_name,min(salary),max(salary),avg(salary) from works group by


company_name;

+--------------+-------------+-------------+-------------+

| company_name | min(salary) | max(salary) | avg(salary) |

+--------------+-------------+-------------+-------------+

| hiteshibank | 35000 | 35000 | 35000.0000 |

| kotabank | 43000 | 43000 | 43000.0000 |

| ncbbank | 30000 | 30000 | 30000.0000 |

| rakshabank | 28000 | 28000 | 28000.0000 |

| sambabank | 40000 | 40000 | 40000.0000 |

+--------------+-------------+-------------+-------------+

5 rows in set (0.00 sec)

QUERY QUESTION 6:

mysql> select company_name,count(company_name),sum(salary) from works group by


company_name;

+--------------+---------------------+-------------+

| company_name | count(company_name) | sum(salary) |

+--------------+---------------------+-------------+

| hiteshibank | 1| 35000 |

| kotabank | 1| 43000 |

| ncbbank | 1| 30000 |

| rakshabank | 1| 28000 |
| sambabank | 1| 40000 |

+--------------+---------------------+-------------+

5 rows in set (0.00 sec)

QUERY QUESTION 7:

mysql> select company_name from works where salary=(select max(salary) from works);

+--------------+

| company_name |

+--------------+

| kotabank |

+--------------+

1 row in set (0.01 sec)

SUBMITTED BY- PRABHU (21166)

Common questions

Powered by AI

Enhancing the database to include employee roles involves adding a new column to the 'employee' or 'works' table, indicating each employee's role. This provides clearer organizational structure information and supports role-based queries for assessing resource allocation or developing training programs, thus enriching data functionality for strategic decision-making .

By aggregating salary data using min, max, and average functions, the database provides insights into the salary distribution for each company, enabling comparisons about financial equity and market competitiveness within each company's compensation strategies . Business decisions regarding salary adjustments or hiring can utilize these insights to align with industry standards or company goals.

The query selecting employees where their city matches the company's city reveals an implicit relationship between 'employee' and 'company' tables established through the 'works' table. This relationship signifies that employees are likely based near or in the cities where their companies are located, suggesting a business logic layer assumes regional employment models .

The employee-manager relationship is established through the 'manages' table, where 'person_name' relates to 'manager_name'. This relationship signifies an organizational hierarchy or communication path within a business, representing managerial oversight and potentially illustrating workflows and responsibility chains .

By altering the 'works' table to include foreign key constraints on 'company_name' and 'person_name', the database enforces referential integrity, ensuring that each 'person_name' must exist in the 'employee' table and each 'company_name' must exist in the 'company' table. This structure eliminates the possibility of orphaned records and maintains data consistency across related tables .

The query selecting manager_names from the 'manages' table for specific company names ('sambabank', 'ncbbank') utilizes filtering conditions directly in the WHERE clause. This approach narrows down the dataset early in the retrieval process, optimizing performance by reducing the number of rows examined, which is a critical aspect of query optimization .

The 'works' table initially had 'person_name' as its primary key. However, the primary key was dropped, and foreign keys were added for 'company_name' and 'person_name', referencing 'company' and 'employee' tables, respectively. This change allowed for a more normalized database structure while ensuring referential integrity between tables .

Adding an email field to the 'employee' table enhances the data model by providing a unique identifier for each employee aside from their name, potentially improving indexing and search capabilities. It also allows for better contact information management, leading to improved communication functionalities within applications utilizing this database .

Using 'email' as an additional candidate key in the 'employee' table would allow more flexible queries and contribute to enforcing uniqueness constraints, ensuring each email ID aligns with one employee. It simplifies user-specific data retrieval, reducing chances for data duplication and enhancing user validation processes .

Having no manager assigned could indicate either a missing data entry or organizational policy not requiring a manager for certain companies. It could also reflect decentralized managerial responsibilities. From a database integrity perspective, this absence could affect queries expecting managerial data for all companies, potentially leading to incomplete or inaccurate analyses .

You might also like