9/28/2023

Outlines

1. Database 2. SQL 3. Various Database

• Difference Between • Data Definition • Distributed Database
Database and File • Data Warehouse
• Data Manipulation
Database • Database Design
• DBMS (Database
• Other Related Techniques
Management System)

1 2

1 2

File System vs Database System Database Function

Data Sharing Function
• Overlapping of data items: Student number and name of the same student are recorded as
overlapping in two files (address file and age file).
It allows identical data to be provided to multiple users.
• Data inconsistency: When the name is modified in the address file but not in the age file, the
student with the same student number has a different name. Data Independence Function
It ensures that data modification does not affect the program

Data Maintenance Function

It ensures that data is always in the correct status (or there is no
contradiction).

Data fault countermeasures function

It allows any fault of a database to be quickly addressed.

Data security protection function

It ensures security of data by setting access rights, and so on.

3 4

3 4

Database Design: Data Model Logical Data Models

Conceptual data model
• Data model refers to modeling of • Hierarchical Model Network Model Relational Model
various relations of data in the real  It models relations of data without considering any
world for handling them in specific database. It defines what kind of data is handled. •Represent as a hierarchical • represents the relations of data as a • represents the relations of data in a
computers. structure where parent and child hierarchical structure where parent two-dimensional table.
are in a one-to-many relationship. and child are in a many-to-many • implemented as RDB (Relational
•implemented as HDB (Hierarchical relationship implemented as NDB DataBase).
Logical data model (external model)
DataBase). (Network DataBase).
 It logically models the relations of data considering a
specific database. It defines interrelations of data in
the database.

Physical data model (internal model)

 It physically models the relations of data considering a

specific database product. It defines the physical internal
structure of a database such as data type.
5 6

5 6

1
 9/28/2023

Relational Model Relational operations

Relational Data Model
• a data model that represents the relations of data in a
two-dimensional tabular form.
• The entire table is called “relation,” rows and columns
that constitute the table are called “tuples” and
“attributes (fields)” respectively.

7 8

7 8

Set Operations Manipulation of Data

Adding (i.e., inserting) new data (i.e., tuples) in a relation

Insert (i.e., table)

Changing (i.e., updating) the data (i.e., tuples) recorded

Update in a relation (i.e.,table)

Deleting the data (i.e., tuples) recorded in a relation

Delete
(i.e., table)

9 10

9 10

Conceptual Design of Databases Entity Relation Model

• the process of creating conceptual data models. E-R diagram
• Conduct data analysis • A technique, used in designing files or
• the meaning and relation of this data are analyzed databases, for expressing results It is an object that is managed and represented with
and summarized. • obtained by grasping the objects to be a rectangle.
• data items are standardized by using rules such as managed and data items. Entity
naming convention • The objects of management and analysis
• Metadata are recorded in the data dictionary for are referred to as entities, which are
users in DD/D (Data Dictionary/Directory). associated with one another by
ER It is a relation between entity and entity, and
• Conceptual Data Model is created as the result of data relationships. Model Relationship it is represented with a rhombus.
analysis. • The elements constituting entities and
• Entity Relationship model (E-R diagram) that represents relationships are called attributes.
the target content with two concepts of entity and Attribute
relationship. ! Elements It is a characteristic or property of an entity and
a relationship, and it is represented with an ellipse.

In E-R model, it is possible to represent cardinality (i.e., multiplicity) of the relationship.

11 12

11 12

2
 9/28/2023

Logical Design of Databases Normalization

• Logical database design is the process of transforming (or mapping) a • Next, data normalization is performed in order to design efficient table. Functional
conceptual schema of the application domain into a schema for the data model • dependency is the property where if a certain attribute is decided, other attributes is
underlying a particular DBMS, such as the relational or object-oriented data model. • uniquely decided.

Primary key Foreign key

