The structure of the circle of friends really gives me a headache - how to implement it in a multi-layer architecture and how to write the script; And the ghost virtual machine, kind of learning is not good at self castration. Do it from the beginning, then.
Each image has its own URL: uniform resource location address protocol: / / hostname [: Port] / path / [; parameters] [? Query] #fragment. Hostname uses DNS domain name or IP address, and port can be saved.
CDN content delivery network: I don't know why.
The basic structure of my circle of friends: Publishing {content, comments, likes}, users, timeline.
Implementation of batch processing:
Dynamic: Publishing dynamic
Comments:
- Post comments
- Delete comment
User:
- User registration
- User logout
View: - When users search for friends, they search by name, which involves the problem of the same name.
Chapter 3 content relational database standard language
SQL Structured Query Language
Features: 1. Comprehensively define DDL, operate DML and control DCL functions; 2. Highly non procedural: the access path is automatically completed by the system; 3. Set oriented operations: relational algebra, set algebra.
Out of architecture mode – view, base table, mode – base table, in architecture mode – storage file
data type
Relationship between schema and basic table: the search path contains a list of schemas, similar to the working environment.
Mode 1 CREATE SCHEMA *** AUTHORIZATION *** CASCADE/RESTRICT{} ;
Mode II CREATE TABLE ***.ss(***);
Table RDBMS stores schema information and integrity constraints into the data dictionary.
Index improves query efficiency: RDBMS data access is based on attribute value (rather than pointer) query, and the mapping from logical layer to physical layer is realized through index. B + tree: dynamic balance / HASH index: fast.
The view is a virtual table
Chapter V content database integrity
What does integrity do- Correctly reflect the displayed data
Factors of data distortion?
Internal causes: 1. input error: mobile phone number, ID number format, nickname repetition; 2. System processing: the content is overwritten and is not set. Check whether the user's nickname can be repeated;
-External cause: 3. System failure: you may have deleted a table by mistake; 4. Malicious destruction: stealing numbers;
Methods: 1. Operation authority management; 2. Content integrity audit;
Integrity constraint does not speak human language definition: for relationship pattern R, specific relationship R
∈
\in
∈ r satisfies the constraint condition f (R); Scope: attribute, tuple, table and multi table. The smaller the scope, the lower the cost:.
definition- DDL definition language
check- insert... Check rules for changing database operations; Consider cost.
violation handling- Reject NO ACTION, CASCADE cascade, set to null, set to null
responsible- RDBMS automatically builds indexes for full table scanning.
Take a look at each: (use "integrity constraint named clause" to facilitate modification!!!)
1. Entity integrity: define column level PRIMARY KEY, NOT NULL; Table level PRIMARY KEY(r0,r1); Violation rejection master code value is not unique and attribute value is empty.
2. Reference integrity: define the FOREIGN KEY of the reference table and the primary code of the cross reference of the referenced table PREFERENCES; In case of violation, all three can be used and rejected by default; You need to define whether the external code is null. For example, unexpectedly... For example, if a student has not assigned a major, the major number is null.
3. User defined integrity: UNIQUE, NOT NULL, CHECK; Violation of refusal;
From the above, let's preliminarily establish the table:
Consideration: 1. In order to improve efficiency, do you need to set sorting or index fields for each table? 2. Random generation? The system generates in order? 3. What is the difference between domain and named clause? 4. How to choose an address when registering?
1. Database building
CREATE DATABASE pyq DEFAULT CHARACTER SET=utf8mb4 COLLATE utf8mb4_unicode_ci; USE pyq
2.1 user table
| surface | user | Primary key | Foreign key | sort field | Index field |
|---|---|---|---|---|---|
| users | DBA | uid | / | uid | uid |
| Attribute name | type | Default value | entity | reference | User defined |
| uid | varchar(8) | The system generates in order | PRIMARY KEY | REFERENCES | / |
| unam | varchar(20) | wxid + system generation | / | come from | NOT NULL |
| uloc | vachar(20) | / | / | / | set selection |
| usex | char(2) | / | / | Male, female, none | |
| ubir | datetime | / | / | 1900-01-01 00:00:00 to 2021-12-31 23:59:59 | |
| upwd | varchar | 12345678 | / | / | NOT NULL, len>=8 |
CREATE TABLE users
{
uid VARCHAR(8) PRIMARY KEY
unam VARCHAR(20) NOT NULL
uloc VARCHAR(20)
usex CHAR(2)
CONSTRAINT csex CHECK(usex in ('female','male','nothing')
ubir DATETIME
CONSTRAINT cbir CHECK(ubir BETWEEN 1900-01-01 00:00:00 AND 2021-12-31 23:59:59)
upwd VARCHAR NOT NULL
CONSTRAINT cpwd CHECK(len(upwd)>=8)
UNIQUE INDEX users_idx(uid ASC)
};
2.2 friends list
| surface | user | Primary key | Foreign key | sort field | Index field |
|---|---|---|---|---|---|
| friends | DBA | / | fid1,fid2 | fid1 | fid1 |
| Attribute name | type | Default value | entity | reference | User defined |
| fid1 | varchar(8) | / | / | Foreign key, refer to users.uid | NOT NULL |
| fid2 | varchar(8) | / | / | Foreign key, refer to users.uid | NOT NULL |
CREATE TABLE friends
{
fid1 VARCHAR(8) NOT NULL
fid2 VARCHAR(8) NOT NULL
FOREIGN KEY(fid1) REFERENCES users(uid)
FOREIGN KEY(fid2) REFERENCES users(uid)
INDEX friends_idx(fid)
};
Isn't there a unique index that can be used here?
2.3 dynamic table
| surface | user | Primary key | Foreign key | sort field | Index field |
|---|---|---|---|---|---|
| moments | DBA | mid | uid | mid | mid |
| Attribute name | type | Default value | entity | reference | User defined |
| mid | varchar(16) | The system generates in order | Primary key | / | / |
| muid | varchar(8) | / | / | Foreign key, refer to users.uid | Not empty |
| mtim | datetime | / | / | / | Not null, 2010-01-01 00:00:00 to 2025-12-31 23:59:59 |
| mcon | text | / | / | / | Not empty |
| mpic | varchar | / | / | / | / |
CREATE TABLE moments
{
mid VARCHAR(16) PRIMARY KEY
muid VARCHAR(8) NOT NULL
mtim DATETIME NOT NULL
CONSTRAINT ctim CHECK(mtim BETWEEN 2010-01-01 00:00:00 AND 2025-12-31 23:59:59)
mcon TEXT NOT NULL
mpic VARCHAR
FOREIGN KEY(muid) REFERENCES users(uid)
UNIQUE INDEX moments_idx(mid)
};
2.4 timeline table
| surface | user | Primary key | Foreign key | sort field | Index field |
|---|---|---|---|---|---|
| timeline_of_uid | DBA | / | mid | / | mid |
| Attribute name | type | Default value | entity | reference | User defined |
| tmid | varchar(16) | / | / | Foreign key, refer to moments.mid | NOT NULL, UNIQUE |
| tsta | tinyint | / | / | / | 0 or 1 |
CREATE TABLE timeline_of_uid
{
tmid VARCHAR(16) NOT NULL UNIQUE
tsta TINYINT NOT NULL
CONSTRAINT csta CHECK(tsta in VALUES(0, 1))
FOREIGN KEY(tmid) REFERENCES moments(mid)
UNIQUE INDEX timeline_idx(tmid)
};
2.5 comment form
| surface | user | Primary key | Foreign key | sort field | Index field |
|---|---|---|---|---|---|
| comments | DBA | cid | cmid | cid | cmid |
| Attribute name | type | Default value | entity | reference | User defined |
| cid | varchar(8) | The system generates in order | Primary key | / | / |
| cmid | varchar(16) | / | / | Foreign key, refer to moments.mid | NOT NULL |
| cuid | varchar(8) | / | / | Foreign key, refer to users.uid | NOT NULL |
| creuid | varchar(8) | / | / | Foreign key, refer to users.uid | / |
| ctim | datetime | / | / | / | Not null, 2010-01-01 00:00:00 to 2025-12-31 23:59:59 |
| ccon | tinytext | / | / | / | Not empty |
CREATE TABLE timeline_of_uid
{
cid VHARCHAR(8) PRIMARY KEY
cmid VARCHAR(16) NOT NULL
cuid VARCHAR(8) NOT NULL
creuid VARCHAR(8) NOT NULL
ctim DATETIME NOT NULL
CONSTRAINT tim CHECK(ctim BETWEEN 2010-01-01 00:00:00 AND 2025-12-31 23:59:59)
ccon TINYTEXT NOT NULL
FOREIGN KEY(cmid) REFERENCES moments(mid)
FOREIGN KEY(cuid) REFERENCES users(uid)
FOREIGN KEY(creuid) REFERENCES users(uid)
UNIQUE INDEX timeline_idx(cmid)
};
2.6 praise form
| surface | user | Primary key | Foreign key | sort field | Index field |
|---|---|---|---|---|---|
| likes | DBA | / | lmid | / | lmid |
| Attribute name | type | Default value | entity | reference | User defined |
| lmid | varchar(16) | / | / | Foreign key, refer to moments.mid | NOT NULL |
| luid | varchar(8) | / | / | Foreign key, refer to users.uid | NOT NULL |
| ltim | datetime | / | / | / | Not null, 2010-01-01 00:00:00 to 2025-12-31 23:59:59 |
CREATE TABLE likes
{
lmid VARCHAR(16) NOT NULL
luid VARCHAR(8) NOT NULL
ltim DATETIME NOT NULL
CONSTRAINT ctim CHECK(ltim BETWEEN 2010-01-01 00:00:00 AND 2025-12-31 23:59:59)
FOREIGN KEY(lmid) REFERENCES moments(mid)
FOREIGN KEY(luid) REFERENCES users(uid)
UNIQUE INDEX likes(lmid)
};