Q1. Consider a small institute in which students register through on-line mode for programmes run by the institute. A student can be a full time or part time . A program can be offered through a normal mode ( face to face) or on-line. Every student necessarily registers in at least one programme and at most two programmes. After completion of a program, students are issued a certificate. Assuming suitable attributes, design an EER Diagram for the same.
a) Draw the EER (extended ER) diagram for the above problem showing all entities, attributes and relationship. Also identify multi-valued and derived attributes.
Answer : -
Q1. b) Draw the appropriate tables and relationship among the tables for the above diagram and normalize the tables up to 3NF.
Answer : -
Table Name : course
| Field Name | Data Type | Constraint | Description |
|---|---|---|---|
| course_code | varchar(10) | Primary Key | Uniquely identify each Course/Subject in this table |
| course_name | varchar(50) | Name of the Subject |
Table Name : professor
| Field Name | Data Type | Constraint | Description |
|---|---|---|---|
| professor_id | varchar(10) | Primary Key | Uniquely identify each Professor in this table |
| p_name | varchar(50) | Professor name | |
| p_address | varchar(100) | Professor address | |
| p_phone | bigint | Professor phone number |
Table Name : teach
| Field Name | Data Type | Constraint | Description |
|---|---|---|---|
| course_code | varchar(10) | Forign Key | Course identification code |
| professor_id | varchar(10) | Forign Key | Professor identification number |
Table Name : programme
| Field Name | Data Type | Constraint | Description |
|---|---|---|---|
| p_code | varchar(10) | Primary Key | Uniquely identify each Programme in this table |
| programme_name | varchar(10) | Name of the programme | |
| total_fees | int | Total fees require for the programme |
Table Name : contents
| Field Name | Data Type | Constraint | Description |
|---|---|---|---|
| p_code | varchar(10) | Forign Key | Programme identification code |
| course_code | varchar(10) | Forign Key | Course identification code |
Table Name : class
| Field Name | Data Type | Constraint | Description |
|---|---|---|---|
| c_code | varchar(20) | Primary Key | Uniquely identify each Class in this table |
| c_date | date | Class date | |
| start_time | time | Class starting time | |
| end_time | time | Class ending time | |
| course_code | varchar(10) | Forign Key | Course identification code |
| professor_id | varchar(10) | Forign Key | Professor identification number |
Table Name : online_class
| Field Name | Data Type | Constraint | Description |
|---|---|---|---|
| c_code | varchar(20) | Foreign Key | Class identification code |
| login_url | varchar(100) | Login URL of the class | |
| login_code | varchar(50) | Login code of the class |
Table Name : offline_class
| Field Name | Data Type | Constraint | Description |
|---|---|---|---|
| c_code | varchar(10) | Foreign Key | Class identification number |
| room_no | varchar(10) | Room number |
Table Name : student
| Field Name | Data Type | Constraint | Description |
|---|---|---|---|
| student_id | varchar(10) | Primary Key | Uniquely identify each Student in this table |
| s_name | varchar(50) | Student name | |
| s_address | varchar(100) | Student address | |
| s_phone | bigint | Student phone number |
Table Name : part_time
| Field Name | Data Type | Constraint | Description |
|---|---|---|---|
| student_id | varchar(10) | Foreign Key | Student identification number |
| total_no_of_classes | int | Number of classes attend by the student |
Table Name : full_time
| Field Name | Data Type | Constraint | Description |
|---|---|---|---|
| student_id | varchar(10) | Foreign Key | Student identification number |
| percentage_of_attendance | int | Percentage of attendance |
Table Name : register
| Field Name | Data Type | Constraint | Description |
|---|---|---|---|
| p_code | varchar(10) | Foreign Key | Programme identification code |
| student_id | varchar(10) | Foreign Key | Student identification number |
Table Name : attendance
| Field Name | Data Type | Constraint | Description |
|---|---|---|---|
| c_code | varchar(20) | Foreign Key | Class identification code |
| student_id | varchar(10) | Foreign Key | Student identification number |
Table Name : exam_schedule
| Field Name | Data Type | Constraint | Description |
|---|---|---|---|
| course_code | varchar(10) | Foreign Key | Course identification code |
| exam_date | date | Examination date | |
| exam_time | time | Examination start time | |
| duration | int | Exam duration in minutes |
Table Name : result
| Field Name | Data Type | Constraint | Description |
|---|---|---|---|
| course_code | varchar(10) | Foreign Key | Course identification code |
| student_id | varchar(10) | Foreign Key | Student identification number |
| marks | int | Obtain marks | |
| grade | varchar(4) | Obtain grade |
Q1. c) Include generalization and aggregation features in the diagram, draw their tabular representations and UML class diagram. Explain
Answer : - Coming Soon
Q1. d) Identify weak entity sets in the above diagram if any. Show how will you convert a weak entity set to a strong entity set? What is the need of such task?
Answer : - An entity type should have a key attribute which uniquely identifies each entity in the entity set, but there exists some entity type for which key attribute can’t be defined. These are called Weak Entity type.
The entity sets which do not have sufficient attributes to form a primary key are known as weak entity sets and the entity sets which have a primary key are known as strong entity sets.
In the above diagram exam_schedule is the week entity.
exam_schedule(course_code, exam_date, exam_time, duration)
We can convert any weak entity set to a strong entity set by simply adding appropriates attributes.
exam_schedule(exam_code, course_code, exam_date, exam_time, duration)
Q1. e) Identify multi-valued dependency in the above diagram if any. Justify.
Answer : - No multi-value dependency present in the above diagram
Q1. f) Create an XML schema for the grade card to be issued by the division having details: student id, programme code, course id, grade, program delivery mode, assignments marks, TEE marks.
Answer : -
GradeCard.xsd
| <?xml version="1.0"?> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="http://www.questionsolves.com" xmlns="http://www.questionsolves.com" elementFormDefault="qualified"> <xs:element name="GradeCard"> <xs:complexType> <xs:sequence> <xs:element name="StudentId" type="xs:integer"/> <xs:element name="ProgrammeCode" type="xs:string"/> <xs:element name="Course" maxOccurs="unbounded"/> <xs:complexType> <xs:sequence> <xs:element name="CourseID" type="xs:string"/> <xs:element name="AssignmentMarks" type="xs:integer"/> <xs:element name="TEEMarks" type="xs:integer"/> <xs:element name="Grade" type="xs:string"/> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:schema> |
Q2. What are XML databases? What are the benefits of using XML? What are its applications? Explain.
Answer : - Coming Soon
Q3. Define cursors, stored procedures and triggers and write SQL syntaxes for each. What are the advantages and disadvantages of stored procedures? What are the main features of a cursor?
Answer : -
Cursor - A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it.
This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set
Syntax :
CURSOR cursor_name IS select_statement; cursor_name – A suitable name for the cursor. |
Example
Copy data from "old_accounts" table to "new_accounts" table
Old Table - old_accounts (account_number, customer_id, branch_id, balance)
New Table - new_accounts (account_number, customer_id, balance)
| CREATE OR REPLACE PROCEDURE copy_table AS account_row old_accounts%ROWTYPE; CURSOR account_cursor IS SELECT * FROM old_accounts; BEGIN OPEN account_cursor; LOOP FETCH account_cursor INTO account_row; EXIT WHEN account_cursor%NOTFOUND; INSER INTO new_accounts VALUES(account_row.account_number, account_row.customer_id, account_row.balance); END LOOP; CLOSE account_cursor; COMMIT; END; / |
Stored Procedures - A procedure is a module that performs one or more actions.
Syntax :
| CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter_name [ IN | OUT | IN OUT ] type , parameter_name [ IN | OUT | IN OUT ] type , . . . . . )] { IS | AS } BEGIN < procedure_body > END; / |
Example - Create a procedure to transfer amount from account A to account B.
account (account_number, customer_id, branch_id, balance)
| CREATE OR REPLACE PROCEDURE amount_transfer (debit_account IN NUMBER, credit_account IN NUMBER, amount IN NUMBER) AS BEGIN UPDATE account SET balance=balance-amount WHERE account_number=debit_account; UPDATE account SET balance=balance+amount WHERE account_number=credit_account; COMMIT; END; / |
Triggers - Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events:
- A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).
- A database definition (DDL) statement (CREATE, ALTER, or DROP).
- A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
Syntax :
| CREATE [OR REPLACE] TRIGGER trigger_name { BEFORE | AFTER | INSTEAD OF } { INSERT [OR] | UPDATE [OR] | DELETE } [ OF col_name ] ON table_name [ FOR EACH ROW ] WHEN(condition) DECLARE Declaration-statements BEGIN Executable-statements EXCEPTION Exception-handling-statements END; / |
Example - Write a trigger for a bank which show the transaction details of an account when transaction is made from this account.
account (account_number, customer_id, branch_id, balance)
| CREATE OR REPLACE TRIGGER transaction AFTER UPDATE OF balance ON Account FOR EACH ROW WHEN (NEW.balance > 0) DECLARE amount NUMBER(10,2); BEGIN IF :NEW.balance > :OLD.balance THEN amount := :NEW.balance - :OLD.balance; dbms_output.put_line('Amount Credit : ' || amount); ELSE amount := :OLD.balance - :NEW.balance; dbms_output.put_line('Amount Debit : ' || amount); END IF; dbms_output.put_line('Old Balance : ' || :OLD.balance); dbms_output.put_line('New Balance : ' || :NEW.balance); END; / |
Advantages of Stored Procedures
Since procedures are compiled once and stored in executable form, whenever you call a procedure the response is quick.
You can group all the required SQL statements in a procedure and execute them at once.
Once you compile a stored procedure you can use it in any number of applications. If any changes are needed you can just change the procedures without touching the application code.
Access to the Oracle data can be restricted by allowing users to manipulate the data only through stored procedures that execute with their definer’s privileges.
Disadvantages of Stored Procedures
Testing of a logic which is encapsulated inside a stored procedure is very difficult. Any data errors in handling stored procedures are not generated until runtime.
An extra developer in the form of DBA is required to access the SQL and write a better stored procedure. This will automatically incur added cost.
Complex stored procedures will not always port to upgraded versions of the same database. This is specially true in case of moving from one database type(Oracle) to another database type(MS SQL Server).
Q4. Explain primary key, foreign key, super key and composite key with the help of an example for each key. Also write the important features of each.
Answer :
Primary Key - A primary key is a column (or columns) in a table that uniquely identifies the rows in that table.
Table Name - Employee
| Employee_id | Employee_name | Department_id |
|---|---|---|
| E-0001 | Debabrata Panchadhyay | D-001 |
| E-0005 | Amit Das | D-002 |
| E-0008 | Sohini Das | D-002 |
| E-0010 | Debarghya Chakraborty | D-001 |
In the Employee table Employee_id is the primary key.
The value 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.
Foreign Key - Foreign keys are columns that point to primary key columns.
Table Name - Employee
| Employee_id | Employee_name | Department_id |
|---|---|---|
| E-0001 | Debabrata Panchadhyay | D-001 |
| E-0005 | Amit Das | D-002 |
| E-0008 | Sohini Das | D-002 |
| E-0010 | Debarghya Chakraborty | D-001 |
Table Name - Department
| Department_id | Department_name | Total_employee |
|---|---|---|
| D-001 | Website Devlopment | 50 |
| D-002 | Software Devlopment | 25 |
Employee_id is the primary key of the table Employee and Department_id is the primary key of the table Department but in Employee table Department_id is the foreign key that points to the primary key in the Department table.
Super Key - A super key is a combination of all possible attribute which can uniquely identify the rows(tuples) in a table. This means that a superkey may have some extra attribute which isn't necessary for uniquely identifying the rows in the table.
Table Name - Employee
| Employee_id | Employee_name | Department_id |
|---|---|---|
| E-0001 | Debabrata Panchadhyay | D-001 |
| E-0005 | Amit Das | D-002 |
| E-0008 | Sohini Das | D-002 |
| E-0010 | Debarghya Chakraborty | D-001 |
In the given Employee table we can have the following keys as the super key.
1. { Employee_id }
2. { Employee_id, Employee_name }
3. { Employee_id, Department_id }
4. { Employee_id, Employee_name, Department_id }
Composite Key - If any single attribute of a table is not capable of being the key i.e it cannot identify a row uniquely, then we combine two or more attributes to form a key. This is known as a composite key.
Table Name - Result
| Student_id | Course_code | Marks |
|---|---|---|
| MCA-00001 | MCS-041 | 72 |
| MCA-00001 | MCS-043 | 68 |
| MCA-00002 | MCS-041 | 75 |
| MCA-00002 | MCS-043 | 62 |
The Composite Key in Result table is { Student_id, Course_code }
Q5. What are the algorithms that can be used to implement JOIN operation? What is the criteria for selecting a particular join algorithm? Explain Hash Join algorithm and perform its cost calculation
Answer : - Coming Soon
Q6. What is concurrency control? What is the need of applying concurrency control methods in database? Give an example. Write the algorithm of timestamp based ordering protocol and explain through an example. What are the problems with this algorithm and what are the proposed solutions for it?
Answer : - Coming Soon
Q7. How is recovery with concurrent transactions performed? Discuss.
Answer : - Coming Soon
Q8. State the differences between classification and clustering. Write an algorithm of K-Means clustering. Explain the algorithm with the help of an example.
Answer : - Coming Soon
Q9. What are Semantic databases? Give the features of semantic databases. Discuss the process of searching the knowledge in these databases.
Answer : - The semantic data model is a method of structuring data in order to represent it in a specific logical way. It is a conceptual data model that includes semantic information that adds a basic meaning to the data and the relationships that lie between them. This approach to data modeling and data organization allows for the easy development of application programs and also for the easy maintenance of data consistency when data is updated.
In this data model the data is organized in such a way that it can be interpreted meaningfully without human intervention.
How Does Semantic Data Work ?
Data is organized based on binary models of objects, usually in groups of three parts: two objects and their relationship. For example, if one wanted to represent a cat sitting on a table, the data organization might look like this: CAT TABLE. The objects (cat and table) are interpreted with regard to their relationship (sitting on). The data is organized linearly, telling the software that since CAT comes first in the line, it is the object that acts. In other words, the position of the word tells the software that the cat is on the table and not that the table is sitting on the cat. Databases designed around this concept have greater applicability and are more easily integrated into other databases.
Goals of Semantic Data
Semantic data systems are designed to represent the real world as accurately as possible within the data set. Data symbols are organized linearly and hierarchically to give certain meanings like the one described above. By representing the real world within data sets, semantic data allow machines to interact with worldly information without human interpretation.
Applications of Semantic Data
Semantic data is very promising for the enterprise world. Database Management Systems can be integrated with one another and compared. For example, since a company's entire infrastructure is represented within the data model, the model can be compared to those of the company's vendors to identify areas of inconsistency and possible improvement. This would help streamline the relationship between company and vendors, making database sharing and integration much simpler. Environments and systems can also be organized graphically within a database to give a more visually-based representation of that system or environment. Recently, a semantic language called Gellish was developed as a formal language to represent data models. Gellish can be interpreted solely by computers and needs no human interaction.