1
votes

Here is the data I have to normalize:

//

1NF

Customer ID [First Name (PK), Last Name (PK), Phone, Address, Town, Postcode, Email]

Booking [Date (PK), Room (PK), Type, Occupants, Nights, Arrival Time]

ExtraID [Item Name, Item Cost, Date (FK), Room (FK)]

//

First Name + Last Name = Composite Key

Date + Room = Composite Key

//

Is this ok?

Also to go into 2NF I have to identify partial dependencies. As far as I see Phone, Address, Town, Postcode and email requires both parts of the composite key? So is this in 2NF already?

Thank you.

1
Names as a primary key is never a good idea. They are not guaranteed to be unique and sometimes change. - Dan Bracuk
@DanBracuk - if not the names then email seems like my best bet? How about making a composite key consisting of first name, last name and email - is this practical? Thanks. - Barboro37
Email is not also a good primary key because it could change thus to be part of composite key is not therefore advisable. By the way is this a class assignment? - Edper
Ok this is my last idea - making a synthetic key in the Customer_ID table? Yes this is a class assignment, why do you ask? - Barboro37
@Barboro37 I look at your data that you have provided and it includes PostCode. What is that? Is that unique? As for my asking if it is an assignment because some people here in SO is allergic to assignments specially ones wherein a student does not show an effort at all ;-) But so far you have done your part so no worry. - Edper

1 Answers

0
votes

It's generally a good idea to use synthetic keys. This has to do with people, especially---none of the natural keys are truly fit for the primary key purpose (we might go into biometrics here but this would be a bit off topic).

So, there would need to be a table of customers with its own customers_pk primary key which could be either a sequence in the RDBMS or, say, GUID (http://en.wikipedia.org/wiki/Globally_unique_identifier).

There should be a historical table for rooms---the rates tend to change, as well as other room's characteristics. We could define a room to be unique physical object and also decide on whether a room stays the same after remodelling (there are pros and contras to that, it would depend on the business standpoint).

I would create a separate dictionary for the extras (we could use receipt IDs in there and CDR's references) and then link the extras with the bookings via a table with it's own primary key, foreign key to booking's primary key and the foreign key to extra's primary key.

Now, bookings' table should have it's own primary key, then customer's key, rooms' key, dates (they could be date type or we could create a separate time dimension where we could list various useful information such as whether it's high season or if there are some local events), number of occupants, sum of extras charges (well, might be not the best of all ideas) and the grant total.

You could use a separate sequence for each table's keys or just one for all of them---the latter is a bit more elegant.