Lesson 2
Task
Suppose you want to design a database for keeping track of all your items of clothing. For each item you want to store the following details: description, picture, type, date of purchase, price. The default types are: sweatshirt, shirt, jumper, T-shirt, trousers, shorts, skirt, shoes, slippers. The database will have to be used by an application which keeps track of the number of times an item of clothing has been used in order to suggest when it should be washed.
Conceptual data model: ER
Note.
The symbol (...) indicates that an attribute is allowed a set of values from a specific domain. In our example the set of values could be: (T-shirt, trousers, shorts, shirt, jacket, shoes, sweatshirt, jumper, skirt, scarf).
Note.
Databases allow also the type date.
Logical data model: relational
itemOfClothing (id, description, photo, type, date, price, times)
AN INSTANCE OF DATABASE
id | description | photo | type | date | price | times |
---|---|---|---|---|---|---|
1 | raduno vasco | \img\mr.png | T-shirt | 15/08/2014 | 0 | 2 |
2 | cargo | \img\tn.png | shorts | 11/07/2013 | 35 | 0 |
3 | converse all star | \img\cas.png | shoes | 05/06/2014 | 70 | 50 |
4 | grey hoodie | \img\zgc.png | sweatshirt | 05/07/2013 | 45 | 12 |
5 | breaking bad | \img\bb.png | T-shirt | 08/06/2014 | 30 | 0 |
6 | chequered | \img\q.png | shirt | 10/10/2008 | 10 | 0 |
Query - 1
Show the description for all clean clothes.
from itemOfClothing
where times=0
description |
---|
cargo |
breaking bad |
chequered |
Query - 2
Find descriptions and types of all the items of clothing which cost between 20 and 50 euros
from itemOfClothing
where (price>=20)and(price<=50)
description | type |
---|---|
cargo | shorts |
grey hoodie | sweatshirt |
breaking bad | T-shirt |
Query - 3
Find ids of all the items of clothing which have been used at least once but less than 20 times.
from itemOfClothing
where (times>0) and (times<=20)
id |
---|
1 |
4 |
Query - 4
Find the description of all the tuples with id 4, 5 or 6.
from itemOfClothing
where (id=4) or (id=5) or (id=6)
description |
---|
grey hoodie |
breacking bad |
chequered |
Note.
A row can also be called a tuple.
Query - 5
Find descriptions and types of all T-shirt and shirts that have been used at least twice.
from itemOfClothing
where ( (times>=2) and ( (type='T-shirt') or (type='shirt') ) )
description | type |
---|---|
raduno vasco | T-shirt |
Query - 6
Find types and descriptions of all the tuples that contain items of clothing which were not a gift.
from itemOfClothing
where (price != 0)
type | description |
---|---|
shorts | cargo |
shoes | converse all star |
sweatshirt | grey hoodie |
T-shirt | breaking bad |
shirt | chequered |
Note.
In order to obtain not equal you can use != or <>
Challenge
Challenge - 1
Add six tuples to the table shown.
Challenge - 2
Apply the query below to the table you have just created.
from itemOfClothing
where id < 7
Challenge - 3
Apply the query below to the table you have just created.
from itemOfClothing
where (type != 'shoes') and (price>30)
Challenge - 4
Create a query which will display a list of the photos of the T-shirts which cost at least 20 euros.
Challenge - 5
Create a query which will return all the data of the tuple referring to the "Breaking bad" T-shirt.
Challenge - 6
Create a query which will show a list of all the items of clothing purchased before 2010 (description, type and date of purchase).
Challenge - 7
Create a query which will display photos and prices for all the T-shirts, the shirts and the trousers in the database.