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

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

Lecture April 2

Introduction to course. Introduction to Database Management Systems. The Relational Model.

Reading

Kifer, Bernstein, and Lewis: Chapters 1-2 and Sections 3.1-2. Slides: pdf, pdf.

Remarks

Our introduction slides do not discuss exactly the same subjects as Chapter 1.

Lecture April 11

More on Relational Model and its formulation in SQL (the Data Definition Part of SQL).

Reading

Kifer, Bernstein, and Lewis: Sections 3.3.1-6.

Lecture April 16 (Expected Contents)

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

Reading

Kifer, Bernstein, and Lewis: Rest of Chapter 3. Chapter 4.

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 (Expected Contents)

Relational algebra.

Reading

Kifer, Bernstein, and Lewis: Section 5.1.

Exercises April 19

Exercises 4.8 (only question a), 4.9 (only question a), 4.6, 4.17, 4.1, translate your diagrams from exercises 4.8 and 4.9 into SQL schemas, 4.5 (base the answer on the diagrams in Fig. 4.28, Fig. 4.30, and Fig. 4.31), and 4.7 (not question c) in Kifer, Bernstein, and Lewis (in that order, i.e., modeling first, then translation into SQL schemas).