B



B.C.A (2st Semester) October/ November – 2012

|1. | Answer following (any seven) |14 |

|(1) |What is the use of Canonical cover during normalization? | |

|Ans. |Canonical cover during normalization:- | |

| |Canonical cover can reduce the efforts spent in checking for violations by testing a simplified set of functional dependencies that | |

| |has the same closure as the given set. | |

| |An attribute of a functional dependency is said to be extraneous if we can remove it without changing the closure of the set of | |

| |functional dependencies | |

| |Canonical Cover. | |

| |It is the use of canonical cover during the normalization. | |

| |A canonical cover for F is a set of dependencies Fc such that | |

| |F logically implies all dependencies in Fc, and | |

| |Fc logically implies all dependencies in F, and | |

| |No functional dependency in Fc contains an extraneous attribute, and | |

| |Each left side of functional dependency in Fc is unique. | |

| |To compute a canonical cover for F: | |

| |repeat | |

| |Use the union rule to replace any dependencies in F | |

| |(1 ( (1 and (1 ( (2 with (1 ( (1 (2 | |

| |Find a functional dependency ( ( ( with an | |

| |extraneous attribute either in ( or in ( | |

| |If an extraneous attribute is found, delete it from ( ( ( | |

| |until F does not change | |

| |Note: Union rule may become applicable after some extraneous attributes have been deleted, so it has to be re-applied | |

| |Computing a Canonical Cover | |

| |R = (A, B, C) | |

| |F = {A ( BC | |

| |B ( C | |

| |A ( B | |

| |AB ( C} | |

| |Combine A ( BC and A ( B into A ( BC | |

| |Set is now {A ( BC, B ( C, AB ( C} | |

| |A is extraneous in AB ( C | |

| |Check if the result of deleting A from AB ( C is implied by the other dependencies | |

| |Yes: in fact, B ( C is already present! | |

| |Set is now {A ( BC, B ( C} | |

| |C is extraneous in A ( BC | |

| |Check if A ( C is logically implied by A ( B and the other dependencies | |

| |Yes: using transitivity on A ( B and B ( C. | |

| |Can use attribute closure of A in more complex cases | |

| |The canonical cover is: A ( B | |

| |B ( C | |

|(2) |What is tuple? | |

|Ans. |Tuple:- | |

| |Tuple is the horizontal bar in table object.A tuple is a record or row in table. | |

| |Tuple is known as the rows in a relation(table).A single row in a table is called tuple . | |

|(3) |Define functional dependency. | |

|Ans. |Functional dependency:- | |

| |Given two sets of attributes X & Y. Y is said to be functionally dependent on X if a given value for each attribute in X uniquely | |

| |determines the value of the attributes in Y. | |

| |X is called the determinate of the functional dependency and it is denoted by X⋄Y. | |

|(4) |What do you understand by ‘Logical data independence’? | |

|Ans. |Logical data independence:- | |

| |Logical data independence also insulates application programs from operations such as combining two records into one or splitting an | |

| |existing record into two or more records. | |

| |Logical data independence is archived by providing the external level or user view of database. | |

| |It indicates that the conceptual schema can be changed without affecting the existing external schemas. | |

|(5) |What is weak entity set? | |

|Ans. | Entity set:- | |

| |An entity set may not have sufficient attributes form a primary key. Such an entity set is terned a weak entity set. | |

| | | |

| |Denoted by: | |

| | | |

|(6) |State difference between delete and drop. | |

|Ans. |Delete: | |

| |The delete command removes tuples from a relation. It includes a WHERE clause, similar to that used in SQL query, to select tuples to| |

| |be deleted. | |

| |Drop: | |

| |The drop command can be used to drop named schema elements, such as tables, domains, constraints. one can also drop a schema. | |

|(7) |What is SQL, and why it is important? | |

|Ans. |SQL: Structure Query Language | |

| |The SQL is presently expended as Structure Query Language. | |

| |SQL is comprehensive database language: It has statements for database data definitions, queries, and updates. Hence, it is both a | |

| |DDL and a DML. | |

| |It is important to note that database programming is a very broad topic | |

|(8) |What do you understand by data type memo? | |

|Ans. |Memo:- | |

| |Memo is long text data type. | |

| |Use for long pieces of text, such as notes and long descriptions. | |

| |It can store up to 64,000 characters. | |

| |Example: address, remarks type of column name. | |

|2. |Answer following {any three) |18 |

|(1) |Discuss the disadvantage of file processing. | |

|Ans. |Disadvantage of file processing:- | |

| |1.Program-Data Dependence. | |

| |File descriptions are stored within each application program that accesses a given file. | |

| |2. Duplication of Data. | |

| | . Applications are developed independently in file processing systems leading to unplanned duplicate files. Duplication is wasteful | |

| |as it requires additional storage space and changes in one file must be made manually in all files. This also results in loss of data| |

| |integrity. It is also possible that the same data item may have different names in different files, or the same name may be used for | |

| |different data items in different files.  | |

| |3. Limited data sharing.  | |

| |Each application has its own private files with little opportunity to share data outside their own applications. A requested report | |

| |may require data from several incompatible files in separate systems.  | |

| |4. Lengthy Development Times.  | |

| |There is little opportunity to leverage previous development efforts. Each new application requires the developer to start from | |

| |scratch by designing new file formats and descriptions  | |

| |5. Excessive Program Maintenance.  | |

| |The preceding factors create a heavy program maintenance load. | |

| |6. Integrity Problem | |

| |. The problem of integrity is the problem of ensuring that the data in the database is accentuate. | |

| |7. Inconsistance data | |

| |8. Security | |

|(2) |What is normalization explain BCNF and compare how it is better than Third Normal Form? | |

|Ans. |Normalization:- | |

| |Normalization is a process of efficiently organizing data in a database. There are two goals of the normalization (1) eliminating | |

| |redundant data (for example storing the same data in more than on table) and (2) ensuring data dependencies make sure (only storing | |

| |related data in a table) both of these are important goals for reduce the amount of space a database consumes and ensure that data is| |

| |logically stored. | |

| |Or | |

| |Normalization is a process of filtering in database which is to reduce anomalies and redundant data. | |

| | | |

| |Introduction. | |

| |0ne of the more crucial topic in the area of database management is the process of normalizing the table in a relational database. | |

| |The understanding ideas in normalization are simple enough. | |

| |Through the normalization we want to design for our relational database asset of files that | |

| |Contain all the data necessary for the purposes that the database is to serve. | |

| |Have as little redundancy as possible. | |

| |Accommodate multiple values for types of data that require them. | |

| |Permit efficient updates of the data in the database. | |

| |Avoid the danger of losing data unknowingly. | |

| |The primary reason for normalizing database to at least the level of the 3rd Normal Form(the levels are explained below) is that | |

| |normalization is a potent weapon against the possible corruption of database stemming from what are called “insertion anomalies”,” | |

| |Deletion anomalies”,” Update anomalies”. | |

| |These types of error can creep into databases that are insufficiently normalized. | |

| | | |

| |What is normalization? | |

| |Definitions: | |

| |Normalization is a technique for producing a set of relations with desirable properties, given the data requirements of an | |

| |enterprise. | |

| |Normalization is the process of efficiently organizing data in a database | |

| |The process of normalization is a formal method that identifies relations based on their primary or candidate keys and the functional| |

| |dependencies among their attributes. | |

| |Normalization is a technique that | |

| |Decomposes data into two dimensional tables. | |

| |Eliminates any relationships in which table data does fully depend upon the primary key of a record. | |

| |Eliminates any relationship that contains transitive dependencies. | |

| |Goals of Normalization : | |

| |Eliminate redundant data. | |

| |Ensuring redundant data is logically store. | |

| | | |

| |Normalization can be viewed as a series of steps designed, one after another, to deal with ways in which tables can be “too | |

| |complicated for their own good”. | |

| |Purpose of normalization : | |

| |The purpose of normalization is to reduce the chances for anomalies to occur in a sdatabase. The definitions of the various levels of| |

| |normalization illustrate complications to be eliminated in order to reduce the chances of anomalies. | |

| |At all levels and in every case of table with a complication , the resolution of theproblem turns out to be the establishment of two | |

| |or more simpler tables which, as a group contain the same information as the original table, but because of their simpler individual | |

| |structures, lack the complication. | |

| | | |

| | | |

| |Pros & cons of normalization. | |

| |Pros: | |

| |More efficient structure. | |

| |Better understanding of your data. | |

| |More flexible data base structure. | |

| |Easier to maintain database structure. | |

| |Few (if any) costly surprise down the road. | |

| |Validates your common sense and intuition. | |

| |Insure that distict table exit when necessary. | |

| | | |

| |Cons: | |

| |You can’t started building the database before you know what the user needs. | |

| | | |

| |Basic Information Of Normalization: | |

| |1ST Normal Form (1ST NF) | |

| |A table (relation) is in 1NF If: | |

| |1. There is no duplicate row in the table. | |

| |2. Each cell is single-valued (no repeting group or array). | |

| |3. Entity in a column (field) are of the same kind. | |

| |The requirement that there be no duplicated rows in the table means that the table has a key (although the key might be made up of | |

| |more than one column, even possibly, of all the columns). | |

| | | |

| |2nd Normal Form (2NF) | |

| |A table is in 2NF if it is in 1NF and if all non-key attributes are dependent on all of the key. | |

| |Since a partial dependency occurs when a non-key attribute is dependent on only a part of the composite key, the definition of 2NF is| |

| |sometimes phrased as, “A table is in 2NF if it is in 1NF and if it has no partial dependencies.” | |

| | | |

| | | |

| |3rd Normal Form (3NF) | |

| |A table is in 3NF if it is in 2NF and if it has no transitive dependencies. | |

| | | |

| | | |

| |Boyce-Codd Normal Form (BCNF) | |

| |A table is in BCNF if it is in 3NF and if every determinant is a candidate key. | |

| | | |

| | | |

| |4th Normal Form (4NF) | |

| |A table is in 4NF if it is in BCNF and if it has no multi-valued dependencies. | |

| | | |

| | | |

| | | |

| |5th Normal Form (5NF) | |

| |A table is in 5NF, also called “Projection-join Normal Form” (PJNF), if it is in 4NF and if every join dependency in the table is a | |

| |consequence of the candidate keys of the table. | |

| | | |

| |Need of normalization. | |

| |Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and | |

| |dependency.  | |

| |Normalization usually involves dividing large tables into smaller (and less redundant) tables and defining relationships between | |

| |them. | |

| |A database table looks like a spreadsheet.  | |

| | One item of data, such as a first name, a last name, a phone number, or a street address, appears in each box, or cell, at each | |

| |intersection of a row and column.  | |

| |When you normalize a database you have four goals: | |

| |Arranging data into logical groupings such that each group describes a small part of the whole | |

| |Minimizing the amount of duplicate data stored in a database; | |

| |Organizing the data such that, when you modify it, you make the change in only one place | |

| |Building a database in which you can access and manipulate the data quickly and efficiently without compromising the integrity of the| |

| |data in storage. | |

| |Data normalization helps you design new databases to meet these goals or to test databases to see whether they meet the goals. | |

| |Sometimes database designers refer to these goals in terms such as data integrity, referential integrity, or keyed data access. | |

| |Normalization is a process followed for eliminating redundant data and establishes a meaningful relationship among tables based on | |

| |rules and regulations in order to maintain integrity of data. | |

| |Normalization is done for maintaining storage space and also for performance tuning. | |

| | | |

| |Domain-key normal form (DKNF). | |

| |A table is in DKNF if every constraint on the table is a logical consequence of the definition of keys and domains. | |

| |Insertion anomaly. | |

| |It is a failure to place information about a new database entryinto all the places in the database where information about the new | |

| |entry needs to be stored. | |

| |In a properly normalized database, information about a new entry needs to be inserted into only one place in the database, in an | |

| |inadequatly normalized database, information about a new entry may need to be inserted into more than one place, and human | |

| |fallibility being what it is, some of the needed additional insertionsmay be missed. | |

| | | |

| |Deletion anomaly. | |

| |It is a failure to remove information about an existing database entry when it is time to remove that entry. | |

| |In a properly normalized database, information about an old, to-be-gotten-rid-of entry needs to be deletedfrom only one place in the | |

| |database, in an inadequatly normalized database, information about that old entry may need to be deleted from more than one place. | |

| | | |

| |Update anomaly. | |

| |An update of a database involves modifications that may be additions, deletions, or both. | |

| |Thus “update anomalies” can be either of the kinds discussed above. | |

| | | |

| |All three kinds of anomalies are highly undesirable, since thier occurence constitutes corruption of the database. Properly | |

| |normalized database are much less susceptible to corruption than are un-normalized databases. | |

| | | |

| | | |

| |Figure of normalization : | |

| | | |

| |[pic] | |

| |Rule 1: Eliminate Repeating Groups. Make a separate table for each set of related attributes, and give each table a primary key. | |

| |Unnormalized Data Items for Puppies | |

| |puppy number | |

| |puppy name | |

| |kennel code | |

| |kennel name | |

| |kennel location | |

| |trick ID | |

| |trick name | |

| |trick where learned | |

| |skill level | |

| |In the original list of data, each puppy description is followed by a list of tricks the puppy has learned. | |

| |Some might know 10 tricks, somemight not know any. To answer the question “Can Fifi roll over?” | |

| |we need first to find Fifi’s puppy record, then scan the list of tricks associated with the record.This is awkward, inefficient, and | |

| |extremely untidy. | |

| |Moving the tricks into a seperate tablehelps considerably. Seperating the repeating groupsof tricks from the puppy information | |

| |results in first normal form. | |

| |The puppy number in the trick table matches the primarykey in the puppy table, providing a foreign key for relating the two tables | |

| |with a join operation. | |

| |Now we can answer our question with a direct retrieval look to see if Fifi’s puppy number and the trick ID for “roll over” appear | |

| |together in the trick table. | |

| |First Normal Form (1st NF): | |

| |Puppy Table | |

| |puppy number                     — primary key | |

| |puppy name | |

| |kennel name | |

| |kennel location | |

| |Trick Table | |

| |puppy number | |

| |trick ID | |

| |trick name | |

| |trick where learned | |

| |skill level | |

| |Rule 2: Eliminate Redundant Data, if an attribute depends on only part of a multi-valued key, remove it to a separate table. | |

| |The trick name (e.g. “roll over”) appears redundantly for every puppy that knows it. Just trick ID whould do. | |

| | | |

| |TRICK TABLE | |

| |Puppy Number Trick ID Trick Name Where Learned Skill Level | |

| |52                     27         ”roll over”             16                      9 | |

| |53                      16         ”Nose Stand”          9                       9 | |

| |54                      27         ”roll over”              9                       5 | |

| |*Note that trick name depends on only a part (the trick ID) of the multi-valued, i.e. composite key. | |

| | | |

| |In the trick table, the primary key is made up of the puppy number and trick ID. | |

| |This makes sense for the “where learned” and “skill level” attributes, since they will be different for every puppy-trick | |

| |combination. But the trick name depends only on the trick ID. The same name will appear redundantly every time its associated ID | |

| |appears in the trick table. | |

|(3) |Explain the binary relationship set and ternary relationship set with an E-R Diagaram. | |

|Ans. | E-R model | |

| |When would we need such a thing? | |

| |Consider a DB with information about employees who work on a particular project and use a number of machines doing that work. We get | |

| |the E-R diagram shown in Figure 2.20.Relationship sets work and uses could be combined into a single set. However, they shouldn't be,| |

| |as this would obscure the logical structure of this scheme. | |

| |The solution is to use aggregation. | |

| |An abstraction through which relationships are treated as higher-level entities. | |

| |For our example, we treat the relationship set work and the entity sets employee and project as a higher-level entity set called | |

| |work. | |

| |Figure 2.20 shows the E-R diagram with aggregation. | |

| |  | |

| |Figure 2.0:E-R diagram with redundant relationships | |

| |[pic] | |

| |Figure2.1:E-R diagram with aggregation | |

| |  | |

| |[pic] | |

| |Figure2.2:Representation of Figure 2.3 using binary relationships | |

| |[pic] | |

| |Transforming an E-R diagram with aggregation into tabular form is easy.We createa table for each entity and relationship set as | |

| |before. | |

| |The table for relationship set uses contains a column for each attribute in the primary key of machinery and work. | |

| |Database Systems:Design of an E-R Database Scheme | |

| |The E-R data model provides a wide range of choice in designing a database scheme to accurately model some real-world situation.Some | |

| |of the decisions to be made are | |

| |Using a ternary relationship versus two binary relationships. | |

| |Whether an entity set or a relationship set best t a real-world concept. | |

| |Whether to use an attribute or an entity set. | |

| |Use of a strong or weak entity set. | |

| |Appropriateness of generalization. | |

| |Appropriateness of aggregation. | |

| |1 Mapping Cardinalities | |

| |2 Use of Entity or Relationship Sets | |

| |3 Use of Extended E-R Features | |

| |1 Mapping Cardinalities | |

| |The ternary relationship of Figure ?? could be replaced by a pair of binary relationships, as shown in Figure ??.However, there is a | |

| |distinction between the two representations: | |

| |In Figure ??, relationship between a customer and account can be made only if there is a corresponding branch. | |

| |In Figure ??, an account can be related to either a customer or a branch alone. | |

| |The design of figure ?? is more appropriate, as in the banking world we expect to have an account relate to both a customer and a | |

| |branch. | |

| |[pic] | |

| |Figure 2.23:E-R diagram with account as a relationship set | |

| |The design of figure is more appropriate, as in the banking world we expect to have an account relate to both a customer and a | |

| |branch. | |

| |2 Use of Entity or Relationship Sets | |

| |It is not always clear whether an object is best represented by an entity set or a relationship set. | |

| |Both Figure ?? and Figure ?? show account as an entity. | |

| |Figure ?? shows how we might model an account as a relationship between a customer and a branch. | |

| |This new representation cannot model adequately the situation where customers may have joint accounts. | |

| |(Why not?) | |

| |If every account is held by only one customer, this method works. | |

| |3Use of Extended E-R Features | |

| |We have seen weak entity sets, generalization and aggregation. Designers must decide when these features are appropriate. | |

| |Strong entity sets and their dependent weak entity sets may be regarded as a single \object" in the database,as weak entities are | |

| |existence-dependent on a strong entity. | |

| |It is possible to treat an aggregated entity set as a single unit without concern for its inner structure details. | |

| |Generalization contributes to modularity by allowing common attributes of similar entity sets to be repre-sented in one place in an | |

| |E-R diagram. Excessive use of the features can introduce unnecessary complexity into the design. | |

|(4) |What are the levels o f data abstraction? Mention their features. | |

|Ans. |Data Abstraction | |

| |1. The major purpose of a database system is to provide users with an abstract view of the system. | |

| |The system hides certain details of how data is stored and maintained | |

| |Complexity should be hidden from database users. | |

| |2. There are several levels of abstraction: | |

| |(a) Physical Level: | |

| |How the data are stored. | |

| |E.g. index, B-tree, hashing. | |

| |Lowest level of abstraction. | |

| |Complex low-level structures described in detail. | |

| |(b) Conceptual Level: | |

| |Next highest level of abstraction. | |

| |DATA MODELS | |

| |[pic] | |

| |The three levels of data abstraction | |

| |Describes what data arestored. | |

| |Describes the relationships among data. | |

| |Database administrator level. | |

| |(c) View Level: | |

| |Highest level. | |

| |Describes part of the database for a particular group of users. | |

| |Can be many different views of a database. | |

| |E.g.tellersinabankgetaviewofcustomeraccounts,butnotofpayrol | |

| | | |

| |Organization of database (Physical, Conceptual, Logical) | |

| | | |

| |The terms "conceptual". "logical", and "physical" are frequently used in data modeling to differentiate levels of abstraction versus | |

| |detail in the model. Although there is no general agreement, let alone accepted authority, which defines these terms, nevertheless | |

| |data modelers generally understand the approximate scope of each.     | |

| |Conceptual E-R Model | |

| |A conceptual entity-relationship model shows how the business world sees information. It suppresses non-critical details in order to | |

| |emphasize business rules and user objects. It typically includes only significant entities which have business meaning, along with | |

| |their relationships. Many-to-many relationships are acceptable to represent entity associations. | |

| |A conceptual model might discover that there is a need to house information about each person in an organization. While considerable | |

| |thought is given to discovering and describing the relevant properties of each person, the designers accept implicitly that each | |

| |person is distinct and unique. | |

| |A conceptual model may include a few significant attributes to augment the definition and visualization of entities. No effort need | |

| |be made to inventory the full attribute population of such a model. A conceptual model may have some identifying concepts or | |

| |candidate keys noted but it explicitly does not include a complete scheme of identity, since identifiers are logical choices made | |

| |from a deeper context. | |

| |  | |

| |Logical E-R Model | |

| |A logical entity-relationship model is provable in the mathematics of data science. Given the current predominance of relational | |

| |databases, logical models generally conform to relational theory. Thus a logical model contains only fully normalized entities. Some | |

| |of these may represent logical domains rather than potential physical tables. | |

| |For a logical data model to be normalized, it must include the full population of attributes to be implemented and those attributes | |

| |must be defined in terms of their domains or logical data types (e.g., character, number, date, picture, etc.). | |

| |A logical data model requires a complete scheme of identifiers or candidate keys for unique identification of each occurrence in | |

| |every entity. Since there are choices of identifiers for many entities, the logical model indicates the current selection of | |

| |identity. Propagation of identifiers as foreign keys may be explicit or implied. | |

| |Since relational storage cannot support many-to-many concepts, a logical data model resolves all many-to-many relationships into | |

| |associative entities which may acquire independent identifiers and possibly other attributes as well. | |

| |Physical Database Schema | |

| |A physical data model is a single logical model instantiated in a specific database management product (e.g., Sybase, Oracle, | |

| |Informix, etc.) in a specific installation. The physical data model specifies implementation details which may be features of a | |

| |particular product or version, as well as configuration choices for that database instance. These include index construction, | |

| |alternate key declarations, modes of referential integrity (declarative or procedural), constraints, views, and physical storage | |

| |objects such as tablespaces.     | |

| |In Summary | |

| |The conceptual model is concerned with the real world view and understanding of data; the logical model is a generalized formal | |

| |structure in the rules of information science; the physical model specifies how this will be executed in a particular DBMS instance. | |

| |Various data modeling methodologies and products provide these layers of abstraction in different ways. Some address only the | |

| |physical implementation; some model only the logical structure; others may provide elements of all three but not necessarily in three| |

| |separate views. In each case it helps the data modeler to understand the level of abstraction to which a particular feature or task | |

| |belongs. | |

| |Express the number of entities to which another entity can be associated via a relationship set. | |

| |Most useful in describing binary relationship sets. | |

| |For a binary relationship set the mapping cardinality must be one of the following types: | |

| |1.One to one | |

| |2.One to many | |

| |3.Many to one | |

| |4.Many to many | |

| |[pic] | |

| |One to one One to many | |

| |Note: Some elements in A and B may not be mapped to any | |

| |elements in the other set | |

| |[pic] | |

| |Many to one Many to many | |

| |Note: Some elements in A and B may not be mapped to any | |

| |elements in the other set | |

| |Mapping Cardinalities affect ER Design | |

| |Can make access-date an attribute of account, instead of a relationship attribute, if each account can have only one customer | |

| |I.e., the relationship from account to customer is many to one, or equivalently, customer to account is one to many . | |

| |[pic] | |

| |E-R Diagrams | |

| |[pic] | |

| |Rectangles represent entity sets. | |

| |Diamonds represent relationship sets. | |

| |Lines link attributes to entity sets and entity sets to relationship sets. | |

| |Ellipses represent attributes | |

| |Double ellipses represent multivalued attributes. | |

| |Dashed ellipses denote derived attributes. | |

| |Underline indicates primary key attributes (will study later) | |

| |E-R Diagram With Composite, Multivalued, and Derived Attributes | |

| |[pic] | |

| |Relationship Sets with Attributes | |

| |[pic] | |

| |Roles | |

| |Entity sets of a relationship need not be distinct | |

| |The labels “manager” and “worker” are called roles; they specify how employee entities interact via the works-for relationship set. | |

| |Roles are indicated in E-R diagrams by labeling the lines that connect diamonds to rectangles. | |

| |Role labels are optional, and are used to clarify semantics of the relationship | |

| |[pic] | |

| |Cardinality Constraints | |

| |We express cardinality constraints by drawing either a directed line ((), signifying “one,” or an undirected line (—), signifying | |

| |“many,” between the relationship set and the entity set. | |

| |E.g.: One-to-one relationship: | |

| |A customer is associated with at most one loan via the relationship borrower | |

| |A loan is associated with at most one customer via borrower | |

| |[pic] | |

| |One-To-Many Relationship | |

| |In the one-to-many relationship a loan is associated with at most one customer via borrower, a customer is associated with several | |

| |(including 0) loans via borrower | |

| |[pic] | |

| |Many-To-One Relationships | |

| |In a many-to-one relationship a loan is associated with several (including 0) customers via borrower, a customer is associated with | |

| |at most one loan via borrower | |

| |[pic] | |

| |Many-To-Many Relationship | |

| |[pic] | |

| |A customer is associated with several (possibly 0) loans via borrower | |

| |A loan is associated with several (possibly 0) customers via borrower | |

|3. |Answer following (any three) |15 |

|(1) |What are the difference between the DBMS and file-processing systems? | |

|Ans. |File-Processing Systems | |

| |DBMS | |

| | | |

| |A database management system coordinates both the physical and the logical access to the data | |

| |File-processing system coordinates only the physical access. | |

| | | |

| |A database management system reduces the amount of data duplication by ensuring that a physical piece of data is available to all | |

| |programs authorized to have access to it | |

| |Data written by one programing a file-processing system may not be readable by another program. | |

| | | |

| |A database management system is designed to allow flexible access to data (i.e., queries) | |

| |File-processing system is designed to allow predetermined access to data (i.e., compiled programs). | |

| | | |

| |A database management system is designed to coordinate multiple users accessing the same data at the same time. | |

| |A file-processing system is usually designed to allow one or more programs to access different data files at the same time. | |

| |In a file-processing system, a file can be accessed by two programs concurrently only if both programs have read-only access to the | |

| |file. | |

| | | |

|(2) |Explain Object-oriented model | |

|Ans. |The Object-Oriented Model | |

| |1. The object-oriented model is based on a collection of objects, like the E-R model. | |

| |An object contains values stored in instance variables within the object. | |

| |Unlike the record-oriented models, these values are themselves objects. | |

| |Thus objects contain objects to an arbitrarily deep level of nesting. | |

| |An object also contains bodies of code that operate on the the object. | |

| |These bodies of code are called methods. | |

| |Objects that contain the same types of values and the same methods are grouped into classes. | |

| |A class may be viewed as a type de nition for objects. | |

| |Analogy: the programming language concept of an abstract data type. | |

| |The only way in which one object can access the data of another object is by invoking the method of that other object. | |

| |This is called sending a message to the object. | |

| |Internal parts of the object, the instance variables and method code, are not visible externally. | |

| |Result is two levels of data abstraction. | |

| |For example, consider an object representing a bank account. | |

| |The object contains instance variables number and balance. | |

| |The object contains a method pay-interest which adds interest to the balance. | |

| |Under most data models, changing the interest rate entails changing code in application programs. | |

| |In the object-oriented model, this only entails a change within the pay-interest method. | |

| |2. Unlike entities in the E-R model, each object has its own unique identity, independent of the values it | |

| |contains: | |

| |Two objects containing the same values are distinct. | |

| |Distinction is maintained in physical level by assigning distinct object identi ers. | |

| |Record-based logical models: | |

| |Also describe data at the conceptual and view levels. | |

| |Unlike object-oriented models, are used to Specify overall logical structure of the database, and Provide a higher-level description | |

| |of the implementation. | |

| |Named so because the database is structured in xed-format records of several types. | |

| |Each record type de nes a xed number of elds, or attributes. | |

| |Each eld is usually of a xed length (this simpli es the implementation). | |

| |Record-based models do not include a mechanism for direct representation of code in the database. | |

| |Separate languages associated with the model are used to express database queries and updates. | |

| |The three most widely-accepted models are the relational, network, and hierarchical. | |

| |This course will concentrate on the relational model. | |

| |The network and hierarchical models are covered in appendices in the text. | |

| |The Relational Model | |

| |Data and relationships are represented by a collection of tables. | |

| |Each table has a number of columns with unique names, e.g. customer, account | |

| |Figure 1.3 shows a sample relational database. | |

| |[pic] | |

| |Figure A sample relational database. | |

| |[pic] | |

| |Figure A sample network database | |

| |The Network Model | |

| |Data are represented by collections of records. | |

| |Relationships among data are represented by links. | |

| |Organization is that of an arbitrary graph. | |

| |Figure 1.4 shows a sample network database that is the equivalent of the relational database of Figure | |

| |The Hierarchical Model | |

| |Similar to the network model. | |

| |Organization of the records is as a collection of trees, rather than arbitrary graphs. | |

| |Figure shows a sample hierarchical database that is the equivalent of the relational database of Figure | |

| |Figure 1.5: A sample hierarchical database | |

| |[pic] | |

| |The relational model does not use pointers or links, but relates records by the values they contain. This allows a formal | |

| |mathematical foundation to be defined. | |

| |2. Database Systems: Physical Data Models | |

| |1. Are used to describe data at the lowest level. | |

| |2. Very few models, e.g. | |

| |Unifying model. | |

| |Frame memory. | |

| |A physical data model (a.k.a. database design) is a representation of a data design which takes into account the facilities and | |

| |constraints of a given database management system. In the lifecycle of a project it is typically derived from a logical data model, | |

| |though it may be reverse-engineered from a given database implementation. A complete physical data model will include all the | |

| |database artifacts required to create relationships between tables or achieve performance goals, such as indexes, constraint | |

| |definitions, | |

| |linking tables, partitioned tables or clusters. The physical data model can usually be used to calculate storage estimates and may | |

| |include specific storage allocation details for a given database system. | |

|(3) |List the responsibilities-functions of database manager? | |

|Ans. |Responsibilities-functions of database manager:- | |

| |1. The database manager is a program module which provides the interface between the low-level data stored in the database and the | |

| |application programs and queries submitted to the system. | |

| |2. Databases typically require lots of storage space (gigabytes). This must be stored on disks. Data is moved between disk and main | |

| |memory (MM) as needed. | |

| |3. The goal of the database system is to simplify and facilitate access to data. Performance is important. Views provide simpli | |

| |cation. | |

| |4. So the database manager module is responsible for | |

| |Interaction with the file manager: Storing raw data on disk using the le system usually provided by a conventional operating system. | |

| |The database manager must translate DML statements into low-level le system commands (for storing, retrieving and updating data in | |

| |the database). | |

| |Integrity enforcement: Checking that updates in the database do not violate consistency constraints (e.g. no bank account balance | |

| |below $25) . | |

| |Security enforcement: Ensuring that users only have access to information they are permitted to see. | |

| |Backup and recovery: Detecting failures due to power failure, disk crash, software errors, etc., and restoring the database to its | |

| |state before the failure. | |

| |Concurrency control: Preserving data consistency when there are concurrent users. | |

| |5. Some small database systems may miss some of these features, resulting in simpler database managers. (For example, no concurrency | |

| |is required on a PC running MS-DOS.) These features are necessary on larger systems. | |

|(4) |Explain the types of Database Users. | |

|Ans. |The database users fall into several categories: | |

| |Application programmers are computer professionals interacting with the system through DML calls embedded in a program written in a | |

| |host language (e.g. C, PL/1, Pascal). | |

| |These programs are called application programs. | |

| |The DML precompiler converts DML calls (prefaced by a special character like $, #, etc.) to normal procedure calls in a host | |

| |language. | |

| |The host language compiler then generates the object code. | |

| |Some special types of programming languages combine Pascal-like control structures with control structures for the manipulation of a | |

| |database. | |

| |These are sometimes called fourth-generation languages. | |

| |They often include features to help generate forms and display data. | |

| |Sophisticated users interact with the system without writing programs. | |

| |They form requests by writing queries in a database query language | |

| |These are submitted to a query processor that breaks a DML statement down into instructions for the database manager module. | |

| |Specialized users are sophisticated users writing special database application programs. These may be CADD systems, knowledge-based | |

| |and expert systems, complex data systems(audio/video), etc. | |

| |Naive users are unsophisticated users who interact with the system by using permanent application programs (e.g. automated teller | |

| |machine). | |

|4. |Answer following (any four) |12 |

|(1) |What is meant by ‘instances and schemas’? What are the types of schemas? | |

|Ans. |Schema: | |

| |Schema is the logical structure of the database | |

| |E.g, The database consists of information about a set of customers and accounts and the relationship between them. | |

| |Analogous to type information of variable in a program. | |

| |Instance: | |

| |Instance is the actual content of the database at a particular point in a time | |

| |Analogous to the value of a variable. | |

| |There are two types of schemas: | |

| |Physical schema : Database design at the physical level. | |

| |Logical schema : Database design at the logical level. | |

| |Database Systems: Instances and Schemes | |

| |1. Databases change over time. | |

| |2. The information in a database at a particular point in time is called an instance of the database. | |

| |3. The overall design of the database is called the database scheme. | |

| |4. Analogy with programming languages: | |

| |Data type de nition - scheme | |

| |Value of a variable - instance | |

| |5. There are several schemes, corresponding to levels of abstraction: | |

| |Physical scheme | |

| |Conceptual scheme | |

| |Subscheme (can be many) | |

|(2) |What is ‘data manipulation’? | |

|Ans. |Data manipulation | |

| |A data-manipulation language(DML) is a language that enables user to access or manipulate data as organized by the appropriate data | |

| |model. The types of access are: | |

| |Retrieval of information stored in the database | |

| |Insertion of new information into the database | |

| |Deletion of information from the database | |

| |Modification of information stored in the database | |

| |There are basically two types: | |

| |Procedural DMLs require a user to specify what data are needed and how to get those data. | |

| |Declarative DMLs (also referred to as nonprocedural DMLs) require a user to specify what data are needed without specifying how to | |

| |get those data. | |

| | | |

| |Declarative DMLs are usually easier to learn and use than are procedural DMLs. However, since a user does not have to specify how to | |

| |get the data, the database system has to figure out and efficient means of accessing data. | |

| |A query is a statement requesting the retrieval of information. The portion of a DML that involves information retrieval is called a | |

| |query language. Although technically incorrect, it is common practice to use the terms query language and data manipulation language | |

| |synonymously. | |

|(3) |What is Total Participation. | |

|Ans. |Total participation | |

| |Every member of entity set must participate in the relationship | |

| |Represented by double line from entity rectangle to relationship diamond | |

| |E.g., A Class entity cannot exist unless related to a Faculty member entity in this example, not necessarily at Juniata. | |

| |You can set this double line in Dia | |

| |In a relational model we will use the references clause. | |

| |Every member of entity set must participate in the relationship | |

| |Represented by double line from entity rectangle to relationship diamond | |

| |E.g., A Class entity cannot exist unless related to a Faculty member entity in this example, not necessarily at Juniata. | |

| |You can set this double line in Dia | |

| |In a relational model we will use the references clause | |

| |[pic] | |

|(4) |What is Domain Integrity | |

|Ans. | Integrity | |

| |Enforcing data integrity guarantees the quality of the data in the database. For example, if an employee is entered with an employee | |

| |ID value of 123, the database should not permit another employee to have an ID with the same value. If you have an employee_rating | |

| |column intended to have values ranging from 1 to 5, the database should not accept a value outside that range. If the table has a | |

| |dept_id column that stores the department number for the employee, the database should permit only values that are valid for the | |

| |department numbers in the company. | |

| |Two important steps in planning tables are to identify valid values for a column and to decide how to enforce the | |

| |integrity of the data in the column. | |

| |Data integrity falls into the following | |

| |categories: | |

| |1.Entity integrity | |

| |2.Domain integrity | |

| |3.Referential integrity | |

| |4.User-defined integrity | |

| |Domain Integrity | |

| |Domain integrity is to do with the values that may be contained within a specific column. | |

| |All columns have an implicitly domain derived from their data type but miner sql also supports the create domain statement. | |

| |A domain can be defined with a number of check clauses and a default value. | |

| |A domain definition can be used instead of a data type in a column definition.This has the advantage that the same definition of data| |

| |type ,check clauses and default values can be used many column definitions and therefore those columns are guaranteed to have the | |

| |same attributes. | |

| |Domain integrity is the validity of entries for a specific column. You can enforce domain integrity to restrict the type by using | |

| |data types, restrict the format by using CHECK constraints and rules, or restrict the range of possible values by using FOREIGN KEY | |

| |constraints, CHECK constraints, DEFAULT definitions, NOT NULL definitions, and rules | |

|(5) |Give the list of various types of joins | |

|Ans. |Types of join | |

| |-simple join | |

| |-equijoin | |

| |-non-equijoin | |

| |-outer join | |

| |Left-outer join | |

| |Right-outer join | |

| |Full-outer join | |

| |-self join | |

| |-theta join | |

| |-pi join | |

| |-union join | |

| | | |

| |Simple join: | |

| |Syntax: | |

| |SELECT table1.column,table2.column | |

| |FROM table1.table2 | |

| |WHERE table1.column=table2.column | |

| | | |

| |-write the join condition the WHERE clause. | |

| |-precede each column name with the table name for clarity. | |

| |- column names must be prefixed with the table name when the same column name appear in more than one table. | |

| | | |

| |Example: | |

| |SQL> SELECT EMP.ENAME,DEPT.DNAME | |

| |FROM EMP, DEPT | |

| |WHERE EMP.DNO=DEPT.DNO and EMP.salary>5000; | |

| | | |

| |Self joins: | |

| | | |

| |-join rows in a table to rows in the same table by using a self join. | |

| |-simulate two tables in the FROM clause by creating two aliases for the table. | |

| | | |

| |Example: | |

| | | |

| |Eno | |

| |Ename | |

| |Esr | |

| | | |

| |1 | |

| |A | |

| |2 | |

| | | |

| |2 | |

| |B | |

| |3 | |

| | | |

| |3 | |

| |C | |

| |3 | |

| | | |

| |3 | |

| |C | |

| |1 | |

| | | |

| | | |

| | | |

| | | |

| | | |

| |SQL> SELECT e. ename, es. ename srname | |

| |FROM emp e, emp es | |

| |Where e.esr =es.eno ; | |

| | | |

| |Output: | |

| |Ename | |

| |Srname | |

| | | |

| |A | |

| |B | |

| | | |

| |B | |

| |C | |

| | | |

| |C | |

| |C | |

| | | |

| |C | |

| |A | |

| | | |

| | | |

| | | |

| | | |

| | | |

|5. | | |

|(a) | Write SQL Statement to create these tables |3 |

| |Emp(emp_id,name,city ,salary ,post,dept_no,join_date) | |

|Ans. |Create table Emp | |

| |( | |

| |Em_pid Number primary Key, | |

| |Name Text, | |

| |City Text, | |

| |Salary Curancy, | |

| |Post Text, | |

| |Dept_no Number, | |

| |Join_date Curancy | |

| |); | |

| |Department(dept_no,dept_name,location) | |

|Ans. |Create table Department | |

| |( | |

| |Dept_no Number, | |

| |Dept_name Text, | |

| |Location | |

| |); | |

| |Skill(emp_id,skill) | |

|Ans. |Create table skill | |

| |( | |

| |Emp_id Number, | |

| |Skill Text | |

| |); | |

|(b) |Solve the following queries |8 |

|(i) |List the employee details that are from ’’Ahmadabad” or Baroda” | |

|Ans. |Select * | |

| |From city | |

| |Where like=” Ahmadabad or Baroda”; | |

|(ii) |Count the no. of employees from “account: dept. | |

|Ans. |Select emp,department | |

| |From dept-Name | |

| |Where like=” | |

|(iii) |Display the information of employees having “typing" | |

|Ans. |Select post | |

| |From employee | |

| |Where employee.post like=”typing”; | |

|(iv) |List the department name of employees whose join date is 21st of any month. | |

|Ans. |Select departname,empemp,dep,doj | |

| |From department,emp | |

| |Where employee.doj like”21/3/2013”; | |

| |Product By :- ( PATEL AKASH & GROUP ( | |

| |12BCA06 BHAVSAR BHAKTI | |

| |12BCA07 CHAUHAN DIVYA | |

| |12BCA08 AKASH R PATEL | |

| |12BCA09 CHAUHAN YATIN | |

| |12BCA10 DESAI AMISHI | |

[pic]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download