[DB] The Relational Model (SQL)

SQL : Structured Query Language

 - Language for creating, manipulating, and querying relational DBMSs

 

Database

 - Relational Database is collection of one or more relations

 - Each table represents each relation

 

Relation : consists of two parts

 - Relation Schema : specifies name of relation, name and domain of each column

 - Relation Instance : a table itself (terms relation instance is often abbreviated to just relation)

 

자바로 치면 schema는 interface나 class, Instance는 instance화 된 object를 말하는 거겠쬬?

Integrity Contraints (IGs)

- Enable to reject operations which might corrupt the data

 

 

If we create Students table like below (which means we create the relation named Students)

sid	name	login	age	gpa
11111	Smith	smith@ee	19	3.8
22222	Smith	smith@cs	18	3.4
33333	Jones	jones@cs	19	3.2

Students instance have

three tuples

five fields named sid, name, login, age, and gpa

 

DDL : Database Definition Language

 

CREAT TABLE

Define a new table

You can set specific condition for column

 

'field_name' TYPE [NULL / NOT NULL] [DEFAULT] [AUTO INCREMENT][PRIMARY KEY / FORIEGN KEY]

CREATE TABLE Students ( sid CHAR(20) , name CHAR(30) , login CHAR(20) , age INTEGER, gpa REAL);

CREATE TABLE Students ( sid CHAR(20) NOT NULL PRIMARY KEY,
                        name CHAR(30) ,
                        login CHAR(20), 
                        age INTEGER, 
                        gpa REAL);

 

 

INSERT

Insert command insert the tuple

INSERT INTO Students (sid, name, login, age, gpa) VALUES (53688, 'Smith', 'smith@ee', 18, 3.2);

 

DELETE

Delete command delete tuple from the table

DELETE FROM Students S WHERE S.name = 'Smith';

If you want to delete NULL tuple

DELETE FROM students where ISNULL(name);

 

UPDATE

Update command modify the column values

 

If we want to increment the age of the student with sid 11111

 UPDATE Students S SET S.age = S.age + 1 WHERE S.sid = 11111;

 

You can also update two columns with one line of code

 UPDATE Students S SET S.age = S.age + 1, S.gpa = S.gpa - 0.1 WHERE S.gpa > 3.0;

 

ALTER

You can set keys after creating the Enrolled table with ALTER command

ALTER TABLE Enrolled
   ADD CONSTRAINT 
   FOREIGN KEY (student_id)
   REFERENCES Students (sid);

# add column
ALTER TABLE STUDENTS
   ADD Height Integer;
   
# change column
ALTER TABLE STUDENTS
   Change Height new_height Integer;
   
# drop column
ALTER TABLE STUDENTS
   DROP new_height;
   

Drop table

DROP table students;

 

 

Key Constraints (키 제약조건)

Statement that ucertain minimal subsets of the fields of the relation is a unique identifier for the tuple

 

Primary Key

A key which is unique from the other tuple. For example in Students, sid can be a primary key and we can refer to a Students tuple by storing its sid value.

 

Candidate Key (specified using UNIQUE)

A set of fleid that uniquely identifies the tuple (by the key constaraints)

A relation can have more than one candidate keys

 

예를 들면,

위의 Students 테이블에서 name field만 본다면 Smith가 중복된다.

그래서 만약 name field를 Primary Key로 설정하게 된다면 다른 이름의 Smith 정보를 저장할 수 없게되어 우리가 원하는 기능을 제대로 수행하지 못하게된다.

하지만 name과 다른 field들의 조합으로 본다면 각 tuple들은 구분 가능하다.

 

BUT, set of keys {sid, name} is not a key, this is Super Key because field {sid} itself can be a key

set of fields {name, login} can be a key. If that case, two students may have the same name or login, but not both.

 

 

CREATE TABLE Students ( sid CHAR(20) ,
                        name CHAR (30) ,
                        login CHAR(20) ,
                        age INTEGER,
                        gpa REAL,
                        UNIQUE (name, login),
                        CONSTRAINT StudentsKey PRIMARY KEY (sid) )

 

Or you can add later after creating the table with ALTER command

ALTER TABLE Students
   ADD CONSTRAINT StudentKey
   PRIMARY KEY (sid);

 

 

Foreign Key

Sometimes one relation can be linked to another relations. Foreign Key provides link between two relations. It should match the primary key of referenced relation.

 

This is Enrolled relation which have cid, grade, and student_id fields

 

cid	grade	student_id
CSE 101	B	11111
CSE 101	C	33333
CSE 373	A	11111
GEO 102	A	22222

student_id field in Enrolled relation is a foreign key which refers to Students. It matchs with primary key {sid} in Students

 

Both foreign key of the Enrolled {student_id} and primary key of the Students {sid}

should have same number of columns, data types (name can be different)

 

 

CREATE TABLE Enrolled ( student_id CHAR(20) ,
                        cid CHAR(20),
                        grade CHAR(10),
                        PRIMARY KEY (student_id, cid),
                        FOREIGN KEY (student_id) REFERENCES Students)

 

 

Referential Integrity

 

4 options on delete and updates

NO ACTION : default action for delete update(reject the delete or update request)

CASCADE : also delete all tuples that refer to deleted tuple (→ weak entity can use this keyword)

SET NULL / SET DEFAULT : set foreign key value of referencing tuple

 

FOREIGN KEY (sid)
  REFERENCES Students
    ON DELETE CASCADE
    ON UPDATE SET DEFAULT

 

⁕ 이후 한국어 설명 추가하기

 

References

Raghu Ramakrishnan and Johannes Gehrke. 2000. <i>Database Management Systems</i> (2nd. ed.). McGraw-Hill, Inc., USA.