SEPTEMBER / OCTOBER-2009[12BCA38,12BCA37,12BCA40]



|Sr.no |Que-Ans |Marks |

|Q-1 |Answer the following question. |12. |

|1 |What is data manager? List the responsibilities of the database manager. | |

|Ans: |Data manager is the central software components of the DBMS. Responsibilities of data manager are | |

| |Responsible for interfacing with file system. | |

| |Enforcing constrain to maintain the consistency and integrity of data. | |

| |Maintaining security | |

| |Convert operations in users query. | |

|2 |Define the team : | |

| |A.Database schema : | |

| |B.Database: | |

|Ans: |Database schema : | |

| |In a relational database the schema define the table the field in each table and the relationship | |

| |between fields and table. | |

| |Schema has a two types | |

| |1. Physical schema. | |

| |2. Logical schema. | |

| |Database: | |

| |A systematic type of argument of related information is called a database. | |

| | | |

|3. |Differentiate between physical and logical data independence. | |

|Ans: |Physical Independence: | |

| |It indicates that the physical storage structure of storage or devices used for storing could be | |

| |changed without necessity a change in conceptual view or any of the external views. | |

| |Logical independence: It indicates that the conceptual schema can be changed without affecting the | |

| |existing external schemas. | |

|4. |Explain association with example. | |

|Ans: |A relationship is an association among several entities. | |

| |For Eg.customer and loan are two entities we can define a relation that associated customer Harish | |

| |with loan L-1. | |

|5. |Explain query processor. | |

|Ans: |Function of query processor is to break down DML statement into instruction the storage manager | |

| |understands. | |

| |Component of processor are DDL interpreter query that the which interprets DDL statement. | |

| |DML compiler which translate DML statement in a query language into evaluation plan. | |

| |Query evaluation engine which executes low level instruction generated by DML compiler. | |

|6. |State the difference between functional and multivalued dependencies. | |

|Ans: |Functional dependencies : | |

| |Given two set of attributes X&Y.Y is said to be functionally dependant on X it a given for each | |

| |attributes in X uniquely determines the values of the attributes in Y.X is called the determinant of| |

| |the functional | |

| |Dependency (fd) and fd is denotes by X>Y. | |

| |Multivalued dependencies. : | |

| |Given a relation schema R, let X&Y be subset of attributes of R and Y need not be distinct. | |

| |Then multivalued dependencies x>y hold in a relation R define R if given two tuples t1 and t2 in R | |

| |with t1(X)=t2(X) R contain two tuples t3 and t4 with the following characteristics: | |

| |T1, T2, T3 and T4 the same values X. | |

| |T1(X)=t2(X)=t3(X)=t4(X) | |

| |The Y values of t1 and t3 are the same The Y values of t2 and t4 the same | |

| |T1(Y)=t2(Y)=t3(Y)=t4(Y) | |

| | | |

|Q2. |Answer in brief. | |

|a. |Write a note on database system user. | |

|Ans: |Application programmers – interact with system through DML calls |08. |

| |Sophisticated users – form requests in a database query language Specialized users – write | |

| |specialized database applications that do not fit into the traditional data processing framework. | |

| |Naïve users – invoke one of the permanent application programs that have been written previously | |

| |E.g. people accessing database over the web, bank tellers, clerical staff | |

| |Users are differentiated by the way they expect to interact with the system | |

| |A transaction is a collection of operations that performs a single logical function in a database | |

| |application. | |

| |Transaction-management component ensures that the database remains in a consistent (correct) state | |

| |despite system failures (e.g., power failures and operating system crashes) and transaction | |

| |failures. | |

| |Concurrency-control manager controls the interaction among the concurrent transactions, to ensure | |

| |the consistency of the database. | |

| | | |

| | | |

| |[pic] | |

| | | |

| |(or) | |

|a. |Discuss type of record-base logical model. Differentiate them with each other. | |

|Ans: |1. Record-based logical models: |08. |

| |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 end 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. | |

| | | |

| |Name | |

| |street | |

| |city | |

| |number | |

| | | |

| |Lowery | |

| |Maple | |

| |Queens | |

