Someone described normalization of a DB design as "a column depends on the key, the whole key, and nothing but the key, so help me Codd." Explain in your words what 3rd normal form is and why it is important.
Each normal form builds upon the one before. Third normal
form introduces non-transitive dependency. In other words, all non-primary
fields should be dependent on the primary key. This plays an important role in
maintaining data integrity. When duplicated data changes, there is a risk of
updating only some of the data, especially if it is spread out in many
different places in the database. We avoid this error by storing data only once
in the proper table, and then referencing it as needed.
If an index will speed up data retrieval, why is it not a good idea to index every column?
If indexes are not properly created, the server may have to
go through more records to retrieve the data requested by a query. This uses
more hardware resources (processor, memory, disk, and network) and takes more time.
A wrong index can be an index created on a column that doesn’t provide easier
data manipulation or an index created on multiple columns which instead of
speeding up queries, slows them down.
What is an SQL view and why is it useful?
A view is a virtual table, defined by a query, that does not
exist until it is invoked by name in an SQL statement. One important use of
views is security. Views can be made accessible to users while the underlying
tables are not. This allows database administrators to give users only the data
they need, while protecting other data in the same table.
No comments:
Post a Comment