Question

In this assignment, you must use the MySQL Command Line Client Window.

• READ ALL INSTRUCTIONS BEFORE STARTING.
• Install MySQL Server if you haven't done it yet. Some instructions are posted here: MySQL
• You'll need to take screen shots throughout the assignment (more details below).
• You may change the properties of your MySQL Command Line window by right-clicking on the title bar (or the icon on the upper left corner of the window), and selecting the Defaults menu.
  • This will allow you change the size and color of the window and the fonts.
  • You should increase the screen buffer size (height), in the Layout tab, to allow you to keep more lines available to review (when you scroll up).
  • The Command History buffer size, in the Options tab, is not the same as the above. It is the number of commands available when you use the up and down arrow keys to review previous commands. You may increase that too.
  • Remember that the command history is gone when you close the MySQL Command Line Client window. Your databases and tables are saved and will still be there when you log in again.  

Perform all the following steps in order. Some steps are repeated on purpose.

Open the MySQL Command Line Client.

1. The first query you must do, before creating the database is:

SELECT NOW();

2. Create a database named 'school'.

• The 'school' database will have the following tables:

student (student_id, first_name, last_name, dob, home_street, home_city, home_state, home_zip)

faculty (employee_id, first_name, last_name, dept_id)

department (dept_id, dept_name, building_code)

• You do not need to specify primary or foreign keys yet.

• Choose appropriate data types and sizes for the fields.

• Make sure the fields student_id and last_name do not allow NULL values. Other fields should allow NULL values.

3. Create the above tables.

4. Load fictitious data into your tables:

• The student table should have at least 10 records. You should have 5 students with Tampa as their home_city. Other students should reside in other cities of your choice.

• The faculty table should have at least 5 records.

• The department table should have at least 3 records. The field building_code is a 3 letter code that identifies the location of the department, such as ENB, CPR...

• You should make up your own data (do not use data from another student).

5. Write (and run) SQL queries to:

• Show all existing databases in your RDBMS.

• Show the tables in the 'school' database.

• Show the structure of each table in the 'school' database.

• Show all the data in each of the three tables in the 'school' database.

• Show only the first name and last name of all the faculty.

• Show only the first name, last name and home city of all the students who live in Tampa.

6. The last query you must do, after everything is done is:

SELECT NOW();

• All of the above steps must be done in the MySQL Command Line Client.
• You must take screen shots of the above commands AND results (showing in your command-line client window). It is okay to include any errors you made along the way.
  • You may use the 'Snipping Tool' or 'Snip and Sketch' (on Windows) , or 'Grab' (on MacOS), or see here for other options: https://www.take-a-screenshot.org/  (Links to an external site.).
  • Please avoid taking pictures of the screen with a camera/phone as it can be very hard to read (blurry/glare/small), and more time-consuming for you than the above tools.

Answer 1