# Database System

:::info
:bulb:
:::

# Chapter 5 Relational Data Model& Relational Database Constraints
> [color= pink]
> Relational Model Conceptes
> Relational Model Constraints
> Relaitonal Databse Schemas
> Update Operations& Constraint Violations

## Relational Model Conceptes
* a relation is a ++mathematical concept++ based on the ideas of **sets**
* basically is the table version of EER diagram

| Informal Terms| Formal Terms|
|---|---|
| Table Relation| Column Header Attribute|
| All possible Column Values| Domain|
| Row| Tuple|
| Table Definition| Schema of a Relation|
| Populated Table| State of the Relation|

### Order of Relations
* tuples
* Not required
* attributes& values
* required
> however, ordering is not required in some expression forms
> * t = {<name, "John">, <SSN, 123456789>}
> * it is **self- describing**

### Values in Tuples
* every values are considered **atomic, indivisible**
* must follow the rules of domain of certain attribute

#### Null Values in Tuples
* unknow
* not available (not exist)
* inapplicable (undefined)
* , etc

:::warning
Design Choice
* **Null Values** should be generally avoided in desogn of a database since it comes with various meaning
:::

## Relational Model Constraints
[Check this out](https://youtu.be/uPOGPL2C0_8?feature=shared)

## Relaitonal Databse Schemas
## Update Operations& Constraint Violations

# Chpater 4 EER
> [color= pink]
> * ER Diagrams (previous chapter)
> * Subclasses& Superclasses
> * Specialization/ Seneralization
> * Categories, aka UNION types
> * Attribute and Relationship Inheritance
> * Constraints on Specialization/ Generalization

## Subclass& Superclass
> aka is-a relationship
* when a entity type have several **meaningful subgroups**, we should create subclass& superclass
* both {ENGINEER} and {MANAGER} are *subclasses* of the *superclass* {EMPLOYEE}
> * ENGINEER is a EMPLOYEE
> * MANAGER is a EMPLYEE
* subclasses **inherit all relationships& attributes** from superclass


* sublasses may have or may Not have **distinguishing characteristics (attributes)** from its superclass
* aka **specialization**
* once may subclasses have **numbers of attributes in common**, we should create a superclass
* aka **generalization**

:::warning
Design Choices
* if subclass has few specific attributes and no specific relationships, that can be merge into the superclass
* Union types should be generally aboided
* Make a proper superclass and specialize it into few subclass of use
:::

### Constraints of Specialization& Generalization
* Disjoitness Constraint
* **subclasses** of specialization must be **Disjoint**
* an entity can be **only one** subclass of the specialization
* a {PERSON} can be either a {MAN} or a {WOMAN}
:::info
notation: d
:::
* if Not Disjoint, that is **Overlapping**
* an entity can be **multiple** subclass of the specialization
* a {PERSON} can be a {EMPLOYEE} and a {COSTUMER} at the same time
:::info
notation: o
:::
* Completeness Constraint
* **Total: every entities of superclass** have to be the members of subclasses
* total inherent
:::info
notation: double line
:::
* **Partial:** some entities of supercalss not belong to any of subcalsses
* partial inherent
:::info
notation: single line
:::
:::success
* we have four types of specialization& generalization:
* Disjoint, total
* Disjoint, partial
* Overlapping, total
* Overlapping, partial
:::

### Union Type
> aka Category
* a single subclass- superclass relationship with more than one superclass
:::danger
SKIPPPPPPP
:::

# Chapter 3 ER

## Conceptual Design
> [color= pink]
> * ER Diagrams (This chapter)
> > Entity Relationship
> * EER (CH4)
> > Enhanced Entity Relationship
> * Design Tools& Large Scale Design
> * UML Class Diagrams
> > Unified Modeling Language

## ER Model Concepts
> [color= pink]
> * Entities
> * Attributes
> * Relationships

### Entities 實體
* specific **Things** or **Objects** in mini- world
> mini- word: field of interest
:::info
notation: ENTITY TYPES
:::

#### Weak Entity
* an entity that **Does not have a Key Attribute**
* have to identify **depend** on other entity type

### Attributes 屬性
* **Properties** used to describe an entity
* has certain **Value Set** for each attribution
>value set: data type
:::info
notation: AttributeName
:::

#### Key Attribute
* a attribute which must have a unique value to refer to an entity
* may be composite
* may have more than one
:::info
notation: ++KeyAttributeName++
:::

#### Attributes of Relationships
* attributes which is valid once the relationship start
* 1:N Relationships
* transffer to the attribute into N side
* M:N Relationships
* remain on the relationships

#### Types of Attributes
* simple
* Sex
* composite 合成
* NAME(FirstName, MiddleName, LastName)
* multi- valued
* {Color} of a CAR
* {PreviousDegrees} of a STUDENT

> composite and multi- valued attributes may be combine together
> * {PreviousDegrees (College, Year, Degree, Field)}

#### Value Sets of Attribute
> Domain of Value of Attribute
* $A : E \rightarrow P(V)$
> * A: attribute
> * E: entity type
> * P: power set (all possible subsets)
> * V: value set (domain)

## ER Diagrams Notation

### Objects in ER Diagrams

### Relationships
* connect two or more (degrees of) distinct entities with a special meaning
* Employees Work_On Projects, where Work_On is a *binary relationship*

:::info
notation: Relation_Type
:::

#### Attributes of Relationships
[Here you go](https://hackmd.io/SAkwNiLeSNeoLJA9GimAGQ?view#Attribute-of-Relationship)

#### Relation Set/ Relationship Instance
* **current state** of a relationship type

#### Constraints on Relationships
> aka Ratio Constraints
* Cardinality Ratio
> specified the **maximum** participation
* 1:1
* 1:N/ N:1
* M:N

* Participation Constraint
> aka Existence Dependency Constraint
> >
> spcified the **minimum** participation
* **mandatory強制的 participation**, existence dependent
* line expression:
* total
:::info
notation: double line
:::
* partial
:::info
notation: singel line
:::

* min max expression:

:::success
single_line when (min, max) == (0, any_number),
else double_line
:::

#### Recursive Relationship Type
> aka Self- referencing Relationship Type
* A relationship type between the *same participating entity type* in *distinct roles*
* EMPLOYEE Supervise (another) EMPLOYEE (maybe a manager)
* role names are required to be displayed in an ER diagram in order to distinguish participants

>
> ## 20230914 W1
>
> ### General
>
> ### Chapter 1
> #### database applications
> * traditional applications
> * number & text
> * more recent applicantions
> * multi-media
> * biological/ genome
> * geographic
> * data warehouse
> * big data storage systems
>
> #### NOSQL system (Not Only SQL)
>
> #### basic definitions
> * database
> * data
> * know facts that can be recorded and have an implicit meaning (means somehow useful)
> * mini-world, aka UoD, 論域
> * some parts of real world data that devolopers care about
> * database management system, aka DBMS
> * a system to create/ write/ read datas in database
> * even back-up the database
> * database system
> * DBMS+ data+ app...
>
> #### typical DBMS function
> * particular database in terms of its **data types**, **structures**, **constraints 限制**
> * CRUD
> * create
> * retrival
> * update
> * delete
> * processing& sharing
> * lock when operating CRUD on particular data
> * send data to other systems
> * make sure database is up-to-date and correct
> #### second class missed
>
> ## 20230913 W1
>
> pass