Questions tagged [normalization]
Normalization is the process of organizing columns into tables within a relational database in such a way as to minimize redundancy and avoid insertion, update and deletion anomalies.
570
questions
0
votes
0answers
28 views
How to normalize a lookup table containing NULL? - or should I bother?
I have a table fee listing different fee descriptions and another table fee_amount holding the amount of the fee. The fee amount is different for each year, whether it's a joint fee or not and what ...
0
votes
0answers
13 views
Should I create a table replicating a CDN (ex. S3 storage bucket)? [closed]
This question can pertain to any CDN, but I am specifically using AWS' S3 and MySQL.
Currently I have about 50 different tables that have columns referencing media stored in a single S3 bucket.
For ...
0
votes
1answer
29 views
BCNF decomposition step without functional dependencies
I am performing the BCNF decomposition for the relation R = (A, B, C, D, E), that has the set of functional dependencies AB -> C, CD -> A, C -> E, and C -> B.
After checking that the ...
2
votes
0answers
36 views
How to handle / model “cost of provided spare parts per PO line item” where one spare qty is divided between multiple line items? / Postgres 11
Problem
My objective is to help improve the data modeling / handling of my company’s present cost-of-goods [‘COGS’] calculation.
Everything in the current Postgres 11 table structure is pretty ...
0
votes
0answers
37 views
How to normalize a database which uses tables as references to other tables?
I'm trying to create the database for my application, but I cannot manage to normalize my data on a MySQL database.
I have a Types of map entity
The Types must have one or more Models associated to ...
0
votes
1answer
33 views
Database design - logging of same value when recorded in multiple tables
I need to record changes to a particular value, which is read as part of multiple events that are all separately tracked, and be able to view a log of all of the values later.
I think an example will ...
1
vote
0answers
28 views
Schema design for an entity with 'heterogeneous' status types
Consider a notification system which gets some event data and list of business email addresses as input (the list is basically computed by looking at which users/addresses are subscribed to which type ...
0
votes
4answers
85 views
Trouble deciding what/how many tables to use; normalisation suggests one
More of a conceptual question for the community, as I am not "thinking with databases" as much as I would like to just yet. I am using Oracle Database 18c Express Edition with SQL Developer.
I have ...
0
votes
1answer
41 views
Store timeseries data in normal SQL database
I have got historic data of around 20 machine sensors with a time resolution of one second stored in csv-files which need to be imported to a SQL database prior to further data handling and analysis.
...
0
votes
0answers
11 views
What normal form is table with two self-determining attributes?
I have table with these attributes:
employee_id (PK)
[...other non-important attributes...]
user_account_id
There are 2 functional dependecies:
employee_id -> user_account_id
user_account_id -&...
-1
votes
1answer
18 views
Structure to represent matches between teams or individuals
Say there's a sport that can either be contested between two individuals, or two teams comprised of several individuals. So obviously we need the standard players, teams and teams_players tables. But ...
0
votes
0answers
22 views
Normalization of a Database - Is primary key = candidate key?
I'm given a database called Projects where I know that id, pid, sid is the primary key. My goal is to decompose this database such that each sub-relation is in BCNF or 3NF.
P(id,pid,sid,sn,pn,mid,mn)
...
0
votes
1answer
47 views
how to professionally normalize my database - screenshots included
Hello database administrators.
I got some excel spreadsheet with data of "my customers" and I'd like to create professionally designed database out of it.
Can anyone tell me how this data should be ...
-2
votes
1answer
22 views
One to many relationship for constant data
I am saving a book inside a database, so that it can be properly queried. The book I'm working with has constant data. Not a character of it will be changed in the future in any way, once the book has ...
-1
votes
1answer
44 views
Removing duplicates (deduplifying) from an existing, huge, table
I have a huge table. Each row has an ID, some columns which relate to that ID specifically, a short (DNA) Sequence, and some columns which relate to that sequence particularly. If these have not yet ...
0
votes
4answers
272 views
Why is it considered that a set unnormalizes a database absolutely?
In order for a relationship to be on 1NF it needs to have all values as atomic, if there's a set it's not even in that first normal form:
But intuitively I think that a table with that set would be ...
-2
votes
1answer
37 views
I need to put the data from the diagram in 3NF [closed]
This is a simple list table of a company trying to keep track of employee assignment to company projects (in other words, not a database but a flat one table file).
You will design a database for ...
4
votes
0answers
75 views
Are these tables in 3NF? [closed]
Update: I updated the question to be more specific and also updated the diagram.
I am currently in my last semester, I did use SQL before then I left for MongoDB but the practice project requires to ...
0
votes
0answers
25 views
SQLServer: Storing File Extension and normalization?
After much research, I've decided to store pdf/img/txt/doc/etc files on a web server instead of a database. However, the reference to that file will be stored in SQL Server.
So far I have...
| ...
-1
votes
1answer
32 views
3NF for table layout [closed]
Assuming a table with the following columns
UserID, UserName, Console, GameID,
GameTitle, GameVendor, VendorCountry, Game Genre, NumberOfPlayers
Is this the correct 3NF?
Users(UserID,UserName, ...
1
vote
1answer
31 views
Database Model for Various Type of Sensors
I am planning to build a database that will store sensor value for various type of sensors in a factory building. Is there any best practice on how to do this?
-
Option 1 : should I integrate all ...
0
votes
1answer
109 views
Normalization of the this relation just want to know what are the functional dependencies
Consider the Customer account database of the ABC big bank, which keeps data for customers and their orders. A database of only one relation is designed, as shown below:
ABC(
customerNo,
...
2
votes
1answer
62 views
Why is this relation not in Boyce-Codd Normal Form (BCNF)?
I have the relation (A,B,C,D,E).
The relevant functional dependencies are:
{ E,B,C } → { D }
{ A,B } → { C }
{ E,B,D } → { C }
{ E,A,B } → { D }
The candidate key is (E,A,B).
I do not understand ...
1
vote
1answer
66 views
Finding all possible minimal covers
I have a relation schema R = {A, B, C} and the following functional dependencies:
A → B
A → C
B → A
B → C
C → A
C → B
How many different minimal covers can I derive from this relation schema? I have ...
8
votes
6answers
2k views
Does an excessive table violate normalization rules?
Here is an example of some tables that exist in a database where I work. The data isn't actually around schools, but the structure is identical.
There are four tables:
** School **
School Id, School ...
-1
votes
1answer
35 views
How do I bring a relation/table to first normal form (1NF) when one of the attributes/columns can hold any number of values?
For example,
MOVIES (movieid, movietitle)
where movietitle can be any number of words, so I believe it's not atomic.
How do you bring something like that to 1NF?
1
vote
1answer
77 views
Is this table really in first normal form (1NF)?
Does this table comply with 1NF, even if CourseNo and StudentNo?
Student(StudentNo, StudentName, Major, CourseNo, CourseName,
InstructorNo, InstructorName, InstructorLocation, Grade)
5
votes
1answer
72 views
Attempting to identify minimal functional dependencies in a Compact Disc details scenario
I believe this relation/table is in first normal form (1NF):
Collection (cd_id, title, label, artist, type, country, song_id, song_title, length)
Note: Bold means keys.
What I have managed to ...
4
votes
1answer
61 views
Attempt to decompose up to second normal form (2NF) in a scenario about students and courses
1NF:
(student_id, name, start, program, course_code, course_name, faculty, credits, time, grade)
Minimal functional dependencies (identified):
{student_id} → { name, start, program }
{course_code}...
2
votes
1answer
35 views
How can I normalize this schema about students and courses further?
1NF:
I am working on 2NF:
student (student_id, name, start, program)
course (coure_code, course_name, faculty, credits, time)
student_course(student_id, course_code, grade)
I am trying to achieve ...
2
votes
1answer
66 views
Normalization with two potential primary keys
I have the following STUDENT relation in first normal form, where id has been identified as the primary key (email here is also unique for all tuples):
STUDENT(id, email, first_name, last_name)
I ...
2
votes
0answers
40 views
Design issue in transitive dependencies between tables
This is a made-up scenario, but I imagine that it can be of general interest, so I'll post it here. Imagine the following business rule:
A course has a grade scale that determines what grades a ...
4
votes
2answers
81 views
Does a computed column break 3NF (third normal form)?
From my understanding of 3NF, there should not be any transitive dependency.
However, this is what I found from the AdventureWorks2012 database Production.WorkOrder table. Since StockedQty is ...
3
votes
1answer
38 views
Determining minimal cover of a relation
I am having trouble understanding why a certain set of functional dependencies is not minimal.
We have a relation R(A,B,C,D,E,F,G) with the following dependencies F:
1. A->CDE
2. B->FG
3. AB-&...
1
vote
0answers
34 views
verifying non-additive join decomposition
I am reading a DMBA book and have come across non-additive (lossless) join property. The algorithm for decomposition into multiple tables is borderline useless for practical work, so I am frequently ...
2
votes
1answer
356 views
Does using composite keys violate 2NF?
From all of the database related reference books that I have come across, there is a universal rule that state a database table should be at least 3NF.
From what I understand, a table is considered ...
0
votes
1answer
77 views
Is there a database table design to avoid storing table names in a table?
So my scenario demands to store details of businesses which offer varied services. One business can offer n different services. Since each service is different, details pertaining to a service are in ...
1
vote
1answer
51 views
Third Normal Form. Multiple attributes determine another?
Concerning 3NF. If you have a composite non-key determinant to a single non-key dependent, does this violate 3NF? Everything I read on transitive dependencies says one non-key determining another non-...
0
votes
1answer
31 views
Strategies for Database Schema Redesign on Un-Normalized Group of Order Tables in Inventory System
Being my first database-driven and SQL powered program, I'd created this more or less on-the-fly, learning as I go.. trying to be cautious- but more or less created with the intent of simply working.....
0
votes
1answer
49 views
Decompose a relation to 2NF and preserve all the functional dependencies
For example, consider the following relation:
R(A, B, C, D, E)
With the functional dependencies (FDs) shown below:
{A→B, BC→E, D→C}
Here candidate key is {AD} and the relation is not in second ...
1
vote
0answers
141 views
Is a relation with two attributes always in Boyce-Codd Normal Form (BCNF)?
I went through some tutorials on BCNF and got that a relation with two attributes is always in BCNF.
My question
What if a relation with two attributes has no prime or non-prime attributes? That is,...
1
vote
2answers
139 views
Deduplication/normalization of string column on insert
I have a database of text tuples. Imagine e.g. full file paths + standard comments. Then you dump large file tree and generate comments for files. A file can have lots of comments, but their text ...
3
votes
3answers
223 views
Proper normalization for an Orders table--whether to split or no
Each order has a unique SalesOrderNumber. An order is made up of various PartID numbers, each with an associated OrderQuantity number.
I have the following table currently in my model:
I'm ...
3
votes
2answers
147 views
Are there relations that cannot be normalized to second normal form (2NF)?
I came across this example that I can't normalize to 2NF, so I was wondering if relations that could not be normalized existed.
Here is the example relation:
R(A, B, C, D, E)
with the functional ...
0
votes
1answer
33 views
When normalising, if you have entities with the same primary key, does it mean it should be the same entity?
Crrently I have this check in data where each check in has check-in details, activities and total extra items charge(extra items are required for some activities).
In UNF, I separated them into 3 ...
0
votes
3answers
789 views
Database Design for Cars
I'm designing a database to capture car data. Here's a simplified design for capturing year, make, model, and trim:
This should handle data like this:
2017 Ford Mustang GT
However, it turns out ...
-1
votes
1answer
301 views
How to normalize CSV invoice data?
For flat data which comes in with fields like:
name
address
status
class
version
user
mode
CSV files are regularly received, with the above as typical columns. ...
1
vote
1answer
81 views
Normalization: Should I do this in 2NF or 3NF?
Consider a database storing the data of invoices, and each invoice takes the following form:
Invoice_Num: 1480
Handled By: John Keaton
Staff Num: 263
Staff Position: Trainee
Item_Num Item_Desc ...
1
vote
2answers
126 views
Way around Composite Primary Key requiring Composite Foreign Key
Here is a table called Make (where name is the PK)
name|other columns
----------------------
A | ...
B | ...
C | ...
And a table called Type (where make, name together is the PK and make ...
2
votes
0answers
30 views
How to handle normalizing the concept of a grid, and also plan for data versioning?
I have inherited a database that has a normalized structure, except for its data tables which are aggressively denormalized. Our system mostly renders grids into a browser, and the previous developer ...
