This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What are the different main type of normalization?
In database design, we start with one single table, with all possible columns. A lot of redundant data would be present since it’s a single table. The process of removing the redundant data, by splitting up the table in a well defined fashion is called normalization.
First Normal Form (1NF):A relation is said to be in first normal form if and only if all underlying domains contain atomic values only. After 1NF, we can still have redundant data.
Second Normal Form (2NF):A relation is said to be in 2NF if and only if it is in 1NF and every non key attribute is fully dependent on the primary key. After 2NF, we can still have redundant data.
Third Normal Form (3NF):A relation is said to be in 3NF, if and only if it is in 2NF and every non key attribute is non-transitively dependent on the primary key.
What is the Primary key?
A primary key is a one or more fields that uniquely identifies a row in a table. The primary key cannot be null (blank). The primary key is indexed.
A primary key is a value that can be used to identify a unique row in a table.
What should be your best practice with Primary Key?
How to find all the Tables without having a Primary Key?
SQL Server: Script to find all the Tables without having a Primary Key
What is the Unique key?
Unique key also ensures data uniqueness like primary key.
A column with unique key defined allows null value. By default, it creates non-clustered index.
What is the Super key?
A superkey is defined in the relational model as a set of attributes of a relation variable (relvar) for which it holds that in all relations assigned to that variable there are no two distinct tuples (rows) that have the same values for the attributes in this set. Equivalently a superkey can also be defined as a set of attributes of a relvar upon which all attributes of the relvar are functionally dependent.
What is the Foreign key?
A foreign key is a relationship between columns in two database tables (one of which is indexed) designed to insure consistency of data.
Usually a foreign key in one table refers to the primary key (PK) of another table. This way references can be made to link information together and it is an essential part of database normalization.
How to Find Foreign key References in SQL Server?
How you can find source and destination information of all foreign keys?
PostgreSQL: Script to find Source and Destination of All Foreign Key Constraint
What is the Composite key?
A composite key consists of more than one field to uniquely identify a record. This differs from a compound key in that one or more of the attributes, which make up the key, are not simple keys in their own right. Taking the example from compound key, imagine we identified a student by their firstName + lastName.
In our table representing students on modules our primary key would now be firstName + lastName + moduleCode. Because firstName + lastName represent a unique reference to a student, they are not each simple keys, they have to be combined in order to uniquely identify the student. Therefore the key for this table is a composite key.
What is the Natural key?
A composite primary key which is composed of attributes (fields) which already exist in the real world e.g. First Name, Last Name, Social Security Number.
What is the Surrogate key?
A primary key which is internally generated (typically auto-incremental integer value) that does not exist in the real world i.e. ID=1 for Customer A and ID=2 for Customer B serves to uniquely identify the record but has no bearing the customer themselves and is an attribute they will never (need to) be aware of.
What is the Candidate key?
A candidate key is a column or group of columns that can uniquely identify a row in the table without referring to any other source.
What is the Compound key?
A composite key consisting of two or more fields that uniquely describe a row in a table. The difference between compound and candidate is that all of the fields in the compound key are foreign keys; in the candidate key one or more of the fields may be foreign keys.
Can a foreign key reference a non-primary key?
Yes, a foreign key can actually reference a key that is not the primary key of a table. But, a foreign key must reference a unique key.
Can a foreign key contain null values?
Yes, a foreign key can hold NULL values. Because foreign keys can reference unique, non-primary keys – which can hold NULL values – this means that foreign keys can themselves hold NULL values as well.
What are the type of data integrity?
- Domain Integrity: data type, check constraints
- Entity Integrity: primary key, unique constraint
- Referential Integrity: foreign key constraint
