We got into the Relational Data Model today in Manoj Kathpalia’s second week lecture. I found this lecture much more interesting and challenging than last weeks which was great. Until now the terminology I had used for Relational Databases was: Table, Column, Row. Today I discovered that the RDBMS industry is engaging in mass duplication of data by having to remember the following alternate names:
- Table – > Relation, Entity
- Column -> Attribute / field
- Row -> Record set / tuple / entry
Why everyone doesn’t just use Table, Column, Row is beyond me… We covered some properties of Relations, Primary, Alternate and Foreign Keys. I was a little confused by the Relation Languages segment where Data Definition Language [DDL] and Data Manipulation Language [DML] were outlined. Does SQL encompass both DDL and DML? If so why separate them? Further investigation reveals DDL, ‘Initially it referred to a subset of SQL, but is now used in a generic sense to refer to any formal language for describing data or information structures, like XML schemas.‘ source: http://en.wikipedia.org/wiki/Data_Definition_Language … which reminds me that I need to do some learning about XML. DDL -> Create and Delete (DBs, Tables, views, integrity contraints, indexes) DML -> Relational Calculus, Relational Algebra, Transform Oriented Languages, Graphical Languages (MS Access), 4GL, 5GL (All exhibit ‘closure ‘ property – whatever you do is not stored permanently and gone on closure of the instance.) For DML we are focusing on Relational Algebra as it is apparently more simplistic. Relational algebra has 8 basic operators:
- Selection
- Projection
- Join
- Union
- Intersection
- Difference
- Cartesian Product
- Division
We went through definitions of these with theoretical examples which I think failed to pull the theory into any relevant practical sense. I hope the tut will give me some more practical connections for this theory otherwise (without a much larger mathematical understanding) this is rote learning without the ability for application. Will update this entry after the tutorial. In the tutorial we went through some clarification of terms and then moved onto some prac questions on Relational Algebra for which I made some notepad notations:
Q4, D
[1] -> (ROOM) |x| ROOM.Hotel-No = HOTEL.Hotel-No (HOTEL)
[2] -> π type,price([1]) PROJECTION
[3] -> Θ [2].Name = Grosvenor ([2])
Q4, e
[1] -> (GUEST) |x| GUEST.Guest-No = BOOKING.Guest-No (BOOKING)
[2] -> ([1]) |x| [1].Hotel-No = HOTEL.Hotel.No (HOTEL)
[3] -> π [2].NAME, [2].ADDRESS ([2])
|x| – JOIN
π – PROJECTION
Θ – SELECTION
note that SELECTION and PROJECTION should be done first if efficiency is a concern.
Working through the Relational Algebra was good as it tied the link between theory and possible practical applications. I know if I had known this theory previously I could have saved time on several projects using SQL databases. Pearl of the Week: Relational Algebra Basic Operators:
- Selection (Slices Tuples)
- Projection (Dices Attributes)
- Join (Natural Join, Left/Right/Full Outer Join, θ-join[uses binary operators {<, ≤, =, >, ≥}]
- Union
- Intersect
- Difference
- Cartesian Product
- Division
- Rename? [(Actually, Codd omitted the rename, but the compelling case for its inclusion was shown by the inventors of ISBL.)source