Chapter 1 Introduction to RDBMS Prepared By Muhammad

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

Introduction l l 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 l 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). l 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) l 3 Indexed Files. (Indices act as pointers, direct & sequential access methods are available)

Terms In File Processing l l l 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 l l l l 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 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 l l l 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). l 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…) l ü ü 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 Sec 1 260 9 -11 … Mr. Ahmad 222 Msc Sec 2 260 11 -1. . Mr. Mhmd 666 PHD. . Sec 3 320 9 -11 Sections Instructors

Database Models (Cont…) l ü ü ü ü ü 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 452 14 St_name Bdate Major Ahmad 1/1/1980 CS 7/8/1981 Acc Ali Saeed 9/3/1978 Marketing

Database Models (Cont. . ) l ü ü ü 15 Advantages of Relational Model : Simple Structure. Easy to use. Based on mathematical theory.

Database Models (Cont. . ) l 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 l l 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 l 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 l Tools. l (Used by the programmers to develop their applications, e. g. Form manager, report builder. . etc) Utilities. (Backup / Statistical analysis) l DBMS Engine. (Manage the raw data & data dictionary). 22

Components of DBMS l 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 l 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. l l l 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 l l ü ü ü 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 l ü ü ü 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. l 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 l 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. l One – Many Relationship: - (1 - ∞) Each value in the first table could relate with many records in the second table. l 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. l 30

Normalization l l 1. 2. l 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 (1 NF, 2 NF, 3 NF)

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

First Normal Form (1 NF) Empno 33 Empname Sal Children 111 Ali 5000 222 Ahmad 4000 {salem, saeed} {yousef, moh’d} R is not in 1 NF

First Normal Form (1 NF) 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 1 NF

First Normal Form (1 NF) l Anomalies for 1 NF: Ø Ø Ø 35 Insertion. Deletion. Update

Second Normal Form (2 NF) l Second Normal Form (2 NF) : - The Entity is in 2 NF if it is in 1 NF and every non key attribute is irreducibly depend on primary key. Sno 36 City Status pno Quantity s 1 AD AD 20 20 p 1 p 2 400 200 s 2 Dub 30 p 1 300 s 2 s 3 Dub Shj 30 40 p 2 p 1 500 150 R is not in 2 NF

Second Normal Form (2 NF) Sno City Status l l Sno 37 R 1 City Status pno Quantity PK : - Sno + Pno Sno → city Sno → status Sno+Pno→ Quantity Sno R 2 Pno Quantity

Third Normal Form (3 NF) l 38 Third Normal Form: - The entity is in 3 NF if it is in 2 NFand 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 (3 NF) R 1 2 NF Sno City Status Sno R 2 2 NF Pno Quantity City →status R 1 3 NF R 2 3 NF 39 Sno City Status Sno Pno R 3 3 NF 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 A 101 Ahmed 24 A 102 Ali 23 A 103 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 l Upgrade the following table from 1 NF to 2 NF then to 3 NF: F 1 l Consider the following relations: – – – 42 F 3 F 4 F 5 F 6 F 7 F 8 F 9 01: Fields (F 1, F 2, F 3) is the Primary key. 02: Field F 4 is depending on field F 5. 03: Field F 7 is depending on Field F 8. 04: Field F 10 is depending on fields (F 1, F 2, F 3). 05: Fields (F 4, F 5, F 6) are depending on fields (F 1, F 2). 06: Fields (F 7, F 8, F 9) are depending on fields (F 2, F 3). F 10

Example on Normalization l Upgrade the following table from 1 NF to 2 NF then to 3 NF: X 1 X 2 X 3 X 4 X 5 X 6 X 7 X 8 X 9 l Consider the following relations: – – – 43 X 10 01: Fields (X 1, X 2, X 3) is the Primary key. 02: Field X 4 is depending on field X 5. 03: Field X 8 is depending on Field X 7. 04: Field X 9 is depending on fields (X 1, X 2, X 3). 05: Fields (X 4, X 5, X 10) are depending on fields (X 1, X 3). 06: Fields (X 6, X 7, X 8) are depending on fields (X 1, X 2).

References: Book Title, Database Systems , Fourth Edition, By Thomas Connolly 44