database management
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,’222 Main Road, Atlanta, GA’,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]
[Jeff,H,Chase,333333301,07-JAN-1970,’145 Bradbury, Sacramento, CA’,M,44000,333333300,6]
[Bonnie,S,Bays,444444401,19-JUN-1956,’111 Hollow, Milwaukee, WI’,F,70000,444444400,7]
[Alec,C,Best,444444402,18-JUN-1966,’233 Solid, Milwaukee, WI’,M,60000,444444400,7]
[Sam,S,Snedden,444444403,31-JUL-1977,’987 Windy St, Milwaukee, WI’,M,48000,444444400,7]
[Nandita,K,Ball,555555501,16-APR-1969,’222 Howard, Sacramento, CA’,M,62000,555555500,6]
[Bob,B,Bender,666666600,17-APR-1968,’8794 Garfield, Chicago, IL’,M,96000,null,8]
[Jill,J,Jarvis,666666601,14-JAN-1966,’6234 Lincoln, Chicago, IL’,F,36000,666666600,8]
[Kate,W,King,666666602,16-APR-1966,’1976 Boone Trace, Chicago, IL’,F,44000,666666600,8]
[Lyle,G,Leslie,666666603,09-JUN-1963,’417 Hancock Ave, Chicago, IL’,M,41000,666666601,8]
[Billie,J,King,666666604,01-JAN-1960,’556 Washington, Chicago, IL’,F,38000,666666603,8]
[Jon,A,Kramer,666666605,22-AUG-1964,’1988 Windy Creek, Seattle, WA’,M,41500,666666603,8]
[Ray,H,King,666666606,16-AUG-1949,’213 Delk Road, Seattle, WA’,M,44500,666666604,8]
[Gerald,D,Small,666666607,15-MAY-1962,’122 Ball Street, Dallas, TX’,M,29000,666666602,8]
[Arnold,A,Head,666666608,19-MAY-1967,’233 Spring St, Dallas, TX’,M,33000,666666602,8]
[Helga,C,Pataki,666666609,11-MAR-1969,’101 Holyoke St, Dallas, TX’,F,32000,666666602,8]
[Naveen,B,Drew,666666610,23-MAY-1970,’198 Elm St, Philadelphia, PA’,M,34000,666666607,8]
[Carl,E,Reedy,666666611,21-JUN-1977,’213 Ball St, Philadelphia, PA’,M,32000,666666610,8]
[Sammy,G,Hall,666666612,11-JAN-1970,’433 Main Street, Miami, FL’,M,37000,666666611,8]
[Red,A,Bacher,666666613,21-MAY-1980,’196 Elm Street, Miami, FL’,M,33500,666666612,8]
Step 5:
PROJECT [pname, pnumber, plocation, dnum]
Add the following records in the PROJECT table:
[ProductX,1,Bellaire,5]
[ProductY,2,Sugarland,5]
[ProductZ,3,Houston,5]
[Computerization,10,Stafford,4]
[Reorganization,20,Houston,1]
[Newbenefits,30,Stafford,4]
[OperatingSystems,61,Jacksonville,6]
[DatabaseSystems,62,Birmingham,6]
[Middleware,63,Jackson,6]
[InkjetPrinters,91,Phoenix,7]
[LaserPrinters,92,LasVegas,7]
Step 6:
DEPT_LOCATIONS [dnumber, dlocation]
Add the following records in the DEPT_LOCATIONS table:
[1,Houston]
[4,Stafford]
[5,Bellaire]
[5,Sugarland]
[5,Houston]
[6,Atlanta]
[6,Sacramento]
[7,Milwaukee]
[8,Chicago]
[8,Dallas]
[8,Philadephia]
[8,Seattle]
[8,Miami]
Step 7:
DEPENDENT [essn, dependent_name, sex, bdate, derelationship]
Add the following records in the DEPENDENT table:
[333445555,Alice,F,05-APR-1976,Daughter]
[333445555,Theodore,M,25-OCT-1973,Son]
[333445555,Joy,F,03-MAY-1948,Spouse]
[987654321,Abner,M,29-FEB-1932,Spouse]
[123456789,Michael,M,01-JAN-1978,Son]
[123456789,Alice,F, 31-DEC-1978,Daughter]
[123456789,Elizabeth,F,05-MAY-1957,Spouse]
[444444400,Johnny,M,04-APR-1997,Son]
[444444400,Tommy,M,07-JUN-1999,Son]
[444444401,Chris,M,19-APR-1969,Spouse]
[444444402,Sam,M,14-FEB-1964,Spouse]
Step 8:
WORKS_ON [essn, pno, hours]
Add the following records in the WORKS_ON table:
[123456789,1, 20.0]
[123456789,2, 7.5]
[666884444,3, 40.0]
[453453453,1, 20.0]
[453453453,2, 20.0]
[333445555,2, 10.0]
[333445555,3, 10.0]
[333445555,10,10.0]
[333445555,20,10.0]
[999887777,30,30.0]
[999887777,10,10.0]
[987987987,10,35.0]
[987987987,30, 5.0]
[987654321,30,20.0]
[987654321,20,15.0]
[888665555,20,null]
[111111100,61,40.0]
[111111101,61,40.0]
[111111102,61,40.0]
[111111103,61,40.0] In the PostgreSQL Company database that you just created, run the following SQL statement:
SELECT COUNT(*) FROM department, dependent, dept_locations, employee, project, works_on WHERE employee.salary < 90000 AND department.dnumber > 2 AND works_on.pno > 2;
What is the number returned in the results?
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
