data base

PART B - Basic SQL to understand Constraints Ques 1. (a) Write the SQL statements (SQL Workshop SQL Commands) needed to create the following relations using all required and mentioned constraints. Check the database instance given in part (b) to identify the appropriate data types. Table Name Attributes Constraints Preference Preft is a primary key. Vendor Vendor ID is a primary key Customer 1. PrefID 2. PreName 1. Vendor 2. VName 1. Customer 10 2. Customer_Name 3. Customer Age 4. Customer City 5. Customer Preference Customer ID is Primary key No two customers can have same age Set default value of Customer Preference as "PPPP Customer Preference is a foreign key Sales Order 1. Order ID 2 Order Date 3. Customer_1D Order ID is Primary key For Order Date, use Date datatype Customer ID is a foreign key Use ON DELETE CASCADE constraint Product ID is Primary key For Standard Price, use REAL datatype. Vendor ID is a foreign key. Product 1. Product ID 2. Product Name 3. Standard Price 4. Vendor ID (b) Write SQL commands to insert the below tuples in the relations Preference Product Pref ID Pref Name Prod 10 P1 Cash on Delivery Product Name Standard Lice Vendor 1000 Office Des 105 100 P2 Online Payment 1001 Managers 209.20 200 P3 Cash On Delivery Desk PPPP Detted Ones 2000 Once Chair 20 NURE 2001 Managers 229 100 Desk Customer Customer 10 Customer Name Customer Age Customer City Customer reference 101 Sinon L New York PI P2 102 Goldberg Smith 23 Sancancisco 103 34 PE Richard Andrew San Diego 1 104 Catherine Wong 2 105 Vendor Vendor VendorID 100 200 VName Titan ABC Manufacturers Sales Order Order ID 200 201 202 203 Order Date Customer ID 2018-01-11 101 2017-10-21 102 2016-05-04 103 2018-02-11 102 What to submit Create a word document having following information: 1. SQL commands used to create above tables [25 Marks] 2. SQL commands used to insert the above tuples in the tables. [15 Marks] 3. Snapshot of the description of each table. To get description, go to SQL workshop SQL Command Type desc table_name [ Marks] 4. Answer the following [10 Marks): (a) What would happen when you delete a tuple from customer table with id 101 ? WH It get deleted? If not, then why? (b) What would happen if you insert the below tuple in customer table . Explain. (c) What would happen when you delete a tuple from preference table with id = *P37 Will It affect any other table? If yes, which one and how? (d) What would happen when you delete a tuple from customer table with id 1027 Wilit affect any other table? If yes, which one and how? (e) What would happen when you delete a tuple from vendor table with id-1007 Ww it affect any other table? If yes, which one and how? LG