HIT326: Database-Driven Web Applications - IT Assignment Help

Assignment Task:

Task:

Part 1 –SQLite activities

SQLite only has a handful of data types. MySQL has many more. Please check out the following link and find out what those types are: http://www.sqlite.org/datatype3.html Note that for our purposes we shall only use four of the five ‘storage classes’ in SQLite. If you peruse Section 2.2 (Affinity Name Examples) you’ll see you can use data types from other database systems too. Complete the following tasks/questions. -Go to the Workshop location in LearnLine week 1 and download the archived file called “example_1.zip”. -Complete Part 1 and Part 2. -Continue to work on the below exercises.

Exercise 1 1. What happens to your table if you run the ‘load’ script over and over again (i.e. without running ‘create’)? You’ll have to ‘test’ it along the way to see what happens. 2

2. Re-create and load the original data again.

3. Comment out the SELECT statement in the ‘test script’. Now write (or copy and modify) the following SELECT statements, and observe the ‘tests’. Note what happens each time. SELECT name FROM users; SELECT user_id, name FROM users; SELECT name, user_id FROM users; SELECT name FROM users WHERE user_id='1'; SELECT name FROM users WHERE user_id='2' AND name='Alice'; SELECT name FROM users WHERE user_id='1' AND name='Alice'; SELECT name FROM users WHERE user_id='1' OR name='Alice';

4. Before you proceed to the next exercise make sure you retain a copy of the previous load files, especially the ‘test’ script. You may find these useful for future workshops and your project. You are about to do another download and extract. If you don’t move the current files they will be overwritten with new load files. Exercise 2

1. Download, extract, create, load and test ‘example_2.zip’ (Week 1) in the same way you did in Part 2 in workshop 1.

2. Examine the CREATE TABLE statement in the ‘create’ script. Note that it now has an extra field (or column) as follows: created_at DATE DEFAULT(datetime('now','localtime')) Also note the modification made to the ‘name’ field. It now has UNIQUE included like this: name TEXT UNIQUE NOT NULL

3. This automatically sets the creation date when new data is INSERT’ed from the ‘load’ script. Especially note the SQLite function called datetime(). ‘Now’ means the time as it’s represented on your computer. ‘localtime’ ensures it is adjusted to the Time Zone set on your computer. 3 UNIQUE ensures that the names must be unique for each new record. For example the database won’t allow more than one record with the name ‘Alice’.

4. Remove ‘localtime’ from the CREATE TABLE script. Create, load and test it again. What is the time on each record now? Put ‘localtime’ back after you see the result and ‘create’, ‘load’ and ‘test’ it again.

5. Please read about date functions and how SQLite handles dates and time at: http://www.sqlite.org/lang_datefunc.html What is UTC?

6. Try to ‘load’ the table data twice or more so the same records are attempted to be inserted. What happens? Why?

7. Try the following SQL statements in the ‘test’ script and observe the results:

a) SELECT * FROM users WHERE created_at > '2012-01-20';

b) INSERT INTO users (name) values ("Margo O'Brien");

c) Run the ‘test’ script again? What happens? Did you see an error? Why?

d) Comment out the INSERT statement.

e) SELECT * FROM users ORDER BY created_at DESC;

f) SELECT * FROM users ORDER BY created_at ASC;

g) What do DESC and ASC do? Part 2 advanced SQLite tasks Sometimes the data which you want to load into your database already exists in another format. We have only dealt with inserting several records so far. But what if there are thousands of records. You don’t want to be sitting there all day or week writing INSERT statements do you? CSV (Comma Separated Values) is a common plain text file format used to store records. Most database systems allow you to address the CSV file to directly insert the records. Now download ‘example_3.zip’ from LearnLine (Week 1 workshop) and extract them. Please read the ‘read_me.txt’ file about some of the modifications I have from the original CSV from http://openflights.org/data.html 4 This database has over 6500 records. Exercise 3 1. As usual inspect all three scripts as well as ‘airports.csv’.

2. In the ‘create’ script you should notice the usage of the data type REAL. Why do those particular fields use REAL and not INTEGER or TEXT?

3. The ‘load’ script is of most interest here. This were we load the CSV into the database. Notice there are only two lines (not counting comments) which do the trick. .separator "," This SQLite dot command, .separator, tells the shell that each value in each row in the CSV file is separated with a comma. .import airports.csv airports This SQLite dot command, .import, tells the shell to import the contents of ‘airports.csv’ into a table named ‘airports’.

4. The ‘test’ script has an SQLite dot command too. It simply ensures that column names (i.e. field names) are displayed. Can you see it?

5. Go ahead and create, load, and test the scripts. You’ll notice the ‘load’ take a little longer than previous examples. The first ‘test’ script will take some to print to the screen. Then try the following statements and tests and make sure you comment them out before you try new ones each time:

a. SELECT name, city, country FROM airports WHERE city = "Darwin";

b. SELECT name, city, country FROM airports WHERE country = "Australia";

c. SELECT name, city, timezone FROM airports WHERE country='Australia';

d. SELECT name, city, country FROM airports WHERE city LIKE "New%" AND country = "United States"; Note the LIKE “New%”. Notice the % sign. Essentially this query asks for cities beginning with “New” followed by zero or more characters AND where the country is the United States.

e. SELECT name, city, country FROM airports WHERE city LIKE "New York%" AND country = "United States"; f. SELECT name, city, country, latitude, longitude FROM airports WHERE longitude > 130 AND longitude <132 AND latitude < 0; g. When lots of records are printed to a screen it is not always easy to carefully inspect them and once you close the window they are lost. Sometimes you want a more permanent record of the results of your queries. In this case we may print to a file.

This HIT326 IT Assignment has been solved by our IT Experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.