• Iterative fields or derived fields (i.e., fields determined through calculation) are eliminated.
• It is a field (or a combination of fields) that • It is a field (or a combination of fields) for First
uniquely identifies each tuple (i.e., record) referencing a tuple (i.e., record) whose Normalization
• in a table. (Composite key made of multiple • values match the primary key of another table.
fields is also acceptable.) For primary key, there • For a foreign key, it is possible to define • When the primary key is a composite key, fields that are dependent on some of the
are constraints, such as non-NULL constraint referential constraint, which requires that the Second • fields constituting the primary key are split into a separate table. (This dependency
that does not allow NULL (null value) and unique primary key of the table to be referenced must • is called partial functional dependency.)
constraint that does not allow duplication of have a tuple (i.e., record) having the same value. Normalization
values in a table.
• Fields dependent on the fields other than the primary key are split into a separate table. (This
Third dependency is called transitive functional dependency.)
Normalization
13 14

13 14

Normalization Physical Design of Databases

• The process of creating physical data models in consideration of the database

product (e.g., database software) to be implemented.
• The most appropriate data type for each attribute is selected from the available data
types in the database
• Required disk capacity is
• Consider defining index (i.e., search key) for improving access efficiency.
• When the size of the database is large, the search efficiency can be significantly
increased by defining index for the attributes that are frequently used in search
Third operation.
Normal
Form

15 16

15 16

DBMS (Database Management Database Definition Function

System) • a function that defines schema (definition and description
Conceptual Data Model

concerning logical structure, storage structure, and physical

• A software that manages databases for effectively using the structure of databases).
databases.
• Schema is mostly defined by using the following three-schema
• DBA (Database Administrator) architecture. Logical Data Model
• The person who manages the databases by using software,
such as DBMS, is called the

Physical Data Model

17 18

17 18

3
 9/28/2023

Database Manipulation Function Dat abase Control Function

• a function that offers the usage environment of database language (e.g., SQL in the case of a relational • Database control function is a function for improving reliability and security of the data
database) that is used in definition and operation of data. recorded in the databases.
• The following are the execution methods of database manipulation language (e.g., SQL) provided by • Maintenance function
DBMS. • A function for maintaining integrity of data (i.e., data integrity).
Host language system
This is a method of executing SQL statements by using a high level language. Double Update
• 1) Program 1 references Data A (100) in the database.
Independent language system
This method independently executes SQL statements. It includes interactive SQL • 2) Program 2 references Data A (100) in the database.
method where SQL statements are entered from the command line and command • 3) Data A is updated with the content (60) that is modified in Program 1.
driven method where the registered SQL statements are called and executed with
• 4) Data A is updated with the content (80) that is modified in Program 2.
commands having parameters.
• → Updated content is overwritten, and update of Program 1 becomes invalid.

19 20

19 20

Dat abase Control Function

Dat abase Control Function
• Example of exclusive control (lock system)
• Exclusive Control
1) Program 1 references Data A (100) in the database.
• Shared lock → Apply exclusive lock to Data A.
S X
• This lock is mainly used when data is read. 2) Program 2 tries to reference Data A in the database. However, since Data A is
S Y N locked, Program 2 waits for it to be unlocked.
• In shared lock, only reading is permitted for
the users other than the user who applied the X N N 3) Data A is updated with the content (60) that is modified in Program 1.

lock. → Release the lock of Data A. (Data A is unlocked.)

4) After the release of the lock is confirmed, Program 2 references Data A (60).
• Exclusive lock
→ Apply exclusive lock to Data A.
• This lock is mainly used when data is updated.
5) Data A is updated with the content (40) that is modified in Program 2.
• Neither reading nor writing is permitted for →Release the lock of Data A. (Data A is unlocked.)
the users other than the user who applied the
lock.
21 22

21 22

Dat abase Control Function Dat abase Control Function

• Security protection function
• Deadlock Security protection function
• Security protection function is a function for • (3) Password setting
• Deadlock is the phenomenon where multiple programs maintaining security of data (i.e., data security). • This method uses a user ID and a password to check whether the
are simultaneously in the waiting state because of the • Critical data and highly confidential data are stored in user of the database has the right to use the database or not.
databases.
lock and their executions completely stop. (1) Encryption
• (4) Registration in journal file (log file)
Contents that are recorded in a database are • This method records the status of use (date, account, details of
encrypted. use) of the database and checks whether there has been any
(2) Access rights setting unauthorized use or not.
In this method, processes (i.e., accesses) are allowed
for the applicable database is defined in advance.
Detailed access rights can be set for each user.

