Quick Revision

1. DBMS Basics

a. Definition

• DBMS = collection of interrelated data + set of programs to access data

b. Common applications

• Banking and finance
• Universities
• Airlines
• Telecommunication
• Online retailers

2. Why DBMS over File System

a. File-system problems

• Data redundancy and inconsistency
• Difficulty in accessing data
• Data isolation
• Integrity problems
• Atomicity problems
• Concurrent-access problems
• Security problems

b. DBMS benefits

• Centralized control
• Better consistency
• Easier querying
• Better access control

3. Relational Model

a. Main idea

• Data stored in tables
• Rows = tuples
• Columns = attributes

b. Attribute facts

• Domain = set of allowed values
• Attribute values usually atomic
• NULL means unknown

4. Schema and Instance

a. Schema

• Logical structure of DB
• Example: instructor(ID, name, dept_name, salary)

b. Instance

• Snapshot of actual data at one time

5. Keys

a. Super key

• Attribute/set of attributes that uniquely identifies tuple

b. Candidate key

• Minimal super key

c. Primary key

• Chosen candidate key
• Unique + not null

d. Alternate key

• Candidate keys not chosen as primary key

e. Foreign key

• Attribute in child table referring to primary key of parent table

6. Foreign Key Idea

a. Parent-child example

• Persons(PersonID, ...)
• Orders(OrderID, OrderNumber, PersonID)

b. Link

• Orders.PersonID -> Persons.PersonID

c. Benefit

• Prevents invalid references

7. University Schema Links

a. Main relations

• department
• course
• instructor
• student
• section
• takes
• teaches
• advisor
• classroom
• time_slot
• prereq

b. Important links

• course.dept_name -> department.dept_name
• instructor.dept_name -> department.dept_name
• student.dept_name -> department.dept_name
• section.course_id -> course.course_id
• takes.ID -> student.ID
• teaches.ID -> instructor.ID
• advisor.s_ID -> student.ID
• advisor.i_ID -> instructor.ID

8. Relational Algebra

a. Six basic operators

• Select σ
• Project Π
• Union ∪
• Set difference −
• Cartesian product ×
• Rename ρ

b. Examples

• Physics instructors: σ dept_name="Physics" (instructor)
• Physics instructors with high salary: σ dept_name="Physics" ∧ salary > 90000 (instructor)
• Rename expression: ρ x (E)

9. SQL Categories

a. DDL

• CREATE
• ALTER
• DROP

b. DML

• INSERT
• UPDATE
• DELETE

c. DQL

• SELECT

d. DCL

• GRANT
• REVOKE

e. TCL

• COMMIT
• ROLLBACK
• SAVEPOINT

10. Oracle Lab Essentials

a. Tools

• Oracle SQL*Plus = command-line tool
• Oracle SQL Developer = GUI tool
• Data Dictionary = metadata about users, tables, privileges

b. User commands

• CREATE USER
• GRANT CREATE SESSION
• GRANT CREATE TABLE, CREATE VIEW
• GRANT CONNECT
• GRANT RESOURCE
• REVOKE

11. Lab Table Structures

a. students

• student_id
• first_name
• last_name
• email
• date_of_birth

b. EMPLOYEES

• EMP_ID
• NAME
• DEPARTMENT
• SALARY
• JOIN_DATE
• GENDER
• EMAIL
• later PHONE

c. Constraints

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

12. Query Practice Reminders

a. Common clauses

• DISTINCT
• ORDER BY
• WHERE
• LIKE
• IN
• BETWEEN
• IS NULL

b. Examples

• distinct departments
• sort by salary
• filter DEPARTMENT = 'IT'
• NAME LIKE 'A%'
• SALARY BETWEEN ...
• EMAIL IS NULL

13. Transaction Concepts

a. Atomicity

• Transaction fully happens or not at all
• Example: fund transfer

b. COMMIT

• Save changes permanently

c. ROLLBACK

• Undo uncommitted changes

14. Security and Concurrency

a. Security

• File system hard to give partial access
• DBMS uses privileges and roles

b. Concurrency

• Many users may access same data
• Uncontrolled updates can create inconsistency

---

IUS Preps - Your Academic Success Partner