The Resort Real Estate Company (RREC) specializes in the rental, sale, and most importantly the maintenance of exotic time share properties. This means that there are multiple owners (or repeating groups) for the same property. A recent law suit was filed against RREC because properties were being poorly maintained. As a result, RREC developed various maintenance fee charges for each type of property. The legal settlement requires RREC to keep track of the maintenance fees and maintenance fee payments made by owners for each property.
You are given the following data structure and are required to normalize this into third-normal-form. (You are required to explain each step in transforming this data structure).
RREC(Property_ID, Property_name, Address, City, State, Unit_no (Owner_name, Owner_phone (Property_type, Property_features (Maintenance_fee_charged (Maintenance_fee_paid, Paid_amount, Paid_Date, Check_no)))))
Links: Normalization Review, wikipedia
dhvrm
March 13, 2010 at 3:53 pm
Table 1: properties
Property_ID INT PRIMARY KEY AUTOINCREMENT; Property_name VARCHAR(100); Address VARCHAR(100); City VARCHAR(50); State VARCHAR(2); Unit_no VARCHAR(10); Property_type VARCHAR(20); Property_features VARCHAR(2000)
Table 2: owners
Owner_ID INT PRIMARY KEY AUTOINCREMENT; Owner_name VARCHAR(100); Owner_phone VARCHAR(15)
Table 3: maintentanceFees
Fee_ID INT PRIMARY KEY AUTOINCREMENT; Property_ID INT FOREIGN KEY ON properties.Property_ID; Maintenance_fee_charged DECIMAL(10,2);
Table 4: keyPropertiesToOwners
Key_ID INT PRIMARY KEY AUTOINCREMENT; Property_ID INT FOREIGN KEY ON properties.Property_ID; Owner_ID INT FOREIGN KEY ON owners.Owner_ID
Table 5: maintenancePayments
Payment_ID INT PRIMARY KEY AUTOINCREMENT; Fee_ID INT FOREIGN KEY ON maintenanceFees.Fee_ID; Paid_amount DECIMAL(10,2); Paid_Date DATETIME; Check_no VARCHAR(10)
http://www.dougv.com/blog/2007/05/12/the-value-of-relational-databases-a-case-study/