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 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 on 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]

[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]

Question 10

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;

What is the number returned in the results?

Note: for this number to be correct, you must have created the database in PostgreSQL exactly as instructed. No partial credits will be awarded.