Chapter 4 Relational Algebra and Relational Calculus

Unary Operation  There are two of unary operations. They are selection and projection.

  

 Selection or Restriction

Opredicate(R) The selection operation works on a single relation R and defines a relation that contains only those tuples of R that satisfy the specified condition (predicate).

Example : List all staff with a salary greater than 10,000.

Osalary>10000(Staff)
The input relation is staff and the predicate is salary>10000. The selection operation defines a relation containing only those staff tuples with a salary greater than 10000. More complex predicates can be generated using the logical operators ^(And),v (OR ) and ~(NOT)

Projection 

IIa1,....,an(R)  The projection operation works on a single relation R and defines a relation that contains a vertical subset of R, extracting the values of specified attributes and eliminating duplicates.

Example : Produce a list of salaries  for all staff, showing only the StaffNo,fName,lName, and salary details.

IIstaffNo,fName,lName,salary(Staff) 

Unioin :
R U S The union of two relations R and S defines a relation that contains all the tuples of R, or S or both R and S, duplicate tuples being eliminated. R and S must be union-compatible.
Example : 
List all cities where there is either a branch office or a property for rent.

IIcity(Branch) U IIcity(PropertyForRent)

 Set difference
R-S  The set difference operations defines a relation consisting of the tuples that are in relation R, but not in S. R and S must be union-compatible
 IIcity(Branch) - IIcity(PropertyForRent) ( take only different )

Intersection
 
   

Chapter 3 Relation Model

1.) Relation Data Structure
- Relation : A relation is a table with columns and rows. Or we can say a primary key field.
- Attribute : An attribute is a named column of a relation.
- Domain : A domain is the set of allowable values for one or more attributes.
- Tuple : A tuple is a row of a relation.
- Degree : The degree is number of attributes or number of fields.
- Cardinality : The cardinality of a relation is the number of tuples it contains.
- Relational database : A collection of normalized relations with distinct relation.

Mathematical Relations:
 Cartesian Product 
  D1 = {2,4} , D2 = {1,3,5} 
D = D1 x D2 = { (2,1),(2,3),(2,5),(4,1),(4,3),(4,5) };
  n
X Di
i=1 


2.) Database Relations
- Relation Schema : A named relation defined by a set of attribute and domain name pairs.
Let A1,A2,....An with domians D1,D2,........,Dn then the set{A1:D1,A2:D2,........,An:Dn}

- Relational Database Schema: A set of relation schema, then we can write the relational database schema, or simply relational schema, R, as : R={R1,R2,.....,Rn}
- Property of Relations:
  + the relation has a name that is distinct from all other relation names in the relational schema.
  + Each cell of the relation contains exactly one atomic (single) value;
  + Each attribute has a distinct name.
  + The value of an attribute are all from the same domain.
  + Each tuple is distinct. There are no duplicate tuples.
  + The order of attributes has no significance.
  + In a relation, the possible value s for a given position are determined by the set, or domain, on which the position is defined. In a table the values in each column must come from the same attribute domain.
  + In a set, no elements are repeated. there are no duplicate tuples.

3.) Relational Keys
- Super key :  An attribute, or set of attributes, that unique identified a tuple  within a relation.
- Candidate Key :  A super key such that no proper subset is a super key within the relation.
- Primary Key :  The candidate key that is selected to identify tuples uniquely within relation.
- Foreign Key : An attribute, or set of attributes, within one relation that matches the candidate key of some ( possibly the same ) relation.

Null represents a value for an attribute that is currently unknown or is not applicable for this tuple.

3.) Entity Integrity In a base relation, no attribute of a primary key can be null.

Referential Integrity 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 or the foreign key value must be wholly null.

General Constraints Additional rules specified by the users or database administrators of a database that define or constrain some aspect of the enterprise.

4.) Terminology
Base Relation : A named relation corresponding to an entity in the conceptual schema whose tuples are physically stored in the database.

View: The dynamic result of one or more relational operations operating on the base relations to produce another relation. A view is a virtual relation that does not necessary exist in the database but can be produced upon request by particular user at the time of request.

The purpose of Views
- It provides a powerful and flexible security mechanism by hiding parts of the database from certain users. Users are not aware of the existence of any attributes or tuples that are missing from the view.
- It permits users to access data in a way that is customized to their needs, so that the same data can be seen by different users in different ways, at the same time.
- It can simplify complex operations on the base relations.
- A user might need Branch tuples that contain names of mangers as well as the other attributes already in Branch.
- Some members of staff should see staff tuples without salary attribute.
- Attributes may be renamed or the order of attributes changed.
- Some members of staff should see only property records for those properties that they manage.

Updating Views

