Flashcards in Lecture 6 Deck (26):
Raw facts and figures without any deeper meaning
Data shaped into meaningful form through context
Results from experience and education
What does a relational database do?
It organises data into one or more related tables
What does a unique key do?
It identifies each row, and can be used to link different tables.
Why should redundancy be reduced? (2)
1. it increases the amount of storage required
2. It is a major source of data inconsistencies
What is one benefit of redundancy?
It can improve database performance since there is not needs for joint operations
What does database normalization do?
It manages the tradeoff between redundancy and performance (ease of use)
Requirements for 1NF?
1. The domain of each attribute contains only atomic values
2. The values of each attribute contain only one value from that domain
Requirement for 2NF?
1. Must satisfy 1NF
2. In the case of a composite key, there are no partial dependencies. ie. non-key attributes are dependent on all key attributes and not just a subset
Requirement for 3NF?
1. Must satisfy 2NF
2. There should be no transitive dependencies. Attributes should not be functionally dependent on other non-key attributes.
What is Entity relationship modelling (ERM)?
describes the relationship between entities (things of interest)
Entity relationship diagrams are built on what four elemental concepts?
What are entities?
They are things of interest to an organisation
What are attributes?
They describe the entity on question
What are relationships?
They express that entities are linked together
What are cardinalities?
They represent the number of occurrences within one entity that are associated with occurrences in another.
What are the 3 types of cardinalities?
1. One-to-one: exactly one occurrence in entity A is associated with exactly one occurrence in entity B
2. One-to-many: Exactly one occurrence in entity A is associated with many occurrences in entity B
3. Many-to-many: Many occurrences in entity A are associated with many occurrences in entity B
What is optionality (lower cardinality) and upper cardinality?
The minimum amount of occurrences, and the maximum amount of occurences
What is an entity set and a relationship set?
It contains all entities of a similar kind / all relationships of a similar kind.
What are candidate keys and a primary key?
There are often multiple possible keys, and the one post suited as the main reference is the primary key. The primary key is chosen from all candidate keys
3 steps or constructing a data model and what do they entail?
1. Conceptual model: Identity all entities and relationships
2. Logical model: Identify the attributes of all entities and determine keys
3. Physical model: a database structure than can be implemented of a specific platform. All M:N relationships can been resolved through associative tables and normalisation has occurred.
SQL: Data definition language (DDL) meaning?
creating, deleting, and altering, databases and tables (STRUCTURE). Important when working with ERD.
SQL: Data manipulation language (DML) meaning?
retrieving, inserting, changing, deleting tuples/records (CONTENT)