Lovely Professional University 2011 B.Tech Computer Science and Engineering Database management system-assignment 1 solution - Question Paper

PART A

1. Design an ER diagram for an IT training group database that will meet the information needs for its training program. Clearly indicate the entities, relationships and the key constraints. The description of the environment is as follows:

The company has 12 instructors and can handle upto 100 trainees for each training session. The company offers 5 Advanced technology courses, each of which is taught by a team of 2 or more instructors Each instructor is assigned to a maximum of two teaching teams or may be assigned to do research Each trainee undertakes one Advanced technology course per training session.

ANS:

100 12

1 5 2

			Advanced technology courses

1

2. Identify at least 5 DBMS successfully running in market and compare their features.

ANS:

1.      IBM's DB2- The IBM DB2 Enterprise Server Edition is a relational model database server developed by IBM. It primarily runs on UNIX (namely AIX), Linux, IBM i, z/OS and Windows servers. DB2 also powers the different IBM InfoSphere Warehouse editions. Alongside DB2 is another RDBMS: Informix, which was acquired by IBM in 2001.  DB2 also lets you store some parts of your code at the server instead of keeping all of it in your client application. This can have performance and maintenance benefits.

There are features to protect data and to define relationships between data. As well, there are object-relational features to create flexible, advanced applications. You can use some features in more than one way. For example, constraints enable you to protect data and to define relationships between data values. Here are some key DB2 features:

         Constraints

         User-defined types (UDTs) and large objects (LOBs)

         User-defined functions (UDFs)

         Triggers

         Stored procedures

2.   Oracle- The Oracle Database is an object-relational database management system(ORDBMS) produced and marketed by Oracle Corporation.

Larry Ellison and his friends and former co-workers Bob Miner and Ed Oates started the consultancy Software Development Laboratories (SDL) in 1977. SDL developed the original version of the Oracle software. The name Oracle comes from the code-name of a CIA-funded project Ellison had worked on while previously employed by Ampex.

         Scalability and Performance

         Manageability

         Backup and Recovery

         High availability

         Business Intelligence

         Content Management

         Security

         Data integrity/Triggers

         Information Integration Features

3.      Microsoft SQL Server- Microsoft SQL Server is a relational model database server produced by Microsoft. Its primary query languages are T-SQL and ANSI SQL.

         Database Mirroring

         Online Restore

         Online Indexing Operations

         Fast Recovery

         Standards-based Information Access SQL

         Server Management Studio

         Dedicated Administrator Connection

4.      PostgreSQL- PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages). It includes most SQL:2008 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage of binary large objects, including pictures, sounds, or video. It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others, and exceptional documentation.

5.      MY SQL- MySQL is a relational database management system (RDBMS)^[1] that runs as a server providing multi-user access to a number of databases. MySQL is officially pronounced /maɪˌɛskjuːˈɛl/ ("My S-Q-L"),^[2] but is often also pronounced /maɪˈsiːkwəl/ ("My Sequel"). It is named after developer Michael Widenius' daughter, My. The SQL phrase stands for Structured Query Language.^[3]

The MySQL development project has made its source code available under the terms of the GNU General Public License, as well as under a variety ofproprietary agreements. MySQL was owned and sponsored by a single for-profit firm, the Swedish company MySQL AB, now owned by Oracle Corporation.

The operating systems the RDBMSes can run on:

Information about what fundamental RDBMS features are implemented natively.

3. How the various components of database management system interact for transaction Processing.

ANS: The various components of database management system are:

  DBMS Engine accepts logical requests from various other DBMS subsystems, converts them into physical equivalents, and actually accesses the database and data dictionary as they exist on a storage device.

  Data Definition Subsystem helps the user create and maintain the data dictionary and define the structure of the files in a database.

  Data Manipulation Subsystem helps the user to add, change, and delete information in a database and query it for valuable information. Software tools within the data manipulation subsystem are most often the primary interface between user and the information contained in a database. It allows the user to specify its logical information requirements.

  Application Generation Subsystem contains facilities to help users develop transaction-intensive applications. It usually requires that the user perform a detailed series of tasks to process a transaction. It facilitates easy-to-use data entry screens, programming languages, and interfaces.

  Data Administration Subsystem helps users manage the overall database environment by providing facilities for backup and recovery, security management, query optimization, concurrency control, and change management.

The transaction mechanism: A database transaction mechanism ideally guarantees ACID properties in order to ensure data integrity despite concurrent user accesses (concurrency control), and faults (fault tolerance). It also maintains the integrity of the data in the database. The DBMS can maintain the integrity of the database by not allowing more than one user to update the same record at the same time. The DBMS can help prevent duplicate records via unique index constraints; for example, no two customers with the same customer numbers (key fields) can be entered into the database.

PART B

4. Comment on the significance of administration of a database system. What are the various functions associated with database administration?

ANS: A database administrator (DBA) is a person responsible for the design, implementation, maintenance and repair of an organization's database. They are also known by the titles Database Coordinator or Database Programmer, and are closely related to the Database Analyst, Database Modeler, Programmer Analyst, and Systems Manager. The role includes the development and design of database strategies, monitoring and improving database performance and capacity, and planning for future expansion requirements. They may also plan, co-ordinate and implement security measures to safeguard the database. Employing organizations may require that a database administrator have a certification or degree for database.

Database administrator's activities can be listed as below:

         Transferring Data

         Replicating Data

         Maintaining database and ensuring its availability to users

         Controlling privileges and permissions to database users

         Monitoring database performance

         Database backup and recovery

         Database security

5. Explain the concepts of Generalization and Specialization with examples.

ANS:

Generalization: The design process may also proceed in a bottom-up manner, in which multiple entity sets are synthesized into a higher-level entity set on the basis of common features. The database designer may have first identified a customer entity set with the attributes name, street, city, and customer-id, and an employee entity set with the attributes name, street, city, employee-id, and salary. There are similarities between the customer entity set and the employee entity set in the sense that they have several attributes in common. This commonality can be expressed by generalization, which is a containment relationship that exists between a higher-level entity set and one or more lower-level entity sets. In our example, person is the higher-level entity set and customer and employee are lower-level entity sets. 

Specialization: An entity set may include subgroupings of entities that are distinct in some way from other entities in the set. For instance, a subset of entities within an entity set may have attributes that are not shared by all the entities in the entity set. The E-R model provides a means for representing these distinctive entity groupings. Consider an entity set person, with attributes name, street, and city. A person may be further classified as one of the following: 
customer 
employee

Each of these person types is described by a set of attributes that includes all the attributes of entity set person plus possibly additional attributes. For example, customer entities may be described further by the attribute customer-id, whereas employee entities may be described further by the attributes employee-id and salary. The process of designating subgroupings within an entity set is called specialization. The specialization of person allows us to distinguish among persons according to whether they are employees or customers.