- Updates are allowed through a view defined using a simple query involving a single base relation and containing either the primary key or a candidate key of the base relation.
- Updates are not allowed through views involving multiple base relations.
- Updates are not allowed through views involving aggregation or grouping operations.

CHAPTER 2 DATABASE ENVIRONMENTS

1. External Level : The users’ view of the database. This level describes that

Part of the database that is relevant to each user.

2. Conceptual Level :

This level describes what data is stored in the database and relationships among the data.

Conceptual level represents: all entities, their attributes, and their relationship

-          The constraints on the data

-          Semantic information about the data

-          Security and integrity information

3. Internal Level: This level describes how the data is stored in the database.

- Storage space allocation for data and indexes.

- Record descriptions for storage (with stored sizes for data items)

- Record replacement.

- Data compression and data encryption techniques.

Schema, Mappings and Instances

The overall description of the database is called database schema. There are three types of schema external, internal and conceptual schema.

Data Independence

 +Logical data independence: refers to external schema to changes in the conceptual schema. Example adds or removes of new entities, attributes or relationship without having to change existing external schema or having to rewrite the application program.

 +Physical data independence:  refers to the conceptual schema to changes in the internal schema. Changes to the internal schema, such as using different file organization or storage structures, using different storage devices, modifying indexes, or hashing algorithms, should be possible without having changed the conceptual or external schema.

Database Languages

 + Data Definition Language (DDL) is a language that allows DBA or users to describe and name the attributes, and relationships required for the application, together with associated integrity and security constraints.

 + Data Manipulation Language (DML) is a language that provides a set of operations to support the basic data manipulations on the data help in the database. Data manipulation operations are insertion, modification, retrieval and deletion.  

Procedural DML: A language that allow users tell the system what data is needed and exactly how to retrieve the data.

Non-procedural DML: A language that allows the user to state what the data is needed rather than how it is to be retrieved.

Data Models and Conceptual Modeling

Data Model: An integrated collection of concepts for describing and manipulating data relationships between data, and constraints on the data in an organization.
 A model is representation of real world objects and events, and their association.It is an abstraction that concentrates on the essential.

Components of data model:
1.) Structural part. consisting of a set of rules according to which database can be constructed.
2.) Manipulative part allow operations insert,delete,update and retrieve data.
3.) Set of integrity constraints to ensure the data is accurate.

Object-Based Data Models
 use concepts such as entities, attributes, and relationships. Common types of object-based data model are :
+ Entity-Relationship
+ Semantic
+ Functional
+ Object-Oriented.

Record-Based Data Models
 Database consists of number of fixed-format records in different types. Each record type defines a fixed number of fields, each field has fixed length. There are three principle type of record-based logical data model.
+Relational data model grouped into relations and provide a declarative method for specifying data and queries: users directly state what information the database contains and what information they want from it, and let the database management system software take care of describing data structures for storing the data and retrieval procedures for answering queries.
+Network data model
a flexible way of representing objects and their relationships. It is of the schema, viewed as a graph in which object types are nodes and relationship types are arcs
+Hierarchical data model
is a data model in which the data is organized into a tree-like structure. The structure allows representing information using parent/child relationships: each parent can have many children, but each child has only one parent (also known as a 1-to-many relationship). All attributes of a specific record are listed under an entity type.

Physical Data Models
describe how data is stored in the computer, representing information such as record structures, record orderings, and access paths.

Conceptual Modeling or Conceptual database design.
is the process of constructing a model of the information use in an enterprise that is independent of implementation details such as the target DBMS, application programs, programming languages or any other physical considerations.

Functionals of a DBMS
1.) Data Storage, retrieval, and update
        A DBMS must have users ability to store, retrieve, and update data in the database.
2.) A User-accessible catalog
       A DBMS must have a catalog in which descriptions of data items are stored which is accessible to users.
System catalog stores: names,types,and sizes of data items, name of relationships,name of authorized users who have access to data,usage statistic,security,audit information,redundancy and consistency.
3.) Transition support
         A DBMS must have a mechanism which will ensure either that all the updates corresponding to a given transaction are made or that none of them is made.
4.) concurrency control services
       A DBMS must have a mechanism to make sure the database is updated correctly when multiple users are updating the database concurrently.
5.) Recovery Services
     A DBMS must have a mechanism for recovering the database when the database has damaged in anyway.
6.) Authorization Services
        A DBMS must have a mechanism to ensure that only authorized users can access the database.
7.) Support for data communication
        A DBMS must be capable of integrating with communication software.
8.) Integrity Services.
      A DBMS must have to ensure that both the data in database and changes to the data follow certain rules.
9.) Services to promote data independence
        A DBMS must include facilities to support the independence of programs from the actual structure of the database.
10.) Utility Services
         A DBMS should provide a set of utility services.

