[Link]

htm

Database is a collection of records stored on some type of media

Entity: An entity is any object in the system that we want to model and store information
about

Entity occurrence is an instance/occurence of an entity aka record. Note - the convention is to

use singular names when identifying entities

Attribute: An item of information which is stored about an entity.

A relationship is an association (link) of entities that is established by a foreign key in one entity
linking to the primary key in another.
degree of relationship (aka cardinality) is the number of occurrences in one entity which
are associated (or linked) to the number of occurrences in another.

 One-to-one is where one occurrence of an entity relates to only one occurrence in

another entity
 One-to-many relationship is where one occurrence in an entity relates to many
occurrences in another entity.
 Many-to-many relationship: many occurrences in an entity relate to many
occurrences in another entity.

Key is a data item that allows us to uniquely identify individual occurrences or an entity type

Types of key fields:

 Primary key: Consists of 1 or more attributes that distinguishes a specific record

from any other. It’s a column in a relational database table whose value must be
unique for each row and used to uniquely identify that row.
 Secondary keys: Candidate key that is not chosen as the primary key. You select a
primary key from your candidate keys; those not selected are called secondary keys.
 Foreign key: 1 or more attributes in an entity that enables a relationship to another entity.
It’s a column in a table that is a primary key in another table.
 A simple key consists of a single attribute to uniquely identify an entity occurrence.
 Compound key: is a unique key u create by combining 2 or more columns.
 Composite key consists of more than one attribute to uniquely identify an entity
occurrence. 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.n
Candidate key: Any column that could be used as the primary key.
Surrogate Key: It’s a column you create to be the record’s primary key identifier. It has no
relationship with the row it is assigned other than to identify the row uniquely. They have no
semantic meaning; there is no way check their validity. USED IN OLAP e.g. in slowly
changing dimensions. Advantages
[Link]

 Immutability
Surrogate keys do not change while the row exists. This has the following
advantages:
o 1 to a row in the database (since the identifier never changes).
 Requirement changes
o Attributes that uniquely identify an entity might change, which might invalidate the
suitability of the natural, compound keys.
 Slowly changing dimensions.
 Performance
o Surrogate keys tend to be a compact data type, such as a four-byte integer. This
allows the database to query the single key column faster than it could multiple
columns
o Surrogate keys are also less expensive to join (fewer columns to compare)
thancompound keys.
 Uniformity
o When every table has a uniform surrogate key, some tasks can be easily automated by
writing the code in a table-independent way.
 Mapping to integrate disparate sources: The same entity may be assigned different natural
keys by different source system. These disparate(different in kind) natural keys can be
matched and the associated with a neutral surrogate key.
 Natural key may fail to adhere to the fundamental properties of a key e.g. uniqueness, not null
ability etc.) and natural key may not be standardized in terms of sizes and data types with
other keys of your data warehouse
Disadvantages
 Disassociation: The values of generated surrogate keys have no relationship to the real-
world meaning of the data held in a row. When inspecting a row holding a foreign key
reference to another table using a surrogate key, the meaning of the surrogate key's row
cannot be discerned from the key itself. Every foreign key must be joined to see the
related data item. This can also make auditing more difficult
 Query optimization: the index is on the surrogate key but most searches are on the
table’s previous key. Remedy is to create an index for the candidate key but this will take
up more disk space.
 The field does not exist hence extra space is needed.

Natural key/business key is a candidate key that has a logical relationship to the attributes
within that row.
The main advantage of a natural key over a surrogate key, which has no such logical
relationship, is that it already exists; there is no need to add a new, artificial column to
the schema. Natural keys prevent duplicate rows. USED IN OLTP
Natural key – Natural keys are identifiers carried over from source systems. Natural keys are also called as
business Keys or source system keys.
Surrogate key/artificial key – A surrogate key is a unique value, usually an integer, assigned to each row
in the dimension table in the Data Warehouse and is the primary key of the dimension table.

Database normalization:
It’s a data analysis technique to design a database system. It involves the process of
organizing the fields and tables of a relational database to minimize redundancy and dependency
and no update anomalies. It involves applying a series of tests to a relation to determine whether
it satisfies or violates the requirements of a given normal form. Normalization usually involves
dividing large tables into smaller (and less redundant) tables and defining relationships between
them.

 You apply a series of test to verify if the relation satisfies or violates the requirements for
