Lesson 7
Task
Suppose you want to design a small database for keeping track
of all the medicines we have at home.
You want to store for each medicine: name, type (syrup, tablet, capsules, etc.), price,
expiry date and the illnesses or symptoms it treats.
Conceptual data model: ER
Note.
In a Many-to-Many relationship an instance of the first entity can be linked to more than one instances of the second entity and viceversa.
Note.
The partial participation of an entity in a relationship is represented by a dotted line. It means that not all of the instances of that entity participate in the relationship.
Logical data model: relational
medicine (id, name, type, price, expiryDate)
illness (id, description)
treat (id, idMedicine, idIllness)
An Instance of database
| medicine | ||||
| id | name | type | price | expiryDate |
|---|---|---|---|---|
| 1 | Ribex Cough | syrup | 12.00 | 25/10/2016 |
| 2 | Zepelin | tablets | 6.00 | 10/09/2018 |
| 3 | Moment | tablets | 7.50 | 01/06/2016 |
| illness | |
| id | description |
|---|---|
| 4 | Headeache |
| 5 | Cold |
| 6 | Cough |
| 7 | Pains in the joints |
| 8 | Pediculosis |
| treat | ||
| id | idMedicine | idIllness |
|---|---|---|
| 9 | 1 | 6 |
| 10 | 2 | 4 |
| 11 | 2 | 7 |
| 12 | 3 | 4 |
| 13 | 3 | 7 |
query - 1
Extract name and expiry date of all medicines of type tablets.
from medicine
where (type = "tablets")
| name | expiryDate |
|---|---|
| Zepelin | 10/09/2018 |
| Moment | 01/06/2016 |
query - 2
Retrieve name and expiry date of all medicines for treating headeache.
from medicine as m, illness as i, treat as t
where (i.description = "Headeache") and (i.id = t.idIllness) and (t.idMedicine = m.id)
| name | expiryDate |
|---|---|
| Ribex Cough | 25/10/2016 |
query - 3
Extract a list of all the illnesses in alphabetical order. Next to each illness display also the name of the medicine for treating it and the expiry date of the medicine.
from medicine as m, illness as i, treat as t
where (i.id = t.idIllness) and (t.idMedicine = m.id)
order by description
| description | name | expiryDate |
|---|---|---|
| Pains in the joints | Zepelin | 10/09/2018 |
| Pains in the joints | Moment | 01/06/2016 |
| Headache | Zepelin | 10/09/2018 |
| Headache | Moment | 01/06/2016 |
| Cough | Ribex Cough | 25/10/2016 |
query - 4
List all the medicines which expire after a certain date (e.g. 01/01/2017) with their price.
from medicine
where (expiryDate > "01/01/2017")
| name | price |
|---|---|
| Zepelin | 6.00 |
Challenge
Challenge - 1
Create a query which will return the table below:
| illnesses |
|---|
| Cold |
| Cough |
| Headache |
| Pains in the joints |
| Pediculosis |
Challenge - 2
Create a query which will return the table below:
| type |
|---|
| tablets |
| syrup |
Challenge - 3
Create a query which will return the table below:
| medicine | illness |
|---|---|
| Moment | Pains in the joints |
| Moment | Headache |
| Ribex Cough | Cough |
| Zepelin | Pains in the joints |
| Zepelin | Headache |
Challenge - 4
Suppose you want to store information about the pharmaceutical industry which produces the medicines. Modify your ER diagram and relational schema accordingly.
Challenge - 5
Suppose you want to store information about the pharmacy where you bought the medicines (name and place). Modify your ER diagram and relational schema accordingly.