CIS3530 Lab4-SQLDDL Solution

Question 1:
a. Create a table called SECOND (sno, city, status). Every supplier lives in a city and every city has a status. Populate this table using table S (you should already have table S in your account from an earlier lab). SECOND should have the following rows in it.
sno | status | city -----+-------+--------+-------- S1 | 20 | LONDON
S2 | 10 | PARIS
S3 | 30 | PARIS
S4 | 20 | LONDON
S5 | 30 | ATHENS
S6 | 12 | GUELPH

b. Create a table called SS using attributes S# and STATUS from table SECOND. Create another table called CS using attributes CITY and STATUS from table SECOND.
Is the decomposition of SECOND into SS and CS non-loss? Write a SQL command that you can use to verify this.
c. Create another table called SC with attributes S# and CITY of SECOND. Using an SQL command, prove that decomposition of SECOND into smaller tables SC (SNO, CITY) and CS (CITY, STATUS) is non-loss.
Submit the SQL commands used in 1b and 1c in a text file called lab4q1.txt.
Question 2: Create a schema called SPJ_Schema. In that schema, create the following relations using. You must create all primary and foreign key constraints.
a. Tables S and P: Create these tables in your current schema SPJ using existing tables S and P that you must have created in an earlier lab. (For example, create table s as select * from public.s; )
b. Add primary keys to S (sno) and P (pno) using ALTER TABLE command. c. Table J has the following structure:
JNO VARCHAR PRIMARY KEY
JNAME VARCHAR
CITY VARCHAR

Create table J using CREATE TABLE. Populate table J using the copy command with tableJ.csv posted in lab4 on moodle. You must save the csv file in your current working folder. The command is:
copy J from tableJ.csv delimiter ‘,’ csv header

d. Use CREATE TABLE command to create table SPJ which has the following structure:

SNO VARCHAR
PNO VARCHAR
JNO VARCHAR
QTY INTEGER
PRIMARY KEY (SNO, PNO, JNO)
FOREIGN KEYS: SNO REFERENCES SNO of table S
FOREIGN KEY (PNO) REFERENCES PNO of table P
FOREIGN KEY (JNO) REFERENCES JNO of table J

Populate SPJ using copy command with tableSPJ.csv.
Question 3: Creating SQL commands in bulk (using SELECT, Postgres’s system catalog tables and o)
In this exercise, you will create a script that has the command to describe the structure of every table in your current schema (e.g. d+ nameOfTable).
SELECT 'd+ ' || tablename
FROM pg_tables;
Using o, save output of this query in a file called lab4q3.sql.
This sql file will have a series of d+ commands, one for each table. This file can then be run using i to display the structure of each table in your schema.
Submit lab4q3.sql file.