23 24

23 24

4
 9/28/2023

Fa ilure rec over y f unc tion Failure recover y function

Two types of recovery processes
• a function that restores databases at the occurrence of a • Rollforward Rollback
failure. This recovery technique is mainly used for logical faults,
• This recovery technique is mainly used for physical such as transaction error.
• Failure recovery uses files, such as backup file where a failure of storage media. The database just after the disabling process is restored
database at a particular time is copied as it is, and journal to the status before the disabling process on the basis
file (i.e., log file) where an update process performed on • Contents of the backup file (or the checkpoint file)
of the information (also known as “undo” information)
are updated on the basis of information (also before the update of the journal file.
the database is recorded.
known as “redo” information) after the update of Rollback is also used in the sense of canceling the
• Journal records are created in the memory and are the journal file, in order to restore the contents as update process that has not been committed yet.
written in a file at the timing of commit (i.e., process of the database at the time the failure occurred.
finalizing the update process) which is performed when the
database update process for each transaction is completed.

25 26

25 26

Failure recover y function

SQL (Structured Query Language)
• Transaction management of database • A database language is a language used in defining and deleting databases and tables
• ACID Properties and searching and updating data.

1. Atomicity: The process completes by commit or rollback. • SQL is a database language for relational databases.
Create
2. Consistency: Contradiction (i.e., inconsistency of data) does not Data Definition Language Alter table
occur before and after the process. Drop
(DDL) - Defines and organizes
Grant
databases Revoke
3. Isolation: Multiple transactions do not mutually interfere.

4. Durability: Results after commit are continuously retained even Database Language
after the process. Select
Data Manipulation Language(DML)
Insert
-search, update, add, and delete
Update
data Delete

27 28

27 28

SQL : Data definition SQL : Data definition

• Data definition
• Definition of Tables • Definition of Tables
Defines the database, table (i.e., base table), view • CREATE TABLE statement is used for defining a • CREATE TABLE statement is used for defining a table.
(i.e., virtual table), and so on. table. • Column Constraint
• Definition of Database 1. PRIMARY KEY : Declaration of primary key (Primary key constraint: non-NULL constraint + unique
• CREATE DATABASE statement is used for defining database. CREATE TABLE name constraint)
(Column name 1, Data type 1, Column 2. FOREIGN KEY : Declaration of foreign key (referential constraint)
CREATE DATABASE name name 2, Data type 2, ... ) 3. NOT NULL : non-NULL constraint (NULL is not allowed as occurrence)
4. UNIQUE : unique constraint (Duplication of occurrence in the table is not allowed)
• Eg01: Define the database “PerformanceManagementDB”. 5. CHECK : check constraint (specifies the conditions of occurrence)

CREATE DATABASE PerformanceManagementDB

29 30

29 30

5
 9/28/2023

SQL : Data definition SQL : Data definition

• Definition of View
• ALTER TABLE statement CREATE VIEW Name AS SELECT StudentNumber, Name FROM
• A table that is virtually set from the real Student
ALTER TABLE name Details of redefinition tables and corresponds to external
schema.
Example: • CREATE VIEW statement is used for
defining a view.
ALTER TABLE Customers
ADD Email varchar(255);

• CREATE VIEW Name AS SELECT …

• Eg03: Extract StudentNumber and

Name from the “Student” table and
define the view “Name”.
31 32

31 32

SQL : Data definition SQL : Data definition

• Definition of Access Right • Eg04: Set the permission to refer and update the “Student” table in
• Access right is the right (i.e., processing privilege) for each user to Definition of Access Right the identifier
use a database. Setting access right is useful for data security • “EducationAffairsOffice”.
protection.
• GRANT statement is used for defining access right. GRANT SELECT, UPDATE ON Student TO
EducationAffairsOffice
• GRANT Privilege 1, Privilege 2, ∙∙∙ ON Table Name TO
Identifier
• REVOKE statement is used for canceling the defined (i.e., granted)
• REVOKE statement is used for canceling the defined (i.e., granted) permission.
permission.
REVOKE Privilege 1, Privilege 2, ••• ON Table name
REVOKE Privilege 1, Privilege 2, ••• ON Table name TO TO Identifier
Identifier

