DBMS END SEM 2009

THANKS TO RAHUL SINGH CHOUDHARY 

CDBMS 2009

PART A :

Q1: What is CASE?
Ans: Computer-aided software engineering (CASE) is the scientific application of a set of tools and methods to a software system which is meant to result in high-quality, defect-free, and maintainable software products. It also refers to methods for the development of information systems together with automated tools that can be used in the software development process.

Q2: Difference between Codification system and numbering system?
Ans: The Codification System (NCS for short) is a standard approach to identify, classify and number items of supply. This is applicable to items that are repetitively used and stocked (e.g., repair parts, equipment, food items, etc.). The result is a unique identification and a data set that can be easily shared and understood by a wide range of users. The process of codification (or cataloging) involves naming, classifying, describing the item.

Q3: What is encryption?
Ans: Encryption is the process used to hide our data, or the contents of a message, from prying eyes throughout the internet. During transmission (such as through a secure socket layer), the data is disguised using codes so that no one along the chain of networks that the data passes though to get to its source can understand the information being sent. When the data arrives at its destination, it is decrypted to reveal the information being transmitted.
The process of disguising the data is called encryption and the process of revealing the data from its encrypted form is called decryption. Both of these are common techniques used in cryptography - the scientific discipline behind secure connections. The processes are done using mathematical logic, or algorithms.

Q4: Name of the types of network architecture?
Ans: A network architecture in which each computer or process on the network is either a client or a server. Servers are powerful computers or processes dedicated to managing disk drives (file servers), printers (print servers), or network traffic (network servers). Clients are PCs or workstations on which users run applications. Clients rely on servers for resources, such as files, devices, and even processing power.

Another type of network architecture is known as a peer-to-peer architecture because each node has equivalent responsibilities. Both client/server and peer-to-peer architectures are widely used, and each has unique advantages and disadvantages.

Client-server architectures are sometimes called two-tier architectures.
        Client-Server (two tier)
        Client server (three tier)
        Peer-to-peer (p2p)
        Distributed Server
        Web server
        Virtual Server

Q5: Difference between virtual company and Click & Mortar Company?
Ans: virtual company, an organization that uses computer and telecommunications technologies to extend its capabilities by working routinely with employees or contractors located throughout the country or the world. Using e-mail, faxes, instant messaging, data and videoconferencing, it implies a high degree of telecommuting as well as using remote facilities. The most extreme type of virtual company is one with only "virtual employees" and no central office. Everyone works from home, including top management.
A click and mortar company is a company that has a traditional physical presence but has added e-marketing to their marketing strategy. An example of a click and mortar company includes JC Penny's, Wal-Mart, K-Mart, Target, etc. Basically it is any company with a website at which a buyer can make purchases either online or in the "brick and mortar" store.

Q6: What is specialization?
Ans: Specialization is the separation of tasks within a system. Agreement within a community, group, or organization under which the members most suited (by virtue of their natural aptitude, location, skill, or other qualification) for a specific activity or task assume greater responsibility for its execution or performance.

Q7: Data warehouse and Operational data store?
Ans: A data warehouse consists of a computer database responsible for the collection and storage of information for a specific organization. This collection of information is then used to manage information efficiently and analyze the collected data. Although data warehouses vary in overall design, majority of them are subject oriented, meaning that the stored information is connected to objects or events that occur in reality. The data provided by the data warehouse for analysis provides information on a specific subject, rather than the functions of the company and is collected from varying sources into one unit having time-variant.

An operational data store (or "ODS") is a database designed to integrate data from multiple sources for additional operations on the data. The data is then passed back to operational systems for further operations and to the data warehouse for reporting.

Q8: Importance of Knowledge management?
Ans: There are following:
1.       Handling of any task, process or interaction in ordinary way or extra ordinary way
2.       MIS and DSS can be well guided.
3.       Collection capability of an individual /work group.
4.       Practice of KM enhances performance/ Productivity increase in continual manner
5.       KM helps corporate in protecting its  market share, build future opportunity share and stay ahead of competition.

Q9: Corporate/enterprise database?

Ans:   A database of information that be accessed by many individuals or a network for the use of editing, creating, and manipulating data and information. In GIS, an enterprise database schema is used to distribute and maintain geographic data in the same way. Enterprise database management ensures database quality, and performance which serves both internal and external users.
No. of users = more than 100.
Architecture= Client-Server (distributed or parallel)
Size= Gigabyte-terabyte.

Q10. Who inserts new records into database?

