Steps To Create A Basic Relational Database Schema From Logical Data Model

Mapping conceptual model to a logical model

The aim of this milestone is to translate the conceptual model achieved from milestone to a logical model and then review the logical model to make sure it supports the specified transactions for BTFrone.

The conceptual model achieved at milestone 1 can be mapped into a logical model by ensuring the following rules and steps are followed;

• Removing of many to many relationships and getting all the entities that pass this rule

This rule entails ensuring that no many-to-many relationships should exist in the logical model. The relationships that are many-to-many should be transformed into one-to-many. This will involve decomposing existing entities to come up with new entities that have one-to-many relationship with the existing entities. After all the many-to-many relationships are eliminated, all the entities achieved from this step are defined.

• Defining relations for the logical data model- This step involves defining the entities making up the logical model. The entities should have their respective attributes and all the relationships between the entities should be defined. The entities and relationships defined can be of different types;
  • Strong entities- this type of entities are the parent entities in all their relationships.
  • Weak entities- this type of entities are the child entities in one or more of their relationships.
  • One-to-many relationships- this types of relationships exist between strong entities and weak entities where by the strong entity has the primary key which is referenced by the foreign key in the child entity.
  • One-to-one relationships- Any one-to-one relationship in the conceptual model should have its participation defined. Participation can be on one side of the relationship or on both sides where an entity will either mandatory participation or optional participation.
  • Superclass or subclass relationships- For all relationships that have a superclass and subclass, in the logical model the superclass becomes the parent entity and the subclass becomes the child entity.
  • Multivalued attributes- Any multivalued attribute should be decomposed to form a new child entity referencing the parent entity.

From the following steps a list of rules can be defined that are supposed to be followed while mapping the conceptual model to a logical model. These rules are;

1. All strong entities should have their respective simple attributes and all composite attributes be decomposed into simple attributes.
2. All weak entities should have a foreign key that should reference the primary key of the parent entity.
3. One-to-many relationships- The entity on the one side of the relationship should be the strong entity and the entity on the many side of the relationship should be the weak entity and thus rule 1 and 2 above should be applied for both the strong and weak entity.
4. One-to-one relationships- for all one-to-one relationship where participation is mandatory on both sides the two relations should be combined to form one entity. For one-to-one relationships where mandatory participation is on one side the entity on the mandatory should be the strong entity while the entity on the optional side should be the weak entity thus both entities should follow rule 1 and 2 specified above.

Using the steps and the rules descried above, the conceptual model achieved in milestone 1 can be transformed into a logical model.

There are many methods of drawing an entity relationship diagram crow’s foot notation being one of them. Crows foot notation is used to model entity relationship diagrams by showing entities and their attributes, relationships between the entities with their cardinalities and participation. Primary keys and foreign keys are also shown. For the relationships, crow’s foot notation uses the following types of notations to represent different types of relationships;

To derive the logical model shown in figure 1 above from the conceptual model achieved in milestone 1 a few steps were followed based on the rules and steps of transforming a conceptual model to a logical model as discussed in the section above. The first step was to identify all the entities from the conceptual model. All the strong entities and the weak identities were identified based on the types of relationships between the entities. For each entity, the simple attributes are indicated as well as the primary key and in every parent entity and foreign keys in every child entity. All multivalued attributes were decomposed to form new entities.

Normalization can be done to validate the logical model achieved from the top down approach used to model the ERD of the logical model. Validation through normalization uses a bottom approach. Normalization involves the following stages;

Normalization to 1NF involves eliminating all the repeating groups. All entities are in 1NF if they do not contain any repeating group. Evaluating my logical model, there seems to be no repeating group so it is right to say that all relations are in 1NF.

ERD using crows foot notation

Normalization to 2NF involves elimination of any partial dependency existing in any of the relations to make sure there is only one primary  key in each of the relations. All the attributes that are not the primary key should be fully dependent on the primary key. After evaluating my logical model, all relations were in 2NF since no partial dependencies were identified.

Normalization to 3NF involves eliminating all transitive dependencies to make sure the all the relations have only key attribute which determines all the other attributes. As a result of transformation from my conceptual model to logical model the following transitive dependencies were identified.

Eliminating this transitive dependency resulted to the two relations shown in the logical model in figure 1;

Subscription (subsID, staffID, duration)

Subscriptiontype (subsID, sub_stype,subs_fee)

Another transitive dependency that was identified is;

Eliminating this transitive dependency resulted into two relations as shown in the logical model in figure 1.

Staff (staffID, fname, lname, address)

Position (position_name, staffID, job_type, salary)

A relation is BCNF if for every non-trivial FD  a1,a2àb1, b2 satisfied by R the set a1,a2 is a superkey for R. Evaluating the logical model all relations are in their BCNF because for all relations the primary key is the only candidate key.

A relation is in 4NF if it is BCNF and for every non-trivial mutivalued dependencies AàB, A is a superkey i.e. X is either a candidate key or a superset. 4NF is mainly oncerned with multivalued dependencies. Evaluating all the relations in the logical model both staff and supplier have multivalued dependencies because both the staff and supplier can have more than one one phone number thus is decomposed to form child entities staff_phone and supplier_phone.

