What is the solution for this question ( using database systems normalization) Consider the following table and functional dependencies:An agency called Instant Cover supplies part-time/temporary staff to hotels throughout Scotland. Thetable shown in Figure below lists the time spent by agency staff working at two hotels. The NationalInsurance Number (NIN) is unique for employee.NINContractNohoursPerWeekeNamehotelNohotelLocation113567WDC102416John SmithН25EdinburghEdinburgh234111XAC102424Diane HocineН25712670YDC102528Sarah WhiteН4Glasgow113567WDC102516John SmithН4| GlasgowNIN -> eNameNIN , ContarctNo -> hoursPerWeekContarctNo -> hotelNo , hotelLocationhotelNo -> hotelLocationa. The table is susceptible to update anomalies. Provide examples of insertion, deletion, andmodification anomalies.b. Describe and illustrate the process of normalizing the table to 3NF. State any assumptions youmake about the data shown in this table.

Normalization: Normalization is the procedure followed to organize the records in the database. It…

Consider the following table and functional dependencies: An agency called Instant Cover supplies part-time/temporary staff to hotels throughout Scotland. The table shown in Figure below lists the time spent by agency staff working at two hotels. The National Insurance Number (NIN) is unique for employee. NIN ContractNo hoursPerWeek eName hotelNo hotelLocation C1024 Edinburgh Edinburgh 113567WD 16 John Smith H25 234111XA C1024 24 Diane Hocine H25 712670ΥD C1025 28 Sarah White H4 Glasgow Glasgow 113567WD C1025 16 John Smith H4 NIN -> eName NIN , ContarctNo -> hoursPerWeek ContarctNo -> hotelNo , hotelLocation hotelNo -> hotelLocation a. The table is susceptible to update anomalies. Provide examples of insertion, deletion, and modification anomalies. b. Describe and illustrate the process of normalizing the table to 3NF. State any assumptions you make about the data shown in this table.

Consider the following table and functional dependencies: An agency called Instant Cover supplies part-time/temporary staff to hotels throughout Scotland. The table shown in Figure below lists the time spent by agency staff working at two hotels. The National Insurance Number (NIN) is unique for employee. NIN ContractNo hoursPerWeek eName hotelNo hotelLocation C1024 Edinburgh Edinburgh 113567WD 16 John Smith H25 234111XA C1024 24 Diane Hocine H25 712670ΥD C1025 28 Sarah White H4 Glasgow Glasgow 113567WD C1025 16 John Smith H4 NIN -> eName NIN , ContarctNo -> hoursPerWeek ContarctNo -> hotelNo , hotelLocation hotelNo -> hotelLocation a. The table is susceptible to update anomalies. Provide examples of insertion, deletion, and modification anomalies. b. Describe and illustrate the process of normalizing the table to 3NF. State any assumptions you make about the data shown in this table.

Oracle 12c: SQL

3rd Edition

ISBN:9781305251038

Author:Joan Casteel

Publisher:Joan Casteel

Chapter1: Overview Of Database Concepts

Section: Chapter Questions

Problem 16MC

See similar textbooks

Similar questions

