create table StudentRegistration (regId number(5) primary key, StudentId number(

Question

create table StudentRegistration
(regId number(5) primary key,
StudentId number(5),
Feestatus varchar2(10) not null,
PaymentID number(3) references Payment
);

create table Section
(SectionId number(4) primary key,
SectionNo number(3),
Courseid number(5) references Course,
LocId number(3) references Location,
starttime datetime,
endtime datetime,
startdate datetime,
enddate datetime,
userID number(4) references user
);

create table course
(Courseid number(5) primary key,
CourseName varchar2(10),
Prereq varchar2(10),
Deptid number(2) references Department,
SectionId number(5) references Section
);

create table Department
(Deptid number(2) primary key,
Name varchar2(20) not null,
Locid number(3) references Location,
CollegeId number(50) references College
)

create table Resident
Resident-Id number(5) primary key,
ResiName varchar2(20) not null,
Feeschedule varchar2(20),
userID number(4) references user
)

create table Location
(Locid number(3) primary key,
LocationName varchar2(20) not null

);

create table Payment
(PaymentID number(3) primary key,
PaymentName varchar2(20) not null,
regId number(5) references StudentRegistration
);
create table USER
( userID number(4) primary key
FirstName varchar2(50) not null,
LastName varchar2(50),
Address varchar2(100),
Telno number(10) not null,
Email varchar2(20),
ResidentId number(3),
Type varchar2(10),
regId varchar2(5)
);

Using this:

Write DML Statements to simulate the following Business Processes:

1. Setup a department, course within a department with 2 sections. Section must have a location assigned. Produce a report showing the department, its course and sections (with complete section information.)

Now register a student to a section and process student payment. Produce a report showing student registration information, including payment information.

How can you improve your DB Schema further. Make four recommendations.

Explanation / Answer

select D.Name, C.CourseName, S.SectionId, S.SectionNo, S.Courseid, S.LocId, S.starttime, S.startdate, S.enddate, S.userID from Section S inner join Course C on C.Courseid = S.Courseid inner join Location L on L.LocId = S.LocId Inner join user u on S.userID = u.userID inner join Department D on D.Locid = S.LocId;

Insert into StudentRegistration values('2312' , '1245' , 'paid' , '1236756');

Select S.regid, S.StudentId, S.Feestatus, P.PaymentID, P.PaymentName from StudentRegistration s join Payment p on S.PaymentID = P.PaymentID;

Recommendatios:

1.Use auto increment option for primary key values.

2.Avoid using multiple joins

3.Use sql normalization from

4.Use procedures for complex queries

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.