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
Using your knowledge of KimTay Pet Supplies, 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, LOCATION, PRICE,
(INVOICE_NUM, INVOICE_DATE, CUST_ID, FIRST_NAME, LAST_NAME, QUANITITY, QUOTED_PRICE))
Using your knowledge of KimTay Pet Supplies, 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.
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)
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
- 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.arrow_forwardDetermine 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))arrow_forwardUsing the INVOICE table structure shown in Table P6.3, do the following:TABLE P6.3a. Write the relational schema, draw its dependency diagram, and identify alldependencies, including all partial and transitive dependencies. You can assumethat the table does not contain repeating groups and that an invoice numberreferences more than one product. (Hint: This table uses a composite primarykey.)b. Remove all partial dependencies, write the relational schema, and draw thenew dependency diagrams. Identify the normal forms for each table structureyou created.NoteYou can assume that any given product is supplied by a single vendor, but a vendorcan supply many products. Therefore, it is proper to conclude that the followingdependency exists:PROD_NUM → PROD_LABEL, PROD_PRICE, VEND_CODE, VEND_NAME(Hint: Your actions should produce three dependency diagrams.)c. Remove all transitive dependencies, write the relational schema, and draw thenew dependency diagrams. Also identify the normal forms for…arrow_forward
- Convert the diagram into a set of relational tables. The tables must be in 3NF. All referential integrity and functional dependencies must be indicated accurately in EITHER of the following 2 ways: Draw lines for referential integrity and for functional dependencies as belows. 1. Show referential integrity as follows: Supplier(S#, Sname, City, Status); Part(P#, Pname, Color, Weight, City); SP(S#, P#, QTY) Foreign key (S#) References Supplier, Foreign key (P#) References Part; 2. Show functional dependencies as follows: B → C CD → Earrow_forwardConsider the following schema and write the following queries in relational algebra. Product(pid, pname, pcolour, price, supplier, year_manufacture) Select the records where product is 'shoes'. Select the records where product colour is 'red' and 'price' is 450. 3. Select records from Product table where supplier is 'Davidson' and price is 500 or those products manufactured after 2018 . 4.Project the columns named as pid and supplier from the Product table.arrow_forwardNormalize the following table to satisfy BCNF: GRADE(StudentID, StudentName, SectionID, Term, CourseID, CourseName, Grade, ProfessorID, ProfessorName) (Hints: you need to break this large table into 5 small tables.)arrow_forward
- Using your knowledge of the TAL Distributors, determine the functional dependencies and then convert this table to an equivalent collection of tables that are in third normal form. Underline the primary key and italicize any foreign keys. ITEM(ItemNum, Description, OnHand, Category, Storehouse, Price, (OrderNum, OrderDate, CustomerNum, CustomerName, NumOrdered, QuotedPrice)) Using your knowledge of the Colonial Adventure Tours Database, determine the functional dependencies and then convert this table to an equivalent collection of tables that are in third normal form. Underline the primary key and italicize any foreign keys. TRIP(TripID, TripName, StateAbbreviation, StateName, (GuideNum, GuideLast, GuideFirst)) Using your knowledge of the Solmaris Condominium Group Database, determine the functional dependencies and then convert this table to an equivalent collection of tables that are in third normal form. Underline the primary key and italicize any foreign keys.…arrow_forwardWrite at least 3 functional dependency from the tablearrow_forwardThe CUSTOMER table contains a foreign key, REP_NUM, that must match the primary key of the REP table. What type of update(s) to the CUSTOMER table would violate the foreign key constraint?arrow_forward
arrow_back_ios
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