Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

Database Systems > Conceptual & Logical Database Design > Flashcards

Conceptual & Logical Database Design Flashcards

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

What is the purpose of a design methodology?

A

To support and facilitate the process of design using procedures, techniques, tools, and documentation aids.

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

What are the three main phases of database design?

A
  • Conceptual database design
  • Logical database design
  • Physical database design
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What is the aim of conceptual database design?

A

To build the conceptual representation of the database, including identifying important entities, relationships, and attributes.

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

What is the output of conceptual database design?

A

ER Diagram

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

What is the aim of logical database design?

A

To translate the conceptual representation to the logical structure of the database, which includes designing the relations.

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

What is the output of logical database design?

A

Relational Database Schema

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

What is the aim of physical database design?

A

To decide how the logical structure is to be physically implemented in the target DBMS.

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

What does physical database design output?

A

Actual implemented database

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

What is the process of constructing a model of data used in an enterprise independent of physical considerations called?

A

Conceptual database design

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

What is the process of producing a description of the implementation of the database on secondary storage?

A

Physical database design

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

What are the steps involved in building a conceptual data model?

A
  • Identify entity types
  • Identify relationship types
  • Identify and associate attributes with entity or relationship types
  • Determine attribute domains
  • Determine candidate, primary and alternate key attributes
  • Consider use of enhanced modelling concepts (optional step)
  • Check model for redundancy
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is normalisation in the context of logical database design?

A

A process to validate relations to ensure they are structured correctly.

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

What is the difference between the term ‘relation’ and ‘relationship’ in the context of databases?

A

‘Relation’ refers to a table in a relational database, while ‘relationship’ refers to associations between entities in an ER model.

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

What are the types of attributes that can exist in an ER diagram?

A
  • Simple
  • Composite
  • Multi-valued
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What are weak entities in database design?

A

Entities that are existence-dependent on some other entity.

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

What is a surrogate key in database design?

A

A simpler key used to replace a cumbersome composite primary key.

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

How is a many-to-many binary relationship represented in a database?

A

By creating a separate relation to represent the relationship and including foreign keys from both participating entities.

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

What does the term ‘mapping’ refer to in logical database design?

A

The process of converting entities and their attributes from an ER diagram to a set of relations.

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

What is the primary key composed of when mapping weak entities?

A

Partial identifier of weak entity and primary key of identifying relation (strong entity).

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

What is the role of foreign keys in database relations?

A

To link records in one relation to records in another, representing relationships between entities.

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

What attributes are included in a relation representing a multi-valued attribute?

A

The primary key of the entity and the multi-valued attribute as a new relation.

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

What is the first step in logical database design?

A

Derive relations for the local logical data model.

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

Fill in the blank: The output of physical database design is the _______.

A

actual implemented database

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

True or False: The output of logical database design is an ER diagram.

A

False

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

What must be checked during the validation of a logical data model?

A

Relations against user transactions.

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

What type of attributes should be used when mapping composite attributes?

A

Only their simple, component attributes.

28
Q

What is the significance of the primary key in the EMPLOYEE_PHONE_NUMBER relation?

A

It is composite and consists of both Employee_ID and Phone_Number.

29
Q

What is the process of designing physical representation in database design?

A

Translating the global logical data model for the target DBMS.

30
Q

What are the steps to design user views and security mechanisms?

A

Define user views and implement security measures.

31
Q

What is the primary key for the PropertyForRent entity?

A

propertyNo

32
Q

What are the attributes of the Client entity?

A
  • clientNo
  • fName
  • lName
  • telNo
33
Q

What is the primary key for the Client entity?

34
Q

What is the purpose of the Viewing relation?

A

To represent the relationship between Client and PropertyForRent

35
Q

What attributes does the Viewing relation contain?

A
  • clientNo
  • propertyNo
  • dateView
  • comment
36
Q

What is the primary key for the Viewing relation?

A

clientNo, propertyNo

37
Q

What foreign key references the Client entity in the Viewing relation?

38
Q

What foreign key references the PropertyForRent entity in the Viewing relation?

A

propertyNo

39
Q

What is the primary key for the Staff entity?

40
Q

What attributes does the Staff entity contain?

A
  • staffNo
  • fName
  • lName
  • position
  • DOB
41
Q

What additional attribute does the Client entity have that is a foreign key?

42
Q

What is the primary key for the Owner entity?

43
Q

What attributes does the Owner entity contain?

A
  • ownerNo
  • address
  • telNo
44
Q

What is the primary key for the PropertyForRent entity after including foreign keys?

A

propertyNo

45
Q

What are the foreign keys in the PropertyForRent entity?

A
  • ownerNo
  • staffNo
46
Q

What does a complex relationship like Registration involve?

A

Links Staff, Branch, and Client entities

47
Q

What are the attributes of the Registration relation?

A
  • clientNo
  • branchNo
  • staffNo
  • dateJoined
48
Q

How is a 1:1 relationship represented when there is mandatory participation on both sides?

A

Combine the entities into one relation

49
Q

In a 1:1 relationship with optional participation on both sides, how is the parent and child entity designated?

A

Arbitrarily, based on which is closer to being mandatory

50
Q

In a 1:1 relationship with mandatory participation on one side, how is the parent and child identified?

A

The entity with optional participation is the parent

51
Q

What is the approach for mapping unary (recursive) relationships in a one-to-many scenario?

A

Create a recursive foreign key in the same relation

52
Q

What is the approach for mapping unary (recursive) relationships in a many-to-many scenario?

A

Create two relations: one for the entity type and one for an associative relation

53
Q

What is the first phase of database design methodology?

A

Conceptual database design

54
Q

What is the second phase of database design methodology?

A

Logical database design

55
Q

What is the third phase of database design methodology?

A

Physical database design

56
Q

What are the three main phases of database design methodology?

A

Conceptual database design, Logical database design, Physical database design

These phases outline the progression from initial concept to implementation.

57
Q

What is the purpose of creating a logical data model from an ER conceptual data model?

A

To structure the data in a way that can be effectively implemented in a database system

It involves defining the relationships and attributes in detail.

58
Q

What types of attributes are mapped in database design?

A

Simple, composite, and multivalued attributes

Each type of attribute has different implications for data storage and retrieval.

59
Q

What is a weak entity in database design?

A

An entity that cannot be uniquely identified by its own attributes alone

Weak entities rely on a ‘strong’ entity for their identification.

60
Q

What are the types of binary relationships in database design?

A

1:1, 1:*, :

These relationships define how entities interact with each other.

61
Q

What is a ternary relationship in database design?

A

A complex relationship involving three entities

It is more complex than binary relationships and requires careful mapping.

62
Q

What does mapping unary (recursive) relationships involve?

A

Mapping relationships where an entity is related to itself

This is common in hierarchical data structures.

63
Q

Fill in the blank: The Item_No is a _______ in the context of database design.

A

Primary Key

Primary Keys uniquely identify records in a database table.

64
Q

What is the significance of the Unit_Cost in the ITEM entity?

A

It represents the cost associated with each item

This attribute is crucial for pricing and inventory management.

65
Q

What does the Quantity attribute represent in the ITEM entity?

A

The number of units available for each item

This is important for inventory control and sales tracking.

Database Systems (6 decks)

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