Cing Sian Dal

Jun 25, 2021

4 min read

Notes on Database Systems

The fundamental concepts necessary for designing, using and implementing database systems. It includes the fundamentals of database modeling and design, relational theory and the Structured Query Language (SQL).

Status: still writing…(will end on September, no longer working on it)

Week 1

  • Relational algebra is a set of operators to manipulate relations: Union, Intersection, Difference, Cartesian product, Select, Project, Join, Divide.
Union
Intersection
Difference
Cartesian Product
Select
Project
Join
Division
  • Relational calculus represents an alternative to relational algebra as a candidate for the manipulative part of the relational data model.
  • The difference between Relational Algebra and Relational Calculus is that calculus formation is descriptive (လိုရင်း ဒဲ့တောင်း) whereas the algebraic one is prescriptive (တစ်ခုပြီးမှ တစ်ခုချင်းစီတောင်း).
  • Tuple relational calculus, for example, QUEL from INGRES
  • Domain relational calculus, for example, Query-By-Example (QBE), ILL
  • An attribute is a characteristic of data.
  • A domain is a set of atomic values that are all of the same type. A value is the smallest unit of data in the relational model.
  • A tuple is an ordered set of values that describe data characteristics at one moment in time.
  • A relation degree is equivalent with the number of attributes of that relation.
  • Relation cardinality is equivalent with the number of tuples of that relation.
  • A database schema is a formal description of all the database relations and all the relationships existing between them.
  • A candidate key (surrogate key, artificial key) is a unique identifier for the tuples of a relation. By definition, every relation has at least one candidate key (the first property of a relation). In practice, most relations have multiple candidate keys. It is said to be a candidate key of R if and only if it satisfies the following two time- independent properties: uniqueness, minimality.
  • A foreign key is an attribute (or attribute combination) in one relation R2 whose values are required to match those of the primary key of some relation R1 (R1 and R2 not necessarily distinct).
  • The entity integrity constraint says that no attribute participating in the primary key of a relation is allowed to accept null values.
  • The referential integrity constraint says that if a relation R2 includes a foreign key FK matching the primary key PK of other relation R1, then every value of FK in R2 must either be equal to the value of PK in some tuple of R1 or be wholly null (each attribute value participating in that FK value must be null).
  • A semantic integrity constraint refers to the correctness of the meaning of the data.
  • A domain constraint implies that a particular attribute of a relation is defined on a particular domain. A domain constraint simply states that values of the attribute in question are required to belong to the set on values constituting the underlying domain.
  • A null constraint specifies that attribute values cannot be null.
  • A unique constraint specifies that attribute values must be different.
  • A check constraint specifies a condition (a predicate) on a relation data, which is always checked when data is manipulated.

Week 2

  • Information (ကုန်ကြမ်းမှ ရလာတဲ့ သတင်းအလက်အလက် အသစ်):
  • Model (မြေပုံ)
  • Modeling (မြေပုံဆွဲတာ)
  • Database Model (database စနစ်ထဲနဲ့ ကိုင်ညီအောင် ဆွဲရတော့မဲ့ မြေပုံ): A database model is an integrated collection of concepts for data description, data relationships, data semantics, and data constraints.
  • Conceptual data model ( စိတ်ကူးထဲကအတိုင်း ရေးဆွဲထားတဲ့ ပုံစံ ) — the outcome of a process that identifies the relations, constraints, keys, and relationships that are required to provide a solution to a particular data management problem. Conceptual modeling emphasizes information as seen by the business world. It identifies entities and relationships of the business. Conceptual data model is a mental image of a familiar physical object and are not specific to a database.
  • Logical modeling is based on a mathematical model.
  • Physical modeling implements a given logical model specifically to a particular database product and version.
Difference between conceptual, logical and physical models

Relationship

Relationship

Cardinality

  • zero / one to many (0:n, 0:m)
  • one to many (1:m, 1:n)
  • one and only one (1:1)
  • many to many (m:n)

Cardinality Optionality

  • mandatory
  • optional
  • zero or one
  • one
  • unary relationship: Librarian ထဲက ကိုယ် Supervisor Libraian ကို ပြန်ထည့်ရတာ။
Unary relationship