Chapter 1 Introduction to RDBMS - PowerPoint PPT Presentation

About This Presentation
Title:

Chapter 1 Introduction to RDBMS

Description:

Chapter 1 Introduction to RDBMS Prepared By: Muhammad Arshad Javed * * First Normal Form (1NF) Children Sal Empname Empno Salem 5000 Ali 111 Saeed 5000 Ali 111 Yousef ... – PowerPoint PPT presentation

Number of Views:477
Avg rating:3.0/5.0
Slides: 45
Provided by: Kha109
Category:

less

Transcript and Presenter's Notes

Title: 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
Write a Comment
User Comments (0)
About PowerShow.com