Entity Name	Start Volume	Growth	Comments
Phone	200	5%	Has a like hood of increasing over time
SupplierPart	2500	5%	Holds parts supplied by a supplier. Has a likelihood of increasing.
Supplier	200	5%	Has a like hood of increasing over time
BTDrone	40000	5%	Has a like hood of increasing over time
Part	2000	5%	Has no like hood of increasing over time
Region	4000	5%	Has a like hood of increasing over time
Contract	2000	5%	Has a like hood of increasing over time
Moving	400000	5%	Has a like hood of increasing over time
Zone	20000	5%	Has a like hood of increasing over time
Account	400000	5%	Has a like hood of increasing over time
Staff	3000	5%	Has a low chance of increasing over time
Viewing	400000	5%	Has a like hood of increasing over time
Staff_Phone	2000	5%	Has a low chance of increasing over time
Position	400	5%	Has low chance of increasing over time
Subscription_Type	400	5%	Not likely to increase over time
Subscription	200000	5%	Has a like hood of increasing over time

 Tables and their attributes

Relation Name	Attribute	Description	Data Type	Length	Key	Validation Rules	Reference Integrity	Entity Constraints
BTDrone	BtdroneID	Unique key identifying a btdrone	Integer	10	PK	Unique, Auto, Increment		Not null
	StaffID	Foreign key referencing a specific stadd	Integer	10	FK	Unique	Mandatory	Not null
	Capacity	Description of the harddisc capacity of a BTDrone	Varchar	10
	Longitude	Measurement of the longitude of a BTDrone	Varchar	50
	Latitude	Measurement of the latitude of a BTDrone	Varchar	50
	Altitude	Measurement of the altitude of a BTDrone	Varchar	50
	Humidity	Measurement of the humidity of a BTDrone	Varchar	50
	Ambient_light_ strength	Measurement of the ambient light strength of a BTDrone	Varchar	50
Part	PartID	Unique primary key identifying a part	Integer	10	PK	Unique, Auto Increment		Not null
	BtdroneID	Foreign key referencing a specific btdrone	Integer	10	FK	Unique	Mandatory	Not null
	Part_name	Name used to identify a part	Varchar	50
	Part_desc	Description of the part	Varchar	250
Supplier	supplierID	Unique key identifying a supplier	Integer	10	PK	Unique, Auto Increment		Not null
	supplier_name	Name identifying the supplier	Varchar	50
	supplier_address	Supplier’s address	Varchar	50
Supplier_Phone	phone_no	Unique phone_no of  a supplier	varchar	25	pk	Unique		Not null
	SupplierID	Foreign key referencing a specific supplier	Integer	10	FK		Mandatory	Not null
Supplier_Part	SupplierID	Part of the composite primary key	Integer	10	PK	Unique		Not null
	PartID	Part of the composite primary key	Integer	10	PK		Not null
Contract	ContractID	Unique key identifying a contract	Integer	10	PK	Unique, Auto Increment		Not null
	BtdroneID	Foreign key referencing a specific btDrone	Integer	10	FK	Unique	Mandatory	Not null
	Start_date	The starting date of the contract	Date
	Duration	Duration of the contract in months	integer	5
Region	regionID	Unique key identifying a region	Integer	10	PK	Unique, Auto Increment		Not null
	ContractID	Foreign key referencing  a specific contract	Integer	10	FK	Unique	Mandatory	Not null
	region_name	Identifier name of the region	Varchar	50
	Region_desc	A description in details of the region	Varchar	255
Zone	ZoneID	Unique identifying a zone	Integer	10	PK	Unique, Auto Increment		Not null
	regionID	Foreign key referencing a specific region	Integer	10	FK		Mandatory	Not null
	Zone_latitude	A zone’s latitude	Varchar	50
	Zone_longitude	A zone’s longitufe	Varchar	50
	Zone_min_latitude	The zone’s minimum latitued	Varchar	50
	Zone_max_latitude	The zone’s maximum longitude	Varchar	50
Staff	StaffID	Unique key identifying a staff	Integer	10	PK	Unique, Auto Increment		Not null
	fname	First name given to a staff	Varchar	50
	lname	Last name given to a staff	Varchar	50
	Address	Address details of a staff	Varchar	255
Account	AccountID	Unique key identifying an account	Integer	10	PK	Unique, Auto Increment		Not null
	SubsID	Foreign key referencing a certain subscription	Integer	10	FK	Unique	Mandatory	Not nukk
	BtdroneID	Foreign key referencing a specific BTDrone	Integer	10	FK	Unique	Mandatory	Not nukk
	Account_type	Type of the account	Varchar	30
	Opening_date	The date of opening of the account	Date
	Username	Username used to access the account	Varchar	50
	Password	Password used to access the accpunt	Varchar	50
