SQL Practice using mySQL on the Alactritas server PDF

Preview

Summary

Practice for my SQL for workshop 01. This document contains instructions on how to connect to the alacritas server on network and use mySQL to practice the examples given.
...

Description

1. Creating Databases In this unit, we are creating databases in MySQL Data Definition Language. This is reasonably simple. Perhaps the most complex parts go beforehand when we derive our ER Diagrams and then convert them to our relational schemas. There are several ways to create a database in MySQL, including using graphical user interfaces. We won’t be doing that. Using command line MySQL is essential for subsequent use – such as embedding MySQL code into websites and applications – so you’re going to learn command line MySQL. To make this easier, you’re going to put your MySQL DDL into a text document and load that text document into MySQL using Unix.

1.1.

Connecting

1.1.1.

On a PC

In File Explorer, you will have direct access to the M: drive (It may say homedrive.unix). This is Alacritas, the Unix server on which you will be using MySQL. If you can’t see Alacritas from File Explorer, then try syncing your UTAS and ICT Passwords. You can do that here: https://password.cis.utas.edu.au/sync/kiosk.php You will then need to log off and on again. If that doesn’t work, then you will need to go to the help desk. Open Alacritas, create a folder called KIT102. Inside the KIT102 folder, save the text file called SQL01.sql from MyLO. Open SQL01.sql using Notepad++. You will see some sample MySQL DDL. Remember to change ‘username’ to your username before saving the file. Next, in the Windows Menu, open PuTTY. Enter the Host Name – alacritas.cis.utas.edu.au

And then click Open. There will be a Security Alert. Press Yes.

Then, you will receive the prompt, “login as:” Enter your University username, and then password. That will log you into Alacritas’s command line interface.

1.1.2.

On a Mac

On the desktop, you will need to connect to Alacritas – the Unix drive that you will use for SQL. Press Command and K. (⌘+K) A ‘Connect to Server’ box will pop up. In that box, you should type: smb://alacritas.cis.utas.edu.au It will then pop up another window asking for your Name (put in your username) and Password. Use the password that you used to log into the computer. Click on the option that is your username, and then click OK. This will open a window on the desktop. This is a Graphical User Interface to the Alacritas Unix server. If your password didn’t work, then try syncing your UTAS and ICT Passwords. You can do that here: https://password.cis.utas.edu.au/sync/kiosk.php You will then need to log off and on again. If that doesn’t work, then you will need to go to the help desk. In the new window on your desktop, create a folder called KIT102. Inside the KIT102 folder, save the text file called SQL01.sql from MyLO. Open SQL01.sql using BBEdit. You will see some sample MySQL DDL. Remember to change ‘username’ to your username before saving the file. Next, click the Shortcuts icon, down the bottom of your desktop, choose Utilities, and Terminal. Enter the Unix Command: ssh alacritas.cis.utas.edu.au It may prompt you to ask if you want to continue connecting. Type yes and press return. Then enter the password that you used to access the computer. IT WILL NOT ECHO TO THE SCREEN. You will be typing, but you will not see it reflected on the screen. Just trust that you remember your password – you can do it!

1.2.

Getting Started

Now, we will change directory to the KIT102 folder: cd KIT102 ls Now you should see your file SQL01.sql.

The command to ‘run’ your SQL01.sql file is this (replace ‘username’ with your username. Your MySQL password is your student number): mysql -t -u username -p < SQL01.sql And the command to open MySQL if you want to play with it manually is this (replace ‘username’ with your username. Your MySQL password is your student number): Mysql -t -u username -p For this activity, we will be using your personal database, to use your database use this command (replace ‘username’ with your username: use username; Or to see the other databases that you have access to, use this command: show databases; To exit MySQL, type exit. To exit Alacritas, type exit.

1.3.

Some Hints

These are some hints to help you create your databases with as few problems as possible.  



  

At the end of each MySQL command, you need to have a semi colon ; Create your tables in this order: 1. Tables with no foreign keys 2. Tables with foreign keys that are related to tables that you have already created Each time that you ‘run’ your file in MySQL, it will try to create your tables. This will work the first time, but after that, it will throw errors because the tables already exist 1. Run your file the first time 2. Add DROP TABLE commands at the start of your text document so that tables you have already created will be deleted before they are created again. 3. Your DROP TABLE commands should be in the reverse order from the order in which your tables are created If you make a mistake, sometimes it’s easier to log directly into MySQL and drop the tables manually rather than using your file. If it looks like nothing happened – then it worked! If you get an error message it didn’t work. If you want to see what happened – go to the end of this document for instructions.

2. Create Databases from Relational Schemas For each of the following relational schemas, create matching tables within your username database in MySQL on Alacritas.

2.1.

Visiting the General Practitioner

This example is worked for you in the SQL01.sql file. Have a look at it to see how it works before moving on to the next examples. Here is the Relational Schema: RECEPTIONIST(ReceptionistID, [pk] Name) PATIENT(PatientID, [pk] Name, Address, Age, Religion, PartnerStatus, SexualOrientation, TravelMethod, ReceptionistID [fk])

2.2.

The Visa Card Foundation

COUNTRY(CountryID, [pk]) PROJECT(ProjectName, [pk], CountryID [fk]) LENDER(LenderID, [pk]) ENTREPRENEUR(EntrepreneurID, [pk] ProjectName [fk]) LENDSTO(LenderID [fk1], EntrepreneurID [fk2], [pk] MonthlyPayments)

2.3.

Tamar Travel Agency

CLIENT(ClientID, [pk] GivenName, Surname, Address) TRAVELCONSULTANT(PayrollNumber, [pk] DateAssigned, HoursSpent) HOLIDAY(HolidayNumber, [pk] ClientID [fk1], PayrollNumber [fk2]) ACTIVITY(ActivityID, [pk] Provider, EstimatedDuration) CONSISTSOF(HolidayNumber [fk1], ActivityID [fk2], [pk])

3. Insert Data into your Tables Start inserting some data into your tables. There is an example in SQL01.sql. Try it for the other tables.

4. Seeing what happened This is optional for the truly curious! Try out these commands in MySQL. Remember to replace username with your username. Your MySQL password is your student number mysql -t -u username -p;

use username;

describe RECEPTIONIST;

describe PATIENT;

select * from RECEPTIONIST;

select * from PATIENT;...