| |900 | |

| | | |

| |Shiver | |

| |North | |

| |Bronx | |

| |556 | |

| | | |

| |Shiver | |

| |North | |

| |Bronx | |

| |647 | |

| | | |

| |Hodges | |

| |Sidehill | |

| |Brooklyn | |

| |801 | |

| | | |

| |Hodges | |

| |Sidehill | |

| |Brooklyn | |

| |647 | |

| | | |

| |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. | |

| | | |

| | | |

| | | |

| | | |

| |Figure 1.3: A sample relational database. | |

| |[pic] Figure 1.4: 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 1.3. | |

| |The Hierarchical Model | |

| |Similar to the network model. | |

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

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

| |Figure 1.3. | |

| |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. | |

|b. |Define relationship. Explain types of relationship with example. | |

|Ans: |A relationship is an association among several entities.For example a depositor relation ship |05. |

| |assosiated customer with each account that she/he has. | |

| |The number of entities set that partcipate in a relationship set is also the relationship set is of | |

| |degree 3. | |

| |Various types of relationship are | |

| |Binary relationship | |

| |ternary relationship | |

| | | |

| |Binary relationship: | |

| |The relationship set borrow and loan branch provide an example of a binary relationship set that is| |

| |one that involes two entities set. | |

| | | |

| |[pic] | |

| |As example considers the entity set employee, branch and job. | |

| | | |

| |Examples of job could include manager teller auditor and so on job entities may have the attributes | |

| |title and level. | |

| | | |

| |The relationship set works on among employee branch and job is an example of a ternary relationship.| |

| |(or) | |

|b. |Compare BCNF and 3RD NF. | |

|Ans: | |05. |

| |BCNF | |

| |3RD NF | |

| | | |

| |1 | |

| |In BCNF for every x>y, | |

| |Y is subset of X | |

| |X is super key. | |

| |In 3NF for every X>Y, | |

| |Y is subset of X. | |

| |X is not super key. | |

| |Y is not subset of key. | |

| | | |

| | | |

| |2 | |

| |BCNF don’t have any redundancy. | |

| |3NF has some redundancy | |

| | | |

| |3 | |

| |BCNF is not dependency preserving. | |

| |3NF is dependency preserving | |

| | | |

| |4 | |

| |The relation in #NF is now decomposed to bring it to BCNF FD of original tables are | |

| |FD:FD1:stu#,sub>teacher | |

| |FD:FD2: stu#,sub>score | |

| |FD:FD3: stu#,sub>sub | |

| |Example: | |

| |Teac Sub | |

| |Teacher | |

| |Subject | |

| | | |

| |Raturi | |

| |Chem. | |

| | | |

| |Siya | |

| |English | |

| | | |

| |Jiya | |

| |History | |

| | | |

| |Ritika | |

| |CS | |

| | | |

| |FD 1: teacher >subject | |

| |Stu# | |

| |Sub | |

| | | |

| |1H123 | |

| |Chem. | |

| | | |

| |1H124 | |

| |English | |

| | | |

| |1H234 | |

| |History | |

| | | |

| |1H254 | |

| |CS | |

| | | |

| |1H235 | |

| |Chem. | |

| | | |

| |FD11:stu#,sub>score | |

| |As per FDs inferd from teach_sub&stu#_score tableside are fd1:teacher>subject | |

| |Fd2:stu#,sub,sub>score | |

| |Original tables have 2 FDs and also not same as original table. | |

| |Thus in BCNF dependency not preserved. | |

| |Example: | |

| |Stud# | |

| |Sub | |

| |teacher | |

| |Score | |

| | | |

| |1H123 | |

| |chem | |

| |Raturi | |

| |4 | |

| | | |

| |1H124 | |

| |English | |

| |Siya | |

| |4 | |

| | | |

| |1H234 | |

| |History | |

| |Jiya | |

| |3 | |

| | | |

| |1H254 | |

| |CS | |

| |Ritika | |

| |3 | |

| | | |

| |1H235 | |

| |chem | |

| |Roma | |

| |2 | |

| | | |

| |FD:FD1:stu#,sub>teacher | |

