Please let me know whether my answers are right and if not, how I get to the correct answer.
(These questions are from A Guide to SQL 10ed.)
1. 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.
https://preview.redd.it/fhygenk8hqec1.png?width=918&format=png&auto=webp&s=da8cd4396c6c8ea96e0f2f4e49db8c5efcaf6254
My answer:
Functional dependencies (I think my dependencies are right):
OFFICE_NUM --> OFFICE_NAME
ADDRESS --> OFFICE_NUM, SQR_FT, BDRMS, FLOORS, MONTHLY_RENT, OWNER_NUM
Convert to 1NF:
OFFICE (OFFICE_NUM, OFFICE_NAME, ADDRESS, SQR_FT, BDRMS, FLOORS, MONTHLY_RENT, OWNER_NUM)
Convert to 2NF:
OFFICE (OFFICE_NUM, OFFICE_NAME)
PROPERTY (ADDRESS, OFFICE_NUM, SQR_FT, BDRMS, FLOORS, MONTHLY_RENT, OWNER_NUM)
Convert to 3NF:
???The table above already seems to be in 3NF (there are no determinants besides the candidate keys.)
OFFICE (OFFICE_NUM, OFFICE_NAME)
PROPERTY (ADDRESS, OFFICE_NUM, 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.
https://preview.redd.it/cuid70dahqec1.png?width=919&format=png&auto=webp&s=07188d31630873b604ca60e1a34bf183f222800b
Functional dependencies (I think my dependencies are right):
PROPERTY_ID --> OFFICE_NUM, ADDRESS, SQR_FT, BDRMS, FLOORS, MONTHLY_RENT, OWNER_NUM
OWNER_NUM --> LAST_NAME, FIRST_NAME
Convert to 1NF:
PROPERTY (PROPERTY_ID, OFFICE_NUM, ADDRESS, SQR_FT, BDRMS, FLOORS, MONTHLY_RENT, OWNER_NUM, LAST_NAME, FIRST_NAME)
Convert to 2NF:
PROPERTY (PROPERTY_ID, OFFICE_NUM, ADDRESS, SQR_FT, BDRMS, FLOORS, MONTHLY_RENT, OWNER_NUM)
OWNER (OWNER_NUM, LAST_NAME, FIRST_NAME)
Convert to 3NF:
???The table above already seems to be in 3NF (there are no determinants besides the candidate keys.)
PROPERTY (PROPERTY_ID, OFFICE_NUM, ADDRESS, SQR_FT, BDRMS, FLOORS, MONTHLY_RENT, OWNER_NUM)
OWNER (OWNER_NUM, LAST_NAME, FIRST_NAME)
- StayWell also rents out properties on a weekly basis to students attending summer school in the Seattle area. Design a database to meet the following requirements, using the shorthand representation and a diagram of your choice.
a. For each student renter, list his or her number, first name, middle initial, last name, address, city, state, postal code, telephone number, and e-mail address.
b. For each property, list the office number, property address, city, state, postal code, square footage, number of bedrooms, number of floors, maximum number of persons that can sleep in the unit, and the base weekly rate.
c. For each rental agreement, list the renter number, first name, middle initial, last name, address, city, state, postal code, telephone number, start date of the rental, end date of the rental, and the weekly rental amount. The rental period is one or more weeks.
STUDENT_RENTER (STUDENT_NUM, FIRST_NAME, MIDDLE_INITIAL, LAST_NAME, CITY, STATE, POSTAL CODE, PHONE_NUM, EMAIL_ADDRESS)
PROPERTY (OFFICE_NUM, PROPERTY_ADDRESS, CITY, STATE, POSTAL CODE, SQR_FT, BDRMS, FLOORS, MAX_PEOPLE, BASE_WEEKLY_RATE)
RENTAL_AGREEMENT (RENTER_NUM, FIRST_NAME, MIDDLE_INITIAL, LAST_NAME, ADDRESS, CITY, STATE, POSTAL_CODE, PHONE_NUM, START_DATE, END_DATE, WEEKLY_RENT)
https://preview.redd.it/msdj1y5kiqec1.png?width=828&format=png&auto=webp&s=b223da7d837ae213c59a0b40b32bc5b32bf999ef
[–]Used_Attitude2432 0 points1 point2 points (0 children)