3rd Session 11/10/2011 Digital Information Technology Architecture

Databases

During this session we looked at ways in which to locate and manage data within a database. We briefly looked at how the introduction of computers in the work related area in the 1950s and 1960s meant the increasing need to be able to store and retrieve larger amounts of data.

Databases were defined as "an integrated collection of data shareable between users and application systems" (Butterworth, 2011). We looked at how the data stored within databases can be grouped and related in order for successful searches. For example, if you were looking for the name of a person/employee, just typing their name is unlikely to be enough information to locate the person in question; but because we can combine data searches (name, department, post code, etc.) we're able to narrow down the search and make locating their data more likely.

What is a Database?

A database is a collection of data tables. A data table is a two-dimensional table of data, made up of rows and columns. Databases should only capture the attributes needed and should discard any unnecessary information.

Designing a Database

The process of database design begins with what was termed in class as an 'Entity Relationship' model. The ER model then sets out the entities you need stored and retrieved within the database and the ways in which the specific information is related. We were told the ER modelling was too advanced for this course but that it was important to have a basic understanding of the rules that govern them.

Structured Query Language, or SQL

• Occasionally pronounced 'sequence'
• Been around since the late 1970s

The model created (SQL) was deemed so good that very little if no modernizing of it have been necessary. SQL is a language used for talking to database management systems and is in a sense a structured variation of English. It can be used to query whats in a database and to insert, modify or delete data from tables.

Database Solutions

Database solutions are ways in which to store all necessary data in a central database, decreasing the likelihood of any inconsistency & redundancy (that is, redundant data). It was mentioned how it's good when you own the data as you can then structure it your needs, make it homogeneous in structure and it's easy to ensure your searches are ran and completed smoothly.

Note: There was a lot more info but I'm not sure how much use it would be to (attempt) to elaborate any further.

Lab Session 3

We were first given some practical exercises (as copied from Moodle below):

Task 1 : Log on to your Unix Account

Please use your normal username/password to log on to your unix account as follows:

Login to the swindon computer, which a Unix machine operated by the Central Services. You will need to type your username and password to log in to swindon - for security reasons the password may not show up as you type :

• From City Windows Labs - Run the Telnet utility Start > Programs > O to P > Putty 0.60 > Putty. Where it says Type in Host Name (or IP Address) , enter swindon.city.ac.uk and click the Open button to login. We recommend that you select the ssh option.
• From off campus use SSH - Run the ssh program from your menu. Click the Quick Connect button and type in swindon.city.ac.uk where it says Host Name:. Click the Connect button to login. You should then login to vega using 'ssh vega'. SSH is available on the module software page.
• At the command menu on unix, you will see the option [U] Unix shell: type 'u' to obtain the unix command line.

Task 2 : Start a MySQL Database Session

You now need to start a MySQL session. Please follow the instructions outlined in the task carefully so that you can run your queries.

1. Once you have logged in to swindon and have the command line ready, run the MySQL application by using the command: mysql -p -u biblio -h vega.soi.city.ac.uk(note that you should be able to copy this commands and others from this window in your browser and paste them into the SSH window either by right clicking and selecting '>Paste' or by clicking the right mouse button in the SSH window)
2. When prompted, use the following password to open the biblio database: b1bl10 (bravo-one-bravo-lima-one-zero)
3. Load the biblio database by using the command: use biblio;

This was just in order to get us into the right programme to carty out the tasks. From here we were asked to familiarise ourselves with the My SQL and 'biblio' Database.

Task 3 : Familiarise yourself with MySQL and the 'biblio' Database

Have a look at this diagram - its shows the relationship between tables in the 'biblio' database, and the data which is held in each table. Familiarise yourself with the information held and the relationships. You'll need to refer to this diagram to formulate your SQL queries.

You can find out more about MySQL and the biblio database by using the following commands :

• show tables; - show what tables are available in the biblio databases.
• desc authors; - show details of one of the database tables (in this case authors).

Also note that MySQL commands must be ended - usually with a semicolon ';'
An alternative is to use \g or \G to format the output in different ways.
Try issuing some SQL queries from the MySQL command line and ending the commands with :

• \G - record by record view, e.g. :
          select year_published, title from titles where year_published < 1970 \G
• \g - output table view, e.g. :
          select year_published, title from titles where year_published < 1970 \g

Then came the 10 tasks, of which I got to number 6.

Task 4 : Querying a Database

Develop SQL queries to return following information :

1. A list of the PubID, Name, Company Name and City for all publishers based in the city of New York
2. A list of all fields for publishers named Prentice Hall.
3. A list of the Title, Year and ISBN for all titles published in 1994.
4. A list of the Title, Year, ISBN and PubID for all titles published since 1980 in year order
5. A list of all fields in the Titles table for books whose title begins with the word 'database' (regardless of upper/lower case letters)
6. A list of all fields in the Titles table for books whose title with the word 'database' anywhere in the title (regardless of upper/lower case letters)
7. A list of the title, Year Published and ISBN for all books with 'SQL' in the title written since 1990 in date order
8. A list of the Company Names of all publishers who have published books on programming since 1990
9. The name of the publisher who published a book with ISBN 0-0280074-8-4
10. The name of the author who wrote "A Beginner's Guide to Basic" listing also, the ISBN and name of this book.

Below is a simple illustration of the procedures I followed to complete the first 5 tasks:

Task 1:

SELECT Pubid, name, company_name, city
FROM publishers
WHERE city = "new york"; (; is needed when wanted to end the command)

Task 2:

SELECT * (* stands for all or everything)
FROM publishers
WHERE company_name = "prentice hall";

Task 3:

SELECT title, year_published, isbn
FROM titles
WHERE year_published = "1994";

Task 4:

SELECT title, year_published, isbn, pubid
FROM titles
WHERE year_published > 1980
order by year_published;

Task 5:

SELECT *
FROM titles
WHERE title like "database%"; (% is used (as Richard said) as a "wild card")

Even though this was the toughest lab sessions so far, I also found it the most enjoyable.