Key concepts in requirements analysis & design
Main topics of analysis & design
Entity (clear, a single noun), attributes (properties of the entity), relationships
Three stages of design
Conceptual-Logical-Physical.
Conceptual design (relationships between entities),
Logical design (entity properties, constraints, normalization),
Physical design (data types, sizes, physical agreements)
Concepts of a database
Shared, integrated, stored, operated
Once it moves into the database, an entity is called a table
A table's horizontal dimension: row, record, tuple
vertical dimension: column, attribute, field
Transaction
The logical unit that must be executed for a database modification (change)
Composed of multiple SQL statements
Properties — Atomicity, Consistency, Isolation, Durability *Atomicity: all or nothing
(A C I D) *Consistency: only in one direction
Types of keys
Candidate key, primary key (PK), alternate key, composite key
Foreign key (FK)
SQL — Structured Query Language
DDL Definition - create, alter, drop
DML - insert, update, delete, select
DCL Control - grant, alter
TCL Transaction - commit, rollback, savepoint
alter — column options
add — add
modify — modify
drop — delete
rename — rename
DB implementation concepts
Table (TableName) — fill in attribute values for the entity
alter table TableName
add(
column_name data_type(size) N/N flag
) //CTRL + ENTER
Table (TableName) — fill in attribute values for the entity
ALTER TABLE TableName
ADD(
CONSTRAINT [unique identifier name] FOREIGN KEY(child key name)
REFERENCES[parent table](parent key);
Find a specific key
select * from USER_CONSTRIANS;
// a system-provided predeclared table
// returns user-declared constraints
Database implementation
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 + add "PRIMARY KEY"
-- but this isn't possible after the table is already created
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 not possible after the table is already created
-- but you may 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.
----//key definition (constraint) option 3) -- inside the sql dev tool
-- right-click the table > Edit > Add Constraint
);
SELECT * FROM LPROD;
----//key definition (constraint) option 4)
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
*/
DESC BUYER; --check BUYER table structure
--June 5
--table structure change (add column)
ALTER TABLE BUYER
ADD(
BUYER_MAIL VARCHAR2(60) NOT NULL,
BUYER_CHARGER VARCHAR2(20) ,
BUYER_TELEXT VARCHAR2(2)
);
--table structure change (modify column)
ALTER TABLE BUYER
MODIFY(
BUYER_NAME VARCHAR2(60) -- in Oracle NOT NULL is the default, so no need to declare it separately
);
ALTER TABLE BUYER
ADD(
CONSTRAINT FR_LPLOG_BUYER FOREIGN KEY(BUYER_LGU) --user-defined name for the table FR_LPLOG_BUYER
REFERENCES LPROD(LPROD_GU)
);
--check FK constraints 1)
-- right-click the table, Edit, Constraints
--check FK constraints 2)
SELECT * FROM USER_CONSTRAINTS; --system-provided predeclared table
-- add a CHECK constraint so BUYER_TELEXT accepts only two-digit numbers
ALTER TABLE BUYER
ADD CONSTRAINT CHECK_PHONE
CHECK(REGEXP_LIKE(BUYER_TELEXT, '[0-9][0-9]'));
--add an index to speed up SELECT queries
CREATE INDEX IDX_BUYER -- not an entity but a DB object; typically recognized as an 'object' inside an entity.
ON BUYER(BUYER_NAME, BUYER_ID);
-- usage example
-- SELECT *
-- FROM BUYER
-- WHERE BUYER_NAME = 'Nongshim';
--drop index
-- DROP INDEX IDX_BUYER;
--create product info table
CREATE TABLE PROD
(
PROD_ID VARCHAR2(10) NOT NULL,
PROD_NAME VARCHAR2(40) NOT NULL,
PROD_LGU CHAR(4) NOT NULL,
PROD_BUYER CHAR(6) NOT NULL,
PROD_COST CHAR(10) NOT NULL,
PROD_PRICE NUMBER(10) NOT NULL,
PROD_SALE NUMBER(10) NOT NULL,
PROD_OUTLINE VARCHAR2(100) NOT NULL,
PROD_DETAIL CLOB,
PROD_IMG VARCHAR2(40) NOT NULL,
PROD_TOTALSTOCK NUMBER(10) NOT NULL,
PROD_INDATE DATE,
PROD_PROPERSTOCK NUMBER(10) NOT NULL,
PROD_SIZE VARCHAR2(10),
PROD_COLOR VARCHAR2(100),
PROD_DELIVERY VARCHAR2(255),
PROD_UNIT VARCHAR2(6),
PROD_QTYSALE NUMBER(10),
PROD_MILEAGE NUMBER(10),
CONSTRAINT PK_PROD PRIMARY KEY(PROD_ID),
CONSTRAINT FR_PROD_LPROD FOREIGN KEY(PROD_LGU)
REFERENCES LPROD(LPROD_GU),
CONSTRAINT FR_PROD_BUYER FOREIGN KEY(PROD_BUYER)
REFERENCES BUYER(BUYER_ID)
);
--create inbound-product info table
CREATE TABLE BUYPROD
(
BUY_DATE DATE NOT NULL,
BUY_PROD VARCHAR2(10) NOT NULL,
BUY_QTY NUMBER(10) NOT NULL,
BUY_COST NUMBER(10) NOT NULL,
CONSTRAINT PK_BUYPROD PRIMARY KEY(BUY_DATE, BUY_PROD),
CONSTRAINT FR_BUYPROD_FROID FOREIGN KEY(BUY_PROD) REFERENCES PROD(PROD_ID)
);
--create member info table
CREATE TABLE MEMBER
(
MEM_ID VARCHAR2(15) NOT NULL,
MEM_PASS VARCHAR2(15) NOT NULL,
MEM_NAME VARCHAR2(20) NOT NULL,
MEM_REGNO1 CHAR(6) NOT NULL,
MEM_REGNO2 CHAR(7) NOT NULL,
MEM_BIR DATE,
MEM_ZIP CHAR(7) NOT NULL,
MEM_ADD1 VARCHAR2(100) NOT NULL,
MEM_ADD2 VARCHAR2(80) NOT NULL,
MEM_HOMETEL VARCHAR2(14) NOT NULL,
MEM_COMTEL VARCHAR2(14) NOT NULL,
MEM_HP VARCHAR2(15) NOT NULL,
MEM_MAIL VARCHAR2(60) NOT NULL,
MEM_JOB VARCHAR2(40) NOT NULL,
MEM_LIKE VARCHAR2(40) NOT NULL,
MEM_MEMORIAL VARCHAR2(40) NOT NULL,
MEM_MEMORIALDAY DATE,
MEM_MILEAGE NUMBER(10) NOT NULL,
MEM_DELETE VARCHAR2(1) NOT NULL,
CONSTRAINT PK_MEMBER PRIMARY KEY (MEM_ID)
);
--create cart info table
CREATE TABLE CART
(
CART_MEMBER VARCHAR2(15) NOT NULL,
CART_NO CHAR(13) NOT NULL,
CART_PROD VARCHAR2(10) NOT NULL,
CART_QTY NUMBER(8) NOT NULL,
CONSTRAINT PK_CART PRIMARY KEY(CART_NO, CART_PROD),
CONSTRAINT FR_CART_MEMBER FOREIGN KEY(CART_MEMBER)
REFERENCES MEMBER(MEM_ID),
CONSTRAINT FR_CART_PROD FOREIGN KEY(CART_PROD)
REFERENCES PROD(PROD_ID)
);
--zip code info table
CREATE TABLE ZIPTB
(
ZIPCODE CHAR(7) NOT NULL,
SIDO VARCHAR2(2 CHAR) NOT NULL,
GUGUN VARCHAR2(10 CHAR) NOT NULL,
DONG VARCHAR2(30 CHAR) NOT NULL,
BUNJI VARCHAR2(10 CHAR),
SEQ NUMBER(5) NOT NULL );
CREATE INDEX IDX_ZIPTB_ZIPCODE ON ZIPTB(ZIPCODE);
-- DDL ends here
-- from here -> DML, select block then ctrl+enter
insert into LPROD (LPROD_ID, LPROD_GU, LPROD_NM) values (1, 'P101','Computer products');
insert into LPROD (LPROD_ID, LPROD_GU, LPROD_NM) values (2, 'P102','Electronics');
insert into LPROD (LPROD_ID, LPROD_GU, LPROD_NM) values (3, 'P201','Women casual');
insert into LPROD (LPROD_ID, LPROD_GU, LPROD_NM) values (4, 'P202','Men casual');
insert into LPROD (LPROD_ID, LPROD_GU, LPROD_NM) values (5, 'P301','Leather goods');
insert into LPROD (LPROD_ID, LPROD_GU, LPROD_NM) values (6, 'P302','Cosmetics');
insert into LPROD (LPROD_ID, LPROD_GU, LPROD_NM) values (7, 'P401','Music/CD');
insert into LPROD (LPROD_ID, LPROD_GU, LPROD_NM) values (8, 'P402','Books');
insert into LPROD (LPROD_ID, LPROD_GU, LPROD_NM) values (9, 'P403','Stationery');
rollback;
select * from lprod; --since we haven't committed yet, this is held in memory, not on disk
--F11 = commit = save to disk
commit;
