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

The objective of this step is to produce a basic working relational database schema from the logical data model. The  process is made up of collating the information gathered in the logical modelling phase and using that information to determine the design of the base relations. The database designer will need to know:

• Whether the system supports the definition of primary keys, foreign keys and alternate keys
• Whether the system supports the definition of required data (that is, whether the system allows attributes to be defined as NOT NULL
• Whether the system supports the definition of domains
• Whether the system supports the definition of enterprise constraints (business rules)
• How to create base relations

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.

 Tables and their attributes

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)

29. Each partID must have a part_name
30. 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.