Update anomaly, Data redundancy, data inconsistency The agent name and phone numbers presented below are stored in both the customer andagent files (data is redundant). Leah has changed his phone number, identify the places(row number, column name) where you need to make the changes?b. Explain how data redundancy would affect data consistency through this example?Reference: Ch1, Slide 27Customer File:12345678a.123C_NAMEC_PHONEAlfred A. Ramas 615-844-2573Leona K. Dunne 713-894-1238Kathy W. Smith 615-894-2285 125 Oak Ln, Babs, TN615-894-2180 217 Lee Ln, Babs, TN615-222-1672 Box 111, New, TN713-442-3381 387 Troll Dr., Fox, KY615-297-1228 21 Tye Rd., Nash, TN615-290-2556 155 Maple, Nash, TNPaul F. OlowskiMyron OrlandoAmy B. O'BrianAgent fileJames G. BrownGeorge WilliamsC_ADDRESSC_ZIP A_NAME218 Fork Rd., Babs, TN 36123 Leah F. HahnBox 12A, Fox, KY 25246 Alex B. Alby36123 Leah F. Hahn36123 Leah F. Hahn36155 Alex B. Alby25246 John T. Okon37118 Leah F. Hahn37119 John T. OkonRENA_PHONE TP AMT615-882-1244 T1 100.00 2012-04-05 00:00:00.000713-228-1249 T1 250.00 2012-06-16 00:00:00.000615-882-2144 S2 150.00 2013-01-29 00:00:00.000615-882-1244 S1 300.00 2012-10-14 00:00:00.000713-228-1249 T1 100.00 2012-12-28 00:00:00.000615-123-5589 T2 850.00 2012-09-22 00:00:00.000615-882-1244 S1 120.00 2013-03-25 00:00:00.000615-123-5589 S1 250.00 2012-07-17 00:00:00.000A_NAME A_PHONE A_ADDRESS ZIPAlex B. Alby 713-228-1249 123 Toll, Nash, TNLeah F. H... 615-882-1244 334 Main, Fox, KYJohn T. O... 615-123-5589 452 Elm, New, TNHIREDYTD_PAY YTD_FIT YTD_FICA YTD_SLS DEP37119 2000-11-01 00:00:00.000 26566.24 6641.56 2125.30 132735.75 325246 1986-05-23 00:00:00.000 32213.76 8053.44 2577.10 138967.35 036155 2005-06-15 00:00:00.000 23198.29 5799.57 1855.86 127093.45 2

Data Redundancy: Data redundancy refers to the duplication of data within a database system. It…

The agent name and phone numbers presented below are stored in both the customer and agent files (data is redundant). Leah has changed his phone number, identify the places (row number, column name) where you need to make the changes? b. Explain how data redundancy would affect data consistency through this example? Reference: Ch1, Slide 27 a.

The agent name and phone numbers presented below are stored in both the customer and agent files (data is redundant). Leah has changed his phone number, identify the places (row number, column name) where you need to make the changes? b. Explain how data redundancy would affect data consistency through this example? Reference: Ch1, Slide 27 a.

Oracle 12c: SQL

3rd Edition

ISBN:9781305251038

Author:Joan Casteel

Publisher:Joan Casteel

Chapter13: Views

Section: Chapter Questions

Problem 3MC

See similar textbooks

Similar questions

