Database design for a farmers herd book.

I will draw up a table that will help you understand the entity types;

Cattle

Transfers

Farmer

Calfing

Logical data models

I am now going to take a look at the different entities and attributes and show all of the different relationships and hopefully eliminate the different many to many relationships.

Logical data modelling is a graphic-intensive technique that results in a data model representing the definition, characteristics, and relationships of data in a business, technical, or conceptual environment. Its purpose is to describe end-user data to systems and end-user staff.

---

Data Modelling Objects

The three types of data objects--entities, attributes, and relationships--are the basic building blocks of modelling:

• Entities are persons, places, or things about which an organisation wishes to save information. Employees, States, Orders, and Time Sheets are examples of entities. (As a convention, I capitalise the first letter of entities.)

∙        Attributes are the properties of entities. Attribute examples include Colour, Employment Date, Name, and Social Security Number. (As a convention, I write attributes in all upper-case letters.)

• Relationships are verbs that describe how entities relate to each other; for example: 'Customers Buy Products,' 'Employees File Time Sheets,' 'Salespeople Place Orders.' A sentence in this entity-relationship-entity construct is called a "relationship entity pair," which is a fashionable mechanism for representing relationships. Relationship entity pairs are bi-directional. Therefore, 'Customers Buy Products' is the same as 'Products are bought by Customers.' "Relationship" describes an end-user relationship, not a technical one

 

---

Entity Relationship-diagram

Entity-Relationship diagrams are a way of representing relationships between data. Emphasis is on data grouping.

Even the very simplest computer applications typically have 10-20 data elements. Individual data elements may number in the hundreds or thousands in a medium to large application. Organising these elements is of immense importance; no other considerations are likely to have the impact of a well-chosen or poorly chosen data layout.

Data elements in an application often correspond to other data elements in complex ways. Entity-Relationship diagrams are a visualisation model for these layouts. After all necessary data elements are determined, related elements are grouped into entities. The next step is to join pairs of entities into relationships.

In the example that follows, the entities are represented by boxes and the relationships by the connecting lines. The `m' and `1' notations are explained further on.

An example of an entity relationship diagram

Data elements for the above diagram have the following definitions:

As you can see, an entity is the approximate equivalent of a table, database, or record (the term used depends on the company providing the database manager).

Note that there is no relationship between EXPENDITURE and HOURS, so no relationship is drawn. On the other hand it is possible for there to be two or even more relationships between two given entities, so multiple lines could be drawn (although not shown in this example).

There is a simple way of thinking of relationships, which corresponds closely with traditional ways of storing data in a computer. That is, we allow two kinds of relationships: one-to-one, and one-to-many. In a one-to-one relationship, for each member of the first entity there is just one member in the related entity. A one-to-many relationship also called a parent-to-child relationship, means that for every one-parent entity there may be many child entities.

Note the use of `1' and `m' in the example diagram to indicate relationships of one-to-many. For example, between WORKER and ASSIGNMENT 1:m is indicated because one worker can have many assignments. The term `many' is misleading as there may be two, one, or even zero children for a parent. In the example, there may be only 1 ASSIGNMENTS for a particular WORKER.

Entities are connected most often by one-to-many relationships. A many-to-many relationship (in this case one PROJECT has many WORKERS and one WORKER has many Projects) is eventually broken down into one or more pairs of one-to-many relationships (note the diagram). Strict one-to-one relationships on the other hand generally collapse the two entities into one entity. There are, however, one-to-one relationships where one parent has either one or zero children. (Perhaps the child is off to school somewhere?)

Entity Relationship Diagram

                                                         

                            1                                1

                             1                

              m                                                                              

                                                                                                                1                

                                                        m

                                        

Task 3

Explanation of normalisation

Normalisation is a process for analysis.  It is a step by step method.  It produces entities and attributes

Entity types – Herd book

Unnormalised 

Transfers (Ear_Tag, Destination, Date,) Cattle (Ear Tag, Breed, Colour, DOB, Sex, Cow Ear Tag, Herd No,) Farmer (Date Bought, Herd No, Name, Surname, Telephone Number,) Calfing (Ear Tag Number, Date Served, Served with, Scan Date, Result.)

1NF

The first stage of normalisation is to remove the repeating groups. In order to uniquely identify.

Transfers (Ear_Tag, Destination, Date,) Cattle (Ear Tag, Breed, Colour, DOB, Sex, Cow Ear Tag, Herd No,) Farmer (Date Bought, Herd No, Name, Surname, Telephone Number,) Calfing (Ear Tag Number, Date Served, Served with, Scan Date, Result.)

