DBMS lab Diploma in Computer Science

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.

 

  1. 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:

  1. 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.
  2. http://www.sql-tutorial.net/
  3. Mysql 5 For Professinals W/Cd, Ivan Bayross / Sharanam Shah, Shroff Publishers & Distributors
  4. 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

www.oracle.com

www.mysql.com

Related Posts Plugin for WordPress, Blogger...