Create a web application for recording persons and contacts. The main goal of the application is to record persons (friends, acquaintances), their addresses, relationships and meetings. Every person can have a name, nickname, age, location and contacts. Each person can have any number of contacts (mobile, Skype, Jabber, ….). A person can have more contacts of the a type (e.g. two emails). Each person can have any number of relationships with other persons in the database. Each relationship should have type (friend, fiend, acquaintance, spouse, …) and description. Contact and relationship types are recorded in the database and can be modified by the end-user…
TENANCY(LANDLORD, TENANT, BEGIN_DATE, END_DATE, RATE)
person
– location
:
person
table:
PERSON(ID_PERSON, FIRST_NAME, NICKNAME, CITY, STREET)
location
table and link it to person
:
PERSON(ID_PERSON, FIRST_NAME, NICKNAME, ID_LOCATION)
LOCATION(ID_LOCATION, CITY, STREET)
person
– location
:
PERSON(ID_PERSON, FIRST_NAME, NICKNAME)
LOCATION(ID_LOCATION, CITY, STREET)
RESIDENCE(ID_PERSON, ID_LOCATION, BEGIN_DATE)
1. Person Name | 2. Person Name | 1. Person Address | 2. Person Address | Begin Date |
---|---|---|---|---|
Karl Oshiro | Marcel Miranda | Mozartstraße 33, Linz | Fountain Rd 27, Stirling | 2016-01-06 |
Remona Deen | Karl Oshiro | Old Rd 182, Muir of Ord | Mozartstraße 33, Linz | 2015-12-20 |
Tuan Brauer | Marcel Miranda | Davenport St., 12, Bolton | Fountain Rd 27, Stirling | 2015-10-09 |
relation
is not in 1st NF:
Name1 | SName1 | SSN1 | Name2 | SName2 | SSN2 | Begin Date |
---|---|---|---|---|---|---|
Karl | Oshiro | 123 | Marcel | Miranda | 987 | 2016-01-06 |
Remona | Deen | 456 | Karl | Oshiro | 123 | 2015-12-20 |
Tuan | Brauer | 789 | Marcel | Miranda | 987 | 2015-10-09 |
relation
is in 1st NF and is not in 2nd NF:
SSN1
and SSN2
attributes.Begin date
depends on the both attributes of the key.Name1
and SName1
depends only on SSN1
– therefore they
depend only on a part of the key.RELATION | PERSON | |||||
---|---|---|---|---|---|---|
SSN1 | SSN2 | Begin_date | SSN | Name | SName | |
123 | 987 | 2016-01-06 | 123 | Karl | Oshiro | |
456 | 123 | 2015-12-20 | 456 | Remona | Deen | |
789 | 987 | 2015-10-09 | 789 | Tuan | Brauer | |
987 | Marcel | Miranda |
relation
and person
is in 2nd NF (and therefore in 1st NF):
ID | Street | No. | City | ZIP |
---|---|---|---|---|
1 | Mozartstraße | 33 | Linz | 4020 |
2 | Old Rd | 182 | Muir of Ord | IV6 7UJ |
3 | Davenport Street | 12 | Bolton | BL1 2LT |
address
table is not in 3rd NF:
zip
attribute depends on the city
attribute,city
will trigger a change in zip
,zip
transitively depends on id
.Address | ZIP Codes | |||||
---|---|---|---|---|---|---|
ID | Street | No. | ZIP | ZIP | City | |
1 | Mozartstraße | 33 | 4020 | 4020 | Linz | |
2 | Old Rd | 182 | IV6 7UJ | IV6 7UJ | Muir of Ord | |
3 | Davenport Street | 12 | BL1 2LT | BL1 2LT | Bolton |
address
and ZIP codes
tables is in 3rd NF:
ZIP code
, removed redundancy of data.