Database (SQL)

PROJECT CONTENTS

I) PROJECT NAME/TITLE

II) PROJECT OBJECTIVE/PROJECT SCOPE

III) ENTITY NAMES (AT LEAST 20 ENTITIES AND RELATIONS)

ORACLE BARKER NOTATIONS -Each EMPLOYEE …….

IV) ENTITY RELATIONSHIP (ER) DIAGRAM BY USING ORACLE BARKER NOTATIONS

USE THE SQL DATA MODELER TOOL BY ORACLE

V) ENTITY RELATIONSHIP (ER) DIAGRAM BY USING ERWIN IDEF1X NOTATIONS

USE THE ERWIN TOOL BY CA

VI) SELECTION OF DBMS AND WHY YOU CHOSE IT

VII) SQL DATA DEFINITION LANGUAGE- DDL STATEMENTS FOR ALL DATABASE (20 TABLES AND RELATIONS)

IMPORTANT: YOO SHOULD HAVE DATABASE DESIGN DOCUMENT FOR ALL 20 TABLES AND RELATIONS.

YOU CAN (ARE SUGGESTED TO) USE ERWIN REPORTS.

VIII) SELECTION OF THREE TABLES AND RELATIONS TO SHOW SQL EXPERIENCE

A) ADDITIONAL DDL STATEMENTS -DATA DEFINITION LANGUAGE

A.1. CREATE/ALTER VIEW EXAMPLES

A.2 CREATE/ALTER INDEX EXAMPLES

B) DML STATEMENTS -DATA MANIPULATION LANGUAGE

B.1. INSERT INTO STATEMENTS (OPEN ENDED)

B.2. UPDATE STATEMENTS (OPEN ENDED)

B.3. DELETE STATEMENTS (OPEN ENDED)

B.4. YOU SHOULD WRITE AT LEAST ONE STORED PROCEDURE OR FUNCTION (TRIGGER)

P.S. INCLUDING VIEW OPERATIONS – e.g. SHOW HOW TO UPDATE MAIN TABLE THROUGH VIEW

C. DQL STATEMENTS -DATA QUERY LANGUAGE

C.1. SIMPLE JOINS – JOINING TWO TABLES

C.2. LEFT/RIGHT INNER/OUTER JOINS

C.3. NESTED QUERIES

C.4. SORTING SUCH AS ORDER BY STATEMENTS

C.5. GROUP BY AND AGGREGATION FUNCTIONS

C.6. USING SELECT STATEMENTS WITH STORED PROCEDURE/FUNCTIONS

D. DCL STATEMENTS -DATA CONTROL LANGUAGE

D.1. TRANSACTION PROCESSING (ROLLBACK/COMMIT ETC) (ORACLE SAVEPOINT)

D.2. SECURITY FUNCTIONS (GRANT/REVOKE)

IX) INTEGRITY TESTS

A.1. YOU SHOULD HAVE 9 TESTS AT LEAST

YPU SHOULD HAVE SCREEN OUTPUTS – EXPLANATION

X) (30 %) MANAGING DATABASE FROM A FRONT-END BY USING JAVA, C/C++, PYTHON, DELPHI, C# etc.

SELECT, INSERT, UPDATE AND DELETE OPERATIONS. (NOTE: USING STORED PROCEDURE/FUNCTION IS A PLUS)

A.1. DESIGN SIMPLE FORM TO PROCESS ONE TABLE

A.2. DESIGN A MASTER/DETAIL TABLE (TWO TABLES AT THE SAME TIME)

A.3. OPEN ENDED. SHOW A PROCESS ON THE FORM TO MANIPULATE THE DATA IN 3 TABLES.

e.g. DEFINE A SIMPLE PROCESS AND MAKE IT THROUGH FORM. FOR/WHILE LOOPS, CONTROL (IF/THEN) STATEMENTS CAN BE MADE.