Ans: DBA,
Database administrator's activities can be listed as below:
1. Maintaining database and ensuring its availability to users
2. Controlling privileges and permissions to database users
3. Monitoring database performance
4. Database backup and recovery
5. Database security
6. Transferring Data
7. Replicating Data

Q11: Difference between Arithmetic unit and logical unit of CPU?
Ans: An arithmetic-logic unit (ALU) is the part of a computer processor (CPU) that carries out arithmetic and logic operations on the operands in computer instruction words. In some processors, the ALU is divided into two units, an arithmetic unit (AU) and a logic unit (LU).
Main difference between AU and Lu is that AU perform all arithmetic function of cpu e.g Addition, Subtraction, Division, multiplication whereas LU perform all the logical functions like AND, OR, etc.


Q12: Define Domain with respect to relational model?
Ans: In Relational model the basic relational building block is the domain or data type, usually abbreviated nowadays to type. A tuple is an ordered set of attribute values. An attribute is an ordered pair of attribute name and type name. An attribute value is a specific valid value for the type of the attribute. This can be either a scalar value or a more complex type.

PART B
Q1: List of any two risks of database approach?
Ans:

Risks:
Legality (having to adhere to laws for keeping personal data)
Getting data wrong and seriously annoying people
Keeping data secure so it can be stolen/changed