Moving	AccountID	Unique key identifying an account while referencing an account	Integer	10	PK & FK	Unique	Mandatory	Not null
	Move_video_stream	Option to move video while streaming	Varchar	1
Viewing	AccountID	Unique key identifying an account while referencing an account	Integer	10	PK & FK	Unique	Mandatory	Not nukk
	View_video_stream	View video option using BtDrone	varchar	1
Position	Position_name	Description identifying the position of a staff in the company	Varchar	25	PK	Unique		Not null
	StaffID	Foreign key referencing  a specific staff	Integer	10	FK	Unique	Mandatory	Not null
	Job_type	Type of job for the position	Varchar	25
	Salary	Salary paid to the stadd	Decimal	10,2
Staff_Phone	PhoneNO	Unique phone number of a staff	Integer	10	PK	Unique		Not  null
	StaffID	Foreign key referencing a specific staff	Integer	10	FK	Unique	Mandatory	Not null
Subscription	SubsID	Unique key identifying a subscription	Integer	10	PK	Unique		Not null
	StaffID	Foreign key referencing a specific staff	Integer	10	FK	Unique	Mandatory	Not null
	Duration	Duration the subscription will take in months	integer	5
	Subs_type	Foreign key referencing a certain subscription type	varchar	20	FK		Mandatory	Not null
Subscription Type	Subs_type	Unique key identifying a certain subscription type	Varchar	20	PK	Unique		Not null
	Subs_fee	Fees paid for that subscription	Integer	10

NaLER is a natural language for interpreting an entity relationship diagram. It provides an easy way to understand ERD for both database designers and users. THe following steps are followed to interpret the logical entity relationship diagram achieved for BTDrone.

For my ERD entities are shown as boxes which are subdivided inside. The subdivisions contain the attributes of the entity. Some attributes are either a primary key which is denoted as PK or a foreign key which is denoted as FK. Others are just simple attributes. Each attribute has a data type with its respective size.

The syntax used to achieve the ERD is correct based on my analysis.

S1. Each BTDrone is identified by a BTDRoneID.

Normalization

S2. Each BTDrone (BTDroneID) must have a capacity.

S3. Each BTDRone (BTDroneID) must have a longitude

S4. Each BTDrone  (BTDroneID)  must have a latitude

S5. Each BTDrone (BTDroneID) must have an altidude

S6. Each BTDrone (BTDroneID)  must have a humidity

S7. Each BTDrone  (BTDroneID) must have Ambient_light_strength

S8. Each part is identified by a (partID)

1. Each partID must have a part_name
2. Each partID must have a part_desc

S11. Each supplier is identified by a supplierID.

S12. Each supplierID must have a supplier_name.

S13. Each supplierOD must have a supplier_address

S14 Each supplier_phone is identified by phone_no

S15. Each supplier_part is identified by (supplierID, partID)

S16. Each contract is identified by a contractID

S17. Each contractID must have a start_date

S18. Each contractID must have a duration 

S19. Each regionID is identified by regionID

S20. Each regionID must have a region_name

S21. Each regionID must have a region_desc

S22. Each zone is idetifeid by a zoneID

S23. Each zoneID must have a zone_latitude

S24. Each zoneID must have a zone_longitude

S25. Each zoneID must have a zone_min_latitude

S26. Each zoneID must have a zone_max_latitude

S27. Each staff is identified bya staffID

S28. Each staffID must have an fname

S29. Each staffID must have an lname

S30. Each staffID must have an address

S31. Each moving is identified by acountID

S32. Each moving (accountID) must have move_video_Stream

S32. Each viewing is identified by accountID

S33. Each viewing (accountID) must have view_video_stream

S34. Each position is identified by a position_name

S35 each position_name must have a job_type

S36. Each position_name must have a salary.

S37. Each staff_phone is identified by (phoneNO, staffID) 

S38.  Each subscription is identified by subsID

S39. Each subscription must have a duration 

S40. Each subscription_type is identified by subs_type

S41. Each subs_type must have a subs_fee 

S42. Each BTDroneID must and is associated to one staffID

S43. Each partID must and is associated to one BTDroneID

S44. Each supplier_phone (phoneNO) must and is associated to one supplierID

S45. Each supplier_part(supplierID)  must and is associated to one supplierID

S46. Each Supplier_part (partID) must and is associated to one partID

S47. Each contractID must and is associated to one BtDroneID

S48. Each regionID must and is associated to one contractID

S49. Each zoneID must and is associated to one regionID

S50. Each accountID must and is associated to one subsID

S51. Each accountID must and is associated to one BTDroneID

S52. Each moving(accountID) must and is associated to one Account (accountID)

S53. Each viewing(accountID must and is associated to one ccount(accountID)

S54. Each postion_name must and is associated to one staffID

S55. Each staff_phone (staffID) must and is associated to one staffID

S56. Each subsID must and is associated to one staffID

Conclusion

The BTDrone logical model is a success because all the steps and rules that are needed in order to transform a conceptual model to a logical model have been followed. The logical model achieved can now act as a basis on which the physical model is constructed.