---
# System prepended metadata

title: Database System

---

# 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

![image.png](https://hackmd.io/_uploads/S1eBSh1Qa.png)

* 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
![ER Diagram Notation 1](https://hackmd.io/_uploads/Hyl8SEPGT.png)

### 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
            :::
        ![image.png](https://hackmd.io/_uploads/S1jAii1Q6.png)
        
    * min max expression:
        ![image.png](https://hackmd.io/_uploads/rJ_UnsJ76.png)
    
    :::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