programming 5

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

______________________________________________________________________________________________________________________________________________________________________________________________________________________

CTI.110 Page 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 MySQL Workbench.

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

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

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

4. Create a new user for the Database mydatabase

5. Make sure mydatabase is selected

6. Select Add user account

7. Fill in the required fields

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

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

10. The new user (‘cti110’) has now 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:

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

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

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.

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.

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:

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

16. Make empID a UNIQUE Key. In the Indexes section, the correct column number is already selected (1), so simply click on 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:

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

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.

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.

20. Enter the required data, make empID a PRIMARY index, click Go and 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 like this:

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

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

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:

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.

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

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.

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

… more data entries …

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

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

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

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

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

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.

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:

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 cti110\xampp\mysql\bin as shown in the following:

You can now access MySQL by typing: mysql –u cti110 –p

When prompted for a password, enter the password mentioned above ( wtcc).

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

Note: It should look something like the following:

e. Save the file as lastname_login.docx

f. Upload the file in Blackboard

Grading rubric for Assignment

Database (mydatabase), Table (personnel), Table (timesheet) setup correctly

Data for personnel and timesheet is setup correctly

Data Type for all Fields set up correctly

Primary and Unique keys set up correctly

User Account set up correctly