DBMS Take IT easy with Christy

• DBMS
• Database Management System

• EDBMS
• Electronic Database Management System
• RDBMS
• Relational Database Management System
• ERDBMS
• Electronic Relational Database Management System

© www.olus.lk. All rights reserved.

DATABASE? Take IT easy with Christy
• What are data?
The raw basic facts about aspects.

• What is information?
The processed data
which are meaningful to the users, to make decisions.

• What is a Database?
It is a collection of organized data or information
typically arranged and stored in paper media or electronic media
In order to filter and access (taking back) quickly and easily.
© www.olus.lk. All rights reserved.
DBMS Take IT easy with Christy
A database is an organized collection of structured information, or data,
typically stored electronically in a computer system.

A database is usually controlled by a Database Management System.

© www.olus.lk. All rights reserved.
Structured Data VS Unstructured Data Take IT easy with Christy

© www.olus.lk. All rights reserved.

Key points of Leaning RDBMS Take IT easy with Christy

* What is DBMS?
Things to remember…
* The differences between Manual and Electronic DBMS
* Advantages and disadvantages
- Content

* Types of DBMS
* Data Groups
* ERD – Entity Related Diagram
* Constrains – A set of rules used in RDBMS
* Key concepts

© www.olus.lk. All rights reserved.

Key points of Leaning RDBMS Take IT easy with Christy

* Relationships
Things to remember…
* Software for ERDBMS
* Microsoft Access – Interface, Extension Names
- Content

* Objects – Tables, Forms, Queries, Reports

* SQL – Structured Query Language
*
*
*

© www.olus.lk. All rights reserved.

Manual Databases - (Paper Media) Take IT easy with Christy

Text Books, News Papers, Magazines

Time Tables, Date Calendars , Periodic Tables

Dictionaries and Encyclopedias

Telephone Directories – (Yellow & Rainbow Pages)

Students Registers, Record Books

Prime Entry Books - Accounting

Bank Statements, Passbooks

© www.olus.lk. All rights reserved.
Electronic Databases Take IT easy with Christy

Smart Phones

Computer System – Operating System

Software Systems

Internet

Social Medias

Online Ticketing, Booking and Banking Systems.

Data Servers
© www.olus.lk. All rights reserved.
Drawbacks of Manual Databases Take IT easy with Christy

* Needs More Space

* Difficult in sharing data and information.
* Less Security
* Difficult to update - (Edit, Delete)
* Integrity Problem
* Concurrent Access Problem
* Atomicity Problem
* Difficult in Data Searching
* Data Redundancy is high
© www.olus.lk. All rights reserved.
Differences between Manual & Electronic Databases Take IT easy with Christy

Manual Electronic

• Less Efficient • More Efficient

• Less Accurate • More Accurate
• Less Credible • More Credible
• Difficult to Analyze Data • Easy to Analyze Data
• Difficult to Update, Delete • Easy to Update and Delete
• Needs more space • More data in small space.
• More man power needed • Less man power needed

© www.olus.lk. All rights reserved.

Advantages & Features of Electronic Databases Take IT easy with Christy

* Minimal Data Redundancy

* Data Consistency
* Increases Efficiency
* Increases Accuracy
* Increases Validity
* Easy to obtain copies
* High Security – Password, Data Encryption and Decryption Methods

© www.olus.lk. All rights reserved.

Terminology Take IT easy with Christy
Entity / Relation (Table)
Attributes – Fields (Columns)
Student
ST_ID F_Name DOB Gender Medium Contact_No

Records
CIS0001 Mark 93/10/09 M English 076775232
Tuples (Rows)

CIS0001 Natasha 92/07/04 F English 071832815

Attribute
Field
TUPLE (ROW) (Column)

* Degree : Total number of Attributes

© www.olus.lk. All rights reserved. * Cardinality : Total number of Tuples

Data Groups Take IT easy with Christy

Teachers

Inventory Students

Coaches Subjects

School
Non-
Academics ECA
Staff

School
Classrooms
Services

Houses

© www.olus.lk. All rights reserved.

Entity Related Diagrams & Constraints Take IT easy with Christy
* Constraints : A set of rules followed in DBMS

* Ellipse
First_Name Last_Name * Singular Noun
Attributes * Name: Title Case, No_Spaces
* First_Name

* Double Lines
* Multi Valued
Name * Rectangle
* Contact_No * Singular Noun
Entity -Table * Name: Title Case, No_Spaces
* Student

DOB
* Prime Attribute
* Primary Key
Age * Dotted Lines St_ID * Underline it
* Derived Data * St_ID
* Marks - Grade
© www.olus.lk. All rights reserved.
ER Diagrames Take IT easy with Christy

Add_No
NIC F_Name

