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