INF3707 – DATABASE DESIGN AND IMPLEMENTATION
ASSESSMENT THREE
DUE DATE: 02 AUGUST 2024
Rehoboth Tutors
, Question 1
To perform the following assignments, refer to the tables in the JustLee books database.
1.1 Create a sequence for populating the Customer# column of the CUSTOMERS table. When
setting the start and the increment values, beer in mind that data already exist in the table.
Hence, you need to know the last customer number stored in the database. The options should
be set not to cycle the values and not to cache any values. No minimum or maximum values
should be declared. (2 marks)
create sequence customers_customer#_seq
increment by 1
start with 10
nocache
nominvalue
nomaxvalue
nocycle;
1.2 Add a new customer row by using the sequence created in Question 1.1. the only data
currently available for the customer is as follows:
last name = Shoulders, first_name = Frank, and zip = 23567. (3 marks)
INSERT INTO CUSTOMERS (Customer#, last_name, first_name, zip)
VALUES (customer_seq.NEXTVAL, 'Shoulders', 'Frank', '23567');
Question 2
JustLee Books has recently hired a temporary salesman. The name of the new employee is
John. The login credential for John should expire at the end of his contract. As a salesperson,
John should be granted some object privileges on the JustLee Books database.
2.1 create the user John with a password that expire with the role of sales_person (3 marks).
CREATE USER john
IDENTIFIED BY sales1234
PASSWORD EXPIRE;
GRANT Sales_person TO john;
Rehoboth Tutors
ASSESSMENT THREE
DUE DATE: 02 AUGUST 2024
Rehoboth Tutors
, Question 1
To perform the following assignments, refer to the tables in the JustLee books database.
1.1 Create a sequence for populating the Customer# column of the CUSTOMERS table. When
setting the start and the increment values, beer in mind that data already exist in the table.
Hence, you need to know the last customer number stored in the database. The options should
be set not to cycle the values and not to cache any values. No minimum or maximum values
should be declared. (2 marks)
create sequence customers_customer#_seq
increment by 1
start with 10
nocache
nominvalue
nomaxvalue
nocycle;
1.2 Add a new customer row by using the sequence created in Question 1.1. the only data
currently available for the customer is as follows:
last name = Shoulders, first_name = Frank, and zip = 23567. (3 marks)
INSERT INTO CUSTOMERS (Customer#, last_name, first_name, zip)
VALUES (customer_seq.NEXTVAL, 'Shoulders', 'Frank', '23567');
Question 2
JustLee Books has recently hired a temporary salesman. The name of the new employee is
John. The login credential for John should expire at the end of his contract. As a salesperson,
John should be granted some object privileges on the JustLee Books database.
2.1 create the user John with a password that expire with the role of sales_person (3 marks).
CREATE USER john
IDENTIFIED BY sales1234
PASSWORD EXPIRE;
GRANT Sales_person TO john;
Rehoboth Tutors
