组织用户5691
Decent Database Design
输入“/”快速插入内容
Decent Database Design
Decent Database Design
用户5691用户56912024年6月14日修改
Relational Database
Basis
•
Organizes data into tables
•
Rows are also called records or tuples (horizontal things)
•
Columns are also called attributes (vertical things)
•
Duplicates are forbidden: use a col or a set of cols to differentiate rows
•
Primary key
•
Entity Relationship Diagram (ER Diagram)
•
Data Definition Language (DDL)
◦
Create schemas, constraints, etc.
◦
Create, alter, drop
◦
To show DDL for a table: \d [table_name]
•
Data Manipulation Language (DML)
◦
Aka query langauge
◦
Select, insert, delete, update
•
SQL: Structured Query Language
•
API: Application Program Interface, allows SQL queries to be sent to a db system
Postgres: a DB system
•
Server-side program: the db mgmt system itself
•
Client-side program: the client tools to manipulate the server via networks
Relational Model
Definitions
•
are attributes
•
is a relation schema
◦
e.g. instructor = (ID, name dept_name, salary)
•
r(R) is a relation instance r of schema R
•
DB instance is a snapshot of data in db at given time
•
Element t of r is called a tuple, by a row in table
◦
Tuples are inordered
•
Domain: set of allowed values of the attr
•
Atomicity: being indivisible
•
We compare them to that in programming languages
◦
Relation - variables
◦
Relation schema - variable types
◦
Relation instance - values stored in the variable
Database schema
•
Database schema is the logical structure of the database
◦
Contains a set of relation schemas and a set of integrity constraints
•
Database instance is a snapshot of the data in the database at a given instant in time
Nullity
•
A special marker for "unkown"
•
Null != 0, null != false, it is not a value
•
Sth + null = null
•
For judging, we use where runtime is null but we cannot use = null anywhere
◦
e.g. (col > NULL) -> NULL, (col = NULL) -> NULL
•
But sometimes it can be not null
◦
false and null -> false because that is for sure
◦
true or null -> true also because that is for sure