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 organization’s 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 …… DB ……… Math……. Acc ……. Ali 102 1/1/1980.. Mhmdi 110 1/1/1981.. Saed 134 9/3/1980....

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

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 isn’t 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 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):- • 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 valuesmust 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 :- • 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. 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) R is not in 1NF

34. First Normal Form (1NF) 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. R is not in 2NF

37. Second Normal Form (2NF) • PK :- Sno + Pno • Sno → city • Sno → status • Sno+Pno→ Quantity 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 City →status R1 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

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

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

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