At this stage we draw the design of any person in the tables to get the ratio of the logical data model. At this stage, we can conclude that approximately differentiate amongst us to our table and what type of information is stored for each attribute. At this stage, we present ER-diagram in 3NF and delete unnecessary information, which may in the future create anomalies.
1) Final ER-diagram (after working with many-to-many, etc.)
2) Derive relations for logical data model. Every entity of the ER-diagram should be described as follows.
Medications entity
| field's content
| field's name
| type, length
| note
|
| Medications ID
| med_id
| C(20)
| PK,
|
| Name drug
| name_drug
| C(20)
| mandatory field
|
| Brief abstract
| brief_abstract
| C(20)
| mandatory field
|
| Manufacturer
| man_id
| C(20)
| FK to Manufacturer
|
| Storage
| storage
| C(20)
| mandatory field
|
| Category
| cat_id
| C(20)
| FK to Category
|
Basket entity
| field's content
| field's name
| type, length
| note
|
| Order ID
| ord_id
| C(20)
| FK to Order
|
| Medications ID
| med_id
| C(20)
| FK to Medications
|
| Date
| date
| D
| mandatory field
|
Order entity
| field's content
| field's name
| type, length
| note
|
| Order ID
| ord_id
| C(20)
| PK,
|
| Customer ID
| cust_id
| C(30)
| FK to Customer
|
| Date
| date
| D
| mandatory field
|
Customer entity
| field's content
| field's name
| type, length
| note
|
| Customer ID
| cust_id
| C(30)
| PK
|
| Name
| name
| C(30)
| mandatory field
|
| Date order
| date_order
| D
| mandatory field
|
| Phone number
| num 1
| N(14)
| mandatory field
|
| Phone number
| num 2
| N(14)
| mandatory field
|
| Order ID
| ord_id
| C(20)
| FK to Order
|
Manufacturers entity
| field's content
| field's name
| type, length
| note
|
| Medications ID
| med_id
| C(20)
| PK,
|
| Name
| name
| C(25)
| mandatory field
|
| City
| city
| C(20)
| mandatory field
|
Suppliers entity
| field's content
| field's name
| type, length
| note
|
| Medications ID
| med_id
| C(20)
| PK,
|
| Name
| name
| C(25)
| mandatory field
|
| City
| city
| C(20)
| mandatory field
|
Employees entity
| field's content
| field's name
| type, length
| note
|
| Employees ID
| emp_id
| C(20)
| PK, FK
|
| Last name
| l.name
| C(25)
| mandatory field
|
| First name
| f.name
| C(25)
| mandatory field
|
| Position
| position
| N(8)
| mandatory field
|
| Medications ID
| med_id
| C(20)
| FK to Medications
|
Address_emp entity
| field's content
| field's name
| type, length
| note
|
| Employees ID
| emp_id
| C(20)
| PK,FK
|
| Name
| name
| C(25)
| mandatory field
|
| Address
| address
| C(20)
| mandatory field
|
| Phone number
| num1
| N(14)
| mandatory field
|
| Phone number
| num2
| N(14)
| mandatory field
|
| Email
| email
| C(30)
| mandatory field
|
Ingredients entity
| field's content
| field's name
| type, length
| note
|
| Ingredients ID
| ing_id
| C(20)
| PK,
|
| Name
| name
| C(25)
| mandatory field
|
Ingredient in recipe entity
| field's content
| field's name
| type, length
| note
|
| Ingredients ID
| ing_id
| C(20)
| PK,
|
| Recipe ID
| rec_id
| C(50)
| FK to Recipe
|
| Amount
| amount
| N(10)
| mandatory field
|
Recipe entity
| field's content
| field's name
| type, length
| note
|
| Recipe ID
| rec_id
| C(20)
| PK,FK
|
| Name
| name
| C(25)
| mandatory field
|
| period
| period
| C(20)
| mandatory field
|
| Customer ID
| cust_id
| C(20)
| FK to Customer
|
| Medications ID
| med_id
| C(20)
| FK to Medications
|
Note: N – for numeric, C – character, D – date.
3. Normalization: validate relations using normalization.
In this database, my aim is to achieve first three forms of normalization and to get rid of redundant data and any anomalies. According to first form of normalization, in each cell I shall have only one single data. As you can see above, there are only single information in every cell. I have followed this rule and tried to save only single data in one cell.
Therefore, here is everything ok.
Second, transitevely depend every non - primary key not dependt on each other to the med_id, which is Primary key in this entity.
Third, transitevely depend to the med_id, which is Primery key every non - primary key not depend on each other fully depend to the med_id, which is Primary key in this entity.
