I have to design a conceptual database model up to 3rd normal form based on this problem, i think i have identified the main entities in 1nf as listed below however i am a little confused as to where to go next. Should national and international flights have their own table?? If so in what form?? Should Completed and future flights have their own table and if so in what form? I have seen many examples and all make sense even though they are generally different, so i am very confused and any help identifying the entities and attributes in 3nf and below would be very helpful because then i can get on with the SQL coding! I just cant get my head around this bit.

Here is a summary of the problem....


What i have so far:

Flight (Flight Number, Date, Day, Departure Time, Arrival Time, Departure Airport, Destination Airport)

Airport (Airport ID, City, Country, Number of runways)

Aircraft (Aircraft ID, National/International, Type, Number of Seats, Engine Size, Flight Make)

march 1st 2003 was a saturday

so even before the flight gets out of the gate, you have a data integrity problem

haha obviously the things in brackets are examples i didnt go and look at the date to find out

I'd suggest that you either attend one of the class discussion sessions, or that you stop and talk with the teacher or TA. Those are really your best choices for this kind of problem.

We generally can help with technical questions (I want to do X, and here's what I've tried, but I keep getting error message Y), but it is practically impossible to help with design issues like you are facing via postings. You really need to get some hands-on, one-on-one help to work through these issues, and I don't know how to give that in the posting environment.

Aside from the practical issues of my not knowing how to really help you with this problem here, it may also help the TA or teacher if they work through things with you. Lots of times they see things from a different perspective while helping people, and may realize that they've missed some important topic in class.

-PatP

That sounds like your off to a good start, are you after it in 3NF?? I think you have the entities right

yes, the entities are right, but having both date and day violates one of the normal forms (can't remember which) because day depends on date and not on flight number

and according to theory, if city is unique (an iffy proposition unless city is a foreign key) then country depends on city, and not on airport id

so do you think i should create a separate table with date and day in as date would be unique and therefore a primary key??

absolutely not

just use the appropriate date function

you wouldn't create this table, would you? --

create table sums
( one integer
, two integer
, oneplustwo integer
)
insert into sums values ( 1, 1, 2 )
insert into sums values ( 2, 2, 4 )
insert into sums values ( 4, 2, 6 )
insert into sums values ( 6, 3, 9 )
...

Don't forget the foreign-key referencing the NUMBERS table this will insure that all of the entries are valid numbers.

omg wtf LOL

another brilliant gem!

urquel++

[thud]
ooouch...It's not often I get knocked off my BarStoo....ummm Office chair

[/thud]

While you're at it. Make sure you have a valid alpha numeric table....make dure to include ASCII Values (ie SELECT ASCII('a')) Don't forget to include case sensitivity...then make sure you check your strings byte by byte. A user defined function would help here.

Or go google

http://databases.about.com/od/specif...malization.htm

To take it a step further. The BYTE table would have a foreign key referencing the BIT table. This would keep those pesky 2's out of the system.

And make sure every table has a clustered unique primary key IDENTITY Column.

Make sure they are all called [id]..saves on typing

helpful....

especially the state codes

you may think you can get away with TX as the natural primary key for Texas, but don't make that mistake

actually, you should be using a GUID (globally unique identifier)

thus, an address row would look like

123 Cowpatty Lane, Austin, {3F2504E0-4F89-11D3-9A0C-0305E82C3301}

and then {3F2504E0-4F89-11D3-9A0C-0305E82C3301} would be the foreign key link to TX in the state table

you know, in case Texas ever changes its state code TX



the part that's always confused me about GUIDs, however, is this: how does sql server know that it's TX i'm generating the GUID key for?

i mean, if it's supposed to be, you know, a globally unique identifier, then for crying out loud, TX had better be the same in everybody's database on the globe, otherwise how could you ever communicate an address from one computer to another?????????????????