CS 631-Assignment 1- Tanmay Agarwal

docx

School

New Jersey Institute Of Technology *

*We aren’t endorsed by this school

Course

631

Subject

Computer Science

Date

Jan 9, 2024

Type

docx

Pages

2

Uploaded by AgentWaterPrairieDog32

Report
CS 631: DATA MANAGEMENT SYSTEMS DESIGN ASSIGNMENT 1 In class we have discussed four types of Integrity Constraints: Domain Constraint, Key Constraint, entity Integrity Constraint and Referential Integrity Constraint. Suppose that each of the following data change operations is applied directly to our COMPANY database, whose schema and instance are shown in Figures 1 and 2 respectively. Identify all the integrity constraints (if any) that will be violated if we perform the following operations. If a constraint is violated: (a) explain briefly why this happens. (b) suggest all possible actions to be taken for correcting the cause of the violation of the constraint. (1) Insert <'John', 'B', 'Smith', '333445555', 'Supervisor', '731 Fondren, Houston, TX', M, 58000, '888665555', 3> into EMPLOYEE. Answer: While inserting the above tuple in the EMPLOYEE relation, the following Integrity constraints will get violated: a. Key Constraint: Since the SSN ‘333445555’ is already present in the relation and SSN is the primary key of the relation the values in the attribute should be unique. b. Domain Constraint: The fourth value in the tuple should be a date but the inserted value is ‘Supervisor’ which is not the correct type of value to be inserted in that domain. c. Referential Integrity: The DNO attribute in EMPLOYEE relation can’t accept value 3 as there is no DNUMBER = 3 in the DEPARTMENT relation which is the primary key of the to which the DNO attribute in EMPLOYEE relation refers to. To solve the above issues, we can proceed with the following steps: i. Instead of the SSN ‘333445555’ we can insert a different SSN which is unique and is not null. ii. To overcome the violation of Domain constraint we have to insert a birth date as the fourth value of the tuple in similar format as present in the BDATE attribute of EMPLOYEE relation. iii. To overcome the violation of referential integrity we can insert the DNO as any of the values of DNUMBER in DEPARTMENT relation or we insert NULL value.
(2) Insert <NULL, 5, NULL, NULL> into PROJECT. Answer: There will be no violation of any constraints as the new tuple to be inserted maintains Domain Integrity as well as the PNUMBER attribute in PROJECT relation which is the Primary Key will also remain unviolated as the number ‘5’ is a Unique value for the attribute. Thus, maintaining the Key Constraint Integrity. (3) Delete the PROJECT tuple(s) with PNAME = 'Computerization' Answer: When deleting the tuple with PNAME = ‘Computerization’, the PNUMBER=10 will be deleted but PNO=10 in WORKS_ON relation will still be present which will violate the referential integrity of the Database because as PNO attribute is the Foreign Key which should be either null or a value present in the primary key ‘PNUMBER’ in PROJECT relation. To overcome the above issue, we can either delete the records in WORKS_ON relation with PNO=10 or we can update the values of PNO from 10 to either NULL or with number whose values are present in the PNUMBER attribute of PROJECT relation. (4) Delete the WORKS_ON tuples with PNO = 8. Answer: There will be no violation of any of the four Integrity constraints as there is no project with PNO=8 in WORKS_ON relation. (5) Update the SSN and the SALARY of the EMPLOYEE tuple(s) with DNO = 1 to '333445555' and 55000 respectively. Answer: The key Constraint Integrity will be violated if we update the SSN of the EMPLOYEE with DNO=1 as the SSN attribute in EMPLOYEE relation is the Primary Key, thus the values in the Primary key attribute should be unique and NOT NULL as it helps to identify each tuple in the relation. To overcome this issue, we must update the SSN of the employee with an SSN which is already not available in the relation, and it should not be a NULL value. (6) Update the SSN and the SUPERSSN of the EMPLOYEE tuple(s) with LNAME = 'Jabbar' to '112233445' and NULL respectively. Answer: There will be no violation of any of the Integrity constraints as the updated value of SSN is unique and is not NULL.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help

Related Documents

Workshop28_2023.pdf
quiz 2.txt
quiz 2 (1).txt
python1.txt
CSIS2101_assgn11.docx
CSIS2101_assgn9.docx
homework5_Tanmay Agarwal.docx
homework2-Tanmay Agarwal.pdf
chapter 8 itev8 exam questions.docx
CSIS2101_assgn4.docx
performance lab report 1.docx
GRA 200 Project Template (Repaired).docx
Recommended textbooks for you
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Text book image
Fundamentals of Information Systems
Computer Science
ISBN:9781337097536
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
Text book image
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781285867168
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
SEE MORE TEXTBOOKS
Recommended textbooks for you
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Text book image
Fundamentals of Information Systems
Computer Science
ISBN:9781337097536
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
Text book image
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781285867168
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
SEE MORE TEXTBOOKS