Worksheets

Anonymous
timer Asked: Nov 7th, 2017
account_balance_wallet $15

Question description

You will only turn in one notepad or word document for all three steps.

You need to complete the worksheets in order! So, Step 1 before completing Step 2 and Step 2 must be completed before completing Step 3!

You will notice that the physical layout sheets have missing data - you will need to fill out this information before beginning to create your tables.

CIS1513 Database Design and SQL Oracle SQL Developer- Inserting records – Employees Step 3 Step 1: Logon to SQL Developer and then CLICK on the + by Tables Step 2: You should now see your. TABLES on left side. CLICK on the Columns tab to see table structure To add records to your tables using SQL Developer: Step 3: CLICK on DATA tab and then click the GREEN + to add a record. Add the following records: AGENCYID AGENCYNAME CONTACT EMAIL 200 201 broll@it.com 5058228000 nicks@work.com 5053457777 IT_FINDERS IT_WORKERS Ben Rollag Stevie Nicks PHONE Step 4: After you have added the records to the Agency table, CLICK on the COMMIT button to make the added records permanent. STEP 5: Add the following records to the DEPARTMENTS table: DEPARTMENT_ID DEPARTMENT_NAME BUDGETAMT 10 20 30 IT FINANCE SALES And the Jobs table: JOB_ID JOB_TITLE 9000 2000 7000 MIN_SALARY MAX_SALARY IT_CLERK IT Clerical Staff IT_DBA Database Admin. SUPER_PROG Super Programmer 2000 10000 9000 2000 20000 18000 And the EMPLOYEES table: Note: Employee data Is separated into 2 lines. EMPLOYEE_ID FIRST_NAME 100 101 102 Tweety Jack Samuel MI LAST_NAME O L Bird Lantern Adams Note: go to the next line for the next column. EMAIL tw@cnm.edu jl@cnm.edu sa@cnm.edu PHONE_NUMBER 5058721345 5058900909 5054505555 Employee Fields continued: SALARY HOURLY MANAGER_ID DEPARTMENT_ID AGENCYID 9000 16.00 12000 100 100 100 After JOB_ID 10 10 10 200 HIRE_DATE JOB_ID 24-Mar-08 SUPER_PROG 02-Apr-08 IT_CLERK 01-Mar-07 IT_DBA
CIS1513 Database Design and SQL Employees In Class step 2 Students use: SQL DEVELOPER to log onto your ORACLE account(startprograms) cnmcb_1513_s## Creating tables and constraints from physical layout charts-Agency and Employees Part 1: Create Agency Table (Don’t forgot to code: PRIMARY KEY CONSTRAINT and 4 NOT NULL constraints) Name of ENTITY: _AGENCIES #AGENCY ID *AGENCY NAME *CONTACT *EMAIL *PHONE Key Type PK,FK PK _EMPLOYEES N(3) EMPLOYEEID FIRSTNAME MI LASTNAME EMAIL V2(25) V2(20) V2(15) V2(10) Optionality * or o Column Name Column Data Type * AGENCY_ID AGENCY_NAME CONTACT EMAIL PHONE NUMBER(3) * * * * VarChar2(25) VarChar2(20) VarChar2(15) Varchar2(10) PHONE HIREDATE JOB_ID (FK) SALARY HOURLY MANAGERID DEPT_ID (FK) AGENCYID(FK) NOTES: PRIMARY KEY CONSTRAINT Example: CONSTRAINT EMP_EMP_ID_PK PRIMARY KEY (EMPLOYEEID) Constraint Names: Use Table name then the column name then the constraint prefix (PK, FK, NN, UK,CK) Part 2: CREATE the Employees table from Layout sheet below, don’t forget to Incorporate the Primary key, Not Null, Foreign Key constraints in the create table #AGENCY ID *AGENCY NAME *CONTACT *EMAIL *PHONE O O * PHONE HIRE_DATE JOB_ID SALARY HOURLY MANAGER_ID EMPLOYEE #EMPLOYEE ID *FIRST NAME oMI *LAST NAME *EMAIL *PHONE *HIRE DATE *JOB ID (FK) oSALARY oHOURLY *MANAGER ID (FK) Column Data Type*DEPT ID (FK) oAGENCY ID(FK) NUMBER(3) VARCHAR2(20) VARCHAR2(1) VARCHAR2(20) VARCHAR2(15) VARCHAR2(10) DATE VARCHAR2(10) NUMBER(7,2) NUMBER(5,2) NUMBER(3) * O DEPARTMENT_ID AGENCY_ID NUMBER(4) NUMBER(3) # JOB ID * JOB TITLE *MIN SALARY *MAX SALARY # DEPARTMENT ID * DEPT NAME o BUDGET AMT Name of ENTITY: _EMPLOYEES__________ Optionality Key Type Column Name PK,FK * or o PK * * O * UK * * * FK * FK-only code not null constraint not foreign key. FK FK EMPLOYEE_ID FIRST_NAME MI LAST_NAME EMAIL NOTES: PRIMARY KEY CONSTRAINT Example: CONSTRAINT EMP_EMP_ID_PK PRIMARY KEY (EMPLOYEEID) Constraint Names: Use Table name then the column name then constraint prefix (PK, FK, NN, UK,CK) NOTES: Example of FOREIGN key Constraint on JOB_ID: CONSTRAINT EMP_JOBID_FK FOREIGN KEY (JOB_ID) REFERENCES JOBS(JOB_ID) Name of constraint TYPE of constraint FK column FOREIGN key Constraint on DEPARTMENT_ID: CONSTRAINT ____________ __________ ( Name of constraint TYPE of constraint FK column parent table PK in parent ) REFERENCES ________ (______) parent tabl e PK
Employees In Class step 1 Creating tables from physical layouts Students log on to your Oracle account using SQL DEVELOPER (startprograms) cnmcb_1513_s## -- use your assigned number Step 1: Creating the JOBS table in ORACLE from Physical Layout chart Entity JOB # JOB ID * JOB TITLE *MIN SALARY *MAX_SALARY Transfer the ERD data to Physical layout chart Name of ENTITY: _JOBS__________ PK-primary key FK-foreign key UK-unique key(indexed) Key Type PK,FK PK Optionality * - means required o – means optional Column Name * or o * * * * Column Data Type (NUMBER, VARCHAR2, DATE) JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY VARCHAR2(10) VARCHAR2(30) NUMBER(6) NUMBER(6) Now we take the information in the Physical Layout(Table Instance Chart) and start creating the table using ther CREATE table command Copy the SQL code below and run it in ORACLE command line or SQL Developer After you SQL runs successfully, Do a describe command (ex: DESC JOBS)shows structure SQL command: CREATE TABLE JOBS (JOB_ID VARCHAR2(10), JOB_TITLE VARCHAR2(30) CONSTRAINT JOB_TITLE_NN NOT NULL, MIN_SALARY NUMBER(6) CONSTRAINT JOB_MINSAL_NN NOT NULL, MAX_SALARY NUMBER(6) CONSTRAINT JOB_MAXSAL_NN NOT NULL, CONSTRAINT JOB_ID_PK PRIMARY KEY (JOB_ID) ); NOT NULL constraints are how we handle making a field required PRIMARY KEY constraints are how we make a column be the PRIMARY KEY (UID) Step 2: Creating DEPARTMENTS table in ORACLE from Physical Layout chart Entity DEPARTMENT # DEPARTMENT ID * DEPARTMENT NAME O BUDGETAMT Transfer the ERD data to Physical layout chart Name of ENTITY: _DEPARTMENTS (use this chart to create your SQL to create the Departments table PK-primary key FK-foreign key UK-unique key(indexed) Key Type PK,FK PK Optionality * - means required o – means optional Column Name * or o * Column Data Type (NUMBER, VARCHAR2, DATE) * NUMBER(4) DEPARTMENT_ID DEPARTMENT_NAME VARCHAR2(30) o BUDGET_AMT NUMBER(5,0) USE the CREATE table command in Oracle to create the DEPARMENTS table using the above physical layout chart. HINT – a good programmer always reuses code/no need to reinvent the code from scratch. (Use the code you just used above for the JOBS table and MODIFY to the SPECS in the Departments table)

Tutor Answer

Carnegie Mellon University

Hello buddy, i have finished the assignment. Thank you for giving me that opportunity to work with you.

Running Head: Database Design and SQL

CIS1513 Database Design and SQL
{Student’s name}
{Professor’s name}
{Course title}
{Date}

Running Head: Database Design and SQL

Step 1: Creating JOBS and DEPARTMENTS tables using the physical layouts
provided.
Part 1: Running DDL Script for JOBS table and then displaying the table structure.

Part 2: Creating Departments table
CR...

flag Report DMCA
Review

Anonymous
Thanks, good work

Similar Questions
Hot Questions

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors