Work with dictionary and create relational database

Assignment Help Basic Computer Science
Reference no: EM13920769

Lab 2: Work with Dictionary and Create Relational Database

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
a) Launch MySQL Workbench;
b) Click File and choose ‘New Model';
c) Add Diagram:

Name: new schema name;
d) 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
a) Launch to MySQL Workbench Home page;
b) 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.
c) 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
a) Click on ‘Local Instance MySQL' and enter password;
b) Local Instance MySQL screen appears;
c) Click MySQL Workbench Home, click database to be connected;
d) EER Diagram screen appears;
e) Choose Database on Toolbar and ‘Forward Engineering' on scroll menu;
f) Forward Engineer to Database screen appears
Set parameters for Connecting to a DBMS:
Stored Connection: Select from saved connection settings; Click ‘Next';
g) Set Options for Database to be Created appears
Select DROP objects before each CREATE object;
Leave selected Include model attached script; Click ‘Next';
h) Select Objects to Forward Engineer screen appears, enter password again;
Select Export MySQL Table Objects and click ‘Next';
i) Review the SQL script to be Executed screen appears for your review and saving to file or copy to Clipboard; Click ‘Next';
j) Forward Engineering Progress screen appears, enter password again;
k) Forward Engineering Progress shows the executed tasks.
l) 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
a) Copy INSERT statements for the given tables into the notepad;
b) Launch to MySQL Workbench Home page;
c) Choose created database instance; enter password;
d) New screen appears with the Connection name;
e) Choose in Navigator your schema's name;
f) Copy script from Notepad to screen ‘Query 1';
g) Highlight executable rows, choose ‘Query' on the Toolbar and Execute (All or Selection);
h) Output will display the results of the execution.
Please select counters and rows in database tables and add screenshots to lab Report.

Reference no: EM13920769

Questions Cloud

What is the best type of hypothesis test : At a 1% level of significance, test to see if the proportion of voters has changed. QUESTION 1: What is the best type of hypothesis test to apply for this problem?
Synopsis of the jones and shephard case study : 1. Provide a synopsis of the Jones and Shephard case study 2. Highlight three enterprise management causes/considerations and three project management causes/considerations for the situation. Include what seemed to be missing
Discuss why the ownership pattern makes economic sense : Discuss why this ownership pattern makes economic sense. Do you think the CEO's conclusions are correct? Why or why not?
Building a multi-project battalion by leading upward : Read the case study titled "A Peaceful Evacuation: Building a Multi-Project Battalion by Leading Upward." before starting this assignment.
Work with dictionary and create relational database : 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..
How does the style and management of nike : How does the style and management of Nike compare and contract to an organization that has adopted Total Quality Management.
Average or actual results important : When are differences (between one time period and another, between different production lines, etc.) between average or actual results important?
State a prediction that could be tested via experiment : State a prediction from this hypothesis that could be tested via additional observation - State a prediction that could be tested via experiment and State a prediction that could be tested via the comparative method.
Frustrating aspects of contract management : One of the most frustrating aspects of contract management to customersclients is the length of time that it commonly takes to close out a project (from an administrative standpoint) after most or all of the contract scope of work has been complet..

Reviews

Write a Review

 

Basic Computer Science Questions & Answers

  For this case write down a 4-5 page explanation of the

for this case write a 4-5 page description of the steps businesses need to follow to create an online presence the

  Explaining organization management responsibilities

Describe in scholarly detail common organization management responsibilities.

  What change management initiatives should km group have

What change management initiatives should the KM group have initiated at Infosys before attempting to develop and implement knowledge management at the company?

  Compensation and benefit strategy

Present to the management a revised compensation and benefit strategy. Your proposal should include a discussion of:

  Discuss the points and tabulate the strengths and weakness

Please discuss the points and tabulate the strengths and weaknesses

  What in the history of programming languages

What, in the history of programming languages, motivated the development of Java? Answer it more than 250 words.

  Choose one area of rapid technological change in it

4. Choose one area of rapid technological change in IT or Computer Science, research and report on recent developments and the outlook for the future in the area that you have chosen. You will need to provide both in text citations and bibliography e..

  Research paper on programmer-defined functions

Create codes for the following problem/s. Compile, test run, and edit them if necessary. Include your original .cpp and related file(s) (if any) in a single folder.

  How the game is played from start to finish

Conduct outside research on the game Yahtzee. You really want to better understand how the game is played and scored. Then, create a 1-2 page paper that explains the following in essay form:

  Write and test a function that prints an error message

Write and test a function that prints an error message in the following format.

  Abstract and introduction

Need paper done with 8-10 pages not including cover page or reference. Please include abstract and introduction, that's already two pages out of your 8-10 pages. Please be original but if you do use other work please paraphrase.

  Determine the number of students having scores

Write a program that reads a file ("scores.txt") consisting of students' test scores in the range of 0-200. These scores should be loaded into a one-dimensional array. Then the program should determine the number of students having scores in each ..

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd