Өгөгдлийн сангийн үндэс

Б.Наранчимэг
Мэдээлэл, компьютерийн ухааны тэнхим
ХШУИС, МУИС
naranchimeg@[Link]
Хичээлийн тухай
• Хичээлийн нэр • Өмнөх хичээл
• Өгөгдлийн сангийн үндэс (Database • Байхгүй
Fundamentals)
• CSII202
• Залгамж хичээл
• 3 кредит • Өгөгдлийн сангийн програмчлал
• Өгөгдлийн сангийн систем
• Судлагдахуун
• Өгөгдлийн сан • Зэрэг үзэхэд тохиромжтой хичээл
• Мэргэжлийн суурийн бусад хичээлүүд
• Лекц
• 2 цаг (16 долоо хоног) • Дүн
• Явц, идэвхи – 40%
• Семинар
• Явцын шалгалт – 10%
• 3 цаг (16 долоо хоног)
• Бие даалт, төсөл - 10%
• Эцсийн шалгалт - 40%
Overview
• What is database?
• A model of reality (Model of real world enterprise )
• A very large, integrated collection of data.
• What is Database Management System (DBMS)?
• A tool to make the model
• A set of tools for storing, searching and managing this data
Data vs. Information
Easy to understand
Database
• Efficient data management typically requires the use of a computer
database. A database is a shared, integrated computer structure that
stores a collection of:
• End-user data, that is, raw facts of interest to the end user.
• Metadata, or data about data, through which the end-user data are
integrated and managed.

• A database management system (DBMS) is a collection of programs

that manages the database structure and controls access to the data
stored in the database.
Databases
• Web indexes • Train timetables
• Library catalogues • Airline bookings
• Medical records • Credit card details
• Bank accounts • Student records
• Stock control • Customer histories
• Personnel systems • Stock market prices
• Product catalogues • Discussion boards
• Telephone directories • and so on…
DBMS

The DBMS manages the interaction between the end user and the database
Database and DBMS
Example of Database Application
• Suppose we are building a system to store the information about
student’s course:
• students
• courses
• enrolled
• who takes what
Can we do it without a DBMS ?
• Sure we can! Start by storing the data in files:

[Link] [Link] [Link]

• Now write C or Java programs to implement specific tasks

11
 Doing it without a DBMS...
• Enroll “Б.Батболд” in “Өгөгдлийн сангийн үндэс”:

Write a C program to do the following:

Read ‘[Link]’
Read ‘[Link]’
Read ‘[Link]’
Find the record “Б.Батболд”
Find the record “Өгөгдлийн сангийн үндэс”
Find & Update the record “15B1SEAS0001” took “CSII202”
Write ‘[Link]’
Problems without an DBMS...
Read ‘[Link]’
• System crashes: Read ‘[Link]’
Read ‘[Link]’
Find the record “Б.Батболд”
Find the record “Өгөгдлийн сангийн үндэс”
CRASH ! Find & Update the record “15B1SEAS0001” took “CSII202”
Write ‘[Link]’

• What is the problem ?

• Large data sets (say 50GB)

• What is the problem ?
• Simultaneous access by many users
• What is the problem?
DBMS vs. Файл систем
DBMS Файл систем
Олон хэрэглэгч хандах боломжтойОлон хэрэглэгчийн хандалтыг
дэмжихгүй
Жижиг, том хэмжээний бизнесийн Жижиг хэмжээний бизнесийн хэрэгцээг
хэрэгцээг бүрэн хангаж чадна хангана
Илүүдэл (redundancy)-ийг арилгаж Илүүдэл болон бүрэн бүтэн байдлыг
бүрэн бүтэн байдал (integrity)-ыг хангах хангах асуудлыг шийдэхэд хэцүү
боломжтой

Үнэтэй Хямд
Хэцүү үйл ажиллагааг хэрэгжүүлэхэд Хэцүү үйл ажиллагааг хэрэгжүүлэх
хялбар боломжгүй
Database Management System Facility

• Data definition language (DDL)

