Tech Interview Questions site organizes over 1000 tech interview questions from multiple job functions. All these are in an easy question and answer format.
Data Model
Data Model
Relational model
In Relational model data is normalized to 1st, 2nd or 3rd Normal form.
1s Normal form
1NF, each column must contain only a single value and each row must contain the same columns.
2nd Normal form
A relation must first fulfill the requirements to be in First Normal Form. Additionally, each nonkey attribute in the relation must be functionally dependent upon the primary key.
br>2NF attempts to reduce the amount of redundant data in a table by extracting it, placing it in new table(s) and creating relationships between those tables.
3rd Normal form
A relation must first fulfill the requirements to be in Second Normal Form. Additionally, all attributes that are not dependent upon the primary key must be eliminated
An example of a 2NF table that fails to meet the requirements of 3NF is:
| Tournament | Year | Winner | Winner Date of Birth |
|---|---|---|---|
| Indiana Invitational | 1998 | Al Fredrickson | 21 July 1975 |
| Cleveland Open | 1999 | Bob Albertson | 28 September 1968 |
| Des Moines Masters | 1999 | Al Fredrickson | 21 July 1975 |
| Indiana Invitational | 1999 | Chip Masterson | 14 March 1977 |
In order to express the same facts without violating 3NF, it is necessary to split the table into two:
|
| Player | Date of Birth |
|---|---|
| Chip Masterson | 14 March 1977 |
| Al Fredrickson | 21 July 1975 |
| Bob Albertson | 28 September 1968 |
Dimensional model
In dimensional model data is denormalized. The typical design is that of a Star where their is a central fact table containing additive, measurable facts and this central fact table is in relationshipB with dimensional tables which generally contain text filters that normally occurs in the WHERE clause of a query.
The dimensional model is often implemented on top of the relational model using a star schema, consisting of one table containing the facts and surrounding tables containing the dimensions.
The Star schema consists of a few "fact tables" (possibly only one, justifying the name) referencing any number of "dimension tables".the star schema's dimensions are denormalized with each dimension being represented by a single table
The snowflake schema is represented by centralized fact tables which are connected to multiple dimensions. In the snowflake schema, however, dimensions are normalized into multiple related tables When the dimensions of a snowflake schema are elaborate, having multiple levels of relationships, and where child tables have multiple parent tables.
what are the advantages and disadvantages of a star schema and snowflake schema.
In Star schema
In Star schema fact table is in normalized format and dimention table is in denormalized format
keeping queries simple and providing fast response time
In snowflake schema
In snowsflake both fact and dimention tables are in normalized format
will reduse the query performance because more joins is there
savings small storage spaces
Partners
News & Event