create table Accounts (
User_Name varchar(20) not null,
Active bit not null,
Password varchar(20) not null,
User_Role varchar(20) not null,
primary key (User_Name)
);
create table Order_Details (
ID varchar(50) not null,
Amount double precision not null,
Price double precision not null,
Quanity integer not null,
ORDER_ID varchar(50) not null,
PRODUCT_ID varchar(20) not null,
primary key (ID)
);
create table Orders (
ID varchar(50) not null,
Amount double precision not null,
Customer_Address varchar(255) not null,
Customer_Email varchar(128) not null,
Customer_Name varchar(255) not null,
Customer_Phone varchar(128) not null,
Order_Date datetime not null,
Order_Num integer not null,
primary key (ID)
);
create table Products (
Code varchar(20) not null,
Create_Date datetime not null,
Image longblob,
Name varchar(255) not null,
Price double precision not null,
primary key (Code)
);
alter table Orders
add constraint UK_sxhpvsj665kmi4f7jdu9d2791 unique
(Order_Num);
alter table Order_Details
add constraint ORDER_DETAIL_ORD_FK
foreign key (ORDER_ID)
references Orders (ID);
alter table Order_Details
add constraint ORDER_DETAIL_PROD_FK
foreign key (PRODUCT_ID)
references Products (Code);
---------------------------------------
insert into Accounts (USER_NAME, ACTIVE, PASSWORD, USER_ROLE)
values ('employee1', 1, '123', 'EMPLOYEE');
insert into Accounts (USER_NAME, ACTIVE, PASSWORD, USER_ROLE)
values ('manager1', 1, '123', 'MANAGER');
----------------
insert into products (CODE, NAME, PRICE, CREATE_DATE)
values ('S001', 'Core Java', 100, current_timestamp() );
insert into products (CODE, NAME, PRICE, CREATE_DATE)
values ('S002', 'Spring for Beginners', 50,
current_timestamp() );
insert into products (CODE, NAME, PRICE, CREATE_DATE)
values ('S003', 'Swift for Beginners', 120,
current_timestamp() );
insert into products (CODE, NAME, PRICE, CREATE_DATE)
values ('S004', 'Oracle XML Parser', 120, current_timestamp() );
insert into products (CODE, NAME, PRICE, CREATE_DATE)
values ('S005', 'CSharp Tutorial for Beginers', 110,
current_timestamp() );
Database quiz:
1. Get all courses having lab sessions
SELECT * FROM course WHERE credit_lab >0;
2. Get all courses of IEM department
SELECT C.name, D.name FROM course as C
INNER JOIN course_department as CD ON C.id = CD.course_id
INNER JOIN department as D ON D.id = CD.department_id
WHERE D.name = ‘IEM’;
3. Count number of courses handled by each instructor
SELECT I.name, count(CI.course_id) FROM Course_instructor as CI
INNER JOIN instructor as I ON I.id = CI.instructor_id
GROUP BY I.name;
4. Fill all instructors handing more than 3 courses
SELECT I.name FROM Course_instructor as CI
INNER JOIN instructor as I ON I.id = CI.instructor_id
WHERE count(CI.course_id) > 3
GROUP BY I.name;
Example:
1. Alice wants to send a message to Bob. And Alice wants to make sure that only Bob can read the
message. What should Alice do?
Ans:
Alice has a Pub_Alice, Priv_Alice, and Pub_Bob.
Bob has a Pub_Bob, Priv_Bob, and Pub_Alice.
Alice should use the public key of Bob (Pub_Bob) to encrypt the message.
Pub_Bob(message)
2. In which situation Alice will use the Priv_Alice to encrypt the message? Priv_Alice(message)
Ans:
In the case that Alice want to make sure that only Alice can write/adjust the message.
3. How can Alice be the one who writes the message and only FB can read the message?
Ans:
Alice uses her private key (Priv_Alice) to encrypt a message. Then, she uses the public key Facebook to
encrypt the above encrypted message again.
Pub_FB(Priv_Alice(message))
4. How to make sure Alice is the one established the message?
Ans: by certification authorities.