• Data manipulation language (DML)
• Structured query language (SQL)
• Security system
• Integrity system
• Concurrency control system
• Backup & recovery system
• View mechanism
Limitations of DBMS

• Complexity
• Size
• Cost
• Software
• Hardware
• Performance
• Vulnerability
DBMS Environment
• Hardware
• Client-server architecture
• Software
• dbms, os, network, application
• Data
• Schema, subschema, table, attribute
• People
• Data administrator & database administrator
• Database designer: logical & physical
• Application programmer
• End-user: naive & sophisticated
• Procedure
• Start, stop, log on, log off, back up, recovery
DBMS системийн орчин
Data modeling
• A data model is a collection of concepts for describing data.
• Data model
• Data structures
• Integrity constraints
• Operations
• Data modeling
• ER diagram of EER diagram
• Relational Model
Relational model - Data structures
Branch
branchNo Street City Postcode
char(4) varchar(25) varchar(15) varchar(8)
B005 22 Deer Rd London SW1 4EH

B007 16 Argyll St Aberdeen AB2 3SU

B003 163 Main St Glasgow G11 9QX

B004 32 Manse Rd Bristol BS99 1NZ

B002 56 Clover Dr London NQ10 6EU

 Relational model - Data structures
Rows
Table name

Column name
Branch
Schema
Data type
branchNo Street City Postcode
char(4) varchar(25) varchar(15) varchar(8)
B005 22 Deer Rd London SW1 4EH
Rows B007 16 Argyll St Aberdeen AB2 3SU

B003 163 Main St Glasgow G11 9QX Cardinality

B004 32 Manse Rd Bristol BS99 1NZ

B002 56 Clover Dr London NQ10 6EU

Degree
 Relational model - Data structures
Rows
Table name

Column name
Branch
Schema
Data type
branchNo Street City Postcode
char(4) varchar(25) varchar(15) varchar(8) Stable over time
B005 22 Deer Rd London SW1 4EH
Rows B007 16 Argyll St Aberdeen AB2 3SU

B003 163 Main St Glasgow G11 9QX Cardinality

B004 32 Manse Rd Bristol BS99 1NZ
Dynamic, changes
B002 56 Clover Dr London NQ10 6EU over time

Degree
Relational model: Constraints
• Constraints expresses rules that cannot be expressed by the data
structures alone:
• BranchNo must be unique
• BranchNo are not allowed to be NULL
• PostCode must be start alphabet Branch
• Cities must be in UK branchNo Street City Postcode
char(4) varchar(25) varchar(15) varchar(8)

B005 22 Deer Rd London SW1 4EH

B007 16 Argyll St Aberdeen AB2 3SU
B003 163 Main St Glasgow G11 9QX
B004 32 Manse Rd Bristol BS99 1NZ
B002 56 Clover Dr London NQ10 6EU
 Data model - Operations
• Operations support change and retrieval of data

Insert into Branch (‘B008’, ‘110 Red St’, ‘Glasgow’, ‘G10 6QX’ );

Branch
branchNo Street City Postcode
Select branchNo, Street from char(4) varchar(25) varchar(15) varchar(8)
Branch where City=‘London’
B005 22 Deer Rd London SW1 4EH
B007 16 Argyll St Aberdeen AB2 3SU
B003 163 Main St Glasgow G11 9QX
B004 32 Manse Rd Bristol BS99 1NZ
B002 56 Clover Dr London NQ10 6EU
Keys and identifiers
• Keys are uniqueness constraints
Branch
branchNo Street City Postcode
char(4) varchar(25) varchar(15) varchar(8)
B005 22 Deer Rd London SW1 4EH

B007 16 Argyll St Aberdeen AB2 3SU

B003 163 Main St Glasgow G11 9QX

B004 32 Manse Rd Bristol BS99 1NZ

B002 56 Clover Dr London NQ10 6EU

Integrity and Consistency
• Integrity: does the database reflect reality well?
• Consistency: is the database without internal conflicts?

Branch BranchAddress
branchNo Street City Postcode branchNo Address

B005 22 Deer Rd London SW1 4EH B005 22 Deer Rd, London