| |FD:FD2: stu#,sub>score | |

| |FD:FD3: teacher>sub | |

| |Key:stu#sub | |

| |In FD1,fd2,fd3 there is no partial dependency so relation is in 2NF. | |

| |In fd1 stu#,sub is key in fd2 | |

| |Stu#, sub is key and in fd3 sub at dependant side is prime attribute and as per 3NF either | |

| |determinant should be key or dependant is prime attribute so this relation is in 3NF. | |

| |3.but still having data redundancy | |

| | | |

| | | |

| | | |

|Q-3 |Write a short note on any three with an example. |21. |

|1. |Explain Generalization and Specialization. | |

|Ans: |Consider extending the entity set account by classifying accounts as being either saving-account or | |

| |chequing-account. | |

| |Figure : Generalization | |

| |[pic] | |

| |Each of these is described by the attributes of account plus additional attributes. (savings has | |

| |interest-rate and chequing has overdraft-amount.) | |

| |We can express the similarities between the entity sets by generalization. | |

| |This is the process of forming containment relationships between a higher-level entity set and one | |

| |or more lower-level entity sets. | |

| |In E-R diagrams, generalization is shown by a triangle, as shown in Figure. | |

| |Generalization hides dierences and emphasizes similarities. | |

| |Distinction made through attribute inheritance. | |

| |Attributes of higher-level entity are inherited by lower-level entities. | |

| |Two methods for conversion to a table form: | |

| |_ Create a table for the high-level entity, plus tables for the lower-level entities containing also| |

| |their specific attributes. | |

| |_ Create only tables for the lower-level entities. | |

| |Specialization | |

| |Top-down design process; we designate sub groupings within an entity set that are distinctive from | |

| |other entities in the set. | |

| |These sub groupings become lower-level entity sets that have attributes or participate in | |

| |relationships that do not apply to the higher-level entity set. | |

| |Depicted by a triangle component libelled ISA (E.g. customer “is a” person). | |

| |Attribute inheritance | |

| |A lower-level entity set inherits all the attributes and relationship participation of the | |

| |higher-level entity set to which it is linked. | |

| | | |

| |[pic] | |

|2. |Date manipulation statement in sql. | |

|Ans: |Data Manipulation(DML) : | |

| |retrieval of information from the database | |

| |insertion of new information into the database | |

| |deletion of information in the database | |

| |modication of information in the database | |

| |A DML is a language which enables users to access and manipulate data. | |

| |The goal is to provide ecient human interaction with the system. | |

| |There are two types of DML | |

| |Procedural: The user species what data is needed and how to get it | |

| |Nonprocedural: The user only species what data is needed Easier for user | |

| |May not generate code as ecient as that produced by procedural languages | |

| |A query language is a portion of a DML involving information retrieval only. The terms DML and query| |

| |language are often used synonymously. | |

| |Data Manipulation Language (DML): DML statements are used for managing data within schema objects. | |

| |Some example: | |

| | | |

| |Command | |

| |Description | |

| | | |

| |SELECT: | |

| |Retrieve data from the a database | |

| | | |

| |INSERT: | |

| |Insert data into a table | |

| | | |

| |UPDATE: | |

| |Update existing data within a table | |

| | | |

| |DELETE: | |

| |Deletes all records from a table, the space for the records remain | |

| | | |

| |MERGE: | |

| |UPSERTE operation (insert or update) | |

| | | |

| |CALL: | |

| |Call a PL/SQL or java subprogram | |

| | | |

| |EXPLAI PLAN: | |

| |Explain access path or data | |

| | | |

| |LOCK TABLE: | |

| |Control concurrency | |

| | | |

|3. |Integrity constraint. | |

|Ans: |Integrity constrain are used to ensure accuracy and consistency of data in a relational database. | |

| |Data integrity is handled in a relational database through the concept or referential integrity. | |

| |There are many type of integrity constrain. | |

| |Entity integrity. | |

| |Referential integrity. | |

| |Domain integrity. | |

| |Entity integrity :- | |

| |Entity integrity is an integrity rule which states that every table must have a primary key and that| |

