Question
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
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 tables structure with the new MySQLdatabase.
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 SetupNew Connection Form
Important note:
TheSetup New Connectionform 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 Managementto 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 MySQLand enter password;
Local Instance MySQLscreen 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 schemas 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.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started