sql some info for help Dean of Admission at DeAnza College has hired you to do t

Question

sql

some info for help

Dean of Admission at DeAnza College has hired you to do the database design for a new registration system. Keeping in the mind the following requirements, design a Database Schema.

1. College has several departments  

2. Department offer several courses  

3. Courses have sections.  

4. College has locations (Room Sem 5 etc.)  

5. Students register for a section from a catalog (which is composed of Departments, Courses, Sections, Location.)  

6. Students have to pay for the course they take. You may want to think about resident, non resident.  

AND THIS IS WHAT I WANT TO KNOW..

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

well we have the following tables....
Remember i am designing database which are in BCNF normal form. That is it is in 1NF, 2NF, 3NF too..

We have following tables...
College(CollegeID, name, address, phone)
Department(DepartmentID, name, number_faculty)
Courses(CoursesID, name, days_to_complete)
Student(StudentID, name, address, phone, email)
Payment(PaymentID, amount)
Section(SectionID, StudentID,CoursesID,DepartmentID,CollegeID,PaymentID)

This is the database.. Initialy college ,Department,Courses details are to be filled by
college adminstrative. Student will select these all fields from registration page and
then Student, Payment, Section tables will be updated.

1)
setup the tab;es information.

INSERT INTO TABLE College(CollegeID,name,address,phone) VALUES (111, 'IIT Delhi', 'delhi', 041234567);
INSERT INTO TABLE Department(DepartmentID, name, number_faculty) VALUES (1112, 'CSE', 20);
INSERT INTO TABLE Courses(CoursesID, name, days_to_complete) VALUES (1113, 'computer science',130);
INSERT INTO TABLE Section(SectionID, StudentID,CoursesID,DepartmentID,CollegeID,PaymentID) VALUES (767,-1,1113,1112,111,000);
INSERT INTO TABLE Section(SectionID, StudentID,CoursesID,DepartmentID,CollegeID,PaymentID) VALUES (768,-2,1113,1112,111,00);

After student registration...

UPDATE Section SET StudentID = 103310507, PaymentID = 145987 WHERE SectionID = 767;

****************************************************************************
We can update this database further by introducing faculty to students, mentors, marks obtained by studens,
semester information etc.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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