©2020-2024 ioDraw All rights reserved
There are two models for this problem ：
The first is to give you numbers , Groups of tables of letters or attributes , Let you use relational algebra
The second is to give “ Relational database schema ”, Let's use “ Relational algebraic expression ” Represents various queries , More difficult than the first
Random this year
Let's take a look at the concept ：
one , Relational database schema ： Abbreviated as R（A1,A2,~~~An）,R Is the relationship name ,A Is the property name
eg: company （ Company No , Company name , address ）
eg:S (S#,SNAME,AGE,SEX)( Some relationship models will be in pure English ）
two . Relational algebraic expression ：
hand over n, and U, difference -（ High school , slightly ）, Cartesian product x, choice σ, Projection Π, connect
Cartesian product ：
S:
T:
A
B
A
B
3
4
1
5
7
2
3
6
be SxT=
S.AS.BT.AT.B
3415
3436
7215
7236
Cartesian product is an operation in horizontal direction , Each row of the first table is connected to each row of the second table
choice σ：
grammar ：σ+ condition （ Table queried ）
eg： commodity （ commodity ID, Trade name , Price , brand , model , colour , manufacturer , Place of Origin ）
Query commodity information of Shanghai origin ：σ Place of Origin =‘ Shanghai ’ ( commodity )
Select a query that is horizontal , The result is all the attributes of the eligible product （ Remember to use single quotation marks for Strings ）
Projection Π：
eg： commodity （ commodity ID, Trade name , Price , brand , model , colour , manufacturer , Place of Origin ）
Inquire about goods whose origin is Shanghai ID, Trade name ：Π commodity ID, Trade name （σ Place of Origin =‘ Shanghai ’（ commodity ））
Query where projection is vertical , The result is a single attribute
connect ：
Connections are typically used for tables , Connected with natural connection , External connection , Left link , Right connection . The teacher won't embarrass us , There are only natural connection questions in the question bank , So let's just talk about the simplest natural connection
As shown above , Connection is to perform Cartesian operations on rows with the same attributes in two relationships
except ：
In the previous question R As dividend ,S As divisor
method ：（1） Find out first R,S Common properties in B,C
（2） And find the dividend R Common attribute values and S There are rows with the same attribute value ,
Namely R（2）：
ABC
a1b1c2
a1b2c3
a2b2c3
a1b2c1
（3） Remove the second step table R（2） Common attribute columns in B,C, Leave only A column
Namely
A
a1
a1
a2
a1
Simplified
Master these basic concepts , We can do the problem ,SQL Various query methods , So the answers to many questions are not unique .
example 1： True topic
solution ：1.
example 2,
solution ：
example 3,
solution ：
Technology