Beginning Development

I’m an ex-database administrator. The logical place for me to start on an application is to consider the data that it will manage. There are other points starting spots, such as constructing the pattern of processing in the code, but I prefer to start with the data.

I naturally gravitate toward a relational database, which dictates the approach for creating the structure of the data.

NOTE
	I will be describing how we create the schema of the database through the process of logical database design. If you have these skills please skip forward.

NOTE
	(WN) Describe a realational database and why we care.

• (WN) Add external link to definition of database schema one add external link to definition of third form normalization

The first process of designing a database is called logical design.

During logical design we seek to organize the data we care about into a series of logical relationships called entities. The entities are described by a data elements known as attributes. Attributes are organized into instances of the entity and the instances are collected into a set, which is called a relation.

Later, in a process called physical design these logical constructs will be mapped to physical constructs. The physical counterparts are frequently much more recognizable.

| Logical | Physical |
| ———– | ——– |
| Relation | Table |
| Instance | Row |
| Attribute | Column |
Mapping Database constructs from Logical to Physical

So let’s think about the basic elements that we will have to capture in our database in order to create our family tree system. Considering the requirements state above we will need to represent a:

• Person
• Couple
• Family
• Event
• Media

Each of these elements is an entity in the design. To get a better understanding of entities consider the following excerpt from Jan L. Harrington’s book “Relational Database Design: Clearly Explained” …

“An entity is something about which we store data. A customer is an entity, as is a merchandise item stocked by Lasers Only. Entities are not necessarily tangible. For example, an event such as a concert is an entity; an appointment to see the doctor is an entity.”¹

Based on these descriptions we have …

| Entity | Description |
| ———– | ——– |
| Person | An individual about which we store data.
| Couple | A set of individuals that have bonded together.
| Family | A couple and their offspring as a set of people.
| Event | An action that has occured and is worth documenting.
| Media | A pre-defined component that we store and associate to other entities within our database.
| Location | A place where events have taken place in our family tree.
Logical Entities for our Application

“Entities have data that describe them (their attributes). For example, a customer entity is usually described by a customer number, first name, last name, street, city, state, zip code, and phone number. A concert entity might be described by a title, date, location and name of the performer.”²

Now, let’s start defining the attributes which describe our Person entity.

| Person
| ———– | —————————- |
| id | An assigned number which uniquely defines this person.
| given | An individual about which we store data.
| surname | A set of individuals that have bonded together. | birth_surname | The family name given at birth. (aka. Maidename)
| gender | The gender or sex of the individual. | adopted | Indicator if this person was adopted
| birthDate | A couple and their offspring as a set of people.
| deathDate | An action that has occured and is worth documenting.
| birthloc | The location of this person’s birth
| deathloc | The location of this person’s death
| internment | The location where this person was buried
| occupation | The primary occupation of this person.
| notations | Notes concerning this person.
Entity/Attribute Definition for Person

“When we represent entities in a database we actually store only the attributes. Each group of attributes that describes a single real-world occurrence of an entity acts to represent an instance of an entity.”³

Person

So we understand that entities are anything about which we store data in the form of attributes. Attributes about a single entity are gathered together to form a single instance of an entity. Multiple instances of an entity, are grouped together

To begin with we will have an entity known as a person.

(WN) Insert External link to definition of entity

There are other entities that we immediately can recognize in this particular application there will be a family there will be relationships between families there will be relationships between people and their families there will be various forms of media including documents, video, Images, and or audio recordings. All of these various types of meat you will have relationships to people and various types of family units and when I say family units I’m referring to a couple versus a family. Each of these entities then has a collection of attributes which must be specified for the entity if I consider the person entity we will have to create the attributes for the person to have a name we will have a birthdate we will have a death date we could have a marriage date but if you think about it that makes more sense as an attribute of a couple because an individual does not get married in isolation we could have a list of children but that really belong to a person as much as to a couple or family.

I’m not going to take the time to do a full analysis of the data structure because that is going to get into techniques that are outside the scope of what were actually discussing in this book. Please refer to appendix B to see the results of this process and if you want to investigate the process more deeply you might check insert reference books on data normalization and relational database design.

Dictation Segment 2

(Writing note) Focus on process rather than techniques Exclusive use of open-source software References to be added to the forward

Once you have your schema and you can find ours and appendix be interesting question is how do you apply that schema and create an actual database in the cloud? In our case were going to use the Amazon cloud service we’re going to use the MySQL open source relational database engine and we’re going to use MySQL work station which is an open source tool for me.

(Writing note) insert in the section illustrations and specific process instructions on how to create a database using Amazon and how to install the workstation then illustrate using workstation to actually populate an instance of a database with the schema that’s been provided in appendix B.

So no we have a relational database it’s been populated with our particular schema for a family tree application. There are some validation steps which are necessary at this point to make sure that the schema works because there’s no point in developing code around a data scheme of that is flawed. It’s also a good practice to create some views of the database to simplify it use and keep the complexity of its use as compartmentalized as possible.

For those of you who are not from layer with the database of you a schema meet will frequently referred to contents of a foreign entity. Boy this is starting to sound very jargon field isn’t it? We’ll see if we can break this down more simply we have an into tea call the person that person may well get to know another person and they might form a couple. In a relational database the way will see that is will have a person entity that has two records in at one for him and one for her. Next will have a couple entity and this will have one record the represents the two of them together. It would be a waste of space to list all of the information we know about a person in the couple record all over again you do have their birthdate their name anything we know about them would be duplicated that’s very inefficient and causes problems and databases. To work around that problem we’re going to store all the individual knowledge about the person in the person entity in the couple entity will simply refer to two person records so if you think of him as record number three and her his record number one the couple record now consists of a single record that says record number one got together with a record number three and if you want to know any details about their names you have to go over and pull the details from the person entity. As you should be able to understand at this point dealing with raw data tables in a relational database can lead to a lot of very unusual or unfriendly displays. The resolution of that situation is frequently the use of views.