2 NF

To carry out stage two of the normalisation process to produce the entities in second normal form, the concept of functional dependence has to be used.

Transfers (Transfers_Ear_Tag, Transfers_Destination, Transfers_Date,)

Cattle (Cattle_Ear_Tag, Cattle_Breed, Cattle_Colour, Cattle_DOB, Cattle_Sex, Cattle_Cow Ear Tag, Cattle_Herd No,)

Farmer (Farmer_DateBought, Farmer_HerdNo, Farmer_Name,

Farmer_Surname, Farmer_Telephone Number,)

Calfing (Calfing_Ear Tag Number, Calfing_Date Served, Calfing_Served with, Calfing_Scan Date, Calfing_Result.)

3 NF

The third stage of normalisation will produce entities in third normal form.  An entity is in third normal form if all its non - identifying attributes are mutually independent.

Transfers (Transfers_Ear_Tag, Transfers_Destination, Transfers_Date,)

Cattle (Cattle_Ear_Tag, Cattle_Breed, Cattle_Colour, Cattle_DOB, Cattle_Sex, Cattle_Cow Ear Tag, Cattle_Herd No,)

Farmer (Farmer_DateBought, Farmer_HerdNo, Farmer_Name,

Farmer_Surname, Farmer_Telephone Number,)

Calfing (Calfing_Ear Tag Number, Calfing_Date Served, Calfing_Served with, Calfing_Scan Date, Calfing_Result.)

Task 4

User Documentation

Step one

In order to load up the spreadsheet you have to click on the Microsoft access icon on the desktop                when this has been done then you will have the following appear;

You enter blank database by clicking on the                  and then clicking on the OK button.  

---

Step two

Once you have opened the database then you have to decide where you are going to save it.  In order to save it you have to click on the and then you will be able to save it to where ever you want.  You enter the name – I have chosen database then click on the create button

---

Step three

 The screen below will now emerge.  As you can see the name of the database is show “database”.  It is important to know that when you are creating the database that when you are in Microsoft Access that you first click                  .

After you have clicked new then the “New Table” window will appear:

It is very important that you click design view, as this will take you into your design view;

---

Deleting a record

When deleting a record you have to click on the delete button shown below

After you have clicked on this button then the following message will appear

Adding a record

When you are going to add a record all you have to do is to click on the add button as shown below;

Amending a record

Amending a record is simple.  As you can see the highlighted field has got the name factory in it, this is correct so all you have to do to click on the arrow for the combo box, and on the destination that the animal went to but if you typed in a destination that is not in the combo box it wont be entered.

How to use a form

When using this form there is ver y little to do as there is so many drop down selection boxes to use.  Clicking on the arrows at the side of the cells uses them               and then the selection box appears.

Instructions

When using the farmer’s form you first need to click on the herd number, the first box. Every herd number is different and has only five digits.  You then fill in the farmers name in the box below and the farmers surname in the next box and the farmers telephone number after that you then fill in the farmers home number and all home telephone numbers in the north of Ireland begin with 028.

---

Technical documentation

Entering data into the database

The design screen below will now appear and then you can start to type in the attributes of the entities.  These entities will be farmer, transfers, calfing and cattle.

When we are in the design view we can then enter the data into the Field name column, this will be the Key field that is in the first row.

There is a little key shape at the side of the row number that indicates that the is the Key field.  In order for me to only allow 5 digits I type 99999 this will allow values from 00001 to 99999 to be entered in this field.

Creating a lookup box

When we have all of the rest of the design view completed I can then start to make the database intensely user friendly as I will enter the “Lookup” function for the “Result’’ ‘’in calf ‘’ and ‘’not in calf’’

Creating a relational database

When the calfing database has been created then we have to create another three databases that will then be linked up, and this is called a relational database.

After you have got the other databases created successfully then it is time to link all of the databases together;

In order to do this I have to first go into the opening screen and then click on the icon that says relationship when you hover the cursor on it. (you can see this below on the next page)

The relational button is this one here

When you click the relationship button the following will appear on your screen.

Creating a form

Creating the forms in Microsoft Access is easy as there is a wizard on it.  In order to start this wizard you have to first click on the new on the form window;

When you click on new then the new form window will appear.  The new form will be on design view so you have to change it on to form wizard and then click on the combo box.

After this all you have to do is click next on each stage of the wizard until your finished.

---

Creating the reports

When creating a report you follow the same steps as creating forms.  When you’re creating reports you click new.

After you click the new button then the following will appear on your screen

Again the same as creating forms you change from design wizard to report wizard then you choose what table or query where your data comes from.  Then you follow each stage the same as the forms and then you create a report for the rest of the tables.

---

Creating a macro

When creating a macro you just go into macros then click on the new button.

 After you click the new button the following will appear on your screen.  Even though there is no wizard it is still really easy to create a wizard.

You click on the arrow and you select what macro you are looking.

---

What I’m going to do is create a macro that will open switchboard form, so you go into macros and click new then on the action and select the open form macro.

You then select the form name that you want opened and you select switchboard

After you have selected the form you then click run and you will be asked do you want to save the macro and what name you want to save it as and you give your macro an approiate name then you just click ok

 Creating the main menu

In order to create a main menu you have to first have to create a switchboard.  Selecting can easily do this;

1. Tools 
2. Add-Ins 

And then Switchboard Manager

After you click on the switchboard manager then the following will appear on your screen.

On this window you have a choice of several different menus.

You click on edit.

The reason clicking on edit is because you have to change the name of your menu.

After you have clicked on edit then the following will appear on your screen and they already had the name main switchboard but I changed mine to Main Menu.

When the name has been changed then you click on close – you will be taken back into the switchboard manager window.

---

Creating a macro button in a form

There are also other ways in which you can create a macro button.  These buttons are buttons that will be used in the forms.  This can be achieved by going into the design view of the forms;

You click on the command button and after you have clicked this button you select the size and place you want your button.

---

After you have clicked on the next button the following will appear on your screen and the wizard asks what form would you like the command button to print and you select the form then click next.

The wizard then aks you if you want text or a picture button and you select then you click next

 

---

Then you give your button a name, which is the final stage of the wizard.  You then click on finish and your button has been created.

Helpful formatting in the forms

This helpful formatting is the Control Tip text – this is when you hover your cursor over an item.

In order to do this you have to go into the design view in the form, and double click on the item shown.  

---

When you have doubled click then the following window will appear.

---

Data Dictionary

The best way for me to describe the validation that is used in this database is to show each of the single databases in tabular form;

---

 Output from queries and reports

In order to make a query you have to think of two values that both have the same data type i.e. forename and surname both use text.  Queries can be very useful when trying to find out information.  When you want to set a query you have to;

Here is a very simple guide shown to show how they are created

1. Click on the queries

2.Click ok.

You select what tables you want in you query by clicking on the tables then clicking add.

When you have got the fields that you want in the query it should look like this;

The following shows the structured Query l

SELECT Calfingtbl.[Ear tag no], Calfingtbl.[Date served], Calfingtbl.[Served with], Calfingtbl.[Scan date], Calfingtbl.ResultFROM (Farmertbl INNER JOIN (Calfingtbl INNER JOIN Cattletbl ON Calfingtbl.[Ear tag no] = Cattletbl.[Ear Tag]) ON Farmertbl.[Herd no] = Cattletbl.[Ear Tag]) INNER JOIN Transfers ON (Transfers.[Ear Tag] = Farmertbl.[Herd no]) AND (Cattletbl.[Ear Tag] = Transfers.[Ear Tag]);

Database Structure

---

Acceptable data

In this database there are certain fields that need certain data ranges.  These are things like the herd number, this field needs to have numbers that above 00000, as the patient number cannot begin with a zero.  So you have to enter in the number into the Validation Rule.  Below I have typed in that it is to be greater than (>) 00000

---

After you have got the validation rule you have to then put in the validation text to remind you what has to go into this particular field.  I want to remind myself that this field needs to have a value greater than 00000;

 Task 5

 

Testing of the Database

---

Testing number 1

In this test I tried to enter the date served as 09/07/65 but then this message appeared on my screen.

Testing number 2

In this test I tried to type a letter in the ear tag number and this message appeared on my screen which proves that you cant put letters in the ear tag number so the validation rule is working.

---

Testing number 3

I tried to enter nothing in the transferred table under the destination but the following appeared on my screen and wouldn’t let me continue.

---

Testing number 4

For this test I tried to type in £9,999 but it wouldn’t let me continue because I know that I wouldn’t get that amount of money so I have to enter a number above or equal to zero and below two thousand.

Testing number 5

I clicked on the arrow and the combo box appeared and the rest of the combo boxes work the same as this one.

Testing number 6

As you can see beside ear tag the control tip text is working.

Testing number 7

Witnessed by

---

Testing number 8

In this test I tried not to enter anything into any of the fields but a message came up not letting me to continue

---

Time Management