Chapter 1 Introduction to RDBMS

1
Chapter 1Introduction to
RDBMS
Prepared By Muhammad Arshad Javed
2
Introduction

• Data It is unorganized collection of facts.
  E.g. (names, telephone numbers and addresses).
• Information It is the result of processing the
  raw material. (Giving meanings to data).

3
Types of Files

• Sequential Files.
• (Data Records are arranged sequentially,
  direct access is not allowed, to access a
  specific record all the previous records should
  be scanned first).
• Relative Files.
• (each record has a key field number attached
  to it, storage location is driven from this key,
  direct access is available, key field should be
  of consecutive numbers to apply direct access)
• Indexed Files.
• (Indices act as pointers, direct sequential
  access methods are available)

4
Terms In File Processing

• Raw Data.
• Access Method.
• Formats.
• (Defines a specific field in a record)
• Validation Logic.
• (Verify the data accuracy)
• Processing Logic.
• (used for calculations manipulation)
• Presentation Logic.
• (Display / print data in useful form)

5
Disadvantages Of File System

• Record Duplications.
• Program-data dependence.
• Query Language is not available.
• Recovery mechanism is poor.
• Very Low level of security.
• Explicit data description is not available.
• Concurrency control among multiple users is not
  available.

6
Database

• Database is shared collection of logically
  related data (and a description of this data),
  designed to meet the information needs of an
  organization.
• Logically related data comprises entities,
  attributes, and relationships of an
  organizations information.

7

• Examples of Database Applications
• Purchases from the supermarket
• Purchases using your credit card
• Booking a holiday at the travel agents
• Using the local library
• Taking out insurance
• Renting a video
• Using the Internet
• Studying at university

8
Advantages of Database

• Avoiding Inconsistency.
• (Data is stored in one place Centralized).
• Many applications could share the same data.
• High level of security.
• (Any user can access the data only if he has
  the authority to do that).
• Data Integrity.
• (It means to check that the inserted data
  is correct)
• Data Independence.
• (Data is not designed for a specific
  program, it can be used by more than one
  application)

9
Database Model Integrated collection of
concepts for describing data, relationships
between data, and constraints on the data in an
organization.

• Types of Database model
• The Hierarchical Model -
• Data is represented by records.
• Relationships are represented by pointers (Links)
  in the form of trees. (Parent, Child).

10
Database Models (Cont)
Section record
Mr. Ahmad 222 Msc
M. Khaled 444 PHD
Math.
Acc .
DB
Ali 102 1/1/1980..
Saed 134 9/3/1980....
Mhmdi 110 1/1/1981..
11
Database Models (Cont)

• Network Model -
• Data is represented by records.
• Relationships are represented by pointers (Links)
  in the form of a network.

12
Database Models (Cont)

• Students Sections
  Instructors

Ali 101 1/1/1977.
Sec1 260 9-11
Mr. Ahmad 222 Msc
Khaleel 102 9/2/1980..
Sec2 260 11-1..
Mr. Mhmd 666 PHD..
Saed 298 1/4/1980
Sec3 320 9-11
13
Database Models (Cont)

• Relational Model-
• Data Relationships are organized in tables.
• A table is made of rows columns.
• Rows called records.
• Columns called fields.
• No two row are identical.
• Columns should be of single value
• No repeating field allowed
• No pointers to connect tables.
• Result of any operation is another new table.

14
Database Models (Cont..)
Field
Record
Major Bdate St_name St_id
CS 1/1/1980 Ahmad 101
Acc 7/8/1981 Ali 323
Marketing 9/3/1978 Saeed 452
15
Database Models (Cont..)

• Advantages of Relational Model -
• Simple Structure.
• Easy to use.
• Based on mathematical theory.

16
Database Models (Cont..)

• Rules for Relational Model-
• All data must be represented in tables.
• Each data can be accessed by determining table
  name and column name.
• Null values must be treated systematically.
• Integrity rules must be defined and stored in DD
  not in the DB.