Costs:
Maintenance (making sure that no-one has hacked it and it's all working fine)
Data Entry (perhaps paying multiple people to enter the same data so that it is definitely entered correctly)
The costs and risks are of DB approach are categorised into 5 factors. They are
        New specialized Personal
        Installation and management cost and complexity
        conversion costs
        Need for explicit backup and recovery
        organizational conflict

Q2: Four steps in data reconciliation process?

Ans: Data reconciliation process
1        Capture (or Extract)
       extracting the relevant data from the source files used to fill the EDW
       the relevant data is typically a subset of all the data that is contained in the operational systems
       two types of capture are:
       Static – A method of capturing a snapshot of the required source data at a point in time, for initial EDW loading
       Incremental - for ongoing updates of an existing EDW; only captures the changes that have occurred in the source data since the last capture



2. Scrub (or Data Cleanse)
       this is removing or correcting errors and inconsistencies present in operational data values (e.g., inconsistently spelled names, impossible birth dates, out-of-date zip codes, missing data, etc.)
       may use pattern recognition and other artificial intelligence techniques
       only part of the solution to poor quality data (see next slide)
       Formal program in total quality management (TQM) should be implemented. It focuses on defect prevention, rather than defect correction.



3        Transform
       converts selected data from the format of the source system to the format of the EDW
       Record-Level Functions
       Selection - selecting data according to predefined criteria (we can use SQL: Select … )
       Joining - consolidating related data from multiple sources (SQL: join tables together if the source data are relational)
       Normalization - discussed in Chapter 5
       Aggregation - summarizing detailed data (for data marts)
       Field-Level Functions
       Single-field transformations 
       Multi-field transformations

4        Load and Index
       the last step in data reconciliation is to load the selected data into the EDW and to create the desired indexes
       two modes for loading data:
       Refresh Mode - employs bulk writing or rewriting of the data at periodic intervals
       Most often used when the warehouse is first created
       Update Mode - only changes in the source data are written to the data warehouse
       Typically used for ongoing data warehouse maintenance
       To support the periodic nature of warehouse data, these new records are usually written to the data warehouse without overwriting or deleting previous records



Q3: Benefits of electronic auction?
Ans: Commercial world has been completely transformed with the advent of Internet. Days of running to the market and executing business transactions on head to head contact are long over. These are the times when billions of transactions are made online between parties who have never seen each other and are located far apart. Such trend has naturally crept into the auction methods and thus online auction is fast becoming the trend in the present day business.

Multiple advantages accrue when a business house uses the online auction model for carrying out its transactions. Some of the major advantages of electronic auction are –
        Absence of any time constraints since the Internet functions 24/7 and any time of the day or night can be used for business transactions.
        Conveniences of buyers increase considerably since they get sufficient time online to search, decide and also to bid.
        The method crosses all geographical barriers. People who are buying products or services can participate at the farthest and remotest corner of the globe in the process.
        The options and scope are much greater in online auction in comparison to traditional auction. In the virtual environment it is possible viewing thousands of items, check and compare their features and prices that would never be possible in traditional form of auction.
        Any number of bidders can participate in the electronic auction. In traditional process the number will be limited by space and other local constraints.
        Absence of any requirement of shipping items to any central locations. In result the costs are reduced considerably and the minimum acceptable prices also become lower consequentially.
        Other advantages of using the online auction procedure are enhancement of intensities of social reactions, increase in number of bidders for products and services, and deriving network economy.

Q4: Advantages of Database System?
Ans: Benefits of DBMS (Database Management Systems) are followings:
A true DBMS offers several advantages over file processing. The principal advantages of a DBMS are the followings:
• Flexibility: Because programs and data are independent, programs do not have to be modified when types of unrelated data are added to or deleted from the database, or when physical storage changes.
• Fast response to information requests: Because data are integrated into a single database, complex requests can be handled much more rapidly then if the data were located in separate, non-integrated files. In many businesses, faster response means better customer service.

• Multiple access: Database software allows data to be accessed in a variety of ways (such as through various key fields) and often, by using several programming languages (both 3GL and nonprocedural 4GL programs).
• Lower user training costs
: Users often find it easier to learn such systems and training costs may be reduced. Also, the total time taken to process requests may be shorter, which would increase user productivity.
• Less storage: Theoretically, all occurrences of data items need be stored only once, thereby eliminating the storage of redundant data. System developers and database designers often use data normalization to minimize data redundancy.
• Data is integrated: means all related data on different computers are linked in such away that all data are linked with each other.
• Data duplication is reduced : As data is integrated ,present on different locations so chances of data duplication are much reduced and date is updated form.
• Data is easy to understand: As data is managed according to the needs of the user and it is in very easy format so that you have no difficulty in using the data through database management system
• Data Validity : Using DBMS your data is more valid and there are less chances that data is orthodox. And there are different checks are applied to check either valid data is entered or not.
• Data Security : using DBMS your data is much secured. Only the relevant users know the required software to operate.

Q5: Types of communication medium?
Ans: Types of Communication Medium
We divide the different types of communication medium into two different categories:
1. Physical media
2. Mechanical media
Physical media
With physical media we mean channels where the person who is talking can be seen and heard by the audience. The whole point here is to be able to not only hear the messages but also to see the body language and feel the climate in the room. This does not need to be two-way channels. In certain situations the receiver expect physical communication. This is the case especially when dealing with high concern messages, e.g. organizational change or downsizing. If a message is perceived as important to the receiver they expect to hear it live from their manager.


        Large meetings, town hall meetings
        Department meetings (weekly meetings)
        Up close and personal (exclusive meetings)
        Video conferences
        Viral communication or word of mouth

Mechanical media
The second of the two types of communication medium is mechanical media. With mechanical media we mean written or electronic channels. These channels can be used as archives for messages or for giving the big picture and a deeper knowledge. But they can also be very fast. Typically though, because it is written, it is always interpret by the reader based on his or her mental condition. Irony or even humour rarely travels well in mechanical channels.
        E-mail
        Weekly letters or newsletters
        Personal letters
        Billboards
        Intranet
        Magazines or papers
        Sms
        Social media

Push or Pull
You can also divide the different types of communication medium in Push or Pull channels.
Push channels are channels where the sender is pushing the message to the receiver. Meaning it is up to the sender to control the communication.
        E-mail
        Newsletters and letters (if sent out)
        Magazines (if sent out)
        Meetings
        Telephone
        Sms
Pull channels on the other hand is when the receiver is pulling the message from the sender. It is up to the receiver when he or she wants to take in the message.
        Intranet
        Billboards
        New letters and letters (if not sent out)
        Magazines (if not sent out)
        Social media

Q6: What is client/server architecture and processing?
Ans: 


S: 
Q7. Explain the SDLC?
Ans: 
The relationship of each stage to the others can be roughly described as a waterfall, where the outputs from a specific stage serve as the initial inputs for the following stage. During each stage, additional information is gathered or developed, combined with the inputs, and used to produce the stage deliverables. It is important to note that the additional information is restricted in scope; “new ideas” that would take the project in directions not anticipated by the initial set of high-level requirements are not incorporated into the project. Rather, ideas for new capabilities or features that are out-of-scope are preserved for later consideration.

After the project is completed, the Primary Developer Representative (PDR) and Primary End-User Representative (PER), in concert with other customer and development team personnel develop a list of recommendations for enhancement
of the current software.
These stages can be characterized and divided up in different ways, including the following[6]:
        Project planning, feasibility study: Establishes a high-level view of the intended project and determines its goals.
        Systems analysis, requirements definition: Refines project goals into defined functions and operation of the intended application. Analyzes end-user information needs.
        Systems design: Describes desired features and operations in detail, including screen layouts, business rules, process diagrams, pseudocode and other documentation.
        Implementation: The real code is written here.
        Integration and testing: Brings all the pieces together into a special testing environment, then checks for errors, bugs and interoperability.
        Acceptance, installation, deployment: The final stage of initial development, where the software is put into production and runs actual business.
        Maintenance: What happens during the rest of the software's life: changes, correction, additions, and moves to a different computing platform and more. This, the least glamorous and perhaps most important step of all, goes on seemingly forever.

Q8: Define entity integrity and referential integrity?
Ans:  In the relational data model,Entity integrity is one of the three inherent integrity rules. Entity integrity is an integrity rule which states that every table must have a primary key and that the column or columns chosen to be the primary key should be unique and not null. A direct consequence of this integrity rule is that duplicate rows are forbidden in a table. If each value of a primary key must be unique no duplicate rows can logically appear in a table. The NOT NULL characteristic of a primary key ensures that a value can be used to identify all rows in a table.
Within relational databases using SQL, entity integrity is enforced by adding a primary key clause to a schema definition. The system enforces Entity Integrity by not allowing operations (INSERT, UPDATE) to produce an invalid primary key. Any operation that is likely to create a duplicate primary key or one containing nulls is rejected. The Entity Integrity ensures that the data that you store remains in the proper format as well as comprehendible.
Referential integrity is a property of data which, when satisfied, requires every value of one attribute (column) of a relation (table) to exist as a value of another attribute in a different (or the same) relation (table).
Less formally, and in relational databases: For referential integrity to hold, any field in a table that is declared a foreign key can contain only values from a parent table's primary key or a candidate key. For instance, deleting a record that contains a value referred to by a foreign key in another table would break referential integrity. Some relational database management systems (RDBMS) can enforce referential integrity, normally either by deleting the foreign key rows as well to maintain integrity, or by returning an error and not performing the delete. Which method is used may be determined by a referential integrity constraint defined in a data dictionary.

PART C

Q1: What is Database and DBMS?
Ans:
What is a database? A database is a collection of information that is organized so that it can easily be accessed, managed, and updated. In one view, databases can be classified according to types of content: bibliographic, full-text, numeric, and images.
In computing, databases are sometimes classified according to their organizational approach. The most prevalent approach is the relational database, a tabular database in which data is defined so that it can be reorganized and accessed in a number of different ways. A distributed database is one that can be dispersed or replicated among different points in a network. An object-oriented programming database is one that is congruent with the data defined in object classes and subclasses.
Computer databases typically contain aggregations of data records or files, such as sales transactions, product catalogs and inventories, and customer profiles. Typically, a database manager provides users the capabilities of controlling read/write access, specifying report generation, and analyzing usage.
A database management system (DBMS), sometimes just called a database manager, is a program that lets one or more computer users create and access data in a database. The DBMS manages user requests (and requests from other programs) so that users and other programs are free from having to understand where the data is physically located on storage media and, in a multi-user system, which else may also be accessing the data. In handling user requests, the DBMS ensures the integrity of the data (that is, making sure it continues to be accessible and is consistently organized as intended) and security (making sure only those with access privileges can access the data). The most typical DBMS is a relational database management system (RDBMS). A standard user and program interface is the Structured Query Language ( SQL). A newer kind of DBMS is the object-oriented database management system (ODBMS).

 Q2: List of Database environment?
Ans: DBMS and Components of Database Environment
Database Management System:
A database management system (DBMS) is a collection of programs that are used to create and maintain a database. DBMS is a general-purpose software system that provides the following facilities:
1. It provides the facility to define the structure of the, database. The user can specify the data types, format, and constraints for the data to he stored in the database.
2. It provides the facility to store the data on some storage medium that is controlled by the DBMS.
3. It provides the facilities to insert, delete, update and retrieve specific data for generating reports etc.
Components of Database Environment:
The important components of a database environment are as follows:
1. Repository: A repository is a collection of all data definitions, data relationships, output styles and report formats etc. All this information is the metadata that is important to manage database.
2. Application Program: An application program is a program that is used to send commands to the database management system to manipulate database. These commands are sent to the DBMS through graphical user interface. The user interacts with the application program and the application program further interacts with the database management system.
3. User Interface: The user interface is a visual environment that is used by the user to communicate with the computer. It consists of menus, buttons and other components. All windows based software use graphical user interface. The user interface consists of the following components:
Forms: The forms are used to enter data in the database. A form consists of textboxes, labels and buttons that are used by the users for entering data easily. The user can also retrieve, change and update data by using forms.
Menus: Menus are a List of commands for performing different operations. Menus are frequently used in windows-based applications. The user can use them easily for manipulating the database.
Reports: Reports are the output of the database application. The user can generate different types of reports by manipulating the database. The information on the reports is arranged in different forms and may contain graphs, charts and tables etc.
4. Data Administrator: Data administrators are the persons who are responsible of the whole information system. They authorize access to the database as well as coordinate and monitor the use of database.
5. End User: End users are those persons who interact with the application directly. They are responsible to insert, delete and update data in the database. They get information from the system as and when required.

Q3: Functions of Database administrator?
Ans: Refer Answer 10.

Q4: List the DDL and DML command?
Ans: A Data Definition Language or Data Description Language (DDL) is a computer language for defining data structures. The term DDL was first introduced in relation to the Codasyl database model, where the schema of the database was written in a Data Description Language describing the records, fields, and "sets" making up the user Data Model.
CREATE TABLE statement
Perhaps the most common CREATE command is the CREATE TABLE command. The typical usage is:
CREATE [TEMPORARY] TABLE [table name] ( [column definitions] ) [table parameters].
For example, the command to create a table named employees with a few sample columns would be:
CREATE TABLE employees (  
    id            INTEGER   PRIMARY KEY,
    first_name    CHAR(50)  NULL,
    last_name     CHAR(75)  NOT NULL,
    dateofbirth   DATE      NULL
);
DROP statements
Drop - To destroy an existing database, table, index, or view.
DROP objecttype objectname.
For example, the command to drop a table named employees would be:
DROP TABLE employees;
The DROP statement is distinct from the DELETE and TRUNCATE statements, in that they do not remove the table itself. For example, a DELETE statement might delete some (or all) data from a table while leaving the table itself in the database, whereas a DROP statement would remove the entire table from the database.

ALTER statements
Alter - To modify an existing database object.
An ALTER statement in SQL changes the properties of an object inside of a relational database management system (RDBMS). The types of objects that can be altered depends on which RDBMS is being used. The typical usage is:
ALTER object type object name parameters.
For example, the command to add (then remove) a column named bubbles for an existing table named sink would be:
ALTER TABLE sink ADD bubbles INTEGER;
ALTER TABLE sink DROP COLUMN bubbles;

The Data Manipulation Language (DML) is used to retrieve, insert and modify database information. These commands will be used by all database users during the routine operation of the database. Let's take a brief look at the basic DML commands:

The Data Manipulation Language (DML) is used to retrieve, insert and modify database information. These commands will be used by all database users during the routine operation of the database. Let's take a brief look at the basic DML commands:

INSERT

The INSERT command in SQL is used to add records to an existing table. Returning to the personal_info example from the previous section, let's imagine that our HR department needs to add a new employee to their database. They could use a command similar to the one shown below:

INSERT INTO personal_info
values('bart','simpson',12345,$45000)

Note that there are four values specified for the record. These correspond to the table attributes in the order they were defined: first_name, last_name, employee_id, and salary.

SELECT

The SELECT command is the most commonly used command in SQL. It allows database users to retrieve the specific information they desire from an operational database. Let's take a look at a few examples, again using the personal_info table from our employees database.

The command shown below retrieves all of the information contained within the personal_info table. Note that the asterisk is used as a wildcard in SQL. This literally means "Select everything from the personal_info table."

SELECT *
FROM personal_info

Alternatively, users may want to limit the attributes that are retrieved from the database. For example, the Human Resources department may require a list of the last names of all employees in the company. The following SQL command would retrieve only that information:

SELECT last_name
FROM personal_info

Finally, the WHERE clause can be used to limit the records that are retrieved to those that meet specified criteria. The CEO might be interested in reviewing the personnel records of all highly paid employees. The following command retrieves all of the data contained within personal_info for records that have a salary value greater than $50,000:

SELECT *
FROM personal_info
WHERE salary > $50000

UPDATE

The UPDATE command can be used to modify information contained within a table, either in bulk or individually. Each year, our company gives all employees a 3% cost-of-living increase in their salary. The following SQL command could be used to quickly apply this to all of the employees stored in the database:

UPDATE personal_info
SET salary = salary * 1.03

On the other hand, our new employee Bart Simpson has demonstrated performance above and beyond the call of duty. Management wishes to recognize his stellar accomplishments with a $5,000 raise. The WHERE clause could be used to single out Bart for this raise:

UPDATE personal_info
SET salary = salary + $5000
WHERE employee_id = 12345

DELETE

Finally, let's take a look at the DELETE command. You'll find that the syntax of this command is similar to that of the other DML commands. Unfortunately, our latest corporate earnings report didn't quite meet expectations and poor Bart has been laid off. The DELETE command with a WHERE clause can be used to remove his record from the personal_info table:

DELETE FROM personal_info
WHERE employee_id = 12345

ALL THE BEST.

No comments: