{"id":33,"date":"2018-07-18T07:27:47","date_gmt":"2018-07-18T07:27:47","guid":{"rendered":"http:\/\/csp4.epgpbooks.inflibnet.ac.in\/?post_type=chapter&p=33"},"modified":"2018-07-18T07:31:48","modified_gmt":"2018-07-18T07:31:48","slug":"introduction","status":"publish","type":"chapter","link":"https:\/\/ebooks.inflibnet.ac.in\/csp4\/chapter\/introduction\/","title":{"rendered":"Introduction"},"content":{"raw":"
\r\n\r\n\u00a0 \u00a0 1. MODULE<\/strong>\r\n\r\n \r\n\r\nThis module deals with the basic concepts of data management systems.\r\n\r\n \r\n\r\n2. INTRODUCTION<\/strong>\r\n\r\n \r\n

This module explains the various data languages. Then it deals with the definition of database views. The benefits of database views are explained. Then the data models are explained in detail.<\/p>\r\n \r\n\r\n3. LEARNING OUTCOME<\/strong>\r\n\r\n \r\n

The outcome of this module is to explain the basic concepts of database management systems and clarify the misconceptions about the various topics in the subject.<\/p>\r\n \r\n\r\n4. LANGUAGES<\/strong>\r\n\r\n \r\n\r\nThe languages involved in database management systems are\r\n\r\n \r\n\r\n(i)\u00a0 Data Definition Language\r\n\r\n(ii)\u00a0 Data Manipulation Language\r\n\r\n(iii)\u00a0 Data Control Language\r\n\r\n \r\n\r\n4.1 DATA DEFINITION LANGUAGE<\/strong>\r\n\r\n \r\n

It is a language used to define the various structure of various database objects such as tables, views, schemas, indexes, etc. The common DDL commands are CREATE, ALTER and DROP.It permits the specification of data types. structures and any data constraints. All these specifications are stored in the database.DDL statements are used to build and modify the structure of your tables and other objects in the database. When you execute a DDL statement, it takes effect immediately.<\/p>\r\n \r\n\r\n\u2022 The create table statement does exactly that:\r\n\r\n \r\n\r\nCREATE TABLE <table name> (\r\n\r\n<attribute name 1> <data type 1>,\r\n\r\n...\r\n\r\n<attribute name n> <data type n>);\r\n\r\n \r\n

The data types<\/strong> that you will use most frequently are character strings, which might be called VARCHAR or CHAR for variable or fixed length strings; numeric types such as NUMBER or INTEGER, which will usually specify a precision; and DATE or related types. Data type syntax is variable from system to system; the only way to be sure is to consult the documentation for your own software.<\/p>\r\n \r\n

\u2022\u00a0 The alter table statement may be used as you have seen to specify primary and foreign key constraints, as well as to make other modifications to the table structure. Key constraints may also be specified in the CREATE TABLE statement.<\/p>\r\n \r\n\r\nALTER TABLE <table name>\r\n\r\nADD CONSTRAINT <constraint name> PRIMARY KEY (<attribute list>);\r\n\r\n \r\n

You get to specify the constraint name. Get used to following a convention of tablename_pk (for example, Customers_pk), so you can remember what you did later. The attribute list contains the one or more attributes that form this PK; if more than one, the names are separated by commas.<\/p>\r\n \r\n

\u2022\u00a0 The foreign key constraint is a bit more complicated, since we have to specify both the FK attributes in this (child) table, and the PK attributes that they link to in the parent table.<\/p>\r\n \r\n\r\nALTER TABLE <table name>\r\n\r\nADD CONSTRAINT <constraint name> FOREIGN KEY (<attribute list>)\r\n\r\nREFERENCES <parent table name> (<attribute list>);\r\n\r\n \r\n

Name the constraint in the form childtable_parenttable_fk (for example, Orders_Customers_fk). If\u00a0there is more than one attribute in the FK, all of them must be included (with commas between) in both the FK attribute list and the REFERENCES (parent table) attribute list.<\/span>You need a separate foreign key definition for each relationship in which this table is the child.<\/span><\/p>\r\n\r\n<\/div>\r\n

\r\n

\u2022\u00a0 If you totally mess things up and want to start over, you can always get rid of any object you\u2019ve created with a drop statement. The syntax is different for tables and constraints.<\/p>\r\n \r\n\r\nDROP TABLE <\/strong><table name>;\r\n\r\nALTER TABLE <table name>\r\n\r\nDROP CONSTRAINT <\/strong><constraint name>;\r\n\r\n \r\n

This is where consistent constraint naming comes in handy, so you can just remember the PK or FK name rather than remembering the syntax for looking up the names in another table. The DROP TABLE statement gets rid of its own PK constraint, but won\u2019t work until you separately drop any FK constraints (or child tables) that refer to this one. It also gets rid of all data that was contained in the table\u2014and it doesn't even ask you if you really want to do this!<\/p>\r\n \r\n\r\n\u2022\u00a0 All of the information about objects in your schema is contained, not surprisingly, in a set of tables that is called the data dictionary<\/strong>. There are hundreds of these tables most database systems, but all of them will allow you to see information about your own tables, in many cases with a graphical interface. How you do this is entirely system-dependent.\r\n\r\n \r\n\r\n4.2 DATA MANIPULATION LANGUAGE<\/strong>\r\n\r\n \r\n

It deals with the general enquiry facility of the data. DML statements are used to work with the data in tables. When you are connected to most multi-user databases (whether in a client program or by a connection from a Web page script), you are in effect working with a private copy of your tables that can\u2019t be seen by anyone else until you are finished (or tell the system that you are finished). You have already seen the SELECT statement; it is considered to be part of DML even though it just retreives data rather than modifying it.<\/p>\r\n \r\n\r\n\u2022 The insert statement is used, obviously, to add new rows to a table.\r\n\r\n \r\n\r\nINSERT INTO <table name>\r\n\r\nVALUES (<value 1>, ... <value n>);\r\n\r\n \r\n

The comma-delimited list of values must match the table structure exactly in the number of attributes and the data type of each attribute. Character type values are always enclosed in single quotes; number values are never in quotes; date values are often (but not always) in the format 'yyyy-mm-dd' (for example, '2006-11-30').<\/p>\r\n \r\n\r\nYes, you will need a separate INSERT statement for every row.\r\n\r\n \r\n\r\n\u2022 The update statement is used to change values that are already in a table.\r\n\r\n \r\n\r\nUPDATE <table name>\r\n\r\nSET <attribute> = <expression>\r\n\r\nWHERE <condition>;\r\n\r\n \r\n

The update expression can be a constant, any computed value, or even the result of a SELECT statement that returns a single row and a single column. If the WHERE clause is omitted, then the specified attribute is set to the same value in every row of the table (which is usually not what you want to do). You can also set multiple attribute values at the same time with a comma-delimited list of attribute=expression pairs.<\/p>\r\n \r\n\r\n\u2022\u00a0 The delete<\/strong> statement does just that, for rows in a table.\r\n\r\n<\/div>\r\n

\r\n\r\n\u00a0 \u00a0 \u00a0DELETE FROM <table name>\r\n\r\nWHERE <condition>;\r\n\r\n \r\n

If the WHERE clause is omitted, then every row of the table is deleted (which again is usually not what you want to do)\u2014and again, you will not get a \u2015do you really want to do this?\u2016 message.<\/p>\r\n \r\n

\u2022\u00a0 If you are using a large multi-user system, you may need to make your DML changes visible to the rest of the users of the database. Although this might be done automatically when you log out, you could also just type:<\/p>\r\n \r\n\r\nCOMMIT;\r\n\r\n \r\n

\u2022\u00a0 If you\u2019ve messed up your changes in this type of system, and want to restore your private copy of the database to the way it was before you started (this only works if you haven\u2019t already typed COMMIT), just type:<\/p>\r\n \r\n\r\nROLLBACK;\r\n\r\n \r\n

Although single-user systems don\u2019t support commit<\/strong> and rollback<\/strong> statements, they are used in large systems to control transactions<\/strong>, which are sequences of changes to the database. Transactions are frequently covered in more advanced courses.<\/p>\r\n \r\n\r\nSELF CHECK EXERCISE<\/strong>\r\n\r\n \r\n\r\nState whether the following statement is true or false.\r\n\r\nProcedural DML allows user to tell system exactly how to manipulate data.\r\n\r\nAnswer : True.\r\n\r\nExplanation : Procedural DML describes how the manipulation is to be done.\r\n\r\n \r\n\r\n4.3 DATA CONTROL LANGUAGE<\/strong>\r\n\r\n \r\n\r\nA controlled access to database may include\r\n\r\n-\u00a0\u00a0 a security system\r\n\r\n-\u00a0\u00a0 an integrity system\r\n\r\n-\u00a0\u00a0 a concurrency control system\r\n\r\n-\u00a0\u00a0 a recovery control system\r\n\r\n-\u00a0\u00a0 user accessible catalog\r\n\r\n \r\n\r\n5. DATABASE VIEWS<\/strong>\r\n\r\n \r\n\r\nThe following unit deals with the database views.\r\n\r\n \r\n\r\n5.1 VIEWS<\/strong>\r\n\r\n \r\n\r\nAllows each user to have his or her own view of the database.\r\n\r\nA view is essentially some subset of the database.\r\n\r\n \r\n\r\n5.2 BENEFITS<\/strong>\r\n\r\n \r\n\r\nReduce complexity\r\n\r\nProvide a level of security\r\n\r\nProvide a mechanism to customize the appearance of the database\r\n\r\nPresent a consistent, unchanging picture of the structure of the database, even if the underlying database is changed.\r\n\r\n \r\n\r\n \r\n\r\n6. COMPONENTS OF DBMS<\/strong>\r\n\r\n<\/div>\r\n

\"\"<\/p>\r\n\r\n

\r\n\r\n\u00a0 \u00a0 Hardware : can range from a PC to a network of computers.\r\n\r\nSoftware : DBMS, operating system, network software (if necessary) and also the application programs.\r\n\r\nData - used by the organization and a description of this data called the schema.\r\n\r\nProcedures - instructions and rules that should be applied to the design and use of the database and DBMS.\r\n\r\nPeople - database administrator, database programmer, database user\r\n\r\n \r\n\r\n7. ROLES<\/strong>\r\n\r\n \r\n\r\nData Administrator(DA)\r\n\r\nDatabase Administrator(DBA) \u2013 where exactly to store\r\n\r\nDatabase Designers(Logical and Physical) \u2013 structure of the database\r\n\r\nApplication Programmers \u2013 merge the differences between the exggternal and internal level\r\n\r\nEnd users(naive and sophisticated) \u2013 never exposed to this kind of system, to who know the query language.\r\n\r\n \r\n\r\n8. DATABASE MODELS<\/strong>\r\n\r\n \r\n\r\nWe have to talk about the evolution of database.\r\n

\"\"<\/p>\r\n \r\n\r\n \r\n\r\n8.1 INFORMATION MODEL<\/strong>\r\n\r\n \r\n\r\nNetwork (CODASYL) \u2013 1970s\r\n\r\nHierarchical (IMS) \u2013 Late 1960 and 1970s\r\n\r\nRelational \u2013 1970s and early 1980s\r\n\r\nEntity \u2013 Relationship \u2013 1970s\r\n\r\nExtended relational \u2013 1980s\r\n\r\nSemantic 1970s and early 1980s\r\n\r\nObject oriented \u2013 Late 1980's and early 1990's\r\n\r\nObject relational \u2013 Late 1980's and early 1990's<\/span>\r\n\r\nSemi structured \u2013 Late 1990's<\/span>\r\n\r\n \r\n\r\n<\/div>\r\n

\r\n\r\n\u00a0 \u00a0 SELF CHECK EXERCISE<\/strong>\r\n\r\n \r\n\r\nState whether the following statement is true or false.\r\n\r\nCategories of data model does not include object based.\r\n\r\nAnswer : False.\r\n\r\nExplanation : Object based, record based, physical are some of the categories of data model.\r\n\r\n \r\n\r\n8.1.1 NETWORK MODEL<\/strong>\r\n\r\n \r\n

The popularity of the network data model coincided with the popularity of the hierarchical data model. Some data were more naturally modeled with more than one parent per child. So, the network model permitted the modeling of many-to-many relationships in data. In 1971, the Conference on Data Systems Languages (CODASYL) formally defined the network model. The basic data modeling construct in the network model is the set construct. A set consists of an owner record type, a set name, and a member record type. A member record type can have that role in more than one set, hence the multiparent concept is supported. An owner record type can also be a member or owner in another set. The data model is a simple network, and link and intersection record types (called junction records by IDMS) may exist, as well as sets between them . Thus, the complete network of relationships is represented by several pairwise sets; in each set some (one) record type is owner (at the tail of the network arrow) and one or more record types are members (at the head of the relationship arrow). Usually, a set defines a 1:M relationship, although 1:1 is permitted. The CODASYL network model is based on mathematical set theory.<\/p>\r\n \r\n\r\n8.1.2 HIERARCHICAL MODEL<\/strong>\r\n\r\n \r\n

The hierarchical data model organizes data in a tree structure. There is a hierarchy of parent and child data segments. This structure implies that a record can have repeating information, generally in the child data segments. Data in a series of records, which have a set of field values attached to it. It collects all the instances of a specific record together as a record type. These record types are the equivalent of tables in the relational model, and with the individual records being the equivalent of rows. To create links between these record types, the hierarchical model uses Parent Child Relationships. These are a 1:N mapping between record types. This is done by using trees, like set theory used in the relational model, \"borrowed\" from maths. For example, an organization might store information about an employee, such as name, employee number, department, salary. The organization might also store information about an employee's children, such as name and date of birth. The employee and children data forms a hierarchy, where the employee data represents the parent segment and the children data represents the child segment. If an employee has three children, then there would be three child segments associated with one employee segment. In a hierarchical database the parent-child relationship is one to many. This restricts a child segment to having only one parent segment. Hierarchical DBMSs were popular from the late 1960s, with the introduction of IBM's Information Management System (IMS) DBMS, through the 1970s.<\/p>\r\n \r\n\r\n8.1.3 RELATIONAL MODEL<\/strong>\r\n\r\n \r\n

A relational database is based on the relational model developed by E.F. Codd. A relational database allows the definition of data structures, storage and retrieval operations and integrity constraints. In such a database the data and relations between them are organized into tables. A table is a collection of records and each record in a table contains the same fields. The contents of a table can be\u00a0permanently saved for future use.<\/span><\/p>\r\n\r\n<\/div>\r\n

\r\n\r\n\u00a0 \u00a0 Properties of the relational database model\r\n\r\nProperties of Relational Tables:\r\n\r\n1.\u00a0 Data is presented as a collection of relations.\r\n\r\n2.\u00a0 Each relation is depicted as a table.\r\n

3.\u00a0 Columns are attributes that belong to the entity modeled by the table (ex. In a student table, you could\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 have name, address, student ID, major, etc.).<\/p>\r\n

4.\u00a0 Each row (\"tuple\") represents a single entity (ex. In a student table, John Smith, 14 Oak St, 9002342, Accounting, would represent one student entity).<\/p>\r\n

5.\u00a0 Every table has a set of attributes that taken together as a \"key\" (technically, a \"superkey\") uniquely identifies each entity (Ex. In the student table, \u2015student ID\u2016 would uniquely identify each student \u2013 no two students would have the same student ID).<\/p>\r\n \r\n\r\nOverview<\/strong>\r\n\r\n \r\n

Certain fields may be designated as keys, which means that searches for specific values of that field will use indexing to speed them up and more importantly, uniquely identify each entity. There are many types of keys, however, quite possibly the two most important are the primary key and the foreign key. The primary key is what uniquely identifies each entity. The foreign key is a primary key of one table that also sits in another table. Ultimately, the use of foreign keys is the heart of the relational database model. This linkage that the foreign key provides is what allows tables to pull data from each other and link data together. Where fields in two different tables take values from the same set, a join operation can be performed to select related records in the two tables by matching values in those fields. Most often, but not always, the fields will have the same name in both tables. For example, an \"orders\" table might contain (customer-ID \u2013 primary key, product-code \u2013 foreign key) pairs and a \"products\" table might contain (product-code \u2013 primary key, price) pairs so to calculate a given customer's bill you would sum the prices of all products ordered by that customer by joining on the product-code fields of the two tables. This can be extended to joining multiple tables on multiple fields. Because these relationships are only specified at retrieval time, relational databases are classed as dynamic database management system.<\/p>\r\n \r\n\r\n8.1.4 OBJECT ORIENTED MODEL<\/strong>\r\n\r\n \r\n

Object DBMSs add database functionality to object programming languages. They bring much more than persistent storage of programming language objects. Object DBMSs extend the semantics of the C++, Smalltalk and Java object programming languages to provide full-featured database programming capability, while retaining native language compatibility. A major benefit of this approach is the unification of the application and database development into a seamless data model and language environment. As a result, applications require less code, use more natural data modeling, and code bases are easier to maintain. Object developers can write complete database applications with a modest amount of additional effort.<\/p>\r\n \r\n

According to Rao (1994), \"The object-oriented database (OODB) paradigm is the combination of object-oriented programming language (OOPL) systems and persistent systems. The power of the OODB comes from the seamless treatment of both persistent data, as found in databases, and\u00a0transient data, as found in executing programs.\"<\/span><\/p>\r\n\r\n<\/div>\r\n

\r\n

In contrast to a relational DBMS where a complex data structure must be flattened out to fit into tables or joined together from those tables to form the in-memory structure, object DBMSs have no performance overhead to store or retrieve a web or hierarchy of interrelated objects. This one-to-one mapping of object programming language objects to database objects has two benefits over other storage approaches: it provides higher performance management of objects, and it enables better management of the complex interrelationships between objects. This makes object DBMSs better suited to support applications such as financial portfolio risk analysis systems, telecommunications service applications, world wide web document structures, design and manufacturing systems, and hospital patient record systems, which have complex relationships between data.<\/p>\r\n \r\n\r\n8.1.5 OBJECT RELATIONAL MODEL<\/strong>\r\n\r\n \r\n

Object\/relational database management systems (ORDBMSs) add new object storage capabilities to the relational systems at the core of modern information systems. These new facilities integrate management of traditional fielded data, complex objects such as time-series and geospatial data and diverse binary media such as audio, video, images, and applets. By encapsulating methods with data structures, an ORDBMS server can execute complex analytical and data manipulation operations to search and transform multimedia and other complex objects.<\/p>\r\n \r\n

As an evolutionary technology, the object\/relational (OR) approach has inherited the robust transaction- and performance-management features of it s relational ancestor and the flexibility of its object-oriented cousin. Database designers can work with familiar tabular structures and data definition languages (DDLs) while assimilating new object-management possibilities. Query and procedural languages and call interfaces in ORDBMSs are familiar: SQL3, vendor procedural languages, and ODBC, JDBC, and proprietary call interfaces are all extensions of RDBMS languages and interfaces. And the leading vendors are, of course, quite well known: IBM, Inform ix, and Oracle.<\/p>\r\n \r\n\r\n8.1.6 SEMI STRUCTURED MODEL<\/strong>\r\n\r\n \r\n

In semistructured data model, the information that is normally associated with a schema is contained within the data, which is sometimes called ``self-describing''. In such database there is no clear separation between the data and the schema, and the degree to which it is structured depends on the application. In some forms of semistructured data there is no separate schema, in others it exists but only places loose constraints on the data. Semi-structured data is naturally modelled in terms of graphs which contain labels which give semantics to its underlying structure. Such databases subsume the modelling power of recent extensions of flat relational databases, to nested databases which allow the nesting (or encapsulation) of entities, and to object databases which, in addition, allow cyclic references between objects.<\/p>\r\n \r\n

Semistructured data has recently emerged as an important topic of study for a variety of reasons. First, there are data sources such as the Web, which we would like to treat as databases but which cannot be constrained by a schema. Second, it may be desirable to have an extremely flexible format for data exchange between disparate databases. Third, even when dealing with structured data, it may be helpful to view it as semistructured for the purposes of browsing.<\/p>\r\n\r\n<\/div>\r\n

\r\n\r\n\u00a0 \u00a0 \u00a08.1.7 ER DIAGRAM<\/strong>\r\n\r\n \r\n

An Entity Relationship Diagram (ERD) is a data model describing how entities (or concepts or things) relate to one another. When created by business analysts, ERDs can be used to understand the business domain, clarify business terminology, and connect business concepts to database structures.<\/p>\r\n \r\n

Essentially, a conceptual or logical ERD will visually show how the terms in your glossary relate to one another. They are especially helpful in clarifying information models for relational databases and helping business users understand database structures at a high level and without details<\/em>.<\/p>\r\n \r\n\r\nThe Key Elements of an ERD<\/strong>\r\n\r\n \r\n\r\nAlthough they look complex, an ERD has 3 simple components.\r\n

    \r\n \t
  • \u00a0Entities <\/strong>\u2013 An entity is a thing. In business domain terms, it\u2019s a concept or glossary-level term. In relational database terms, it\u2019s the table.<\/li>\r\n \t
  • Relationships <\/strong>\u2013 The real insight from this type of diagram comes when we see how entities relate to one another, or relationships. Relationships can be thought of as verbs that link two or more nouns. Relationships can be modeled numerically, using the multiplicity syntax from a class diagram, or using Crows Foot Notation.<\/li>\r\n \t
  • Attributes <\/strong>\u2013 Within each entity, there can be more than one attribute. Attributes provide detailed information about the concept. In a relational database, attributes are represented by the fields where the information inside a record is held.<\/li>\r\n<\/ul>\r\n

    \u00a0Like any analysis model, creating an ERD is an iterative process that involves elicitation, analysis, and review with stakeholders. Here are some steps you\u2019ll go through as you create an ERD.<\/p>\r\n \r\n

    \u00a0 1. Create boxes for each entity or primary business concept relevant to your model.<\/p>\r\n

    \u00a02. Model the relationships between each by drawing lines to connect related entities. Label the relationships using verbs or a numeric notation. Crows Foot Notation is common for ERDs, but you can also use the multiplicity notation from UML\u2019s Class Diagrams.<\/p>\r\n

    3. Identify relevant attributes within each entity. For a conceptual model, focus on the most important attributes. As your model evolves, make your attribute lists more specific.<\/p>\r\n

    4.\u00a0 Review your model with business and technical stakeholders.<\/p>\r\n

    5.\u00a0 Repeat until your domain is well-represented by your model.<\/p>\r\n \r\n

    As an end result, you\u2019ll have clearly defined how different business concepts relate to one another, and created a solid conceptual foundation for designing a relational database to support your business requirements, as well as a way to get business and technical stakeholders on the same page about how these concepts relate.<\/p>\r\n \r\n\r\n9. ADVANTAGES OF DBMS<\/strong>\r\n\r\n<\/div>\r\nBalance conflicting requirements\r\n\r\nImproved data accessibility and reponsiveness\r\n\r\nIncreased productivity\r\n\r\nImproved maintenance through data independance\r\n\r\nIncreased concurrency\r\n\r\nImproved backup and recovery services.\r\n

      \r\n \t
    1. SUMMARY\u00a0<\/strong><\/li>\r\n<\/ol>\r\nThis session we have dealt with the introduction to database management systems.\r\n\r\nWe have also discussed about the roles and components of database management system.\r\n\r\nThe various information models have been discussed. We have listed the advantages of DBMS.\r\n

      \"\"<\/p>","rendered":"

      \n

      \u00a0 \u00a0 1. MODULE<\/strong><\/p>\n

       <\/p>\n

      This module deals with the basic concepts of data management systems.<\/p>\n

       <\/p>\n

      2. INTRODUCTION<\/strong><\/p>\n

       <\/p>\n

      This module explains the various data languages. Then it deals with the definition of database views. The benefits of database views are explained. Then the data models are explained in detail.<\/p>\n

       <\/p>\n

      3. LEARNING OUTCOME<\/strong><\/p>\n

       <\/p>\n

      The outcome of this module is to explain the basic concepts of database management systems and clarify the misconceptions about the various topics in the subject.<\/p>\n

       <\/p>\n

      4. LANGUAGES<\/strong><\/p>\n

       <\/p>\n

      The languages involved in database management systems are<\/p>\n

       <\/p>\n

      (i)\u00a0 Data Definition Language<\/p>\n

      (ii)\u00a0 Data Manipulation Language<\/p>\n

      (iii)\u00a0 Data Control Language<\/p>\n

       <\/p>\n

      4.1 DATA DEFINITION LANGUAGE<\/strong><\/p>\n

       <\/p>\n

      It is a language used to define the various structure of various database objects such as tables, views, schemas, indexes, etc. The common DDL commands are CREATE, ALTER and DROP.It permits the specification of data types. structures and any data constraints. All these specifications are stored in the database.DDL statements are used to build and modify the structure of your tables and other objects in the database. When you execute a DDL statement, it takes effect immediately.<\/p>\n

       <\/p>\n

      \u2022 The create table statement does exactly that:<\/p>\n

       <\/p>\n

      CREATE TABLE <table name> (<\/p>\n

      <attribute name 1> <data type 1>,<\/p>\n

      …<\/p>\n

      <attribute name n> <data type n>);<\/p>\n

       <\/p>\n

      The data types<\/strong> that you will use most frequently are character strings, which might be called VARCHAR or CHAR for variable or fixed length strings; numeric types such as NUMBER or INTEGER, which will usually specify a precision; and DATE or related types. Data type syntax is variable from system to system; the only way to be sure is to consult the documentation for your own software.<\/p>\n

       <\/p>\n

      \u2022\u00a0 The alter table statement may be used as you have seen to specify primary and foreign key constraints, as well as to make other modifications to the table structure. Key constraints may also be specified in the CREATE TABLE statement.<\/p>\n

       <\/p>\n

      ALTER TABLE <table name><\/p>\n

      ADD CONSTRAINT <constraint name> PRIMARY KEY (<attribute list>);<\/p>\n

       <\/p>\n

      You get to specify the constraint name. Get used to following a convention of tablename_pk (for example, Customers_pk), so you can remember what you did later. The attribute list contains the one or more attributes that form this PK; if more than one, the names are separated by commas.<\/p>\n

       <\/p>\n

      \u2022\u00a0 The foreign key constraint is a bit more complicated, since we have to specify both the FK attributes in this (child) table, and the PK attributes that they link to in the parent table.<\/p>\n

       <\/p>\n

      ALTER TABLE <table name><\/p>\n

      ADD CONSTRAINT <constraint name> FOREIGN KEY (<attribute list>)<\/p>\n

      REFERENCES <parent table name> (<attribute list>);<\/p>\n

       <\/p>\n

      Name the constraint in the form childtable_parenttable_fk (for example, Orders_Customers_fk). If\u00a0there is more than one attribute in the FK, all of them must be included (with commas between) in both the FK attribute list and the REFERENCES (parent table) attribute list.<\/span>You need a separate foreign key definition for each relationship in which this table is the child.<\/span><\/p>\n<\/div>\n

      \n

      \u2022\u00a0 If you totally mess things up and want to start over, you can always get rid of any object you\u2019ve created with a drop statement. The syntax is different for tables and constraints.<\/p>\n

       <\/p>\n

      DROP TABLE <\/strong><table name>;<\/p>\n

      ALTER TABLE <table name><\/p>\n

      DROP CONSTRAINT <\/strong><constraint name>;<\/p>\n

       <\/p>\n

      This is where consistent constraint naming comes in handy, so you can just remember the PK or FK name rather than remembering the syntax for looking up the names in another table. The DROP TABLE statement gets rid of its own PK constraint, but won\u2019t work until you separately drop any FK constraints (or child tables) that refer to this one. It also gets rid of all data that was contained in the table\u2014and it doesn’t even ask you if you really want to do this!<\/p>\n

       <\/p>\n

      \u2022\u00a0 All of the information about objects in your schema is contained, not surprisingly, in a set of tables that is called the data dictionary<\/strong>. There are hundreds of these tables most database systems, but all of them will allow you to see information about your own tables, in many cases with a graphical interface. How you do this is entirely system-dependent.<\/p>\n

       <\/p>\n

      4.2 DATA MANIPULATION LANGUAGE<\/strong><\/p>\n

       <\/p>\n

      It deals with the general enquiry facility of the data. DML statements are used to work with the data in tables. When you are connected to most multi-user databases (whether in a client program or by a connection from a Web page script), you are in effect working with a private copy of your tables that can\u2019t be seen by anyone else until you are finished (or tell the system that you are finished). You have already seen the SELECT statement; it is considered to be part of DML even though it just retreives data rather than modifying it.<\/p>\n

       <\/p>\n

      \u2022 The insert statement is used, obviously, to add new rows to a table.<\/p>\n

       <\/p>\n

      INSERT INTO <table name><\/p>\n

      VALUES (<value 1>, … <value n>);<\/p>\n

       <\/p>\n

      The comma-delimited list of values must match the table structure exactly in the number of attributes and the data type of each attribute. Character type values are always enclosed in single quotes; number values are never in quotes; date values are often (but not always) in the format ‘yyyy-mm-dd’ (for example, ‘2006-11-30’).<\/p>\n

       <\/p>\n

      Yes, you will need a separate INSERT statement for every row.<\/p>\n

       <\/p>\n

      \u2022 The update statement is used to change values that are already in a table.<\/p>\n

       <\/p>\n

      UPDATE <table name><\/p>\n

      SET <attribute> = <expression><\/p>\n

      WHERE <condition>;<\/p>\n

       <\/p>\n

      The update expression can be a constant, any computed value, or even the result of a SELECT statement that returns a single row and a single column. If the WHERE clause is omitted, then the specified attribute is set to the same value in every row of the table (which is usually not what you want to do). You can also set multiple attribute values at the same time with a comma-delimited list of attribute=expression pairs.<\/p>\n

       <\/p>\n

      \u2022\u00a0 The delete<\/strong> statement does just that, for rows in a table.<\/p>\n<\/div>\n

      \n

      \u00a0 \u00a0 \u00a0DELETE FROM <table name><\/p>\n

      WHERE <condition>;<\/p>\n

       <\/p>\n

      If the WHERE clause is omitted, then every row of the table is deleted (which again is usually not what you want to do)\u2014and again, you will not get a \u2015do you really want to do this?\u2016 message.<\/p>\n

       <\/p>\n

      \u2022\u00a0 If you are using a large multi-user system, you may need to make your DML changes visible to the rest of the users of the database. Although this might be done automatically when you log out, you could also just type:<\/p>\n

       <\/p>\n

      COMMIT;<\/p>\n

       <\/p>\n

      \u2022\u00a0 If you\u2019ve messed up your changes in this type of system, and want to restore your private copy of the database to the way it was before you started (this only works if you haven\u2019t already typed COMMIT), just type:<\/p>\n

       <\/p>\n

      ROLLBACK;<\/p>\n

       <\/p>\n

      Although single-user systems don\u2019t support commit<\/strong> and rollback<\/strong> statements, they are used in large systems to control transactions<\/strong>, which are sequences of changes to the database. Transactions are frequently covered in more advanced courses.<\/p>\n

       <\/p>\n

      SELF CHECK EXERCISE<\/strong><\/p>\n

       <\/p>\n

      State whether the following statement is true or false.<\/p>\n

      Procedural DML allows user to tell system exactly how to manipulate data.<\/p>\n

      Answer : True.<\/p>\n

      Explanation : Procedural DML describes how the manipulation is to be done.<\/p>\n

       <\/p>\n

      4.3 DATA CONTROL LANGUAGE<\/strong><\/p>\n

       <\/p>\n

      A controlled access to database may include<\/p>\n

      –\u00a0\u00a0 a security system<\/p>\n

      –\u00a0\u00a0 an integrity system<\/p>\n

      –\u00a0\u00a0 a concurrency control system<\/p>\n

      –\u00a0\u00a0 a recovery control system<\/p>\n

      –\u00a0\u00a0 user accessible catalog<\/p>\n

       <\/p>\n

      5. DATABASE VIEWS<\/strong><\/p>\n

       <\/p>\n

      The following unit deals with the database views.<\/p>\n

       <\/p>\n

      5.1 VIEWS<\/strong><\/p>\n

       <\/p>\n

      Allows each user to have his or her own view of the database.<\/p>\n

      A view is essentially some subset of the database.<\/p>\n

       <\/p>\n

      5.2 BENEFITS<\/strong><\/p>\n

       <\/p>\n

      Reduce complexity<\/p>\n

      Provide a level of security<\/p>\n

      Provide a mechanism to customize the appearance of the database<\/p>\n

      Present a consistent, unchanging picture of the structure of the database, even if the underlying database is changed.<\/p>\n

       <\/p>\n

       <\/p>\n

      6. COMPONENTS OF DBMS<\/strong><\/p>\n<\/div>\n

      \"\"<\/p>\n

      \n

      \u00a0 \u00a0 Hardware : can range from a PC to a network of computers.<\/p>\n

      Software : DBMS, operating system, network software (if necessary) and also the application programs.<\/p>\n

      Data – used by the organization and a description of this data called the schema.<\/p>\n

      Procedures – instructions and rules that should be applied to the design and use of the database and DBMS.<\/p>\n

      People – database administrator, database programmer, database user<\/p>\n

       <\/p>\n

      7. ROLES<\/strong><\/p>\n

       <\/p>\n

      Data Administrator(DA)<\/p>\n

      Database Administrator(DBA) \u2013 where exactly to store<\/p>\n

      Database Designers(Logical and Physical) \u2013 structure of the database<\/p>\n

      Application Programmers \u2013 merge the differences between the exggternal and internal level<\/p>\n

      End users(naive and sophisticated) \u2013 never exposed to this kind of system, to who know the query language.<\/p>\n

       <\/p>\n

      8. DATABASE MODELS<\/strong><\/p>\n

       <\/p>\n

      We have to talk about the evolution of database.<\/p>\n

      \"\"<\/p>\n

       <\/p>\n

       <\/p>\n

      8.1 INFORMATION MODEL<\/strong><\/p>\n

       <\/p>\n

      Network (CODASYL) \u2013 1970s<\/p>\n

      Hierarchical (IMS) \u2013 Late 1960 and 1970s<\/p>\n

      Relational \u2013 1970s and early 1980s<\/p>\n

      Entity \u2013 Relationship \u2013 1970s<\/p>\n

      Extended relational \u2013 1980s<\/p>\n

      Semantic 1970s and early 1980s<\/p>\n

      Object oriented \u2013 Late 1980’s and early 1990’s<\/p>\n

      Object relational \u2013 Late 1980’s and early 1990’s<\/span><\/p>\n

      Semi structured \u2013 Late 1990’s<\/span><\/p>\n

       <\/p>\n<\/div>\n

      \n

      \u00a0 \u00a0 SELF CHECK EXERCISE<\/strong><\/p>\n

       <\/p>\n

      State whether the following statement is true or false.<\/p>\n

      Categories of data model does not include object based.<\/p>\n

      Answer : False.<\/p>\n

      Explanation : Object based, record based, physical are some of the categories of data model.<\/p>\n

       <\/p>\n

      8.1.1 NETWORK MODEL<\/strong><\/p>\n

       <\/p>\n

      The popularity of the network data model coincided with the popularity of the hierarchical data model. Some data were more naturally modeled with more than one parent per child. So, the network model permitted the modeling of many-to-many relationships in data. In 1971, the Conference on Data Systems Languages (CODASYL) formally defined the network model. The basic data modeling construct in the network model is the set construct. A set consists of an owner record type, a set name, and a member record type. A member record type can have that role in more than one set, hence the multiparent concept is supported. An owner record type can also be a member or owner in another set. The data model is a simple network, and link and intersection record types (called junction records by IDMS) may exist, as well as sets between them . Thus, the complete network of relationships is represented by several pairwise sets; in each set some (one) record type is owner (at the tail of the network arrow) and one or more record types are members (at the head of the relationship arrow). Usually, a set defines a 1:M relationship, although 1:1 is permitted. The CODASYL network model is based on mathematical set theory.<\/p>\n

       <\/p>\n

      8.1.2 HIERARCHICAL MODEL<\/strong><\/p>\n

       <\/p>\n

      The hierarchical data model organizes data in a tree structure. There is a hierarchy of parent and child data segments. This structure implies that a record can have repeating information, generally in the child data segments. Data in a series of records, which have a set of field values attached to it. It collects all the instances of a specific record together as a record type. These record types are the equivalent of tables in the relational model, and with the individual records being the equivalent of rows. To create links between these record types, the hierarchical model uses Parent Child Relationships. These are a 1:N mapping between record types. This is done by using trees, like set theory used in the relational model, “borrowed” from maths. For example, an organization might store information about an employee, such as name, employee number, department, salary. The organization might also store information about an employee’s children, such as name and date of birth. The employee and children data forms a hierarchy, where the employee data represents the parent segment and the children data represents the child segment. If an employee has three children, then there would be three child segments associated with one employee segment. In a hierarchical database the parent-child relationship is one to many. This restricts a child segment to having only one parent segment. Hierarchical DBMSs were popular from the late 1960s, with the introduction of IBM’s Information Management System (IMS) DBMS, through the 1970s.<\/p>\n

       <\/p>\n

      8.1.3 RELATIONAL MODEL<\/strong><\/p>\n

       <\/p>\n

      A relational database is based on the relational model developed by E.F. Codd. A relational database allows the definition of data structures, storage and retrieval operations and integrity constraints. In such a database the data and relations between them are organized into tables. A table is a collection of records and each record in a table contains the same fields. The contents of a table can be\u00a0permanently saved for future use.<\/span><\/p>\n<\/div>\n

      \n

      \u00a0 \u00a0 Properties of the relational database model<\/p>\n

      Properties of Relational Tables:<\/p>\n

      1.\u00a0 Data is presented as a collection of relations.<\/p>\n

      2.\u00a0 Each relation is depicted as a table.<\/p>\n

      3.\u00a0 Columns are attributes that belong to the entity modeled by the table (ex. In a student table, you could\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 have name, address, student ID, major, etc.).<\/p>\n

      4.\u00a0 Each row (“tuple”) represents a single entity (ex. In a student table, John Smith, 14 Oak St, 9002342, Accounting, would represent one student entity).<\/p>\n

      5.\u00a0 Every table has a set of attributes that taken together as a “key” (technically, a “superkey”) uniquely identifies each entity (Ex. In the student table, \u2015student ID\u2016 would uniquely identify each student \u2013 no two students would have the same student ID).<\/p>\n

       <\/p>\n

      Overview<\/strong><\/p>\n

       <\/p>\n

      Certain fields may be designated as keys, which means that searches for specific values of that field will use indexing to speed them up and more importantly, uniquely identify each entity. There are many types of keys, however, quite possibly the two most important are the primary key and the foreign key. The primary key is what uniquely identifies each entity. The foreign key is a primary key of one table that also sits in another table. Ultimately, the use of foreign keys is the heart of the relational database model. This linkage that the foreign key provides is what allows tables to pull data from each other and link data together. Where fields in two different tables take values from the same set, a join operation can be performed to select related records in the two tables by matching values in those fields. Most often, but not always, the fields will have the same name in both tables. For example, an “orders” table might contain (customer-ID \u2013 primary key, product-code \u2013 foreign key) pairs and a “products” table might contain (product-code \u2013 primary key, price) pairs so to calculate a given customer’s bill you would sum the prices of all products ordered by that customer by joining on the product-code fields of the two tables. This can be extended to joining multiple tables on multiple fields. Because these relationships are only specified at retrieval time, relational databases are classed as dynamic database management system.<\/p>\n

       <\/p>\n

      8.1.4 OBJECT ORIENTED MODEL<\/strong><\/p>\n

       <\/p>\n

      Object DBMSs add database functionality to object programming languages. They bring much more than persistent storage of programming language objects. Object DBMSs extend the semantics of the C++, Smalltalk and Java object programming languages to provide full-featured database programming capability, while retaining native language compatibility. A major benefit of this approach is the unification of the application and database development into a seamless data model and language environment. As a result, applications require less code, use more natural data modeling, and code bases are easier to maintain. Object developers can write complete database applications with a modest amount of additional effort.<\/p>\n

       <\/p>\n

      According to Rao (1994), “The object-oriented database (OODB) paradigm is the combination of object-oriented programming language (OOPL) systems and persistent systems. The power of the OODB comes from the seamless treatment of both persistent data, as found in databases, and\u00a0transient data, as found in executing programs.”<\/span><\/p>\n<\/div>\n

      \n

      In contrast to a relational DBMS where a complex data structure must be flattened out to fit into tables or joined together from those tables to form the in-memory structure, object DBMSs have no performance overhead to store or retrieve a web or hierarchy of interrelated objects. This one-to-one mapping of object programming language objects to database objects has two benefits over other storage approaches: it provides higher performance management of objects, and it enables better management of the complex interrelationships between objects. This makes object DBMSs better suited to support applications such as financial portfolio risk analysis systems, telecommunications service applications, world wide web document structures, design and manufacturing systems, and hospital patient record systems, which have complex relationships between data.<\/p>\n

       <\/p>\n

      8.1.5 OBJECT RELATIONAL MODEL<\/strong><\/p>\n

       <\/p>\n

      Object\/relational database management systems (ORDBMSs) add new object storage capabilities to the relational systems at the core of modern information systems. These new facilities integrate management of traditional fielded data, complex objects such as time-series and geospatial data and diverse binary media such as audio, video, images, and applets. By encapsulating methods with data structures, an ORDBMS server can execute complex analytical and data manipulation operations to search and transform multimedia and other complex objects.<\/p>\n

       <\/p>\n

      As an evolutionary technology, the object\/relational (OR) approach has inherited the robust transaction- and performance-management features of it s relational ancestor and the flexibility of its object-oriented cousin. Database designers can work with familiar tabular structures and data definition languages (DDLs) while assimilating new object-management possibilities. Query and procedural languages and call interfaces in ORDBMSs are familiar: SQL3, vendor procedural languages, and ODBC, JDBC, and proprietary call interfaces are all extensions of RDBMS languages and interfaces. And the leading vendors are, of course, quite well known: IBM, Inform ix, and Oracle.<\/p>\n

       <\/p>\n

      8.1.6 SEMI STRUCTURED MODEL<\/strong><\/p>\n

       <\/p>\n

      In semistructured data model, the information that is normally associated with a schema is contained within the data, which is sometimes called “self-describing”. In such database there is no clear separation between the data and the schema, and the degree to which it is structured depends on the application. In some forms of semistructured data there is no separate schema, in others it exists but only places loose constraints on the data. Semi-structured data is naturally modelled in terms of graphs which contain labels which give semantics to its underlying structure. Such databases subsume the modelling power of recent extensions of flat relational databases, to nested databases which allow the nesting (or encapsulation) of entities, and to object databases which, in addition, allow cyclic references between objects.<\/p>\n

       <\/p>\n

      Semistructured data has recently emerged as an important topic of study for a variety of reasons. First, there are data sources such as the Web, which we would like to treat as databases but which cannot be constrained by a schema. Second, it may be desirable to have an extremely flexible format for data exchange between disparate databases. Third, even when dealing with structured data, it may be helpful to view it as semistructured for the purposes of browsing.<\/p>\n<\/div>\n

      \n

      \u00a0 \u00a0 \u00a08.1.7 ER DIAGRAM<\/strong><\/p>\n

       <\/p>\n

      An Entity Relationship Diagram (ERD) is a data model describing how entities (or concepts or things) relate to one another. When created by business analysts, ERDs can be used to understand the business domain, clarify business terminology, and connect business concepts to database structures.<\/p>\n

       <\/p>\n

      Essentially, a conceptual or logical ERD will visually show how the terms in your glossary relate to one another. They are especially helpful in clarifying information models for relational databases and helping business users understand database structures at a high level and without details<\/em>.<\/p>\n

       <\/p>\n

      The Key Elements of an ERD<\/strong><\/p>\n

       <\/p>\n

      Although they look complex, an ERD has 3 simple components.<\/p>\n

        \n
      • \u00a0Entities <\/strong>\u2013 An entity is a thing. In business domain terms, it\u2019s a concept or glossary-level term. In relational database terms, it\u2019s the table.<\/li>\n
      • Relationships <\/strong>\u2013 The real insight from this type of diagram comes when we see how entities relate to one another, or relationships. Relationships can be thought of as verbs that link two or more nouns. Relationships can be modeled numerically, using the multiplicity syntax from a class diagram, or using Crows Foot Notation.<\/li>\n
      • Attributes <\/strong>\u2013 Within each entity, there can be more than one attribute. Attributes provide detailed information about the concept. In a relational database, attributes are represented by the fields where the information inside a record is held.<\/li>\n<\/ul>\n

        \u00a0Like any analysis model, creating an ERD is an iterative process that involves elicitation, analysis, and review with stakeholders. Here are some steps you\u2019ll go through as you create an ERD.<\/p>\n

         <\/p>\n

        \u00a0 1. Create boxes for each entity or primary business concept relevant to your model.<\/p>\n

        \u00a02. Model the relationships between each by drawing lines to connect related entities. Label the relationships using verbs or a numeric notation. Crows Foot Notation is common for ERDs, but you can also use the multiplicity notation from UML\u2019s Class Diagrams.<\/p>\n

        3. Identify relevant attributes within each entity. For a conceptual model, focus on the most important attributes. As your model evolves, make your attribute lists more specific.<\/p>\n

        4.\u00a0 Review your model with business and technical stakeholders.<\/p>\n

        5.\u00a0 Repeat until your domain is well-represented by your model.<\/p>\n

         <\/p>\n

        As an end result, you\u2019ll have clearly defined how different business concepts relate to one another, and created a solid conceptual foundation for designing a relational database to support your business requirements, as well as a way to get business and technical stakeholders on the same page about how these concepts relate.<\/p>\n

         <\/p>\n

        9. ADVANTAGES OF DBMS<\/strong><\/p>\n<\/div>\n

        Balance conflicting requirements<\/p>\n

        Improved data accessibility and reponsiveness<\/p>\n

        Increased productivity<\/p>\n

        Improved maintenance through data independance<\/p>\n

        Increased concurrency<\/p>\n

        Improved backup and recovery services.<\/p>\n

          \n
        1. SUMMARY\u00a0<\/strong><\/li>\n<\/ol>\n

          This session we have dealt with the introduction to database management systems.<\/p>\n

          We have also discussed about the roles and components of database management system.<\/p>\n

          The various information models have been discussed. We have listed the advantages of DBMS.<\/p>\n

          \"\"<\/p>\n","protected":false},"author":4,"menu_order":2,"template":"","meta":{"pb_show_title":"on","pb_short_title":"","pb_subtitle":"","pb_authors":[],"pb_section_license":""},"chapter-type":[],"contributor":[],"license":[],"class_list":["post-33","chapter","type-chapter","status-publish","hentry"],"part":3,"_links":{"self":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-json\/pressbooks\/v2\/chapters\/33","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-json\/wp\/v2\/users\/4"}],"version-history":[{"count":5,"href":"https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-json\/pressbooks\/v2\/chapters\/33\/revisions"}],"predecessor-version":[{"id":42,"href":"https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-json\/pressbooks\/v2\/chapters\/33\/revisions\/42"}],"part":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-json\/pressbooks\/v2\/parts\/3"}],"metadata":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-json\/pressbooks\/v2\/chapters\/33\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-json\/wp\/v2\/media?parent=33"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-json\/pressbooks\/v2\/chapter-type?post=33"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-json\/wp\/v2\/contributor?post=33"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-json\/wp\/v2\/license?post=33"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}