I am using Oracle Database 18c Express Edition with SQL Developer.
I have been asked to build a database to log sales in a shop:
- Date
- Day
- Time
- Value [either Low, Medium, or High]
- Location [on a (-5,-5) to (5,5) grid]
If we throw in an incremental UID as the PK (primary key) for each sale, the one table does this. I would need one entity, Sale, and it would consist of six attributes (the UID and the list above). It would have a PK, no repeating groups, no composite PK, and no non-key fields dependent on another non-key field.
Queries (which might influence the design):
- Find quietest day
- Find busiest hour
- Find highest income location
- Generate a report on sales by value range
- Generate a follow-on report of purchases by hour in each value range
- Identify locations with no income
There are constraints (shop isn't open Sundays, only open between hours x and y, escalator to get to the floor is at (0,0), etc).
Normalisation will do most of the database design, making new tables to separate attributes into their own entities. The task specifies 3NF (and says there needs to be no normalising, confusingly). I am reading "Systems Analysis: A Beginner’s Guide" by Kevin Bowman as my main reference, and the data is already in 3NF, and doesn't need more than the one table.
This seems too simple, and I suspect I am not getting the process. A colleague plans on a table for each attribute, which seems mad and unnecessary.
What are the issues with my reasoning?