Question

Consider the database schema of college database. Students have a major department and take classes where faculty can be met.

Student (cwid int, name, text, age int, majorDept text)

Department (name text, chair text)

Faculty (name text, deptName text)

Enrollment (facName text, studID int)

Note that the underlined attributes are primary keys. studID, chair, deptName of Faculty, facName and majorDept of Student are foreign keys to cwid, name of Faculty, name of Department, name of Faculty and also name of Department, respectively. Insertion statements should include those tuples such that the SQL results should be NON-NULL.

a) Compare shreadsheets (e.g., Excel) and databases (e.g., SQLite3).

b) Compare simple database systems (e.g., SQLite3 or Microsoft Access) and complete database systems (e.g., Oracle or mySQL, SQLserver). Due to the shortage, how can such strong capabilities available in Oracle be implemented in a simple database system?

b) Define the table Department above in DML. Primary key should be defined.

d) Write an insertion statement to Student table (the value chosen by you as you wish)

e) Write an SQL statement to list the chair of all departments.

f) Write an SQL statement to list the faculty names who teach the student "Tom".

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

This demonstration is using SQL Server.

b.Create Department Table :

create table Department(
name varchar(50) primary key,
chair varchar(50)
);

d.Inserting records :

insert into student values(1,'Virat Kohli',23,'Maths');

e.

select chair from Department;

f.

select facName from Enrollment ,Student
where Enrollment.studID=Student.cwid and
name='Tom';

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.