17
The object-oriented database
An object database (also object-oriented
database) is a database model in which
information is represented in the form of objects
as used in object-oriented programming. The main
benefit of creating a database with objects as
data is speed. OODBMS are faster than relational
DBMS because data isnt stored in relational rows
and columns but as objects
18
Database Users

• Data Base Administrator (DBA)-
• Full Control over the DB, (authorizing
  access to the DB, performance monitoring and
  modification of the DB description or its
  relationship).
• System Analysis.
• (determine the requirements of end users,
  then design and analysis for the whole system).
• Application Programmer.
• (implements the program, test, debug,
  document and make it ready to end user).
• End User.
• (the person who used the application done by
  application programmer, his job requires access
  to DB for querying, reporting and updating).

19
DBMS

• DBMS (Database Management System)- It is a
  software to create and maintain a database,
  allows manipulating and managing the data and
  create relationships among items.

20
Database Management System (DBMS)
21
Components of DBMS Environment
22
Components of DBMSCont

• Tools.
• (Used by the programmers to develop their
  applications, e.g. Form manager, report builder..
  etc)
• Utilities.
• (Backup / Statistical analysis)
• DBMS Engine.
• (Manage the raw data data dictionary).

23
Components of DBMS

• Data Sub language.
• Data Definition language- allow users to
  define each data element and create databases
• Data Manipulation Language- allows accessing
  and modifying data in the Database. E.g. SQL.
• Data Control Language- Control Access to the
  Database

24
Architecture of DBMS

• Defines the ways in which the data can be viewed.
  (Three General levels)-
• Internal View (Physical View) -
  The way the data is stored in the
  storage media. (Specified by the DBA)
• Conceptual View (Logic View)-
  Describes the structure and constraints for
  the whole database. (Specified and used by the
  programmers).
• External View (Sub-Scheme)-
  The view of the database as seen by
  the end user.

25
Important Definitions.

• Primary Key - It is a field or set of fields
  that uniquely identified each record in the
  table. PK must be unique and Not Null.
• Foreign Key- It is a field or set of fields that
  are identical to a primary key in another table.
• Candidate Key- It is a field or set of fields
  that can be uniquely used to identify the data
  base. Determining the candidate key is the step
  before choosing the primary key.

26
Important Definitions...Cont

• Tables Operations- ADD, Delete, Append and
  Update.
• Integrity Rules -
• PK must be unique.
• Related fields should have the same field type.
• Related tables should belong to the same DB.

27
Important Definitions...Cont

• Integrity Conditions-
• Foreign Key values must be identical to PK
  values.
• Records of primary tables shouldnt be deleted if
  it is related to another table.
• Primary key shouldnt be changed if this record
  is related to another table.

28
Important Definitions...Cont

• Cascade update related fields -
• During updating the PK in the primary table
  the value of the FK should be updated
  automatically.
• Cascade deletes related records -
• During delete a record from the primary table
  all related records in related tables should be
  deleted also.
• Entity- Any this which has some attributes is
  called an entity. Like hospital, doctor, car etc

29
Data Dictionary It contains information about
the data attributes, elements relationship, user
details, security restrictions and integrity
constraints.
30
Relationships Link between different entities
of the database is called relationship.Types of
Relationships

• One - One Relationship- (1 1)
• Each value in the first table could relate
  with only one record in the second table.
• One Many Relationship- (1 - 8)
• Each value in the first table could relate
  with many records in the second table.
• Many Many Relationship (8 - 8)
• Each value in the first table could relate
  with many records in the second table and each
  value of the second table could relate with many
  records in the first table.

31
Normalization

• Normalization- a Process of decomposing the
  relations by breaking up their attributes into
  smaller relations.
• Two goals of the normalization process -
• Eliminate redundant data.
• Ensure storing only related data in a table ( to
  get a well design for DB).
• Normalization process is performed by using
  Normal Forms (1NF,2NF,3NF)

32
NormalizationCont.

