Question

In: Computer Science

Create an ERD chart that prevents data redundancies and anomalies. The entire primary key must determine...

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]

Expert Solution

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:

venereology answered 1 year ago

You must create an ERD diagram for medical office wait times for patients with the following...

You must create an ERD diagram for medical office wait times for patients with the following minimum requirements. Your ERD diagram must include your Business Rules with a min. of 10 rules At least 5 entities. These may be real (e.g. a person, vehicle, etc.) or abstract concepts (e.g. course, enrollment, categories, etc.) • There must be logical relationships between the entities. • Aim for all entities to have a minimum of seven attributes, but no fewer than 3 entities...

create table node( node_id integer primary key, node_color varchar(10)); create table edge( edge_id integer primary key,...

create table node( node_id integer primary key, node_color varchar(10)); create table edge( edge_id integer primary key, origin_id integer, destination_id integer, foreign key (origin_id) references node(node_id), foreign key (destination_id) references node(node_id)); write an SQL query that lists all those nodes that have edges with a destination node that has color 'red'.

Create table, create primary and foreign key constraints. Create index on the table to satisfy a...

Create table, create primary and foreign key constraints. Create index on the table to satisfy a query with aggregate functions.

Using the data shown, you will create two charts. The first chart is a bar chart...

Using the data shown, you will create two charts. The first chart is a bar chart that will display % growth in different job titles between 2010 and 2020Est. The second chart will be a pie chart showing the 2020Est. Jobs in different categories as a % of overall 2020Est. jobs. Computer-Related Jobs 2010 2020 Est. % Change Systems Analysts 544,400 664,800 Software App Developers 520,800 664,500 Programmers 363,100 406,800 Network/System Admins 347,200...

1. Create one XML data file and one DTD file for the entire data set (using...

1. Create one XML data file and one DTD file for the entire data set (using a subset of SQL assignment – see below). [Use of ID and IDREF are not required.] 2. Write and execute XML statements for the following two queries: Q1. Find the name of an employee who lives in Lincoln and works in Omaha. Q2. Find salaries of employees who live in the same cities as the companies for which they work. [You can replace one...

1. Use SQL to create a polyinstantiated table including a primary key and a unique constraint...

1. Use SQL to create a polyinstantiated table including a primary key and a unique constraint 2.Use SQL to insert multiple records for each security classification with the same ID. You must have 4 classifications. 3.Use SQL to create 4 schemas, one for each security classification 4.Use SQL to create a view in each schema that restricts the records to those belonging to a particular security classification and restricts the columns to only those columns that have relevant data. 5.Select...

The first step in selecting a Shewhart chart is to determine the type of data you...

The first step in selecting a Shewhart chart is to determine the type of data you are collecting. For this discussion, identify and discuss the 2 types of data that can be used with this type of chart. What are some examples of each type? Be sure to discuss the subdivision of each type. Based upon your response to these questions, why is this step so important? What would happen if you skipped it?

create a chart in Word that categorizes Wave’s key accounting features. Next, write one paragraph, within...

create a chart in Word that categorizes Wave’s key accounting features. Next, write one paragraph, within the same document, explaining whether Wave would be an efficient software for your restaurant business.”

Use the data in the following table to create a fraction nonconforming (p) chart. The column...

Use the data in the following table to create a fraction nonconforming (p) chart. The column of np represents the number of non-conforming units. Is the process in control? (5 points) Sample n np p 1 100 7 0.07 2 100 10 0.10 3 100 12 0.12 4 100 4 0.04 5 100 9 0.09 6 100 11 0.11 7 100 10 0.10 8 100 18 0.18 9 100 13 0.13 10 100 21 0.21 Question 2 A bank's manager...

Consider the following table definitions create table node( node_id integer primary key, node_color varchar(10)); create table...

Consider the following table definitions create table node( node_id integer primary key, node_color varchar(10)); create table edge( edge_id integer primary key, origin_id integer, destination_id integer, foreign key (origin_id) references node(node_id), foreign key (destination_id) references node(node_id)); What is the result of the following query? select node_id, node_color, destination_id from node, edge; An inner join of the tables node and edge that lists origin node_id and node_color together with the node_id of the destination node for all those nodes that have outgoing...