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 1CAT
Program Plan Intro
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.
First Normal Form (1NF):
A table or a relation is said to be in first normal form if and only if the domain of each attribute consists of only unique values and the value of each attribute consists of only a single value.
Second Normal Form (2NF):
A table or a relation is said to be in second normal form if and only if it is in first normal form and no non-key attribute of the table is dependent on only a portion of the primary key.
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 the types of entities found in the Colonial Adventure Tours database (trips, guides, clients, and reservations), create an example of a table that is in first normal form but not in second normal form and an example of a table that is in second normal form but not in third normal form. In each case, justify your answers and show how to convert to the higher forms.
Create a table that is in first normal form but not in second normal form using the types of entities found in the Colonial Adventure Tours database (trips, guides, clients, and reservations) and a table that is in second normal form but not in third normal form using the types of entities found in the Colonial Adventure Tours database (trips, guides, clients, and reservations). Justify your replies in each circumstance and show how to transition to higher forms.
Create an example of a table that is in first normal form but not in second normal form using the types of entities found in the Colonial Adventure Tours database (trips, guides, clients, and reservations) and an example of a table that is in second normal form but not in third normal form using the types of entities found in the Colonial Adventure Tours database. Justify your responses in each situation and demonstrate how to convert to the higher forms.
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
- The ______________ model is a simple but highly useful way to organize data into collections of two-dimensional tables called relations.arrow_forwardBased on the picture, create the ERD for this database. Then explain if the tables exhibit referential or entity integrity? Finally, Identify the TRUCK table’s candidate keysarrow_forwardI need you solve this question (Based on the Un-Normalized database table below, create a data model for a Rental Caragency by normalizing them with 2nd and 3rd normal forms.) i need you to complete just like the second image in hand writtenarrow_forward
- Explain the concept of database normalization and provide an example of a table that is not in a normalized form. How would you normalize it to the third normal form (3NF)?arrow_forwardExplain the concept of database normalization and provide an example of a table that is not in the first normal form. How would you bring it to 1NF?arrow_forwardUse the database shown in Figure P3.1 to answer the questions below:A. For each table, identify the primary key and the foreign key(s). If a table doesnot have a foreign key, write None.B. Do the tables exhibit entity integrity? Answer yes or no, and then explainyour answer.C. Do the tables exhibit referential integrity? Answer yes or no, and then explainyour answer. Write NA (Not Applicable) if the table does not have a foreignkey.D. Describe the type(s) of relationship(s) between STORE and REGION.E. Create the ERD to show the relationship between STORE and REGION.F. Create the relational diagram to show the relationship between STORE andREGION.G. Describe the type(s) of relationship(s) between EMPLOYEE and STORE.(Hint: Each store employs many employees, one of whom manages the store.)H. Create the ERD to show the relationships among EMPLOYEE, STORE, andREGION.I. Create the relational diagram to show the relationships among EMPLOYEE,STORE, and REGION.arrow_forward
- What are the advantages and disadvantages of utilising logical views in databases to link tables together, and which should you choose to use? What about ideas that are materialistic, and what are the advantages and disadvantages of holding such views?arrow_forwardExplain the concept of database normalization and denormalization. When is denormalization justified, and what are the trade-offs involved?arrow_forwardUsing 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)arrow_forward
- produce a list of relations indicating the primary key, columns and foreign keys. Consider the following attributes for tables in a relational model designed to keep track of information for a moving company that moves residential customers, usually from one home or apartment to another:customerID, customerName, customerCurrentAddress, customerCurrentPhone, customerNewAddress, customerNewPhone, pickupLocation, dropOffLocation, dateOfMove, startingTime, estimatedWeight, estimatedCost, truck#Assigned, driverName, driverLicNo, actualCost, amountOfDamages, truckCapacity, costOfTolls, tax, finalAmount, invoiceNumber, amountPaid, datePaid, checkNumber, amountDueAssume:- Although in most cases the pickupLocation is the customer’s old address and the dropOffLocation is the new address, there are exceptions, such as when furniture is moved to or from storage.-An estimate is provided before the move using a pre-printed invoice containing a unique invoice number. The actual cost is recorded on…arrow_forwardImplement a new independent entity phone in the Sakila database. Attributes and relationships are shown in the following diagram: The diagram uses Sakila naming conventions. Follow the Sakila conventions for your table and column names: All lower case Underscore separator between root and suffix Foreign keys have the same name as referenced primary key Write CREATE TABLE and ALTER TABLE statements that: Implement the entity as a new phone table. Implement the has relationships as foreign keys in the Sakila customer, staff, and store tables. Remove the existing phone column from the Sakila address table. Step 2 requires adding a foreign key constraint to an existing table. Ex: ALTER TABLE customer ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id) ON DELETE SET NULL ON UPDATE CASCADE; Specify data types as follows: phone_id, phone_number, and country_code have data type INT. phone_type has date type VARCHAR(12) and contains strings like 'Home', 'Mobile', and 'Other'. Apply…arrow_forward
arrow_back_ios
arrow_forward_ios
Recommended textbooks for you
A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
COMPREHENSIVE MICROSOFT OFFICE 365 EXCEComputer ScienceISBN:9780357392676Author:FREUND, StevenPublisher:CENGAGE L
Principles of Information Systems (MindTap Course...Computer ScienceISBN:9781285867168Author:Ralph Stair, George ReynoldsPublisher:Cengage Learning
Principles of Information Systems (MindTap Course...Computer ScienceISBN:9781305971776Author:Ralph Stair, George ReynoldsPublisher:Cengage Learning
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
COMPREHENSIVE MICROSOFT OFFICE 365 EXCE
Computer Science
ISBN:9780357392676
Author:FREUND, Steven
Publisher:CENGAGE L
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781285867168
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781305971776
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning