Even complicated and confusing topics will be easily developed and covered if you request our help writing an essay. Place an order today!

Lab 2: Work with Dictionary and Create Relational Database (60 pts.)

iLAB OVERVIEW

Scenario and Summary

In this lab, you will prepare a Data Dictionary based on the list of elements. Also, your task will be determined the tables, their relationships, primary and foreign keys. Based on this analysis, you will create Database Schema, relational tables, Entity –Relational Diagram (ERD), establish connection to your local MySQL Server, create physical database and insert data to the tables.

MySQL provides two primary types of file management: dictionary-managed files and MySQL Workbench-managed files. As part of this iLab, you will need to supply some information as to how you would use both of these approaches, and you will have to discuss some of the advantages of each.

For Step 3, you need access to your database instance. If you have any difficulties connecting your database instance, let's take error messages, screen shots, descriptions of the situation to the graded threads and work as a team to resolve issues.

Now you are ready to proceed.

Deliverables

Your assignment will be graded based on the following.

Assignment Step

Description

Points

Step 1

Create Data Dictionary for provided elements (Word document)

15

Step 2

Create SCHEMA and database tables in MySQL Workbench

15

Step 3

Establish connection to the MySQL Server (screenshots)

15

Step 4

Insert data to tables using MySQL Workbench

15

Total Lab Points

60

For Steps 1, 2, 3 and 4 create a single Word document and include the answers or solutions to all problems. Be sure to label your document and include your name and course number in the heading. Save your document as "yourname_Lab_2.docx."

Submit both "yourname_Lab_2.docx" to the Dropbox for this week.

iLAB STEPS

STEP 1: Create Data Dictionary for provided elements

As the DBA for your company, you have decided to install a new version of the MySQL database to replace the current database version being used. The old database has become a constant headache and seems to be causing an overload on the disk drive's I/O channels. Further analysis has also shown that two primary large tables are the main points of access. The new tables will be DEPT, EMPLOYEE, and BONUS.

Describe how you plan to compile the Data Dictionary and decide on the table’s structure with the new MySQL database.

Given list of elements:

NN

Attribute Name

Column name

Data Type

1

Employee number (PK)

EMPNO

NUMBER(4)

2

Employee first name

EFNAME

VARCHAR2(10)

3

Employee last name

ELNAME

VARCHAR2(20)

4

Job category (FK)

JOBCATEGORY

VARCHAR2(4)

5

Manager

MGR

NUMBER (4)

6

Hire date

HIREDATE

DATE

7

Salary

SAL

NUMBER (7.2)

8

Commission

COMM

NUMBER (7.2)

9

Department number(FK)

DEPTNO

NUMBER(2)

10

Department name

DEPTNAME

VARCHAR2(14)

11

Location

LOC

VARCHAR2(13)

12

Job title

JOBTITLE

VARCHAR2(20)

13

Job description

JOBDESC

VARCHAR2(20)

Compile Data Dictionary (in alphabetic order):

NN

Attribute Name

Column name

Data Type

Data element description

Table name

Primary key/ Foreign key indicator (P/F)

Not NULL

Default value

 

Department number

DEPTNO

NUMBER(2)

         

Place and save your answers in a Word document named "yourname_Lab_2.docx."

STEP 2: Create SCHEMA and database tables in MySQL Workbench

2.a Create SCHEMA

Launch MySQL Workbench;

Click File and choose ‘New Model’;

Add Diagram:

Name: new schema name;

Press ‘Enter’ and new SCHEMA will be added;

2.b Create tables

a) In Model overview (top part of the screen) Click ‘Add Diagram’; Navigation pane shows new schema in Catalog Tree;

b) Place a new table on the free part of screen;

c) Fill:

Table Name:

Column Name, Datatype; PK; NN; UQ;BIN; UN; ZF;AI; Default;

Press ‘Enter’

d) Continue to add all tables;

2.c Foreign key creation

a) Click on the bottom of the Form ‘Foreign key’ to establish the reference to parent table;

b) Choose the Reference table and Reference column;

c) Choose Foreign key options On Update and On Delete; Enter.

2.d Save database

a)     Choose ‘File’ on the Toolbar and Save Model as on your folder.

