A Guide to SQL
9th Edition
ISBN: 9781111527273
Author: Philip J. Pratt
Publisher: Course Technology Ptr
expand_more
expand_more
format_list_bulleted
Question
Chapter 2, Problem 3SCG
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
The following fields are presented in the table: Member Last Name, MemberFirstName, Street, City, State, ZipCode and MemberFee. The table contains 75,000 documents. How would you build indexes for the table and why would you create these indexes?
Create the Database Schema, the Normalize table, and the Final ERD
StudentID
Name
Age
Course
CourseName
Room
ProfessorID
Professor
1
Michael
|17
СОЕО01
Computer Fundamentals O01
201201
Nath
COEO02
Computer Programming 020
201202
Butch
COEO03
Computer Design
300
201201
Nath
2
Jane
18
СОЕО01
Computer Fundamentals 001
201201
Nath
COEO02
Computer Programming 020
201202
Butch
3
Max
|17
СОЕО01
Computer Fundamentals 001
201201
Nath
COEO03
Computer Design
300
201201
Nath
Step by step procedure of Normalization
access databases
Normalization Exercise
Represent this table as a collection of tables in 3NF:
Student (StudentNumber, StudentName, NumberCredits, AdvisorNumber, AdvisorName, CourseNumber, CourseDescription, 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
Similar questions
- 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.arrow_forwardUse UStayWell Student Accommodation Database to answer the following questions A. Create a LARGE_PROPERTY table with the structure shown in Figure 6-29. B. Insert into the LARGE_PROPERTY table the office number, address, bedrooms, baths, monthlyrent, and owner number for those properties whose square footage is greater than 1,500 squarefeet. C. StayWell has increased the monthly rent of each large property by $150. Update the monthly rentsin the LARGE_PROPERTY table accordingly. D. Delete all properties in the LARGE_PROPERTY table for which the owner number is BI109. E. The property in managed by Columbia City with the address 105 North Illinois Rd is in the processof being remodeled and the number of bedrooms is unknown. Change the bedrooms value in theLARGE_PROPERTY table to null. F. Add to the LARGE_PROPERTY table a new character column named OCCUPIED that is onecharacter in length. (This column indicates whether the property is currently occupied.) Set thevalue for the OCCUPIED…arrow_forwardDatabasearrow_forward
- Design your application's/organization's database. You need to create the 3 tables for each of the services offered. You must provide at least 5 examples in each of the table. There must be a table in which you have prices linked to the services you are providing. THIS IS THE NAME & DESCRIPTION OF THE ORGANIZATION FOR WHICH YOU NEED TO CREATE 3 TABLES FOR EACH SERVICE PROVIDED *Name of the Organization* :: ECOHUB *Description of the Organization* :: EcoHub is a sustainability-focused organisation that seeks to encourage environmentally friendly behaviours, increase public knowledge of environmental problems, and offer solutions for people, organisations, and communities to reduce their carbon footprint and help make the world a greener place. We think that many little individual actions can have a big influence on the environment when taken together. *Services Offered by the Organization are*1. EcoConsultancy : We provide individualised eco-consultancy services to people and…arrow_forward1. Use the following table to answer the questions below: Table name: Products Column Data Type Length Constraint Role Name Stores the ID of a product P ID P_Name Primary key Number Stores the name of a Varchar2 25 product P_entryDate Stores the entry date of a product Date Cannot be null P_Expiry Stores the expiry date of a product Date P_Еxpiry date should be greater than P_entryDate P_Price Stores the price of a product Number 3,2 Cannot be null a. Write a SQL query that displays the mumber of products that will expire in the year 2021 b. Write in SQL a query that displays the ids of the most expensive products c. Create a view named ProductDetails' to hold all the details of products whose names start with 'DELL' and their prices are between 200 and 500 Omani Rialsarrow_forwardCreate a database table about Book Owner, Book Borrower, and also about the Book. Please list the primary key.arrow_forward
- - The HR department needs a report to display employee information as shown from the job_history table. The months worked is the number of months from the start date to the end date. Only list those employees whose months worked is an even number. Order by months worked descendingarrow_forward1. Use the following table to answer the questions below: Table name: Products Column Role Data Type Length Constraint Name Stores the ID of a product Stores the name of a product P_entryDate Stores the entry date of a | product Stores the expiry date of a product P_ID P_Name Number Varchar2 |Primary key 25 Date Cannot be null P_Expiry Date P_Expiry date should be greater than P_entryDate |Cannot be null P_Price | Stores the price of a product | Number 3,2 2. a. Write in SQL a command that creates the table Products according to the above description b. Write in SQL a statement that adds to the table Products a new column named supplierlD of type Number(5). This column should be defined as a foreign key that relates the table Products to the table Supplier c. Write in SQL a query that lists all the details of products arranged in descending order according to the entry date a. Write a SQL query that displays the number of products that will expire in the year 2021| e. Write in SQL a…arrow_forwardbased of the image, write a sql statement that shows the total number of patients handle by each nurse. sort the result based on the nurse's ID and display the nurse's name tooarrow_forward
- The Car Maintenance team also wants to store the actual maintenance operations in the database. The team wants to start with a table to store CAR_ID (CHAR(5)), MAINTENANCE_TYPE_ID (CHAR(5)) and MAINTENANCE_DUE (DATE) date for the operation. Create a new table named MAINTENANCES. The PRIMARY_KEY should be the combination of the three fields. The CAR_ID and MAINTENACNE_TYPE_ID should be foreign keys to their original tables. Cascade update and cascade delete the foreign keys.arrow_forwardData normalisationarrow_forwardUsing the schema below write the DDL code to create the customer and order table in the new database. Be sure to specify the primary key and the foreign key constraints. USE DML code enter 5 records into the customer table and 5 records in the order tablearrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage Learning
Np Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:Cengage
A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher: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
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr