HW2

pdf

School

Purdue University *

*We aren’t endorsed by this school

Course

448

Subject

Computer Science

Date

Apr 3, 2024

Type

pdf

Pages

2

Uploaded by JusticeClover34803

Report
CS448 HW2 Spring 2023 Shatakshi Singh Question 1. (1.0 point) Suppose that each of the following operations is applied directly to the database state shown in the Figure 5.6 and database schema in the Figure 5.7 in the Textbook (7th Edition). The figures are also shown in slides 5-31 and slides 5-37 respectively. Discuss all integrity constraints violated by each operation (including the tables affected), if any, and the different ways to enforce the constraints (without modifying the operation) with some relevant explanation(s). For example, the operation Delete the PROJECT tuple with Pname = ‘ProductX’ violates a referential integrity constraint with WORKS_ON. Possible ways to enforce the operation are: (i) reject the delete; (ii) delete cascade (delete all tuples in WORKS_ON that correspond to Pname = ‘ProductX’). a) Delete the WORKS_ON tuples with Essn = ‘999887777’. b) Modify the Super_ssn attribute of the EMPLOYEE tuple with Ssn = ‘999887777’ to NULL. c) Insert tuple <‘555123456’, Phil, ‘M’, 2000-12-01, ‘Son’> into Dependent. d) Delete the DEPT_LOCATIONS tuples where Dnumber < 3. Solution: a) There are no integrity constraints violated by the operation. b) Modify the Super_ssn attribute of the EMPLOYEE tuple with Ssn = ‘999887777’ to NULL violates the referential integrity constraint with EMPLOYEE itself. a. Reject the operation c) Insert tuple <‘555123456’, Phil, ‘M’, 2000-12-01, ‘Son’> into Dependent violates the referential integrity constraint with EMPLOYEE since there is no employee with the ssn 555123456. Ways to enforce the operation would be to: a. Reject the operation d) Delete the DEPT_LOCATIONS tuples where Dnumber < 3 violates the referential integrity constraint with DEPARTMENT. Ways to enforce the operation would be to: a. Reject the operation b. Delete cascade
Question 2. (1.0 point) Database design often involves decisions about the storage of attributes. For example, the SSN can be subdivided in groups and stored in multiple attributes for security/validation purpose (validate the last four digits). Another example is the address, which can be stored as a single attribute or split in multiple attributes (City, State, ZIP, Street Address). a) Discuss specific scenarios where it is useful to store the address as one attribute (i.e., a text field) or multiple attributes (i.e., separate fields for city, state, zip, number). Explain your answer. b) Discuss at least 2 additional scenarios of information that can benefit of such decisions (split in multiple attributes)? You need to use other examples i.e. other than “Address” and “SSN”. Explain you answer. Solution: 1. It is useful to store the address as a single attribute i.e., a text field when the address is being used for the display or informational purposes only and is not used for data analysis or manipulation. For example: in a customer management system, it may be sufficient to store the address as a single attribute in a customer's profile, as it may be only used to display the customer's billing or shipping address. It is useful to store the address as multiple attributes i.e., multiple attributes like City, State, ZIP, Street Address when the address need to be used for data analysis or manipulation. For example, in a retail business, if the business wants to analyze sales data by region, having separate fields for city, state, and zip would make it easier to filter and group the data. 2. Two scenarios of information which can benefit from splitting in multiple attributes are phone numbers and dates. a. Phone numbers: These can be split into attributes such as area code, exchange, and line number. This is helpful when the phone numbers are being used for analysis such as identifying the geographical patterns in calls. b. Dates: These can be split into attributes such as day, month, and year. This is useful in case we want to sort or group data by date or specific date components, such as month or year.
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

Lab 8 - Understanding IDPS.docx
Lab 4 - Hardening Windows Operating System.docx
Lab 3 - Legal Ethics, Policies, and Professional Issues-1.docx
wk4 (1).pdf
HW1.pdf
CS 448 HW 4.docx
CIS 256 Lab 6 - Configuring Routed Networks.docx
CIS 256 Lab 4 - More calculating IPv4 Subnetting with Wi-Fi Network Design.docx
Lab 4 – Wi-Fi Hotspotting and WLAN Frame Identification (1).docx
Wireshark_NAT_v8.1.doc
ASOR1Ex1cR_AanikaManghnani.doc
homework5.tex
Recommended textbooks for you
Text book image
Fundamentals of Information Systems
Computer Science
ISBN:9781337097536
Author:Ralph Stair, George Reynolds
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
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
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781305971776
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
SEE MORE TEXTBOOKS
Recommended textbooks for you
Text book image
Fundamentals of Information Systems
Computer Science
ISBN:9781337097536
Author:Ralph Stair, George Reynolds
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
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
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781305971776
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
SEE MORE TEXTBOOKS