Questions

1. Vehicle Rental Database Design

Vehicle rental company wants centralized DB. Customer rents vehicle through rental record. Each rental includes payment. Every vehicle must have insurance and belong to one branch.

a. Generate schema diagram of this Vehicle Rental Management System using entities Customer, Vehicle, Rental, Payment, Insurance, Branch.

b. Identify primary key and foreign key placements for this design.

c. Why is DBMS better than file system for this company in terms of redundancy, isolation, and consistency?

2. University Database Design

University wants one DB for departments, courses, instructors, students, sections, classrooms, time slots, advisors, and prerequisites.

a. Draw schema diagram showing important links among these relations.

b. Explain database schema and database instance using instructor(ID, name, dept_name, salary).

c. Which attribute should be primary key in instructor relation? Why?

3. Parent-Child Relations and Keys

Online ordering system stores customer data in one table and order data in another. Design must prevent invalid order references. Teacher also gives one student relation for key analysis.

a. Draw schema diagram for Persons and Orders. Mark parent table, child table, PK, and FK.

b. For Student(RollNo, Name, Email, Phone, DeptID), find super key, candidate key, primary key, alternate key, unique key, composite key, and foreign key. Assume RollNo, Email, and Phone are unique; DeptID repeats.

c. Explain atomicity of updates with fund-transfer example and concurrent access problem with balance-withdraw example.

4. SQL Categories and Employee Table

Office stores employee data in one table. DBA must know which commands create structure, change data, control access, and manage transactions.

a. Write structure of EMPLOYEES(EMP_ID, NAME, DEPARTMENT, SALARY, JOIN_DATE, GENDER, EMAIL) with constraints.

b. Classify CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, GRANT, REVOKE, COMMIT, ROLLBACK, SAVEPOINT under correct SQL groups.

c. Define DBMS and give two application examples.

5. Student-Course Lab Practice

In Oracle lab, store student data in one table and course data in another. Then write basic SQL for create, insert, display, update, and delete.

a. Write schema for students table and proposed courses(course_id, course_name, credit_hours) table with suitable primary keys.

b. Write SQL commands to create courses, insert at least 3 rows, display all rows, update credit_hours for one row, and delete one course row.

c. Explain difficulty in accessing data and integrity problems in file systems.

6. Department Schema and Relational Algebra

College office stores department and instructor data in relational form.

a. Draw schema diagram for department(dept_name, building, budget) and instructor(ID, name, dept_name, salary). Show connection.

b. Write relational algebra expressions for:

instructors from Physics department
instructors from Physics department having salary greater than 90000
departments where dept_name = building

c. What is relational model?

7. Section Scheduling and Relational Algebra

Registrar office stores section schedule using section, classroom, and time_slot.

a. Draw schema diagram using section(course_id, sec_id, semester, year, building, room_number, time_slot_id), classroom(building, room_number, capacity), and time_slot(time_slot_id, day, start_time, end_time).

b. Write relational algebra expressions for:

all course IDs taught in Fall 2017 or Spring 2018 or both
all course IDs taught in Fall 2017 but not in Spring 2018
rename result of an expression as x

c. Differentiate schema and instance.

8. Oracle User Management

Lab administrator sets up Oracle users and privileges for students. Write commands and explain core Oracle tools.

a. Prepare simple schema-oriented layout showing User, Privilege, and ObjectPrivilege.

b. Write Oracle commands for creating user student, granting CREATE SESSION, granting CREATE TABLE, CREATE VIEW, granting CONNECT, granting RESOURCE, revoking CREATE TABLE, granting SELECT, INSERT on employee, and dropping unused user.

c. What is Oracle SQL*Plus? What is Data Dictionary?

9. Employee Query Practice

Company adds PHONE column to EMPLOYEES table and asks for common reporting queries.

a. Draw schema diagram for EMPLOYEES table after adding PHONE column.

b. Write SQL queries to:

show distinct departments
sort employees by salary
retrieve employees from IT department
retrieve employees whose salary is between two values
retrieve employees whose name starts with A
retrieve employees whose department is in ('HR', 'IT')
retrieve employees whose email is NULL

c. Explain role of COMMIT and ROLLBACK.

10. Registration System and Security

University wants clear registration design for students, courses, sections, advisors, instructors, and prerequisites. Then review one employee-table key question and one security theory point.

a. Generate schema diagram for student-course registration system using student, course, section, takes, advisor, instructor, prereq.

b. For EMPLOYEES(EMP_ID, NAME, DEPARTMENT, SALARY, JOIN_DATE, GENDER, EMAIL), mention possible candidate key, primary key, alternate key, and nullable attribute based on given constraints.

c. Explain security problems in file system and how DBMS helps solve them.

IUS Preps - Your Academic Success Partner

On this page