Transcript Terms In File Processing

Chapter 1
Introduction to RDBMS
Prepared By: Muhammad Arshad Javed
1
Introduction


2
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).
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)

3
Indexed Files.
(Indices act as pointers, direct & sequential access methods
are available)
Terms In File Processing






4
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)
Disadvantages Of File System







5
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.
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 organization’s
information.
6
Examples of Database Applications:
1.
2.
3.
4.
5.
6.
7.
8.
7
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
Advantages of Database





8
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)
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).

9
Database Models (Cont…)
Section record
Mr. Ahmad 222 Msc……
DB ………
Ali 102 1/1/1980..
10
Math…….
M. Khaled 444 PHD ……
Acc …….
Mhmdi 110 1/1/1981..
Saed 134 9/3/1980....
Database Models (Cont…)



11
Network Model :Data is represented by records.
Relationships are represented by pointers (Links)
in the form of a network.
Database Models (Cont…)
Ali 101 1/1/1977….
Khaleel 102 9/2/1980..
Saed 298 1/4/1980 …
Students
12
Sec1 260 9-11 …
Mr. Ahmad 222 Msc
Sec2 260 11-1..
Mr. Mhmd 666 PHD..
Sec3 320 9-11
Sections
Instructors
Database Models (Cont…)










13
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.
Database Models (Cont..)
Field
Record
St_id
101
323
St_name
Bdate
Major
Ahmad
1/1/1980
CS
Ali
Acc
7/8/1981
452
14
Saeed
9/3/1978 Marketing
Database Models (Cont..)




15
Advantages of Relational Model :Simple Structure.
Easy to use.
Based on mathematical theory.
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.



16
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 isn’t stored
in relational rows and columns but as objects
17
Database Users




18
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).
DBMS

19
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.
Database Management System (DBMS)
20
Components of DBMS Environment
21
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).
22
Components of DBMS

23
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
Architecture of DBMS

1.
2.
3.
24
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.
Important Definitions.



25
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.
Important Definitions...Cont





26
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.
Important Definitions...Cont




27
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.
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

28
Data Dictionary:
It contains information about the data
attributes, elements relationship, user details,
security restrictions and integrity constraints.
29
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.

30
Normalization


1.
2.

31
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)
Normalization…Cont.


32
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.
First Normal Form (1NF)
Empno
33
Empname
Sal
Children
111
Ali
5000
{salem,
saeed}
222
Ahmad
4000
{yousef,
moh’d}
R is not in 1NF
First Normal Form (1NF)
34
Empno
Empname
Sal
Children
111
Ali
5000
Salem
111
Ali
5000
Saeed
222
Ahmad
4000
Yousef
222
Ahmad
4000
Moh’d
R is in 1NF
First Normal Form (1NF)

Anomalies for 1NF:


35
Insertion.
Deletion.
Update
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.
Sno
36
City
Status pno Quantity
s1
s1
AD
AD
20
20
p1
p2
400
200
s2
Dub
30
p1
300
s2
s3
Dub
Shj
30
40
p2
p1
500
150
R is not in
2NF
Second Normal Form (2NF)
Sno
City
Status




Sno
37
R1
City Status
pno
Quantity
PK :- Sno + Pno
Sno → city
Sno → status
Sno+Pno→ Quantity
Sno
R2
Pno
Quantity
Third Normal Form (3NF)

38
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
Third Normal Form (3NF)
R1
Sno
City
Status
Sno
R2
2NF
Pno
Quantity
2NF
City →status
R1
3NF
R2
3NF
39
Sno
City
City
Status
Sno
Pno
R3 3NF
Quantity
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
Emp_ID
Emp_Name
Age
A101
Ahmed
24
A102
Ali
23
A103
Imran
21
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
40
Emp_Name
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.
41
Transitive Dependency:
When non key attributes depend on non key
attribute, this is called Transitive Dependency.
Example on Normalization

Upgrade the following table from 1NF to 2NF then to 3NF:
F1

Consider the following relations:
–
–
–
–
–
–
42
F2 F3 F4 F5 F6 F7 F8 F9
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
Example on Normalization

Upgrade the following table from 1NF to 2NF then to 3NF:
X1 X2 X3 X4 X5 X6 X7 X8 X9

Consider the following relations:
–
–
–
–
–
–
43
X10
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).
References:
Book Title,
Database Systems ,
Fourth Edition,
By Thomas Connolly
44