Lovely Professional University 2011 B.Tech Computer Science and Engineering Design problem- database management system solution - Question Paper

Design problem: 1

Database And Management System

Submitted to: Submitted By:

Miss Jaspreet Kaur Shikha Kumari

Roll No. 20

SECTION: A1811

Problem:

A database is to be designed for a University to monitor students' progress throughout their course of study. The students are reading for a degree (such as B.Tech, B.Tech (Hons) MCA, etc) within the framework of the modular system. The University provides a number of courses, each being characterized by its code, title, credit value, course coordinator, teaching staff and the department they come from. A course is co-ordinated by a course coordinator who shares teaching duties with one or more lecturers. A lecturer may teach more than one course. Students are free to choose any course they wish but the following rules must be observed: some courses require pre-requisites courses and some degree programs have compulsory courses. The database is also to contain some information about students including their numbers, names, addresses, degrees they read for, and their past performance (i.e. courses taken and examination results).

Represent the complete scenario with the help of ER Diagram and design logical schema for this database. Also specify desirable constraints and justify your design approach even in a single small step.

Relation of entities

1.      University offers degrees: one to many

2.      Degrees has departments: many to many relation

3.      Lecturer belongs to department: many to one relation

4.      Department offers courses: one to many relation

5.      Lecturer teach courses: many to many relation

6.      Instructor plan courses: one to one relation

7.      Instructor shares duties with lecturer: one to many relation

8.      Student studies courses: many to many relation

9.      Student acquire grades: many to one relation

10.  Some courses has prerequisite: one to one relation

Identification of Entities and Relations in ER Diagram

university(university_id, university_name,degree)

courses (course_code, course_name, credit, hours, dept_id)

department ( dept_id, dept_name, HOD)

lecturer (faculty_id, f_name, l_name, salary, phone_no, email, dept_id, inst_id)

instructor(inst_id,f_name,l_name,salary,phone_no,email, course_code)

students (reg_no, f_name, l_name, address, CGPA)

grades ( registration_no, letter_grade, course_code)

studies (course_code, reg_no)

teach (faculty_id, course_code)

prerequisites (course_code, prerequisites)

ER Diagram

(please see the attachment with this document for clear view of diagram)

Entity Relationship Key

courses

department

lecturer

instructor

students

studies

teach

prerequisites

Logical Schema of the ER diagram

Logical schema concentrates on describing entities, data types, relationships and constraints.

university

courses

department

lecturer

instructor

student

studies

teach

has

Entities with desirable constraints

university( university_id <primary key>,

university_name <NOT NULL constraint>)

courses (course_code <primary key> ,

course_name <NOT NULL constraint>,

credit <check greater than or equal to 1>,

hours <check less than 60 hours> ,

dept_id <foreign key referred to department_id of department>)

department ( dept_id <primary key>,

dept_name <NOT NULL constraint>,

HOD)

lecturer (faculty_id <primary key>,

f_name <NOT NULL constraint>,

l_name,

salary,

phone_no,

email,

dept_id <foreign key referred to department_id of department>,

inst_id <foreign_key referred to inst_id of instructor>)

instructor(inst_id <primary key>,

f_name <NOT NULL constraint>,

l_name,

salary,

phone_no,

email<NOT NULL constraint>,

course_code id <foreign key referred to course_code of courses>)

students (reg_no<primary key>,

f_name<NOT NULL constraint>,

l_name,

address<NOT NULL constraint>,

CGPA)

grades ( reg_no<foreign key referred to reg_no of student>,

letter_grade,

course_code <foreign key referred to course_code of courses>)

Relations with desirable constraints

studies (course_code <foreign key referred to course_code of courses>,

reg_no <foreign key referred to reg_no of student>)

teach (faculty_id <foreign_key referred to inst_id of instructor>,

course_code <foreign key referred to course_code of courses>)

has (course_code <foreign key referred to course_code of courses>,

prerequisites)

Creating tables

1.     CREATE TABLE university( university_id VARCHAR(20) primary key,

University_name VARCHAR(50) NOT NULL);

2.     CREATE TABLE courses ( course_code VARCHAR(20) Primary Key,

course_name VARCHAR(20) NOT NULL,

credit NUMBER(10) NOT NULL,

hours NUMBER(10) NOT NULL,

dept_id NUMBER(10) REFERENCES department(dept_id));

3.     CREATE TABLE department( dept_id NUMBER(10) primary key,

dept_name VARCHAR(20) NOT NULL,

HOD VARCHAR(10));

4.     CREATE TABLE instructor( inst_id NUMBER(10) primary key,

f_name VARCHAR(20) NOT NULL,

l_name VARCHAR(20),

salary NUMBER(10),

phone_no NUMBER(10),

email VARCHAR(20) NOT NULL,

course_code VARCHAR(20) REFERENCES courses(course_code));

5.     CREATE TABLE lecturer (faculty_id NUMBER(10) primary key,

f_name VARCHAR(20) NOT NULL,

l_name VARCHAR(20),

salary NUMBER(10),

phone_no NUMBER(10),

email VARCHAR(30),

dept_id NUMBER(10) REFERENCES department(dept_id),

inst_id NUMBER(10) REFERENCES instructor(inst_id));

6.     CREATE TABLE students ( reg_no NUMBER(10) primary key,

f_name VARCHAR(20) NOT NULL,

l_name VARCHAR(20),

address VARCHAR(20) NOT NULL,

CGPA NUMBER(10));

7.     CREATE TABLE grades (registration_no NUMBER(10),

letter_grade VARCHAR(2),

course_code VARCHAR(20));

8.     CREATE TABLE studies (course_code VARCHAR(20) REFERENCES courses(course_code),

reg_no NUMBER(10) REFERENCES students(reg_no));

9.     CREATE TABLE teach (faculty_id NUMBER(10) REFERENCES lecturer(faculty_id),

course_code VARCHAR(20) REFERENCES courses(course_code));

10.                        CREATE TABLE has ( course_code VARCHAR(20) REFERENCES courses(course_code),

prerequisites VARCHAR(50) NOT NULL);

Overview of tables created

university

courses

department

lecturer

instructor

students

teach

studies

has