Conceptual Design

Question 1

What is a data model?

Answer 1

A data model is a set of concepts to describe the relationships between and constraints upon data.

Question 2

What is a conceptual data model?

Answer 2

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.

Question 3

What type of model is an Entity-Relationship Model?

Answer 3

Conceptual data model

Question 4

What is a logical data model?

Answer 4

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.

Question 5

What type of model is a Relational Model?

Answer 5

Logical data model

Question 6

What is a physical data model?

Answer 6

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.

Question 7

What steps might we take to design a database?

HINT: Conceptual, Logical, Physical

Answer 7

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.

Question 8

What is the Entity-Relationship (ER) Model?

Answer 8

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.

Question 9

What is the component of an ER model that refers to “a group of objects with the same properties”?

Answer 9

Entity

Question 10

What is the component of an ER model that refers to “a meaningful or many meaningful associations between two or more entities”?

Answer 10

Relationships

Question 11

What is the component of an ER model that refers to “a property of an entity or relationship”?

Answer 11

Attribute

Question 12

What is the difference between an entity type and an entity occurrence?

Answer 12

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.

Question 13

What is the degree of a relationship?

Answer 13

The degree of a relationship - if you think back to Discrete Maths - is the number of participating edges/entity types in a relationship.

Question 14

What is the difference between a recursive and a multiple relationship?

Answer 14

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.

Question 15

What is a composite attribute?

Answer 15

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.

Question 16

What is a derived attribute?

Answer 16

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.

Question 17

What is a multi-valued attribute?

Answer 17

A multi-valued attribute is one with multiple values. It is denoted by a range ([1..99]) placed after the attribute name.

Question 18

What is a key?

Answer 18

A key is an attribute that helps us to identify a row uniquely in a table.

Question 19

What is a primary key?

Answer 19

A primary key is a unique identifier for an entity occurrence. The primary key must be not null, and must be unique (distinct).

Question 20

What is a candidate key?

Answer 20

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.

Question 21

What is an alternate key?

Answer 21

A candidate key that has not been chosen for the primary key.

Question 22

Can we place attributes as being under a relationship?

Answer 22

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.

Question 23

What is the difference between a strong and a weak entity type?

Answer 23

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.

Question 24

Describe the meaning of {Mandatory/Or} in an modelled superclass-subclass(es) relationship.

Answer 24

‘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.

Question 25

What is the concept of aggregation in ER modelling?

Answer 25

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.

Question 26

What is specialisation and generalisation in ER modelling?

Answer 26

Specialisation and generalisation are two subjective approaches to defining a set of superclasses and their related subclasses.

Question 27

What are the differences between specialisation and generalisation in ER modelling?

Answer 27

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.

Question 28

What is meant when we say that specialisation, generalisation and aggregation techniques are subjective?

Answer 28

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.