B007 16 Argyll St Aberdeen AB2 3SU B007 16 Argyll St, Bristol
B003 163 Main St Glasgow G11 9QX B003 163 Main St, Glasgow
B004 32 Manse Rd Bristol BS99 1NZ B004 32 Manse Rd, Bristol
B002 56 Clover Dr London NQ10 6EU B002 56 Clover Dr, Glasgow
Simple example
• Students takes Courses
• Student (Id, Lname, Fname, Phone_number, date_of_birth, age)
• Courses (Id, Name, Credit)
E-R diagram

LName Sid Cid Name

1..n 1..n
FName Student Takes Course

PhoneNumber Age Credit

Date_of_birth
Relation
Student Phone_number
Sid LName FName Date_Of_Birth
15b1seas0001 Бат Болд 1990-01-24 Sid Phone

15b1seas0002 Тунгалаг Болор 1991-02-23 15b1seas0001 9978….

15b1seas0001 8885….
15b1seas0003 Санчир Туяа 1990-07-08 ….
….

Takes Course
Sid Cid Cid Name Credit
15b1seas0001 ICSI302 ICSI302 Database programming 3
15b1seas0002 ICSI302 CSII202 Fundamentals of DB 3
15b1seas0003 CSII202 …
SQL?
• Create ?
• Insert ?
• Update ?
• Delete ?
Create
• CREATE TABLE Student(
Sid CHAR(12) PRIMARY KEY NOT NULL,
LName varchar(50),
FName varchar(50),
Date_Of_Birth Date
)…
Insert
• INSERT INTO student
VALUES(’15b1seas0001’, ‘Бат’, ‘Болор’, ‘1996-01-24’)
…
Update?
• UPDATE student
SET Fname = ‘Болд’ WHERE Sid = ’15b1seas0001’

• DELETE from student

WHERE Sid = ’15b1seas0001’
Query
• Search all courses that “Болд” takes
• Who enrolled which course ordered by student Fname
Search all courses that “Болд” takes
• SELECT [Link], [Link]
FROM Student S, Takes T, Course C
WHERE [Link]=‘Болд’ and
[Link] = [Link] and [Link] = [Link]
Who enrolled which course ordered by
student Fname
• SELECT [Link], [Link]
FROM Student S, Takes T, Course C
WHERE [Link] = [Link] and [Link] = [Link]
ORDERED BY [Link]
Topics
• Удиртгал
• Өгөгдөл хадгалах, боловсруулах арга технологи
• Файл систем vs. Өгөгдлийн сан
• Өгөгдлийн сан удирдах систем
• Холбоост өгөгдлийн сан
• Өгөгдөл загварчлах арга
• Холбоост алгебр
• Өгөгдлийн сангийн хэл
• SQL
• DDL
• DML
Topics
• Өгөгдлийн сангийн дизайн
• Мэдээллийн систем
• Систем хөгжүүлэх амьдралын цикл
• Өгөгдлийн сангийн системийн амьдралын цикл
• Нэгж хоорондын харилцан хамаарлын загварчлал
• ERD
• Нормчлох
• Өгөгдлийг давхардуулан хадгалахаас үүдэн гарах асуудлууд, шийдэл
• Өгөгдлийн сангийн дизайн ба хэрэгжүүлэлт
• Бодит жишээн дээр
Topics
• Өгөгдлийн сангийн аюулгүй байдал
• Өгөгдлийн санг зохион байгуулах технологийн шийдэл,
аргачлалууд
• Өгөгдлийн сангийн хэл, түүний нэмэлт боломжууд
Books
• Thomas Connoly and Carolyn Begg, Database Systems: A Practical
Approach to Design, Implementation and Management,©Pearson
Education Limited2005, 1374p.
• Ramez Elmasri, Fundamentals of Database systems

• Coronel/Morris/Rob, Database systems: Design, Implementation and

Management
• Abraham Silberschatz, Henry [Link], [Link], Database
systems concepts
Summary
• Database
• Database management system
• Data modeling
• Topics
Thank you!
Any questions?