Instructions:

Using the guidance and instructions given in the module download and install the appropriate version of PostgreSQL for your system.

Familiarize yourself with the pgAdmin web interface.

Create a database called Company.

Replicate the final database schema for the Company database example as shown in class using PostgreSQL. Use the schema and data given here to guide you and complete each step given $($remember$,$ you will need to add the foreign key constraint to Employees after creating the Department table, as seen in class$).$

Make sure you include all keys as shown in the table definitions in lectures, but do not include extra constraints $($e$.$g$.,$ domains, etc.$)$ created just as didactical examples in class.

Please note that the schema listed here has some different field names than the ones used in the lectures. Please use the names provided here to define your tables as we may use this updated schema in other assignments $($make sure you also save your completed work for later$).$

Once the database schema is created, transform the statements given below to DDL SQL commands and execute them in PostgreSQL fixing any issues you may encounter.

Follow the order given for the commands as it is important due to database constraints.

Do not forget to save the database definition since you may be using it again later on$.$

Hint: Using certain applications, such as Microsoft Excel, could greatly help you to speed up this process. Check the additional resources section for a video explaining how this is done.

Finally, proceed to answer the assignment's related question.

Step $1$:

EMPLOYEE $[$fname$,$ minit, lname$,$ ssn$,$ bdate, address, sex, salary, superssn, dno$]$

Add the following records to the EMPLOYEE table:

$[$James$,$E$,$Borg,$888665555,10-$NOV$-1927,450$ Stone, Houston, TX$,$M$,55000,$null,null$]$

$[$Franklin$,$T$,$Wong,$333445555,08-$DEC$-1945,638$ Voss, Houston, TX$,$M$,40000,888665555,$null$]$

$[$Jennifer$,$S$,$Wallace,$987654321,20-$JUN$-1931,291$ Berry, Bellaire, TX$,$F$,43000,888665555,$null$]$

$[$Jared$,$D$,$James,$111111100,10-$OCT$-1966,123$ Peachtree, Atlanta, GA$,$M$,85000,$null,null$]$

$[$Alex$,$D$,$Freed,$444444400,09-$OCT$-1950,4333$ Pillsbury, Milwaukee, WI$,$M$,89000,$null,null$]$

$[$John$,$C$,$James,$555555500,30-$JUN$-1975,7676$ Bloomington, Sacramento, CA$,$M$,81000,$null,null$]$

Step $2$:

DEPARTMENT $[$dname$,$ dnumber, mgrssn$,$ mgrstartdate$]$

Add the following records to the DEPARTMENT table:

$[$Research$,5,333445555,22-$MAY$-1978]$

$[$Administration$,4,987654321,01-$JAN$-1985]$

$[$Headquarters$,1,888665555,19-$JUN$-1971]$

$[$Software$,6,111111100,15-$MAY$-1999]$

$[$Hardware$,7,444444400,15-$MAY$-1998]$

$[$Sales$,8,555555500,01-$JAN$-1997]$

Step $3$:

Update the following records in the EMPLOYEE table:

Update dnumber $=5$ to the employee with ssn $=333445555$

Update dnumber $=4$ to the employee with ssn $=987654321$

Update dnumber $=1$ to the employee with ssn $=888665555$

Update dnumber $=6$ to the employee with ssn $=111111100$

Update dnumber $=7$ to the employee with ssn $=444444400$

Update dnumber $=6$ to the employee with ssn $=555555500$

Step $4$:

Add the following employees in the EMPLOYEE table:

$[$John$,$B$,$Smith,$123456789,09-$Jan$-1955,731$ Fondren, Houston, TX$,$M$,30000,333445555,5]$

$[$Alicia$,$J$,$Zelaya,$999887777,19-$JUL$-1958,3321$ Castle, Spring, TX$,$F$,25000,987654321,4]$

$[$Ramesh$,$K$,$Narayan,$666884444,15-$SEP$-1952,971$ Fire Oak, Humble, TX$,$M$,38000,333445555,5]$

$[$Joyce$,$A$,$English,$453453453,31-$JUL$-1962,5631$ Rice Oak, Houston, TX$,$F$,25000,333445555,5]$

$[$Ahmad$,$V$,$Jabbar,$987987987,29-$MAR$-1959,980$ Dallas, Houston, TX$,$M$,25000,987654321,4]$

$[$Jon$,$C$,$Jones,$111111101,14-$NOV$-1967,111$ Allgood, Atlanta, GA$,$M$,45000,111111100,6]$

$[$Don$,$T$,$Smith,$211111101,11-$NOV$-1987,\text{'}222$ Main Road, Atlanta, GA$\text{'},$M$,65000,111111100,6]$

$[$Justin$,$null,Mark,$111111102,12-$JAN$-1966,2342$ May, Atlanta, GA$,$M$,40000,111111100,6]$

$[$Brad$,$C$,$Knight,$111111103,13-$FEB$-1968,176$ Main St$.,$ Atlanta, GA$,$M$,44000,111111100,6]$

$[$Evan$,$E$,$Wallis,$222222200,16-$JAN$-1958,134$ Pelham, Milwaukee, WI$,$M$,92000,$null,$7]$

$[$Josh$,$U$,$Zell,$222222201,22-$MAY$-1954,266$ McGrady, Milwaukee, WI$,$M$,56000,222222200,7]$

$[$Andy$,$C$,$Vile,$222222202,21-$JUN$-1944,1967$ Jordan, Milwaukee, WI$,$M$,53000,222222200,7]$

$[$Tom$,$G$,$Brand,$222222203,16-$DEC$-1966,112$ Third St$,$ Milwaukee, WI$,$M$,62500,222222200,7]$

$[$Jenny$,$F$,$Vos,$222222204,11-$NOV$-1967,263$ Mayberry, Milwaukee, WI$,$F$,61000,222222201,7]$

$[$Chris$,$A$,$Carter,$222222205,21-$MAR$-1960,565$ Jordan, Milwaukee, WI$,$F$,43000,222222201,7]$

$[$Kim$,$C$,$Grace,$333333300,23-$OCT$-1970,6677$ Mills Ave, Sacramento, CA$,$F$,79000,$null,$6]$

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Databases Questions!

Q:

Part 2: DDL Instructions: Using the guidance and instructions given in the module download and install the appropriate version of PostgreSQL for your system. Familiarize yourself with the pgAdmin web...

Q:

Instructions: Using the guidance and instructions given in the module download and install the appropriate version of PostgreSQL for your system. Familiarize yourself with the pgAdmin web interface....

Q:

Part 3:Brainstorm and Create Ideas BSBMGT407 Apply digital solutions to work processes Additional Resources You will be provided with the following resources before you begin each Assessment Task....

Q:

I have to create a program in C and I can't figure it out. The program has to read a source file. Please help. /******************************************************************** PROJECT: Glossary...

Q:

Help on Financial Statement analysis homework, need a perfect solution to improve grade UNITED STATES SECURITIES AND EXCHANGE COMMISSION Washington, D.C. 20549 Form 10- K (Mark One) 3 ANNUAL REPORT...

Q:

Any idea on 24 and 25,,, ive already installed ab i don't no how to run it and i need help with 25 24.Install and run ab to test the apache benchmark for your own website to see how fast your site...

Q:

Chapter 2 User-Centered Systems Design: A Brief History Abstract The intention of this book is to help you think about design from a user-centered perspective. Our aim is to help you understand what...

Q:

Part 1: Planning D Introduction Planning for a project is an important skill, so the TT284 EMA includes an element to encourage you to think about the work you will have to complete, how long the...

Q:

OL 325: Final Project Guidelines and Rubric Overview Acting as a recently hired compensation consultant, you will assist the burgeoning online music firm e-sonic to develop an internally consistent...

Q:

Module 9 Assignment: TOC Answer all the questions and submit your answer report to Module 9 Assignment in Dropbox by the deadline . The report should be typed, single spaced, in one MS Word file. You...

Q:

Show the location of the two planar nodes in this 3d atomic orbital?

Q:

The accompanying data are the number of wins and the earned run averages (mean number of earned runs allowed per nine innings pitched) for eight baseball pitchers in a recent season. Find the...

Q:

You have $1,000 invested in an account that you expect to earn an effective annual rate of 5%. How much is your $1,000 worth in five years? Group of answer choices $1,221 $1,276 $1,283 $1,050 $1,250

Q:

Compared with half a century ago, adoption has become _ _ _ _ _ _ _ _ _ common, but it is more open and acceptabl e , so we probably discuss it _ _ _ _ _ _ _ . fill in the blanks more or much less or...

Q:

Explain why the following statements are false. a. The aggregate-demand curve slopes downward because it is the horizontal sum of the demand curves for individual goods. b. The long-run...

Q:

Suppose that the economy is currently in a recession. If policymakers take no action, how will the economy change over time? Explain in words and using an aggregate-demand/ aggregate-supply diagram.

Q:

Suppose the U.S. economy begins in long-run equilibrium. Concerns about global climate change cause the government to significantly restrict the production of electricity from fossil fuels. Because...

Previous Question Next Question