Please enable JavaScript.

Coggle requires JavaScript to display documents.

CHAPTER 2: RELATIONAL DATA MODEL, STU_NUM = Student number STU_LNAME…

- - - - cardinality (number of rows)
      - degree (number of coumns)
      - attribute (column/field)
      - key (Primary,foreign,candidate)
      - tuple (row/record)
      - domain (set of values allowed in an attribute)
      - relation (table)
  - - - Application areas of RDBMS
        
        Universities >For student information, course registrations, colleges and grades.
        
        Telecommunication > It helps to keep call records, monthly bills, maintaining balances, etc.
        
        Banking > For storing information about stock, sales, and purchases of financial instruments like stocks and bonds.
  - - - To ensure:
        
        every relation has a primary key
        
        data values for the primary key are all valid
      - Null:
        
        a value that may be assigned to an attribute
        
        when no other value applies or the applicable value is unknown
      - Primary key:
        
        may not be null
        
        MUST have data
  - - - missing information can represented using null
        
        NULL indicates an unknown/ missing value
        
        Primary keys cannot contain NULL values (entity integrity)
  - - - used to ensure accuracy and consistency of data in a relational database
      - 2 types of relational integrity :
        
        ENTITY INTEGRITY
        
        REFERENTIAL INTEGRITY
  - - - Tabular Form
        
        Enable user to update multiple rows in a table at once from single page.
        
        This built-in process performs optimistic locking behind the scene to maintain the data integrity.
  - - - Relational Database Management System
      - RDBMS is a database management system based on relational
        model defined by E.F. Codd.
      - The data in an RDBMS is stored in database objects which are
        called as tables.
      - This table is basically a collection of related data entries and it
        consists of numerous columns and rows.
      - Most RDBMS use SQL as database query language.
      - The most popular RDBMS are MS SQL Server, DB2, Oracle and
        MySQL.
  - - - each table row(tuple) represent a single entity occurrence within the entity set
        
        each table column represent an attribute and each column has a distinct name
        
        each intersection of a row and column represents a single data value
        
        all values in a column must conform to the same data format
        
        each column has a specific range of values known as the attribute domain
        
        the order of the row and column is immaterial to the DBMS
        
        1 more item...
- - - - An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.
      - There are three kinds of outer joins-
        LEFT OUTER JOIN, RIGHT OUTER JOIN AND FULL OUTER JOIN.
      - An outer join extends the result of a simple join
  - - - To combine the table we will need follow example: EMPLOYEE join Employee_ID=Employee_ID Department
  - - - Q: find which worker have salary more than 200.show name and salary
        
        solution;
        
        using an intermediate result
        temp<-- select salary>200(E)
        result<-- project name,salary(Temp)
  - - - all tuples from both relation are included in the resulting relation
      - if there are no matching tuples for both relations, their respective unmatched attributes are made NULL
  - - - From this example, if it means R difference S :
        the output must be a set of tuples that available in R but not in S
        
        And, if it S difference R :
        the outputs must be a set of tuples that available in S but not in R
  - - - Consists of a set of operations on relations
      - Defines theoretical way of manipulating table contents using relational operators
      - Introduced by E.F Codd in 1972
      - Use of relational algebra operators on existing relations produces new relations
    - - Difference (- or & setmn;)
        
        Difference between tow relations A and B (in that order) is the set of all tuples belonging to A and not to B
        
        Like UNION, the tables must be union-compatible to yield valid results
  - - - Operation to select specific rows/ tuples/ records.
        
        Consider this table: Emp
      - Example:
        
        σ salary < 200(Emp)
        
        σ salary < 200 and nr >= 7 (Emp)
        
        General form:
        
        σ <selection condition>(R)
- - - - left outer join returns all rows in the left table (A) and all the matching rows found in the right table (B).
        
        It means the result of the SQL left join always contains the rows in the left table.
        
        If there are tupples in A without any matching tuple in the Right relation B, then the B-attributes of the resulting relation are made NULL.
- - - - SQL uses relational algebra,
      - DBMS converts into RA with SQL queries
      - Execution planning and optimization of queries are RA based.