Lecture 3

Question 1

Main purposes of data modeling include:

Answer 1

• to assist in understanding the meaning (semantics) of the data;
• to facilitate communication about the information requirements.

Question 2

Building data model requires answering questions about

Answer 2

entities, relationships, and attributes

Question 3

Conceptual Database Design

Answer 3

• Process of constructing a model of the data used in an enterprise, independent of all physical considerations
• built using the information in users’ requirements specification
• Conceptual data model is source of information for logical design phase

Question 4

Logical Database Design

Answer 4

• Process of constructing a model of the data used in an enterprise based on a specific data model (e.g. Relational or Dimensional),
• but independent of physical considerations and a particular DBMS

Question 5

Physical Database Design

Answer 5

• Process of producing a description of the database implementation on secondary storage
• Describes base relations, file organizations, and indexes used to achieve efficient access to data
• Also describes any associated integrity constraints and security measures
• Tailored to a specific DBMS

Question 6

Application Design

Answer 6

• Design of user interface and application programs that use and process the database.
• Database design and application design are parallel activities.
• Includes two important activities:
  
  • transaction design;
  • user interface design.

Question 7

Implementation

Answer 7

• Physical realization of the database and application designs
• This will include the database transactions implemented using a host programming language

Question 8

usability

Answer 8

• Should also test usability of system (HCI)
• Evaluation conducted against a usability specification (measuring learnability, adaptability, etc.)

Question 9

Concepts of the ER Model

Answer 9

Entity types

• Group of objects with same properties, identified by enterprise as having an independent existence
• Entity occurrence, uniquely identifiable object of an entity type.

Relationship types

• Set of meaningful associations among entity types.
• Relationship occurrence, uniquely identifiable association, which includes one occurrence from each participating entity type.

Attributes

Question 10

Semantic net of Has relationship type

Answer 10

Question 11

ER relationship diagram of Branch Has Staff

Answer 11

Question 12

degree of a relationship

Answer 12

Number of participating entities in relationship.

• two is binary
• three is ternary
• four is quaternary

Question 13

Examples: Binary Relationship

Answer 13

Question 14

Example: Ternary Relationship

Answer 14

Question 15

Example: Quaternary Relationship

Answer 15

Question 16

relationship types

Answer 16

Recursive relationship - where same entity type participates more than once in different roles.

(Relationships may be given role names to indicate purpose that each participating entity type plays in a relationship.)

Question 17

Entities associated through two distinct relationships with role names

Answer 17

Question 18

Attributes & Attribute Domains

Answer 18

Attribute
Property of an entity or a relationship type

• Example: A staff entity type may be described by the staffNo, name, position, and salary attributes
• The attributes hold values that describe each entity occurrence and represent the main part of the data stored in the database

Attribute Domain
Set of allowable values for one or more attributes

• Example: A house can only have between 1 - 15 rooms
• Attributes may share a domain
  
  • Example: PrivateOwner, and BusinessOwner entity types share the same domain for theAddress and Branch attributes (Domain: all possible addresses)

Question 19

simple attributes vs. composite attributes

Answer 19

Simple Attribute - composed of a single component with an independent existencen

Composite Attribute - composed of multiple components, each with an independent existence

Some attributes can be further divided to yield smaller components with an independent existence of their own; Your choice whether simple/composite.

• SIMPLE: Address = 163 Main St, Glasgow, G11 9QX
• COMPOSITE: Address can be divided into:
  
  • No=163
  • Street=Mainst
  • City=Glasgow
  • Postcode=G119QX

Question 20

sub-domains

Answer 20

Domains can also be composed of domains

Example: the domain for the address attribute of the Branch entity is made up of subdomains:

• Street
• City
• Postcode.

Question 21

single-valued, multi-valued, derived attributes

Answer 21

Single-valued Attribute - holds a single value for each occurrence of an entity type

Multi-valued Attribute - holds multiple values for each occurrence of an entity type

• multiple phone numbers for tel_No (office, mobile number)

Derived Attribute - represents a value that is derivable from value of a related attribute, or set of attributes, not necessarily in the same entity type

• the value for the duration attribute of the Lease Entity is calculated from:
  
  • the rentStart attribute and
  • the rentFinish attributes

Question 22

candidate, primary, composite key

Answer 22

Candidate Key - minimal set of attributes that uniquely identifies each occurrence of an entity type

• There might be more than one unique identifier, as such we need to define a primary key

Primary Key (PK) - A selected candidate key to uniquely identify each occurrence of an entity type

Composite Key - A candidate key that consists of two or more attributes; together are unique for each entity occurrence but not separately