slide1 n.
Download
Skip this Video
Download Presentation
Relational Database Structure

Loading in 2 Seconds...

play fullscreen
1 / 67

Relational Database Structure - PowerPoint PPT Presentation


  • 220 Views
  • Uploaded on

Relational Database Structure. - Relation : set of tuples - Tuple : 1) ordered set (i.e. list) of values from domain D1, D2, ..., Dk 2) mapping from attribute names to values in the domains of the attributes => standard definition

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Relational Database Structure' - jabari


Download Now An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

Relational Database Structure

- Relation : set of tuples

- Tuple : 1) ordered set (i.e. list) of values from domain D1, D2, ..., Dk

2) mapping from attribute names to values in the domains

of the attributes

=> standard definition

Ex) Relation CITIES

First attribute = Name

Second attribute = State

Third attribute = Population

<Buffalo, NY, 800>

<San Jose, CA, 100>

<Las Vegas, Nevada, 900>

CITIES Name State Popularation

Buffalo NY 800

San Jose CA 100

Olando FL 900

slide2

Relational Database Structure (cont'd)

- Relation scheme : list of attributye names (R)

- Relation instance : relation (r)

- (Relational) Database scheme : collection of relation schemes

- Key (of relation scheme R) :

subset K = {A1, A2, ... , Am} of attributes of R such that

no two tuples of relation r(R) can have the same value for K

- Primary key : a key of the relation scheme that we select

to identify tuples

Ex)

primary key

CAR Licence-number Engine_number Maker Model-year

Texas-ABC-123 F300001 KIA 1991

Michigan-ZEP-453 X21023 Hyundai 1995

key

slide3

Integrity Constraints

- Entity integrity constraint

no primary key can be null

- Referential integrity constraints (foreign key constraints)

foreign key : set of attributes which is the primary key

of another relation

(ex) Dept = (Dname, Manager, Budget)

Employee = (Ename, Bdate, Salary, Dname)

Every value of the foreign key must exist in the set of the

corresponding primary key values of another relation(Dept)

slide4

Database Operation (Formal Language)

  • - Operation to manipulate a relation is needed
  • - There are two types of operation, Relational Algebra and
  • Relational Calculus
  • Relational Algebra
  • query is expressed by applying specialized algebraic
  • operators operators => SQL
  • * procedural : how the desired data is retrieval
  • Relational Calculus
  • query is described a desired set of tuples by specifying a
  • predicate the tuples must satisfy => QUEL, QBE
  • * nonprocedural : what data is needed
slide5

Relational Algebra

Relational Algebra is set of operators to manipulate a relation

(1) Classification by Set or Relational operaors

- Set operators : Union () R S

Intersection ( ) R S

Set Difference ( ) R S

Cartesian product () R S

- Relational operator : Selection

Projection

Join

Division

slide6

