Consider an application which is being developed for a 'Used Car seller'. For storing various details...

  

Transcribed Image Text:

Consider an application which is being developed for a 'Used Car seller'. For storing various details related to the cars and owners, create the tables according to the schemas given below; CAR(Car Reg_No, Brand, Model, Variant, Model_Year, Color, Year_of_Purchase, Kilometers) OWNER(Owner ID, Owner_Name, Owner_Phone, License_Number) OWNER_CAR(Owner ID, Car Reg_No, Price) 1- Consider the following as important components while creating the tables; a. The columns that are underlined are Primary keys b. All columns must contain some values. c. Choose the appropriate data types which would match the most for all the attributes. d. Use constraint names for all the constraints you create. e. For table CAR; i. Brand should be one of { 'Maruti', "Ford', 'Hyundai' } ii. Color should be one of { 'Black', 'White', 'Red', 'Blue' } iii. Kilometers should be less than 50000. f. For table OWNER; i. Owner_ID should start with 'OID' ii. License_Number should be unique value. g. For table OWNER_CAR; i. Price should be greater than Rs.150000. II - After table creation, write the queries to alter the tables according to the requirements given below; a. Add the Foreign Key in OWNER_CAR table to refer Owner_ID from OWNER table. b. Add the Foreign Key in OWNER_CAR table to refer Car_Reg_No from CAR table. c. Add an attribute Owner_Address in OWNER table. d. Add an attribute Test_Status of the car which accepts character based values. e. Remove the column Year_Of_Purchase from CAR. f. Remove the column Owner Address from OWNER table. g. Remove the Unique constraint of License_Number attribute. h. Remove the color constraint so that any colored cars can be inserted. i. Add a column Years_Used to OWNER_CAR table which stores the number of years the car used by the owner. j. Remove the table OWNER_CAR from the database completely. k. Change the type of License_Number attribute from character based type to number type. Consider an application which is being developed for a 'Used Car seller'. For storing various details related to the cars and owners, create the tables according to the schemas given below; CAR(Car Reg_No, Brand, Model, Variant, Model_Year, Color, Year_of_Purchase, Kilometers) OWNER(Owner ID, Owner_Name, Owner_Phone, License_Number) OWNER_CAR(Owner ID, Car Reg_No, Price) 1- Consider the following as important components while creating the tables; a. The columns that are underlined are Primary keys b. All columns must contain some values. c. Choose the appropriate data types which would match the most for all the attributes. d. Use constraint names for all the constraints you create. e. For table CAR; i. Brand should be one of { 'Maruti', "Ford', 'Hyundai' } ii. Color should be one of { 'Black', 'White', 'Red', 'Blue' } iii. Kilometers should be less than 50000. f. For table OWNER; i. Owner_ID should start with 'OID' ii. License_Number should be unique value. g. For table OWNER_CAR; i. Price should be greater than Rs.150000. II - After table creation, write the queries to alter the tables according to the requirements given below; a. Add the Foreign Key in OWNER_CAR table to refer Owner_ID from OWNER table. b. Add the Foreign Key in OWNER_CAR table to refer Car_Reg_No from CAR table. c. Add an attribute Owner_Address in OWNER table. d. Add an attribute Test_Status of the car which accepts character based values. e. Remove the column Year_Of_Purchase from CAR. f. Remove the column Owner Address from OWNER table. g. Remove the Unique constraint of License_Number attribute. h. Remove the color constraint so that any colored cars can be inserted. i. Add a column Years_Used to OWNER_CAR table which stores the number of years the car used by the owner. j. Remove the table OWNER_CAR from the database completely. k. Change the type of License_Number attribute from character based type to number type.

Answer rating: 100% (QA)

Answers The queries are written in SQL as used in Oracle RDBMS I Table Creation I have listed the requirements for creating tables as specified in the question in the table given below Type column in ... View the full answer