Contact_No L_Name

Address
Student DOB

Medium Gender
Religion

© www.olus.lk. All rights reserved.

Entity Take IT easy with Christy

Student
Add_No F_Name L_Name DOB Gender Religion Medium Address City Contact_No

© www.olus.lk. All rights reserved.

Key Constraints – Restrictions Take IT easy with Christy

© www.olus.lk. All rights reserved.

Key Constraints Take IT easy with Christy

Candidate Key

Super Key

Primary Key

Foreign Key

Composite Key / Compound Key

© www.olus.lk. All rights reserved.

Primary Key Take IT easy with Christy

A term related to ERDBMS.

The most important key attribute in an entity

A primary key is a field in a table
which uniquely identifies each row/record
in a database table.

It cannot be null, Data Must be available,

can be customized.

e.g:
Staff_ID, Student_ID, Item_ID, Book_ID

© www.olus.lk. All rights reserved.

Candidate Key Take IT easy with Christy

A term related to ERDBMS.

By definition, a candidate key is a minimal

super key.

It is a set of attributes that uniquely identify a

row, record.

e.g.:

NIC, Passport_No, EPF, ETF, Phone_No, BC_No

© www.olus.lk. All rights reserved.

ER Diagrames Take IT easy with Christy
* CK – Candidate Key * PK Title
Ethnicity Staff_ID F_Name

* SK – Super Key * SK * CK
Photo L_Name
* PK – Primary Key

Salary DOB

Gender
Maritual_Status
Academic_Staff
Religion
ETF
* CK
Medium
EPF
* CK Address
NIC Contact_No
Email * CK * CK
* CK City
© www.olus.lk. All rights reserved.
ER Diagrams Take IT easy with Christy

Class_ID
Class_Name

Class Class_
Teacher
Location

Asst_Teach Monitor
er
© www.olus.lk. All rights reserved.
ER Diagrams Take IT easy with Christy

House_ID
House_ House_Name
Captian

House House_In
Games_
Cap

Logo
Colour

© www.olus.lk. All rights reserved.

Relationships Take IT easy with Christy

One TO One

One TO Many

Many TO Many

© www.olus.lk. All rights reserved.

Relationships – Constraints Take IT easy with Christy

Entity Verb Entity

1 1
Principal admin School
1 1

© www.olus.lk. All rights reserved.

Relationships – Constraints Take IT easy with Christy

1 1
Principal admin School
1 1
PK Principal FK PK FK
School
Pr_ID Title First_Name Gender Contact SL_ID Schl_Name City Contact Pr_ID
SL_ID
P001 Rev. Fr. Shiwantha M 077*** SL004 SL001 LIS N-Eliya P002
P002 Mr. Patmore M 071*** SL001 SL002 RIS Ragala P005
P003 Mrs. Rebeca F 077*** SL003 SL003 IBS N-Eliya P003
P004 Mrs. Jasintha F 076*** SL005 SL004 OLUS N-Eliya P001
P005 Mr. Mark M 072*** SL002 SL005 GSC N-Eliya P004

Master Table / Parent Table Child Table

© www.olus.lk. All rights reserved.

Foreign Key Constraint Take IT easy with Christy

A term related to ERDBMS.

It is used to link two or more tables together.

(To create relationships)

A Foreign Key is a field (or collection of

fields) in one (Parent) table that refers to the
Non-Primary Key in another (Child) table.

© www.olus.lk. All rights reserved.

Relationships – Constraints Take IT easy with Christy

1 1
Student 1… *
in
1 House
n
PK Student FK PK FK FK
House
St_ID First_Name DOB Gender Contact SL_ID H_ID H_Name Colour H_Captain St_ID
ST001 Shane 12/6 M 077*** H01 H01 Raphael Yellow ST003 ST001
ST002 Taniya 10/01 F 071*** H02 H02 Michael Red ST004
ST003 Keneth 03/05 M 077*** H03 H03 Gabriel Blue ST007
ST004 Natasha 02/07 F 076*** H01
ST005 Larry 10/09 M 072*** H02
ST006 Juditha 04/18 F 076*** H03
ST007 Vikash 10/01 M 071*** H01

© www.olus.lk. All rights reserved.

Relationships – Constraints Take IT easy with Christy

1 1
Student 0…*
learn
1 Class
n
PK Student PK Class
FK FK FK
St_ID First_Name DOB Gender Contact Cls_ID Cls_ID Class_Name Class_Teacher St_ID
ST001 Shane 12/6 M 077*** 10S1 10S1 10 Sin-1 ST001 ST001
ST002 Taniya 10/01 F 071*** 10S1 10S2 10 Sin-2 ST002
ST003 Keneth 03/05 M 077*** 10S1 10T1 10 Tam -1 ST003
ST004 Natasha 02/07 F 076*** 10S2 10T2 10 Tam -2 ST004
ST005 Larry 10/09 M 072*** 10S2 11S1
ST006 Juditha 04/18 F 076*** 10T1 11T1
ST007 Vikash 10/01 M 071*** 10T2

