Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

ECS519U - Database Systems > Week 5 - DB Design using ER model > Flashcards

Week 5 - DB Design using ER model Flashcards

1
Q

What are the four steps in the DB design process?

A

1) Requirements collection and analysis
2) Conceptual Design
3) Logical design / data model mapping
4) Physical design

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What happens in Step 1: Requirements Collection and Analysis?

A
  • Gather users’ data requirements (what kind of data needs to be stored e.g. student records)
  • Gather users’ functional requirements (what does the db need to do e.g. allow users to search for employees)
  • Use SWE tools like data flow diagrams and UML.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is the goal of Step 2: Conceptual Design?

A
  • Create a high-level blueprint of the database using a conceptual data model (e.g., ER model).
  • Focus on understanding data relationships without considering physical storage.
    Refine the model to remove redundancy.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What happens in Step 3: Logical Design?

A
  • Convert the conceptual model into a logical data model (e.g., relational data model).
  • Structure data in a way that the database management system (DBMS) can implement.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What is done in Step 4: Physical Design?

A
  • Define storage structures, file organization, and access paths.
  • Design and implement application programs for interacting with the database.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What does a database (DB) application consist of?

A

A database (DB) and its associated programs, which interact with the data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What is ER modelling in database design?

A

A high-level conceptual design technique used to visualize and describe the structure of a database with textual and graphical representations.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What are the three main concepts of ER modelling?

A

Entities: Objects or “things” in the database (e.g., Student, Book).
Relationships: Associations between entities (e.g., Student enrolls in Course).
Attributes: Characteristics of entities and relationships (e.g., Student has a Name, ID).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

How are relationships represented in ER diagrams?

A

Diamonds

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

How are entities represented in ER diagrams?

A

Rectangles

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What is an attribute?

A

A particular property which describes the entity. They can be classified in terms of their characteristics. An individual attribute has a value.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

How are attributes represented in ER diagrams?

A

Ovals connected to their respective entity or relationship.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is the ‘value set’ or ‘domain’ of an attribute?

A

The set of possible values.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What is assigned to attributes for which the values are unknown or non-existant?

A

NULL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

How are values represented in ER diagrams?

A

They aren’t.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is an entity type?

A

A blueprint that defines the structure of the table, including its name and attributes.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What is an entity set?

A

The collection of all entities (or rows) that belong to a particular entity type.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

How are entity types and sets named?

A

The entity type and its associated entity set typically share the same name.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What is an entity?

A

A specific instance of an entity type containing data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What are simple (atomic) attributes?

A

Attributes that cannot be broken down into smaller parts (indivisible).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Give an example of a simple (atomic) attribute?

A

A person’s date of birth or their national ID number.

22
Q

What are composite attributes?

A

Attributes that can be divided into smaller subparts, each of which has its own independent meaning.

23
Q

What are the two features of composite attributes?

A

1) They often form a HIERARCHY, where a main attribute is composed of smaller attributes.
2) They are useful when you need to handle the attribute sometimes as a single unit, and other times as individual components.

24
Q

Give an example of a composite attribute

A

An address can be viewed as a composite attribute comprising:
- City
- Postcode
- Line 1
- Line 2
- Country
This means the address can be stored/retrieved as a whole, but its subparts can also be accessed or manipulated individually when needed (e.g. filtering records by city or country)

25
Q

Single-valued attributes VS Multi-valued attributes

A

Single-valued attributes have a single value for a particular type, whereas multi-valued attributes have a set of values for a particular type.

26
Q

What are multi-valued and composite attributes collectively known as?

A

Complex attributes

27
Q

Features of complex attributes

A

Can be nested in an arbitrary way

28
Q

Example of complex attribute

A

Addresses is multi-valued. Address is composite.

29
Q

What is a derived attribute

A

An attribute whose value is calculated using the value of another stored attribute of the same, or a related, entity type.

30
Q

Give an example of a derived attribute

A

‘age’ can be derived from current year and date of birth

31
Q

Stored attributes (in theory VS in practice)

A

In theory, the value of a stored attribute is not derivable from any other source. In practice, we sometimes find derived attributes being stored (materialised)

32
Q

What are relationship types?

A

A structural specification of associations among entity types.

33
Q

Give an example of a relationship type

A

In a DB for a school, the relationship type might be “Teaches” for connecting entity types “Teacher” and “Course”

34
Q

What is a relationship set?

A

The collection of all associations among entities for a particular relationship type.

35
Q

Give an example of a relationship set

A

For the relationship type “Teaches”, the relationship set might include:
- Teacher: Dr. Smith, Course: Database Systems
- Teacher: Prof. Jones, Course: Machine Learning
These are concrete pairs (or tuples) of entities that participate in the relationship.

36
Q

How are relationship types and relationship sets named?

A

They typically both have the same name, for simplicity and clarity.

37
Q

What does relationship degree do?

A

It indicates the number of participating entity types.

38
Q

What names are given to relationship types of degree 2 and 3?

A

2: Binary
3: Ternary

39
Q

What is the purpose of role names?

A

Role names explain what a relationship means and indicate the role an entity plays in a relationship instance.

40
Q

What do role names clarify in a relationship instance?

A

Role names clarify the specific function or responsibility of an entity within the relationship.

41
Q

What are role names absolutely necessary for?

A

Recursive relationships where the same entity type participates more than once in a relationship, but in different roles.

42
Q

What is an example of role names in a “Manages” relationship?

A

In a “Manages” relationship between Employee and Department, role names could be “Manager” for the employee and “Managed Department” for the department.

43
Q

Explain relationship type constraints

A

These are structural constraints that restrict how entities can be related to each other within a database. They help define the rules or limitations on how relationships between entities are formed.

44
Q

What are the two types of relationship type constraints

A

Participation Constraints
Cardinality Ratio Constraint (Cardinality Constraint)

45
Q

What is the difference between total and partial participation constraints?

A

Total - Any individual entity in the entity set MUST be related to another entity by relationship type
Partial - Any individual entity in the entity set MAY be related to another entity by relationship type

46
Q

What are all the possible cardinality ratio constraints?

A

1:1
1:N (N:1)
M:N

47
Q

Relationship type attributes

A

idk wtf this is

48
Q

Weak Entity Types

A
  • Have no key attribute
  • Existence depends on some other entity type
  • You can sometimes represent it as a complex attribute instead
49
Q

Steps of ER Modelling

A

1) Initial Conceptual Design
- Identify entities
- Identify attributes and their properties (e.g. pkey, simple/composite, …)
2) Refined conceptual design
- Identify relationships (degree, recursive, cardinality, …)
- Remove redundant attributes
3) ER Diagram

50
Q

UML Class Diagram VS ER Diagram

A

UML Class is more object oriented focused
Different terminology used (entity -> object)
UML Class specifies operations

51
Q

ER Conceptual Design: Stages

A

ECS519U - Database Systems (12 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions