Conceptual Design Flashcards
What is a data model?
A data model is a set of concepts to describe the relationships between and constraints upon data.
What is a conceptual data model?
A conceptual data model is one that identifies the high-level data structure of a database. It is independent of the DBMS, application programs and physical considerations - purely conceptual.
What type of model is an Entity-Relationship Model?
Conceptual data model
What is a logical data model?
A logical data model describes the data in terms of data structures such as graphs, trees or relations. It is independent of a particular DBMS and its storage technology.
What type of model is a Relational Model?
Logical data model
What is a physical data model?
A physical data model describes how data is stored in the computer, representing record structures, record orderings and access paths. It is highly dependent on the target DBMS.
What steps might we take to design a database?
HINT: Conceptual, Logical, Physical
We use the ER data model to create a conceptual design, then a relational database model to logically string it together. Then, we create a file organisation and access path system to create a physical design, and finally implement it using our DBMS of choice.
What is the Entity-Relationship (ER) Model?
The ER model is a way to describe how data is related in a domain of knowledge. It is best used as a way to describe how the database will be laid out to end-users of the product.
What is the component of an ER model that refers to “a group of objects with the same properties”?
Entity
What is the component of an ER model that refers to “a meaningful or many meaningful associations between two or more entities”?
Relationships
What is the component of an ER model that refers to “a property of an entity or relationship”?
Attribute
What is the difference between an entity type and an entity occurrence?
Think of it like a class definition and an object instance - the entity type is what the entity is, whereas an occurrence is an instance of that entity.
What is the degree of a relationship?
The degree of a relationship - if you think back to Discrete Maths - is the number of participating edges/entity types in a relationship.
What is the difference between a recursive and a multiple relationship?
A recursive relationship is one where an attribute in one table is related to another attribute in the same table.
A multiple relationship is when two entities are associated through more than one relationship.
What is a composite attribute?
A composite attribute is made up of two or more attributes that collapse into one parent attribute. Typically denoted this way to represent the atomicity of data.
What is a derived attribute?
A derived attribute is one whose value is calculated based on the value of other attributes in the entity type, denoted by a slash (/) placed before the attribute name.
What is a multi-valued attribute?
A multi-valued attribute is one with multiple values. It is denoted by a range ([1..99]) placed after the attribute name.
What is a key?
A key is an attribute that helps us to identify a row uniquely in a table.
What is a primary key?
A primary key is a unique identifier for an entity occurrence. The primary key must be not null, and must be unique (distinct).
What is a candidate key?
A candidate key is a key whose values uniquely identify each entity occurrence. Candidate keys may then be either a primary or alternate key depending on administrator choice.
What is an alternate key?
A candidate key that has not been chosen for the primary key.
Can we place attributes as being under a relationship?
Yes, we may want to do this if we want to allow for, for example, a student to enrol on different courses on different days - without an intermediary table, we would have duplicate student data, which violates the primary key.
What is the difference between a strong and a weak entity type?
A weak entity type is dependent on some other entity. Because of this, each entity occurrence cannot be uniquely identified using only the attributes associated with that entity type. A strong entity type is the opposite.
Describe the meaning of {Mandatory/Or} in an modelled superclass-subclass(es) relationship.
‘Mandatory’ implies that the parent entity must be one of the related entities.
‘Or’ implies that the parent entity must be given only one of the related entities.
Therefore, {Mandatory/Or} implies that the parent entity must always have exactly one relationship.
For example, a member of Staff must either be a FullTimeStaff or a PartTimeStaff - therefore, this is a {Mandatory/Or} relationship.
What is the concept of aggregation in ER modelling?
Aggregation is a subjective approach to modelling relationships where we treat a group of entities and relationships as one encompassing high-level entity, sort of like Dataiku’s Flow Zones.
What is specialisation and generalisation in ER modelling?
Specialisation and generalisation are two subjective approaches to defining a set of superclasses and their related subclasses.
What are the differences between specialisation and generalisation in ER modelling?
Specialisation is a top-heavy approach that refers to the maximisation of differences between members of an entity by identifying their distinguishing characteristics.
Generalisation is a bottom-heavy approach that refers to the minimisation of differences by identifying their common characteristics.
What is meant when we say that specialisation, generalisation and aggregation techniques are subjective?
Their use all comes down to personal preference. Some people prefer to use them because they simplify and abstract the model, while some see them as hard to read because too much is abstracted.
