1.
Create a databse called “w” using: create database w;
2. Create the relation “department”
create table department(
Dname varchar(30),
Dnumber varchar(30) not null primary key,
Start_Date date);
3. Create the relation “Employee”
create table employee(
ssno char(10) not null primary key,
salary double precision,
sex char(1),
Fname varchar(50),
Mname varchar(50),
Lname varchar(50),
Birth_Date date,
Dnumber varchar (30),
Foreign key(Dnumber) references department(Dnumber),
Foreign key (ssno) references employeew(ssno));
4. Add a foreign key to relation “department”(if it has)
alter table department add ssno char(10) references employee(ssno);
5. Create the relation “project”.
create table project(
Pname varchar(40),
Pnumber varchar (40),
Plocation varchar(20)
primary key (Pname, Pnumber)
Dnumber varchar (30) references department(Dnumber));//while creating
the the table
OR
alter table project add Dnumber varchar(30) references
department(Dnumber);//after table creation
6. Create the relation “dependent”
create table dependent(
ssno char (10),
Name varchar(50),
sex char(1),
Birth_Date date,
Relationship varchar (50),
primary key(ssno, Name));
7. Create the relation “works_on”
create table works_on(
ssno char(10),
Pname varchar(40),
Pnumber varchar(40),
hours time,
primary key(ssno, Pname, Pnumber));
8. Create the relatio “department_location”
create table department_Location(
Dnumber varchar(30),
Dlocation varchar(40),
primary key (Dnumber, Dlocation));
Inserting Values to each Relation
1. Insert the following data in to department relation using:
insert into department (Dname, Dnumber,Start_Date) values('cs', '123w',
'1987-05-04');
insert into department (Dname, Dnumber,Start_Date) values('MBA', '126w',
'1987-05-10');
2. Insert the following data in to employee relation using:
insert into employee(ssno, salary, sex, Fname, Mname, Lname, Birth_Date)
values('w124', 12345.00, 'M', 'Haile', 'Beyene', 'Grum', '1987-09-04');
insert into employee(ssno, salary, sex, Fname, Mname, Lname, Birth_Date)
values('w164', 12348.00, 'M', 'Alem', 'Sisay', 'Gebre', '1999-10-04');
insert into employee(ssno, salary, sex, Fname, Mname, Lname, Birth_Date)
values('w144', 12348.00, 'M', 'Hiwot', 'Abebe', 'Shibru', '1987-07-25');
insert into employee(ssno, salary, sex, Fname, Mname, Lname, Birth_Date)
values('w194', 12348.00, 'F', 'Lemlem', 'Beyene', 'Eshete', '1983-06-
24');
insert into employee(ssno, salary, sex, Fname, Mname, Lname, Birth_Date)
values('w444', 12348.00, 'F', 'Roman', 'Debebe', 'Berhanu', '1999-11-
04');
3. Insert the following data in to project relation using:
insert into project(Pname, Pnumber, Plocation) values ('Road', '123',
'Dessie');
insert into project(Pname, Pnumber, Plocation) values ('Building',
'124', 'AA');
insert into project(Pname, Pnumber, Plocation) values ('Building',
'125', 'Adama');
insert into project(Pname, Pnumber, Plocation) values ('Family
Planning', '126', 'Bahrdar');
insert into project(Pname, Pnumber, Plocation) values ('Family
Planning', '127', 'Jimma');
4. Insert the following data in to dependent relation using:
insert into dependent(ssno, name,sex, Birth_Date, Relationship)
values('123w', 'Eshetu', 'M', '1978-08-06', 'Father');
insert into dependent(ssno, name,sex, Birth_Date, Relationship)
values('122w', 'Gedefaw', 'M', '1978-08-06', 'Father');
insert into dependent(ssno, name,sex, Birth_Date, Relationship)
values('125w', 'Haile', 'M', '1978-08-06', 'Father');
insert into dependent(ssno, name,sex, Birth_Date, Relationship)
values('128w', 'Gebre', 'M', '1978-08-06', 'Father');
5. Insert the following data in to works_on relation using:
insert into works_on(ssno, Pname, Pnumber, hours) values('123w',
'Construction','123w','6:00');
insert into works_on(ssno, Pname, Pnumber, hours) values('126w',
'Construction','126w','7:00');
insert into works_on(ssno, Pname, Pnumber, hours) values('126w',
'Construction','128w','7:00');
6. Insert the following data in to department_location relation using:
insert into Department_Location(Dnumber, Dlocation) values('126w',
'Desie');
insert into Department_Location(Dnumber, Dlocation) values('129w',
'Assela');
insert into Department_Location(Dnumber, Dlocation) values('125w',
'Bahrdar');
insert into Department_Location(Dnumber, Dlocation) values('196w',
'Adama');
