How to Create a Database using SQLite

Solution Preview

Hello,

My name is Jim. I hope I will answer any questions you have.

The nature of this assignment is to learn the basic syntax of SQL using SQLlite.

The first thing you'll need to do is to decide what data types you'll need in your table (or tables). The entities you've listed, "date time height weight temp. Pulse Resp BP systolic BP diastolic provider id", seem to be almost all numeric values.

For the date and time, SQL has a specific data type for dates and times, called the "date" type. If you use that it can help you avoid problems like having a date like "September 31, 2013" entered. (September only has 30 days in it.) It also allows you to do things like date arithmetic, so that you can easily figure out how many days there are between two dates, like from September 18, 2012 to December 25, 2012. It also allows you to retrieve the date from the column in a number of different formats depending on what you want (like as "12/25/2012", or as 25-December-2012"). There's also typically a function that will allow you to retrieve a date/time stamp as an integer containing the number of seconds that have elapsed from January 1, 1970 until the date/time stamp in the specific row and column.

The numeric fields, weight, and blood pressure values are all typically integers (whole numbers without a fractional part or a decimal point and numbers to the right of it). So the normal "int" data type would be appropriate. In SQL they also distinguish between the various sizes of integers you might be storing. Obviously blood pressure and weight don't typically get very high, certainly not high enough to warrant the number having, say, 10 digits. So the smaller int data types would be more appropriate (like int(2) and int(4).

The temperature is also a numeric field, but typically is expressed as a real number or floating point number, as in "98.6" which is the normal body temperature in degrees Fahrenheit. Since you will want to store fractions the data type for the temperature column will have to be different from the weight and blood pressure readings. The specific name is usually something like "double" or "float". That's something you can look up in the manual.

You'll have to decide for yourself what type of field is best to hold the height in. You could store it as a simple character string, for example. Then the data stored in the database would look something like this: 5' 4", though storing it like this: 5ft 4in might be easier for you to manipulate with the queries, because a character string value is typically denoted with opening and closing parenthesis. Or, you could use your own convention and store it like this: 5,4, with the number to the left of the comma being feet and the number to the right being inches. Please keep in mind that if the field is entered into the database, or that column is qualified in the where clause of a select statement, you will have to enclose the height in character strings. That's what I was trying to make easier with the comma and the "ft" and "in". If you wanted to find all the people that are 5 feet 6 inches, you'd have to have something like this at ...