So we will create a view for the couple entity and when you reference the view instead of getting back a list of numbers of other person records you’ll actually get something meaningful like the name of the man and the name of the woman and perhaps their wedding date tour anniversary date far more useful than native data tables.

To create a view, basically you can create a query that produces the results you desire then save that query in the database.

Creating a view in this manner allows you to subsequently reference the view as if it were table. The more complicated query gets executed and you get back the desired results without having to deal with the complexity of the more sophisticated underlying query.

As a best practice, I recommend that you create views which return lists rather than single rows. A list can always be qualified by secondary where clause to return only a single row making it far more reusable. However, a view that returns a single row is much more limited in usage.

(writing note) insert an example of creating a view with illustrations.

(Writing note) we need to insert a description of the use of foreign keys in relational databases without the jargon

(Writing note) insert a section on defining the characteristics of a relational database industry terms

Dictation Segment 3

(Writing note) augment description of starting with the database with the possibility of starting with the infrastructure of the web app code

• We could also have started by establishing the application architecture with in our web application There are multiple ways to accomplish this I will not be reviewing each of the possibilities but will go directly to my preferred method
• preference of model, view , controller pattern using Java beans

(writing note) insert reference to documentation of this pattern

• With struts and without stru

(WN) insert a section about testing and validation at the Database Level

(WN) insert references to master detail presentation and the common usage of selection lists - noting that views don’t need to contain all of the information as they will typically be used to present selection lists

# relational databases

Relational database is our very frequently used in data processing applications. Well this is not new technology by any stretch of the imagination it is very effective technology. A relational database can be thought of as a collection of tables and the tables can be related together in order to show information was this taken from the beginning and it’ll probably make more sense

When we talked about analyzing our data we briefly discussed an entity was going to that a little further. An entity is one concept it is one something that we might want to keep information about all of the information for that particular something should be stored in one table which represents the entity. For example if the entity is person then we would probably store attributes such as name age maybe birthdate haircolor anything that is information about that single person. Notice I said that single person that’s because each role in the table call person represents one person the table itself may represent hundred 2000s of people with each person being represented by information in one room. One of the interesting concepts that everyone should understand with the relational database is that these Rose have a special column which is referred to as its primary key. A primary key is the special column which uniquely identifies that person from every other person in the person table. As I said there may be many many people with their information stored in this table but if the key is the primary key knowing its value allows me to isolate one person separate from all others.(Writing out) this description lost hang out we need to find some way to make it more straightforward. So it’s taking example I have a person table and it may have many rows in at each role in the person table will contain information for one and only one person. That’s a very important point I need to say that again each row contains information for one person only. Earlier we talked about relational database as we were doing the analysis of our data needs. In the analysis phase we talked about an obstruction called an entity. An entity represents a single thing or a single concept frequently entities can be described using now owns a car a person house there one single thing. We can put multiple entities together into a table when we do that by convention that table will have the name of the entity so our table name would be person let’s say when we start that are person table has 10 rows each row represents one individual of a group of 10 the table represents the entire group the individual roll represents one person. Now in order to enable the database to locate the record for a single person we have to have a special column designated in that role. This column is called the primary key. The primary key is a special value which will uniquely identify every person and separate them from every other person that might be in the same table. Let’s imagine for a second that we use the Social Security number as the primary key. Because we know that within our country Social Security numbers are not shared between people it’s a valid value to use as the primary key in a person table. It does come with some risks but those have to do with the character characteristics of that particular date element it’s probably not a good choice but it did illustrate my point so what we need to remember is that a table is a collection of Rose and everyone of those rose contains a different entity but they all contain the same type of entity they all contain in our example a person and that there is a primary key that allows us to separate a person from any other person in that table The important point that we’ve just discuss is the notion of the boundaries of the table what can go in it and what camps and the fact that each row must be uniquely identified by a primary key. Each row must be identified by primary key if member if if your primary key is going to be made up of Social Security number each role as to have a different social security number it has to have a unique value underline you need OK so now we have a table and we have its primary key and it contains a variety of other columns all of which have to do with the entity a person and those other columns when you’re designing a referred to as attributes when you implement the table of course they become columns now is when it gets interesting. The value of a relational database is that you can make relations across tables for example if I have a person table I could have a couple table and that would show me the relationship it’s up to Persons I know it’s supposed to be people but I’m sick with me here that exist in the person table but they’re in a relationship so they are a couple in the way we do that is with something called a foreign key. A foreign key is a column in an outside table that points to a column in a primary entity for example we talked about a person table and now I’ve brought up the notion of a couples table the couples table might have a column called father it might have a column called mother strike that go back it might have a column called husband it might have a column called wife The value that I put in those columns will be the primary key from the person table that represents those people. Remember a minute ago I suggested you might use a Social Security number as the primary key so what would you expect to find in the couple table? You’re going to find one row because it represents one entity that being a couple and a column named husband that has the Social Security number of the man who is the husband and another column called wife that contains the Social Security number or the primary key value from the person table this is called a relation that table couple tells us about a relationship of two different people. The value of a relational database is it allows us to recognize that relationship without having to make a copy of all of the information in the people table. In previous technologies it would’ve been necessary to copy the name the birthdate the various columns of the husband and the wife into the couples table with with relational database technology that is avoided so you avoid the problems with having to keep things in sync and up-to-date in multiple places. (Writers note) insert section prior to the relational database helping the novice understand why this is important information so they’re willing to stick with it and get through it

• Technology
  • Java
  • J2EE (Servlets, Java ServerPages)
  • MySQL Relational Database
  • Eclipse