Database Design & Relational Model

Question 1

Core Database Design Steps

Answer 1

Conceptual Design, Logical Design, Physical Design

Question 2

Conceptual Design

Answer 2

o Construct a description of the information used in an enterprise
o Focus on documenting customer intention, disregard technology

Question 3

• Logical Design

Answer 3

o Construct a description based on a specific data model (e.g relational)
o Focus on abstract tech, disregard implementation

Question 4

• Physical Design

Answer 4

o Describe implementation using a particular DBMS, file structures, indexes, security…

Question 5

Key constraints

Answer 5

It is how many entities take part in a given relationship(1:1, 1:many, many:1)

Question 6

Entity

Answer 6

Real World object distinguishable from other objects

Question 7

Entity Set

Answer 7

Collection of similar entities. All entities on a entity set have the same attributes

Question 8

Attribute Domain

Answer 8

Data Type of the attribute

Question 9

Relationship

Answer 9

Unique association among two or more entities

Question 10

Total Participation Constraint

Answer 10

Entity set E is total with relation to set R if all entities in E participates in R.

Question 11

Partial Participation Constraint

Answer 11

Entity set E is partial with relation to set R, if some E entities does not participate in R

Question 12

ISA Hierarchy

Answer 12

The purpose of ISA hierarchies is to add attributes specific to a subclass and also identify specific entities that participate in a relationship. There’s a superclass and numerous subclasses

Question 13

Aggregation

Answer 13

Aggregation is a relationship which involves entity sets and a relationship set. With aggregation, we treat a relationship as an entity set for purposes of participation in other relationships.

Question 14

Ternary Relationship

Answer 14

(cost) |
| | |
|Projects – Sponsors —Depts.|
——————————————–
|
monitors - (since)
|
Employees

Question 15

Cardinality

Answer 15

Number of rows of a table

Question 16

Degree/Arity

Answer 16

Number of columns in a table

Question 17

Attributes

Answer 17

Sid, name, login, gpa etc…

Question 18

Translating ISA Hierarchies to Relations Alternative 1

Answer 18

Alternative 1: Separate relation per entity set
o 3 relations: ex. Employees, Hourly_Emps, Contract_Emps
o Every employee is recorded in Employees
o Must delete Hourly_Emps tuple if reference Employees tuple is deleted
o Queries involving all employees are easy, those involving just Hourly_Emps require a join to get some attributes

Question 19

Translating ISA Hierarchies to Relations Alternative 2

Answer 19

Alternative 2: Relations only sets with instances
o 2 relations: Hourly_Emps and Contract_Emps
o Hourly_Emps: ssn, name blab la bla
o Each employee must be in one of these two subclasses, so they both have employees’ attributes as their own

Question 20

Translating ISA Hierarchies to Relations Alternative 3

Answer 20

Alternative 3: One big relation

o 1 relation: Employees

Question 21

Advantages and Disatvantages ISA Hierarchies

Answer 21

Advantage: Clean results
Disadvantage: Centralization of Power