Relational Algebra (cont'd)

(2) Classification by Primitive or Composite operators

- Primitive operators : Union

Intersection

Cartesian product

Selection

Projection

- Composite operator : Join

Intersection

Division

basic element

operation

composed of

primitive operators

slide7

Relational Algebra (cont'd)

(1) Union ( R S )

- set of tuples that are in R or S or both

S

d1 d2 d3

b1 b2 b3

e1 e2 e3

R

a1 a2 a3

b1 b2 b3

c1 c2 c3

R U S

a1 a2 a3

b1 b2 b3

c1 c2 c3

d1 d2 d3

e1 e2 e3

only once

  • <b1, b2, b3> appears only once in R S
  • (i.e., deplicates are not eliminated. Remember
  • a relation is a set)
  • A set allowing duplicates is called a multiset
slide8

Relational Algebra (cont'd)

  • Definition Union-compatible
  • 1) Relations are of the same arity
  • 2) attribute in the same position have compatible domains
  • Union can be formed only for union-compatible relations
  • (2) Intersection ( R S )
  • - set of tuples that are in both R and S. i.e., R - (R - S)
  • - relations must be union-compatible

R S

b1 b2 b3

S

d1 d2 d3

b1 b2 b3

e1 e2 e3

R

a1 a2 a3

b1 b2 b3

c1 c2 c3

slide9

Relational Algebra (cont'd)

(3) Cartesian Product ( R S )

- R : set of k1-tuples

S : set of k2-tuples

R S : set of (k1 + k2)-tuples

S

d1 d2 d3

e1 e2 e3

R

a1 a2 a3

b1 b2 b3

c1 c2 c3

R S

a1 a2 a3 b1 b2 b3

a1 a2 a3 e1 e2 e3

b1 b2 b3 d1 d2 d3

b1 b2 b3 e1 e2 e3

c1 c2 c3 d1 d2 d3

c1 c2 c3 e1 e2 e3

slide10

Relational Algebra (cont'd)

(4) Set difference ( R - S )

- set of tuples in R but not in S

- must be union-compatible

S

d1 d2 d3

b1 b2 b3

e1 e2 e3

R

a1 a2 a3

b1 b2 b3

c1 c2 c3

R - S

a1 a2 a3

c1 c2 c3

slide11

Relational Algebra (cont'd)

(5) Selection, F(R)

- F : selection predicate

- select only those tuples that satisfy F

Example

Age > 30 AND Dept = 512 EMP

EMP Name Dept Age

Adams 512 25

Jones 512 40

Gilbert 400 32

Result

slide12

Relational Algebra (cont'd)

(6) Projection, i1, i2, i3, ... , im (R)

- take a subset of attributes from R

Example

Name, Age EMP

1, 3 EMP)

EMP Name Dept Age

Adams 512 25

Jones 512 40

Gilbert 400 32

Result

slide13

Relational Algebra (cont'd)

(7) Join, R join predicate S

- set of tuples in R X S satisfying the join predicate

- join predicate : arithmetic comparison expression involving

attribute in R as well as in S

EMPEMP.Child = Children.Cname Children

(7.1) Equijoin, R R.a = S. b S

Children Cname School

Mary Foxlane

Charlie Bronxville

EMP Ename Age Child

Jones 45 Mary

White 25 Charlie

Ename Age Child

Jones 45 Mary

White 25 Charlie

Cname School

Mary Foxlane

Charlie Bronxville

slide14

Relational Algebra (cont'd)

(7.2) Thetajoin, RR.a S.b S

-  : any arithmetic comparision operator (=, <, >, <=, >=, !=)

Example

R A B C

1 2 3

4 5 6

7 8 9

S D E

3 1

6 2

RB < D S

A B C D E

1 2 3 3 1

1 2 3 6 2

4 5 6 6 2

slide15

Relational Algebra (cont'd)

(7.3) Natural Join, RS

- Equijoin based on attributes of the same name

- only one attribute of the same name is projected in the result

Example

(7.1) Equijoin, R R.a = S. b S

Children Childname School

Mary Foxlane

Charlie Bronxville

EMP Ename Age Childname

Jones 45 Mary

White 25 Charlie

EMPChildren =

School

Foxlane

Bronxville

Ename Age Childname

Jones 45 Mary

White 25 Charlie

slide16

.

.

.

.

.

.

Relational Algebra (cont'd)

(8) Division, RS

- Let two attribute sets X = {A1, A2, ... , Am}

Y = {B1, B2, ... , Bm}

- Two relation R(X, Y), S(Y) have above attribute set

- R S => can be divided by S(Y) and only one tuple

Example

S C D

c d

e f

A B

a b

e d

R A B C D

a b c d

a b e f

b c e f

e d c d

e d e f

a b d e

=

slide17

Relational Algebra (cont'd)

  • Semijoin
  • R S = R (S[X Y])
  • Outerjoin R의 애트리뷰트 S의 애트리뷰트
  • - R + S
  • - result relation have all tuples of the two relations
  • - if two tuples is not common, the result relation is filled null
  • attribute value
  • See the Lee, S. H.'s book p.141
slide18

Relational Calculus (Introduction)

- Query is described a desired set of tuples by specifying a

predicate the tuples must satisfy

- Another representation of operation for relation

- Based on predicate calculus

- Declarative not procedural

-Tuple Relational Calculus

Domain Relational Calculus

- Equivalent in expressive power to

Relational Algebra

= Tuple Relational Calculus

= Domain Relational Calcucus

slide19

Tuple Relational Calculus

  • What ? represent tuple calculus expression to specify
  • user's request
  • Basic Concepts
  • A query in tuple relational calculus is expressed as
  • { t | P(t) } t : tuple variable
  • P(t) : well-formed formula (WFF) = condition
  • t in P(t) : bound variable
  • A well-formed formula produces True or False given
  • an interpretation
  • Well-Formed Formula (WFF)
  • Atomic formulas connected by logical connectives, AND, OR,
  • NOT and quantified by quantifiers, A (existential quantifier),
  • E (universal quantifier)

result

tuples

slide20

Tuple Relational Calculus (cont'd)

  • Atomic Formula
  • 1. R(s) : R is a relation name
  • s is tuple variable
  • means that tuples s is in relation R.
  • 2. ti[A] tj[B]
  •  : comparision operator ( =, <, >, <=, >=, != )
  • ti , ti : tuple variables
  • A, B : attributes
  • e.g., t1.A = t2.B (specifies  join)
  • 3. ti[A] constant
  • e.g., ti[A] = 'Mary' (specifies selection)
slide21

Tuple Relational Calculus (example)

  • Tuple Relational Calculus Query
  • Consider relations EMP(Name, MGR, DEPT, SAL)
  • CHILDREN(Ename, Cname, Age)
  • Q1 : Retrieve SALary and CHILDREN's Name of EMPloyees
  • whose manager is 'White'
  • {t | ( E e)( E c) (EMP(e) AND CHILDREN(c) AND
  • /* instantiate tuple variables
  • e[Name] = c[Ename] AND /* join condition
  • e[MGR] = 'White' AND /* selection condition
  • t[1st attribute] = e[SAL] AND
  • t[2nd attribute] = c[Cname] ) } /* projections

result

tuples

slide22

Domain Relational Calculus (introduction)

  • What ? represent domain variables expression to specify
  • user's request
  • Basic Concepts
  • A query in domain relational calculus is expressed as
  • { x1, x2, ... , x2 | P(x1, x2, ... , x2) }
  • x1, x2, ... , x2 : domain variables
  • P(t) : well-formed formula (WFF) = condition
  • Well-Formed Formula (WFF)
  • Atomic formulas connected by logical connectives, AND, OR,
  • NOT and quantified by quantifiers, E (existential quantifier),
  • A (universal quantifier)

result

tuples

slide23

Domain Relational Calculus (cont'd)

  • Atomic Formula
  • 1. R(x1, x2, ... , x2 ) : R is k-ary relation R(t)
  • xi is domain variable or constant
  • 2. x y (specifies join) ti[A] tj[B]
  •  : comparision operator ( =, <, >, <=, >=, != )
  • x, y : domain variables
  • 3. x constant (specifies selection) ti[A] constant
  • e.g., ti[A] = 'Mary' (specifies selection)
slide24

Domain Relational Calculus (example)

  • Domain Relational Calculus Query
  • Consider relations EMP(Name, MGR, DEPT, SAL)
  • CHILDREN(Ename, Cname, Age)
  • Q1 : Retrieve SALary and CHILDREN's Name of EMPloyees
  • whose manager is ’White'
  • {q, s | ( E u) (E v) ( E w) ( E x) ( E y)
  • (EMP(u, v, w, q) AND CHILDREN(x, s, y) AND
  • /* instantiate domain variables
  • u = x AND /* join condition
  • v = 'White' } /* selection condition
  • /* projections is implied (q, s)
slide25

Commercial Database Languages

  • Background
  • The formal languages(operations) provide a concise notation
  • for representing queries.
  • However commercial database systems require a more
  • user friendly query language
  • Commercial Language vs Formal Language
  • SQL Relational Algebra + Relational Calculus
  • QBE Domain Relational Calculus (p. 204- 209)
  • QEUL Tuple Relational Calculus ( X )
slide26

SQL (History)

  • Belief History of SQL Standards

CODASYL Database Facility

SQL NDL

SQL86 (about 100 pages)

SQL89(about 115 pages)

SQL2 (about 500 pages)

SQL3(over 1000 pages)

1960's - 1970's : DBTG in

CODASYL

The late 1970's : ANSI

Since the early 1980's

:ISO

slide27

SQL (Overview)

  • Overview of SQL
  • - Previously called SEQUEL(Structured English QUEry Language)
  • - SQL has serveral parts :
    • Data Definition Languae (DDL) :
  • defining relation schemes, deleting relations, creating indices
  • modifying relation schemes
    • Data Manipulation Languae (DML) :
  • retrieve, insert, delete, modifying tuples in the database
    • Embedded Data Manipulation Languae
  • embedded form of SQL is designed for use within
  • general-purpose programming language such as C, Fortran
slide28

SQL (Overview)

    • View Definition
  • SQL DDL for defining views
    • Authorization
  • Specifying access rights to relations and views
    • Transaction Control
  • Specifying the beginning and ending of transactions
slide29

DDL (Overview)

  • - A relation is called a Table
  • A table allows duplicate tuples
  • A Multiset rather than a set
  • Duplicates must be eliminated explicitly
  • (by using 'SELECT DISTINCT')
  • - A table consist of three types :
  • Base table : created by DDL
  • View : virtual table based on base table (such as, window)
  • Temporary table : do not created by DDL, but result of
  • query processing
slide30

DDL (cont'd)

  • Creating Base Table
  • created an empty table which have no data just now
  • CREATE TABLE base-table-name
  • (column-name data-type [NOT NULL]
  • { , column-name data-type [NOT NULL] }* ) ;
  • * [ ] : optional part, { }* : repeated part
  • Example
  • CREATE TABLE STUDENT
  • (SNO INTEGER NOT NULL,
  • SNAME CHAR(20),
  • YEAR INTEGER,
  • DEPT CHAR(4),
  • PRIMARY KEY (SNO)) ;
slide31

DDL (cont'd)

  • Data Type
    • INTEGER : 4-byte integer
    • SMALLINT : 2-byte integer
    • FLOAT(p) : floating point integer
    • DECIMAL(i, j) : fixed point integer
    • CHAR(n) : n-charater string
    • DATE : date (yymmdd)
    • TIME : time (hhmmss)
slide32

DDL (cont'd)

  • Altering Base Table
  • modify base table structure (scheme)
  • ALTER TABLE base-table-name
  • ADD column-definition column-type
  • | DROP column-name ;
  • Example
  • ALTER TABLE STUDENT
  • ADD ADDR CHAR(60) ;
  • * STUDENT table have five columns. But added column is filled
  • null values.
slide33

DDL (Create Index)

  • Creating Index
  • creating index file for fast query processing
  • but, they have an overhead to maintain index
  • CREATE [UNIQUE] INDEX index-name
  • ON base-table (column-name [ASC | DESC]
  • {, column-name [ASC | DESC] }* )
  • [CLUSTER] ;
  • Example
  • CREATE UNIQUE INDEX CNINDEX
  • ON COURSE(CNO) ;
  • * COURSE table have an index. We can access the table COURSE
  • by CNO values
slide34

DDL (Create Index)

  • Example
  • CREATE INDEX XINDEX
  • ON REL(COL1, COL2 DESC, COL3)
  • CLUSTER ;
  • * REL table have an index XINDEX which is consist of COL1,
  • COL2(descending order), COL3.
  • * Record of REL table is stored in the order of XINDEX values.
  • Physically, XINDEX values are adjacent by sequence of
  • XINDEX values.
slide35

DDL (Drop Table)

  • Deleting Table
  • Deleting base table which can not be accessed forever
  • DROP TABLE base-table-name ;
  • Example
  • DROP TABLE ENROL ;
  • * This statement delete view and index as well as base table.
slide36

DDL (Drop Index)

  • Deleting Index
  • Deleting index from base table
  • DROP INDEX index-name ;
  • Example
  • DROP INDEX XINDEX ;
slide37

DML

  • SQL DML have following facilities
  • - retrieve, insert, delete, modifying tuples in the database
  • - SELECT, INSET, DELETE, UPDATE statement
  • - they manipulate to the base table or view
  • SELECT (basic structure)
  • - basic SQL structure for retriving
  • - selection and projection operation
  • SELECTcolumn-list
  • FROMtable-list
  • WHEREcondition ;

basic form

slide38

DML (Select)

  • Example
  • List the names and student-numbers of students who major in
  • 'Computer Engineering' in STUDENT table.
  • SELECT SNAME
  • FROM STUDENT
  • WHERE DEPT = 'Computer Engineering'
  • SNAME SNO
  • Kim Jin Oh 901234
  • Park Eun Yeong 920049

result is a table

(temporary table)

slide39

DML (Select)

  • SELECT (Eliminating Duplicates)
  • - delete duplicate tuples in result table
  • Example
  • List DEPT in STUDENT table.
  • SELECT DISTINCT DEPT
  • FROM STUDENT ;
slide40

DML (Select)

  • SELECT (All the columns)
  • - wildcard * usage
  • Example
  • List STUDENT table.
  • SELECT *
  • FROM STUDENT ;
  • is equivalent to SELECT SNO, SNAME, YEAR, DEPT
  • FROM STUDENT ;
slide41

DML (Select)

  • SELECT (Ordeing the result tuples)
  • Example
  • List all student whose scores are greater than 90 descending
  • order by SNO, ascending order by CNO.
  • SELECT SNO, CNO
  • FROM ENROL
  • WHERE MIDTERM >= 90
  • ORDER BY SNO DESC, CNO ASC ;
  • 400 C312
  • 400 C324
  • 300 C312
  • 300 C423
  • 100 C413
  • 100 E412

first ordering second ordering

in first ordering

slide42

DML (Select)

  • SELECT (With arithmetic expression and charater string)
  • Example
  • For each student who enrol the 'Database',
  • list SNO and MIDTERM + 3 with 'Midterm =' string.
  • SELECT SNO, 'Midterm =', MIDTERM+3
  • FROM ENROL
  • WHERE CNO = 'Database' ;
  • SNO
  • 300 Midterm = 93
  • 400 Midterm = 83
  • 500 Midterm = 73
slide43

DML (Select)

  • SELECT (Join, Selection from multiple tables)
  • Example
  • For student who enrol the 'Database', list SNAME, DEPT,
  • and GRADE.
  • SELECT STUDENT.SNAME, STUIDENT.DEPT
  • ENROL.GRADE
  • FROM STUDENT, ENROL
  • WHERE STUDENT.SNO = ENROL.SNO AND
  • ENROL.CNO = 'Database' ;
  • SNAME DEPT GRADE
  • Kim CE A
  • Chung CE A
  • Lee Math B
slide44

DML (Select)

  • SELECT (Self Join)
  • Example
  • For each employee whose salary exceeds his/her manager's
  • salary, list the employee's name and his/her manager's name.
  • SELECT X.Name, Y.Name
  • FROM EMP X, EMP Y
  • WHERE X.Sal > Y.Sal

ambiguous; thus, use table labels X and Y

slide45

DML (Select)

  • SELECT (Aggregate function)
  • - Operate on aggregates of tuples
  • - The result of an aggregate function is a single value
  • - To applying single column
  • Functions
  • AVG (average) - only for numeric type
  • SUM (total) - only for numeric type
  • COUNT(count)
  • MAX (maxmun)
  • MIN (minimum)
slide46

DML (Select)

  • Example
  • Count number of students in STUDENT table.
  • SELECT COUNT(*)
  • FROMSTUDENT ;
  • Result
  • 5

* Result include duplicated tuples

COUNT(DISTINCT CNO) don't have duplicated tuples

slide47

DML (Select)

  • Example
  • How many courses are enrolled by student (SNO = 300)?
  • SELECT COUNT(DISTINCT CNO)
  • FROMENROL
  • WHERESNO = 300 ;
  • Result
  • 2

* COUNT(DISTINCT CNO) don't have duplicated tuples

slide48

DML (Select)

  • Example
  • Find average of midterm examination of 'Database'.
  • SELECT AVG(Midterm)
  • FROMENROL
  • WHERECno = 'Database' ;
  • Result
  • 87
slide49

DML (Select)

  • SELECT (Group by)
  • - Grouping by specified column name
  • Example
  • Find average of each course's final examination.
  • SELECT Cno, AVG(Final)
  • FROMENROL
  • GROUP BY Cno ;
  • Result
  • Cno
  • Database 91
  • Architecture 88
  • Data Structure 87
slide50

DML (Select)

  • SELECT (Having)
  • - Having clause specify retieval condition about each group
  • Example
  • Find average of each course which is enrolled more than
  • 10 students
  • SELECT Cno, AVG(Final)
  • FROMENROL
  • GROUP BY Cno
  • HAVING COUNT(*) >= 10 ;
  • Result
  • Cno
  • Database 91
  • Data Structure 87

We think one group

if Group by/Having

clause is omitted

slide51

DML (Select)

  • SELECT (Subquery)
  • - Having clause specify retieval condition about each group
  • Example
  • Find names of students who enroll the 'Database' course.
  • SELECT Sname
  • FROMSTUDENT
  • WHERE Sno IN
  • (SELECTSno
  • FROMENROL
  • WHERECno = 'Database' );

Result

Sname

Park J. K.

Kim S. Y.

Lee J. T.

slide52

DML (Select)

  • SELECT Sname
  • FROMSTUDENT
  • WHERE Sno IN (100, 200, 300) ;
  • SELECT STUDENT.Sname
  • FROMSTUDENT, ENROL
  • WHERE STUDENT.Sno = ENROL.Sno AND
  • ENROL.Cno = 'Database' ;
slide53

DML (Select)

  • Example
  • Find names of students who did not enroll the 'Database' course.
  • SELECT Sname
  • FROMSTUDENT
  • WHERE Sno NOT IN
  • (SELECTSno
  • FROMENROL
  • WHERECno = 'Database' );

Result

Sname

Choi

Ha

slide54

DML (Select)

  • Example
  • Find dept and names of students who have the same department
  • with 'Park K. S.'
  • SELECT Sname, Dept
  • FROMSTUDENT
  • WHERE Dept =
  • (SELECTDept
  • FROMSTUDENT
  • WHERESname = 'Park K. S.');
slide55

DML (Select)

  • SELECT (Like predicate)
  • - LIKE clause is search condition to specify character string
  • - 's_ _' stands for 3-string starting with 's'
  • - 's%' stands for a freestring starting with 's'
  • Example
  • Find Cno and course name starting with character 'D'.
  • SELECT Cno, Cname
  • FROMCOURSE
  • WHERE Cno LIKE'D%' ;

Result

Dno Cname

C123 Database

C230 Data Structure

C330 Data Communication

slide56

DML (Select)

  • SELECT (Exists)
  • - EXISTS quantifier check the result of subquery is true or false
  • - NOT EXISTS is negation of EXISTS quantifier
  • Example
  • Find Sname who have 'Database' and grade 'A'.
  • SELECT Sname
  • FROMSTUDENT
  • WHERE EXISTS (SELECT *
  • FROMENROL
  • WHERESno = STUDENT.Sno
  • AND Grade = 'A'
  • AND Cno = 'Database') ;
slide57

DML (Select)

  • Example
  • Find Sname who do not have 'Database' and grade 'A'.
  • SELECT Sname
  • FROMSTUDENT
  • WHERE NOT EXISTS (SELECT *
  • FROMENROL
  • WHERESno = STUDENT.Sno
  • AND Grade = 'A'
  • AND Cno = 'Database') ;

Result (NOT EXITS)

Sname

Kim

Choi

Yoon

Result (EXITS)

Sname

Lee

Park

slide58

DML (Select)

  • SELECT (Union)
  • - UNION add tuples (union set)
  • Example
  • List Sno who is junior and did enrol the 'Database'.
  • SELECT Sno
  • FROMSTUDENT
  • WHERE Year = 3
  • UNION
  • SELECT Sno
  • FROMENROL
  • WHERE Cno = 'Database' ;

Result (UNION)

Sno

100

200

300

slide59

DML (Insert)

  • INSERT DML have two types tuple insertion facilities
  • - Simple insert : one tuple insertion
  • - Query-dependent insert : multiple tuple insertion
  • INSERT INTO table-name [ (column-name {[, column-name] }*]
  • VALUES (column-list) ;
  • or
  • INSERT INTO table-name [ (column-name {[, column-name] }*]
  • SELECT statement ;
slide60

DML (Insert)

  • Simple insert
  • Example
  • Insert a new employee named 'Jones' with employee number 568
  • in Dept. 300.
  • INSERT INTO EMP(EMPno, Name, Dno)
  • VALUES (538, 'Kim', 300) ;
  • INSERT INTO EMP(EMPno, Name)
  • VALUES (538, 'Kim') ;
  • * Dno is filled null value.
slide61

DML (Insert)

  • Query-dependent insert
  • Example
  • Insert into CANDIDATE table all those employees whose tax
  • is greater than half their salary.
  • INSERT INTO CANDIDATE
  • SELECT EMPno, Name, Dno, Sal
  • FROM EMP
  • WHERE Tax > 0.5 *Sal ;
slide62

DML (Delete)

  • DELETE DML delete all the records or records specified
  • in where clause
  • DELETE
  • FROM table-name
  • [ WHEREsearch-condition ] ;
  • Example
  • Delete Sno = 100 in STUDENT table.
  • DELETE
  • FROM STUDENT
  • WHERESno = 100 ;
  • * Referential Integrity :
  • if some table have Sno column a foreign key, the records (100) must be deleted.
slide63

DML (Update)

  • UPDATE DML update all the records or records specified
  • in where clause
  • UPDATE table-name
  • SET column-name = arithmetic expression
  • {column-name = arithmetic expression }*
  • [ WHEREsearch-condition ] ;
  • Example
  • Give 10% salary for each employee in the Candidate table.
  • UPDATE EMP
  • SET Sal = Sal * 1.1
  • WHEREEMPno IN (SELECT EMPno
  • FROM CANDIDATE) ;
slide64

연습문제

1 key란 무엇이며, 어떤 종류가 있는가? Primary 키, candidate 키, Superkey,

Foreign 키 등의 특징을 구분하여 설명하라.

2. 릴레이션의 특성 4가지를 들고 설명하라.

3. 관계형 데이터 모델의 제약 사항으로 개체 무결성과 참조 무결성이 있다. 이들을

설명하라.

4. 관계형 데이터 모델은 크게 구조(structure), 무결성 제약(integrity constraint),

연산(operation)으로 구분하여 볼 수 있다. 연산은 다시 관계 대수와 관계 해석으로

이루어진다. 관계 대수 연산자 8개를 들고 실제 연산되는 예를 보여라.

5. SQL 문을 익히기 위해 텍스트에 나오는 시험 DB를 구축한 후, 다양한 질의를

SQL 문으로 만들어 실습해 보라. SQL문을 습득하면 DB를 마음대로 사용할 수 있다.

6. SQL에서 정의하는 테이블(table)과 관계형 데이터 모델의 릴레이션(relation)의

차이점을 설명하라.

slide65

연습문제(계속)

7 SQL에서 뷰의 정의는 무엇인가? 뷰를 갱신할 때 문제점은 무엇인가?

8. 커서란 무엇이며, 어떻게 사용되는가?

9. 다음과 같은 릴레이션이 있을 때 아래 질의문을 SQL로 표현하라.

학생(학번, 이름, 학년)

과목(과목번호, 과목이름, 교수)

등록(과목번호, 학번, 점수)

(1) 과목 ‘데이타베이스’를 수강하는 3학년 학생의 이름을 검색하라.

(2) 과목 ‘운영체제’를 삭제하라.

(3) ‘자료구조’ 과목을 삽입하라(과목번호는 CE312이다).

(4) 학번 9612345인 학생의 학년을 4로 변경하라.

slide66

서커스

쉼터

내가 십대였을 때의 일이다. 어느 날 나는 아버지와 함께 서커스 구경을 가기 위해

매표소 앞에 줄을 서 있었다. 표를 산 사람들이 차례로 서커스 장 안으로 들어가고,

마침내 매표소와 우리 가족 사이에는 한 가족만이 남았다. 그 가족은 무척 인상적

이었다. 열두 살 이하의 아이들이 무려 여덟 명이나 되는 대식구였다.

분명히 말할 수 있는 것은 그들은 결코 부자가 아니라는 사실이었다. 아이들은 둘씩

짝을 지어 부모 뒤에 손을 잡고 서 있었다. 그리고 그날 밤 구경하게 될 온갖 곡예들에

대해 흥분한 목소리로 이야기를 하고 있는 것으로 보아, 전에는 한번도 서커스 구경을

한적이 없다는 것을 알 수 있었다. 아이들의 아버지와 어머니는 맨 앞줄에 자랑스럽게

마주보며 서 있었다.

남자는 “어린이 표 여덟장과 어른표 두 장을 주시오”하고 목소리에 힘을 주어 말했다.

여직원이 입장료를 말했다. 남편을 고개를 떨구었다. 남자는 매표소 창구에 몸을 숙이고

다시 물었다. 그 남자는 그만큼의 돈을 갖고 있지 않은 게 분명했다.

이때였다. 상황을 지켜 보던 나의 아버지가 말없이 주머니에 손을 넣더니 20달러 짜리

지폐를 꺼내 바닥에 떨어뜨렸다. 그 다음 아버지는 몸을 굽혀 그것을 다시주워 들더니

앞에 서 있는 남자의 어깨를 두드리며 말했다.

“여보시오, 선생. 방금 당신의 호주머니에서 이것이 떨어졌오.”

slide67

“여보시오, 선생. 방금 당신의 호주머니에서 이것이 떨어졌소.”

남자는 무슨 영문인지 금방 알아차렸다.그는 결코 남의 적선을 요구하지 않았지만

절망적이고 당혹스런 그 상황에서 아버지가 내밀어 준 도움의 손길은 실로 큰 의미를

가진 것이었다.

남자는 아버지의 눈을 똑바로 쳐다보더니 아버지의 손을 잡았다. 그리고 20달러 짜리

지폐를 꼭 움켜잡으며 떨리는 목소리로 말했다.

“고맙소 선생. 이것은 나와 내 가족에게 정말로 큰 선물이 될 것이오.”

남자의 눈에서는 눈물이 글썽거렸다. 그들은 곧 표를 사 갖고 서커스 장 안으로 들어갔다.

나와 아버지는 차를 타고 집으로 동아와야 했다. 그 당시 우리집 역시 전혀 부자가

아니었던 것이다. 우리는 그날 밤 서커스 구경을 못했지만 마음은 결코 허전하지 않았다.

<“마음을 열어주는 101가지 이야기”중에서>