Consider the following database
Employee(eno,ename,designation,salary)
Department(dno,dname,location)
The relationship is as follows: Employee-Department: one-to-many. Add constraint employee
salary > 5000
Q 2) A Create the above database in PostGreSQL and insert sufficient records.
Answers
Answer:
To create the database for the employees in PostgreSQL follow the below steps:
- Open the pgAdmin > PostgreSQL 14 > Databases > Postgres > right-click on Postgres > Query Tool, then type the command CREATE DATABASE lwd;
- You can create the database on your server by using the above command.
- After you execute the above command, right-click and refresh the Databases on the left navigation, and you will see that the lwd database is created.
- After that, we successfully created the lwd database, and before we create any table we have to make sure that we are connected to this database and not the default Postgres database.
- Now, to create the table type the given command,
CREATE TABLE table_name (
col1 datatype,
col2 datatype,
col3 datatype,
....
);
Repeat the tables and enter the data you need to create for the data.
#SPJ1
The steps listed below should be followed to create the employee database in PostgreSQL:
You can build the database on your server by using the aforementioned command. Open pgAdmin > PostgreSQL 14 > Databases > Postgres > right-click on Postgres > Query Tool, then type the command CREATE DATABASE data.
When you right-click the Databases menu item in the left navigation after running the aforementioned command, you will see that the lwd database has been created.
After that, the data database was successfully created; however, we must ensure that we are linked to this database and not the default Postgres database before creating any tables.
Now, enter the supplied command to build the table.
CREATE TABLE table_name (
col1 datatype,
col2 datatype,
col3 datatype,
....
);
eno ename Designation Salary Date_of_joining
1 Ritaja Intern 15000 29/12/2022
2 Shivani Trainer 50000 01/06/2017
3 Akriti Manager 90000 13/08/2014
4 Anik HOM 150000 24/04/2009
dno dname loc
1 computer Bangalore
2 Math Bombay
3 Biology Chennai
4 Physics Bangalore
To create the Emp table,
The syntax:
create table Emp (
eno int primary key ,
ename varchar (20),
Designation varchar (20),
Salary int,
Date_Of_Joining varchar (15)
);
SQL>desc emp.
eno not null number
ename varchar (20)
Designation varchar (20)
Salary int
Date_Of_Joining varchar (15)
In the above solution the table has been created.
To insert values in the table created:
insert into emp(eno,ename,Designation,Salary,Date_of_joining)
values (1, 'Ritaja' , 'Intern', 15000, '29/12/2022'),
(2, 'Shivani' , 'Trainer', 50000, '01/06/2017'),
(3, 'Akriti' , 'Manager', 90000, '13/08/2014'),
(4, 'Anik' , 'HOM', 150000, '24/04/2009');
To extract values :
select * from emp.
eno ename Designation Salary Date_of_joining
1 Ritaja Intern 15000 29/12/2022
2 Shivani Trainer 50000 01/06/2017
3 Akriti Manager 90000 13/08/2014
4 Anik HOM 150000 24/04/2009
To create the Dept table as per question
Syntax:
create table dept (
dno number int ,
dname varchar (20),
loc varchar (10)
);
SQL> desc dept
Name Type
dno not null number
dname varchar2(20)
loc varchar2(10)
To insert value to dept
insert into dept(dno,dname,loc)
values(1, 'computer', 'Bangalore'),
(2, 'Math', 'Bombay'),
(3, 'Biology', 'Chennai'),
(4, 'Physics', 'Bangalore');
Therefore, the outputs of RDB for Emp and Dept in 3NF are
eno ename Designation Salary Date_of_joining
1 Ritaja Intern 15000 29/12/2022
2 Shivani Trainer 50000 01/06/2017
3 Akriti Manager 90000 13/08/2014
4 Anik HOM 150000 24/04/2009
dno dname loc
1 computer Bangalore
2 Math Bombay
3 Biology Chennai
4 Physics Bangalore