LAB 10 Create a simple database with two tables

CTI.110Module 3 Lab 7A[Type here]

______________________________________________________________________________________________________________________________________________________________________________________________________________________

CTI.110Page 22

LAB 10 Create a simple database with two tables

Objectives:

· Create a database with two tables

· Add user access to the created database

· Use a Web Stack with a GUI database administration tool

· Use the command line to connect to a database

Basic concepts for creating databases

In this lab, we will work with the three language subsets of SQL:

· Data Definition Language (DDL),

· Data Control Language (DCL), and

· Data Manipulation Language (DML).

We will be using commands from the DDL and DCL subsets. With the DDL we will create a simple database with two tables. We will control access to our database using the DCL. The tool that we will use is called phpMyAdmin, part of the XAMPP install package.

phpMyAdmin is a DDL, DCL, and DML GUI interface used to create and manage MySQL/Maria databases. Another popular GUI interface for creating and managing MySQL/Maria database is .

Remember behind the GUIs are the actual SQL statements. These statements are the same statements we would use in our code when creating applications that work with databases or working at the command line without a GUI interface. We will work with the mydatabase database using the Data Manipulation Language (DML) at the command line and in our code in later labs.

This lab creates a database named mydatabase with two tables ( personnel & timesheet) and adds a user and password to access the mydatabase database. This database is used in the upcoming labs, so follow the instructions closely to make sure your database is setup correctly.

Lab Task Checklist

Complete the following tasks for this lab:

1. Review the Lesson (Assignment, Reading and Additional materials)

2. Create a Database called mydatabase

3. Add the cti110 user with a password of wtcc for mydatabase

4. Create the following tables (see Database Requirements below):

a. personnel

i. empID, firstName, lastName, jobTitle, hourlyWage

ii. populate the table and add data to columns

b. timesheet

i. empID, hoursWorked

ii. populate the table and add data to columns

5. Submit Assignment files:

a. lastname_mydatabase.sql

b. lastname_designer

c. lastname_login

where lastname is your last name.

Database Requirements:

Database Name: mydatabase

Tables:

· personnel

· timesheet

User Account:

· Username: cti110

· Password: wtcc

Table Structure:

personnel:

Column	Data Type	Null	Links to
empID	int (11)	No
firstName	varchar (64)	No
lastName	varchar (64)	No
jobTitle	varchar (64)	No
hourlyWage	float	No

timesheet:

Column	Data Type	Null	Links to
empID	int (11)	No	->personnel. EmpID
hoursWorked	int (11)	No

Table Data:

personnel records (empID, firstName, lastName, jobTitle, hourlyWage)

· 12345, Chris, Smith, Sales, 12.55

· 12347, Mary, Peters, Sales, 12.55

· 12348, Mike, Jones, Manager, 24.15

· 12353, Anne, Humphries, Accountant, 25.45

· 12356, Ann, Jones, Sales,13.75

· 12357, John, Jackson, Reception, 8.75

· 12358, John, King, Cleaner, 7.75

· 12360, Ken, Stewart, Accountant, 28.55

· 12361, Joan, Smith, Cleaner, 8.25

· 12363, Jesse, Andrews, Sales, 10.75

timesheet records (empID, hoursWorked)

· 12345, 30

· 12347, 35

· 12348, 40

· 12353, 35

· 12356, 20

· 12357, 40

· 12358, 32

· 12360, 20

· 12361, 32

· 12363, 35

Lab Instructions for creation and setup of database

1. Select Admin to open Admin Console

XAMPP Control Panel - Select Admin for MySql

2. Once the Admin Console opens (in your browser), select the Databases tab.

Select Database tab

3. Create the mydatabase database (fill in the required values and click Create)

Create the mydatabase

4. Create a new user for the Database mydatabase

Step 4 Select the Privileges tab

5. Make sure mydatabase is selected

To the Left select mydatabase and Privileges tab

6. Select Add user account

Shows users having access to mydatabase. Select Add user account

7. Fill in the required fields

Fill in the information: Username: cti110 hostname: localhost password:wtcc Grand all privileges

8. Scroll down the screen, set the privileges ( Check All), then Go.

Select the check all

9. The resulting screen should include a confirmation message indicating the successful addition of your user. Select the User accounts tab to return to the User accounts overview page

Select the User Accounts

10. The new user (‘cti110’) has now been added.

new user cti110 has been added

NOTE: If you were creating a user at the “command prompt” or in your code, the Data Access Language (DAL) SQL statement would be similar to this:

SQL of create user example

11. Select the mydatabase. You should now be in the Structure Tab.

Select mydatabase, structure tab

12. Create the “personnel” table. The “personnel” table contains 5 fields/columns. Enter the table Name, Number of columns and click Go.

Server 127.0.0.1 Add personnel table with 5 columns

13. Define the information required for this new table using the information provided in the Table Structure section. Enter the Name, Type, and Length/Values for each column.

Input the Name, type and length values for: empid firstName jobTitle hourlyWage

14. Before leaving this screen, click on the Index field associated with empID to define it as the table’s PRIMARY key. A pop-up window will open. Click Go and Save to complete the process.

Select Primary define as primary select go then save

NOTE: If you were creating this table at the “command prompt” or in your code, the Data Definition Language (DDL) SQL statement would be similar to this:

Example SQL for Create Table

15. The resulting screen shows the action was successful [Server: 127.0.0.1 >> Database: mydatabase >> Table: personnel].

Shows structure of personnel table

16. Make empID a UNIQUE Key. In the Indexes section, the correct column number is already selected (1), so simply click on Go.

Create index 1 and select GO

NOTE: If you were modifying this table at the “command prompt” or in your code the Data Definition Language (DDL) SQL statement would be similar to this:

SQL example for alter table

17. Enter the index name ( empID) info and click Go.

enter index name empid

18. Review the details for the mydatabase database, and specifically the personnel table, in the left-hand navigation pane. Expand all the “+” signs to see the underlying details.

screenshot of menu on the left showing personnel and index

19. Now, create the timesheet table by repeating the same steps used to create the personnel table. Reference the correct Table Structure information for the column and index details.

Create timesheet table primary empid ad hoursWorked

go back to left menu...select new

20. Enter the required data, make empID a PRIMARY index, click Go and Save.

Enter required data

NOTE: If you were creating this table at the “command prompt” or in your code the Data Definition Language (DDL) SQL statement would be like this:

SQL Example of create table timesheet

21. The resulting screen shows the action was successful [Server: 127.0.0.1 >> Database: mydatabase >> Table: timesheet].

Structure of timesheet

22. Make empID a UNIQUE Key. In the Indexes section, the correct column number is already selected (1), so simply click on Go.

Make empId unique key

23. Enter the index name ( empID) info and click Go.

NOTE: If you were modifying this table at the “command prompt” or in your code the Data Definition Language (DDL) SQL statement would be similar to this:

SQL example alter table

24. Review the details for the mydatabase database, and specifically the timesheet table, in the left-hand navigation pane. Expand all the “+” signs to see the underlying details.

Review details mydatabase

25. Now it’s time to add the data. Starting with the personnel table …

select personnel, select insert to add records

26. When the data entry page opens, use the Continue insertion with # rows drop-down menu to increase the number of entries to 10 to get 10 empty slots for data. Enter 10 rows

27. Enter the personnel data provided in the Table Data section. When all 10 entries are complete, click Go to save the data.

Enter personnel data

… more data entries …

when done Select Go

28. The resulting screen should include a confirmation message indicating the successful addition of 10 rows of data. DO NOT PRESS GO HERE!

Caution do not press go

29. Select the Browse tab to see/review the personnel data you entered.

select browse tab to review personnel data

shows records (10)

30. Now, add the timesheet data following the same procedure used for personnel. Start by selecting timesheet and allow for 10 rows of data.

add timesheet data

Select 10 rows

31. The resulting screen should include a confirmation message indicating the successful addition of 10 rows of data. REMEMBER: DO NOT PRESS GO HERE!

do not press go warning

32. Select the Browse tab to see/review the timesheet data you entered. The data information should look like this:

browse timesheet data

You have now successfully created the database mydatabase. This database will be used in future labs, so it is important to create the database, tables and indexes correctly!

Submissions Required:

Creating files for submission:

1. lastname_12_mydatabase.respective extension (docx or sql)

a. Select the mydatabase database in the left navigation area.

b. Select the Export tab.

c. Change the Format to SQL (can be used as backup later) or Word and select Go.

export sql

d. Upload the file in Blackboard.

2. lastname_12_designer.pdf

a. Select the mydatabase database in the left navigation area.

b. Select the Designer tab.

c. Take a snapshot and paste it into a Word document.

Note: It should look something like the following:

create designer for mydatabase

d. Save the file as lastname_11_designer.pdf

e. Upload the file in Blackboard.

3. lastname_login.docx

a. Launch your command line interface.

You may press the Windows logo key plus the ‘r’ key (i.e. hold down Windows logo key + ‘r’ key) and type ‘cmd’.

b. Your Windows command link window pops up.

c. Assume your thumb drive is on g: drive, and XAMPP has been installed in folder CTI110.

a. Change your drive from C: to g: drive

b. Change directory by typing cd cti110xamppmysqlbin as shown in the following:

LAB 10 Create a simple database with two tables