Database Design

Database Design Sequence

1. Requirement Analysis

 Understand what data is to store, what app is to be built and which operation are most frequent 

2. Conceptual Design

High-level description of data closely matching how users think of the data 

3. Logical Design

 Conceptual design into logical database schema 

4. Schema Design

   Identify problems into current schema & refine

5. Physical Design*

     Logical database schema into a physical schema for a specified DBMS 

6. App & Physical Design

Conceptual Database Design

• Goal: Specify the database schema
• Conceptual Database Design: A technique for understanding and capturing business information requirements graphically

Entity-Relationship Diagrams (ER Diagrams)

• Entity: an individual object

• Entity Set (entity type): a collection of entity that shares the common properties or characteristics

• Attribute: describes one aspect of an entity set

Entity (实体) & Attribute (属性) 表示图形：

entity set 表示实体，例如students 学生
A1 到 An 表示基于该实体的Attribute(属性)，例如学生的学生号码，名字，年龄，出生日期等
A1底下的横线表示该 Entity Set 的 Primary Key 
图片 Reference (The University of Sydney, 2020, Lecture 2 Power Point)

• Domain: possible value of an attribute

     Can be simple and composite attribute 
       (Composition Attribute 例如学生的 first name 和 last name 可以 combine 为学生的 name Attribute)
     Can be sing-valued and multi-valued attribute 
       (Multi-valued Attribute 允许存储多个value在同一个Attribute内部，例如学生可能拥有多个不同的Phone Number 和 Email)
     Double ellipses for multi-valued attribute 
     Dot ellipses for derived attribute
        (Derived Attribute 是派生的Attribute，例如如果知道学生的 date of birth 可以推测派生出学生的 age，age 就是 students 的 Derived Attribute)
  

• Relationship: relates two or more entities

• Relationship Type (Relationship Set): Set of relationships

   Diamond represents relationship type 
  

Relationship Between Student and Course:

 学生 Enrol 到不同的课程中，这个图展示了 Student 和 Course 的两个不同 Entity Set 之间的关系
 图片 Reference (The University of Sydney, 2020, Lecture 2 Power Point)

Relationship 的类型：

   Employee 可能具备管理者和普通员工，所以 Employee 的实体集可以管理其自身
   两个实体集之间可以具备某种关系
   三个不同的实体集也可以具备同一个关系
   关系可以具备某个Attribute，例如学生（Entity Set）可被记录到某一天 Enroll 到某个课程（Entity Set）中
   图片 Reference (The University of Sydney, 2020, Lecture 2 Power Point)

• Week Entity Type: An entity type that does not have a self-contained primary key

• Generalization: low-level of entity will inherence the attribute of high-level entity

Generalization 的表示方法：

    Child 会继承 Parent Entity Set 所存在的全部 Attribute。
    IsA 是用来展示 Generalization 的 Relationship 名称。
    Child 同样可以拥有自己特殊的 Attribute。
    图片 Reference (The University of Sydney, 2020, Lecture 2 Power Point)

Logical Design

赞等更新

Schema Design

暂等更新

Physical Design

暂等更新

Relational Data Model

Concept: A database is a collection of one or more relations, where each relation is a table with rows and columns

SQL Language

DDL (Data Definitaion Language)

DDL: The subset of SQL that supports the creation, deletion and modification of tables.

SQL Code To Create Table:

 CREATE TABLE name ( list-of-columns);

Based Data Type to Support DDL Query:

 图片 Reference (The University of Sydney, 2020, Lecture 3 Power Point)

SQL Code To Delete Table:

 DROP TABLE name;

SQL Code To Change Table:

 ALTER TABLE name ADD COLUMN … | ADD CONSTRAINT…| …