Transcript Semi-structured data exercises Exercise 1 • Represent the relations below using the OEM data model. r_id r1 r2 r3 c_id c1 c2 name Hamlet Normandie McDonald's name Linkoping Norkoping Cities Restaurants r_id r1 r2 r3 c_id c1 c1 c2 street Storgatan St.Larsgatan Kungsgatan Restaurants&Cities.

Semi-structured data
exercises
1
Exercise 1
• Represent the relations below using the OEM data
model.
r_id
r1
r2
r3
c_id
c1
c2
name
Hamlet
Normandie
McDonald's
name
Linkoping
Norkoping
Cities
Restaurants
r_id
r1
r2
r3
c_id
c1
c1
c2
street
Storgatan
St.Larsgatan
Kungsgatan
Restaurants&Cities
2
Answer exercise 1 - the OEM model
DB
Restaurants
RestCity
T
5
T
T
6
r_id
Cities
3
2
T
1
7
r_id
8
r_id
name
name
name
14
16
18
Hamlet
Normandie
McDon.
13
15
17
r1
r2
r3
4
T
T
9
r_id
c_id
T
10
r_id
c_id
11
r_id
c_id
c_id
street
street
street
21
24
27
Storgatan
St.Larsgatan
T
12
c_id
name
Kungsgatan
name
29
31
Linkoping
Norkoping
28
c1
30
c2
3
Exercise 2
• Using the data model from the previous question,
formulate the following queries using Lorel:
– find all the restaurants that are located in Linkoping
– find the address (city and street) of the “Hamlet” restaurant
– list the restaurants by city (equivalent of GROUP BY)
4
Answer Exercise 2
find all the restaurants that are located in Linkoping
select R.name
from DB.Restaurants.T R, DB.RestCities.T RC, DB.Cities.T C
where R.r_id = RC.r_id
and RC.c_id = C.c_id
and C.name = “Linköping”
list the restaurants by city (equivalent of GROUP BY)
select C.name, (select R.name
from DB.Restaurants .T R, DB.RestCities.T RC
where R.r_id = RC.r_id
and RC.c_id = C.c_id)
from DB.Cities.T C
5
Exercise 3
• Draw strong and the minimal Data Guides for
the restaurant guide data model below.
Guide
Restaurantguide
1
restaurant
restaurant
cafe
nearby
nearby
2
4
3
nearby
category name address
5
gourmet
contact
7
6
8
Chef Chu
street
16
El Camino Real
name
9
Saigon
city
zipcode
17
18
Palo Alto
92310
manager
address contact
10
11
Menlo Park
category name
19
20
phone
phone
26
71-72-73
14
12
13
fast food
Sandra
reservation
street
21
address contact
15
city zipcode reservation manager
24
25
phone
phone
27
28
29
11-12-13
31-32-33
34-35-36
Rydsvagen
22
Linkoping
23
58435
6
Answer Exercise 3 - Strong Data Guide
Restaurantguide
41
restaurant
42
cafe
66
43
67
nearby
category name address
44
45
street
52
contact
category name
46
city
53
nearby
47
48
49
zipcode reservation manager
54
55
56
phone
62
street
57
address contact
50
51
city zipcode reservation manager
58
59
60
61
phone
phone
phone
63
64
65
7
Answer Exercise 3 - Strong Data Guide continued
nearby
nearby
43
66
category name address
48
68
street
57
contact
name
69
city
58
67
70
73
zipcode reservation manager
59
71
61
address contact
74
75
reservation
56
phone
72
8
41: 1
42: 2,3
43: 4
44: 5
45: 6,9
46: 7,10
47: 8, 11
48: 12
49: 13
50: 14
51: 15
52: 16
53: 17
54: 18
55: 19
56: 20
57: 21
58: 22
59: 23
60: 24
61: 25
62: 26
63: 27
64: 28
65: 29
66: 3,4
67: 3
68: 9,13
69: 10,14
70: 11,15
71: 20,24
72: 27,28
73: 9
74: 10
75: 11
9
Answer Exercise 3 - Minimal Data Guide
Restaurantguide
80
restaurant
cafe
nearby
81
category name address
82
83
84
street city
86
contact
87
85
reservation
zipcode
88
manager
89
phone
90
10