Part 1: Database Design

SQL Query output:  Database –‘University’

create database University  Use University

 Campus  create table Campus (campus_id varchar(50) primary key, campus_name varchar(50), campus_location varchar(50), country varchar(50)) 

insert into Campus values (‘AB101’, ‘Hindustan University’, ‘Washington’, ‘USA’)

insert into Campus values (‘AB102’, ‘Harvard University’, ‘London’, ‘UK’)

insert into Campus values (‘AB103’, ‘Hamilton University’, ‘Tokiyo’, ‘Japan’)

insert into Campus values (‘AB104’, ‘XYZ University’, ‘Melbourn’, ‘Australiya’)

select * from Campus

 

Faculties   create table Faculties (fac_code varchar(50) primary key, fac_name varchar(50), fac_abb_name varchar(50), fac_description varchar(100), fac_head varchar(50), fac_contactno varchar(15)) 

insert into Faculties values (‘F101’, ‘H.JOHN MATHEW’,’JOHN’,’Associate Professor’, ‘KRISH’, ‘7845961230’)

insert into Faculties values (‘F102’, ‘M.KEVIN ALBERT’,’KEVIN’, ‘Associate Professor’, ‘Krish’, ‘9556628741’)

insert into Faculties values (‘F103’, ‘MOHAMMAD ALI’,’MOHAMAD’, ‘Associate Professor’, ‘RAJ’, ‘7598211829’)

insert into Faculties values (‘F104′,’K.ASHA’, ‘AHSA’, ‘Assistant Professor’, ‘RAJ’,’9978456123′) 

select * from Faculties Programmes create table Programmes (program_code varchar(50) primary key, program_name text, total_credits int, program_level text, certification char(50)) 

insert into Programmes values (‘CS101’, ‘Software design and principles’, ‘4’,’Primary level’, ‘A+’)

insert into Programmes values (‘CS102’, ‘Database Design’, ‘3’,’Primary level’, ‘A++’)

insert into Programmes values (‘CS103’, ‘Programming in C++’, ‘4’,’Primary level’, ‘A+’)

insert into Programmes values (‘CS104’, ‘Data Structures’, ‘4’,’Primary level’, ‘A++’) 

select * from ProgrammesTimeperiod  create table Timeperiod(term_id varchar(50) primary key not null, term_name varchar(50), term_year int ) 

insert into Timeperiod values (‘T001′,’first semester’, ‘2018’)

insert into Timeperiod values (‘T002’, ‘trimester’,’2018′)

select * from TimeperiodStudent_enrolments  create table Student_enrolments (enrol_id int primary key not null, program_code varchar(50) references Programmes(program_code), enrol_date date, term_id varchar(50) references Timeperiod(term_id)) 

insert into Student_enrolments values(‘1’, ‘CS101′, ’01/05/2017′,’T001’)

insert into Student_enrolments values(‘2′,’CS101′,’01/01/2018′,’T002’)

select * from Student_enrolments Course_offering

create table Course_offering (student_id varchar(50) primary key not null references Student ( student_id), course_id varchar(50)  not null references  Courses(course_id), term_id varchar(50) references Timeperiod ( term_id), campus_id varchar(50) references Campus (campus_id))

insert into Course_offering values(‘S00001′,’CS’,’T001′,’AB101′)

insert into Course_offering values(‘S00002′,’CS’,’T002′, ‘AB102’) 

select * from Course_offering  Staff  create table Staff (Staff_no varchar(50) primary key, Staff_name varchar(50), Staff_Address varchar(100), Staff_email varchar(50),Staff_contactno varchar(15)) 

insert into Staff values (‘S1’, ‘Meera.S’, ‘XXXYYY’, ‘[email protected]‘, ‘9712364781’)

insert into Staff values (‘S2’, ‘Mayzee.P’, ‘ABCDEF’,’[email protected]‘, ‘7598278778’)

insert into Staff values (‘S3’, ‘Anna.H’,’MNBASD’,’[email protected]‘, ‘74589632156’)

insert into Staff values (‘S4’, ‘SWAH.M’,’TREDSW’,’[email protected]‘, ‘9658741236’)

select * from Staff Facilities create table Facilities (facility_id varchar(50) primary key, facility_building text, facility_roomno int, facility_capacity varchar(70), facility_type text) 

insert into Facilities values (‘F101′,’Newton Block’, ‘101’, ‘500 people can together’, ‘Auditorium’)

insert into Facilities values (‘F102′,’MCA Block’, ‘707’, ‘100 people can together’, ‘Class room’)

insert into Facilities values (‘F103′,’CSE Block’, ‘532’, ‘200 people can together’, ‘Auditorium’)

insert into Facilities values (‘F104’, ‘Mech Block’,’631′,’12 people can use’,’Restroom’)

insert into Facilities values (‘F105′,’CT’,’101′,’500 people can use’, ‘classroom’) 

select * from Facilities Courses  create table Courses (course_id varchar(50) primary key, course_name text, course_credits int, course_description text) 

insert into Courses values (‘CS’, ‘Computer Science’, ‘4’,’About computer progamming technologies’)

insert into Courses values (‘IT’, ‘Information Technology’, ‘3’,’About computer progamming and emerging technologies’)

insert into Courses values (‘ECE’, ‘Electronics Communication’, ‘3’,’About communication network technologies’)

insert into Courses values (‘EEE’, ‘Electrical Engineering’, ‘3’,’About electical technologies’) 

select * from CoursesStudent  create table Student (student_id varchar(50) primary key, student_name varchar(50), student_address varchar(70), student_phone varchar(15)) 

insert into Student values (‘S00001’, ‘M.Nikitha’, ‘Michale’, ‘7894561232’)

insert into Student values (‘S00002’, ‘T.Shahith’, ‘Simho’, ‘7894561232’)

insert into Student values (‘S00003’, ‘K.Karthick’, ‘Dhrona’, ‘7894561232’)

insert into Student values (‘S00004’, ‘V.Nithish’, ‘Mirkali’, ‘7894561232’) 

select * from Student Timetable  create table Timeperiod(term_id varchar(50) primary key not null, term_name varchar(50), term_year int ) 

insert into Timeperiod values (‘T001′,’first semester’, ‘2018’)

insert into Timeperiod values (‘T002’, ‘trimester’,’2018′) 

select * from Timeperiod Q1 – Given a student name X, print the names of courses that the student has successfully completed. Print the course code and course name. A student has completed a course when s/he has obtained a final grade P, C, D or HD for the course.

Select student_id, course_id from Course_offering Q2 – Given a course name X, semester Y and year Z, print its timetable. That is, the details of all course offerings including: course code, course name, day of week, activity, start time, end time, campus, building and room numbers that the course is offered in.

select course_id, course_name,day_of_week,activity, time, campus_name, facility_building, facility_roomno from Courses, Timeperiod,Timetable,Campus,Facilities Q3 – Room number 101 in CT building will be closed for refurbishment in trimester 1, 2018. To inform the affected students, we need to get the email addresses of the affected students. Find the student number and email address of students who have classes in Room 101 in CT building for Trimester 1, 2018.Select student_name,student_phone,student_address from Student,Course_offering where Student.student_id =Course_offering.student_id and Course_offering.term_id=’T002′