PARUL- MCA – Advanced Database Management System
Design a Relational Schema
Question 1: Consider a university database that stores information about students, courses, and instructors. Each student has a unique student ID, name, and major. Each course has a course ID, course name, and is taught by an instructor. For advanced management of a Database System, design a relational schema for this database using primary and foreign keys. Write SQL statements to create the necessary tables.
Expected Response: Write CREATE TABLE SQL statements for Students, Courses, and Instructors with appropriate primary and foreign keys.
Write SQL Query for Data Retrieval
Question 2: Given a table Orders with columns order_id, customer_id, order_date, and total_amount, write an SQL query to retrieve the customer_id and the total number of orders placed by customers who have placed more than 5 orders. Ensure to apply advanced database management techniques.
Expected Response: Write an SQL query using GROUP BY and HAVING clauses to count the number of orders per customer and filter those who have placed more than 5 orders.
Normalization Exercise
Question 3: You are given a table Employee(employee_id, employee_name, department_name, department_location, project_name). The same employee can work on multiple projects, and a department can have multiple employees. Normalize this table to the 3rd Normal Form (3NF). Discussing normalization is critical for an advanced database management system.
Expected Response: Show the process of decomposing the table into smaller tables, eliminating redundancy, and ensuring that no transitive dependencies exist.
Joins and Subqueries
Question 4: Write a query to find all employees who have never worked on a specific project, given the tables Employee(employee_id, employee_name) and Works_On(employee_id, project_name). Use both a subquery and a join to achieve the same result. These methods are essential in an advanced system for database management.
Expected Response: Write two SQL queries: one using a LEFT JOIN or NOT IN subquery to find employees who are not associated with a specific project.
Transaction Management
Question 5: In a banking system, you have two accounts Account_A and Account_B. You need to transfer $500 from Account_A to Account_B. Write an SQL script that ensures atomicity, consistency, isolation, and durability (ACID properties) during the transfer. Implementing ACID is a key aspect of an advanced Database Management System.
Expected Response: Provide SQL commands for starting a transaction, updating balances, handling any potential errors, and committing or rolling back the transaction.
To get the complete answer/solution to this Parul University assignment, you can contact Dr. Aravind Banakar’s Academic Writing Services.
Dr. Aravind Banakar prepares two types of Assignments. General assignments & Customized assignments. Both assignments are 100 % Plagiarism-free, which is crucial even for an advanced database management system project.
Dr. Aravind Banakar is the number 1 Parul University Academic Writing Professional. He is a highly experienced academic professional and a reputable and reliable academic content writer with over 24 years of experience. You can obtain ready-made, customized, plagiarism-free MBA, BBA, MCA, BCA, MSc, MSW, M.Com and B.Com assignments.
Important Notice for Parul University Assignments:
To ensure your Parul assignments meet the university’s standards, The Parul Assignments must be 100% customized, plagiarism-free, and unique. This is pivotal when dealing with advanced database systems, as integrity is key.
Copying from Google, AI tools, blogs, books, or any other sources is strictly prohibited, and if you copy answers, you will get ZERO marks.
Before purchasing assignments from any academic writer, always demand the following verification:
- Turnitin Report – To ensure the content is plagiarism-free.
- Grammarly Report – This is to ensure grammar and writing quality.
- AI Detection Report – To guarantee no AI-generated content.
Remember, these reports are not just a formality. They are your shield against ZERO marks. Take charge of your grades by ensuring your work is original and meets Parul University standards. This is particularly important in advanced courses like database management systems.
Dr. Banakar leads a team of over 100 PhD-qualified professionals dedicated to creating high-quality, plagiarism-free assignments tailored to meet the exact standards and requirements of Parul University. With a proven track record, students who have used his services often score 25+ marks out of 30, highlighting the level of detail, precision, and thorough research into every assignment. This emphasis on research is crucial for assignments in advanced subjects such as database management systems, instilling confidence in the quality of the work.