Data management

What are the characteristics in an ER diagram?

An entity - Rectangle labelled in the middle

Relationship - Solid lines between entities

What is a cardinality in ER diagrams?

Numerical relationship between entities

What are the possible cardinalities?

1. One to one

2. One to many

3. Many to many

How to represent many to many cardinality?

Crow-foot on both ends

What are the benefits of data normalisation?

1. Less space / storage OR reduce data redundancy

Organised into related tables → reduces chance of storing same data (duplicates) multiple times → 1. Save Storage 2. Improve data consistency

2. Data Integrity

Data update anomalies and minimized as when data is inserted, updated or deleted, the changes are reflected accurately across the database without causing inconsistently, hence enhancing reliability and data integrity. 

When is a data said to be in First Normal Form (1NF)?

If

• all its data values are atomic (single values)

• there are no repeating columns (or groups of columns)

When is a data said to be in Second Normal Form (2NF)?

If

• it is also in 1NF

• the non-key fields are fully dependent on the primary key (no partial dependence)

What is partial dependence?

Where a table has a composite key, if the non-key fields depend only on some of the fields in the composite key, this is called partial dependence

When is a data said to be in Third Normal Form (3NF)?

If

• it is also in 2NF

• the non-key attributes are non-transitively dependent on primary key

(Understanding) What does transitively dependent mean?

If an attribute is transitively dependent on a primary key, that means it depends on another attribute which is in turn dependent on the primary key.

Data integrity (definition)

The accuracy and consistency of data stored

Accuracy - accurately reflect the real world

Consistency - data in one part of the system should not contradict data in other parts

What are some types of data integrity constraints?

Entity integrity - PK must be unique and not null

Referential integrity - FK point to existing PK (if not, it is orphaned record)

Domain integrity - Each column should adhere to the same schema

User-defined integrity - Apply further integrity checks

Data redundancy (definition)

When non-key values are repeated

(such as civics tutor of a student with unique id)

What are the four general points of comparison between SQL and NoSQL databases?

1. Structured

2. Strict

3. Scale direction

4. Hierarchical data storage

(SSS)

Compare SQL vs NoSQL database (structure)

SQL

• Each field has a fixed data type

• Each record has the same fields

NoSQL

• Fields need not have fixed data type

• Records can have different fields

Compare SQL vs NoSQL database (strict)

SQL

• Schema is difficult to change

• Maintains strict data integrity

NoSQL

• No fixed schema to follow

Compare SQL vs NoSQL database (scale direction)

SQL

• When database grows, need a more powerful machine

NoSQL

• When database grows, can add more machines (scale horizontally without degradation of performance)

Compare SQL vs NoSQL database (Hierarchical)

SQL

• Does not support

NoSQL

• often supports where less frequently accessed data can be moved to more cost-effective storage solutions, optimizing storage costs and performance.