33 34

33 34

SQL : Data definition SQL : Data Manipulation

Reference Without Specifying Conditions
• Data Storage Data Manipulation Language (DML)
• the relational operation “Projection” that extracts the
• A process of inserting data in a table. • DML is a language system used to manipulate specified attributes.
databases by the user.
1) Storing data in units of tuples (i.e., records)
• INSERT statement is used for storing data in units of tuples (i.e., records).
• Structure of SELECT Statement
• INSERT INTO Table name VALUES (Data 1, Data 2, ∙∙∙)

• Eg05: Store tuple (K11, English I) in the “Subject” table. SELECT item 1, item 2, …
INSERT INTO Subject VALUES ('K11', 'English I') FROM table 1, table 2, …
WHERE condition
2) Using data storage program
• A data storage program is prepared in the host language system beforehand, or the data storage program that is
available in the utility program is used.

35 36

35 36

6
 9/28/2023

SQL : Data Manipulation SQL : Data Manipulation

• Reference Without Specifying Conditions • Reference With Specifying Conditions
• E.g06: Extract all columns from the “Student” table. • the relational operation “Selection” that extracts the tuples that satisfy the specified conditions.
SELECT Column name 1, Column name 2, ∙∙∙
SELECT * FROM Student
FROM Table name WHERE Extraction
conditions
• Eg07: Extract Gender from the “Student” table. (Projection).
• • Eg09: From the “Student” table, extract tuples where Gender is
SELECT Gender • 'Female' (Selection).
FROM Student SELECT * FROM Student WHERE
Gender='Female'
• Eg08: Extract Gender from the “Student” table after duplication
is eliminated. (Projection)
• Eg10: From the “Subject” table, extract subject names other than
SELECT DISTINCT Gender • the SubjectName 'Mathematics' (Selection, Projection).

FROM Student SELECT SubjectName FROM Subject WHERE

SubjectName<>'Mathematics'
37 38

37 38

SQL : Data Manipulation SQL : Data Manipulation

LIKE Operator Description
• Reference With Specifying • Reference With Specifying Conditions
Finds any values that start • Eg11: From the “Score” table, extract StudentNumber, SubjectNumber, and
WHERE CustomerName LIKE 'a%'
• Conditions with "a" Score where SubjectNumber contains '2' and Score is between 80 and 90.
Finds any values that end
WHERE CustomerName LIKE '%a' SELECT StudentNumber, SubjectNumber, Score
with "a"
• FROM Score
WHERE CustomerName LIKE Finds any values that have
'%or%' "or" in any position WHERE (SubjectNumber LIKE '%2%')
Finds any values that have AND (Score BETWEEN 80 AND 90)
WHERE CustomerName LIKE '_r%'
"r" in the second position
Finds any values that start • Eg12: From the “Score” table, extract StudentNumber, SubjectNumber, and
WHERE CustomerName LIKE 'a_%' with "a" and are at least 2 Score where SubjectNumber contains '2' or Score is between 80 and 90.
characters in length
SELECT StudentNumber, SubjectNumber, Score
Finds any values that start FROM Score
WHERE CustomerName LIKE 'a__%' with "a" and are at least 3 WHERE (SubjectNumber LIKE '%2%')
characters in length OR (Score BETWEEN 80 AND 90)
Finds any values that start
WHERE ContactName LIKE 'a%o'
with "a" and ends with "o"
39 40

39 40

SQL : Data Manipulation SQL : Data Manipulation