Established database are visible on Home page.

STEP 3: Create and configure a new connection to the MySQL Server

Part 1 Create a new connection to the MySQL Server

Launch to MySQL Workbench Home page;

To add a connection, click the [+] icon to the right of the MySQL Connections title. This opens the Setup New Connection form:

Figure 3.1 Setup New Connection Form

Important note:

The Setup New Connection form features a Configure Server Management button (bottom left) that is required for the MySQL connection to perform tasks that requires shell access to the host. For example, starting/stopping the MySQL instance or editing the configuration file Fill out the connection details and optionally click Configure Server Management to execute the Server Management wizard. Click OK to save the connection.

Important

All connections opened by MySQL Workbench automatically set the client character set to utf8. Manually changing the client character set, such as using SET NAMES ..., may cause MySQL Workbench to not correctly display the characters.

New connections are added to the Home page as a tile, and multiple connections may be opened simultaneously in MySQL Workbench.

Part 2 Configure a New MySQL Connection

Click on ‘Local Instance MySQL’ and enter password;

Local Instance MySQL screen appears;

Click MySQL Workbench Home, click database to be connected;

EER Diagram screen appears;

Choose Database on Toolbar and ‘Forward Engineering’ on scroll menu;

Forward Engineer to Database screen appears

Set parameters for Connecting to a DBMS:

Stored Connection:   Select from saved connection settings; Click ‘Next’;

Set Options for Database to be Created appears

Select DROP objects before each CREATE object;

Leave selected Include model attached script; Click ‘Next’;

Select Objects to Forward Engineer screen appears, enter password again;

Select Export MySQL Table Objects and click ‘Next’;

Review the SQL script to be Executed screen appears for your review and saving to file or copy to Clipboard; Click ‘Next’;

Forward Engineering Progress screen appears, enter password again;

Forward Engineering Progress shows the executed tasks.

Click ‘Close’.

Please add Management, INSTANCE and PERFORMANCE screenshots for the created database to lab Report.

STEP 4: Insert data to tables using MySQL Workbench

Copy INSERT statements for the given tables into the notepad;

Launch to MySQL Workbench Home page;

Choose created database instance; enter password;

New screen appears with the Connection name;

Choose in Navigator your schema’s name;

Copy script from Notepad to screen ‘Query 1’;

Highlight executable rows, choose ‘Query’ on the Toolbar and Execute (All or Selection);

Output will display the results of the execution.

Please select counters and rows in database tables and add screenshots to lab Report.

testimonials icon
11. Steps of Data Collection Process Used in The Study.First, the study used a nursing survey that was prepared at the university hospitalin Seoul, S...
testimonials icon
Discuss a practice that was common in nursing that is no longer valid or acceptable, discuss why the practice is no longer used and include...
testimonials icon
 For this assignment, write a paper that assesses the relationship  between substance use disorders and psychosis. Drawin...
testimonials icon
Create a 7-12-slide PowerPoint Presentation about the way drugs   affect the brain. Include the following in your presentation: Titl...
testimonials icon
Need a Case Study Paper in APA format with 4 citations and 4 references in 500-700 words with no plagiarism with Abstract, Below questions answer...
testimonials icon
Pick a character from the book, Anne Frank: The Diary of a Young Girl, and some aspect of the human spirit, which is demonstrated by that character...
testimonials icon
Please explain answer and label accordingly...
testimonials icon
Homework 61. Suppose you have m dierent parameters 1 , ..., m , and you construct m (1 )confidence intervals, denoted by [j,left , j,right ] for j =...
testimonials icon
Cyber warfare and its implications for the united states...
testimonials icon
WhatsApp Forums for Academic & Article WritersBelow is a list of WhatsApp Groups for Academic and Article Writers, CHOOSE YO...
testimonials icon
Assignment 1: LASA2: Historical ContextEvery generation of psychologists seems to engage in practices that the next generation looks back up...

Other samples, services and questions:

Calculate Price

When you use PaperHelp, you save one valuable — TIME

You can spend it for more important things than paper writing.

Approx. price
$65
Order a paper. Study better. Sleep tight. Calculate Price!
Created with Sketch.
Calculate Price
Approx. price
$65