CLAG’s relation schema with entry for normalization analysis 1 Person(PersonId,
ID: 3712585 • Letter: C
Question
CLAG’s relation schema with entry for normalization analysis
1
Person(PersonId, LName, FName, Phone, Email, Address, City, State, Zip)
Candidate Keys
[1] PersonId
Foreign Keys
Nullable Attributes
Phone, Email, Address, City, State, Zip
Notes
Normalization Analysis
FD:
Highest NF:
2
Owner(OwnerId, SSN, EMail_2, PersonId)
Candidate Keys
[1] OwnerId, [2] PersonId, [3] SSN
Foreign Keys
[1] PersonId references Person(PersonId)
Nullable Attributes
EMail_2
Notes
[1] The surrogate key OwnerId is created as the primary key.
Normalization Analysis
FD:
Highest NF:
3
Artist(ArtistId, Description, ArtistStatement, PersonId)
Candidate Keys
[1] ArtistId, [2] PersonId
Foreign Keys
[1] PersonId references Person(PersonId)
Nullable Attributes
Description may be nullable, depending on the assumption made.
Notes
[1] The surrogate key ArtistId is created as the primary key.
Normalization Analysis
FD:
Highest NF:
4
Customer(CustomerId, Loyalty, Comment, PersonId)
Candidate Keys
[1] CustomerId, [2] PersonId
Foreign Keys
[1] PersonId references Person(PersonId)
Nullable Attributes
Possibly Comment, depending on the assumptions made.
Notes
[1] The surrogate key CustomerId is created as the primary key.
Normalization Analysis
FD:
Highest NF:
5
Style(StyleId, Style, Description)
Candidate Keys
[1] Style, [2] StyleId
Foreign Keys
Nullable Attributes
Description
Notes
[1] The surrogate key StyleId is created as the primary key.
Normalization Analysis
FD:
Highest NF:
6
ArtistStyle(AS_Id, ArtistId, StyleId)
Candidate Keys
[1] AS_Id, [2] ArtistId, StyleId
Foreign Keys
[1] ArtistId references Artist(ArtistId), [2] StyleId references Style(StyleId)
Nullable Attributes
Notes
[1] The surrogate key AS_Id is created as the primary key.
Normalization Analysis
FD:
Highest NF:
7
ArtifactType(AT_Id, ArtifactType, Description)
Candidate Keys
[1] AT_Id, [2] ArtifactType
Foreign Keys
Nullable Attributes
Description
Notes
[1] The surrogate key AT_Id is created as the primary key.
Normalization Analysis
FD:
Highest NF:
8
ArtifactTag(TagId, Tag)
Candidate Keys
[1] TagId, [2] Tag
Foreign Keys
Nullable Attributes
Notes
[1] The surrogate key TagId is created as the primary key.
Normalization Analysis
FD:
Highest NF:
9
SaleType(ST_Id, SaleType, Description)
Candidate Keys
[1] ST_Id, [2] SaleType
Foreign Keys
Nullable Attributes
Description
Notes
[1] The surrogate key ST_Id is created as the primary key.
Normalization Analysis
FD:
Highest NF:
10
Artifact(ArtifactId, Title, Description, ListPrice, BaselinePrice, OwnerId, PrimaryArtistId, SaleTypeId, AT_Id, StyleId)
Candidate Keys
[1] ArtifactId
Foreign Keys
[1] OwnerId references Owner(OwnerId), [2] AT_Id references ArtifactType(AT_Id), [3] ST_Id references SaleType(ST_Id), [4] StyleId references Style(StyleId), [5] PrimaryArtistId references Artist(ArtistId).
Nullable Attributes
Description, OwnerId, PrimaryArtistId,
Notes
[1] The surrogate key ArtifactId is created as the primary key.
Normalization Analysis
FD:
Highest NF:
11
ArtifactTag(ATag_Id, ArtifactId, AT_Id)
Candidate Keys
[1] ATag_Id, [2] ArtifactId, AT_Id
Foreign Keys
[1] ArtifactId references Artifact(ArtifactId), [2] AT_Id references ArtifactTag(AT_Id)
Nullable Attributes
Notes
[1] The surrogate key ATag_Id is created as the primary key.
Normalization Analysis
FD:
Highest NF:
12
SecondaryCreator(SC_Id, ArtifactId, ArtistId)
Candidate Keys
[1] SC_Id, [2] ArtifactId, ArtistId
Foreign Keys
[1] ArtifactId references Artifact(ArtifactId), [2] ArtistId references Artist(ArtistId)
Nullable Attributes
Notes
[1] The surrogate key SC_Id is created as the primary key.
Normalization Analysis
FD:
Highest NF:
13
SaleItem(SaleItemId, SalePrice, Comment, ArtifactId, SaleId)
Candidate Keys
[1] SaleItemId
Foreign Keys
[1] ArtifactId references Artifact(ArtifactId), [2] SaleId references Sale(SaleId)
Nullable Attributes
Comment
Notes
[1] The surrogate key SaleItemId is created as the primary key.
Normalization Analysis
FD:
Highest NF:
14
Sale(SaleId, SaleTime, Comment, TotalPrice, CustomerId)
Candidate Keys
[1] SaleId
Foreign Keys
[1] CustomerId references Customer(CustomerId)
Nullable Attributes
Comment
Notes
[1] The surrogate key SaleId is created as the primary key.
Normalization Analysis
FD:
Highest NF:
1
Person(PersonId, LName, FName, Phone, Email, Address, City, State, Zip)
Candidate Keys
[1] PersonId
Foreign Keys
Nullable Attributes
Phone, Email, Address, City, State, Zip
Notes
Normalization Analysis
FD:
Highest NF:
2
Owner(OwnerId, SSN, EMail_2, PersonId)
Candidate Keys
[1] OwnerId, [2] PersonId, [3] SSN
Foreign Keys
[1] PersonId references Person(PersonId)
Nullable Attributes
EMail_2
Notes
[1] The surrogate key OwnerId is created as the primary key.
Normalization Analysis
FD:
Highest NF:
3
Artist(ArtistId, Description, ArtistStatement, PersonId)
Candidate Keys
[1] ArtistId, [2] PersonId
Foreign Keys
[1] PersonId references Person(PersonId)
Nullable Attributes
Description may be nullable, depending on the assumption made.
Notes
[1] The surrogate key ArtistId is created as the primary key.
Normalization Analysis
FD:
Highest NF:
4
Customer(CustomerId, Loyalty, Comment, PersonId)
Candidate Keys
[1] CustomerId, [2] PersonId
Foreign Keys
[1] PersonId references Person(PersonId)
Nullable Attributes
Possibly Comment, depending on the assumptions made.
Notes
[1] The surrogate key CustomerId is created as the primary key.
Normalization Analysis
FD:
Highest NF:
5
Style(StyleId, Style, Description)
Candidate Keys
[1] Style, [2] StyleId
Foreign Keys
Nullable Attributes
Description
Notes
[1] The surrogate key StyleId is created as the primary key.
Normalization Analysis
FD:
Highest NF:
6
ArtistStyle(AS_Id, ArtistId, StyleId)
Candidate Keys
[1] AS_Id, [2] ArtistId, StyleId
Foreign Keys
[1] ArtistId references Artist(ArtistId), [2] StyleId references Style(StyleId)
Nullable Attributes
Notes
[1] The surrogate key AS_Id is created as the primary key.
Normalization Analysis
FD:
Highest NF:
7
ArtifactType(AT_Id, ArtifactType, Description)
Candidate Keys
[1] AT_Id, [2] ArtifactType
Foreign Keys
Nullable Attributes
Description
Notes
[1] The surrogate key AT_Id is created as the primary key.
Normalization Analysis
FD:
Highest NF:
8
ArtifactTag(TagId, Tag)
Candidate Keys
[1] TagId, [2] Tag
Foreign Keys
Nullable Attributes
Notes
[1] The surrogate key TagId is created as the primary key.
Normalization Analysis
FD:
Highest NF:
9
SaleType(ST_Id, SaleType, Description)
Candidate Keys
[1] ST_Id, [2] SaleType
Foreign Keys
Nullable Attributes
Description
Notes
[1] The surrogate key ST_Id is created as the primary key.
Normalization Analysis
FD:
Highest NF:
10
Artifact(ArtifactId, Title, Description, ListPrice, BaselinePrice, OwnerId, PrimaryArtistId, SaleTypeId, AT_Id, StyleId)
Candidate Keys
[1] ArtifactId
Foreign Keys
[1] OwnerId references Owner(OwnerId), [2] AT_Id references ArtifactType(AT_Id), [3] ST_Id references SaleType(ST_Id), [4] StyleId references Style(StyleId), [5] PrimaryArtistId references Artist(ArtistId).
Nullable Attributes
Description, OwnerId, PrimaryArtistId,
Notes
[1] The surrogate key ArtifactId is created as the primary key.
Normalization Analysis
FD:
Highest NF:
11
ArtifactTag(ATag_Id, ArtifactId, AT_Id)
Candidate Keys
[1] ATag_Id, [2] ArtifactId, AT_Id
Foreign Keys
[1] ArtifactId references Artifact(ArtifactId), [2] AT_Id references ArtifactTag(AT_Id)
Nullable Attributes
Notes
[1] The surrogate key ATag_Id is created as the primary key.
Normalization Analysis
FD:
Highest NF:
12
SecondaryCreator(SC_Id, ArtifactId, ArtistId)
Candidate Keys
[1] SC_Id, [2] ArtifactId, ArtistId
Foreign Keys
[1] ArtifactId references Artifact(ArtifactId), [2] ArtistId references Artist(ArtistId)
Nullable Attributes
Notes
[1] The surrogate key SC_Id is created as the primary key.
Normalization Analysis
FD:
Highest NF:
13
SaleItem(SaleItemId, SalePrice, Comment, ArtifactId, SaleId)
Candidate Keys
[1] SaleItemId
Foreign Keys
[1] ArtifactId references Artifact(ArtifactId), [2] SaleId references Sale(SaleId)
Nullable Attributes
Comment
Notes
[1] The surrogate key SaleItemId is created as the primary key.
Normalization Analysis
FD:
Highest NF:
14
Sale(SaleId, SaleTime, Comment, TotalPrice, CustomerId)
Candidate Keys
[1] SaleId
Foreign Keys
[1] CustomerId references Customer(CustomerId)
Nullable Attributes
Comment
Notes
[1] The surrogate key SaleId is created as the primary key.
Normalization Analysis
FD:
Highest NF:
Explanation / Answer
normalization analysis
1
Person(PersonId, LName, FName, Phone, Email, Address, City, State, Zip)
Candidate Keys
[1] PersonId
Foreign Keys
Nullable Attributes
Phone, Email, Address, City, State, Zip
Notes
Normalization Analysis
FD: PersonId -> LName, FName, Phone, Email, Address, City, State, Zip
Highest NF: 3NF
2
Owner(OwnerId, SSN, EMail_2, PersonId)
Candidate Keys
[1] OwnerId, [2] PersonId, [3] SSN
Foreign Keys
[1] PersonId references Person(PersonId)
Nullable Attributes
EMail_2
Notes
[1] The surrogate key OwnerId is created as the primary key.
Normalization Analysis
FD: SSN-> EMail_2(Transitive dependency)
Highest NF:2NF
3
Artist(ArtistId, Description, ArtistStatement, PersonId)
Candidate Keys
[1] ArtistId, [2] PersonId
Foreign Keys
[1] PersonId references Person(PersonId)
Nullable Attributes
Description may be nullable, depending on the assumption made.
Notes
[1] The surrogate key ArtistId is created as the primary key.
Normalization Analysis
FD: ArtistId-> Description, ArtistStatement
Highest NF:3NF
4
Customer(CustomerId, Loyalty, Comment, PersonId)
Candidate Keys
[1] CustomerId, [2] PersonId
Foreign Keys
[1] PersonId references Person(PersonId)
Nullable Attributes
Possibly Comment, depending on the assumptions made.
Notes
[1] The surrogate key CustomerId is created as the primary key.
Normalization Analysis
FD: CustomerId-> Loyalty, Comment
Highest NF:3NF
5
Style(StyleId, Style, Description)
Candidate Keys
[1] Style, [2] StyleId
Foreign Keys
Nullable Attributes
Description
Notes
[1] The surrogate key StyleId is created as the primary key.
Normalization Analysis
FD:StyleId ->Style, Description
Highest NF:3NF
6
ArtistStyle(AS_Id, ArtistId, StyleId)
Candidate Keys
[1] AS_Id, [2] ArtistId, StyleId
Foreign Keys
[1] ArtistId references Artist(ArtistId), [2] StyleId references Style(StyleId)
Nullable Attributes
Notes
[1] The surrogate key AS_Id is created as the primary key.
Normalization Analysis
FD: AS_Id-> ArtistId, StyleId
Highest NF:3NF
7
ArtifactType(AT_Id, ArtifactType, Description)
Candidate Keys
[1] AT_Id, [2] ArtifactType
Foreign Keys
Nullable Attributes
Description
Notes
[1] The surrogate key AT_Id is created as the primary key.
Normalization Analysis
FD: AT_Id-> ArtifactType, Description
Highest NF:3NF
8
ArtifactTag(TagId, Tag)
Candidate Keys
[1] TagId, [2] Tag
Foreign Keys
Nullable Attributes
Notes
[1] The surrogate key TagId is created as the primary key.
Normalization Analysis
FD: TagId, Tag
Highest NF: 3NF
9
SaleType(ST_Id, SaleType, Description)
Candidate Keys
[1] ST_Id, [2] SaleType
Foreign Keys
Nullable Attributes
Description
Notes
[1] The surrogate key ST_Id is created as the primary key.
Normalization Analysis
FD:ST_Id-> SaleType, Description
Highest NF:3NF
10
Artifact(ArtifactId, Title, Description, ListPrice, BaselinePrice, OwnerId, PrimaryArtistId, SaleTypeId, AT_Id, StyleId)
Candidate Keys
[1] ArtifactId
Foreign Keys
[1] OwnerId references Owner(OwnerId), [2] AT_Id references ArtifactType(AT_Id), [3] ST_Id references SaleType(ST_Id), [4] StyleId references Style(StyleId), [5] PrimaryArtistId references Artist(ArtistId).
Nullable Attributes
Description, OwnerId, PrimaryArtistId,
Notes
[1] The surrogate key ArtifactId is created as the primary key.
Normalization Analysis
FD: ArtifactId-> Title, Description, ListPrice, BaselinePrice, PrimaryArtistId,
Highest NF:3NF
11
ArtifactTag(ATag_Id, ArtifactId, AT_Id)
Candidate Keys
[1] ATag_Id, [2] ArtifactId, AT_Id
Foreign Keys
[1] ArtifactId references Artifact(ArtifactId), [2] AT_Id references ArtifactTag(AT_Id)
Nullable Attributes
Notes
[1] The surrogate key ATag_Id is created as the primary key.
Normalization Analysis
FD:ATag_Id,->ArtifactId, AT_Id
Highest NF:3NF
12
SecondaryCreator(SC_Id, ArtifactId, ArtistId)
Candidate Keys
[1] SC_Id, [2] ArtifactId, ArtistId
Foreign Keys
[1] ArtifactId references Artifact(ArtifactId), [2] ArtistId references Artist(ArtistId)
Nullable Attributes
Notes
[1] The surrogate key SC_Id is created as the primary key.
Normalization Analysis
FD: SC_Id-> ArtifactId, ArtistId
Highest NF:3NF
13
SaleItem(SaleItemId, SalePrice, Comment, ArtifactId, SaleId)
Candidate Keys
[1] SaleItemId
Foreign Keys
[1] ArtifactId references Artifact(ArtifactId), [2] SaleId references Sale(SaleId)
Nullable Attributes
Comment
Notes
[1] The surrogate key SaleItemId is created as the primary key.
Normalization Analysis
FD:SaleItemId-> SalePrice, Comment
Highest NF:3NF
14
Sale(SaleId, SaleTime, Comment, TotalPrice, CustomerId)
Candidate Keys
[1] SaleId
Foreign Keys
[1] CustomerId references Customer(CustomerId)
Nullable Attributes
Comment
Notes
[1] The surrogate key SaleId is created as the primary key.
Normalization Analysis
FD: SaleId,->SaleTime, Comment, TotalPrice
Highest NF:3NF
Do ask if any doubt. Please upvote.
1
Person(PersonId, LName, FName, Phone, Email, Address, City, State, Zip)
Candidate Keys
[1] PersonId
Foreign Keys
Nullable Attributes
Phone, Email, Address, City, State, Zip
Notes
Normalization Analysis
FD: PersonId -> LName, FName, Phone, Email, Address, City, State, Zip
Highest NF: 3NF
2
Owner(OwnerId, SSN, EMail_2, PersonId)
Candidate Keys
[1] OwnerId, [2] PersonId, [3] SSN
Foreign Keys
[1] PersonId references Person(PersonId)
Nullable Attributes
EMail_2
Notes
[1] The surrogate key OwnerId is created as the primary key.
Normalization Analysis
FD: SSN-> EMail_2(Transitive dependency)
Highest NF:2NF
3
Artist(ArtistId, Description, ArtistStatement, PersonId)
Candidate Keys
[1] ArtistId, [2] PersonId
Foreign Keys
[1] PersonId references Person(PersonId)
Nullable Attributes
Description may be nullable, depending on the assumption made.
Notes
[1] The surrogate key ArtistId is created as the primary key.
Normalization Analysis
FD: ArtistId-> Description, ArtistStatement
Highest NF:3NF
4
Customer(CustomerId, Loyalty, Comment, PersonId)
Candidate Keys
[1] CustomerId, [2] PersonId
Foreign Keys
[1] PersonId references Person(PersonId)
Nullable Attributes
Possibly Comment, depending on the assumptions made.
Notes
[1] The surrogate key CustomerId is created as the primary key.
Normalization Analysis
FD: CustomerId-> Loyalty, Comment
Highest NF:3NF
5
Style(StyleId, Style, Description)
Candidate Keys
[1] Style, [2] StyleId
Foreign Keys
Nullable Attributes
Description
Notes
[1] The surrogate key StyleId is created as the primary key.
Normalization Analysis
FD:StyleId ->Style, Description
Highest NF:3NF
6
ArtistStyle(AS_Id, ArtistId, StyleId)
Candidate Keys
[1] AS_Id, [2] ArtistId, StyleId
Foreign Keys
[1] ArtistId references Artist(ArtistId), [2] StyleId references Style(StyleId)
Nullable Attributes
Notes
[1] The surrogate key AS_Id is created as the primary key.
Normalization Analysis
FD: AS_Id-> ArtistId, StyleId
Highest NF:3NF
7
ArtifactType(AT_Id, ArtifactType, Description)
Candidate Keys
[1] AT_Id, [2] ArtifactType
Foreign Keys
Nullable Attributes
Description
Notes
[1] The surrogate key AT_Id is created as the primary key.
Normalization Analysis
FD: AT_Id-> ArtifactType, Description
Highest NF:3NF
8
ArtifactTag(TagId, Tag)
Candidate Keys
[1] TagId, [2] Tag
Foreign Keys
Nullable Attributes
Notes
[1] The surrogate key TagId is created as the primary key.
Normalization Analysis
FD: TagId, Tag
Highest NF: 3NF
9
SaleType(ST_Id, SaleType, Description)
Candidate Keys
[1] ST_Id, [2] SaleType
Foreign Keys
Nullable Attributes
Description
Notes
[1] The surrogate key ST_Id is created as the primary key.
Normalization Analysis
FD:ST_Id-> SaleType, Description
Highest NF:3NF
10
Artifact(ArtifactId, Title, Description, ListPrice, BaselinePrice, OwnerId, PrimaryArtistId, SaleTypeId, AT_Id, StyleId)
Candidate Keys
[1] ArtifactId
Foreign Keys
[1] OwnerId references Owner(OwnerId), [2] AT_Id references ArtifactType(AT_Id), [3] ST_Id references SaleType(ST_Id), [4] StyleId references Style(StyleId), [5] PrimaryArtistId references Artist(ArtistId).
Nullable Attributes
Description, OwnerId, PrimaryArtistId,
Notes
[1] The surrogate key ArtifactId is created as the primary key.
Normalization Analysis
FD: ArtifactId-> Title, Description, ListPrice, BaselinePrice, PrimaryArtistId,
Highest NF:3NF
11
ArtifactTag(ATag_Id, ArtifactId, AT_Id)
Candidate Keys
[1] ATag_Id, [2] ArtifactId, AT_Id
Foreign Keys
[1] ArtifactId references Artifact(ArtifactId), [2] AT_Id references ArtifactTag(AT_Id)
Nullable Attributes
Notes
[1] The surrogate key ATag_Id is created as the primary key.
Normalization Analysis
FD:ATag_Id,->ArtifactId, AT_Id
Highest NF:3NF
12
SecondaryCreator(SC_Id, ArtifactId, ArtistId)
Candidate Keys
[1] SC_Id, [2] ArtifactId, ArtistId
Foreign Keys
[1] ArtifactId references Artifact(ArtifactId), [2] ArtistId references Artist(ArtistId)
Nullable Attributes
Notes
[1] The surrogate key SC_Id is created as the primary key.
Normalization Analysis
FD: SC_Id-> ArtifactId, ArtistId
Highest NF:3NF
13
SaleItem(SaleItemId, SalePrice, Comment, ArtifactId, SaleId)
Candidate Keys
[1] SaleItemId
Foreign Keys
[1] ArtifactId references Artifact(ArtifactId), [2] SaleId references Sale(SaleId)
Nullable Attributes
Comment
Notes
[1] The surrogate key SaleItemId is created as the primary key.
Normalization Analysis
FD:SaleItemId-> SalePrice, Comment
Highest NF:3NF
14
Sale(SaleId, SaleTime, Comment, TotalPrice, CustomerId)
Candidate Keys
[1] SaleId
Foreign Keys
[1] CustomerId references Customer(CustomerId)
Nullable Attributes
Comment
Notes
[1] The surrogate key SaleId is created as the primary key.
Normalization Analysis
FD: SaleId,->SaleTime, Comment, TotalPrice
Highest NF:3NF