Database Design & Relational Model Flashcards
Core Database Design Steps
Conceptual Design, Logical Design, Physical Design
Conceptual Design
o Construct a description of the information used in an enterprise
o Focus on documenting customer intention, disregard technology
• Logical Design
o Construct a description based on a specific data model (e.g relational)
o Focus on abstract tech, disregard implementation
• Physical Design
o Describe implementation using a particular DBMS, file structures, indexes, security…
Key constraints
It is how many entities take part in a given relationship(1:1, 1:many, many:1)
Entity
Real World object distinguishable from other objects
Entity Set
Collection of similar entities. All entities on a entity set have the same attributes
Attribute Domain
Data Type of the attribute
Relationship
Unique association among two or more entities
Total Participation Constraint
Entity set E is total with relation to set R if all entities in E participates in R.
Partial Participation Constraint
Entity set E is partial with relation to set R, if some E entities does not participate in R
ISA Hierarchy
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
Aggregation
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.
Ternary Relationship
(cost) |
| | |
|Projects – Sponsors —Depts.|
——————————————–
|
monitors - (since)
|
Employees
Cardinality
Number of rows of a table
Degree/Arity
Number of columns in a table
Attributes
Sid, name, login, gpa etc…
Translating ISA Hierarchies to Relations Alternative 1
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
Translating ISA Hierarchies to Relations Alternative 2
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
Translating ISA Hierarchies to Relations Alternative 3
Alternative 3: One big relation
o 1 relation: Employees
Advantages and Disatvantages ISA Hierarchies
Advantage: Clean results
Disadvantage: Centralization of Power