• Reference With Specifying Conditions • Reference With Specifying Conditions
• Eg11: From the “Score” table, among the tuples where the score is greater than 60, extract the tuples where the 2nd
digit as counted from the left edge ofStudentNumber is '7' or ExaminationDate is '20XX-10-21’. • Eg13: From the “Score” table, extract StudentNumber,
SubjectNumber, and Score where SubjectNumber contains '2' and
SELECT * FROM Score Score is between 80 and 90.
WHERE Score > 60 •
SELECT StudentNumber, SubjectNumber, Score
AND (StudentNumber LIKE '_7_ _' OR ExaminationDate =
'20XX-10-21') FROM Score
WHERE (SubjectNumber LIKE '%2%')
• Eg12: From the “Score” table, extract StudentNumber, SubjectNumber, and Score where SubjectNumber contains '2' or AND (Score BETWEEN 80 AND 90)
Score is between 80 and 90.

SELECT StudentNumber, SubjectNumber, Score

FROM Score
WHERE (SubjectNumber LIKE '%2%')
OR (Score BETWEEN 80 AND 90)

41 42

41 42

7
 9/28/2023

SQL : Data Manipulation

SQL : Data Manipulation
• Reference With Specifying Conditions
• Projection and Selection
SELECT * FROM Score
WHERE Score > 60
AND (StudentNumber LIKE '_7_ _' OR ExaminationDate = '20XX-10-21')

43 44

43 44

SQL : Data Manipulation SQL : Data Manipulation

• Grouping of Data
• Grouping of Data
• Eg14: From the “Score” table, determine and
• handling the tuples where a certain attribute has the same value in a consolidated manner, and functions used for this are extract average score of each subject.
referred to as set functions (or aggregate functions).
SELECT SubjectNumber, AVG(Score) FROM Score
• GROUP BY clause is used for grouping of data.
GROUP BY SubjectNumber
SELECT Extraction items • Eg15: From the “Score” table, determine and
FROM Table name GROUP BY Grouping extract the number of subjects for which the
column name respective student took an examination.
SELECT StudentNumber, COUNT(*) AS NumberOfSubjects FROM Score
SELECT column1, function_name(column2) GROUP BY StudentNumber
FROM table_name • Eg16: From the “Score” table, determine and
WHERE condition GROUP BY column1, column2 extract the number of days for which the
respective student took an examination.
ORDER BY column1, column2
SELECT StudentNumber, COUNT(DISTINCT ExaminationDate) AS NumberOfDays
FROM Score GROUP BY StudentNumber

45 46

45 46

SQL : Data Manipulation SQL : Data Manipulation

• Grouping of Data • Sorting of Data
• Eg17: From the “Score” table, extract students having Rearranging the extraction results in the specified order of a particular attribute.
TotalScore (total of score) of 150 or more.
• ORDER BY clause is used for sorting data.
SELECT column-list
SELECT StudentNumber, SUM(Score) AS TotalScore FROM table_name
FROM Score [WHERE condition]
GROUP BY StudentNumber HAVING SUM(Score) >= 150 [ORDER BY column1, column2, .. columnN] [ASC | DESC];

• Two types of Sorting Orders

47 48

47 48

8
 9/28/2023

SQL : Data Manipulation SQL : Data Manipulation

• Sorting of Data
Eg18: Sort and extract the “Score” table in the descending order of score. • Joining the Tables
• Joining the tables means combining two or more tables into one
SELECT * FROM Score ORDER BY Score DESC table.
• Cross Join or Cartesian Product
• The CROSS JOIN is used to generate a paired combination of each
• Eg19: From the “Score” table, determine the highest score for each row of the first table with each row of the second table. This join
student and extract in the ascending order. type is also known as cartesian join.

SELECT StudentNumber, MAX(Score) FROM Score

GROUP BY StudentNumber ORDER BY 2

49 50

49 50

SQL : Data Manipulation SQL : Data Manipulation

