Unit 1 and 2 - ER Flashcards
What are the three things we need in a database?
Entities (students, faculty, courses), attributes of entities (student name, course number, faculty webpage), and relationships between entities (students’ enrollment in a course, faculty teaching courses)
What’s a database management system (DBMS)?
Software that creates databases and queries the stored data
DBMS Examples: MySQL, Oracle, MongoDB
What’s a data model?
Collection of high-level data description constructs (tables, graphs, trees) that hide low-level details about physical storage of data.
Common data model = relational data model
Data model vs. semantic data model
What’s a widely used semantic model?
Semantic data model = more abstract, good initial description, starting point
Translated into a data model that DBMS implements
Widely used semantic data model = ER (entity-relationship) model
Types of Attributes: simple (atomic) vs. composite
Attribute describes an entity (student name, ID, etc.)
Simple/atomic attribute: cannot be further divided - e.g., student ID
Composite attribute: made up of 2 or more simple attributes - e.g., name is made up of first, middle, last name.
Types of Attributes: single-value vs. multi-value
Single-value: an attribute that holds maximum one/single value - e.g., employee’s SSN, student ID, GPA
Multi-value: an attribute that has more than one value - e.g., two phone numbers, multiple email addresses
Can an attribute be atomic and multi-value?
Yes! For example, phone number is atomic b/c you don’t split it out more but multi-value because you can have a home and cell phone number
Key (aka relational key)
Attribute or set of attributes that uniquely identifies the individual entities within the entity set.
Key attribute cannot be null!
Is it possible to have duplicate tuples (rows) in a relation (table)?
No. That’s why you can use attributes (relational keys) to UNIQUELY identify a tuple/ row.
Candidate key
A key that has the minimal number of attributes whose values uniquely identify the individual entity
We can have more than one candidate key per entity!
Candidate keys have two properties:
Uniqueness. In each tuple of R, the values of K uniquely identify that tuple.
Irreducibility. No proper subset of K has the uniqueness property.
Primary key
ONE of the candidate keys, chosen by database designer
The candidate key that is selected to identify tuples uniquely within
the relation.
Instance
Snapshot of relationship set at a given time
Partial vs. total participation
Total - when EACH entity in the entity set is involved in the relationship (represented by double line)
Partial - when not all entities are involved in the relationship (represented by single line)
(min, max) notation
For example, each employee can manage 1 department at most, and department must be managed by 1 employee
Each entity in the entity set must be linked to at least min number of entities in the relationship, and at most max.
Dept – (1,1) – manages – (0,1) – Employee
Strong vs. weak entity
An entity is strong if its existence does not depend on another entity.
Weak - existence depends on existence of another entity. (represented by two triangles)
How is a weak entity linked to other entites?
It’s linked to its strong entity via an ‘identifying relationship’ (two diamonds)
How do you identify a strong vs. weak entity?
Strong entity - uniquely identifiable thru its primary keys
Weak entity - uniquely identifiable ONLY thru the identifying relationship that it has w/ strong entity (aka NO PRIMARY KEY* for weak entity)
Partial key
Uniquely differentiates between weak entities
Primary key of a weak entity
Composite key formed from the primary key of the strong entity + partial key of the weak entity
Foreign key
Taken from another entity set (e.g., when primary key of strong entity is used as part of primary key of weak entity)
Conceptual Hierarchical Design: let’s say we have hourly employees and contract employees, but both have same attributes as regular employees. How to say this?
Hourly-Emp ISA (“is a”) Employee
AKA attributes of employee are inherited BY hourly-emp
Contract-emp ISA Employee.
Employee is super-class.
Hourly-Emp and Contract-Emp are sub-classes.
Generalization
Identifying common characteristics of a collection of entity sets and creating a new entity set with those characteristics.
For example, you notice that hourly and salaried employees are both employees, so you create a NEW entity set called Employee.
These sets were generalized to ‘Employee’
Subclasses defined first, then superclass.
Specialization
Superclass defined first, then subclasses.
You define ‘student’ first, in general, then you define grad, undergrad, and phd (the specialities)
Student specialized into grad, undergrad, and phd.
