Search code examples
sqldatasetdatabase-normalizationfunctional-dependenciesbcnf

Trying to convert my relation into BCNF (3.5NF)

I have a dataset of hotel reviews. Each file in the dataset is for a different hotel and contains a number of reviews written by visitors for this hotel. I have been given 5 tasks, list:

1) The relation in my dataset:

HotelReview(int: OverallRating, int: AveragePrice, url: URL, string: Author, string: Content, date: Date, int: No. Reader, int: No. Helpful, int: Overall, int: Value, int: Rooms, int: Location, int: Cleanliness, int: Checkin / front desk, int: Service, int: Business Service)

2) The primary key of my dataset:

Author and URL (**Composite Key**)

3) The Functional Dependencies:

•   Content -> OverallRating, AveragePrice, URL, Author, Date, No. Reader, No. Helpful, Overall, Value, Rooms, Location, Cleanliness, Checkin / front desk, Service, Business Service

•   Author, URL -> Content -> OverallRating, AveragePrice, URL, Content, Date, No. Reader, No. Helpful, Overall, Value, Rooms, Location, Cleanliness, Checkin / front desk, Service, Business Service 

•   Author, Date -> OverallRating, AveragePrice, URL, Author, Date, No. Reader, No. Helpful, Overall, Value, Rooms, Location, Cleanliness, Checkin / front desk, Service, Business Service

4) The potential candidate keys:

Content

But now I am struggling with the fifth task. I have been asked to normalise my relation into BCNF (3.5NF). I have researched how to do this but it just doesn't make sense to me and I have not been able to replicate the normalisation in my own relation. Any help and advice would be greatly appreciated.

Here is an example file from the dataset for a hotel:

<Overall Rating>4
<Avg. Price>$173
<URL>http://...

<Author>everywhereman2
<Content>Old seattle getaway...
<Date>Jan 6, 2009
<img src="http://cdn.tripadvisor.com/img2/new.gif" alt="New"/>
<No. Reader>-1
<No. Helpful>-1
<Overall>5
<Value>5
<Rooms>5
<Location>5
<Cleanliness>5
<Check in / front desk>5
<Service>5
<Business service>5

<Author>RW53
<Content>Location! Location?       view from room of nearby freeway 
<Date>Dec 26, 2008
<No. Reader>-1
<No. Helpful>-1
<Overall>3
<Value>4
<Rooms>3
<Location>2
<Cleanliness>4
<Check in / front desk>3
<Service>-1
<Business service>-1

...new review e.t.c

and here is an example of a review for a hotel in table form:

An example review in table form

The blue tone represents columns that identify the hotel which the review is about whilst the yellow columns represent my composite primary key (Author and

Thanks for your time.

Solution

  • Given your functional dependencies, there are three candidate keys:

    { (Author, Date) (Author, URL) (Content) }

    You can easily verify this if you compute the closure of each of them.

    For this reason the relation is already in Boyce-Codd Normal Form (BCNF), since for each dependency the determinant is a (candidate) key (this is the definition of BCNF).