a given normal form.

Every data element must provide a fact about the key, the whole key and nothing but the key.

Data analysis technique for producing a relational schema with minimal redundancy, dependency
and no update anomalies

Anomalies are inconvenient or error-prone situations that arise when you process tables.

 Update Anomaly exists when one or more instances of duplicated data is updated, but
not all.
 Delete Anomaly exists when certain attributes are lost because of the deletion of other
attributes. For example, consider what happens if Student S30 is the last student to leave
the course - All information about the course is lost.
 Insert Anomaly occurs when certain attributes cannot be inserted into the database
without the presence of other attributes. For example this is the converse of delete
anomaly - we can't add a new course unless we have at least one student enrolled on the
course.

Normalization Stages

 Step 1: Select the data source and convert into an unnormalised table (UNF)
o Source data includes:
  Analyze documentations e.g. reports, screen layouts from the
system and select the data to be stored.
 Analyze the various applications (running on the system) and
identify the accepted inputs.
 Analyze the database for various inputs and their data types.
o Create column headings, ignoring calculated fields.
Calculated field: is one that can be derived from other info on the form.
o Enter sample data into a table.
o Identify a key for the table.
o Remove duplicate data.
 Step 2: Transform the unnormalised data into first normal form (1NF)
o Identify repeating attributes.
o Remove repeating attributes to a new table with a copy of the key from
the UNF
Repeating attribute: It’s a data field that within the UNF table that may
occur with multiple values for a single value of the key. There is potential
for more than 1 occurrence of these attributes for each value of the
key.e.g for a project code, u can have multiple employees.
o Assign a key to the new table (and underline it). The key from the original
unnormalised table always becomes part of the key of the new table.
A compound key is created. The value for this key must be unique for
each entity occurrence.
o A table is in the first normal form if it contains no repeating
columns.
o Multi-value data: At least 2 distinct values in the same data element. E.g
name includes 1stname & last name. It needs to be broken down. Another
example is address. Should be broken down to address line, city, state, zip
code.
 Step 3: Transform data in first normal form (1NF) into second normal form (2NF).
o Remove any key attributes (partial Dependencies) that only depend on
part of the table key to a new table with copy of the part of the key it is
dependent upon
o A table is in the second normal form if it is in the first normal form
and contains only columns that are dependent on the whole table
(primary) key.
o Ignore tables with (a) a simple key or (b) with no non-key attributes (these
go straight to 2NF with no conversion).
o Take each non-key attribute in turn and ask the question: is this attribute
dependent on one part of the key?
 o If yes, remove the attribute to a new table with a copy of the part of the
key it is dependent upon. The key it is dependent upon becomes the key
in the new table. Underline the key in this new table.
o The key it is dependent upon should be left in the original relation
and marked as a foreign key.
o If no, check against other part of the key and repeat above process
o If still no, i.e.: not dependent on either part of the key, keep attribute in
current table.
o
 Step 4: Transform data in second normal form (2NF) into third normal form (3NF)
o Remove any no
o A table is in the third normal form if it is in the second normal
form and all the non-key columns are dependent only on the
primary key.
o Remove to a new table any non-key attributes that are more dependent
on other non-key attributes than the table key.
o Ignore tables with zero or only one non-key attribute (these go straight to
3NF with no conversion).
o Move the dependent attribute, together with a copy of the non-key
attribute upon which it is dependent, to a new table.
o Make the non-key attribute, upon which it is dependent, the key in the
new table. Underline the key in this new table.
o Leave the non-key attribute, upon which it is dependent, in the original
table and mark it a foreign key (*).
Step 2: Transform the unnormalised data into first normal form (1NF)
 Boyce-Codd Normal Form
o Boyce Codd Normal Form (BCNF) is a further refinement of 3NF. In his
later writings Codd refers to BCNF as 3NF. A row is in Boyce Codd
normal form if, and only if, every determinant is a candidate key.
Most entities in 3NF are already in BCNF.

BCNF covers very specific situations where 3NF misses

[Link] between non-key (but candidate key) attributes.
Typically, any relation that is in 3NF is also in BCNF. However, a 3NF
relation won't be in BCNF if (a) there are multiple candidate keys, (b)
the keys are composed of multiple attributes, and (c) there are
common attributes between the keys.
 4NF: Reduce multi-valued columns. A column having more than I value. E.g. a single
