Question

Create an ERD chart that prevents data redundancies and anomalies. The entire primary key must determine the values in the other columns of a table. No non-key column should determine the value of another column.

The data this system must store:

Property lot numbers and their corresponding addresses

Owners’ last names and first names

Owners’ various phone numbers

Owners various email addresses

Owner various mailing addresses and the months when they reside at them

The relationship between owners and properties

Lot number	Property Address	Owner	Mailing a Address 1	Months	Mailing a Address 2	Months	Phone Numbers	Phone 2	Email Addresses
1	1 First St., Tucson, AZ, 85700	Andrew Andover	1 First St., Tucson, AZ, 85700	1 2 3 4 5 6 7 8 9 10 11 12			520-111-1111		[email protected]
2	2 Second St.,Tucson, AZ 85700	Barbara Berthold	2 Second St.,Tucson, AZ 85700	1 2 3 4 5 6 7 8 9 10 11 12			520-222-2222, 520-222-2223		[email protected], [email protected]
3	3 Third St., Tucson, AZ 85700	Carl Clementi & Carol Clementi	3 Third St., Tucson, AZ 85700	1 2 3 4 5 6 7 8 9 10 11 12			520-333-3333 (Carl), 520-333-3334 (Carol)		[email protected], [email protected]
4	4 Fourth St, Tucson, AZ 85700	David Lopez	4 Fourth St, Tucson, AZ 85700	10 11 12 1 2 3 4	4 Faraway Rd., Show Low, AZ 85400	5 6 7 8 9	520-444-4444, 520-444-4445		[email protected]
5	5 Fifth St., Tucson, AZ 85700	Edward Eversharp & Elena Eagleton	5 Fifth St., Tucson, AZ 85700	10 11 12 1 2 3 4	5 Forest Way, Show Low, AZ 85400	5 6 7 8 9	520-555-5555 (land), 520-555-5556 (Edward), 520-555-5557 (Elena)		[email protected], [email protected]

Answer 1

The database system needs to be normalised to remove redundancies and anomalies,

Let’s break the given table in below tables and add FirstName and LastName for the Owner as per the requirement:

• Property(LotNumber, Property Address), where LotNumber is primary key in the table.
• Owner(OwnerID, FirstName,LastName, MailingAddres1,Months, MailingAddress2,Months,Phone Number, Phone 2, EmailAddresses), where OwnerID is newly addred field to remove redundancy in the Owner table
• PropertyOwner(LotNumber,OwnerID),where LotNumber and OwnerID is composite key to store the corresponding Owner’s details for Plot.

Now Consider the table Owner, if we observe, the table is not in 1^st normal form as:

• Multiple EmailAddresses values for single record, which violates 1^st normal form.
• Multiple PhoneNumbers for single record, which violates 1^st normal form.

Break the Owner table to store the EmailAddreses and PhoneNumber in separate tables, Owner table will be changed in this process:

• Owner(OwnerID, FirstName,LastName, MailingAddres1,Months, MailingAddress2,Months)
• OwnerPhone(OwnerID, PhoneNumber), where both the columns are composite key.
• OwnerEmail(OwnerID, EmailAddresses), where both the columns are composite key.

Now consider Address and Months columns in Owner table, an owner can stay at different addresses for different months. Thus these columns need to be handled in separate tables:

• Owner(OwnerID, FirstName, LastName)
• MailingAddress(AddressID, Address), where AddressID is primary key.
• ResidencyMonths(OwnerId, AddressID, StartingMonth, EndingMonth), where (OwnerId, AddressID) is composite key.StartingMonth will store the number when the owner started living in the address, EndingMonth will store the number when the Owner ended living in the address

Thus the table given in the problem is broken into below tables:

• Owner(OwnerID, FirstName, LastName)
• Property(LotNumber, Property Address)
• PropertyOwner(LotNumber,OwnerID)
• MailingAddress(AddressID, Address)
• ResidencyMonths(OwnerId, AddressID, StartingMonth, EndingMonth)
• OwnerPhone(OwnerID, PhoneNumber)
• OwnerEmail(OwnerID, EmailAddresses)

Let’s create the ERD for the above tables:

Relationship in ERD Diagram:

• One property can be owned by multiple owners(One to Many Relationship)
• One owner can have multiple emailAddresses, where email can belong to one owner only (One to Many Relationship).
• Owner can have multiple PhoneNumbers(One to Many relationship).
• Owner can have multiple EmailAddress(One to Many relationship)

ERD Notations:

• Entities are displayed as below:
  • 
• Relationships are displayed as below:

• Attributes are displayed as below:

• Primary key is displayed as below (underlined column name):

• One to Many relationship is displayed as below: