S2 - data storage and database design

database keys

attributes are either primary keys, foreign keys, or descriptive attributes while each attribute in a table must be uniquely named, each row is not inherently uniquely named without a database key *help to identify each record in a table and facilitate the relationship between related tables

transitively dependent columns

attributes that violate 3NF means that the attribute depends on not just the primary key, but another non-key attribute as well

attibutes

columns in a relational database describe the characteristics or properties desired to be know about each entity *must be unique to that table and relevant to the purpose of the table

benefits associated with relational databases

completeness no redundancy (require there to be one version of the truth for each element of data stored in only one place) business rules enforcement communication and integration of business processes

different aspects of database models

conceptual (least complex) logical physical (most complex)

dimension tables

contain descriptive or contextual data for measures; descriptive attributes describe a dimension

fact tables

contain measures or metrics; these facts measure the business *do not contain descriptive elements about the business *do contain foreign keys to the dimension tables

normalization

database technique that reduces data redundancy and eliminates undesirable characteristics like: -insertion -update anomalies -deletion anamolies normalization rules divide larger tables into smaller tables and link them using relationships purpose: eliminate redundant (repetitive) data and reasonably assure data is stored logically

data types

each attribute in a table has a designated data type specify how the data is stored and indicate how the data in a given attribute can be analyzed

first normal form (1NF)

first step to normalizing data is to determine whether the data conforms to the first normal form, which will make sorting and filtering data easier two criteria: 1. only one value may be in a column 2. each record in every table must be uniquely identified (accomplished with a primary key)

conceptual

high-level, big picture representation of the data structures in an information system defines main entities and relationships of the data, without going into the details of the attributes or the physical implementation of the database used to understand the overall structure and meaning of the data *useful for communicating the design of a data model to stakeholders

logical

more detailed representation of the data structures in an information system at the level of the data itself defines the entities and relationships of the data as well as the attributes of each entity *useful for data-oriented projects, such as designing a data warehouse or system development

physical

most detailed representation of data structures compared to conceptual or logical data models; specify how the data will be stored in the database *should be complete enough that the database can be built based on the description in this model

tables

relational databases are made up of at least two tables that are related tables are organization structures that establish columns and rows to store specific types of data records referred to as entities and each table represents an object in the database

records

rows in relational database tables each record contains information about one entity within the table

fields

space created at the intersection of a column and row in a table in which data is entered

popular schemas for data warehouses and data marts

star schema and snowflake schema *both have two types of tables: fact and dimension

data dictionaries

type of metadata - data about data provide and summarize information about the data in a database to: -make it easier to work with data; and -understand how it can be used to inform decisions and build meaningful reports

two key differences between the logical and conceptual models

1. logical model will identify the primary and foreign keys in each entity 2. logical model adjusts any entity-relationship issues related to first normal form or second normal form

second normal form (2NF)

Requires all non-key attributes in a table to depend on the entire primary key *meaningful for tables that have composite primary keys if a table has a composite primary key, every non-key attribute needs to describe each component of the primary key, not just one piece of it

third normal form (3NF)

ascertain that each column in a table describes only the primary key *establish that none of the non-key attributes depend on other non-key attributes