AIMS
• To be able to acquire the knowledge and skill in the database system concept.
• To be able to familiarize with data models in database systems.
• To be able to acquire the knowledge and skill in the Relational databases
• To be able to acquire the knowledge and skill in the Integrity & security.
• To be able to acquire the knowledge and skill in the Data storage, Transactions & concurrency control and Database system architecture.
SHORT DESCRIPTION
Database system concept; Data models; Relational databases, Integrity & security, Data storage, Transactions & concurrency control and Database system architecture.
DETAIL DESCRIPTION
Theory:
1. Understand the basic concept of database system.
1.1 Define database management system.
1.2 Explain the purpose of database management system.
1.3 Mention the different between conventional file system and database management system.
1.4 Mention the advantages & disadvantages of database management system.
1.5 Define data abstraction, instances and schemas.
1.6 Mention the types of schema.
2. Understand the concepts of database languages, users, manager and administrator.
2.1 Describe the database languages with examples.
2.2 Describe the basic operation of DDL, DML and data dictionary.
2.3 Describe the different types of database system users.
2.4 Example the different tasks of database manager.
2.5 Describe the functions of a database administrator.
2.6 Describe the functional components of a database system.
3. Understand the data models.
3.1 Define the entity and entity set.
3.2 Define the data model.
3.3 Mention the meaning of E-R diagram symbol.
3.4 Describe the E-R diagram for different mapping constrains.
3.5 State different types of attribute uses in E-R diagram.
3.6 State the techniques to convert E-R diagram to table.
3.7 Describe the different types of data models with examples.
3.8 Describe the constraints in entity-relationship (mapping, cardinalities and existences) with diagrams.
3.9 Define key in DBMS.
3.10 State the meaning of different types of keys in DBMS (super key, candidate key, primary key and foreign key).
3.11 Distinguish between strong and weak entity sets.
3.12 Describe the schema diagram with example.
4. Understand the relational database.
Query language.
4.1 Define query language.
4.2 Mention the different among SQL, QBE and Datalog.
4.3 Describe the fundamental operations of relational algebra(select, project, union, set difference, cartesian product, rename, set intersection, natural joint, division and assignment).
5. Understand the SQL.
5.1 Mention the several parts of SQL.
5.2 Explain three clauses of SQL expression (select, from, and where).
5.3 Describe the uses of SQL set operations (union, intersect, and except).
5.4 Describe the uses of SQL aggregate functions (avg, min, max, sum and count).
5.5 Describe the technique to add, remove and change information with SQL (delete, insert, and update).
6. Understand the integrity and security.
6.1 Define integrity constraint.
6.2 Describe the referential integrity in SQL.
6.3 Describe the assertions in DBMS.
6.4 Define the triggers and need for triggers in DBMS.
6.5 Define the security in DBMS.
6.6 Describe the protection of database.
6.7 Define encryption and authentication in database.
6.8 Mention the technique of encryption.
7. Understand the relational database design.
7.1 Define the normalization.
7.2 Mention the need for normalization.
7.3 Describe the term redundancy in DBMS.
7.4 Explain the three stages/rules of normalization in database management system (1NF, 2NF, and 3NF)
7.5 Describe the overall database design process.
8. Understand the data-storage media.
8.1 List the physical storage media.
8.2 Describe the storage-device hierarchy used for database storage.
8.3 Define the RAID.
8.4 Describe the different levels of RAID.
8.5 Describe the choice of RAID levels.
9. Understand the Transactions and concurrency controls.
9.1 Define transaction and concurrent execution in DBMS.
9.2 Mention the properties of the transaction.
9.3 Explain the transaction state with diagram.
9.4 Mention the reasons for allowing concurrency.
10. Understand the database system architecture.
10.1 Define centralized, parallel and distributed database system.
10.2 Explain the homogeneous and heterogeneous databases.
10.3 Explain the structure of server (Centralized and client server), parallel and distributed database system architecture.
10.4 Describe the advantages and disadvantages of server, parallel and distributed database system architecture.
PRACTICAL:
1. Arrange the necessary hardware and operating system for installing MS-Access, SQL server or Oracle.
2. Create a new database for the result process application using MS-Access, SQL server or Oracle.
3. Create tables such as Student Information, Department Information, Subject Information, Year information and Mark Information (including):
I. Create a new table
II. Define primary key
III. Save the table structure
IV. Edit a table structure
V. Insert a field
VI. Delete a field
VII. Move a field
VIII. Change or remove a key field
4. Create relationship among tables.
5. Sort the Mark Information table lookup fields.
6. Create data entry form for entering data in Student Information, Department Information, Subject Information, Year Information and Mark Information tables
7. Create a query involving only one table.
8. Query linked tables and create a form from a query.
9. Create a total query to find the GPA of each student of a particular year.
10. Apply Normalization (1NF, 2NF and 3NF) on result process database.
11. Use Auto Report to create a table reports of result process.
12. Use the report wizard to create a grade sheet /mark sheet/transcript.
13. Use the report wizard to create a merit list.
14. Use the report wizard to create a tabulation sheet.
15. Perform the task to install Oracle Database Language and Invoking SQL Plus.
16. Perform the task to manipulate data in data base management system (select, project, union, set difference, cartesian product, rename, set intersection, natural joint, division and assignment).
17. Perform the task to view, delete and update data into a table (delete, insert, and update).
18. Perform the task to modify the structure of a table.
19. Perform the task to Compute on Table Data including ‘DUAL’ and SYSDATE
20. Perform the task to work with Oracle functions and different Type of constraints.
21. Perform the task to work for grouping data from tables and manipulate dates in SQL
22. Perform the task to work with Sub queries and JOINS in SQL
23. Perform the task to work with Indexes in SQL
24. Perform the task to work with View, sequences and Security in SQL including user and administrative level.
25. Perform the task to work with oracle transaction, process and Parameterized cursor in PL/SQL
26. Perform the task to work with Concurrency Control (Implicit and explicit
lock) and error handling in PL/SQL
27. Perform the task to work with Database object, Oracle package and database trigger in PL/SQL
28. Given basic salary, increment and date of joining of 25 employees. Create a database to perform an annual budget from July to June.
REFERENCE BOOK
1. Database System Concepts Henry F. Korth.
2. Successful projects in ACCESS – P.M Heathcote
3. SQL, PL/SQL
The programming language of ORACLE- By-IVAN BAYROSS
4. Introduction To Oracle 10g SQL Volume-1
5. Introduction To Oracle 10g SQL Volume-2
6. Introduction To Oracle 10g PL/SQL Volume-1
7. Introduction To Oracle 10g PL/SQL Volume-2
References Web Site:-
www.java2s.com/Tutorial/Oracle/CatalogOracle.htm
www. docs.oracle.com
Powered BY: BTEB
