--- # System prepended metadata title: Database Theories tags: [必修, Database Theories, NTNU, CSIE] --- # Database Theories NTNU 資料庫理論 ##### [Back to Note Overview](https://reurl.cc/XXeYaE) {%hackmd @sophie8909/pink_theme %} ###### tags: `NTNU` `CSIE` `必修` `Database Theories` ## Score - Midterm Exam 25% - Final Exam 25% - Homework 20% - Team Project 25% ## Textbook ### Database System Concepts #### Ch.27 ~ 32 ## Schedule ![Schedule](https://i.imgur.com/0ENxDDp.png) ## Chapter 1 Introduction ### 1.3 View of Data ![view of data](https://i.imgur.com/bI3x2R9.png) #### Data Level - Physical Level - Logical Level - View Level #### Instances and Schemas ##### Schema structure ##### Instances value ### 1.4 Database Languages #### Data Manipulation Language (DML) - known as query language #### Data Definition Language (DDL) - DDL compiler generates a set of tables stored in a data dictionary - Data dictionary contains metadata - Database schema - Integrity constraints Primary key (ID uniquely identifies instructors) - Referential integrity - e.g. dept_name value in any instructor tuple must appear in department relation ### 1.6 Database Engine - Storage manager - the interface between - the low-level data stored in the database - the application programs and queries submitted to the system - Authorization and integrity manager 權限控管 - Transaction Manager 過程控管 - Recovery manager - ensures consistent (correct) of the database - state despite failures - Concurrency-control manager - controls the interaction among the concurrent transactions - ensure the consistency of the database. - File manager 倉庫管理 - Buffer manager 取貨控管 ## Chapter 2 Introduction to the Relational Model ### 2.3 Keys - Let $K \subseteq R$ - **superkey** - identify of unique tuple of possible relation - K is a superkey of R if values for K are sufficient to identify a unique tuple of each possible relation r(R - **candidate key**: if superkey $K$ is minimal - 只要沒有多餘的欄位就算，不一定是這個表裡面需要取最小的，所以一個表可以有多個 candidate key - **primary key**: one of the candidate keys - 通常是最常用來查表的 - 只有一個 - 別人的 primary key - **Foreign Key** - value in one relation must appear in another - 連接跟另一個表的關係 - Referencing relation - Referenced relation ### 2.6 The Relational Algebra ![Relational Algebra Table](https://i.imgur.com/TbS8aUa.png) ## Chapter 3 Introduction to SQL ## Chapter 4 Intermediate SQL ### Join operations | operations | meaning | | ---------------- | ------- | | Outer Join | | | Left Outer Join | | | Right Outer Join | | | Full Outer Join | | #### on #### using ### Create Views ### Transactions ### Integrity Constraints #### DDL 的參數設定 - Not null - check - casecade - 如果飲用的東西被刪掉就跟著被刪 - set nu ### Built-in Data Types in SQL ### Index ### Authorization ## Chapter 5 Advanced SQL ## Chapter 6 Database Design Using the E-R Model ## Chapter 7 Relational Database Design ## Chapter 27 Formal Relational Query Languages ### Relational Algebra - Procedural language - Six basic operators - selection: $\sigma$ - project: $\Pi$ - union: $\cup$ - set difference: – - Cartesian product: x - rename: $\rho$ - The operators take one or two relations as inputs and produce a new relation as a result ## Relational Model ### Data Definition Language(DDL) - Database schema - Integrity ### Data Manipulation Language (DML) - also known as query language ### Procedure Query Language (relational algebra) ### Embedded SQL ### Database Design - Design Process - Initial - 了解使用者需求 - Outcome: a specification of user requirements - Secomd phase(conceptual design) - 選擇一個資料模型 - 將需求轉換成 - Logical Design - Deciding on the database schema ### Transaction Management - atomicity - consistency - durability - 一旦交易成功，新的資料必須存在disk #### Recovery manager - ensure that the database remains in a consistent(correct) state despite failures - system faiure ### Two-tier and Three-tier architectures ![](https://i.imgur.com/kwtmvwf.png) ### History of Database Systems ## Entity-Relationship Model ### Example attribute tuple ### Binary relations ### Relations notations ## Relational Algebra - Procedural Language ### Selection operation - Notation: $\sigma_p(r)$ - $p$ selection predicate - Defined as $\sigma_p(r) = \{ t| t \in r \land p(t)\}$ ### Project Operation - Notation: $\Pi$ ### Union Operation ### Set different Operation ### Cartesian-Product Operation Composition ### Natural Join Operation 相同欄位會合併 #### Theta Join Operation $r\bowtie_\theta s=\sigma_\theta(r\times s)$ 相同欄位會保存 ### Outer Join Operation #### Left #### Right ### Aggregate Function {%hackmd @NTNUCSIE112/DB109-2_RelaAlge %} ## ER Model ### Design Process - Characterize the data needs - Conceptual design - scheme 要有什麼 - Specify functional requirements - 需要哪些功能性需求 - Implementation - Logical-design phase - Physical-design phase - Design alternatives - Redundancy - Incompleteness ## Attributes - Attribute types - Simple and composite ### Composite Attributes ### Strong Entity Set ### Weak Entity Set - Redundant: 關係的表在一邊都已經記錄到了 ### Design Issus - Use of entity set or attributes - Entity set - Attributes - 如果該資料有獨立需要管理的東西 ## Boyce-Codd Normal Form ## Ch.8 Relational Database Design ### Features of Good Relational Design decompose: split up Functional dependency lossy decomposition: cannot reconstruct the original relation 有損 lossless-join decomposition: 無損 ### Atomic Domains and First Normal Form first normal form: the domains of all attributes of R are atomic 沒有多筆資料在一個欄位 - functional dependencies - multivalued dependencies ### Functional Dependency Theory - closure: certain other functional dependencies - denote the closure of F by F+ - $F^+ \supseteq F$ ### Decomposition Using Functional Dependencies ### Decomposition Algorithms ### Decomposition Using Multivalued Dependencies ### Database-Design Process