For the SQL part of the assignments 2 and 3 you will use the Mimer SQL 9.2.2 database and the graphical user interface WSQL. They can be downloaded from: http://developer.mimer.se/downloads/index.htm. Instead of WSQL you can also use the command line interface Batch SQL, which is part of the Mimer instalation.
However, there are no Mimer SQL Engine version 9.2.2 to download for users of the Windows 98 operating system. Therefore, users of such operating systems are refered to the bottom of this page for some brief setup instructions regarding Mimer SQL Engine version 9.1.3.
2) Set PATH environment variable to point to the directory where you installed Mimer 9.2.2 if that's not already done by installation program.
3) Create a new local database called for example 'DBTEK' with the 'Mimer Administrator' program located in the Mimer SQL 9.2.2 directory. You will also get prompted to choose a password for your username 'SYSADM'. When the creation procedure prompts for system databanks creation, answer yes.
4) At the end of database creation procedure choose to start the wizard to create a new developer user and a databank. A new databank is needed in the database 'DBTEK', for example call it 'Company' with corresponding file e.g. 'Company.dbf', where the user tables will be stored.
1) Download schema and data for Jonson Brothers database:
company_schema.sql
company_data.sql
2) Start the program 'Batch SQL' in the Mimer 9.2.2 directory (or in submenu Utilities in the Programs/Mimer menu).
3) Load the scripts that create the Jonson Brothers database schema
and populate the schema with data by issuing the following commands
at the SQL> prompt:
SQL> read all input from 'company_schema.sql';
SQL> read all input from 'company_data.sql';
If the files are not stored in the Mimer directory, you need to provide the full path name. Now you are ready to work on assignments 2 and 3.
Mimer 9.2.2 SQL installation on PC has command line interface + GUI (WSQL). There are two ways to enter and execute SQL commands in command line setting. Either you can type directly at the console OR type them in a separate file and then load the file. The first one is useful for experimentation, whilst the latter is the preferred way of work because that allows you to edit the commands more conveniently in a text editor and save your work. Of course you can also use the GUI.
To read SQL commands from a file you have to use the 'READ INPUT' command:
SQL> read all input from '(sql_file_name)';
Here (sql_file_name) is either a file in your current directory or the
full path name of a file with SQL commands.
Be aware that when you read more than once an SQL file that contains
DDL statements, you first have to drop (in the correct order) all the
database objects created in the file to avoid errors.
You can see how this is done in the schema definition file for the
Jonson Brothers database:
company_schema.sql
You can create runnable files with SQL commands in which all other
text is entered as SQL comments (prefixed with '--'). To generate your report file you have to log the execution of the SQL command file. Logging can be set by the following lines at the beginning
of your SQL command file:
SET OUTPUT ON;
SET ECHO ON;
SET PAGELENGTH 0;
SET INPUT, OUTPUT LOG ON;
LOG INPUT, OUTPUT ON 'dbt-(lab_number)-(user_name).log';
In particular for Assignment 3 (SQL) you are required to use as a starting point for the SQL command file the template file:
Copy this file to your work directory and rename it to whatever you like. Fill in the answers to the questions below the corresponding numbers, run the file when you are ready and print the resulting .log file. Please read the additional comments in the template file. Be aware that output filename will delete previous text from the file filename!
An example file with framework for user abcd1234 handing in Assignment 3 looks like this dbt-3-abcd1234.log
You can always erase your existing database and start from an empty one by deleting all *.dbf files in your working directory and repeat step 5 under section "How to create.." above.
Before you start working on your assignments, you have to create the sample database for the assignments 2 and 3. For this you have to complete the following steps:
1) With the help of WSQL, create a new databank in the datasource 'DBTEK' called 'Company'. You will be prompted to provide your username 'SYSADM' and choosen password.
2) Download schema and data for Jonson Brothers database:
company_schema_v9.sql
company_data_v9.sql
3) Do not set logging of execution of SQL command files. Instead copy and paste executed commands and comments into .txt file.
4) Start WSQL. Make sure that 'Auto Commit' is activated.
Copy and paste the scripts that create the Jonson Brothers database schema
and populate the schema into the window in WSQL where you can execute your own SQL commands.
Now you are ready to work on assignments 2 and 3.