1, what is the Average number of days it takes for a client to receive a visit (tbl_ClientVisits.clientVisit_id), from the time they were admitted for service as part of a treatment episode (tbl.ClientEpisodes>auto_Admission_date) consider the tables join is done column needed - ClientVisit_id - Episode_id. - auto_admission_date - auto_discharge date tbl_clientVisit represent all the visits the company client make to the business. each visit is associated with one, and only one clientEpisode, however, each episode may consists of multiple visits.
For the following collection of entities, give the assumptions concerning the relationship between entities. EMPLOYEE( EMP _CODE, EMP _LNAME, EMP _FNAME, EMP _INITIAL) BUILDING(BLDG_CODE, BLDG_NAME, EMP _CODE) ITEM(ITEM_ID, ITEM_DESCRIPTION, ITEM_ROOM, BLDG_CODE)
Given the following relation that stores details of employees and their projects: ProjectID EmployeeName EmployeeSalary 100-A Salim Housni 2000 100-A Taleb Maashani 1800 100-B Taleb Maashani 1800 200-A Salim Housni 2000 200-B Salim Housni 2000 200-C Salim Housni 1350 200-C Taleb Maashani 1800 200-D Salim Housni 1350 If the functional dependencies are consistent with the above relation, put TRUE. If they are not, put FALSE ProjectID --> EmployeeName (....) ProjectID --> EmployeeSalary (....) ProjectID , EmployeeName -->EmployeeSalary (....) EmployeeName --> EmployeeSalary (....) EmployeeSalary --> ProjectID (....) EmployeeSalary --> EmployeeName , ProjectID (....)
The following shows a training company’s workshops, the number of days of each, and their registration fees. Workshop # of Days Registration FeeHandling Stress 3 $ 1,000Time Management 2 $ 800Supervision Skills 3 $ 1,200Negotiation 6 $ 1,500Interview Skills 1 $ 300The training company conducts its workplace in the six locations shown in the following table. The table also shows the lodging fees per day at each location. Location Lodging Fees per DayAustin $ 151Chicago $ 225Dallas $ 179Orlando $ 322Phoenix $ 175Raleigh $ 165When a customer registers for a workshop, he or she must pay the registration fee plus the lodging fees for the selected location. For example, here are the charges to attend the Supervision Skills workshop in Orlando. Registration: $1,200Lodging: $322 * 3 days = $966Total: $2,166Create an application that lets the user input a workshop and location from the lists. The application should display the workshop and lodging prices, calculate and display the registration…
Regional Labs is a company that conducts research and development work on a contract basis for other companies and organizations. Figure 2-31 shows data that Regional Labs collects about projects and the employees assigned to them. This data is stored in a relation (table) named PROJECT: PROJECT (ProjectID, EmployeeName, EmployeeSalary) Assuming that all functional dependencies are apparent in this data, which of the following are true? ProjectID → EmployeeName ProjectID → EmployeeSalary (ProjectID, EmployeeName) → EmployeeSalary EmployeeName → EmployeeSalary EmployeeSalary → ProjectID EmployeeSalary → (ProjectID, EmployeeName
SQL: Consider the following relational schema: Staff (staffNo, name, dept, skillCode) Skill (skillCode, description, chargeOutRate) Project (projectNo, startDate, endDate, budget, projectManagerStaffNo) Booking (staffNo, projectNo, dateWorkedOn, timeWorkedOn) where: Staff contains staff details and staffNo is the key. Skill contains descriptions of skill codes (e.g. Programmer, Analyst, Manager, etc.) and the charge out rate per hour for that skill; the key is skillCode. Project contains project details and projectNo is the key. Booking contains details of the date and the number of hours that a member of…
Regional Labs is a company that conducts research and development work on a contract basis for other companies and organizations. Figure 2-33 shows data that Regional Labs collects about projects and the employees assigned to them. This data is stored in a relation (table) named PROJECT: PROJECT (ProjectID, EmployeeName, EmployeeSalary)A. Assuming that all functional dependencies are apparent in this data, which of the following are true? 1. ProjectID → EmployeeName 2. ProjectID → EmployeeSalary 3. (ProjectID, EmployeeName) → EmployeeSalary 4. EmployeeName → EmployeeSalary 5. EmployeeSalary → ProjectID 6. EmployeeSalary → (ProjectID, EmployeeName) B. What is the primary key of PROJECT? C. Are all the nonkey attributes (if any) dependent on the primary key? D. In what normal form is PROJECT? E. Describe two modification anomalies that affect PROJECT. F. Is ProjectID a determinant? If so, based on which functional dependencies in part A? G. Is EmployeeName a determinant? If so, based on…
Consider the following relation with functional dependencies as shown below. R{sno, sname, age, cno, cname, group) R(A, B, C, D, E, F) Divyansh sharma AB, C C F D-E In which normal form is the relation R is in ? (A) First normal form (B) Second normal form (C) Third normal form (D) Boyce Codd normal form
Using your knowledge of the college environment, determine the functional dependencies that exist in the following table. After determining the functional dependencies, convert this table to an equivalent collection of tables that are in third normal form. Do not create a diagram of the tables. Instead use the symbolism like that found in the textbook when showing functional dependencies. (StudentNum, StudentName, NumCredits, AdvisorNum, AdvisorName, DeptNum, DeptName, CourseNum, Description, Term, Grade)
Functional Dependency & Normalization The table below lists customer/car hire data. Each customer may hire cars from various outlets throughout Glasgow. A car is registered at a particular outlet and can be hired out to a customer on a given date. carReg make model custNo custName hireDate outletNo outletLoc M565 0GD Tesla Model S C100 Smith, J 5/14/2018 101 San Francisco M565 0GD Tesla Model S C201 Helena, P 5/15/2016 101 San Francisco N734 TPR Nissan Sunny C100 Smith, J 5/16/2016 101 San Francisco M134 BRP Tesla Model S C313 Blatt, O 5/14/2018 102 Los Angeles M134 BRP Tesla Model S C100 Smith, J 5/20/2018 102 Los Angeles M611 0PQ Nissan Sunny C295 Alice, T 5/20/2018 102 Los Angeles            Fig: Table 1 Identify the functional dependencies represented by the data shown in the table above. State any assumptions you make about the data. Using…
Given the following relation that stores details of employees and their projects: If the functional dependencies are consistent with the above relation, put TRUE. If they are not, put  FALSE. 1.ProjectID ➡  EmployeeName 2.ProjectID ➡  EmployeeSalary 3.ProjectID ,  EmployeeName ➡  EmployeeSalary 4.EmployeeName ➡  EmployeeSalary 5.EmployeeSalary ➡  ProjectID 6.EmployeeSalary ➡  EmployeeName , ProjectID
Given the following relation that stores details of student registration: Stu_ID Name Town Province Course RegisteredDate S101 Malak Al Nadabi Al Khuwair Western ITDR2106 02-02-2020 S101 Malak Al Nadabi Al Khuwair Western ITDR1105 14-09-2019 S102 Nadar Al Hashli Daesit Southern ITDR1102 05-02-2020 S102 Nadar Al Hashli Daesit Southern ITDR1101 05-02-2020 S103 Basit Barkat Ruwi Central ITDR1105 06-02-2020 S104 Yeshan Al Khuwair Western ITDR2106 15-09-2019 If the functional dependencies are consistent with the above relation, put TRUE. If they are not, put  FALSE. Stu_ID à Name,  Town Town à Province Course à RegisteredDate   RegisteredDate à Course   Stu_ID, Course à RegisteredDate   Stu_ID   à Course Stu_ID

SEE MORE QUESTIONS

Recommended textbooks for you

Oracle 12c: SQL

Computer Science

ISBN:

9781305251038

Author:

Joan Casteel

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

Oracle 12c: SQL

Computer Science

ISBN:

9781305251038

Author:

Joan Casteel

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

SEE MORE TEXTBOOKS