UNIVERSITY OF COLOMBO

1
UNIVERSITY OF COLOMBO SCHOOL OF
COMPUTING
IT2301 Database Management Systems
DEGREE OF BACHELOR OF INFORMATION TECHNOLOGY
2
Todays Agenda

• Syllabus
• Examination
• Question Paper Structure
• Introduction to DBMS
• Resources For Learning
• Reference materials
• Web resources

3
BRIEF SYLLABUS

• TOPIC
  MIN. NO. OF HOURS
• 1. File Organisation ...
  12
• 2. Introduction to DBMS
  06
• 3. Data Model
  04
• 4. Database Design Process
  11
• 5. Data Normalisation process ... 03
• 6. Data Manipulation
  24

4
Data Manipulation 24
STUDENTS ARE EXPECTED TO HAVE A TOTAL OF 10
ADDITIONAL HOURS OF PRACTICAL TUTORIALS TO
FURTHER STRENGTHEN THEIR KNOWLEDGE OF THIS SECTION
5

• Examination
• Paper Structure (MCQ)
• File Organisation
• Introduction to DBMS
• Data Model
• Database Design
• Data Normalisation
• Data Manipulation

40-60 08 - 12 04 - 06 02 - 04 08 - 11 02 -
03 16 - 24
6
SYLLABUS - DBMS

• TOPIC MIN.
  NO. OF HOURS
• 2. Introduction to DBMS 06
• a. The Evolution of Database Technology 02
• b. Database Architecture 04

7
Introduction to DBMS - OBJECTIVES

• Explain the difference between conceptual,
  external and physical schemas and the reasons for
  a three-schema architecture for databases

8
Database Approach
e.g. Integrated human resources database
DBMS
data files

• Employees
• Name, Address
• NID number, Designation
• Payroll
• Hours worked, Pay rate
• Benefit
• Insurance, Pension plan

• Personnel application program
• Payroll application program
• Benefits application program

9
3 Level ANSI/SPARC Architecture

• Made databases more independent of application
• Became a standard for the organisation of DBMS

10
3 Levels

• External
• Conceptual
• Physical

11
Schema for the level

• External (Sub) Schema
• defines the external view of data
• as seen by a user or program
• Conceptual Schema
• defines the logical view of data
• as seen by all users and programs
• Physical (Internal) Schema
• defines the physical view of data
• as seen by a DBMS

12
3 Level Architecture
user a
user i/program j
program x
sub-schema a
sub-schema i
sub-schema z
conceptual schema
physical schema
13
(No Transcript)
14
Physical View

• The DBMS must know
• exact physical location
• precise physical structure

15
Logical View

• The user/application must know
• existence
• logical reference

Employee
16

• Conceptual Layer
• The conceptual model is a logical representation
  of the entire contents of the database.
• The conceptual model is made up of base tables.
• Base tables are real in that they contain
  physical records.

17
External View

• The user/application see
• authorised data
• own format

Lecturer
18
External View cont.

• External Views Allows to
• hide unauthorised data
• e.g. salary, dob
• provide user view
• e.g. view employee name, designation, department
  data taken from employee and department files
• derive new attributes
• e.g. age derived from dob or nid

19
External View cont.

• External Views Allows to
• change unit of measurement
• e.g. show age in years or months
• define security levels
• e.g. update access to employee file
• read-only to department file

20

• External Layer
• The external model represents how data is
  presented to users.
• It is made up of view tables. View tables are
  "virtual"-- they do not exist in physical
  storage, but appear to a user as if they did

21
Conceptual Layer - Example
Department
Employee
Base Tables
22
External Layer - Example
View Tables
23
(No Transcript)
24
Model Paper Question 12
12) Which of the following is (are) true for the
3 level architecture? (a) The physical schema
should assist to locate the data stored on
disk. (b) The logical view of the data provides
the view of data for the user. (c) Data
authorisation can be specified for the physical
schema. (d) Program/data independence is
eliminated due to this architecture. (e) New
data can be derived with the help of sub-schemas.
25
Model Paper Question 12
12) Which of the following is (are) true for the
3 level architecture? (a) The physical schema
should assist to locate the data stored on
disk. (b) The logical view of the data provides
the view of data for the user. (c) Data
authorisation can be specified for the physical
schema. (d) Program/data independence is
eliminated due to this architecture. (e) New
data can be derived with the help of sub-schemas.
v
v
26
2002 August Paper Q13.

• Consider the following six statements.
• External - Various user views exist and each of
  these views gives a user-oriented description of
  the data elements and relationships of which the
  view is composed. These views are defined using
  sub schema data definition language.

v
27
Question 13

• External - A logical description of some portion
  of the database that is required by a user to
  perform some task and these are defined using
  data manipulation language.
• Logical - The result of the conceptual design,
  which involves analysis of all users information
  needs and data definitions of data items.

v
28
Question 13 contd.

• Logical - The result of the logical design, which
  involves analysis of important users information
  needs.
• Physical - The precise physical structure and the
  exact physical location of the database, which is
  defined by the database administrator using data
  definition language.

v
29
Question 13 contd.

• Physical - The precise physical structure and the
  exact physical location of the database, which is
  defined by the database administrator using data
  manipulation language.
• Answer
• Correct Statements (i) (iii) (v)
• Correct choice d

30
Question 42 (2002 August)

• Which of the following statement(s) is/are true
  with respect to user views?
• User views are virtual tables that are visible to
  users.
• User views occupy a particular amount of storage
  space and contain query output data.

v
31

• Most of the SQL commands that can be performed on
  tables can be performed on views too.
• User views are a way of protecting data from
  unauthorized access.
• DROP VIEW command will remove the view table and
  its corresponding data.

v
v
32
Learning Resources

• Teaching Materials
• http//www.bit.lk/TeachingMaterial/IT2301
• Reference Materials
• Main Readings and Recommended Readings
• Preparing for BIT
• http//www.bit.lk/Information/tv/
• Past Papers
• http//www.bit.lk/Examinations/s2/

33
References

• MAIN READING
• Database Management and Design by G.W. Hansen and
  J.V. Hansen, 2nd edition, Prentice-Hall of India,
  Eastern Economy Edition, 1999.
• Database System Concepts by A. Silberschatz, H.F.
  Korth and S. Sudarshan, 3rd edition, McGraw-Hill,
  International Edition, 1997.

34
REFERENCES

• SUPPLEMENTARY READING
• Principles of Database Management by James
  Martin.
• Database Management Systems by A.K. Majumdar ...
• An Introduction to Database Systems by C.J. Date.
  
• Fundamentals of Database Systems by R. Elmasri
  ...
• Modern Database Management by R.F. McFadden ...
• Database Processing Fundamentals, Design, ...

ANY BOOK WILL COVER ALL MAJOR PARTS OF THE
SYLLABUS