INF3707 ASSIGNMENT 03 FULL ANSWERS 2022

INF3707
DATABASE DESIGN & IMPLEMENTATION
ASSIGNMENT 03 ANSWERS

, QUESTION 1

1.1
--creatig table toys
CREATE TABLE TOYS (
toy_ID number(10) PRIMARY KEY,
Name varchar2(30),
order_date DATE,
cost number(5,2),
retail int,
discount number(4,2),
category varchar(12)
);
--creating table TOYCREATOR
CREATE TABLE TOYCREATOR (
toy_ID number(10) FOREIGN KEY references toys(toy_ID),
creator_ID number(4) FOREIGN KEY REFERENCES CREATORS(CREATOR_ID)
);
--create table CREATOR
CREATE TABLE CREATOR (
creator_ID number(4) PRIMARY KEY,
Lname varchar2(10),
Fname varchar2(10)
);


1.2
INSERT INTO TOYS
(toy_ID,Name,order_date,cost,retail,discount,category)
VALUES ('1001' ,'F1 Car','14-JULY-2020'
,'200','250','0' ,'Cars');

INSERT INTO CREATOR (creator_ID,Lname ,Fname)
VALUES ('20100714001' ,'Jan' ,'Temba');

INSERT INTO TOYCREATOR
(toy_ID,creator_ID)
VALUES ('1001','20100714001');



1.3
SELECT
CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITION,R_CONS
TRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'TOYCREATOR';

1.4
ALTER TABLE CREATOR
MODIFY (LNAME VARCHAR(30));

1.5
ALTER TABLE CREATOR
ADD (House_Number varchar(40),
Street varchar(40),
City varchar(40),