How do you create an index? How do you create a unique index? What is the difference between an index and a unique index?
An EMPLOYEES table was added to the JustLee Books database to track employee information. Display a list of each employee’s name, job title, and manager’s name. Use column aliases to clearly identify employee and manager name values. Include all employees in the list and sort by manager name.
Problem 1 and 2 are based on the following query: SELECT EMP_LNAME, EMP_FNAME, EMP_AREACODE, EMP_SEX FROM EMPLOYEE WHERE EMP_SEX = 'F' AND EMP_AREACODE = '615' ORDER BY EMP_LNAME, EMP_FNAME; What indexes should you create? Write the required SQL commands.
Assuming there is a table named payroll, with 1000 rows of data. The table contains attributes staffid (as primary key with values between 1001 and 2000), allowance, and salary. CREATE OR REPLACE TRIGGER trg_salary BEFORE INSERT OR UPDATE OF salary ON payroll FOR EACH ROW BEGIN END: DBMS_OUTPUT.PUT_LINE (`Payroll table changed.'); IF UPDATING THEN ELSE DBMS_OUTPUT.PUT_LINE(New salary: ' || :NEW.salary); DBMS_OUTPUT.PUT_LINE('ID:"||:NEW.staffid); DBMS_OUTPUT.PUT_LINE(Allowance:||:NEW.allowance); DBMS_OUTPUT.PUT_LINE('Salary:'||:NEW.salary); END IF; Given a database trigger as presented above, with the assumption of SET SERVEROUTPUT ON command is enabled, state 'True' or 'False' for each of the following statements: ✓ Trigger trg_salary will be fired after a row is deleted from the payroll table. A. True ✓ Message 'Payroll table changed.' will be displayed ONE THOUSAND (1000) times before a valid SQL statement UPDATE employee SET increment = 1.05 is successfully executed. B. False ✓…
Database course: a. Add attribute deptId to the table course. b. Update deptId in table course of course NW3101 to “NW”. c. Create a view named batch1 that contains student id, student name and the department name of all students.
Assuming there is a table named payroll, with 1000 rows of data. The table contains attributes staffid (as primary key with values between 1001 and 2000), allowance, and salary. CREATE OR REPLACE TRIGGER trg_salary BEFORE INSERT OR UPDATE OF salary ON payroll FOR EACH ROW BEGIN END; DBMS_OUTPUT.PUT_LINE (`Payroll table changed.'); IF UPDATING THEN ELSE DBMS_OUTPUT.PUT_LINE(New salary: || :NEW.salary); DBMS_OUTPUT.PUT_LINE(ID:||:NEW.staffid); DBMS_OUTPUT.PUT_LINE(Allowance:||: NEW.allowance); DBMS_OUTPUT.PUT_LINE(Salary:'||:NEW.salary); END IF; Given a database trigger as presented above, with the assumption of SET SERVEROUTPUT ON command is enabled, state 'True' or 'False' for each of the following statements: ✓ Trigger trg_salary will be fired after a row is deleted from the payroll table. A. True ✓ Message 'Payroll table changed.' will be displayed ONE THOUSAND (1000) times before a valid SQL statement UPDATE employee SET increment = 1.05 is successfully executed. B. False ✓ Message…
Task 6: The Car Maintenance team considered that the available maintenance tasks should also have the price information in the database. Alter the MAINTENANCE_TYPES table to include a new column named MAINTENANCE_PRICE of type FLOAT. ANSWER IN MYSQL PLEASE
5
One of the TAL Distributors database's denormalized tables is as follows: The Customer table is no longer in third normal form due to which column or fields (CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum, RepName)? In this example, what is the denormalized table's normal form?
DATABASE SQL CREATE TABLE PATIENT_RECORD(RECORD_ID INT PRIMARY KEY,PATIENT_ID INT NOT NULL,TREAT_ID INT NOT NULL,MED_ID INT NOT NULL,QUANTITY_MED VARCHAR(64) NOT NULL,DATE DATE,COMPLAINT TEXT NOT NULL,MEDICAL_FINDINGS TEXT NOT NULL,DOC_INCHARGE VARCHAR(64) NOT NULL,FOREIGN KEY(PATIENT_ID)REFERENCES PATIENT(PATIENT_ID),FOREIGN KEY(TREAT_ID)REFERENCES TREATMENT(TREAT_ID),FOREIGN KEY(MED_ID) REFERENCES MEDICINE(MED_ID)); INSERT PATIENT RECORD DATA RECORD_ID PATIENT_ID TREAT_ID MED_ID QUANTITY_MED DATE COMPLAINT MEDICAL_FINDINGS DOC_INCHARGE 100 01 1003 232 10mg 2020-12-10 Food allergy Patient allergic to seafood Joseph Liam 101 01 1002 234 10mg 2020-12-10 High Blood Test The blood test result is high and abnormal Nurhidayat Bin Anuar 102 02 1003 233 10mg 2020-12-10 Cough Productive cough. Yellow mucus detected Joseph Liam 103 03 1002 235 100mg 2020-12-10 Chest pain and nausea Patient…
please use sql to answer the following question and erd and code from the last test (tesk 3) have been providing and it connects to task 4 as well the code is shown below CREATE PROCEDURE PROD_ADD_SP( product_name IN VARCHAR2, description IN VARCHAR2 , image_filename IN VARCHAR2, price IN NUMBER , active_status IN NUMBER) ASBEGIN INSERT INTO BB_PRODUCT VALUES(product_name, description, image_filename, price, active_status);END PROD_ADD_SP; 1EXECUTE PROD_ADD_SP.execute('Roasted Blend','Well-balancedmix of roasted beans, a medium body','roasted.jpg',9.50,1); 2 select * from BB_PRODUCT; Task 4: Adding Descriptions for Order Status Codes When a shopper returns to the Web site to check an order’s status, information from the BB_BASKETSTATUS table is displayed. However, only the status code is available in the BB_BASKETSTATUS table, not the status description. We also need Description of order status. Create a function named STATUS_DESC_SF that accepts a stage ID and returns the…
write SQL statements for given questions in order to get familiar to basicSQL data manipulation and definition statements by using insert using update using alter create table create view2 Problem descriptionQuestions are listed below. See template for required insert, delete and update statements. You will also findsome comments and queries in order to see some possible problems. 1. DEFINE TABLE AUTHORS WITH COLUMNS AUTHORID INT AND AUTHORNAME VARCHAR(30),ADD COLUMN LEVEL PRIMARY KEY CONSTRAINT FOR AUTHORID, ADD COLUMN LEVEL NOT NULL CONSTRAINT FOR AUTHORNAME 2. DEFINE TABLE PUBLISHERS WITH COLUMNS PUBLISHERID INT AND PUBLISHERNAMEVARCHAR(30), ADD TABLE LEVEL PRIMARY KEY CONSTRAINT FOR PUBLISHERID, ADDTABLE LEVEL UNIQUE CONSTRAINT FOR PUBLISHERNAME 3. ADD NEW COLUMN(CITY VARCHAR(20)) TO PUBLISHERS TABLE 4. SET CITY OF PUBLISHER CAN YAYINLARI TO ˙ISTANBUL 5. DEFINE TABLE NAMED BOOKS WITH COLUMNS BOOKID INT, TITLE VARCHAR(30), AUTHORID ?, PUBLISHERID ?, GENRE VARCHAR(20), ADD…

SEE MORE QUESTIONS

Recommended textbooks for you

Oracle 12c: SQL

Computer Science

ISBN:

9781305251038

Author:

Joan Casteel

Publisher:

Cengage Learning

A Guide to SQL

Computer Science

ISBN:

9781111527273

Author:

Philip J. Pratt

Publisher:

Course Technology Ptr

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

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

Np Ms Office 365/Excel 2016 I Ntermed

Computer Science

ISBN:

9781337508841

Author:

Carey

Publisher:

Cengage

CMPTR

Computer Science

ISBN:

9781337681872

Author:

PINARD

Publisher:

Cengage

Oracle 12c: SQL

Computer Science

ISBN:

9781305251038

Author:

Joan Casteel

Publisher:

Cengage Learning

A Guide to SQL

Computer Science

ISBN:

9781111527273

Author:

Philip J. Pratt

Publisher:

Course Technology Ptr

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

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

Np Ms Office 365/Excel 2016 I Ntermed

Computer Science

ISBN:

9781337508841

Author:

Carey

Publisher:

Cengage

CMPTR

Computer Science