Components of DBMS

 

 Multi-User DBMS Architectures.
1.) Teleprocessing : There is one computer with a single central processing unit (CPU) and number of terminals like pictures.

2.) File-Server Architecture

As the file-server has no knowledge of SQL, the DBMS has to request the files corresponding to the Branch and staff relations from the file-server, rather than just the staff names that satisfy the query.

Disadvantage of file-server

+ Large amount of network traffice.

+ A full copy of the DBMS is required on each workstation.

+ Concurrency, recovery, and integrity control are more complex because there can be multiple DBMS accessing the same files.

Two Tier and Three Tier Client-Server Architecture

 

Transaction Processing Monitors.

TP Monitor A program that controls data transfer between clients and servers in order to provide a consistent environment, particularly for online trans-action processing.

Transaction Routing The TP Monitors can increase scalability by directing transaction to specific DBMS.

Load Balancing: The TP Monitor can balance client requests across multiple DBMS on one or more computers by directing client service calls to the least loaded server. It can dynamically bring in additional DBMS as required to provide necessary performance.

Funneling in environment with large number of users sometime users difficult logged on simultaneously to the DBMS. The TP Monitor can establish connections with the DBMS as the required, and can funnel user request through these connection.

Increases reliability: TP Monitor acts as a transaction manager, performing necessary actions to maintain consistency of the database, with the DBMS acting as a resource manager. If the DBMS fails, the TP Monitor maybe able to resubmit the transaction to another DBMS or can hold the transaction until the DBMS become available again.

Managing distributed transaction TP Montor can manage transactions that require access to data held in multiple .

Chapter 1

DBMS Summary:

Chapter 1

1.) File Base System

            A collection of application programs that perform services for the end-users such as production of reports. Each program defines and mange its own data.

2.) Limitation of the File-Based Approach

- Separation and isolation of data

- More difficult to access the data when data is isolated in separate files.

- Difficult processing when data are in difference files.

- Duplication of Data

                - is wasteful. It cost time and money to enter the data more than once.

                - It takes up additional storage space, again with associated cost. Duplication can avoid by sharing files.

                - Lead to loss of data integrity. The data is no longer consistent. For example the payroll and personnel department if a member staff moves house and the change address is communicated only to personnel and not to Payroll. So the person pay check will sent to the wrong address.

- Data dependence

                Changes of existing structure are difficult to make. Not flexible and hard to maintain. Because if changes one it will effect to others.

-Incompatible file formats

                The structure of files is embedded in the application programs; the structures are dependent on the application programming languages. For example structure of a file generated by cobol program maybe different from structure of file generated by c program.

                The incompatibility is files make them difficult to process jointly.

-Fixed queries/proliferation of application programs.

                There was no provision for security or integrity.

                Recovery, in the event of a hardware or software failure, was limited or non-existent;

                Access to the file was restricted to one user at a time. There is no provision shared access  by staff in the same department.

3.) Database: A shared collection of logically related data, and a description of this data, designed to meet the information needs of an organization.

The database represents the entities, the attributes, and logical relationships between the entities.

4.) DBMS: A software system that enables users to define, create, maintain, and control access to the database.

The DBMS is the software interacts with user’s application program and the database.

-          Allow user define database by DDL (Data Definition Language) DDL allow users to specify data types and structures and the constrains on the data stored in the database.

-          Allow insert, delete, update and retrieve data from database by DML(Data Manipulation Language). DML use query language the common query language is SQL.

-          Provide control access to database

o   Security system

o   Integrity system maintains the consistency of stored data.

o   A concurrency control system allows shared access of the database.

o   Recovery control system restore the database

o   A user-accessible catalog contains description of data in the database.

5.) Application Programs

                A computer program that interacts with the database by SQL statement to the DBMS.

Views

                Views provide security. Views can exclude data that some users should not see.

                Views provide a mechanism to customize the appearance of the database.

                Views can present a consistent.

Component of DBMS

                Hardware, Software, Data (schema, tables, attributes, relationship), People and

                Procedures

                                Log on to the DBMS

                                Application programs

                                Start & Stop DBMS

                                Make backup copies of database

                                Handle software and Hardware failure. Change the structure of table.

Advantage of DBMS

-          Control of data redundancy

-          Data consistency ស្ថិរភាព

-          More information from the same amount of data, sharing of data

-          Improved data integrity

-          Improved security

-          Enforcement of standards

-          Economy of scale

-          Balance of conflicting requirements

-          Improved data accessibility and responsiveness

-          Increased productivity

-          Improved maintenance through data independence

-          Increased concurrency

-          Improved backup and recovery services.

       Disadvantage of DBMS

      

C     Complexity , Size, Cost of DBMS, Additional Hardware cost,cost of conversion,performance, higher impact failure.