| Lecture | Slide | Comments | ||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2 | 4 | A key is shown on the next slide. Note, this is not necessarily the only key. | ||||||||||||||||||||
| 2 | 5 | Problems with this approach are mentioned on the following slide. | ||||||||||||||||||||
| 2 | 15 | Course(code, name, teacher) | ||||||||||||||||||||
| 2 | 19 | See next slide. | ||||||||||||||||||||
| 2 | 25 | An extra attribute is now added to relation LecturesIn: #times | ||||||||||||||||||||
| 2 | 28 |
Entity Weekday gives: Weekdays(day)
Relation LecturesIn becomes: LecturesIn(code, name, day) With one extra reference: day → Weekdays.day |
||||||||||||||||||||
| 2 | 34 |
A many-to-exactly-one relationship does not require a relationship relation when we map to the relational model. The relationship information can go into the entity relation of the entity on the "many" side of the relationship.
In this case, this would give:
Rooms(name, #seats) Course(code, name, teacher,room) room → Rooms.name |
||||||||||||||||||||
| 2 | 35 | These two alternative approaches are discussed on slide 37. | ||||||||||||||||||||
| 2 | 46 |
Courses(code, name) Rooms(name, #seats) GivenCourses(course, period, teacher) course → Courses.code LecturesIn(course, period, room) (course, period) → GivenCourses.(course, period) room → Rooms.name |
||||||||||||||||||||
| 3 | 5 |
For this slide, and the two that follow, just follow the recipes on the
previous slide.
Rooms(name, #seats) ComputerRooms(name, #computers) name → Rooms.name |
||||||||||||||||||||
| 3 | 6 | Rooms(name, #seats, #computers) | ||||||||||||||||||||
| 3 | 7 | Rooms(name, #seats) ComputerRooms(name, #seats, #computers) | ||||||||||||||||||||
| 4 | 3 |
"LHS" = "left-hand side" "RHS" = "right-hand side" See update anomaly in lecture 2. |
||||||||||||||||||||
| 4 | 4 | The result of decomposing is shown on the next slide. | ||||||||||||||||||||
| 4 | 7 | "w.r.t." = "with respect to" | ||||||||||||||||||||
| 4 | 11 | The natural join of the two relations in the middle of the slide is a relation with four rows. | ||||||||||||||||||||
| 5 | 13 | See the next two slides. | ||||||||||||||||||||
| 4 | 20 |
Consider the following data:
|
||||||||||||||||||||
| 22 | 48 | The relation on the right is not in fourth normal form. | ||||||||||||||||||||
| 5 | 26 | Deletes all rows from relation Courses. | ||||||||||||||||||||
| 5 | 42 | The result relation has two rows - the rows of relation Givencourses that satisfy the selection condition. | ||||||||||||||||||||
| 5 | 44 |
|
||||||||||||||||||||
| 5 | 51 | σ teacher="Niklas Broberg" ( Courses x GivenCourses ) | ||||||||||||||||||||
| 5 | 55 |
|
||||||||||||||||||||
| 5 | 57 |
|
||||||||||||||||||||
| 5 | 65 |
The SQL statement will give a result with three rows:
| ||||||||||||||||||||
| 6 | 3 |
Find all teachers who have lectures on Mondays in period 2.
The SQL formulation on this slide is to illustrate a subquery. There are other ways to express this query in SQL. |
||||||||||||||||||||
| 6 | 10 | Find all lectures that are scheduled in rooms with too few seats. | ||||||||||||||||||||
| 6 | 14 | Find days in period 2 when course TDA357 has lectures in room VR. | ||||||||||||||||||||
| 6 | 50 |
SELECT teacher, AVG(nrSt) FROM GivenCourses GROUP BY teacher γ teacher, AVG(nrSt) ( GivenCourses ) |
||||||||||||||||||||
| 8 | 6 | SET NULL is not a possible policy for either of the modifications affecting GivenCourses.(course, period), since these are key attributes in relation Lectures, and so cannot have null values. The CASCADE policy seems reasonable if a row is deleted from GivenCourses, or if a room name in Rooms is changed. If a row in GivenCourses is updated, it might seem reasonable to CASCADE the update if it is simply updating the course code, but we might not want a change or period to cascade (i.e. we might not want to automatically reschedule all lectures for the same times and rooms in the new period), so it could be better to REJECT updates to the referenced row. If a row is deleted from the Rooms relation, it seems reasonable to SET NULL the room or lectures scheduled for the deleted room. Or we could consider to REJECT such modifications (until all affected lectures have first been relocated to other rooms). Cancelling lectures (i.e. cascading) just because a particular room is no longer available seems an extreme step to take. | ||||||||||||||||||||
| 8 | 28 | "RAISE_USE_ERROR" should be "RAISE_APPLICATION_ERROR" |