a) Create a matching table on the database server for each of the relations. In

Question

a) Create a matching table on the database server for each of the relations. In each case, consider which data type makes sense for which column.

Remember the specification and the meaningful naming of the primary and foreign keys.
NULL values are not allowed in the following columns: NAME, SALARY, P_NAME, Dep_num.

b) Fill in the tables with the given data .You can think of the project description.

c) Additionally create constraints for the following constraints.
Name all constraints.
If necessary, search for the appropriate syntax in the Oracle documentation and look at so-called "check constraints".
I. Every project must have a manager
II. The employee number must be at least three digits
III. The salary must not be negative

d) In the Employees table, add a "Setting Date" column with the appropriate data type.
The column should get a NOT NULL constraint.
You can choose the hiring data of the employees at random (but different!).

e) Remove the Proz column from the Emp_program table

Dep num Employee Empnum Name City Kali Job Programmer Analyst Salary 4500 5600 Max Wellly MartinOm dising expert 8000 Sara Hani 112 205 Hari 6400 4 Hafof Hafof Engineer Administrator 198 5000 199 Proj manager 205 Pro description Pro num 12 18 17 Name Datawarehouse intranet Project DBMigration VU Project 198 198 Proz 100 20 70 30 80 50 Emp num 112 205 Pro num 12 18 17 17 18 Emp programm 198 198 198

Explanation / Answer

a)CREATE TABLE EMPLOYEE
(
Emp_num int primary key,
Name varchar(64),
city varchar(64) not null,
job varchar(64) not null,
salary int,
dep_num int
);

create table project
(
Pro_num int,
Name varchar(64),
Pro_description varchar(64) not null deafult '....',
proj_manager int,
primary key(Pro_num),
Foreign key(proj_manager) references employee(emp_num)
);

create table emp_programm
(
Pro_num int,
Emp_num int,
Proz int
);

b)insert into Employee(emp_num,Name,City,job,Salary,Dep_num)
values
(112,'Max','Kali','Programmar',4500,3),
(205,'Welly','hari','Analyst',5600,3),
(117,'Martin','Oman','Advertising expert',8000,5),
(198,'Sara','Hafof','Engineer',6400,4),
(199,'Hani','Hafof','Administrator',5000,3);

insert into Project(pro_num,Name,Proj_manager)
values(12,'Datawarehouse',205),
(18,'intranet',117),
(17,'ProjectDBMigration'198),
(33,'VU',198);

insert into emp_program(Pro_num,Emp_num,Proz)
values(12,112,100),
(18,205,20),
(17,117,70),
(17,198,30),
(18,198,80),
(33,198,50);

c)alter table emp_program
add constraint chk_Proz check(Proz>=30);

1.alter table Project
add constraint chck_cons check(Proj_manager is not null);

2.alter table employee
add constraint chck_emp check(len(emp_num)>=3);

3.alter table employee
add constraint chck_sal check(salary<0);

d) alter table employee
add SettingDate datetime not null;

e)alter table emp_program
Drop column proz;

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.