Hafta 1
Introduction to Database Management Systems. Advantages of DBMS SQL as Data Definition Language: Create tables/ define constraints Keys / domain constraints
Hafta 2
Entity Integrity Referential Integrity Managing Schema Objects (Tables) Create / Add / Alter / Drop /Rename/Modify/Set Unused Column/ Deferring Constraints/ Disabling Constraints/ Flush Back Tables SQL/DML: Insert LAB 1: 1. ORACLE Academy-Student License Agreement 2. Getting familiar with ORACLE. 3. Using SQLDeveloper 4. Creating tables by using Schema Manager and SQL Commands 5. Creating BankingDB
Hafta 3
SQL as Data Manipulation Language: Retrieving data using SELECT statement Column Aliasing/Quote Operator DISTINCT/LIKE/IN/ORDER BY LAB 2: 1. Managing Schema objects PARTII-SQL-II Practice 3-1 2. Creating CompanyDB and Foreign Keys (Referential Integrity)
Hafta 4
SQL/DML General Functions :NVL-NVL2 Conditional Expressions: CASE-DECODE JOIN types – INNER/OUTER JOIN LAB 3: 1. Practice 2-1: Retrieving Data Using the SQL SELECT Statement 2. Using DDL statements to Create and Manage Tables 3. Practice 3-1: Restricting and Sorting Data 4. Practice 5-1: Using Conversion Functions and Conditional Expressions 5. Self Study: Practice 7-1: Displaying Data from Multiple Tables Using Joins
Hafta 5
Set operations, subqueries ANY/ALL operations with multiple-row subqueries ANY/ALL operations with multiple-row subqueries LAB 4: 1. SELF STUDY: Practice 8-1: Using Subqueries to Solve Queries (Ex: 3, 4, 5, 6, 7)
Hafta 6
Grouping and aggregate functions (SQL ) LAB QUIZ
Hafta 7
Correlated subqueries Pairwise/Non-pairwise comparison subqueries Transaction Properties Transaction Management LAB 5: 1. Single row functions 2. Reporting Aggregated Data Using the Group Functions (Practice 6-1) 3. Using Sub-queries to Solve Queries(Practice 8-1) 4. Retrieving Data by Using Subqueries (Practice 7-1)
Hafta 8
Transactions start-end /Commit/Rollback/Save Point Review for process management and Physical read/write in O/S SQL/DML: insert/delete/update Read consistency (select for update) LAB 6: 1. Row Locking, Select for update 2. Manipulating Data (Add, Delete, Update) 3. Controlling Transactions (Commit, Savepoint, Rollback) 4. Practice 4-1: Using Single-Row Functions to Customize Output 5. Practice 5-1: Using Conversion Functions and Conditional Expressions
Hafta 9
OTHER DATABASE OBJECTS . Views . Sequences LAB7:PL/SQL 1. PL/SQL Programming Language : Variables, DB values, Data Conversin Functions, iterations, Conditions, Strings, Worksheet, Cursors, Explicit cursors, Exceptions MIDTERM 1
Hafta 10
Logical Design: Requirement definition and analysis Introduction to ER Diagramming LAB8: PL/SQL: 1. PL/SQL:Cursor examples, Procedure, Function 2. Case Statement, Extract, Accept Creating Procedure, Creating Functions Creating named blocks for a stored procedure and functions Implicit/explicit cursor examples, Handle exceptions
Hafta 11
ER-Diagramming Basics- Logical relational database design LAB 9: PL/SQL: 1.Creating other Schema Objects (view, sequence) 2. PL/SQLExercises Practicing on cursors, procedure, function(Market DB) Review Guide for PL/SQL (FormulaDB) Handling Exceptions
Hafta 12
Samples for ER Diagrams LAB 10: 1. Oracle Forms: Creating A Data Block Form 2. Creating a List Of Values (LOV), Check Box, List Item, Radio Button 3. Representing Data values Using Other Item Types (List Item, Radio Button) 4. Master - Detail Relationships. Form Triggers: Form Triggers with Master - Detail Relationships 5. Oracle Reports
Hafta 13
Normalization (1-5 Normal Forms) Normalization Examples LAB 11: 1. Oracle Forms & Reports Example (Online Supermarket) 2. Oracle Forms & Reports Example (Student DB) TAKE HOME: Study for LabExam-Hospital DB Example MIDTERM 2
Hafta 14
Controlling user Access in SQL/DCL LAB 12: 1. Controlling User Access 2. Managing Objects with Data Dictionary Views. LAB EXAM