Week 3 Flashcards
What are the two approaches to database design?
Normalisation (bottom-up) and Semantic Data Modelling (top-down)
What is normalisation (Bottom-Up) in database design?
this has one table describing all relevant data and serves as the starting point. Consists of multiple tables which are decomposed until 3NF.
What is Semantic Data Modelling (Top-down) in database design?
Knowledge about business processes and information needs is used to create a diagram (entity relationship diagram). This describes the data and its relationships that are to be recorded in a database.
What is Entity Relationship Modelling (ERM)
ERM is a modelling notation used to model the characteristics and relationships of data. Useful to formalize and visualize the structure of data for implementation of databases.
What does ERD consist of?
Entity, Attributes and Relationships
What are entities in ERD?
Real word things we want to record data about
What are attributes in ERD?
Characteristics of entities, all entities should have the same in common. Predefined value range.
What are relationships in ERD?
Associations between entities. Defines a set of relationships among entities from different entity types. A relationship type can also have an attribute.
What are cardinalities?
Expresses the number of entity occurrences associated with one occurrence of a related entity.
What is an entity type?
collection of entities with similar characteristics. The name should be a generic term describing all entities of the collection.
What is a key?
The identifying attribute - it is an attribute that unambiguously identifies an entity. A key can be a combination of multiple attributes.
What are the 4 meanings of carinalities?
1 = {1}
C = {0, 1}
N = {1, 2, …, n}
NC = {0, 1, 2, …, n}
What is an 1:1 relationship?
Everyone uses one
What is a C:C relationship?
Everyone can have 1 or 0
What is an example of 1:N relationship?
Mother & child
What is an example of 1:NC relationship?
Woman & child
What is an example of 1:C relationship?
Woman & Husband
What is an example of M:N relationship?
House & Landlord
What is an example of M:NC relationship?
Lecturer & Lecture
What is an example of MC:NC relationship?
Adult & Child
What should be done with attributes that cannot be assigned to an entity?
Attributes that cannot be assigned to an entity have to be assigned to relationships
What is step 1 of ERM into relational models?
Every entity type is translated into a table, the attributes of the entity type become the columns of the table
What is step 2 of ERM into relational models?
1:1 relationships
What is step 3 of ERM into relational models?
1:N relationships
What is step 4 of ERM into relational models?
M:N relationships
What is the process in step 1; transformation of entities?
- An entity type becomes a table
- Attributes become column
- A single entity is represented by a single row
- The primary key is used to unambiguously identify one row.
What is the process in step 2; transformation of 1:1-relationships?
The data from two tables is combined into one single table.
What is the process in step 3: transformation of 1:N-relationships?
You have 2 tables. The primary key of the higher table (Customer) is added as an additional column (foreign key) in the lower order table (Order)
What is the process in step 4: transformation of M:N-relationships?
3 tables are required. The new table includes the primary key of both entity tables and attributes of the relationships.
What does the transformation of C:C carinals look like?
Usually C:C is treated is NC:NC, to avoid NULL values.
4 Data requirements for Relational Databases
- Every column must be single valued.
- Primary key must contain data (nut null)
- Referential integrity
- All non-key attributes must describe a characteristic of the entity identified by the primary key.
What is referential integrity?
Foreign keys must contain the same data as the primary key in another table
The constraint that all primary keys must have non-null values is referred to as
Entity integrity rule
