Govt. of Karnataka, Department of Technical Education
Diploma in Computer Science & Engineering
Fourth Semester
Subject : DBMS lab
Contact Hrs / week : 6 Total hrs: 96
Note: Draw E-R diagrams & Relational schema.
- Create the following tables for a COMPANY database
EMPLOYEE
| Fname | Minit | Lname | Ssn | Bdate | Address | Sex | Salary | Super_ssn | Dno |
DEPARTMENT
| Dname | Dnumber | Mgr_ssn | Mgr_start_date |
DEPT_LOCATIONS
| Dnumber | Dlocation |
PROJECT
| Pname | Pnumber | Plocation | Dnum |
WORKS_ON
| Essn | Pno | Hours |
DEPENDENT
| Essn | Dependent_name | Sex | Bdate | Relationship |
For table structure refer page no: 247 and for sample values refer page no. 159 of the reference text 1.
| 2 | Illustrate the use of constraints |
| | NOT NULL |
| | PRIMARY KEY |
| | UNIQUE |
| | CHECK |
| | DEFAULT |
| | REFERENCES |
| 3 | DATA MANIPULATION : INSERTING VALUES INTO A TABLE |
| 4 | Illustrate the use of SELECT statement |
| 5 | Conditional retrieval - WHERE clause |
| 6 | Query sorted - ORDER BY clause |
| 7 | Grouping the result of query - GROUP BY clause and HAVING clause |
| 8 | Aggregate functions in SQL (Count, Sum, Max, Min, Avg) |
| 9 | SQL operators |
| 10 | Query multiple tables using JOIN operation |
| 11 | Write nested and complex queries using multiple tables |
| 12 | Use different single row and multi row (group) functions |
| 13 | Perform UPDATE, ALTER, DELETE, DROP operations on tables |
| 14 | Illustrate the use of CREATE VIEW command and manipulating |
| 15 | Database Security And Privileges : Use GRANT and REVOKE commands |
| 16 | Use COMMIT and ROLL BACK commands |
| 17 | Use SAVEPOINT commands |
| 18 | Using any front end tool develop an application involving minimum 3-4 tables. (Not for exam) |
Scheme of examination
| 1 | Record | 05 |
| 2 | Create tables (min 2 tables ) | 10 |
| 3 | Inserting records | 05 |
| 4 | Writing 5 queries on minimum two tables | 30 |
| 5 | Execution with results for 5 queries | 30 |
| 6 | Viva – Voce | 20 |
| | Total marks | 100 |
References:
- Fundamentals of Database Systems, 5th edition, Ramez Elmasri, Shamkan B. Navathe, Pearson EducationSql Pl/Sql The Programming Language Of Oracle W/CD, Ivan Bayross, Bpb Publications, 2009.
- http://www.sql-tutorial.net/
- Mysql 5 For Professinals W/Cd, Ivan Bayross / Sharanam Shah, Shroff Publishers & Distributors
- Database Management System Oracle SQl and PL/SQL, Pranab Kumar das Gupta, PHI, 2009 Print
Softwares:
Database : SQl/MySQL/Oracle
Front end tool for database querying: Sqldeveloper or any other query tool
