Unit 1 and 2 - ER

Question 1

What are the three things we need in a database?

Answer 1

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)

Question 1

What’s a database management system (DBMS)?

Answer 1

Software that creates databases and queries the stored data
DBMS Examples: MySQL, Oracle, MongoDB

Question 2

What’s a data model?

Answer 2

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

Question 3

Data model vs. semantic data model
What’s a widely used semantic model?

Answer 3

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

Question 4

Types of Attributes: simple (atomic) vs. composite

Answer 4

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.

Question 5

Types of Attributes: single-value vs. multi-value

Answer 5

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

Question 6

Can an attribute be atomic and multi-value?

Answer 6

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

Question 7

Key (aka relational key)

Answer 7

Attribute or set of attributes that uniquely identifies the individual entities within the entity set.
Key attribute cannot be null!

Question 8

Is it possible to have duplicate tuples (rows) in a relation (table)?

Answer 8

No. That’s why you can use attributes (relational keys) to UNIQUELY identify a tuple/ row.

Question 9

Candidate key

Answer 9

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.

Question 10

Primary key

Answer 10

ONE of the candidate keys, chosen by database designer
The candidate key that is selected to identify tuples uniquely within
the relation.

Question 11

Instance

Answer 11

Snapshot of relationship set at a given time

Question 12

Partial vs. total participation

Answer 12

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)

Question 13

(min, max) notation

For example, each employee can manage 1 department at most, and department must be managed by 1 employee

Answer 13

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

Question 14

Strong vs. weak entity

Answer 14

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)

Question 15

How is a weak entity linked to other entites?

Answer 15

It’s linked to its strong entity via an ‘identifying relationship’ (two diamonds)

Question 16

How do you identify a strong vs. weak entity?

Answer 16

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)

Question 17

Partial key

Answer 17

Uniquely differentiates between weak entities

Question 18

Primary key of a weak entity

Answer 18

Composite key formed from the primary key of the strong entity + partial key of the weak entity

Question 19

Foreign key

Answer 19

Taken from another entity set (e.g., when primary key of strong entity is used as part of primary key of weak entity)

Question 20

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?

Answer 20

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.

Question 21

Generalization

Answer 21

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.

Question 22

Specialization

Answer 22

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.