• Joining the Tables
• Joining the Tables
• Joining the tables means combining two or more tables into one table.
• Natural Join • Eg20: From the “Score” table and the “Subject” table, extract StudentNumber,
Natural Join joins two tables based on same attribute name and datatypes. The resulting table will contain all the SubjectName for which the students took an examination, and Score.
attributes of both the tables but only one copy of each common column.
SELECT StudentNumber, SubjectName, Score FROM
• Eg20: From the “Score” table and the “Subject” table, extract StudentNumber, SubjectName for which the students
took an examination, and Score. Score, Subject
WHERE Score.SubjectNumber = Subject.SubjectNumber
SELECT StudentNumber, SubjectName, Score
FROM Score, Subject
SELECT StudentNumber, SubjectName, Score
WHERE Score.SubjectNumber = Subject.SubjectNumber
FROM Score X, Subject Y
WHERE X.SubjectNumber = Y.SubjectNumber

51 52

51 52

SQL : Data Manipulation SQL : Data Manipulation

• Joining the Tables • Joining the Tables
• SQL Join statement • Inner Join : selects all rows from both the tables as long as the condition is satisfied.
• combine data or rows from two or more tables based on a common field between them. • Syntax:

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 INNER JOIN table2
ON table1.matching_column = table2.matching_column;

SELECT Student.name.score.subjectNumber,
FROM Student INNER JOIN score
ON Student.studentNumber = score.studentNumber;

53 Ref: https://www.geeksforgeeks.org/sql-join-set-1-inner-left-right-and-full-joins/ 54

53 54

9
 9/28/2023

SQL : Data Manipulation SQL : Data Manipulation

• Joining the Tables • Joining the Tables
• Left Inner Join : This join returns all the rows of the table on the left side of the join and matches rows for the table • Right Inner Join : This join returns all the rows of the table on the right side of the join and matching rows for the
on the right side of the join. table on the left side of the join.
• Syntax: • Syntax:
SELECT table1.column1,table1.column2,table2.column1,.... FROM
table1 LEFT JOIN table2 SELECT table1.column1,table1.column2,table2.column1,....
ON table1.matching_column = table2.matching_column; FROM table1 RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;
SELECT Student.name,score.score
FROM Student LEFT JOIN score SELECT Student.name,score.score
ON Student.studentNumber=score.studentNumber; FROM Student RIGHT JOIN score
ON Student.studentNumber=score.studentNumber;

Ref: https://www.geeksforgeeks.org/sql-join-set-1-inner-left-right-and-full-joins/
55 Ref: https://www.geeksforgeeks.org/sql-join-set-1-inner-left-right-and-full-joins/
56

55 56

SQL : Data Manipulation SQL : Data Manipulation

• Joining the Tables • Joining the Tables
• Outer joins are joins that return matched values and unmatched values from either or both tables. • Right Outer Join : returns all record from right table and matching records from the left table.
• Left Outer Join : returns only unmatched rows from the left table, as well as matched rows in both tables. • Syntax:
• Syntax:
SELECT table1.column1,table1.column2,table2.column1,.... FROM
table1 RIGHT JOIN table2
SELECT table1.column1,table1.column2,table2.column1,.... FROM
ON table1.matching_column = table2.matching_column;
table1 LEFT JOIN table2
ON table1.matching_column = table2.matching_column;
SELECT Student.name,score.score
FROM Student LEFT JOIN score
SELECT Student.name,score.score ON Student.studentNumber=score.studentNumber;
FROM Student LEFT JOIN score
ON Student.studentNumber=score.studentNumber;

Ref: https://www.geeksforgeeks.org/sql-join-set-1-inner-left-right-and-full-joins/
57 Ref: https://www.geeksforgeeks.org/sql-join-set-1-inner-left-right-and-full-joins/
58

57 58

SQL : Data Manipulation SQL : Data Manipulation

• Joining the Tables • Joining the Tables
• Full Outer Join : The full outer Join keyword returns all records when there is a match in left or right table records. • Three Join Operations
• Syntax:
• CROSS JOIN (Cross join)
SELECT table1.column1,table1.column2,table2.column1,.... FROM • It specifies direct product of multiple tables
table1 FULL OUTER JOIN table2
ON table1.matching_column = table2.matching_column;
• INNER JOIN (Inner join)
• It specifies equijoin of tables with joining conditions.
• OUTER JOIN (Outer join)
SELECT Student.name,score.score • It extracts all tuples of the priority table even if the joining conditions are not
FROM Student FULL OUTER JOIN score met.
ON Student.studentNumber=score.studentNumber;
• LEFT OUTER JOIN : Table on the left is the priority table.
• RIGHT OUTER JOIN: Table on the right is the priority table.

