INF3707 ASSIGNMENT 3 2023 Database Design and Implementation

QUESTION 1

1.1 The sequence MY_FIRST_SEQ can be created by using the following code:



CREATE SEQUENCE MY_FIRST_SEQ

START WITH 5

INCREMENT BY -3

MINVALUE 0

NOCYCLE;



1.2 The SELECT statement to display NEXTVAL for MY_FIRST_SEQ three times can be issued as
follows:



SELECT MY_FIRST_SEQ.NEXTVAL FROM dual;

SELECT MY_FIRST_SEQ.NEXTVAL FROM dual;

, SELECT MY_FIRST_SEQ.NEXTVAL FROM dual;



The error on the third SELECT statement is caused by reaching the maximum value defined for the
sequence. Since the sequence has a maximum value of 0, it cannot generate a value greater than 0.



1.3 The failure to create the table ord_items is caused by referencing a non-existent table "orders" in
the FOREIGN KEY constraint. The corrected CREATE TABLE code is as follows:



CREATE SEQUENCE ord_seq

INCREMENT BY 10

START WITH 120

MAXVALUE 9999

NOCYCLE;



CREATE TABLE ord_items

(ord_no NUMBER (4) DEFAULT ord_seq.NEXTVAL NOT NULL,

item_no NUMBER(3),

qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200),

expiry_date date CHECK (expiry_date > SYSDATE),

CONSTRAINT its_pky PRIMARY KEY (ord_no, item_no),

CONSTRAINT ord_fky FOREIGN KEY(ord_no) REFERENCES ord_items(ord_no)

);



QUESTION 2

.1 To create the user Peter with a password that expires and assign the role of salesperson, use the
following code:



CREATE USER Peter IDENTIFIED BY [password] PASSWORD EXPIRE;



GRANT salesperson TO Peter;