MCA DBMS Chapter-1 Notes

1. Basic ConceptsData and Information:What is data?–Data can be defined in many ways. Information science definesdataas unprocessedinformation.What is information?–Information is data that have been organized and communicated in a coherent (clear) and meaningfulmanner. –Data is converted into information, and information is converted into knowledge.–Knowledge; information evaluated and organized so that it can be used purposefully.Flow of DataDataInformationKnowledgeActionDisadvantages of File Management System:In FMS the data is stored as collection of operating system files.This approach has many drawbacks, including thefollowing:We probably do not have 500 GB of main memory to hold all the data.We must therefore store data in a storage device such as a disk or tape and bring relevant parts into main memory for processing as needed.Even if we have 500 GB of main memory, on computer systems with 32-bit addressing, we cannot refer directly to more than about 4 GB of data. So the main draw back of FMS is storage and memory management.Another drawback is the security of the data on the FMS.Duplication of data.Limited data sharing and lengthy processing time. 1 DatabaseDatabase:A database is a structured or organizedcollection of related records or data that is stored in a computer system, and which can easily be accessed, managed, and updated.Some examples of databases you may encounter in your daily life are: –A Telephone book –T.V. Guide –Airline reservation system –Motor vehicle registration records –Papers in your filing cabinet –Files on your computer hard drive.Database management system (DBMS):A DBMS is a database program or software system that stores, retrieves, and modifies data in the database on request.Examples of DBMS:Microsoft Access, FileMaker, Lotus Notes, Oracle, SQL Server, dBASE, Clipper, FoxPro e.t.c.., These DBMS software tools are used to organize that data in a flexible manner.It includes tools to add, modify or delete data from the database, ask questions (or queries) about the data stored in the database and produce reports summarizing selected contents.1.1 Need for DBMSKeep records of our:–Clients–Staff–VolunteersTo keep a record of activities and interventions;Keep sales records;Develop reports;Perform research1.2 Main Characteristics of the DatabaseSelf-describing nature of a database system:A DBMS catalogstores the descriptionof the database. This description is called meta-data.This allows the DBMS software to work with different databases.Insulation (isolation) between programs and data:Which is called as program-data independence.Allows changing data storage structures and operations without having to change the DBMS access programs.Data Abstraction:A data modelis used to hide storage details and present the users with a conceptual view of the database.Support of multiple views of the data:Each user may see a different view of the database, which describes only the data of interest to that user.Sharing of data and multiuser transaction processing : Allowing a set of concurrent users to retrieve and to update the database. Concurrency control within the DBMS guarantees that each transactionis correctly executed or completely aborted. OLTP(Online Transaction Processing)is a major part of database applications.Advantages of Using the Database ApproachControlling redundancy–Sharing of data among multiple users.Restricting unauthorized access to data.Providing persistent storage for program Objects.Providing Storage Structures (e.g. indexes) for efficient Query Processing.Backup and recovery services.Multiple interfaces to different classes of users.Complex relationships among data.Integrity constraints.Drawing inferences and actions from the stored data using deductive and active rulesAdvantages of DBMS:Independence of data and program :This is the main advantage of a database.Both the database and the user program can be altered independently of each other thus saving time and money which would be required to retain consistency. Data shareability and nonredunndanc of data:The ideal situation is to enable applications to share an integrated database containing all the data needed by the applications and thus eliminate as much as possible the need to store data redundantly. Data Integrity:With many different users sharing various portions of the database, it is impossible for each user to be responsible for the consistency of the values in the database and for maintaining the relationships of the user data items to all other data item, some of which may be unknown or even prohibited for the user to access. Centralised control:With central control of the database, the DBA can ensure that standards are followed in the representation of data. Security:Having control over the database the DBA can ensure that access to the database is through proper channels and can define the access rights of any user to any data items or defined subset of the database. The security system must prevent corruption of the existing data either accidently or maliciously. Performance and Efficiency:In view of the size of databases and of demanding database accessing requirements, good performance and efficiency are major requirements. Knowing the overall requirements of the organization, as opposed to the requirements of any individual user, the DBA can structure the database system to provide an overall service that is `best for the enterprise'. 1.3 Database UsersUsers may be divided into those who actually use and control the content also called as (“Actors on the Scene”), and those who enable the database to be developed i.e the DBMS software to be designed and implemented (called “Workers Behind the Scene”).1. Database Administrators:Responsible for authorizing access to the database, for co-ordinating and monitoring its use, acquiring software, and hardware resources, controlling its use and monitoring efficiency of operations. 2. Database Designers:Responsible to define the content, the structure, the constraints, and functions or transactions against the database.They must communicate with the end-users and understand their needs.3. End-Users:They use the data for queries, reports and some of them actually update the database content.Types of End-Users:A. Casual-Users :Access database occasionally when needed.B. Naive or Parametric Users:They make up a large section of the end-user population. They use previously well-defined functions in the form of “canned transactions” against the database. Canned transactions are standard types of queries and updates which frequently used by Naive or parametric end users to constantly querying and updating database.Examples are bank-tellers or reservation clerks who do this activity.C. SophisticatedUsers:These include business analysts, scientists, engineers, others thoroughly familiar with the system capabilities. Many use tools in the form of software packages that work closely with the stored database.D. Stand-AloneUsers:Mostly maintain personal databases using ready-to-use packaged applications. An example is a tax program user that creates his or her own internal database.1.4 2-TIER ARCHITECTURE:It is the traditional client/server environment that divides the application into GUI (client), and the Data(server).GUI can be developed using a product like VB or Power Builder.GUI is often referred to as “Presentation Layer” with business logic.Server or data tier consists of data base.In 2-tier architecture all the pieces of applications are either on the client (tier1) or server (tier2).Advantages:There are only two components the client and server to deal with.So, in most cases there will be only 2 vendors, the GUI vendor (Power soft or Micro Soft), and the DBMS vendor (Oracle corp., Micro Soft, or Sybase).Developers do not have to learn multiple products.Greatly reduces data traffic on the network.24Improved data integrity since it is all processed centrally.It allows devices to share files and resources.Development time is faster.Disadvantages:Main disadvantage is not scalablein adding more users.Hard to modify the changed business rules.3-TIER ARCHITECTURE:In 3-tier architecture application is divided into 3-logic tiers.The presentation (GUI) or User services.Business rules or Business services.Data Server or Data Services.In this type of client-server architecture business tier undergoes the maximum amount of change or modifications as business grows and change.Advantages:Increased Scalability.Improved customer service.It is easier to modify or replace any tier without affecting the other tiers.Separating the application and database functionality means better load sharing.Disadvantages:System complexity increases.For more complex applications, a 3-tier solution may fall short, and you may need a N-tiered approach, where the greatest benefit is breaking the business logic, which resides on the application tier, into a more fine-grained models.Harder to maintain.More interfaces to test.1.5 Data ModelsData Model:A set of concepts to describe the structureof a database,and certainconstraintsthat the database should obey.Data Model Operations:Operations for specifying database retrievals and updates by referring to the concepts of the data model. Operations on the data model may include basic operationsand user-defined operations.Most DBMS use relational data model, instead of Network and Hierarchical data models.Schema:A schema is description of data in terms of a data model.It can also be called as a template for describing a table/relation.Example:In relational model the schema for a relation or table specifies its name, and name of each field/attribute/column and its data type.Stu( s_id int(), sname char());Categories of Data ModelsConceptual(high-level, logical) data models:Provides the logical structure of data.Physical(low-level, internal) data models:Provide concepts that describe details of how data is stored in the computer.Externaldata models:ProvidesUser’s and Application’s view of data.user auser i/program jprogram xsub-schema asub-schema isub-schema zconceptual schemaphysical schema1.6 DBMS-3 Level ArchitectureData BaseName (20 characters) Address (40 characters)ID (10 char) Designation (15 char)Physical ViewData baseA.B.C. De Silva |222, Galle Road, Colombo |650370690V|Senior Lecturer•The DBMS must knowexact physical location of your data.EX: Employee Record:•Physical view provides the disk drives, physical addresses, indexes and pointers.•Physical Database Design is the responsibility of the Database Administrator (DBA). •No user is concerned with this view. •Physical devices contain the data.•Access methods to retrieve and update data.•Maintain and improve database performance.Conceptual/Logical View•This is a single logical description of all data elements and their relationships.•It is the result of the conceptual design which involves analysis of all users information needs and data definition of data items needs to meet them.•Like SQL CREATE TABLE statement is used to define the data elements.databaseAddress222, Galle Road, Colombo •Example of Employee table details:Name A.B.C. De Silva DesignationSenior LecturerEID650370690VExternal View•This consists of user views of the database.•Each definable user group will have its own view of the database.•Each of these views gives a user-oriented description of the data elements and relationships of which the view is composed.DatabaseDepartmentDept. of Computer ScienceExample: The user/applicant can see authorised data own format.LecturerName A.B.C. De Silva DesignationSenior LecturerAge 351.7 Data IndependenceThere are two distinct levels of data independence Logical data independence:It indicates that the conceptual schema can be changed without affecting the existing external schemas.Physical data independence:It indicates that the physical storage structures or devices used for storing the data could be changed without changing conceptual view or any of the external view.1.8 Different Data ModelsRelational Model:proposed in 1970 by E.F. Codd (IBM), first commercial system in 1981-82. Now in several commercial products (DB2, ORACLE, SQL Server, SYBASE, INFORMIX).Network Model:The first one to be implemented by Honeywell in 1964-65 (IDS System). Adopted heavily due to the support by CODASYL (CODASYL -DBTG report of 1971). Later implemented in a large variety of systems -IDMS (Cullinet -now CA), DMS 1100 (Unisys), IMAGE (H.P.), VAX -DBMS (Digital Equipment Corp.).Hierarchical Data Model:implemented in a joint effort by IBM and North American Rockwell around 1965. Resulted in the IMS family of systems. The most popular model. Other system based on this model: System 2k (SAS inc.)Object-oriented Data Model(s):several models have been proposed for implementing in a database system. One set comprises models of persistent O-O Programming Languages such as C++ (e.g., in OBJECTSTORE or VERSANT), and Smalltalk (e.g., in GEMSTONE). Additionally, systems like O2, ORION (at MCC -then ITASCA), IRIS (at H.P.-used in Open OODB). Object-Relational Models:Most Recent Trend. Started with Informix Universal Server. Exemplified in the latest versions of Oracle-10i, DB2, and SQL Server etc. systems. Different Data Models in detailsEntity-Relationship modelRelational modelOther models:–Object-oriented model–Network model–Hierarchical modelEntity-Relationship ModelExample of schema in the entity-relationship modelE-R model of real world:Entities (objects): E.g. customers, accounts, bank branch.Relationships between entities:E.g. Account A-101 is held by customer JohnsonRelationship set depositorassociates customers with accounts.Widely used for database designDatabase design in E-R model usually converted to design in the relational model (coming up next) which is used for storage and processing.Relational ModelExample of tabular data in the relational modelcustomer-nameCustomer-idcustomer-streetcustomer-cityaccount-numberJohnsonSmithJohnsonJonesSmith192-83-7465019-28-3746192-83-7465321-12-3123019-28-3746AlmaNorthAlmaMainNorthPalo AltoRyePalo AltoHarrisonRyeA-101A-215A-201A-217A-201AttributesA Sample Relational Database1.8.1 Hierarchical data model-IMSThe hierarchical data model (HDM) uses the tree concept to represent data and the relationship among data.The nodes of the tree are the record types representing the entity sets and are connected by pointers or links.A pointer or link represents a relationship between exactly two records.The hierarchical data model restricts each record type to only one parent record type.A parent record type have any number of children record types.Library database using the hierarchical modelLIBRARYBOOKCLIENTBRANCHBOOK_COPYBOOK_DUEDEPT_SECTIONEMPLOYEEEMPL_ASSIGNMENTDS_ASSIGNDBOOK_TREECLIENT_TREEBRANCH_TREEDS_TREEEMPLOYEE_TREETree conceptA tree is a collection of nodes. One node is designated as the root node; the remaining nodes form trees or subtrees.An ordered tree is a tree in which the relative order of the subtrees is significant.Traversing an ordered tree can be done in a number of ways.The order of processing the nodes of the tree depends on whether or not one processes the node before the node’s subtree and the order of processing the subtrees i.e left to right or right to left1.8.1 Network Data Model1 Expressing relationships ; DBTG set2 Expressing M : N relationships in DBTGThe Database Task Group (DBTG) is a special group within the Conference on Data Systems Languages (CODASYL), issued a final report in 1971. The report was the first standard specification for a database system.A number of commercial database management systems were based on this report.The network data model (NDM) represents data for an entity set by a logical record type.The data for an instance of the entity set is represented by a record occurrence of the record type.Consider the same example of Library databaseIn this record can be represent as followsCLIENT: This record type can be defined as followstype CLIENT = recordClient_No : String;Name :String;Address : String;endClient_No Name AddressExpressing relationships ; DBTG setClient_NoNameAddressCLIENTBOOKNOBNameAUTHORBOOKBORROWEDCLIENTBOOKCLIENTBOOKFig AFig bFig c•Applications:•Assembles in manufacturing, personnel organization in companies .•Language is simple; uses constructs like GET, GET UNIQUE, GET NEXT, GET NEXT WITHIN PARENT etc.•DISADVANTAGES:•Navigational and procedural nature of processing.•Database is visualized as a linear arrangement of records so performance slows down.•Little scope for "query optimization" .•ADVANTAGES:Network Model is able to model complex relationships and represents semantics of add/delete on the relationships.•Can handle most situations for modeling using record types and relationship types.•Language is navigational; uses constructs like FIND, FIND member, FIND owner, FIND NEXT within set, GET etc. •Programmers can do optimal navigation through the database.