1 | P a g e NYU – TANDON SCHOOL OF ENGINEERING CS-GY 6083 – B, FALL 2019 Principles of Database Systems Assignment: 2 [100 points] Please submit your assignment on NYU class with PDF document attachment. Please mention Student ID, Name, Course, Section Number, and date of submission on first page of your submission. Insert picture of result screenshots in same PDF document. NO LATE SUBMISSION or RESUBMISSION WILL BE ALLOWED. DO NOT WAIT UNTIL LAST DAY OF SUBMISSION DEADLINE, CHECK DUE DATE CAREFULLY, AND CHECK YOUR SUBMISSION TH0ROUGHLY BEFORE SUBMITTING. Problem 1: 35 Points Relational Model Figure 1. 2 | P a g e For relational schema model in figure 1, do following a) Generate DDL for physical schema, make sure that comments are added for each column in RDBMS b) Create all tables in RDBMS c) Write DDL command and add a constraint for allowable CHEMIST_ID value from 1001 to 9999 d) Write DDL command and add constraints to allow FNAME and LNAME of CHEMIST in upper case e) Write DDL command and add constraint to have PROJECT END_DATE higher than START_DATE f) Using data dictionary queries, provide details of all tables and for each table provide column details, constraint details, comments on each column. Insert result screenshots to your assignment submission document. Problem 2: 35 points Relational Model Figure 2. 3 | P a g e For relational schema model in figure 2, do following, a) Generate DDL for physical schema, make sure that comments are added for each column in RDBMS b) Create all tables in RDBMS c) Write DDL command and add default value of ORDER_DATE in ORDERS table as system current date. d) Write DDL command and a new column INVOICE_ID in INVOICE table with numeric size of 5 digits e) Write DDL command and drop existing primary key of INOICE table and add new primary key for INOCIE_ID column f) Using data dictionary queries, provide details of all tables and for each table provide column details, constraint details, comments on each column. Insert result screenshots to your assignment submission document. Problem 3: 30 points This problem MUST be solved AFTER implementing Problem 1 and Problem 2 as above. a) After implementing problem solution for Problem 1 above, populate sample data for each tables. There must be minimum 7 and maximum 12 records for each table. Run select statements to retrieve all records of each table and insert screen shorts to assignment submission. b) After implementing problem solution for Problem 2 above, populate sample data for each tables. There must be minimum 7 and maximum 12 records for each table. Run select statements to retrieve all records of each table and insert screen shorts to assignment submission.
