Answers

1. Vehicle Rental Database Design

a. Vehicle Rental schema diagram

b. Primary key and foreign key placements

c. Why DBMS better than file system

• reduces data redundancy
• reduces data isolation
• improves consistency
• easier shared access through common schema and query system

Example: same rental data in many files -> mismatch risk. DBMS stores linked data in related tables.

2. University Database Design

a. University schema diagram

b. Schema, instance, primary key

• Schema: logical structure of DB
• Example: instructor(ID, name, dept_name, salary)
• Instance: snapshot of actual data at one time
• Primary key: ID
• Reason: unique, stable, not null

c. Primary key in instructor

• ID
• unique identifier
• stable
• primary key must be unique and not null

3. Parent-Child Relations and Keys

a. Persons-Orders schema diagram

• Persons.PersonID = primary key
• Orders.OrderID = primary key
• Orders.PersonID = foreign key referencing Persons.PersonID

b. Keys for Student(RollNo, Name, Email, Phone, DeptID)

Super keys:

• {RollNo}
• {Email}
• {Phone}
• {RollNo, Name}
• {RollNo, DeptID}
• {Email, Name}
• {Phone, Name}

Candidate keys:

• {RollNo}

• {Email}

• {Phone}

• Primary key: {RollNo}

• Alternate keys: {Email}, {Phone}

• Unique keys: Email, Phone

• Composite key examples: {RollNo, Name}, {Email, Name}

• Foreign key: DeptID if it references department table

c. Atomicity and concurrency

• Atomicity: transaction fully happens or not at all; example: fund transfer
• Concurrent access problem: many users update same data; example: two users read 100, both withdraw 50, result wrong

4. SQL Categories and Employee Table

a. EMPLOYEES structure

EMPLOYEES(EMP_ID, NAME, DEPARTMENT, SALARY, JOIN_DATE, GENDER, EMAIL)

• EMP_ID primary key
• NAME not null
• EMAIL may be null

b. SQL groups

DDL:

• CREATE
• ALTER
• DROP

DML:

• INSERT
• UPDATE
• DELETE

DQL:

• SELECT

DCL:

• GRANT
• REVOKE

TCL:

• COMMIT
• ROLLBACK
• SAVEPOINT

c. DBMS and application examples

• DBMS: collection of interrelated data and set of programs to access data
• Examples: banking and finance, universities

5. Student-Course Lab Practice

a. students and courses schema

• students.student_id = primary key
• courses.course_id = primary key

b. SQL commands

CREATE TABLE courses (
  course_id NUMBER(5) PRIMARY KEY,
  course_name VARCHAR2(50),
  credit_hours NUMBER(2)
);

INSERT INTO courses VALUES (101, 'DBMS', 3);
INSERT INTO courses VALUES (102, 'OOP', 3);
INSERT INTO courses VALUES (103, 'Math', 3);

SELECT * FROM courses;

UPDATE courses
SET credit_hours = 4
WHERE course_id = 101;

DELETE FROM courses
WHERE course_id = 103;

c. Difficulty in access and integrity problems

• Difficulty in access: new task needs new program
• Integrity problem: constraints stay buried in code; hard to add/change
• Example: account balance > 0

6. Department Schema and Relational Algebra

a. Department-Instructor schema

• instructor.dept_name references department.dept_name

b. Relational algebra

σ dept_name="Physics" (instructor)

σ dept_name="Physics" ∧ salary > 90000 (instructor)

σ dept_name=building (department)

c. Relational model

• all data stored in tables
• tables contain rows and columns

7. Section Scheduling and Relational Algebra

a. Section-Classroom-TimeSlot schema

• section(building, room_number) references classroom(building, room_number)
• section.time_slot_id references time_slot.time_slot_id

b. Relational algebra

Π course_id (σ semester="Fall" ∧ year=2017 (section)) ∪ Π course_id (σ semester="Spring" ∧ year=2018 (section))

Π course_id (σ semester="Fall" ∧ year=2017 (section)) − Π course_id (σ semester="Spring" ∧ year=2018 (section))

ρ x (E)

c. Schema vs instance

• Schema: logical structure of DB
• Instance: actual data snapshot at given time

8. Oracle User Management

a. Oracle user administration layout

• system privilege example: CREATE SESSION
• role examples: CONNECT, RESOURCE, DBA
• object privilege examples: SELECT, INSERT

b. Oracle commands

CREATE USER student IDENTIFIED BY password123;

GRANT CREATE SESSION TO student;

GRANT CREATE TABLE, CREATE VIEW TO student;

GRANT CONNECT TO student;

GRANT RESOURCE TO student;

REVOKE CREATE TABLE FROM student;

GRANT SELECT, INSERT ON employee TO student;

DROP USER student;

c. SQL*Plus and Data Dictionary

• Oracle SQL*Plus: command-line tool for Oracle
• Data Dictionary: metadata about users, tables, privileges

9. Employee Query Practice

a. Employee schema after adding PHONE

• EMP_ID = primary key
• NAME = not null

b. SQL queries

SELECT DISTINCT DEPARTMENT
FROM EMPLOYEES;

SELECT *
FROM EMPLOYEES
ORDER BY SALARY;

SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT = 'IT';

SELECT *
FROM EMPLOYEES
WHERE SALARY BETWEEN 50000 AND 80000;

SELECT *
FROM EMPLOYEES
WHERE NAME LIKE 'A%';

SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT IN ('HR', 'IT');

SELECT *
FROM EMPLOYEES
WHERE EMAIL IS NULL;

c. COMMIT and ROLLBACK

• COMMIT: saves changes permanently
• ROLLBACK: cancels uncommitted changes

10. Registration System and Security

a. Student-course registration schema

b. Keys for EMPLOYEES

• Candidate key: {EMP_ID}
• Primary key: {EMP_ID}
• Alternate key: none guaranteed by lab constraints
• Nullable attribute: EMAIL

c. Security problem in file system and DBMS solution

Because file systems caused:

• data redundancy and inconsistency
• security problems

DBMS solution:

• uses controlled access and privileges
• Lab 1 shows GRANT, REVOKE, object privileges, roles like CONNECT, RESOURCE, DBA

IUS Preps - Your Academic Success Partner