DM505, Spring 2007, 4th Quarter - Weekly Note 3

This week, there will be one lecture slot and two exercise slots.

Lecture April 16

More on the Data Definition Part of SQL. The Entity-Relationship (E/R) model.

Reading

Kifer, Bernstein, and Lewis: Rest of Chapter 3. Sections 4.1-3.

Remarks

According to the textbook (p. 33, bottom), a data model should contain means for describing the basic structure of data, means for describing additional constraints on the allowable instances, and means for describing data manipulation (a query language). If we relax the third condition (having a query language), then the E/R model is a data model (contrary to the statement on p. 70, top).

The textbook is unclear when defining entities (p. 70, bottom). It states that entities (in the E/R model) are real or abstract objects. In fact, entities are simply tuples (actually, "unordered tuples", i.e. sets of attributes). The textbook apparently confuses this with the fact that we use these tuples as models of real or abstract objects. When choosing the attributes of the entity, we choose which characteristics of these objects we will focus on (and which we will disregard), as in any modeling situation.

The concept entity type (p. 71, top) is analogous to the concept of schema for relations. The textbook uses the word entity instance for a single entity tuple (p. 70, bottom). To keep the analogy with the relational model, it would be better to reserve this term for the currently stored entity tuples. This is the terminology we will use.

In the E/R model of the book, entities may have set-values attributes (p. 71, bottom), and entity instances are multi-sets, not sets (p. 71, middle). However, at the same time these differences are introduced in the textbook, they are also mentioned as troublesome (p. 72, middle, and p. 71, middle). A variant E/R model is one having only atomic attribute types, and sets as entity instances (a set valued attribute can always be modeled using an extra entity and a relationship - indeed, when mapping to the relational model, normalization (mentioned on p. 87, and the subject of Chapter 6) of the relations will give the same end result in the relational model anyway). This variant of the E/R model is preferable, in the view of the lecturer.

In this variant, the statement (p. 70, top) that the E/R model is not an generalization of the relational model become a bit strained - the entities of the E/R model are identical in structure to relations of the relational model. The E/R model has more modeling power in the sense that is also includes the concept of relationships. The constraint specification power of the two models are incomparable (if we consider the naturally specifiable ones - in both models, we may additionally write up arbitrary constraints in plain text). Finally, the relational model has an associated query formalism (actually several, of which one is the relational algebra studied in Section 5.1), while the E/R model has none, and is purely for modeling use.

Lecture April 18

More on the E/R model. Transfer from E/R model to relational model. Start on relational algebra.

Reading

Kifer, Bernstein, and Lewis: Rest of Chapter 4, except Section 4.6. Section 5.1.1.

Remarks

An alternative to modeling in the E/R model is using the database modeling part of UML. Both are graphical languages, and they are somewhat similar. For time reasons, we leave the UML alternative to your own reading (it will not be part of curriculum).

Lecture April 23 (Expected Contents)

More on relational algebra. Start on query part of SQL.

Reading

Kifer, Bernstein, and Lewis: Sections 5.1.2 and 5.2.1-7.

Exercises April 25

Exercises 4.10 (only question a, but add a transfer to the relational model), 4.11, 5.1, 5.3, 5.4, 5.5, 5.7, 5.8, and 5.10 (only part (i)) in Kifer, Bernstein, and Lewis.

Exercises April 26

Exercises 5.2, 5.10 (only part (ii)), 5.17, 5.18, 5.23, and 5.25 in Kifer, Bernstein, and Lewis.