Question

Student(Student number, student name, semester)

Course(Course number, course name, credits, transfer credits)

PART A:

1. Using above BS in MIS degree requirements, perform the following
   (25 points)

• Develop tables (provide create statements)
• Normalize them (make sure they are in 3^rd NF)

2. Draw a single ERD – clearly identify Primary and Foreign keys (state any assumptions made)
   (10 points)
3. Create following views:
   (5 points)

• Create a view that would list all MIS required courses
• Create a view that will give a total count of courses with course ID above 300 or more (ex: INSS300, INSS421, MGMT315 etc. should be included in the count)

BONUS (5 points)

• Develop the above tables in ORACLE
• Insert data in tables
• Create the view (part 3 above)
• give the query and output of part 3 from oracle

Answer 1

SQL

create table Student(SNumber numeric primary key, SName varchar(20), CNumber varchar(6), Semester numeric, foreign key(CNumber) references Course(CNumber));

You have made changes to the database.

insert into Student values(1, 'Gopal', 'CSC123', 1);

You have made changes to the database. Rows affected: 1

insert into Student values(2, 'Elangovan', 'CSC133', 2);

You have made changes to the database. Rows affected: 1

insert into Student values(3, 'Dhana', 'INSS300', 3);

You have made changes to the database. Rows affected: 1

insert into Student values(4, 'Vadivel', 'MGMT315', 1);

You have made changes to the database. Rows affected: 1

insert into Student values(5, 'Varnika', 'CSC155', 3);

You have made changes to the database. Rows affected: 1

select * from Student;

Number of Records: 5

SNumber	SName	CNumber	Semester
1	Gopal	CSC123	1
2	Elangovan	CSC133	2
3	Dhana	INSS300	3
4	Vadivel	MGMT315	1
5	Varnika	CSC155	3

create table Course(CNumber varchar(6) primary key, CName varchar(25), Credits numeric, Transfer_Credits numeric);

You have made changes to the database.

insert into Course values('CSC123', 'Computer Science', 10, 5);

You have made changes to the database. Rows affected: 1

insert into Course values('CSC133', 'Language', 6, 2);

You have made changes to the database. Rows affected: 1

insert into Course values('CSC155', 'COBOL Programming', 3, 7);

You have made changes to the database. Rows affected: 1

insert into Course values('INSS300', 'Information Security', 5, 5);

You have made changes to the database. Rows affected: 1

insert into Course values(' MGMT315', 'Management Information System', 9, 8);

You have made changes to the database. Rows affected: 1

select * from Course;

Number of Records: 5

CNumber	CName	Credits	Transfer_Credits
CSC123	Computer Science	10	5
CSC133	Language	6	2
CSC155	COBOL Programming	3	7
INSS300	Information Security	5	5
MGMT315	Management Information System	9	8

• Create a view that would list all MIS required courses

create view RequiredCourses as
select CName as 'Required Courses' from Course;

You have made changes to the database.

select * from RequiredCourses;

Number of Records: 5

Required Courses
Computer Science
Language
COBOL Programming
Information Security
Management Information System

• Create a view that will give a total count of courses with course ID above 300 or more (ex: INSS300, INSS421, MGMT315 etc. should be included in the count)

create view Count_Courses as
SELECT count(CNumber) as Count_Course FROM [Course] where CNumber like '__%__%3%__';

You have made changes to the database.

select * from Count_Courses;

Number of Records: 1

Count_Course
2

Oracle

• Create a view that would list all MIS required courses

create view RequiredCourses as
select CName
from Course;

select * from RequiredCourses;

CNAME
Computer Science
Language
COBOL Programming
Information Security
Management Information System

• Create a view that will give a total count of courses with course ID above 300 or more (ex: INSS300, INSS421, MGMT315 etc. should be included in the count)

create view Count_Courses as
SELECT count(CNumber) as Count_Course
FROM Course where CNumber like '__%__%3%__';

View created.

select * from Count_Courses;

COUNT_COURSE
2