Homework Answers
(1)
Bdate is given the value 'Supervisior' in the insert statement which violates Domain Constraint. This is because a date value cannot be a string of alphabets only .
Bdate should be in date format.
(2)
Null value for DNum violates Referential Integrity Constraint as it is foreign key to Department table . So it cannot have Null value.
DNum should be given valid Department number.
(3)
Deletion of tuples with PName = 'Computerization' violates Referential Integrity Constraint as PNumber is foreign key to Works_On table.
(4)
Deletion of rows with Pno = 8 from Works_On table will result in Referential Integrity Constraint as Pno is foreign key to Project table.
(5)
Updating Ssn will arise Referential Integrity Constraint as Ssn is foreign key in Department, Works_On and Dependent tables .
(6)
Updating Ssn and SuperSsn together will arise Referential Integrity Constraint as Ssn is foreign key in Department, Works_On and Dependent tables .
Do ask if any doubt. Please upvote.
Add Answer to:
In class we have discussed four types of Integrity Constraints: Domain Constraint, Key Constraint, Entity Integrity...
drop table department cascade constraints; create table department ( Dname varchar2(15) not null, Dnumber int not...
drop table department cascade constraints;
create table department (
Dname varchar2(15) not null,
Dnumber int not null,
Mgr_ssn char(9) not null,
mgr_start_date Date,
primary key (Dnumber),
Unique (Dname));
insert into DEPARTMENT values ('Research', '5', '333445555',
'22-May-1988');
insert into DEPARTMENT values ('Administration', '4', '987654321',
'01-Jan-1995');
insert into DEPARTMENT values ('Headquarters', '1', '888665555',
'19-Jun-1981');
drop table employee cascade constraints;
create table employee (
Fname varchar2(15) not null,
Minit char(1),
Lname varchar2(15) not null,
Ssn char(9),
Bdate date,
Address varchar2(30),
Sex char(1),...
(SELECT pnumber FROM project, department, employee WHERE dnum=dnumber AND mgr_ssn=ssn AND lname='Smith') UNION (SELECT pnumber FROM...
(SELECT pnumber
FROM project, department, employee
WHERE dnum=dnumber AND mgr_ssn=ssn AND lname='Smith')
UNION
(SELECT pnumber
FROM project, works_on, employee
WHERE pnumber=pno AND essn=ssnAND lname='Smith')
Can you draw the relation step by step , how to solve it
?!
Figure 5.6 One possible database state for the COMPANY relational database schema. EMPLOYEE Sex Salary Super ssn Dno B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 3334455555 Ssn Fname Minit Lname John Franklin TWong 333445555 1955-12-08 638 Voss, Houston, TXM40000...
I need help with the following SQL query for a company database (script given below). The...
I need help with the following SQL query for a company database (script given below). The name of the Department. The number of employees working in that department. The number of different projects controlled by this department. The name of the project controlled by this department that has the maximum number of employees of the company working on it. The number of the above project. The cumulative sum of the number of employees of the company working on the projects...
Query #2: List the name of the project and total number of hours worked on by...
Query #2: List the name of the project and total number of hours worked on by all the employees on this project, also report the number of employees working on each project. Database: //STEP #1: CREATE TABLE employee ( fname varchar(15) not null, minit varchar(1), lname varchar(15) not null, ssn char(9), bdate date, address varchar(50), sex char, salary decimal(10,2), Super_ssn char(9), dno char(4), primary key (ssn)); CREATE TABLE department ( dname varchar(25) not null, dnumber char(4), Mgr_ssn char(9) not null,...
Query 1: Retrieve names of all the projects as well as First and Last name of...
Query 1: Retrieve names of all the projects as well as First and Last name of managers if they are working on any of these projects. Database: //STEP #1: CREATE TABLE employee ( fname varchar(15) not null, minit varchar(1), lname varchar(15) not null, ssn char(9), bdate date, address varchar(50), sex char, salary decimal(10,2), Super_ssn char(9), dno char(4), primary key (ssn)); CREATE TABLE department ( dname varchar(25) not null, dnumber char(4), Mgr_ssn char(9) not null, Mgr_start_date date, primary key (dnumber)); CREATE...
Search all employee who work total on all project less than 40 hrs from works_on table....
Search all employee who work total on all project less than 40
hrs from works_on table.
Please give me a query for this.
Figure 5.6 One possible database state for the COMPANY relational database schema. EMPLOYEE Fname Minit Lname Sex Salary Super ssn Dno B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 5 FranklinT Wong 333445555 1955-12-08 638 Voss, Houston, TX M 40000 888665555 5 JZelaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 9876543214 JenniferSWallace 9876543211941-06-20...
Please provide the relation algebra, the oracle SQL code and the output tuples for the following (Answer #6 only the BONUS question). I have the others actually I have the BONUS, but I want to compar...
Please provide the relation algebra, the oracle SQL code and the
output tuples for the following (Answer #6 only the BONUS
question). I have the others actually I have the BONUS, but I want
to compare with your solution to make sure I did it correctly.
Thank you very much!
LAB exercises 2 Write the Oracle DML query codes for the following questions and take the screen shot of the output. 1. Retrieve the name and address of all employees...
Book: Fundamental of database systems Chapter 5 exercise 5.9 Figure 3-6 here provides examples on how...
Book: Fundamental of database systems
Chapter 5 exercise 5.9
Figure 3-6 here provides examples on how to answer this
question:
Photos - 20190906_111020[3691].jpg -ox See all photos + Add to Edit & Create Share : Q u ♡ 2 # d. A view that has the project name, controm of employees, and total hours worked per week on the project for each project with more than one employee working on it. 5.9. Consider the following view, DEPT SUMMARY, defined on...
New to DB's Please help! You are designing and creating a database named COMPANY for an HR (Human Resource) Dept of...
New to DB's Please help!
You are designing and creating a database named COMPANY for an HR (Human Resource) Dept of a Software Consulting Company to manage their employees and their assigned work (projects) in the company First Step to Design and Create a Database is Creating an E-R Diagram by Identifying Entities, Attributes and Relationships between any two Entities to Create a Company Database Scheme. 1. Create an E-R Diagram for the Company database from the raw data files...
Q2. Retrieve the names of all employees from the employee table to produce output on CSV...
Q2. Retrieve the names of all employees from the employee table to produce output on CSV format or delimited format with a common delimeter, rather than separete columns. Hint:Put the whole row into a string with a semicolon as the seperator(delimeter) between thecolumns: FORMAT:(fname;minit;lname) Example: EMPLOYEES -------------- James;E;Borg Frank;T;Wong Q3. Write a query to show the employees name from the employee table in this format: first letter of the first name, followed by a dot, a blank, and the full...
drop table department cascade constraints;
create table department (
Dname varchar2(15) not null,
Dnumber int not null,
Mgr_ssn char(9) not null,
mgr_start_date Date,
primary key (Dnumber),
Unique (Dname));
insert into DEPARTMENT values ('Research', '5', '333445555',
'22-May-1988');
insert into DEPARTMENT values ('Administration', '4', '987654321',
'01-Jan-1995');
insert into DEPARTMENT values ('Headquarters', '1', '888665555',
'19-Jun-1981');
drop table employee cascade constraints;
create table employee (
Fname varchar2(15) not null,
Minit char(1),
Lname varchar2(15) not null,
Ssn char(9),
Bdate date,
Address varchar2(30),
Sex char(1),...
(SELECT pnumber
FROM project, department, employee
WHERE dnum=dnumber AND mgr_ssn=ssn AND lname='Smith')
UNION
(SELECT pnumber
FROM project, works_on, employee
WHERE pnumber=pno AND essn=ssnAND lname='Smith')
Can you draw the relation step by step , how to solve it
?!
Figure 5.6 One possible database state for the COMPANY relational database schema. EMPLOYEE Sex Salary Super ssn Dno B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 3334455555 Ssn Fname Minit Lname John Franklin TWong 333445555 1955-12-08 638 Voss, Houston, TXM40000...
Search all employee who work total on all project less than 40
hrs from works_on table.
Please give me a query for this.
Figure 5.6 One possible database state for the COMPANY relational database schema. EMPLOYEE Fname Minit Lname Sex Salary Super ssn Dno B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 5 FranklinT Wong 333445555 1955-12-08 638 Voss, Houston, TX M 40000 888665555 5 JZelaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 9876543214 JenniferSWallace 9876543211941-06-20...
Please provide the relation algebra, the oracle SQL code and the
output tuples for the following (Answer #6 only the BONUS
question). I have the others actually I have the BONUS, but I want
to compare with your solution to make sure I did it correctly.
Thank you very much!
LAB exercises 2 Write the Oracle DML query codes for the following questions and take the screen shot of the output. 1. Retrieve the name and address of all employees...
Book: Fundamental of database systems
Chapter 5 exercise 5.9
Figure 3-6 here provides examples on how to answer this
question:
Photos - 20190906_111020[3691].jpg -ox See all photos + Add to Edit & Create Share : Q u ♡ 2 # d. A view that has the project name, controm of employees, and total hours worked per week on the project for each project with more than one employee working on it. 5.9. Consider the following view, DEPT SUMMARY, defined on...
New to DB's Please help!
You are designing and creating a database named COMPANY for an HR (Human Resource) Dept of a Software Consulting Company to manage their employees and their assigned work (projects) in the company First Step to Design and Create a Database is Creating an E-R Diagram by Identifying Entities, Attributes and Relationships between any two Entities to Create a Company Database Scheme. 1. Create an E-R Diagram for the Company database from the raw data files...
Most questions answered within 3 hours.
-
Industry: Telecommunications in India
Here, you need to discuss the sources of the growth pattern
and...
asked 46 minutes ago -
Hydrobromic acid
dissolves solid iron according to the followiaung reaction:
Fe(s)+2HBr(aq)→
FeBr2(aq)+ H2(g)
Part A. What...
asked 2 hours ago -
Verify the following vector identity in SPHERICAL
COORDINATES.
div(curl(A)) = 0
asked 4 hours ago -
The work function (Φ) for a metal is 7.50×10-19 J.
What is the longest wavelength (nm)...
asked 4 hours ago -
1. Would the following procedural changes cause the
calculated mass percent Ni2+ ion in Ni(NH3)nCl2 to...
asked 6 hours ago -
1. Why does the voltage of the capacitor vary with frequency?
where does this missing voltage...
asked 7 hours ago -
p p please write each step out clearly
3. A manager of a company is considering...
asked 6 hours ago -
On October 1, Ebony Ernst organized Ernst Consulting; on October
3, the owner contributed $83,540 in...
asked 7 hours ago -
A.)As an electron moves through a region of space, its speed
decreases from 9.60 × 106...
asked 7 hours ago -
In java with the netbeans program do:
Through object programming
Create a class "ContadorP", which is...
asked 7 hours ago -
Pattern Corporation acquired all of Science
Company's outstanding stock on January 1, 2016 for $600,000 cash....
asked 7 hours ago -
Here are all accounts Tyler Co. maintains.
Tax expenses $4,750
Common stocks $48,840
SG&A expenses $2,500...
asked 8 hours ago