A Guide to SQL
9th Edition
ISBN: 9781111527273
Author: Philip J. Pratt
Publisher: Course Technology Ptr
expand_more
expand_more
format_list_bulleted
Concept explainers
Question
Chapter 2, Problem 4TD
Program Plan Intro
Functional dependency:
- A column “X” is said to be functionally dependent on another column “Y”, when at any point of time, a value for “X” determines a single value for “Y”.
- In other words, a column “X” is said to be functionally dependent on another column “Y”, when two tuples agree on attribute “X” values then they should also agree on attribute “Y” values.
- It is usually represented as “XY”, where the terms “X” and “Y” are composite.
- It can be said as either “Y” is functionally dependent on “X” or as “X” functionally determines “Y”.
Normalization:
- Normalization refers to the method of representing a
database in the terms of relations, which are in the standard normal forms. - It is organized in a table manner to reduce data redundancy and data dependency.
Third Normal Form (3NF):
A table or a relation is said to be in third normal form if and only if it is in second normal form and if the candidate keys are its only determinants.
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
Determine the functional dependencies that exist in the following table and then convert this table to an equivalent collection of tables that are in third normal form. Office (OFFICE_NUM, OFFICE_NAME, (ADDRESS, SQR_FT, BDRMS, FLOORS, MONTHLY_RENT, OWNER_NUM)
Use the following ERD to answer the next 2 questions:
What is the primary key of the WorkHours table?
Indicate the redundant field in the Employees table.
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.
ITEM(ITEM_ID, DESCRIPTION, ON_HAND, CATEGORY, LOCAITON, PRICE,(INVOICE_NUM, INVOICE_DATE, CUST_ID, FIRST_NAME, LAST_NAME, QUANTITY, QUOTED_PRICE))
Chapter 2 Solutions
A Guide to SQL
Ch. 2 - Prob. 1RQCh. 2 - What is an attribute?Ch. 2 - What is a relationship? What is a one-to-many...Ch. 2 - Prob. 4RQCh. 2 - What is a relation?Ch. 2 - Prob. 6RQCh. 2 - Prob. 7RQCh. 2 - How do you qualify the name of a field, and when...Ch. 2 - Prob. 9RQCh. 2 - What is a primary key? What is the primary key for...
Ch. 2 - Prob. 11RQCh. 2 - Prob. 12RQCh. 2 - Define second normal form. What types of problems...Ch. 2 - Define third normal form. What types of problems...Ch. 2 - Prob. 15RQCh. 2 - Prob. 16RQCh. 2 - List the changes you would need to make to your...Ch. 2 - Prob. 1TDCh. 2 - Prob. 2TDCh. 2 - Prob. 3TDCh. 2 - Prob. 4TDCh. 2 - Prob. 5TDCh. 2 - Prob. 1CATCh. 2 - Identify the functional dependencies in the...Ch. 2 - Prob. 3CATCh. 2 - Determine the functional dependencies that exist...Ch. 2 - Prob. 2SCGCh. 2 - Prob. 3SCG
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Similar questions
- Consider the Sales Order and table used to store its data below. Sales_Order (SalesOrderNo, Date, CustomerNo, CustomerName, CustomerAddress, ClerkNo, ClerkName, ItemNo, Description, Quantity, UnitPrice, OrderTotal) Please normalize this table to 3NF.arrow_forwardChange to use the database frozenfood Alter table customerOrder add column total as data type decimal size 7,2, not null, default value of 0.0 Update table customerOrder, set column total equal to column quantity multiplied by subquery a. (select column price from table product where column productId in table customerOrder equals column id in table product) Update table customerOrder, set column orderStatelId equal to subquery a. (select column id from table orderstate where state equals “Payment received") Alter table customerPayment add column paymentDate as data type date, not null, default value of (CURRENT_DATE) (Note: the parenthesis MUST be included) Insert into table customerPayment, columns customerOrderId and total result set returned from query a. Select columns id and total from table customerOrder where column orderStateld is equal to subquery i. (select column id from table orderState where column state is equal to "Payment received") Write Test Case 1 as a SQL join query…arrow_forwardNote that primary keys are in BOLD. Note also that attributes with the same name in different tables are foreign keys. For example, doctorID in the Treatment table is a foreign key that refers to the Doctor table. Doctor(doctorId:integer, specialtyID:integer, firstName:varchar(20), lastName:varchar(20), salary:numeric(11,2)) Specialty(specialtyID:integer, specialtyName:varchar(20), specialtyCost:numeric(11,2)) Treatment(doctorId:integer, patientSSN:integer, dateTreated:date, costOfTreatment:numeric(11,2)) Patient(patientSSN:integer, firstName:varchar(20), lastName:varchar(20), street:varchar(30), city:varchar(15), state:char(2),zipCode:integer(5)) 2.3) Write an SQL statement to increase the cost of all Specialty treatment by 2.5% 2.4) Write a SQL statement to delete all treatment rows that were done before 2015 and cost less than $500. 2.5) Write an SQL statement to select all Specialties where the specialtytName has a null valuearrow_forward
- Closely examine the following tables and use it to answer the questions that follow: department course student_type student_category Student registration registration_type Suppose that the following are true: - all id column are primary keys in whichever table they are - the dept_id in course table is foreign key that is referencing the id column in the department table - the foreign keys stud_type_id, stud_cat_id in the student table are referencing the id columns in the student_type and student_category tables respectively. - the coursecode and stud_id columns in the registration table are foreign keys in the course and student tables respectively. 1. Design an Entity Relationship Diagram for the system above. 2. Write query that would return the names and contacts of all Local students. 3. Write a query that would return names, contact and locations of all departments that has a location text with Ave 3 as part of it. [hint: use the LIKE clause with “_” or “%”] 4. What would…arrow_forwardConvert the following table to third normal form (3NF). In this table, StudentNum determines StudentName, NumCredits, AdvisorNum, and AdvisorName. Advisor Num determines AdvisorName. CourseNum determines Description. The combination of StudentNum and CourseNum determines Grade. STUDENT (StudentNum, StudentName, NumCredits, AdvisorNum, AdvisorName, CourseNum, Description, Grade). Identify the primary key for the original table and the primary keys for the resulting tables. Indicate the foreign keys with italic font. Give meaning names to the resulting tables.arrow_forwardYou have the following tables: APARTMENTS (ADDRESS, CITY, STATE, RENTER_ID, RENTER_LAST_NAME) RENTERS (RENTER_ID, FIRST_NAME, LAST_NAME) What is the primary key for the APARTMENTS table? (It may be a composite key involving 2 or more fields) What are the foreign keys, if any? What is the primary key for the RENTERS table? What are the foreign keys, if any? What field in RENTERS can be eliminated so that the RENTERS table is normalized?arrow_forward
- Table 1 is the Sales table, which is an unnormalized table. The description of the Sales table are SNUM = salesman number, CNUM = customer number, WNUM = warehouse number, SAMOUNT = sale amount, etc. a.)Write down four functional dependencies (FDs) that are valid in the Sales table b.)Write down the Sales table's normalization result that is in the 2NF, but not in the 3NF.Explain why the table is not in the 3NF.arrow_forwardRewrite the query below to exclude vendors from TN or KY: SELECT VENDOR.V_CODE, VENDOR.V_NAME, VENDOR.V_STATE, PRODUCT.P_CODE, PRODUCT.P_DESCRIPT, [P_PRICE]*[P_QOH] AS TOTALFROM VENDOR INNER JOIN PRODUCT ON VENDOR.V_CODE = PRODUCT.V_CODEWHERE (((VENDOR.V_STATE)="TN")) OR (((VENDOR.V_STATE)="KY"))ORDER BY VENDOR.V_STATE, [P_PRICE]*[P_QOH] DESC;arrow_forward8. The head of the hospital’s accounting department is concerned about the prescribed charge for services versus the actual charges for services in the surgery category of services (service_cat_id = ‘SUR’). Produce a listing of surgery services listed in the treatment table by service_id where the prescribed service_charge differs from the actual_charge recorded in the treatment table. The result table should also include the service_charge and actual-charge. Use a subquery approach to produce a listing of service_id values from the service table that belong to the ‘SUR’ category of services. Display the result table with the service and actual charges formatted to include the dollar sign ($)arrow_forward
- Drop Student Table Refer the following schema and drop Student table. Hint: To drop parent table all associate tables need to be dropped. Student - Course Enrollement Management STUDENT COURSE Number(4) Pipnis FirstName PK PK courseid number(4) Varchar2(20) CourseName varchar2(20) LastName Varchar2(20) Duration number(2) Street Varchar2(20) Fees number(7,2) City Varchar2(20) Date REGISTRATION PK-Primary key FK-Foreign key FK CourselD number(4) FK number(4) O Pipnis Date F5 F10 A 63 70arrow_forwardConsidering Vaccination Database, write queries to: Display the Subject CNIC, Name, Contact, Dose1 Center, and Dose2 Center as shown below: CNIC Name Contact Dose1 Center Dose2 Center 22401-6645321-1 Nasir 3409991112 Lachi Kohat 2 14301-6045321-5 Shahab 3409991112 kohat 3 Dara The subjects who have been vaccinated different types in Dose1 and Dose2. For instance, subjects who were vaccinated ‘Sinovac’ in the first dose, while Pfizer in the second dose.How many Viles of each type have been consumed so far. How many subjects are vaccinated from expired viles. Suggest the name of Incharge for the best performance award based on the highest number of subjects vaccinated on a single dayarrow_forward8. The head of the hospital’s accounting department is concerned about the prescribed charge for services versus the actual charges for services in the surgery category of services (service_cat_id = ‘SUR’). Produce a listing of surgery services listed in the treatment table by service_id where the prescribed service_charge differs from the actual_charge recorded in the treatment table. The result table should also include the service_charge and actual-charge. Use a subquery approach to produce a listing of service_id values from the service table that belong to the ‘SUR’ category of services. Display the SQL code writtenarrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
- A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr