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 15RQ
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.
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.
Given:
STUDENT (STUDENT_NUM, STUDENT_LAST_NAME, STUDENT_FIRST_NAME, ADVISOR_NUM, ADVISOR_LAST_NAME, ADVISOR_FIRST_NAME, (COURSE CODE, DESCRIPTION， GRADE))
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
You 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?
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)
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))
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
- 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. LOCATION (LOCATION_NUM, LOCATION_NAME, (UNIT_NUM, SQR_FT, BDRMS, BATHS, CONDO_FEE))arrow_forwardCUSTOMER Customer table is composed of customer number, name and phone number. Give and fill-in the appropriate attribute name for each of the column. custID 123 124 125 126 reservelD 5001 5002 5003 5004 5005 5006 5007 RESERVATION Each reservation is for one taxi. Reservation table is composed of reservation identification number, start reservation date, end reservation date, reservation days requested by customers starting from reservation date until end of reservation date, customer number that make the reservation and taxi number assigned to the reservation. Give and fill-in the appropriate attribute name for each of the column. taxill custName Ahmad Bin Abdullah Fatimah Binti Adam LAI LA2 Ruqayya Binti Idris Sulaiman Bin Daud LA3 LA4 startDate 01/10/2019 05/10/2019 05/10/2019 15/10/2019 20/10/2019 27/10/2019 02/11/2019 taxiType endDate 03/10/2019 12/10/2019 08/10/2019 17/10/2019 25/10/2019 Sedan Sedan Van Van 30/10/2019 04/11/2019 cust Phoneno TAXI Taxi table is composed of taxi…arrow_forwardThe 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_forward
- Considering 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_forwardModify the PILOT table to add the attribute shown in the following table, with a precision and scale of (8,2).arrow_forwardThe following tables are used to answer the question MEMBER (memberID (PK), profession, name, address, telNo, areaCode (FK)VOLUNTEER (volunteerID (PK), typeVolunteer, description)MEMBER_VOLUNTEER (memberID (PK), volunteerID (PK), dateJoin, dateDisjoin, allowance) This query is to select the names, address, and telephone number of member, type and description of volunteer, dateJoin, dateDisjoin, and allowance of the volunteer. SELECT m.name, m.address, m.telNo, v.typeVolunteer, v.description, mv.dateJoin, mv.dateDisjoin, mv, allowanceFROM MEMBER m, MEMBER_VOLUNTEER mv, VOLUNTEER vWHERE m.memberID=mv.memberID AND v.volunteerID=mv.volunteerID AND mv.dateJoin > “1/1/2010” AND mv.dateDisjoin < “31/12/2020” AND mv.allowance = NULL AND m.profession = “Teacher” The Question Draw a relational algebra tree (RAT) for the queries and use the heuristic rules totransform the queries into a more efficient form. State each step and any transformation rules used in the process.…arrow_forward
- Subject: DatabaseANSWER THE FOLLOWING QUESTIONS BASED ON THE GIVEN TABLE a) write the DDL statement to create the table CUSTOMER_DETAIL with the primary key constraint. (use most appropriate data type for each attribute). b) Add NOT NULL constraint to attribute of CustomerName c) Add a column named "Country" in the CUSTOMER_DETAIL table d) Rename the table to CUSTOMERarrow_forwardFor this problem create a (temporary) table called instructor_course_nums. Write a procedure that accepts an instructor ID as input. The procedure calculates the total number of course sections taught by that instructor, and adds a tuple to the temporary table consisting of the instructors ID number, name, and total courses taught - call these attributes: ID, name, and tot_courses. If the instructor already has an entry in the table, then the procedure makes sure the total number of courses taught in the temporary table is up-to-date. (Schema pic attached)arrow_forwardFor this problem create a (temporary) table called instructor_course_nums. Write a procedure that accepts an instructor ID as input. The procedure calculates the total number of course sections taught by that instructor, and adds a tuple to the temporary table consisting of the instructors ID number, name, and total courses taught - call these attributes: ID, name, and tot_courses. If the instructor already has an entry in the table,then the procedure makes sure the total number of courses taught in the temporary table is up-to-date. You must name your procedure: Written in PostgreSQLarrow_forward
- Subject: DatabaseWrite the SQL statements for following modification tasks: a) Add constraint to the table BORROWER named borrower_cardno_pk to create the attribute name as the primary key. b) Delete the column city from the table BORROWER c) Modify the column of "phone" in table BORROWER, change the data type into type of VARCHAR2 with length of 20.arrow_forwardUsing the Henry Books database, add the new title from Stephen King to the wrote table: Book code = 3578 Author number = 6 Edition = 1 You will insert 2 snips for this question. What construct did you use to add a row to the wrote table. Insert the snip of the construct used: Insert the snip of the wrote table. Be sure the new row is displayed:arrow_forwarddatabasearrow_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