[Link] in 2 jobs
 5NF: No composite keys

 4NF
 o An entity is in Fourth Normal Form (4NF) when it meets the
requirement of being in Third Normal Form (3NF) and additionally:
 Has no multiple sets of multi-valued dependencies. In other
words, 4NF states that no entity can have more than a single
one-to-many relationship within an entity if the one-to-many
attributes are independent of each other.
 Many:many relationships are resolved independently.
 5NF
o An entity is in Fifth Normal Form (5NF) if, and only if, it is in 4NF and
every join dependency for the entity is a consequence of its
candidate keys.
Enployees and departments.
UNF
Proj_code, proj_name,proj_mgr,proj_budget,emp_no,_emp_name,dept_no,dept_name,
hourly_rate
1NF
Proj_code, emp_no. emp_name, dept_no,dep_name
Proj_code, proj_name,proj_mgr,proj_budget

2NF
Emp_no, emp_name, dept_no,dept_name
Proj_code, emp_no,
Proj_code, proj_name, proj_mgr,proj_budget

3NF
Dept_no, dept_name,
Emp_no, emp_name, dept_no
Proj_code, emp_no,
Proj_code, proj_name, proj_mgr, proj_budget
Step 2: Transform the unnormalised data into first normal form (1NF)

Relational/Normalization challenges
 Resolving super types & sub types
 Concurrency: simple joins: Hierachy:
 History & auditability: Data Vault
 Anomalies: solved thru normalization
Data integrity ensures that the data in a database is both accurate and complete, in other words,
that the data makes sense. Different types of integrity that needs to be considered:
Data integrity is the consistency of data in the database

 Naming Standard
 Correct Data types data sizes
 Mandatory field i.e NOT NULL
  Domain Constraints: set of all unique values permitted for an attribute. For
example, a domain of Date is the set of all possible valid dates, a domain of Integer is
all possible whole numbers. This in effect is defining rules for a particular attribute.
 Entity Integrity: ensures that each row of a table has a unique and non-null primary
key value.

 Column Constraints: Defined by biz rules and filters the set of all possible value for
a particular attribute. During data analysis, business rules will identify any column
constraints likes salary cannot be negative.

 User-Defined Integrity Constraints: Business rules may dictate that when a specific
action occurs, further actions should be triggered. For example, deletion of a record
automatically writes that record to an audit table. E.g. insert trigger.

 Referential integrity is with the relationships between the tables of a database, ie that
the data of one table does not contradict the data of another table. Specifically, every
foreign key value in a table must have a matching primary key value in the related
table.

 Redundancy, dependency & Anomalies: Resolved thru normalization

 Error tables for logical errors. Generate reports to see the most re-occurring errors & the
reasons behind them.

o Audit DIM: When the particular batch has no errors, there is only 1 record in the
audit dim.

o Error Event Schema

DATA QUALITY
1. Purpose of data quality
a. Competitive advantage
b. Integration
c. compliance
 Complete: All relevant data —such as accounts, addresses and
relationships for a given customer—is linked.
 Accurate: Common data problems like misspellings, typos, and
random abbreviations have been cleaned up.
  Available: Required data is accessible on demand; users do not
need to search manually for the information.
 Timely: Up-to-date information is readily available to support
decisions.

Derived attribute: If an attribute’s value can be determined from the values of other attributes,
then the attribute is derivable, and is said to be a derived attribute. For example, if we have two
attributes for an employee, birth date and current age, then age is derivable by subtracting the
birth date from the current date.

Entity-relationship model (ER model ) is an abstract and conceptual representation of data.

An entity-relationship model (ERM) is an abstract conceptual data model

Requirement: A statement of what the system must do.

 Gathering

 Analyze

 Organize

 Validate

A Business Requirement is a statement of need expressed by a user or stakeholder.”

Use case is a set of activities that produce some output result

Used case: A use case is a series of events (scenarios) and interactions between two entities
(actors or systems), that achieves a specific goal necessary to satisfy a Business Requirement.

Functional Requirements: A process the system has to perform

Nonfunctional Requirements: Behavioral properties the system must have

 Operational

 Performance

 Security

 Cultural and political