1 / 33

Relational Database Model

R.SARAVANA KUMAR S.NAVEEN. Relational Database Model. RELATIONAL MODEL. Basically concerned with three things Data structure Data Integrity Data Manipulation A relation is defined as table with columns and rows. Data can be stored in form of a two dimensional table .

clancy
Télécharger la présentation

Relational Database Model

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. R.SARAVANA KUMAR S.NAVEEN Relational Database Model

  2. RELATIONAL MODEL Basically concerned with three things Data structure Data Integrity Data Manipulation A relation is defined as table with columns and rows. Data can be stored in form of a two dimensional table. Row will be the records Columns will be the attributes. Tuple – it a row of a relation. Degree of Relation – No of attributes it contains. Cardinality – No of tuples it contains.

  3. attributes (or columns) customer_name customer_street customer_city Main North North Park Harrison Rye Rye Pittsfield Jones Smith Curry Lindsay tuples (or rows) Each attribute of a relation has a name The set of allowed values for each attribute is called the domain of the attribute The special value null is a member of every domain The current values (relation instance) of a relation are specified by a table

  4. Database • A database consists of multiple relations • Information about an enterprise is broken up into parts, with each relation storing one part of the information account : stores information about accountsdepositor : stores information about which customer owns which account customer : stores information about customers • Storing all information as a single relation such as bank(account_number, balance, customer_name, ..)results in • repetition of information (e.g., two customers own an account) • the need for null values (e.g., represent a customer without an account)

  5. keys • A key is a set of one or more attributes that uniquely identify each tuple in a relation. • Keys can be classified as: • Candidate key • Super key • Primary Key

  6. Candidate Key (CK) When more than one or group of attributes serve as a unique identifier, they are each called as candidate key. Super Key (SK) an attribute or set of attributes that uniquely identifies a tuple within a relation. Primary Key (PK) It is defined as the candidate key that is selected to identify tuples uniquely within the relation. candidate Key(Ck) = Super key(Sk) - primary key(PK)

  7. Foreign Key • A foreign key is an attributes or combination of attributes in relation R2 whose values must match the primary key values in R1.In referring to the R1 tuple

  8. Properties of primary key Stable : The value of a primary key must not change or should not become Null throughout the life of an entity. Minimal: The primary key should be composed of the minimum number of fields that endures the occurrence are unique. Definitive: A value must exist for every record at the time of creation. Accessible: Anyone who wants create, read or delete a record must be able to see the primary key value.

  9. Relational integrity rule There are two important integrity rules: Entity integrity rule. In a base relation, the value of attribute of a primary key cannot be null. We should not be able to insert a tuple into a table with a NULL for primary key. If a user tries to do so then RDBMS reports an error message. Referential integrity rule. If a foreign key exists in a relation, either the foreign key value must match a candidate key value of some tuple in its home relation. • The master table or the parent table which contains the linking column must be a primary key. • The linking column in the transaction table or the child table reference the parent table. This column in the child table which reference a primary key of the parent table is called as a foreign key.

  10. CODD’S RULES E.F. Codd formulated thirteen rules for RDBMS. 0-single foundation rule Information rule. Guaranteed Access Systematic treatment of Null Active online Catalogue Comprehensive Data Sublanguage View updation rule High Level global insert, update & delete Physical Data independence. Logical Data independence Integrity independence Distribution independence Non subversion rule.

  11. Relational Algebra It is a procedure language with operations that is performed on one or more existing relation to derive result ( another) relations without changing the original relations. It has two categories 1. Common set operation • Union b. Intersection c. Difference d. Product 2. Native relational operations a. Selection b. Projection c. Join d. Division

  12. UNION ( U ) • Combine two relation into single table & Eliminates duplicate records

  13. INTERSECTION ( ∩ ) An intersect operator is used to get common records from two relations.

  14. Difference ( -- ) Tuples that are in one relation but are not in another. The expression ( r – s ) produces a relation containing those tuples in rbut not in s.

  15. Cartesian Product ( Χ ) • A set of all possible records, that means combine two relations.

  16. Native Relational Operations : Selection( σ ) Selects a subset of rows from relation (horizontal). σage < 30 ( emp)

  17. Projection ( π ) Retains only wanted columnsfrom relation (vertical). Πage, salary ( emp)

  18. Division B1 B2 B3 A/B2 A/B3 A/B1 A • A / Bcontains allxtuples such that for everyy tuple in B, there is an xy tuple in A.

  19. Join ( ) • Retrieving data from multiple tables using a single SQL statement • Natural join • In which this duplication of column values is eliminated by taking a projection of the table which includes only one of the duplicated columns. • left outer join take all tuples in the left relation that did not match with any tuple in the right relation , pads the tuples that tuple with null values for all other attributes from the right relation • Right Outer join it pads tuple from the right relation that did not match any from that left relation with nulls • Full outer join

  20. EMBEDDED SQL

  21. Embedded SQL • Embedded SQLs are SQL statements included in the programming language. • The programming language in which the SQL statements are included is called the host language. • Some of the host languages are C,COBOL,Pascal,FORTRAN.

  22. This embedded SQL source code is submitted to an SQL precompiler,which processes the SQL statements. • Variables of the host language can be referenced in the embedded SQL statements thus allowing the values calculated by the program to be used by the SQL statements.

  23. The host language variables are used by the embedded SQL statements to receive the result of the SQL queries thus allowing the programming language to process the retrieved values.

  24. EMBEDDED SQL FEATURES • The embedded SQL statement appears in the host language. • Embedded SQL statements are prefixed by a delimiter EXEC SQL – so that they can be distinguished from the host language statements

  25. Every embedded SQL statements is terminated with a delimiter. In COBOL it is END-EXEC.In C,Pascal it is semicolon. • SQL statements can include reference to host variable.Such reference must be prefixed with a colon(:). • Host variables and SQL columns can have the same name.

  26. ADVATAGE OF EMBEDDED SQL • The mixing of SQL statements with the programming language statements is an efficient way of merging the strength of two programming environments.

  27. A simple COBOL Program. IDENTIFICATION DIVISION. PROGRAM ID EMBDSQL. ENVIRONMENT DIVISION. CONFIGURATION SECTION. SOURCE-COMPUTER IBM 3090. OBJECT-COMPUTER IBM 3090. * DATA DIVISION. WORKING-STORAGE SECTION. *Host variables for receiving the values retrieved using the SQL 01 WS-EMP-DETAILS 05 WS_EMPNO PIC 9(3). 05 WS_NAMEPIC X (30). 05 WS_DEPTID PIC X(2). EXEC SQL

  28. INCLUDE SQLCA END_EXEC. * PROCEDURE DIVISION. * *Initialize the working storage variables *The Embedded SQL statements that retrieve the values. EXEC SQL SELECT EMPNO,NAME,DEPTNO INTO:WS_EMPNO,:WS_NAME,:WS_DEPTID FROM EMPLOYEE WHERE EMPNO=100 END-EXEC

  29. *COBOL code to display the values retrieved. DISPLAY “Employee NO. :”,WS_EMPNO. DISPLAY “Employee Name:”, WS_NAME. DISPLAY “Department ID.:”WS_DEPT. STOP RUN

  30. CURSOR IN EMBEDDED SQL • Cursor declaration • Open cursor statement • Fetch statement • Close statement

  31. DYNAMIC SQL • Allows programs to construct and submit SQL queries at run time. • Example of the use of dynamic SQL from within a C program. char * sqlprog = “update account set balance = balance * 1.05 where acc_no = ?” EXEC SQL prepare dynprog from :sqlprog; char acc [10] = “101”; EXEC SQL execute dynprog using :acc;

  32. The dynamic SQL program contains acc_no= ?, which is a place holder for a value that is provided when the SQL program is executed.

More Related