Database keys
-
Hello,
I have troubles with a table normalization assignment for university.
The main problem I have is that I need to set primary keys. I decided that it is reasonable to use Date, Room and EventTime attributes but then I noticed that there is a blank row in EventTime and blank means null, so I cant use it for primary key.
Then I deciced that I could use Date, Room and EventType but this way I cant have a unique match on every record. For example I have same events in the same room on the same date I will get two matches with different EventTime (which I cannot set for primary key because as I said it has a null value).
-
Hi and welcome to the forum.
The EventTime has to be present otherwise the key doesn't hold. The bigger problem here though is the design, i.e. a full day event would require three entries, one for the morning, one for the afternoon and one for the evening.
I strongly suggest you take this design back to the drawingboard.
😎
-
For your clustered index, consider an artificial key - an Integer Identity column. Because you can schedule rooms in the future, they won't be inserted in order by date.
I have to agree with Eirikur on the design. The larger issue is that it doesn't support scenarios that are likely to come up. For example:
1. What if one event needs smoke machine 1, tables and chairs?
2. Do you have a finite number of chairs you can allocate at any one time?
3. I trust you have a finite number of rooms and that the room capacity would be a property of the room, not the event.
4. Please tell me this Excel screen is just a copy of the data pasted into a worksheet. Dates should be stored as dates.
5. Do the event times have a corresponding start time and end time that'll never change? I'd think actual start and end datetime columns would be a better approach if you want to rent rooms for flexible time periods.
There's a lot of duplicated data here. You should probably consider normalizing your structure.
Hope this helps.
-
Hello,
Thank you for your opinions. I have an assigment and I just created a table in excel to fulfill the data. I have to normalize the table from UNF to 3NF step by step.
I cannot have the duration of the events the way they are giving me the data is
Date:
Room,evening, event , contacts , requirements etc.
room,evening and afternoon, event, event, contacs, requirements etc,
Another Date
the same as above
The data in my 1st post has to be in 1NF and the data in this post is in 2NF does this makes more sense to you now ?
-
Soliciting other people to do your homework on an Internet form is cheating. Specifically, academic plagiarism, presenting the work of others as your own. We need to report you to your school.
But besides this little problem, you fail to post DDL.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply