SQL-сценарии создания структуры базы данных
Person
CREATE TABLE person (
ID NUMBER(8) PRIMARY KEY,
lastname VARCHAR2(50) NOT NULL,
firstname VARCHAR2(50) NOT NULL,
birthday VARCHAR2(50) NOT NULL,
telephone VARCHAR2(50) NOT NULL,
role VARCHAR2(50) NOT NULL,
status VARCHAR2(50) NOT NULL,
bonuspunishment VARCHAR2(50) NOT NULL
);
CREATE SEQUENCE person_id
START WITH 1
INCREMENT BY 1
ORDER;
CREATE OR REPLACE TRIGGER person_t
BEFORE INSERT ON person
FOR EACH ROW
BEGIN
SELECT person_id.NEXTVAL
INTO :new.ID FROM DUAL;
END;
/
Login\Password
CREATE TABLE LoginPassword (
ID NUMBER(8) PRIMARY KEY,
person VARCHAR2(50) NOT NULL,
login VARCHAR2(50) NOT NULL,
password VARCHAR2(50) NOT NULL
);
CREATE SEQUENCE LoginPassword_id
START WITH 1
INCREMENT BY 1
ORDER;
CREATE OR REPLACE TRIGGER LoginPassword_t
BEFORE INSERT ON LoginPassword
FOR EACH ROW
BEGIN
SELECT LoginPassword_id.NEXTVAL
INTO :new.ID FROM DUAL;
END;
/
Role
CREATE TABLE Role (
ID NUMBER(8) PRIMARY KEY,
role VARCHAR2(50) NOT NULL
);
DROP SEQUENCE Role_s;
CREATE SEQUENCE Role_id
START WITH 1
INCREMENT BY 1
ORDER;
Status
CREATE TABLE Status (
ID NUMBER(8) PRIMARY KEY,
status VARCHAR2(50) NOT NULL
);
DROP SEQUENCE Status_s;
CREATE SEQUENCE Status_id
START WITH 1
INCREMENT BY 1
ORDER;
Schedule
CREATE TABLE Schedule (
ID NUMBER(8) PRIMARY KEY,
data VARCHAR2(50) NOT NULL,
person NUMBER NOT NULL
);
CREATE SEQUENCE Schedule_id
START WITH 1
INCREMENT BY 1
ORDER;
CREATE OR REPLACE TRIGGER Schedule_t
BEFORE INSERT ON Schedule
FOR EACH ROW
BEGIN
SELECT Schedule_id.NEXTVAL
INTO :new.ID FROM DUAL;
END;
/
Staff
CREATE TABLE Staff (
ID NUMBER(8) PRIMARY KEY,
person VARCHAR2(50) NOT NULL,
salary VARCHAR2(50) NOT NULL
);
CREATE SEQUENCE Staff_id
START WITH 1
INCREMENT BY 1
ORDER;
Заносим данные в таблицу Role
INSERT INTO Role (id,role) VALUES (1, 'admin');
INSERT INTO Role (id,role) VALUES (0, 'worker');
Заносим данные в таблицу Status
INSERT INTO Status (id,status) VALUES (1, 'dont work');
INSERT INTO Status (id,status) VALUES (0, 'work');
Заносим данные в таблицу Schedule
INSERT INTO Schedule (person,data) VALUES (0, 'Monday');
INSERT INTO Schedule (person,data) VALUES (0, 'Tuesday');
INSERT INTO Schedule (person,data) VALUES (0, 'Wednesday');
INSERT INTO Schedule (person,data) VALUES (0, ' Thursday ');
INSERT INTO Schedule (person,data) VALUES (0, 'Friday');
INSERT INTO Schedule (person,data) VALUES (0, 'Saturday');
INSERT INTO Schedule (person,data) VALUES (0, 'Sunday');