© www.olus.lk. All rights reserved.

Relationships – Constraints Take IT easy with Christy

n
1 1…*
Student 0…*
take
1 Subject
n

n m
Many to Many

© www.olus.lk. All rights reserved.

Relationships – Constraints Take IT easy with Christy
FK FK
PK Student PK Subject
St_ID First_Name DOB Gender Contact Sub_ID Sub_ID Sub_Name Sub_Teacher St_ID
ST001 Shane 12/6 M 077*** Sub01 Sub01 Maths ST010 ST001
ST002 Taniya 10/01 F 071*** Sub02 ICT ST004
ST003 Keneth 03/05 M 077*** Sub03 English ST003
ST004 Natasha 02/07 F 076*** Sub04 Science ST009
ST005 Larry 10/09 M 072*** Sub05 Music ST008

Student_Subject
FK FK
St_ID Sub_ID
ST001 Sub01
ST001 Sub02
ST002 Sub01
ST002 Sub03
ST003 Sub01
ST001 Sub02
© www.olus.lk. All rights reserved.
Relationships – Constraints Take IT easy with Christy
FK FK
PK Student PK Subject
St_ID First_Name DOB Gender Contact Sub_ID Sub_ID Sub_Name Sub_Teacher St_ID
ST001 Shane 12/6 M 077*** Sub01 Sub01 Maths ST010 ST001
ST002 Taniya 10/01 F 071*** Sub02 ICT ST004
ST003 Keneth 03/05 M 077*** Sub03 English ST003
ST004 Natasha 02/07 F 076*** Sub04 Science ST009
ST005 Larry 10/09 M 072*** Sub05 Music ST008

Student_Subject
FK FK
St_ID Sub_ID PK Composite Key
ST001 Sub01
ST001 Sub02
ST002 Sub01
ST002 Sub03
ST003 Sub01
ST001 Sub02
© www.olus.lk. All rights reserved.
Composite Key Constraint Take IT easy with Christy

A term related to ERDBMS.

Used in Many to Many Relationships.

A primary key that is made by the

combination of more than one attribute is
known as a composite key. .

© www.olus.lk. All rights reserved.

Database Models Take IT easy with Christy

© www.olus.lk. All rights reserved.

Software Types for ERDBMS – PC Version Take IT easy with Christy

Proprietary - Closed Source Software

© www.olus.lk. All rights reserved.

Software Types for ERDBMS – PC Versions Take IT easy with Christy

Non - proprietary – Open Source Software

Base
© www.olus.lk. All rights reserved.
Software Types for ERDBMS – Enterprise Versions Take IT easy with Christy

© www.olus.lk. All rights reserved.

Software Types for ERDBMS – Cloud Versions Take IT easy with Christy

© www.olus.lk. All rights reserved.

Microsoft Office – Access Take IT easy with Christy

© www.olus.lk. All rights reserved.

Objects in Access Take IT easy with Christy

Tables store a database’s data in rows and columns.

Queries are used to filter data from one or many tables in the database.

Forms are used for entering and editing records.

Reports are presenting data formatted for printing.

Macros help you perform routine tasks by automating them into a single command.

automate tasks but by using a built-in programming language called Visual

Modules Basic for Applications or VBA.

© www.olus.lk. All rights reserved.

Objects in Access Take IT easy with Christy
Table Properties
▪ A Table Name can contain maximum : 64 characters
▪ Maximum number of Tables in a Database : 2048 (Approximately)
Optional
To provide the Title

Description
The data type It helps the user

Data Type
of your field.
determines the to type a note
Field

Filed Name of a Table can kind of data that which describes

contain : 64 characters user can store in the field fields
(Including Spaces)
the field. and also displays
Maximum Number of
in the Status bar
Fields : 255 when you select
a field on a
form.
© www.olus.lk. All rights reserved.
Data Types Take IT easy with Christy
Short Text
Long Text
Number
Date /Time
Currency

Data Types
Auto Number
Yes/No
OLE Object
Hyperlink
Attachment
Calculated
© www.olus.lk. All rights reserved. Lookup Wizard
Data Types Take IT easy with Christy

Used to type Texts

Text Number and Symbols.
• Cannot handle mathematical calculation
• Maximum 255 Characters
• 2 Bytes Per Character
• Can handle UNICODE characters
• E.g. You can type all the character in the keyboard.
© www.olus.lk. All rights reserved.