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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
Related searches
- 2009 vantage v8 for sale
- what happened in 2009 timeline
- 2009 ford f150 body parts
- 2009 important events in america
- ford f150 2009 parts
- 2009 world events top 10
- an education 2009 cast
- 2009 first time homebuyer credit
- minecraft 2009 download
- djia 2009 to present
- dow jones 2009 to 2019
- 2009 aston martin vantage reliability