SQL — Structured Query Language
1. Transaction
A logical unit (cycle) with a purpose.
The basic unit of database operation.
On failure, it reverts to the initial state (atomicity).
2. Transaction properties
1) Atomicity (Atomicity)
If a transaction does not finish completely, it must be as if it never happened.
This property is called "All or Nothing".
2) Consistency (Consistency)
Effects among transactions must propagate in only one direction.
3) Isolation (Isolation)
A transaction's partial state must not be visible to other transactions.
e.g. just as Byeonsato cannot enter while Hong Gildong is in the middle of business in the bathroom
4) Durability (Durability)
After a successful transaction (commit), the result must be written to the database (disk).
cmd
> sqlplus sys/java@localhost:1521 as sysdba;
> create user pc04 identified by java;
> grant connect, resource to pc04;
ERwin > (physical) > tools
Environment settings
1) database > choose database > oracle (select)
2) database > database connection > id/pw/ip (or localhost or 127.0.01:1521/xe)
// my network address / for practice
forward engineering
1) tools > forward engineering > schema > (uncheck trigger) > generate..
ERD ERwin ----- forward flow -----> DB SQL development
reverse engineering
1) tools > reverse engineering > (choose logical/physical)
2) table/views owned owner of entities/virtuals
current user — the currently connected user
ERD ERwin <--- pulling the structure (schema) back ---- DB SQL development
SQL development
CTRL + N : create a database file ( study.sql )
CREATE TABLE LPROD --product category table
(
LPROD_ID NUMBER(5) NOT NULL, --sequence
LPROD_GU CHAR(4) NOT NULL, --product category code
LPROD_NM VARCHAR(40) NOT NULL, --product category name
CONSTRAINT PK_LPROD PRIMARY KEY(LPROD_GU) --identifier (key)
);
SELECT * FROM LPROD;
CREATE TABLE BUYER --transaction info table
(
BUYER_ID CHAR(6) NOT NULL, --buyer code
--//key definition (constraint) option 1) BUYER_ID CHAR(6) NOT NULL PRIMARY KEY,
BUYER_NAME VARCHAR2(40) NOT NULL, --buyer name
BUYER_LGU CHAR(4) NOT NULL, --major product category handled
BUYER_BANK VARCHAR2(60), --bank
BUYER_BANKNO VARCHAR2(60), --account number
BUYER_BANKNAME VARCHAR2(15), --account holder
BUYER_ZIP CHAR(7), --zip code
BUYER_ADD1 VARCHAR2(100), --address 1
BUYER_ADD2 VARCHAR2(70), --address 2
BUYER_COMTEL VARCHAR2(14) NOT NULL, --phone
BUYER_FAX VARCHAR2(20) NOT NULL --FAX
----//key definition (constraint) option 2) CONSTRAINT PK_BUYER_ID PRIMARY KEY(BUYER_ID)
--identifier (key) // add key definition + PRIMARY KEY
-- but you might wonder what BUYER_ID is. e.g. "Leader, come here!" VS "Leader Hong Gildong, come here!"
-- primary keys exist in every table, so they can clash. Hence a separate name.
-- => (entity name)PK_BUYER_ID must be unique within a single account.
);
SELECT * FROM LPROD;
----//key definition (constraint) option 2)
ALTER TABLE BUYER
ADD(
CONSTRAINT PK_BUYER PRIMARY KEY(BUYER_ID)
);
/* ALTER TABLE BUYER
ADD: add columns and constraints
MODIFY: modify a column
DROP: drop a column
RENAME: rename a column
*/
-- check BUYER table structure
DESC BUYER;