| |the column or chosen to be the primary key should be unique and not null. | |

| |If each value of a primary key must be unique no duplicate row can logically appear in a table. | |

| |The not NULL characteristic of a primary key ensures that a value can be use to identify all rows in| |

| |a table. | |

| |Within relational database using SQL, entity integrity is enforced by adding a primary key cause to | |

| |a schema definition. | |

| | | |

| |Referential integrity: | |

| |There are cases where we wish to ensure that a value that appears in one relation for a given set of| |

| |attribute also appears for a certain set of attributes in another relation this is called | |

| |referential integrity. | |

| |Database modification can cause violation of referential integrity. | |

| |When a referential integrity constrain is violated the normal procedure is to reject the action that| |

| |caused violation. | |

| |Example: | |

| |Emp(eid,ename,add,contect_no,); | |

| |Dept(dptid,dptname); | |

| |Emp_dept(empid,dptid); | |

| |In table it can be enforced using following example. | |

| |CREATE TABLE EMP | |

| |( | |

| |EID NUMBER PRIMARY KEY, | |

| |ENAME TEXT, | |

| |ADD TEXT, | |

| |CONTACTECT_NO NUMBER | |

| |); | |

| | | |

| |Domain integrity: | |

| |A domain defines the possible values of an attribute. | |

| |Domain integrity rules govern these values. | |

| |In a database system the domain integrity is define by, | |

| |The data type and the length | |

| |The NULL value acceptance | |

| |The allows able values, through techniques like constrain or rules | |

| |The default value. | |

|4. |Mapping cardinalities. | |

|Ans: |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 | |

| |Explain in brief | |

| |1. One to One | |

