Conceptual Design

Question 1

What are the three levels of ANSI-SPARC Architecture?

Answer 1

The three levels of ANSI-SPARC architecture are the external level, the conceptual level, and the internal level

Question 2

What is the external level of ANSI-SPARC Architecture?

Answer 2

The external level is how the user actually views the database. The data of the database that is relevant to the user is described here.

Question 3

What is the conceptual level of ANSI-SPARC Architecture?

Answer 3

The conceptual level is the community view of the database and it describes what data is actually stored in the database; the entities, attributes and relationships.

This level contains the logical structure of the database without thinking about any storage considerations.

Question 4

What is the internal level of ANSI-SPARC Architecture?

Answer 4

The internal level describes the physical representation of the database on the computer; how it is actually stored.

Question 5

What is meant by independence in a database?

Answer 5

Logical data independence means that users/applications are immune to changes to the logical structure of a database.

Physical data independence means that users/applications are immune to changes to the physical representation of the data.

logical changes would be on conceptual level, physical would be internal

Question 6

What is a data model

Answer 6

A data model is “an integrated collection of concepts for describing and manipulating data, relationships between data, and constraints on the data in an organization.”

Quote by Connolly & Begg

Question 7

What are the three design phases?

Answer 7

Conceptual design, logical design, and physical design.

Question 8

What happens in the conceptual design phase?

Answer 8

A semantic model of the information used is built, independent of all physical considerations.

Question 9

What happens in the logical design phase?

Answer 9

A database model based on a specific kind of data model is constructed, independent of any particular DBMS.

DBMS = Database Management System

Question 10

What happens in the physical design phase?

Answer 10

The DBMS is implemented; how the data is stored on the disk.

DBMS = Database Management System

Question 11

What are the main components of an entity-relationship model?

Answer 11

Entities/Entity Types, Relationships, Attributes and Constraints.

Question 12

What is an entity?

Answer 12

A uniquely identifiable data object or item of interested.

Can be physical or abstract.

Belongs to an entity type. (A group of entities with the same properties)

(Jake and Sam can be two distinct entities belonging the same entity type (student)

Question 13

What is a relationship?

Answer 13

A relationship is a relation between two ore more entity types. E.g; A branch employs staff.

Branch and staff are entity types, Employs is a relationship.

Question 14

What is the degree of a relationship?

Answer 14

The number of entity types involved.

Question 15

What are attributes?

Answer 15

Attributes are facts, aspects, properties or details about an entity or relationship.

Entity “staff” might have a phone number, DOB or address.
Relationship “employs” might have a start date.

Question 16

What is a simple attribute?

Answer 16

Has a domain in which its values are taken.

May be single valued or multiple valued. (e.g, one or several phone numbers)

May be a key attribute, part of a group of attributes which uniquely identify entities.

Question 17

What is a composite attributes

Answer 17

An attribute that consists of multiple components each of which can be simple or composite.

E.g, parts of an address.

Question 18

What is a derived attribute?

Answer 18

A value that is derivable from the value of one or several other attributes. E.g, age can be derived from date of birth.

Question 19

What is a key attribute?

Answer 19

Keys usually consist of one attribute; national insurance number, serial number, registration number. These uniquely identify a person or object.

They can also consist of several attributes working together.

Question 20

What is a weak entity type?

Answer 20

An entity type that is existence dependent on another entity type (via a relationship)

They are only uniquely identifiable through the relationship with another strong entity type.

A client can have a preference for a particular house. Client is a strong entity type, but preference is weak as it is dependent on client to be uniquely identifiable.

Question 21

What is a cardinality constraint?

Answer 21

A cardinality constraint describes the maximum number of possible relationship occurrences for an entity participating in a relationship.

A branch might employ many workers, but a worker can only be employed by one branch

• Relationship “Branch employs staff”:
• A branch employs many staff
• A member of staff is employed by one branch
• Cardinality is 1:M

Question 22

What is a participation constraint?

Answer 22

A participation constraint determined whether all or some entities participate in a relationship.

All branches have workers, and all workers belong to a branch.

Not all workers manage a branch, but all branches must be managed by a worker

• Relationship “Branch employs staff”:
• A branch may employ staff.
• A member of staff must be employed by a branch.
• participation is partial for branch, total for staff.

Question 23

What is a multiplicity constraint?

Answer 23

A multiplicity constraint describes participation and cardinality together by labelling the participation of an entity type in a relationship.
Uses exact upper and lower bounds.
“A branch is managed by exactly one staff”
“A member of staff can manage at most two branches”

Staff -(1,1)-> Manages -(0,2)-> Branch

If there’s no upper bound, you can use “*”

(E.g, (0,*))

Question 24

What is a non-binary relationship?

Answer 24

A relationship involving three entitities.

E.g, between staff, branch, and client.
* Staff registers client at a branch
* At a branch a member of staff registers many clients
* A client at a branch is registered by one member of staff

Question 25

What is a recursive relationship?

Answer 25

A recursive relationship is a relationship type where the same entity type participates more than once in different roles.
E.g, Staff supervises staff.
Staff as an entity type appears twice, but one staff is supervising the other staff.

Question 26

What is meant by subclasses and superclasses?

(Aspect of Enhanced Entity Relationships)

Answer 26

Subclass entity types inherit attributes from superclass entity types. It avoids specifying the same attributes twice.
E.g, “Vehicle” might be the superclass of subclasses, truck, bus and car.

Question 27

What is meant by participation constraint of a supertype?

Answer 27

Determines whether every member in the supertype must participate as a member of a subtype.
Mandatory - Every member of a supertype must also be a member of a subtype, or Optional - Opposite of mandatory

Question 28

What is meant by disjoint constract of a supertype?

Answer 28

Disjoint constraint indicates whether it is possible for a member of a supertype to be a member of one or more than one subtype.

Question 29

What is a fan trap?

Answer 29

When a model represents a relationship between entity types, but the pathway between certain entity types is ambigious.
E.g, What branch a member of staff might work for.

Question 30

What is a chasm trap?

Answer 30

When a model suggests the existence of a relationship between entity types, but the pathway between those occurrences does not exist.