Database desing: attribute domains - date attribute types
Queries: and - or - comparison operators

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

Layer 1 ITEM OF CLOTHING description photo type(...) date price times

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.

select description
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

select description, type
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.

select id
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.

select description
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.

select description, type
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.

select type, description
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.

select type
from itemOfClothing
where id < 7

Challenge - 3

Apply the query below to the table you have just created.

select photo
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.