| |[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 | |

| | | |

|Q-4 |Answer the following : |12. |

|A. |Describe the need of normalization and steps to achieve BCNF. | |

|Ans: |Definition : | |

| |Normalization is the process to filtering database which is to reduce a normalization and | |

| |redundancies data. | |

| |Normalization is the process of efficiently organising data in data base. | |

| | | |

| |Goal : | |

| |Eliminate redundant data. | |

| |Ensuring redundant data is logically staror. | |

| | | |

| |Purpose: | |

| |Normalization is the technique for producing a set of relation with describes properties, given the | |

| |data requirement of an enterprise. | |

| |The process of normalization is a formal method that identifies relation base on their primary & | |

| |candidate key and the functional dependencies among their attribute. | |

| |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. | |

| | | |

| | | |

| |1st Normal form : | |

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

| |give each table a primary key. | |

| |First NF is a relation in which the intersection of each row and column contain one value. | |

| |There are two approaches to removing repeating group for unnormalization table. | |

| |Remove the repeating group by entering appropriate data in the empty column of row contains the | |

| |repeating data. | |

| |Remove repeating group by placing the repeating data along with a copy of the original key attribute| |

| |in a shaparate relation a primary key is identify for the new relation. | |

| |2nd Normal Form : | |

| | | |

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

| |it to a separate table. | |

| | | |

| |Second NF is a relation that is in first normal form and every non-primary key attribute is fully | |

| |functionally dependent on the primary key. | |

| |Second NF is removing partial dependencies if a partial dependency exists; we remove the function | |

| |dependency attributes form the form the relation by placing them in a new relation among with a copy| |

| |of their determinant. | |

| |3rd normal form : | |

| |Rule 3: Eliminate columns not dependent on key. If attributes do not contribute to a description of | |

| |the key, remove them to a separate table. | |

| | | |

| |A relation that is in first n & second normal form and in which no none primary key attribute is | |

| |transitively dependent on the primary key. | |

| |Third nf is remove transitive dependency by placing the attribute in a new relation among with a | |

| |copy of the determinant. | |

| |BCNF : | |

| |Rule 4: Isolate independent multiple relationships. No table may contain two or more 1:N | |

| |(one-to-many) or N:M (many-to-many) relationships that are not directly related. | |

| | | |

| |Bocyce code normal from. | |

| |A relationship in bcnf, if n only if every determine is a candidate key. | |

| |The difference between 3rd nf and bcnf is that for a functional dependency a → b 3rd nf allow this | |

| |dependency in a relation b is a primary key attribute in a attribute and a is not a candidate key. | |

| |Where bcnf instance that for this dependency to remain in to relation a must be candidate key. | |

| | | |

| | | |

| | | |

| | | |

| |[pic] | |

|B. |Explain the concept of keys and their significance. | |

|Ans: |Key : | |

| |Key is a minimal set of attribute. | |

| |A key is an entity set such that number of two entities agrees on all attribute. | |

| |A key is a data item of field which computer using to identify to records database system which is | |

| |refer two as keys. | |

| |A key is a single attribute or combination of attribute that is use to identify one or more instance| |

| |of the set. | |

| |There are eight (8) types of keys. | |

| |Primary key. | |

| |Foreign key/reference key. | |

| |Super key. | |

| |Candidate key. | |

| |Composite key. | |

| |Alternate key. | |

| |Simple key. | |

| |Secondary key. | |

| |1.primary key :- | |

| |Primary key is a uniquely identification of column is in table. | |

| |Primary key has a tow rules given as bellow. | |

| |Primary key is a unique. | |

| |Primary key is not null. | |

| |A primary key is a field that uniquely identifies each record in the database. | |

| |When you declare primary key in your table then that table is called parent table/prime table/main | |

| |table. | |

| |If you can put key in a table then you see the little symbol of key that particular field’s right | |

| |side. | |

| |Example: | |

| |CUSTOMERS | |

| | | |

| |CustomerNo | |

| |FirstName | |

| |LastName | |

| | | |

| |1 | |

| |Sally | |

| |Thompson | |

| | | |

| |2 | |

| |Sally | |

| |Henderson | |

| | | |

| |3 | |

| |Harry | |

| |Henderson | |

| | | |

| |4 | |

| |Sandra | |

| |Wellington | |

| | | |

| | | |

| |For example, in the table above, Customer No is the primary key. | |

| |The values placed in primary key columns must be unique for each row: no duplicates can be | |

| |tolerated. In addition, nulls are not allowed in primary key columns. | |

| |E.g. of Primary Key - Database designer can use one of the Candidate Key as a Primary Key. In this | |

| |case we have “Code” and “Name, Address” as Candidate Key, we will consider “Code” Key as a Primary | |

| |Key as the other key is the combination of more than one attribute. | |

| | | |

| |2. Foreign key/reference key:- | |

| |Foreign key/reference key is a relation of two or more table in which you can take primary key field| |

| |to another. | |

| |Table that referential integrity is created. | |

| |When you declare primary key in your table then that table is called parent table/prime table/main | |

| |table. | |

| |When that primary key is reference in another that which is called foreign key/reference table/child| |

| |table/detail table. | |

| |If the data is exit in master table then n only then we can use in child table | |

| | | |

| |Example :- | |

| |Table 1: | |

| |Field 1 | |

| |Field 2 | |

| |Field 3 | |

| | | |

| | | |

| | | |

| | | |

| | | |

| | | |

| |Reference way | |

| | | |

| |Table 2: | |

| |Field a | |

| |Field b | |

| |Field 1 | |

| | | |

| | | |

| |See above example field 1 has a primary key in table 1 and that field is again use in table 2 as a | |

| |references is called a foreign key/reference key | |

| | | |

| |3. Super key :- | |

| |Super key is a set of each field of given table or subset of any field of that given table. | |

| |In shrot,there is primary key in a simple table but when we are describe that table then their | |

| |described(subset) table have not a primary key is called super key. | |

| |Example :- | |

| |My_emp{empid,ename,salary,doj,design,address}; | |

| |Here, there is a give one table my_emp, and empid has a primary key. Now we can describe the table | |

| |given as below. | |

| |Field1{ename,salary,doj,address}; | |

| |Field2{empid,ename}; | |

| |Field3{salary, design}; | |

| |When we are describe the simple table just like field1,field2,field3 then which described(subset) | |

| |table has a primary key it’s not a super key or super table | |

| |Here, there are field1 & field3 have not primary key then it is a super key or super table and | |

| |field2 have a primary key then it is not a super key or super table. | |

| | | |

| |4.candidate key :- | |

| |Candidate key can be define as minimal super key in other word in attribute a combination of | |

| |attribute that identify that record, uniquely but non of is proper subset can identify record | |

| |uniquely. | |

| |Candidate key has at least one primary key of described (subset) table. | |

| | | |

| |Candidate key is also super key but super key is not candidate key. | |

| | | |

| |Example :- | |

| |My_emp(empid,ename,salary,doj,design); | |

| |Here, there is a give one table my_emp, and empid has a primary key. Now we can describe the table | |

| |given as below. | |

| |Field1{empid,ename,salary,doj,address}; | |

| |Field2{ename,salary}; | |

| |Field3{empid,salary,design}; | |

| |When we are describe the simple table just like field1,field2,field3 then which described(subset) | |

| |table have a primary key is called candidate key or candidate table | |

| |Here, there are field1 & field3 have primary key then it is a candidate key or candidate table and | |

| |field2 have not primary key then it is not a candidate key or candidate table. | |

| |Here all table is also know as super key or super table | |

| | | |

| |posite key :- | |

| |Composite key is a set of two primary key in one table. | |

| |Here, you are confused that how it is possible because one table have an only one primary key then | |

| |how it is possible? It confiscation’s answer is given as bellow through examples & some brief | |

| |information. | |

| |First a fall we can create two tables when the in a table have at least one primary key and we can | |

| |meagre that table in third table as a reference key and that table is define as a composite key or | |

| |composite table. | |

| |Example :- | |

| |Table 1: | |

| |Student(sid,sname,city,achievement,contactno); | |

| |Table 2: | |

| |Course(cid,cname); | |

| |Here, there are two tables and is have a primary key to field sid and cid. | |

| |Now we are describing that table and create third table as stu_course. | |

| |Table 3: | |

| |Stu_course(sid,cid); | |

| |See, here table 3 has a tow primary key before we can describe that two tables as a stu_course and | |

| |see table 1 have a primary key as a sid and table 2 have a primary key as cid and when we are | |

| |describe that as a table 3 stu_course sid & cid is a primary key as a reference key it is called a | |

| |composite key. | |

| | | |

| |6.alternative key :- | |

| |Alternative key can be any of the candidate key accepts for the primary key. | |

| |Alternative key is a combination of that attributes of table have a no composite key. | |

| |In short it is just like a candidate key but it is not candidate key or super key. | |

| |Example :- | |

| |Table 1: | |

| |Student(sid,sname,city,achievement,contactno); | |

| |Table 2: | |

| |Course(cid,cname); | |

| |Here, there are two tables and is have a primary key field sid and cid and other field are none | |

| |selected in key that all are define as alternative key. | |

| |Now we are describing that table and create third table as stu_course. | |

| |Table 3: | |

| |Stu_course(sid,cid); | |

| |See, here table 3 has a tow primary key before we can describe that two tables as a stu_course and | |

| |see table 1 have a primary key as a sid and table 2 have a primary key as cid and when we are | |

| |describe that as a table 3 stu_course sid & cid is a primary key as a reference key it is called a | |

| |composite key. | |

| |Table 4: | |

| |Stu_cource1(sname,city,achiement,contactno,cname); | |

| |See, here there are not primary key in a table 4 it is called alternative key or alternative table. | |

| | | |

| |7.secondary key :- | |

| |The attribute that are not event the super key but can be still use for identification of record not| |

| |(unique) are know as secondary key or secondary table. | |

| |In short table have a no primary key. | |

| |Their records are not uniquely or randomly. | |

| |Secondary key is working as an alternate of primary key. | |

| |Means secondary key is null. | |

| |Secondary key is not unique. | |

|C. |Explain the concept of database management system. | |

|Ans |DBMS: database management system. | |

| |Database: a systematic type of argument of related information is called a database. | |

| |DBMS: Database management system is collection of interrelated data and a set of program to access | |

| |those data. | |

| |It is a collection of inter related data. | |

| |It is set of program to access the data | |

| |DBMS is a contain of information about a particular enterprise. | |

| |DBMS provide an environment that is both convenient and efficient to use. | |

| |Goal of DBMS: DBMS is providing a way to store and relative database information that is both | |

| |convenient and efficient. | |

| |Database system application: | |

| |Banking. | |

| |Airlines. | |

| |Universities/schools | |

| |Credit card transitions. | |

| |Telecommunication. | |

| |Finance. | |

| |Sales. | |

| |On-line retails. | |

| |Manufacturing. | |

| |Human resource. | |

| |Purpose of database system(drawback of file processing system): | |

| |Data redundancy and inconsistency. | |

| |Difficulty in accessing data. | |

| |Data isolation. | |

| |Integrity problem. | |

| |Atomicity problem. | |

| |Concurrent-access anomalies. | |

| |Security problem. | |

| | | |

| |Advantage of DBMS: | |

| |Data independency. | |

| |Efficient data. | |

| |Data administrator. | |

| |Concurrent & cress recovery. | |

| |Reduce application development time. | |

| |Feasibility. | |

| |Fast response to information request. | |

| |Multi access. | |

| |Lest store age. | |

| | | |

| |Advantage of DBMS in brief: | |

| |Application program should be as independent as possible from details of data representation and | |

| |storage. The DBMS can provide an abstract view of the data to insulate application code form such | |

| |detail. | |

| |A DBMS utilize avarety of sophisticated techniques to store adretrive data efficiently. This feature| |

| |is especially impotent if the data is stored on external storage devices. | |

| |If data is always accessed through the DBMS, the DBMS can enforce integrity constrain on the data | |

| |for example before inserting on the data for an employee. | |

| |When several users share the data cartelization the administrator of data can offer significant | |

| |improvements. Experienced professionals who understand the nature of data being managed. | |

| |A DBMS schedules concurrent access tit hr data in such a manner that user can think of the data as | |

| |being accessed by only one user. | |

| |Clearly the DBMS supports many important functions that are common to many applications accessing | |

| |store in the DBMS. | |

| | | |

|Q-5 |Consider the following relations. |12. |

| |Myemp(empno,ename,doj,sal,desig) | |

| |♫ create table myemp | |

| |( | |

| |Empno number primary key, | |

| |Ename text, | |

| |Doj text, | |

| |Sal currency, | |

| |Design text | |

| |); | |

| |Project(pcode,pname) | |

| |♫ create table project | |

| |( | |

| |Pcode text primary key, | |

| |Pname text | |

| |); | |

| |Emp_prog(pcode,empno) | |

| |♫ create table emp_prog | |

| |( | |

| |Pcode text references project(pcode), | |

| |Empno number references myemp(empno) | |

| |); | |

| |When we was a create table then we must be insert records and when we insert records then we must be| |

| |follow insert command query it is given as below with example | |

| |Syntax :- | |

| |Insert into | |

| |Values(value1,value2,value3,……value_n); | |

| |Example :- | |

| |Insert into myemp | |

| |Values(1,’abc’,’01-01-2001’,12000’programmer’); | |

| | | |

| |Insert into project | |

| |Values(‘p001’,xyz’); | |

| | | |

| |Insert into emp_prog | |

| |Values(‘p001’,’02’); | |

| |Ms access is a case sensitive then if you was wrote fields names in upper or lower case and you may | |

| |be use that field as a references then you must be write in same name and same case. | |

| |Write a appropriate sql statement for the following : | |

|1. |Get the details of employees, whose designation start with an alphabet ‘p’ and have length of more | |

| |than 10 characters. | |

|Ans: | | |

|2. |Get the details of all employees whose designation are “programmer” and not allocate any project. | |

|Ans: | | |

|3. |Get the name of an employees who is working on project ‘p001’ and ‘p002’ both | |

|Ans: | | |

|4. |Get the name of an employees working on project ‘p001’ | |

|Ans: | | |

|5. |Get the details of employees who get minimum salary and working on project ‘p001’ | |

|Ans: | | |

[pic]

-----------------------

Primary key

|name |balance |

|900 |55 |

|556 |100000 |

|647 |105366 |

|801 |10533 |

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

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

Google Online Preview   Download