chapter 1 introduction to rdbms

Chapter 1

Introduction to RDBMS

1

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 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 organization’s information.

Database

7

Examples of Database Applications:

1. Purchases from the supermarket2. Purchases using your credit card 3. Booking a holiday at the travel agents 4. Using the local library 5. Taking out insurance 6. Renting a video7. Using the Internet 8. 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 modelThe 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……

Ali 102 1/1/1980 ..

Math .…… Acc .…… DB ………

M. Khaled 444 PHD ……

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.…

Khaleel 102 9/2/1980..

Saed 298 1/4/1980… Sec3 320 9-11

Sec2 260 11-1..

Sec1 260 9-11…

Mr. Mhmd 666 PHD..

Mr. Ahmad 222 Msc

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..)

St_idSt_nameBdate Major

101 Ahmad 1/1/1980CS

323 Ali 7/8/1981 Acc

452 Saeed 9/3/1978Marketing

Field

Record

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

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 isn’t stored in relational rows and columns but as objects

The object-oriented database:

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 DBMS……Cont

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):-

1. Internal View (Physical View) :- The way the data is stored in the storage media. (Specified by the DBA)

2. Conceptual View (Logic View):- Describes the structure and constraints for the whole database. (Specified and used by the programmers).

3. 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 shouldn’t be deleted if

it is related to another table. Primary key shouldn’t 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 - ∞) Each value in the first table could relate with many records

in the second table. Many – Many Relationship (∞ - ∞) 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 :-

1. Eliminate redundant data.

2. 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

Normalization…Cont.

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)

EmpnoEmpnameSalChildren

111Ali5000{salem, saeed}

222Ahmad4000{yousef,moh’d}

R is not in 1NF

34

First Normal Form (1NF)

EmpnoEmpnameSalChildren

111Ali5000Salem

111Ali5000Saeed

222Ahmad4000Yousef

222Ahmad4000Moh’d

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.

SnoCityStatuspnoQuantity

s1AD20p1400

s1AD20p2200

s2Dub30p1300

s2Dub30p2500

s3Shj40p1150

R is not in 2NF

37

Second Normal Form (2NF)

PK :- Sno + Pno Sno → city Sno → status Sno+Pno→ Quantity

SnoCityStatuspnoQuantity

SnoCityStatus SnoPnoQuantity

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

City →status

SnoCityStatus SnoPnoQuantity

Third Normal Form (3NF)

SnoCity

City Status

R1

2NF

R2 2NF

SnoPnoQuantityR1 3NF

R2

3NF

R3 3NF

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

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

Emp_IDEmp_NameAge

A101Ahmed24

A102Ali23

A103Imran21

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).

F1F2F3F4F5F6F7F8F9F10

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).

X1X2X3X4X5X6X7X8X9X10

44

References:Book Title,

Database Systems , Fourth Edition, By Thomas Connolly