59 60

59 60

10
 9/28/2023

SQL : Data Manipulation SQL : Data Manipulation

• Subreference (Subquery)
• Subreference (Subquery) • Subreference (Subquery)
Correlation subquery (subquery statement that cannot be
• Sub reference (subquery) refers to searching for another table on the basis Subquery statement that can be independently executed
executed
of the search results of a certain table. • Eg21: From the “Student” table and the “Score” table,
• Correlation subquery uses items of the table in the
• Two Types of Subquery extract the names where
main query statement as a condition, and therefore,
• ExaminationDate is '20XX-10-20'. this subquery statement cannot be executed
(1) Subquery statement that can be independently executed
SELECT Name FROM Student independently.
(2) Correlation subquery (subquery statement that cannot be executed WHERE StudentNumber IN • Eg22: From the “Student” table and the “Score”
independently) ( SELECT StudentNumber FROM Score table, extract the names where ExaminationDate is
WHERE ExaminationDate = '20XX-10-20' ) '20XX-10-20'.

SELECT Name FROM Student X

WHERE EXISTS ( SELECT * FROM Score Y
WHERE X.StudentNumber = Y.StudentNumber
AND ExaminationDate = '20XX-10-20' )

61 62

61 62

SQL : Data Manipulation SQL : Data Manipulation

• Other Methods of Using SQL • Other Methods of Using SQL
Other Data Manipulation Languages • Cursor
• INSERT statement: This statement is used when a tuple is inserted (i.e., added) into the table. • Cursor is used when data in the database is used in the host
language system (i.e., embedded SQL).
INSERT INTO Table name VALUES ( Data 1, Data 2, ••• )
• UPDATE statement: This statement is used when the data in the table is updated (i.e., changed).
UPDATE Table name SET Column name = Updated
value WHERE Update specification condition

• DELETE statement: This statement is used when a tuple is deleted from the table.

DELETE FROM Table name WHERE Delete specification

condition

63 64

63 64

Distributed database Distributed database

• Two-phase commitment (Two-phase commit)

• a technology that handles databases distributed at multiple sites as if they were one database.
• A commitment control method that executes the update process in the distributed database after the process
• Transparency
is divided into two phases.
• Users can use the database without becoming aware of which database they are accessing by using the • It is suitable when an instantaneous update is required.
RDA (Remote Database Access) system.
• The control method that instructs COMMIT or ROLLBACK without checking whether the update process
can be performed or not is called one-phase commitment.
Problems
security management 1) 1st phase
consistency of data • check the possibility of update process for all databases.
Duplication
2) 2nd phase
• Finalize (commit) if all databases can be updated (i.e., if all databases respond with a positive
responses), and cancel (i.e., rollback) if even only one database cannot be updated (i.e., if even only
one database responds with a negative response.
65 66

65 66

11
 9/28/2023

Distributed database Distributed

database • Replication
• Distributed Two-phase Commit Phase 2: Commit/Abort Phase
• This method places a copy (i.e., replica) of the original database
•After the controlling site has received “Ready” message from as the distributed database and reflects the update done in the
• The steps performed in the two phases are as follows −
all the slaves − original database into the replicated database at regular
• Phase 1: Prepare Phase • The controlling site sends a “Global Commit” message intervals (or specified times). It is suitable when an
to the slaves. instantaneous update is not required.
• After each slave has locally completed its transaction, it sends
a “DONE” message to the controlling site. When the • The slaves apply the transaction and send a “Commit
controlling site has received “DONE” message from all slaves, ACK” message to the controlling site.
it sends a “Prepare” message to the slaves. • When the controlling site receives “Commit ACK”
message from all the slaves, it considers the transaction
• The slaves vote on whether they still want to commit or not. as committed.
If a slave wants to commit, it sends a “Ready” message.
•After the controlling site has received the first “Not Ready”
• A slave that does not want to commit sends a “Not Ready” message from any slave −
message. This may happen when the slave has conflicting • The controlling site sends a “Global Abort” message to
concurrent transactions or there is a timeout. the slaves.
• The slaves abort the transaction and send a “Abort
ACK” message to the controlling site.
• When the controlling site receives “Abort ACK”
message from all the slaves, it considers the transaction
as aborted. 67 68

67 68

D a t a Wa r e h o u s e Data Mining
• Data mining is the method to analyze data or regularity required for the management by using mathematical and
• a company-wide integrated information system (multi-dimensional statistical techniques such as OLAP.
database) that expands the functions of databases and extracts the
required decision-making information for the business strategy of the • Star schema
• It is a schema of a database for analysis. It places analysis values in a radial manner focusing on the target of
company. analysis. Creating index for implementing star schema is one of the preparations required for data mining.
ETL (Extract/Transform/Load) • Cluster analysis
It refers to extracting the raw data stored in the mission critical system, processing the raw data into an easy- • It is an analytical technique that the similarity of data to be analyzed is quantitatively (e.g., by using distance
to-use format with tasks such as name identification and standardization, and exporting it to a data warehouse. or extent of similarity) determined in order to group data.
It is also software that supports this series of processes. • Dendrogram is used for showing the results of analysis.
Data Cleansing (Data cleaning)
It refers to removing duplication and inconsistency of notations from the database. It is used in database • This series of data management/analysis work from building a data warehouse to data mining is also called data
optimization and ETL processing. administration.

69 70

69 70

OLAP (Online Analytical

Data Mart Processing )
• OLAP is the concept of analytical application in which the end user
discovers problems and solutions by directly searching and
• A data mart is a database which stores data obtained organizing a database; the goal is to achieve quick data access and
from a data warehouse. to provide a function for easy analysis.
• The data stored in a data mart, is selected and •
summarized according to the purposes of a specific
user group.
• Whereas a data warehouse contains information for
the entire company, a data mart has a relatively small
amount of data tailored for the target users.

71 72

71 72

12
 9/28/2023

O LT P ( O n l i n e T r a n s a c t i o n Informational dat abases vs

Process) Operational databases
• OLTP is the processing mode in which messages are sent to the
host computer from multiple terminals connected online to the
host computer, which, according to the message received, in
turn performs the process including access to a series of
databases and returns the process results immediately to the
terminals.
•
• Databases used by OLTP are called business databases or,
sometimes, operational databases.

73 74

73 74

IRDS ( In formation Resou rce Special Database

Dictionar y System)
• Multimedia database

• IRDS is the system that registers and manages metadata, such as Commercial database • It is a database that can handle information (e.g., images, audio) in addition
attribute, meaning, and storage location of data, in DD/D (Data • It is a database that contains independently collected to characters and numeric values. It is very hard for users to use the
information and that is offered to third parties on a database by being aware of various pieces of information such as images
Dictionary). chargeable basis for generating profits. and audio, and therefore, the concept of OODB (Object-Oriented
DataBase) is used in most of the cases.
• It is also used as a repository that manages source code or such
other data, in an integrated manner in software development and OODB (Object-Oriented Database) • Hypertext database

maintenance. • It is a database that is managed with objects where data • It is a network structured database that manages hypertext (e.g., a
and processing (i.e., procedures) are integrated (i.e., document that can freely search/display the relevant information by using
encapsulated). Users can process the data by simply keywords) used in the Internet.
giving specific instructions (i.e., messages).
• XML database
• It is a database that can manage the document structure (e.g., tags) of XML
as it is. This database uses the features of XML and has excellent flexibility
and expandability.

75 76

75 76

Database integration techniques

Data mapping

• It refers to creating a table (i.e., data map) that correlates

data between different applications. It is used for
correlating a database of different DBMSs.

• JDBC ( Java DataBase Connectivity)

• This API is used for accessing databases from Java. It

Old Question Practice .
allows the development of a highly general-purpose
program that does not depend on DBMS.

77 78

77 78

13