Databases.
Topic 2
The relational model
The theoretical foundation of database computing.
Data types
Relationships
Constraints
Several existing database models are
-Relational model (We'll be discussing this)
-Hierarchical model
-Network model (these two are older models)
-Object-oriented model
In the relational model, everything is in a table. It's quite simple.
It's founded on a mathematical model, called the relational model.
The Object Oriented model hasn't taken off, because many companies have invested in it.
A. Data Structure.
a. Domain. A pool of valid values. In which each attribute can take a value from. Ie. Day {Wed, Thurs}.
b. Relation. A relation is composed of tuples. Means 'A table is composed of records'. Each record must have a unique id that can identify it.
c. Attribute.
Each value is atomic. There is only one single value in each 'cell'. A set of column names is called a schema for the relation.
Keys.
A student is identified by a student #, and that is a key.
This is a way of uniquely identifying each tuple.
Superkeys is a set of attributes that has at least as much information as is necessary to identify a record.
1. {Driver, Tram, Day}
2. {Driver, Route, Day} all these are unique, and so they are keys. They uniquely identify a record.
A universal addressor for three dimensional space would have to have something relative to it, time and position at the earth at some predefined point might be an idea.
B. Data Integrity.
Nullify, Cascade, Restrict.
C. Data Manipulation.
Relational Algrebra
This is about the manipulation of the data.
Take a set
Process it. There are 8 different things you would want to do.
Get the results (also a table)
The 8 process things
Projection
Pleection
Union
Intersection
Difference
Product
Join
Division
Combine these, and you can basically answer any question. You can retrieve any piece of information in the relational model. Pretty brilliant stuff.
In this subject, we focus on design and retrieval.
1. Projection
Selects specified attributes from a relation.
Students might have many columns, but projection is choosing which column you want.
π Name (Student)
"I would like to project the name of the student."
π
relation
You're taking slices of a table.
2. Selection
Like Projection, but cuts horizontally.
ie
Ó Relation
Ó Dept="Bsys" and Name="VB" (Subject)
Ó Balance>$100 (Account)
π Name (Ó Suburb = "Clayton" (Student))
"Get the name of all the students who live in Clayton".
You can store the results in variables.
R1 = xxx
What is the student number of glen of Clayton?
π ID (Ó Name ="glen" and Suburb ="Clayton" (Student))
3. Union
Deals with 2 tables.
R1 U R2
Duplicates will be removed, as the results have to be a relation, where there are no duplicate tuples.
Union will only work if both of the tables are, surprise surprise, Union compatible! Columns have to match up.
4. Intersection
Difference.
R1 - R2
The part of R1 that is not part of R2.
6. Cartesian Product
Or Multiply. The product of R1 and R2 is the table of all possible combinations. This doesn't have much meaning, but we can use it to calculate other meanings.
7. Join
Join operation is the combination of the product selection and projection operations.
ar
br
cs
rx
sy
tz
arx
brx
csy
R1 Join R2.
You can ignore the join condition when the join is based on the primary key/foreign key.
8. Division
Remember 'ALL'.