• An entity is said to be in a particular normal
  form if it satisfies a certain set of conditions.
• First Normal Form (1NF)- (the beginning is
  unnormalized entity.
• The relation is in 1NF if it satisfies the
  condition that it contains scalar values.

33
First Normal Form (1NF)
Children Sal Empname Empno
salem, saeed 5000 Ali 111
yousef,mohd 4000 Ahmad 222
R is not in 1NF
34
First Normal Form (1NF)
Children Sal Empname Empno
Salem 5000 Ali 111
Saeed 5000 Ali 111
Yousef 4000 Ahmad 222
Mohd 4000 Ahmad 222
R is in 1NF
35
First Normal Form (1NF)

• Anomalies for 1NF-
• Insertion.
• Deletion.
• Update

36
Second Normal Form (2NF)

• Second Normal Form (2NF) - The Entity is in 2NF
  if it is in 1NF and every non key attribute is
  irreducibly depend on primary key.

Quantity pno Status City Sno
400 p1 20 AD s1
200 p2 20 AD s1
300 p1 30 Dub s2
500 p2 30 Dub s2
150 p1 40 Shj s3
R is not in 2NF
37
Second Normal Form (2NF)
Quantity pno Status City Sno

• PK - Sno Pno
• Sno ? city
• Sno ? status
• SnoPno? Quantity

Status City Sno
Quantity Pno Sno
R1
R2
38
Third Normal Form (3NF)

• Third Normal Form- The entity is in 3NF if it is
  in 2NFand every non-key attributes are mutually
  independent. (two or more attributes are mutually
  independent if none of them is functionally
  depend on any combination of the others

39
Third Normal Form (3NF)
R2 2NF
R1 2NF
Status City Sno
Quantity Pno Sno

• City ?status

Quantity Pno Sno
City Sno
R1 3NF
R2 3NF
R3 3NF
Status City
40
FUNCTIONAL DEPENDENCY When all non key
attributes depend on the primary key , it is
called functional dependency or fully functional
dependency. or
If there are two columns A and B in a table, then
column B is said to be functionally dependent on
column A if, given A we can precisely determine B
Age Emp_Name Emp_ID
24 Ahmed A101
23 Ali A102
21 Imran A103
If we say, that Emp_name can be determined
precisely by Emp_id, then Emp_name is
functionally dependent on Emp_id OR Emp_id
functionally determines Emp_name So, we can use
the following notation for these two
statements- Emp_id Emp_Name
41
Types of Dependencies
Partial Dependency When non key attributes
depend on some part of Primary key not on
complete Primary key, this is called Partial
Dependency.
Transitive Dependency When non key attributes
depend on non key attribute, this is called
Transitive Dependency.
42
Example on Normalization

• Upgrade the following table from 1NF to 2NF then
  to 3NF
• Consider the following relations
• 01 Fields (F1, F2, F3) is the Primary key.
• 02 Field F4 is depending on field F5.
• 03 Field F7 is depending on Field F8.
• 04 Field F10 is depending on fields (F1, F2,
  F3).
• 05 Fields (F4, F5, F6) are depending on fields
  (F1, F2).
• 06 Fields (F7, F8, F9) are depending on fields
  (F2, F3).

F10 F9 F8 F7 F6 F5 F4 F3 F2 F1
43
Example on Normalization

• Upgrade the following table from 1NF to 2NF then
  to 3NF
• Consider the following relations
• 01 Fields (X1, X2, X3) is the Primary key.
• 02 Field X4 is depending on field X5.
• 03 Field X8 is depending on Field X7.
• 04 Field X9 is depending on fields (X1, X2, X3).
• 05 Fields (X4, X5, X10) are depending on fields
  (X1, X3).
• 06 Fields (X6, X7, X8) are depending on fields
  (X1, X2).

X10 X9 X8 X7 X6 X5 X4 X3 X2 X1
44
References Book Title, Database Systems ,
Fourth Edition, By Thomas Connolly