Title: Chapter 1 Introduction to RDBMS
1 Chapter 1Introduction to
RDBMS
Prepared By Muhammad Arshad Javed
2Introduction
- 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).
3Types 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)
4Terms 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)
5Disadvantages 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.
6Database
- 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
8Advantages 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)
9Database 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).
10Database 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..
11Database Models (Cont)
- Network Model -
- Data is represented by records.
- Relationships are represented by pointers (Links)
in the form of a network.
12Database 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
13Database 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.
14Database 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
15Database Models (Cont..)
- Advantages of Relational Model -
- Simple Structure.
- Easy to use.
- Based on mathematical theory.
16Database 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.
17The 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
18Database 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).
19DBMS
- 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.
20Database Management System (DBMS)
21Components of DBMS Environment
22Components 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).
23Components 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
24Architecture 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.
25Important 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.
26Important 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.
27Important 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.
28Important 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
29Data Dictionary It contains information about
the data attributes, elements relationship, user
details, security restrictions and integrity
constraints.
30Relationships 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.
31Normalization
- 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)
32NormalizationCont.
- 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.
33First Normal Form (1NF)
Children Sal Empname Empno
salem, saeed 5000 Ali 111
yousef,mohd 4000 Ahmad 222
R is not in 1NF
34First 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
35First Normal Form (1NF)
- Anomalies for 1NF-
- Insertion.
- Deletion.
- Update
36Second 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
37Second 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
38Third 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
39Third Normal Form (3NF)
R2 2NF
R1 2NF
Status City Sno
Quantity Pno Sno
Quantity Pno Sno
City Sno
R1 3NF
R2 3NF
R3 3NF
Status City
40FUNCTIONAL 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
41Types 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.
42Example 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
43Example 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
44References Book Title, Database Systems ,
Fourth Edition, By Thomas Connolly