(Solved) : Execute Following Query Db Display Discuss Integrity Constraints Violated Following Operat Q30580839 . . .
Execute the following Query on theDb to display and discuss the integrity constraints violated by anyof the following operations
1. Insert(‘Robert’, ‘F’, ‘Scott’, ‘943775543’, ’21-JUN-42′, ‘2365 NewcastleRd, Bellaire, TX’, M, 58000, ‘888665555’, 1 ) intoEMPLOYEE.
2. Insert ( ‘677678989’, null, ‘40.0’ ) into WORKS_ON.
3. Insert ( ‘453453453’, ‘John’, M, ’12-DEC-60′, ‘SPOUSE’ ) intoDEPENDENT
4. Delete the WORKS_ON tuples with ESSN= ‘333445555’.
5. Modify the MGRSSN and MGRSTARTDATE of the DEPARTMENT tuple withDNUMBER=5 to ‘123456789’ and ’01-OCT-88′, respectively.
Alter thetables to
1. Add Foreign Keysusing Alter Table [if not done earlier].
2. Drop Foreign key defined on SuperSSN and add it using Altertable command.
3. Make name of Project as Unique and sex of employee as notnull.
4. In the copy table add the columns door no, street, city, State,Continent.
5. Make salary of employee to accept real values.
Mention the SQL code used and the Output for each and everypart.
Table Name: Employee
Attribute
Data Type
Constraint
First Name
Varchar (15)
Not Null
Mid Name
Char(2)
Last Name
Varchar (15)
Not Null
SSN Number
Char (9)
Primary Key
Birthday
Date
Address
Varchar (50)
Sex
Char(1)
Sex In (M,F,m,f)
Salary
Number (7)
Default 800
Supervisor SSN
Char (9)
Foreign Key Employee (SSN) on delete set null
Department number
Number(5)
Foreign key to department number of department table on deletecascade
Table Name : Department
Attribute
Data type
Constraint
Department Name
Varchar(15)
Not Null
Department number
INT(5)
Primary key
Manager SSN
Char (9)
Foreign key-Employee (SSN)
on delete set null
Manage start date
Date
Table Name : Dept_locations
Attribute
Data type
Constraint
Department Number
Number(5)
Department (dep no) onDelete
Cascade
Department Location
Varchar (15)
Table Name: Project
Attribute
Data type
Constraint
Project Name
Varchar2(15)
Not Null
Project number
Number(5)
Primary key
Project Location
Varchar2(50)
Department Number
Number(5)
Foreign Key –Department (dep
no ) on delete set null
Table Name: Works_On: The combination ofEmployee SSN and Project Number must be a Primary Key
Attribute
Data type
Constraint
Employee SSN
Char (9)
Foreign Key
Employee (SSN) on delete cascade
Project number
INT(5)
Foreign Key project (Pnumber) on delete cascade
Hours
Decimal (3,1)
Not null
Name: Dependent
The combination of EmployeeSSN and Dependent Name must be a Primary Key.
Attribute
Datatype
Constraint
Employee
Char (9)
Foreign Key- Employee (SSN) on Delete
Cascade
Dependent Name
Varchar(15)
Sex
Char(1)
Check Sex in (M,F,m,f)
Birthday
Date
Relationship
Varchar(8)
Data for table -Dept_Locations
Dep No
D Location
1
Houston
1
Chicago
2
New York
2
San Francisco
3
Salt Lake City
4
Stafford
4
Bellaire
5
Sugarland
5
Houston
Data for Table -Works_On
ESSN
Pno
Hours
123456789
3388
32.5
123456789
1945
7.5
666884444
3388
40.0
453453453
7745
20.0
453453453
2212
20.0
333445555
7745
10.0
333445555
6688
10.0
333445555
4345
35.0
333445555
2212
28.5
999887777
2212
11.5
543216789
2212
17.0
554433221
1945
21.5
Data for Table -Dependent
ESSN
Dependent_name
Sex
Bdate
Relationship
333445555
Alice
F
05-Apr-76
Daughter
333445555
Theodore
M
25-Oct-73
Son
333445555
Joy
F
03-May-48
Spouse
987654321
Abner
M
29-Feb-32
Spouse
123456789
Alice
F
31-Dec-78
Daughter
123456789
Elizabeth
F
05-may-57
Spouse
Expert Answer
Answer to Execute Following Query Db Display Discuss Integrity Constraints Violated Following Operat Q30580839 . . .
OR