Types of Keys in Data Warehouse Schema
Types of Schema are as following below as follows:
- Star Schema – In figuring, the star schema is the least complex style of information store composition and is the methodology most broadly used to create information distribution centers. The star schema of at least one actuality table referencing any number of dimension tables. The star diagram is a significant uncommon instance of the snowflake composition and is increasingly viable for dealing with less difficult inquiries.
- Snowflake Schema – Not at all like the Star pattern, the dimensions table in a snowflake diagram are standardized. For instance, the thing dimensions table in star construction is standardized and part into two dimensions tables, to be a specific thing and provider table.
- Fact Constellation Schema – The Fact constellation implies at least two reality tables sharing at least one measurement. It is likewise called Galaxy schema. Fact Constellation Schema portrays a consistent structure of a data warehouse. Fact Constellation Schema can structure with an assortment of de-standardized FACT Dimension, Shared Dimensions, and Conformed Dimension tables.
Types of Keys:
- Primary Key – Every row in a dimension table is identified by a unique value which is generally known as primary key. The primary key is a unique identifier that helps isolate each row in a dimension. Tables can have numerous records, however each record has only one Primary Key.
- Surrogate Key – These are the keys which are generated by the system and generally does not have any built in meaning. It is UNIQUE and SEQUENTIAL since it is a consecutively created number for each record in the table. It is MEANINGLESS since it doesn’t have any business significance other than identifying each row. For example, if a data warehouse contains information on 20,000 clients the dimension table will contain 20,000 surrogate keys one for each client.
- Foreign Key – In the fact table the primary key of other dimension table is act as the foreign key.
- Alternate key – It is also a unique value of the table and generally knows as secondary key of the table.
- Composite key – It consists of two or more attributes. For example, the entity has a clientID and a employeeCode as its primary key. Every one of the characteristics that make up the primary key are basic keys on the grounds that each speaks to an exceptional reference while distinguishing a client in one occasion and a employee in the other, so this key is a composite key.
- Candidate key – A substance type in an intelligent information model will have at least zero competitor keys, likewise alluded to just as one of a kind identifiers . For instance, on the off chance that we just interface with American residents, at that point SSN is one up-and-comer key for the Person element type and the mix of name and telephone number (expecting the mix is one of a kind) is possibly a subsequent competitor key. Both of these keys are called up-and-comer keys since they are possibility to be picked as the essential key, a substitute key or maybe not so much as a key at all inside a physical information model.
Share your thoughts in the comments
Please Login to comment...